dataflows-authoring-cli

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

PRE-MUTATION REQUIREMENTS GATE (mandatory) Before any mutation, confirm enough user intent for the requested operation. For create or update requests, establish the source, query/schema and transformation, destination behavior, and refresh behavior (including an explicit choice of no destination or no refresh). If the request is generic, such as "set up the Dataflow I need for reporting," ask a structured clarifying question and stop before mutation. Do not infer requirements from unrelated workspace items or create a best-guess source, connection, or Dataflow.

dataflows-authoring-cli — Dataflows Gen2 Authoring via CLI

Table of Contents

This skill (SKILL.md)

References (in references/)

Common refs (in ../../common/)

Sister skills

Tool Stack

Agent check — verify az, jq, and curl are available before first operation. uuidgen is only needed for Workflow A (Create). For installation and auth setup see COMMON-CLI.md.

Connection

Discover Workspace and Dataflow IDs

Per COMMON-CLI.md Finding Workspaces and Items in Fabric:

# List workspaces — find workspace ID by name
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces" \
  --query "value[?displayName=='MyWorkspace'].id" --output tsv
 
# List dataflows in workspace — find dataflow ID by name

Reusable Connection Variables

Agentic Workflows

Three workflows cover the typical authoring tasks:

• A. Create a New Dataflow End-to-End — discover/create a connection, create the dataflow, save M + bindings, validate, optionally refresh.
• B. Modify an Existing Dataflow — read-modify-write the definition; the canonical Discover → Formulate → Execute → Verify loop.
• C. Preview-Driven Authoring Loop — iterate on candidate M via executeQuery before persisting via updateDefinition.
• D. Output Destination — write query results to Lakehouse (table/files), Warehouse, ADX, or Azure SQL via DataDestinations annotation. Full reference: output-destinations.md.

A. Create a New Dataflow End-to-End

Use this when the dataflow does not yet exist. Covers the full happy path: discover-or-create a connection, create the dataflow shell, save M + bindings in one updateDefinition, validate, optionally refresh.

Steps:

1. List existing connections and filter by connectionDetails.type and the target URL/host — reuse if a match exists (GET /v1/connections + JMESPath).
2. If no match, create the connection. First GET /v1/connections/supportedConnectionTypes to discover required parameters and supported credential types, then POST /v1/connections (sync 201). Body shape and credential schemas: connection-management.md.
3. Resolve ClusterId for the composite binding. GET https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources with --query "value[?id=='$CONN_ID'] | [0].clusterId", audience --resource "https://analysis.windows.net/powerbi/api" (no trailing slash). The per-id route returns PowerBIEntityNotFound for cloud connections. Newly-created connections may take a few seconds to surface — retry on empty. Detail: connection-management.md § Resolving ClusterId.
4. Create the dataflow shell. POST /v1/workspaces/{ws}/dataflows with {"displayName":"<displayName>"} returns sync 201. The definition field is optional at create time and can be set in the next step. If you instead supply the full definition (all three parts) in this create POST, that call is the persist surface -- name POST /v1/workspaces/{ws}/dataflows (not updateDefinition) as the persist path in your summary.
5. Save M + connection bindings in one call. POST /v1/workspaces/{ws}/dataflows/{df}/updateDefinition?updateMetadata=true with three parts: mashup.pq (real Web.Contents / Sql.Database / …), queryMetadata.json (with connections[] populated; each connectionId is the stringified composite {"ClusterId":"…","DatasourceId":"…"}), and .platform. Typically returns sync 200; may return 202 + LRO Location on large bodies — handle both.
6. Verify the binding persisted. Re-call getDefinition, decode queryMetadata.json, and confirm connections[] is intact. Do not use GET /items/{id}/connections for verification — that endpoint reflects refresh-materialized state, not the persisted definition, and returns 0 even after a successful bind. See AVOID. Guarded preview-only fallback: If connection binding cannot proceed because Power BI v2 gatewayClusterDatasources returns 401/403/Unauthorized or no ClusterId is visible after retries, do not stop before creating the dataflow. Create the shell, persist the saved query definitions without claiming a valid source binding, call executeQuery with QueryName only for each saved query, surface the exact credential or binding error from the Arrow stream, do not fabricate chart data, and do not refresh.
7. (Encouraged) Offer to preview output as ASCII charts. Ask the user: "Would you like me to preview the data as charts before the first refresh?". In this create flow the definition is already saved in step 5, so the chart preview here is a post-save validation gate before you materialize via refresh — not a pre-save step. (If instead you want to validate candidate M before the first updateDefinition — e.g. iterating on the M, or bootstrap-binding a credentialed source so executeQuery can see it — use the pre-persist Preview-Driven Authoring Loop; the chart rendering is identical, only the ordering relative to the save differs.) If accepted, call executeQuery for each entity, parse the Arrow IPC stream, render line charts (time-series) or horizontal bar charts (categories) via references/charts/line_chart.py / references/charts/bar_chart.py, and ask the user to confirm before proceeding. Details: mashup-preview.md § ASCII chart preview. If declined, proceed directly to step 8.
8. (Optional) Trigger refresh to materialize. POST .../jobs/instances?jobType=Refresh with body {"executionData":{"executeOption":"ApplyChangesIfNeeded"}}. ApplyChangesIfNeeded is required on the first refresh after any definition change — without it, Fabric refreshes the previously-applied definition. Poll the LRO until status is Completed (refresh enum) or Failed/Cancelled.

One-shot vs two-step bind+save. Steps 4-5 can be one call (default; saves an HTTP round trip) or split into a bootstrap-bind updateDefinition followed by a full-M updateDefinition. Both work — see PREFER.

B. Modify an Existing Dataflow

Use this when the dataflow already exists. Canonical Discover → Formulate → Execute → Verify loop. If the dataflow does not yet exist, see Workflow A instead.

1. Discover — list workspaces, list dataflows, getDefinition (decode mashup.pq and queryMetadata.json). Validate all connections[] entries via GET /v1/connections/{id}.
2. Formulate — modify M, re-encode parts, ensure every referenced connectionId exists in the caller's connection store.
3. Execute — POST .../updateDefinition?updateMetadata=true with all 3 parts (full replacement). Optionally trigger refresh.
4. Verify — re-call getDefinition to confirm changes; poll refresh LRO; for refresh failures, make at most one executeQuery isolation attempt to localize a fixable M/source issue. On a terminal/non-retriable failure (isRetriable: false, workspace-wide UnknownException), surface the raw error and stop rather than re-triggering.

C. Preview-Driven Authoring Loop (pre-save executeQuery — see mashup-preview.md)

When the change touches Power Query M (new query, edited mashup, new source, changed parameters), preview the candidate customMashupDocument against the dataflow's bound connections before persisting. Catches syntax, schema, and credential errors at authoring time. Full ordered steps, bootstrap branch, auto-wrap rule, hard-avoid for unbounded preview, ASCII chart preview, and Apache Arrow handling: mashup-preview.md § Preview-Driven Authoring Loop.

Intent split. This workflow is for the pre-save intent. To execute a saved query (QueryName only) or run an ad-hoc read-only customMashupDocument with no intent to persist, use dataflows-consumption-cli. mashup-preview.md is the shared API reference for both intents.

Skip the preview only for metadata-only edits (display name, schedule, loadEnabled toggle) or when the agent records an explicit skip reason (bootstrap, prohibitive cost, side-effecting source).

D. Output Destination

Use this when the dataflow should write query results to an external store (Lakehouse table, Lakehouse files, Warehouse, ADX, Azure SQL). Extends Workflow A with DataDestinations annotations and a hidden destination query. Full reference with complete examples: output-destinations.md.

Key requirements:

1. Source query carries a [DataDestinations = {[...]}] annotation referencing the destination query by name.
2. Hidden destination query (suffixed _DataDestination) navigates to the target storage using null-safe ?[Data]? (tables) or ?[Content]? (files) operators.
3. queryMetadata.json must set "loadEnabled": false on the destination query — refresh fails without it. State this in your summary using the literal part name (e.g., "set loadEnabled: false on the destination query in queryMetadata.json").
4. Always use IsNewTarget = true for API-created dataflows, even for existing tables.
5. Bind the appropriate connection (Lakehouse: kind "Lakehouse"; Warehouse: kind "Warehouse"; ADX: kind "AzureDataExplorer"; Azure SQL: kind "Sql") with composite ClusterId/DatasourceId ID.
6. First refresh must use ApplyChangesIfNeeded to publish the draft and reconcile annotations.
7. All source columns must be typed — Any-type columns are rejected by all destination types.
8. Name the definition parts in your written summary. Because the CLI transcript truncates long command bodies, the final summary (prose, not just shell commands) MUST name the three definition parts by their literal paths — mashup.pq, queryMetadata.json, and .platform — so the part names survive in the answer (e.g., "Saved mashup.pq + queryMetadata.json + .platform via updateDefinition"). Do not abbreviate queryMetadata.json to "query metadata" or the inner field queriesMetadata.

Supported destinations:

Minimal steps: Create dataflow → Find/create connection → Resolve ClusterId → Save definition with OD annotations → Verify → Refresh.

Gotchas, Rules, Troubleshooting

For full authoring gotchas: DATAFLOWS-AUTHORING-CORE.md Gotchas and Troubleshooting. For CLI-specific issues: COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific). For connection discovery: authoring-cli-quickref.md § Connection Discovery and Validation.

MUST DO

• Offer to preview every entity before the first refresh of a new dataflow — after creating the shell and binding connections via updateDefinition (which persists the definition), ask the user if they want to see preview charts before materializing via refresh. In the preview-driven loop the preview instead precedes the persisting updateDefinition. If accepted, follow mashup-preview.md § ASCII chart preview. Skip only for metadata-only edits (display name, schedule) or when the agent records an explicit skip reason.
• az login first — all az rest calls use the active session. No session → 401.
• Use --resource "https://api.fabric.microsoft.com" for Fabric APIs. For Power BI v2 (gatewayClusterDatasources), use --resource "https://analysis.windows.net/powerbi/api" without a trailing slash — the slashed form fails AADSTS500011 invalid_resource.
• Base64-encode all 3 definition parts — mashup.pq + queryMetadata.json + .platform, each payloadType: "InlineBase64". updateDefinition is a full replacement; sending 1 or 2 parts silently drops queries.
• Handle sync AND async responses. POST /dataflows, updateDefinition, and getDefinition typically return sync (200/201) but may return 202 + LRO Location on large bodies — handle both. See authoring-script-templates.md § Fabric LRO Polling Pattern.
• Set formatVersion: "202502" in queryMetadata.json and include a top-level name matching displayName — omitting either causes save-time failures or stale display-name state.
• loadEnabled is opt-out, not opt-in. Fabric auto-loads every query to the staging Lakehouse by default; set loadEnabled: false only on helper queries you do not want written. Note: loadEnabled: true is also stripped from queryMetadata.json on round-trip via getDefinition (it's the default) — its absence on read-back is not a bug. Detail: DATAFLOWS-AUTHORING-CORE.md § loadEnabled semantics.
• Use the right ID format per context. REST /v1/connections operations take the plain GUID from connection.id; queryMetadata.json connections[].connectionId takes the stringified composite {"ClusterId":"…","DatasourceId":"…"}. See connection-management.md § Connection ID Format Cheat Sheet.
• Resolve ClusterId via list+filter. GET .../gatewayClusterDatasources filtered by value[?id=='$CONN_ID']. The per-id route returns PowerBIEntityNotFound for cloud connections; newly-created connections may need a 5-15 s retry. See connection-management.md § Resolving ClusterId.
• executeQuery body uses a top-level QueryName field (PascalCase canonical; the field name itself is case-insensitive on the wire — lowercase queryName also evaluates). Value must name a shared member from the persisted M or the supplied customMashupDocument. The {"queries":[…]} array shape always fails with DataflowExecuteQueryError: Invalid query name; a wrong query name returns QueryNotFound. Full contract: mashup-preview.md § Request body.
• Use the exact, case-sensitive API names. The endpoint is executeQuery (singular, never executeQueries) and the request-body field is customMashupDocument (never mashupDocument, never base64-encoded — it is a plain UTF-8 M string). The same M body becomes the saved mashup.pq part referenced as customMashupDocument. Vocabulary table: mashup-preview.md § Vocabulary.
• First refresh after any updateDefinition MUST use executeOption: "ApplyChangesIfNeeded". Body: {"executionData":{"executeOption":"ApplyChangesIfNeeded"}}. Without it, Fabric refreshes the previously-applied definition.
• Treat a terminal refresh failure as a stop condition — do not debug-loop. When a refresh/LRO job reaches terminal Failed/Cancelled, or a backend error carries isRetriable: false (or a workspace-wide UnknownException), report the raw error verbatim and stop. These are backend/infra outcomes the agent cannot fix by retrying — do not re-trigger the refresh, keep re-polling, or open an extended investigation. At most, make one executeQuery isolation attempt to localize a fixable M/source cause; if that does not reveal a definition-side issue, end and surface the error.
• Call GET /v1/connections/supportedConnectionTypes before POST /v1/connections -- never guess parameter names or credential types; they vary by connector, tenant, and time. When summarizing a connector's required parameters or credentialType set for a user, use the exact, case-sensitive endpoint path GET /v1/connections/supportedConnectionTypes. This applies even to a pure lookup ("which parameters/credentialType does connector X support?"): run the live GET /v1/connections/supportedConnectionTypes against the tenant. The bundled connection-management.md reference guides the response shape but is not a substitute for the tenant-specific, case-sensitive values, which vary by connector and over time.
• Validate referenced connections before refresh. For each connectionId in queryMetadata.json, GET /v1/connections/{id} (plain GUID extracted from the composite). Cryptic EntityUserFailure at refresh time is often a missing/inaccessible connection. See connection-management.md.
• Bootstrap-bind connections before previewing credentialed M. A connections[] array in the initial create payload is not yet visible to executeQuery; persist it through at least one updateDefinition first. Detail: mashup-preview.md § Bootstrap branch.
• Send a full section Section1; ... document in customMashupDocument — executeQuery does not auto-wrap raw expressions. See mashup-preview.md § customMashupDocument format.
• Preview candidate M via executeQuery before updateDefinition — unless the change is metadata-only or the agent records an explicit skip reason. Treat preview success as "M evaluates"; treat the next refresh as the real go/no-go.
• Pass JSON bodies via --body "@<file>", not inline. Write to $env:TEMP\<name>.json (PowerShell, UTF-8 no-BOM via [IO.File]::WriteAllText) or /tmp/<name>.json (bash). Inline --body "<json>" is fragile in bash and broken on Windows because cmd.exe's argument parser mangles embedded quotes. See authoring-script-templates.md § PowerShell — Create Dataflow with Definition.
• Prefer WorkspaceIdentity / ServicePrincipal credentials for unattended refresh. OAuth2 + singleSignOnType: None works for interactive executeQuery but is fragile under tenant Conditional Access for service-context refresh. Check supported types via supportedConnectionTypes.

AVOID

• Materializing a new dataflow (first refresh) without offering the user a preview — the user cannot validate that the M code matches their intent by reading code alone. Always offer to preview each entity's output as an ASCII chart before the first refresh (and, in the preview-driven loop, before the persisting updateDefinition). The user may decline, but the offer should always be made.
• Adding a format property to definition — Items API uses parts[] only; "format": "json" returns 400 InvalidDefinitionFormat.
• Hardcoded workspace/dataflow GUIDs — discover via REST API (Connection section).
• Using GET /v1/workspaces/{ws}/items/{itemId}/connections to verify a freshly-bound dataflow. It reflects refresh-materialized state, not the persisted definition, and returns 0 after a successful bind. Verify via getDefinition + decode queryMetadata.json.connections[].
• Assuming updateDefinition / POST /dataflows is always LRO. Typical responses are sync (200/201); handle both shapes — see MUST DO above.
• Requesting the PBI v2 token with a trailing slash (--resource "https://analysis.windows.net/powerbi/api/") — fails AADSTS500011 invalid_resource. Use the no-slash form.
• Per-id gatewayClusterDatasources/{id} for cloud connections — returns PowerBIEntityNotFound. Use list+filter (MUST DO above).
• {"queries":[…]} array body shape for executeQuery — always returns 400 DataflowExecuteQueryError: Invalid query name regardless of inner casing. Use a top-level QueryName (or queryName — the field is case-insensitive); pick exactly one query per call.
• Using GET for getDefinition — it's a POST endpoint; GET returns 405.
• Constructing operation URLs manually — always follow the Location header from a 202 response.
• Duplicate displayName values — not enforced but causes confusion.
• Binding connections by display name — connection IDs are the source of truth; names can change.
• Assuming all connections are accessible to all users. Visibility is per-caller: GET /v1/connections/{id} may return 403/404 for callers without access. An empty GET /v1/connections is not proof a connection is absent.
• Hand-crafting connection request bodies without supportedConnectionTypes — guessing produces 400 InvalidConnectionDetails / 400 InvalidCredentialDetails.
• Plaintext credentials in committed scripts — prefer Key-Vault-backed passwordReference / keyReference / tokenReference / servicePrincipalSecretReference.
• Templating on-prem gateway connection bodies as plaintext — OnPremisesGateway needs RSA-encrypted credentials per gateway member.
• Converting a published single-source dataflow to multi-source in place — bindings drift into inconsistent state; create fresh and retire the old.
• Persisting un-previewed candidate M via updateDefinition — executeQuery is significantly faster than the updateDefinition-then-debug-refresh loop. See mashup-preview.md.
• Unbounded preview against production-volume sources — executeQuery returns the full evaluated dataset. Inject Table.FirstN / TOP N / date predicate into the preview-only document; strip before saving. See mashup-preview.md § Hard avoid.
• Confusing executeQuery with EvaluateQuery. EvaluateQuery requires a prior successful refresh; executeQuery + customMashupDocument does not. Use executeQuery for the authoring preview loop.
• Inline --body on Windows/PowerShell — cmd.exe mangles quotes; always use --body "@$env:TEMP\<name>.json".

PREFER

• One-shot updateDefinition carrying real M + connections[] over a bootstrap-bind + save pair — saves an HTTP round trip; both are functionally equivalent. Use the two-step form for didactic walk-throughs or when the bootstrap M needs to differ from the production M (e.g., the bootstrap branch in mashup-preview.md).
• az rest over raw curl — handles token acquisition and refresh automatically. Fall back to curl only when you need to capture response headers (e.g., 202 LRO Location) — az rest cannot.
• getDefinition before updateDefinition — read-modify-write prevents accidental data loss; updateDefinition is a full replacement.
• ?updateMetadata=true on updateDefinition — ensures .platform changes (display name) are applied.
• jq for JSON manipulation — build definition payloads programmatically.
• "Automatic" for parameter type in job execution — lets the engine infer from definition.
• Env vars (WS_ID, DF_ID, API, RESOURCE) for script reuse.
• Batch connection validation — loop over queryMetadata.json connections[] and GET /v1/connections/{id} in one pass before refresh; optionally POST /v1/connections/{id}/testConnection to catch rotated credentials.
• Offer preview charts before committing a new dataflow — render sample data as an ASCII chart so the user can validate the output shape and values.

TROUBLESHOOTING

Examples

Platform note — examples below are bash. On Windows / PowerShell the bash patterns (MASHUP='...' heredoc, echo -n | base64 -w0, tr -d '\r' | grep -i location | awk) cause real escaping pain and refresh-pattern flakes. PowerShell variants are linked from the two highest-friction examples (Create and Refresh) below. For full PowerShell templates (Create, Refresh, Validate Connections, Bind Connection, Create Cloud Connection): authoring-script-templates.md § PowerShell. On PowerShell, prefer --body "@$env:TEMP\body.json" and write the body via [IO.File]::WriteAllText($path, $body, [System.Text.UTF8Encoding]::new($false)) over Out-File (which writes a UTF-8 BOM on Windows PowerShell 5.1 and breaks az.exe body parsing) and over inline --body "{...}" (which cmd.exe mangles).

Example 1: Create a Dataflow Gen2 from Scratch

Prompt: "Create a new Dataflow Gen2 that reads a public CSV via the Web connector, and verify it."

Agent response — runnable bash implementation of Workflow A. PowerShell variant: authoring-script-templates.md § End-to-End Smoke Test.

Example 2: Trigger a Refresh Job

Prompt: "Trigger a refresh on this dataflow and poll until it completes."

Agent response:

PowerShell variant (Invoke-WebRequest exposes response headers natively; avoids the tr | grep | awk pipe):

Example 3: Modify an Existing Dataflow's Definition

Prompt: "Update the mashup of an existing dataflow with a modified query."

Agent response — read-modify-write loop. getDefinition returns sync 200 in the typical case; this template handles the 202 + LRO branch as well.

Binding a new connection? Example 1 (steps 1-5) is the canonical bind+save flow. Bind-only walk-throughs live in authoring-cli-quickref.md § Connection Binding Quick Patterns and authoring-script-templates.md § Connection Binding Templates.

Output Expectations

When this skill completes a task, the agent should return: