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

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
WS_ID="<workspaceId>"
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/dataflows" \
  --query "value[?displayName=='MyDataflow'].id" --output tsv

Reusable Connection Variables

WS_ID="<workspaceId>"
DF_ID="<dataflowId>"
API="https://api.fabric.microsoft.com/v1"
RESOURCE="https://api.fabric.microsoft.com"

---

Agentic Workflows

Three workflows cover the typical authoring tasks:

• [A. Create a New Dataflow End-to-End](#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](#b-modify-an-existing-dataflow) — read-modify-write the definition; the canonical Discover → Formulate → Execute → Verify loop.
• [C. Preview-Driven Authoring Loop](#c-preview-driven-authoring-loop) — iterate on candidate M via executeQuery before persisting via updateDefinition.
• [D. Output Destination](#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":"…"} returns sync 201. The definition field is optional at create time and can be set in the next step. 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. 7. (Optional) Validate via executeQuery before refresh. POST /v1/workspaces/{ws}/dataflows/{df}/executeQuery with body {"QueryName":"<shared-member>"} (top-level, PascalCase QueryName). See Workflow C. 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.

# Concise skeleton — full runnable bash is Example 1 below.
# PowerShell + LRO-polled variants: references/authoring-script-templates.md

WS_ID="<workspaceId>"; URL="<source-url>"
RES="https://api.fabric.microsoft.com"; API="$RES/v1"
PBI="https://analysis.windows.net/powerbi/api"

# 1. List existing & try reuse
CONN_ID=$(az rest --method get --resource "$RES" --url "$API/connections" \
  --query "value[?connectionDetails.type=='Web' && connectionDetails.path=='$URL'] | [0].id" -o tsv)

# 2. Create connection if missing — see connection-management.md for full body
# 3. List+filter for ClusterId
CLUSTER_ID=$(az rest --method get --resource "$PBI" \
  --url "https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources" \
  --query "value[?id=='$CONN_ID'] | [0].clusterId" -o tsv)

# 4. Empty dataflow shell — sync 201
SHELL_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"displayName":"my-df"}' > "$SHELL_BODY"
DF_ID=$(az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows" \
  --headers "Content-Type=application/json" \
  --body "@$SHELL_BODY" --query id -o tsv)
rm -f "$SHELL_BODY"

# 5. One-shot updateDefinition with real M + connections[] (sync 200 typical)
#    Body assembly (mashup.pq + queryMetadata.json + .platform, base64-encoded;
#    queryMetadata.json.connections[].connectionId = composite ClusterId/DatasourceId):
#    see Example 1 below.

# 6. Verify via getDefinition (NOT GET /items/{id}/connections — see AVOID)
# 7. (optional) executeQuery — Workflow C
# 8. (optional) Refresh with executeOption=ApplyChangesIfNeeded — Example 2

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, isolate M+source via executeQuery before re-triggering.

# Concise skeleton — full templates: references/authoring-script-templates.md
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = $RESOURCE).
RESOURCE="https://api.fabric.microsoft.com"; API="$RESOURCE/v1"

# 1. Discover — getDefinition (handles 200 sync and 202 + LRO via curl)
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  -D "$HDR" -o "$BODY" -w "%{http_code}")
if [ "$CODE" = "202" ]; then
  LOC=$(tr -d '\r' < "$HDR" | grep -i "^location:" | awk '{print $2}')
  RETRY=$(tr -d '\r' < "$HDR" | grep -i "^retry-after:" | awk '{print $2}'); RETRY=${RETRY:-5}
  while :; do
    sleep "$RETRY"
    OP=$(az rest --method get --resource "$RESOURCE" --url "$LOC")
    case "$(echo "$OP" | jq -r '.status // empty')" in
      Succeeded) RESULT=$(az rest --method get --resource "$RESOURCE" --url "${LOC%/}/result"); break ;;
      Failed|Cancelled) echo "ERROR: getDefinition $(echo "$OP" | jq -r '.status')" >&2; exit 1 ;;
    esac
  done
else
  RESULT=$(cat "$BODY")
fi
rm -f "$HDR" "$BODY"

# Validate bound connections (connectionId is a composite JSON string — iterate safely)
QUERY_META=$(echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)
echo "$QUERY_META" | jq -c '.connections[]?' | while IFS= read -r conn; do
  RAW=$(echo "$conn" | jq -r '.connectionId')
  DATASOURCE_ID=$(echo "$RAW" | jq -r '.DatasourceId? // empty' 2>/dev/null)
  [ -z "$DATASOURCE_ID" ] && DATASOURCE_ID="$RAW"
  # GET /v1/connections/$DATASOURCE_ID to confirm access
done

# 2-3. Formulate & Execute — see Example 3
# 4. Verify — trigger refresh via curl (az rest cannot capture Location header).
#    Full LRO polling: references/authoring-script-templates.md.

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 prerequisites, bootstrap branch, auto-wrap rule, hard-avoid for unbounded preview, and Apache Arrow handling: mashup-preview.md.

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.

Minimal ordered steps:

1. Locate or create the dataflow shell — POST /v1/workspaces/{ws}/dataflows with {"displayName":"…"} (workflow A step 4). 2. Ensure connections are bound — for new credentialed sources, do a minimal updateDefinition with queryMetadata.json connections[] first (the "bootstrap save"). A connections[] array declared only in the initial create payload is not yet visible to executeQuery. 3. Compose the candidate `customMashupDocument` as a complete section Section1; ... document. The request's QueryName (top-level, PascalCase) must match a shared member in the document. 4. Preview — POST /v1/workspaces/{ws}/dataflows/{df}/executeQuery with body {"QueryName": "<name>", "customMashupDocument": "<section>"}. Pass --output-file results.arrow — az rest writes the raw Apache Arrow IPC stream to disk. Arrow → CSV/pandas: dataflows-consumption-cli § Query Evaluation. 5. Validate the preview (two-tier — both required before persisting):

• a. Embedded-error check. HTTP 200 is not proof of success; engine errors are embedded inside the stream as {"Error":"..."}. Quick scan: grep -q '"Error":"' results.arrow. Canonical pyarrow detector inspects schema metadata — see mashup-preview.md § Error handling — A.
• b. Render `head(10)` as a markdown table to the user. The embedded-error check only catches engine-level failures (column not found, cast errors, SEM0100, etc.). It does not catch silent-success bugs: filter dropped all rows, wrong column referenced, wrong join key, off-by-one filter, wrong cast producing epoch dates. The 10-row visual lets the human verify shape, row count, and value sanity in seconds. Snippet + suppression rules: dataflows-consumption-cli § Example 5b.
• c. Probe for per-cell errors. An errored cell serializes as an Arrow null — indistinguishable from a genuine null in the head(10) view. To disambiguate, wrap the cell in try and read the [HasError] field: try <step>{N}[Col] returns [HasError = true, Error = [...]] for an errored cell vs [HasError = false, Value = ...] otherwise; filter with Table.SelectRows(<step>, each not (try [Col])[HasError]). Detail: m-language.md § Per-cell errors.

6. Persist via `updateDefinition` — strip any preview-only Table.FirstN / TOP N / test-mode parameters from the saved mashup. Verify queryMetadata.json connections[] survived the full-replacement write before triggering refresh.

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.

# Skeleton — full PowerShell recipe: references/output-destinations.md § Complete Example
WS_ID="<workspaceId>"; LH_ID="<lakehouseId>"; RES="https://api.fabric.microsoft.com"

# M pattern (two queries):
# 1. Source with [DataDestinations] annotation
# 2. Hidden _DataDestination query with ?[Data]? null-safe navigation
# queryMetadata: source loadEnabled=true, destination loadEnabled=false + isHidden=true
# Refresh: {"executionData":{"executeOption":"ApplyChangesIfNeeded"}}

---

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

• `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.
• 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.
• 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

• 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.

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.

# Prereqs: az login, jq, base64, uuidgen. Workspace must support Dataflow Gen2.
WS_ID="<workspaceId>"
DF_NAME="my-titanic-df"
CONN_NAME="my-titanic-web-conn"
URL="https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv"
RES="https://api.fabric.microsoft.com"; API="$RES/v1"
PBI="https://analysis.windows.net/powerbi/api"   # NO trailing slash

# Step 1: List existing connections, try to reuse by name.
CONN_ID=$(az rest --method get --resource "$RES" --url "$API/connections" \
  --query "value[?displayName=='$CONN_NAME'] | [0].id" -o tsv)

# Step 2: Create if missing (Web + Anonymous; see connection-management.md for other shapes).
if [ -z "$CONN_ID" ] || [ "$CONN_ID" = "null" ]; then
  BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp)  # GNU + BSD/macOS compatible
  cat > "$BODY_FILE" <<EOF
{
  "displayName": "$CONN_NAME",
  "connectivityType": "ShareableCloud",
  "connectionDetails": {
    "type": "Web", "creationMethod": "Web",
    "parameters": [{"name": "url", "dataType": "Text", "value": "$URL"}]
  },
  "privacyLevel": "Organizational",
  "credentialDetails": {
    "singleSignOnType": "None", "connectionEncryption": "NotEncrypted",
    "skipTestConnection": false,
    "credentials": {"credentialType": "Anonymous"}
  }
}
EOF
  CONN_ID=$(az rest --method post --resource "$RES" --url "$API/connections" \
    --headers "Content-Type=application/json" --body "@$BODY_FILE" --query id -o tsv)
  rm -f "$BODY_FILE"
fi

# Step 3: Resolve ClusterId via list+filter; retry — PBI v2 lags by 5-15s on new conns.
for i in 1 2 3 4 5 6 7 8; do
  CLUSTER_ID=$(az rest --method get --resource "$PBI" \
    --url "https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources" \
    --query "value[?id=='$CONN_ID'] | [0].clusterId" -o tsv 2>/dev/null)
  [ -n "$CLUSTER_ID" ] && [ "$CLUSTER_ID" != "null" ] && break
  sleep $((i*3))
done
# Fail-fast: an empty ClusterId silently corrupts the composite connectionId and the
# resulting updateDefinition / refresh failures are hard to debug. Stop here instead.
if [ -z "$CLUSTER_ID" ] || [ "$CLUSTER_ID" = "null" ]; then
  echo "FAIL: ClusterId not resolved for $CONN_ID after retries. Verify the connection is visible at PBI v2 (api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources)." >&2
  exit 1
fi

# Step 4: Create empty dataflow shell (sync 201).
SHELL_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"displayName":"%s"}' "$DF_NAME" > "$SHELL_BODY"
DF_ID=$(az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows" \
  --headers "Content-Type=application/json" \
  --body "@$SHELL_BODY" --query id -o tsv)
rm -f "$SHELL_BODY"

# Step 5: One-shot updateDefinition — real M + composite-bound connections[] + .platform.
MASHUP='section Section1;
shared Titanic = let
    Source = Csv.Document(Web.Contents("'"$URL"'"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in Headers;'

COMPOSITE_ID="{\"ClusterId\":\"$CLUSTER_ID\",\"DatasourceId\":\"$CONN_ID\"}"
QUERY_META=$(jq -n --arg name "$DF_NAME" --arg cid "$COMPOSITE_ID" --arg url "$URL" --arg qid "$(uuidgen)" '{
  formatVersion: "202502",
  name: $name,
  queriesMetadata: { Titanic: { queryId: $qid, queryName: "Titanic" } },
  connections: [ { connectionId: $cid, kind: "Web", path: $url } ]
}')
PLATFORM=$(jq -n --arg name "$DF_NAME" --arg lid "$(uuidgen)" '{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json",
  metadata: { type: "Dataflow", displayName: $name },
  config: { version: "2.0", logicalId: $lid }
}')

MASHUP_B64=$(echo -n "$MASHUP" | base64 -w0)
META_B64=$(echo -n "$QUERY_META" | base64 -w0)
PLAT_B64=$(echo -n "$PLATFORM" | base64 -w0)

BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp)  # GNU + BSD/macOS compatible
cat > "$BODY_FILE" <<EOF
{"definition":{"parts":[
  {"path":"mashup.pq",          "payload":"${MASHUP_B64}", "payloadType":"InlineBase64"},
  {"path":"queryMetadata.json", "payload":"${META_B64}",   "payloadType":"InlineBase64"},
  {"path":".platform",          "payload":"${PLAT_B64}",   "payloadType":"InlineBase64"}
]}}
EOF
az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/updateDefinition?updateMetadata=true" \
  --headers "Content-Type=application/json" --body "@$BODY_FILE"
rm -f "$BODY_FILE"

# Step 6: Verify connections[] persisted via getDefinition (NOT /items/{id}/connections).
# Assumes the sync 200 fast-path (typical, ~1s). If the call ever returns 202 LRO,
# az rest can't expose the Location header — switch to the curl + poll pattern from
# Example 3 / authoring-script-templates.md and decode the polled 200 body instead.
PERSISTED=$(az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  --headers "Content-Length=0" \
  | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d \
  | jq -r '.connections | length')
[ "${PERSISTED:-0}" -gt 0 ] && echo "OK: connections[] persisted." || { echo "FAIL: bind missing (or getDefinition returned a 202 LRO body — see note above)." >&2; exit 1; }

# Step 7 (optional): Validate the M evaluates — top-level QueryName, PascalCase.
EQ_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
printf '{"QueryName":"Titanic"}' > "$EQ_BODY"
az rest --method post --resource "$RES" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/executeQuery" \
  --headers "Content-Type=application/json" \
  --body "@$EQ_BODY" --output-file /tmp/titanic.arrow
rm -f "$EQ_BODY"
# Apache Arrow stream — embedded {"Error":"..."} means failure even on HTTP 200.
grep -q '"Error":"' /tmp/titanic.arrow && { echo "executeQuery surfaced an error." >&2; exit 1; }

# Step 8 (optional): Trigger refresh with ApplyChangesIfNeeded on first run — see Example 2.

Example 2: Trigger a Refresh Job

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

Agent response:

# Trigger refresh (returns 202 + Location header for polling).
# jobType MUST be "Refresh"; "Pipeline" returns 400 InvalidJobType.
# On the first refresh after any updateDefinition, body MUST include executeOption=ApplyChangesIfNeeded
# (otherwise Fabric refreshes the previously-applied definition).
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = https://api.fabric.microsoft.com).
LOCATION=$(curl -sS -X POST \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  --data '{"executionData":{"executeOption":"ApplyChangesIfNeeded"}}' \
  "https://api.fabric.microsoft.com/v1/workspaces/${WS_ID}/dataflows/${DF_ID}/jobs/instances?jobType=Refresh" \
  -o /dev/null -D - | tr -d '\r' | grep -i "^location:" | awk '{print $2}')

# Poll until terminal (Fabric refresh job status enum: NotStarted / InProgress / Completed / Failed / Cancelled).
while true; do
  STATUS=$(az rest --method get --url "$LOCATION" \
    --resource "https://api.fabric.microsoft.com" --query "status" -o tsv)
  echo "Status: $STATUS"
  [[ "$STATUS" == "Completed" || "$STATUS" == "Failed" || "$STATUS" == "Cancelled" ]] && break
  sleep 10
done

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

# Notes:
# - $Resp.Headers["Location"] returns string or string[] depending on PS version — never
#   use .Location[0] (returns first character on Windows PS 5.1 plain-string case).
# - Wrap Invoke-WebRequest in try/catch on 5.1 (-SkipHttpErrorCheck is PS 7+).
# - Fabric refresh job status enum: NotStarted / InProgress / Completed / Failed / Cancelled.
#   This is distinct from the LRO operation enum (Running / Succeeded / Failed / Cancelled).
#   Refresh "success" = "Completed", not "Succeeded".
# Acquire $Token per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = https://api.fabric.microsoft.com).
try {
  $Resp = Invoke-WebRequest -Method POST -UseBasicParsing `
    -Uri "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/dataflows/$DF_ID/jobs/instances?jobType=Refresh" `
    -Headers @{ Authorization = "Bearer $Token"; "Content-Type" = "application/json" } `
    -Body '{"executionData":{"executeOption":"ApplyChangesIfNeeded"}}'
} catch {
  Write-Error "Refresh trigger failed: $($_.Exception.Message)"; exit 1
}
$Location = $Resp.Headers["Location"]
if ($Location -is [array]) { $Location = $Location[0] }

while ($true) {
  $Status = az rest --method get --url $Location `
    --resource "https://api.fabric.microsoft.com" --query "status" -o tsv
  Write-Host "Status: $Status"
  if ($Status -in 'Completed','Failed','Cancelled') { break }
  Start-Sleep -Seconds 10
}

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.

RESOURCE="https://api.fabric.microsoft.com"
# Acquire $TOKEN per common/COMMON-CLI.md § Token-in-Variable Pattern (resource = $RESOURCE).

# 1. Read current definition (sync 200 or 202 LRO — handle both).
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "$RESOURCE/v1/workspaces/${WS_ID}/dataflows/${DF_ID}/getDefinition" \
  -D "$HDR" -o "$BODY" -w "%{http_code}")
if [ "$CODE" = "202" ]; then
  LOC=$(tr -d '\r' < "$HDR" | grep -i "^location:" | awk '{print $2}')
  RETRY=$(tr -d '\r' < "$HDR" | grep -i "^retry-after:" | awk '{print $2}'); RETRY=${RETRY:-5}
  while :; do
    sleep "$RETRY"
    OP=$(az rest --method get --resource "$RESOURCE" --url "$LOC")
    case "$(echo "$OP" | jq -r '.status // empty')" in
      Succeeded) DEF=$(az rest --method get --resource "$RESOURCE" --url "${LOC%/}/result"); break ;;
      Failed|Cancelled) echo "ERROR: getDefinition $(echo "$OP" | jq -r '.status')" >&2; exit 1 ;;
    esac
  done
else
  DEF=$(cat "$BODY")
fi
rm -f "$HDR" "$BODY"

# 2. Decode each part, modify mashup.pq, re-encode all 3.
MASHUP=$(echo "$DEF" | jq -r '.definition.parts[] | select(.path=="mashup.pq")          | .payload' | base64 -d)
META=$(  echo "$DEF" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)
PLAT=$(  echo "$DEF" | jq -r '.definition.parts[] | select(.path==".platform")          | .payload' | base64 -d)

NEW_MASHUP=$(echo "$MASHUP" | sed 's/old-pattern/new-pattern/')   # edit M here

MASHUP_B64=$(echo -n "$NEW_MASHUP" | base64 -w0)
META_B64=$(echo -n "$META"        | base64 -w0)
PLAT_B64=$(echo -n "$PLAT"        | base64 -w0)

# 3. Build the updateDefinition body in a temp file (full replacement — all 3 parts).
BODY_FILE=$(mktemp --suffix=.json 2>/dev/null || mktemp)  # GNU + BSD/macOS compatible
cat > "$BODY_FILE" <<EOF
{"definition":{"parts":[
  {"path":"mashup.pq",          "payload":"${MASHUP_B64}", "payloadType":"InlineBase64"},
  {"path":"queryMetadata.json", "payload":"${META_B64}",   "payloadType":"InlineBase64"},
  {"path":".platform",          "payload":"${PLAT_B64}",   "payloadType":"InlineBase64"}
]}}
EOF
az rest --method post --resource "$RESOURCE" \
  --url "$RESOURCE/v1/workspaces/${WS_ID}/dataflows/${DF_ID}/updateDefinition?updateMetadata=true" \
  --headers "Content-Type=application/json" --body "@$BODY_FILE"
rm -f "$BODY_FILE"

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:

Authoring CLI Quick Reference

Concise az rest invocation patterns for Dataflows Gen2 authoring, base64 helpers, definition manipulation, and agent tips. For full API patterns and M code structure, see DATAFLOWS-AUTHORING-CORE.md. For full reusable scripts, see authoring-script-templates.md.

All examples assume reusable connection variables are set:

WS_ID="<workspaceId>"
DF_ID="<dataflowId>"
API="https://api.fabric.microsoft.com/v1"
RESOURCE="https://api.fabric.microsoft.com"

Cross-shell `--body` rule — bash snippets below use inline --body "{...}" for brevity, but

on Windows / PowerShell that pattern breaks: az.exe is launched through cmd.exe's

argument parser, which mangles embedded quotes and corrupts base64 payloads. For any non-trivial

body (anything with quotes, newlines, or base64), write the JSON to a temp file and pass

--body "@<path>". PowerShell-safe write that avoids a UTF-8 BOM:

[IO.File]::WriteAllText("$env:TEMP\body.json", $json, [Text.UTF8Encoding]::new($false)).

Full PowerShell create template (workload-specific /dataflows endpoint, no type field needed):

authoring-script-templates.md § PowerShell — Create Dataflow with Definition.

Core Authoring via CLI

Create Dataflow (Empty)

az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows" \
  --body '{"displayName":"MyDataflow","description":"Sales ETL dataflow"}'

Create Dataflow (With Definition)

# Prepare definition parts (base64-encode each file)
QM_B64=$(cat queryMetadata.json | base64 -w0)
MASHUP_B64=$(cat mashup.pq | base64 -w0)
PLATFORM_B64=$(cat .platform | base64 -w0)

az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows" \
  --body "{
    \"displayName\": \"MyDataflow\",
    \"definition\": {
      \"parts\": [
        {\"path\":\"queryMetadata.json\",\"payload\":\"$QM_B64\",\"payloadType\":\"InlineBase64\"},
        {\"path\":\"mashup.pq\",\"payload\":\"$MASHUP_B64\",\"payloadType\":\"InlineBase64\"},
        {\"path\":\".platform\",\"payload\":\"$PLATFORM_B64\",\"payloadType\":\"InlineBase64\"}
      ]
    }
  }"

Get Definition

⚠ LRO caveat: getDefinition can return 202 + Location (the body is empty on the initial response) instead of an inline 200. The one-liner below only works for the synchronous case. For production code, use the LRO-aware curl pattern in Validate All Connections in a Dataflow below, or copy the Fabric LRO Polling Pattern bash branch into your script.

# POST (not GET!) — happy-path one-liner; 200 only. See LRO caveat above.
az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition"

Preview Before Save (executeQuery)

Preview a candidate Power Query M document against the dataflow's bound connections before persisting via updateDefinition. Surfaces syntax / source / credential errors at authoring time. Full bootstrap branch + auto-wrap rule + Arrow handling: mashup-preview.md. Full recipe + Arrow → CSV: dataflows-consumption-cli § Query Evaluation.

# customMashupDocument MUST be a complete `section Section1; ... shared X = ...;` doc.
# QueryName MUST match a `shared` member in the document.
# Cap preview cost: include Table.FirstN / TOP N — strip before saving.

QUERY_NAME="Customers"
M_DOC='section Section1;
shared Customers = let
    Source = Sql.Database("srv","db"),
    T = Source{[Schema="dbo", Item="Customers"]}[Data],
    Limited = Table.FirstN(T, 100)
in Limited;'

# executeQuery returns raw Apache Arrow IPC bytes (NOT a JSON envelope) via az rest.
# Pass --output-file so az captures the binary cleanly; failures are embedded as
# {"Error":"..."} inside the stream and HTTP 200 alone does NOT mean success.
jq -n --arg q "$QUERY_NAME" --arg m "$M_DOC" \
  '{QueryName: $q, customMashupDocument: $m}' > preview-req.json

az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/executeQuery" \
  --body @preview-req.json \
  --output-file preview.arrow

if grep -q '"Error":"' preview.arrow; then
  echo "✗ Preview failed (embedded source error):"
  python3 -c "import re,sys; raw=open(sys.argv[1],'rb').read().decode('utf-8','replace'); m=re.search(r'\\{\"Error\":\"[^\"]+\"\\}', raw); print(m.group(0) if m else '(marker present, JSON not parsed)')" preview.arrow
  exit 1
fi
echo "✓ Preview OK — $(wc -c < preview.arrow) bytes captured."

Bootstrap (new credentialed dataflow): bind connections via a minimal updateDefinition save before previewing credentialed M — mashup-preview.md § Bootstrap branch.

Update Definition

# Always send all 3 parts — full replacement
az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/updateDefinition?updateMetadata=true" \
  --body @definition.json

Delete Dataflow

az rest --method delete \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID"

Trigger Refresh (Simple)

# jobType MUST be "Refresh" for dataflows. "Pipeline" returns 400 InvalidJobType.
az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/jobs/instances?jobType=Refresh" \
  --headers "Content-Length=0"

Trigger Refresh (With Parameters)

az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/jobs/instances?jobType=Refresh" \
  --body '{
    "executionData": {"executeOption": "ApplyChangesIfNeeded"},
    "parameters": [
      {"name":"ServerName","value":"prod.database.windows.net","type":"Automatic"},
      {"name":"StartDate","value":"2025-01-01","type":"Automatic"}
    ]
  }'

Rename Dataflow (Properties Only)

az rest --method patch \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID" \
  --body '{"displayName":"RenamedDataflow","description":"Updated description"}'

Connection Discovery and Validation

Critical: Always validate all connection IDs before triggering refresh. Nonexistent connections cause cryptic refresh failures.

List All Connections

# List all accessible connections
az rest --method get \
  --resource "$RESOURCE" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value[].{id:id, name:displayName, type:connectionDetails.type}" -o json

Find Connection by Name

# Find a connection by display name (may return multiple results)
CONN_NAME="MyDatabase"
az rest --method get \
  --resource "$RESOURCE" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value[?displayName=='$CONN_NAME'].{id:id, type:connectionDetails.type}" -o json

Find Connection by Type

# Find all SQL Server connections
az rest --method get \
  --resource "$RESOURCE" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value[?connectionDetails.type=='SQL'].{id:id, name:displayName}" -o json

# Find all Azure Blob Storage connections
az rest --method get \
  --resource "$RESOURCE" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value[?connectionDetails.type=='AzureBlobs'].id" -o tsv

Validate a Specific Connection Exists

CONN_ID="550e8400-e29b-41d4-a716-446655440000"

# List all connections and filter by ID
RESULT=$(az rest --method get \
  --resource "$RESOURCE" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value[?id=='$CONN_ID'] | [0] | {id:id, name:displayName, type:connectionDetails.type}" \
  -o json)

if [ "$RESULT" == "null" ] || [ -z "$RESULT" ]; then
  echo "❌ Connection not found: $CONN_ID"
  exit 1
else
  echo "✅ Connection found:"
  echo "$RESULT" | jq '.'
fi

Extract Connection IDs from Dataflow Definition

⚠ LRO caveat: the az rest --method post .../getDefinition call below is a happy-path one-liner that only works when the response is synchronous (200). For large definitions or under load the API returns 202 + Location; this snippet will then yield an empty/operation-status payload and jq will fail silently. Use the LRO-aware curl pattern in Validate All Connections in a Dataflow below, or copy the Fabric LRO Polling Pattern bash branch into your script before relying on this in production.

# Get definition, extract queryMetadata, then list all referenced connections
RESULT=$(az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  --headers "Content-Length=0")

QUERY_META=$(echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)

# List all connection references
echo "$QUERY_META" | jq '.connections[] | {path:.path, connectionId:.connectionId}'

Validate All Connections in a Dataflow (Pre-Refresh Check)

#!/bin/bash
# Validate all connections referenced in a dataflow before refresh

WS_ID="<id>"
DF_ID="<id>"
RESOURCE="https://api.fabric.microsoft.com"

# getDefinition can return 200 (sync) or 202 + Location (LRO) — handle both.
TOKEN=$(az account get-access-token --resource "$RESOURCE" --query accessToken -o tsv)
GET_DEF_BODY=$(mktemp); GET_DEF_HDR=$(mktemp)
HTTP_CODE=$(curl -sS -X POST \
  -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  -D "$GET_DEF_HDR" -o "$GET_DEF_BODY" -w "%{http_code}")
if [ "$HTTP_CODE" = "202" ]; then
  # Fabric LRO: poll operation state, then GET /result.
  # See authoring-script-templates.md § Fabric LRO Polling Pattern for the contract.
  LOCATION=$(tr -d '\r' < "$GET_DEF_HDR" | grep -i "^location:" | awk '{print $2}')
  RETRY=$(tr -d '\r' < "$GET_DEF_HDR" | grep -i "^retry-after:" | awk '{print $2}'); RETRY=${RETRY:-5}
  while :; do
    sleep "$RETRY"
    OP=$(az rest --method get --resource "$RESOURCE" --url "$LOCATION")
    case "$(echo "$OP" | jq -r '.status // empty')" in
      Succeeded) RESULT=$(az rest --method get --resource "$RESOURCE" --url "${LOCATION%/}/result"); break ;;
      Failed|Cancelled) echo "ERROR: getDefinition $(echo "$OP" | jq -r '.status')" >&2; exit 1 ;;
    esac
  done
else
  RESULT=$(cat "$GET_DEF_BODY")
fi
rm -f "$GET_DEF_BODY" "$GET_DEF_HDR"

QUERY_META=$(echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d)

# List all connections once for efficiency
ALL_CONNECTIONS=$(az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/connections" \
  --query "value" -o json)

MISSING=0
echo "Validating connections..."
# queryMetadata.json connections[].connectionId is a STRINGIFIED COMPOSITE
# of shape {"ClusterId":"…","DatasourceId":"…"}. The plain-GUID `id` returned
# by GET /v1/connections matches the DatasourceId field. Parse before comparing.
# Use process substitution so MISSING updates persist (a piped `... | while` runs in a subshell).
while IFS= read -r row; do
  RAW_CONN_ID=$(echo "$row" | jq -r '.connectionId')
  CONN_PATH=$(echo "$row" | jq -r '.path')

  DATASOURCE_ID=$(echo "$RAW_CONN_ID" | jq -r '.DatasourceId? // empty' 2>/dev/null)
  [ -z "$DATASOURCE_ID" ] && DATASOURCE_ID="$RAW_CONN_ID"

  CONN_NAME=$(echo "$ALL_CONNECTIONS" | jq -r ".[] | select(.id==\"$DATASOURCE_ID\") | .displayName" 2>/dev/null || echo "")

  if [ -z "$CONN_NAME" ]; then
    echo "❌ $CONN_PATH: $DATASOURCE_ID NOT FOUND"
    MISSING=$((MISSING + 1))
  else
    echo "✅ $CONN_PATH: $DATASOURCE_ID ($CONN_NAME)"
  fi
done < <(echo "$QUERY_META" | jq -c '.connections[]')

if [ $MISSING -gt 0 ]; then
  echo "⚠️  Validation failed: $MISSING missing connection(s)"
  exit 1
else
  echo "✅ All connections validated"
fi

Base64 Encoding Helpers

Bash

# Encode file to base64 (no line wrapping)
base64 -w0 < mashup.pq

# Decode base64 payload to file
echo "<base64string>" | base64 -d > mashup.pq

# Extract and decode a specific part from getDefinition response
echo "$RESPONSE" | jq -r '.definition.parts[] | select(.path=="mashup.pq") | .payload' | base64 -d

PowerShell

# Encode file to base64
[Convert]::ToBase64String([System.IO.File]::ReadAllBytes("mashup.pq"))

# Decode base64 to file
[System.IO.File]::WriteAllBytes("mashup.pq", [Convert]::FromBase64String($base64String))

# Encode string content (UTF-8, no BOM)
[Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($content))

Definition Manipulation Patterns

Read-Modify-Write Workflow

⚠ LRO caveat: the one-liner getDefinition below assumes a synchronous 200 response. Under load or for large definitions the API returns 202 + Location, which this snippet does not handle — it will silently fail and decode garbage. For production, use the LRO-aware curl pattern in Validate All Connections in a Dataflow above, or copy the Fabric LRO Polling Pattern bash branch into your script.

# 1. Get current definition (happy path; 200 only)
RESULT=$(az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition")

# 2. Extract and decode each part
echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 -d > queryMetadata.json
echo "$RESULT" | jq -r '.definition.parts[] | select(.path=="mashup.pq") | .payload' | base64 -d > mashup.pq
echo "$RESULT" | jq -r '.definition.parts[] | select(.path==".platform") | .payload' | base64 -d > .platform

# 3. Edit files as needed (e.g., modify mashup.pq)

# 4. Re-encode and build update payload
QM_B64=$(base64 -w0 < queryMetadata.json)
MASHUP_B64=$(base64 -w0 < mashup.pq)
PLATFORM_B64=$(base64 -w0 < .platform)

jq -n \
  --arg qm "$QM_B64" --arg mash "$MASHUP_B64" --arg plat "$PLATFORM_B64" \
  '{definition:{parts:[
    {path:"queryMetadata.json",payload:$qm,payloadType:"InlineBase64"},
    {path:"mashup.pq",payload:$mash,payloadType:"InlineBase64"},
    {path:".platform",payload:$plat,payloadType:"InlineBase64"}
  ]}}' > definition.json

# 5. Update
az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/updateDefinition?updateMetadata=true" \
  --body @definition.json

LRO Polling Helper

# Poll operation until terminal status. Accepts both enum sets:
#   - LRO operation status (getDefinition/updateDefinition/async create): Succeeded
#   - Job instance status (refresh jobs): Completed
# Both have the same terminal Failed / Cancelled values.
poll_operation() {
  local url="$1"
  while true; do
    STATUS=$(az rest --method get --resource "$RESOURCE" --url "$url" --query "status" --output tsv)
    echo "Status: $STATUS"
    case "$STATUS" in
      Succeeded|Completed|Failed|Cancelled) break ;;
      *) sleep 10 ;;
    esac
  done
  echo "Final status: $STATUS"
}

Status Enum Reference — LRO Operation vs Job Instance

Fabric returns two different status enums depending on which endpoint you polled. Conflating them produces infinite polling loops or silently treats a still-running operation as terminal.

Rule of thumb: if the URL the agent is polling came from …/jobs/instances/…, the terminal-success value is Completed; otherwise it is Succeeded. Failed and Cancelled are valid terminal failures in both enums.

Connection Creation Quick Patterns

For the full decision tree, schemas per credential type, and pitfalls, see connection-management.md.

List supported connection types (always do this before POST /v1/connections):

az rest --method get --resource "$RESOURCE" \
  --url "$API/connections/supportedConnectionTypes" \
  --query "value[?type=='SQL']"

Create cloud SQL connection (Basic auth, Key Vault-backed password):

az rest --method post --resource "$RESOURCE" --url "$API/connections" --body '{
  "connectivityType": "ShareableCloud",
  "displayName": "ContosoSqlConnection",
  "connectionDetails": {
    "type": "SQL", "creationMethod": "SQL",
    "parameters": [
      {"dataType":"Text","name":"server","value":"contoso.database.windows.net"},
      {"dataType":"Text","name":"database","value":"sales"}
    ]
  },
  "privacyLevel": "Organizational",
  "credentialDetails": {
    "singleSignOnType": "None",
    "connectionEncryption": "Encrypted",
    "skipTestConnection": false,
    "credentials": {
      "credentialType": "Basic",
      "username": "admin",
      "passwordReference": {"connectionId":"<kvConnId>","secretName":"sql-pwd"}
    }
  }
}'

Create cloud Lakehouse connection (Workspace identity):

az rest --method post --resource "$RESOURCE" --url "$API/connections" --body '{
  "connectivityType": "ShareableCloud",
  "displayName": "ContosoLakehouseConnection",
  "connectionDetails": {
    "type": "Lakehouse", "creationMethod": "Lakehouse",
    "parameters": [
      {"dataType":"Text","name":"workspaceId","value":"<wsId>"},
      {"dataType":"Text","name":"lakehouseId","value":"<lhId>"}
    ]
  },
  "privacyLevel": "Organizational",
  "credentialDetails": {
    "singleSignOnType": "None",
    "connectionEncryption": "Encrypted",
    "skipTestConnection": false,
    "credentials": {"credentialType":"WorkspaceIdentity"}
  }
}'

Capture the new connection's plain-GUID `id` directly from the response:

NEW_CONN_ID=$(az rest --method post --resource "$RESOURCE" \
  --url "$API/connections" --body @body.json \
  --query "id" --output tsv)

Connector parameters and supported credentials vary by tenant, gateway, and over time. Treat the snippets above as illustrative; supportedConnectionTypes is authoritative.

Connection Binding Quick Patterns

Get current definition (handles both 200 sync and 202 LRO):

# az rest cannot return response headers; use curl with an az-acquired token to capture Location.
TOKEN=$(az account get-access-token --resource "$RESOURCE" --query accessToken -o tsv)
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  -D "$HDR" -o "$BODY" -w "%{http_code}")
if [ "$CODE" = "202" ]; then
  # Fabric LRO: poll operation state, then GET /result.
  # See authoring-script-templates.md § Fabric LRO Polling Pattern for the contract.
  LOCATION=$(tr -d '\r' < "$HDR" | grep -i "^location:" | awk '{print $2}')
  RETRY=$(tr -d '\r' < "$HDR" | grep -i "^retry-after:" | awk '{print $2}'); RETRY=${RETRY:-5}
  while :; do
    sleep "$RETRY"
    OP=$(az rest --method get --resource "$RESOURCE" --url "$LOCATION")
    case "$(echo "$OP" | jq -r '.status // empty')" in
      Succeeded) DEF=$(az rest --method get --resource "$RESOURCE" --url "${LOCATION%/}/result"); break ;;
      Failed|Cancelled) echo "ERROR: getDefinition $(echo "$OP" | jq -r '.status')" >&2; exit 1 ;;
    esac
  done
else
  DEF=$(cat "$BODY")
fi
rm -f "$HDR" "$BODY"

Get ClusterId for a connection:

The endpoint lives on the Power BI control plane (not on api.fabric.microsoft.com) and the field is clusterId (camelCase). Pass --resource "https://analysis.windows.net/powerbi/api" to get the right token audience.

CONN_ID="<connectionId>"
PBI_RESOURCE="https://analysis.windows.net/powerbi/api"

# Use the LIST endpoint and filter by `id`. The per-id route
# (.../gatewayClusterDatasources/$CONN_ID) returns PowerBIEntityNotFound for
# cloud connections; list+filter is the supported pattern. Newly-created
# connections may take a few seconds to surface here — retry if empty.
CLUSTER_ID=$(az rest --method get \
  --resource "$PBI_RESOURCE" \
  --url "https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources" \
  --query "value[?id=='$CONN_ID'] | [0].clusterId" --output tsv)

Extract queryMetadata.json from definition:

echo "$DEF" | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' | base64 --decode > queryMetadata.json

Add connection to queryMetadata.json (with ClusterId):

jq '.connections += [{
  "connectionId": "{\"ClusterId\": \"'$CLUSTER_ID'\", \"DatasourceId\": \"'$CONN_ID'\"}",
  "kind": "Sql",
  "path": "[dbo]"
}]' queryMetadata.json > queryMetadata_updated.json

Update dataflow with all 3 parts (full read-modify-write):

# Encode all parts
QM_B64=$(base64 -w0 < queryMetadata_updated.json)
MASHUP_B64=$(echo "$DEF" | jq -r '.definition.parts[] | select(.path=="mashup.pq") | .payload')
PLATFORM_B64=$(echo "$DEF" | jq -r '.definition.parts[] | select(.path==".platform") | .payload')

# Build the body in a temp file and pass via --body "@<path>".
# DO NOT use inline --body "{...}" with embedded base64 — cmd.exe argument parsing
# on Windows mangles the quotes and corrupts the payload. See SKILL.md MUST DO.
UPDATE_BODY=$(mktemp --suffix=.json 2>/dev/null || mktemp)
jq -n --arg qm "$QM_B64" --arg mp "$MASHUP_B64" --arg pf "$PLATFORM_B64" '{
  definition: { parts: [
    { path: "queryMetadata.json", payload: $qm, payloadType: "InlineBase64" },
    { path: "mashup.pq",          payload: $mp, payloadType: "InlineBase64" },
    { path: ".platform",          payload: $pf, payloadType: "InlineBase64" }
  ]}}' > "$UPDATE_BODY"

az rest --method post --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/updateDefinition" \
  --headers "Content-Type=application/json" \
  --body "@$UPDATE_BODY"
rm -f "$UPDATE_BODY"

See authoring-script-templates.md § Connection Binding Templates for complete end-to-end examples.

Agent Integration Notes

• GitHub Copilot CLI: Generate az rest one-liners for dataflow CRUD or complete .sh scripts. Always include --resource "https://api.fabric.microsoft.com" in output. Remind user about base64 encoding for definition parts.
• Claude Code / Cowork: Run az rest commands via bash tool directly. For definition manipulation: write files first, then encode and send. Always verify az login before first use. After updates: get definition again to confirm.
• Common agent pattern:

1. Discover workspace ID + dataflow ID 2. Get current definition (decode all 3 parts) 3. Formulate changes to M code or metadata 4. Re-encode all 3 parts and update definition 5. Optionally trigger refresh and poll for completion

Connection Management via CLI

Operational guide for the full Fabric data source connection lifecycle — discover, create, list, find, inspect, test, and bind to dataflows — using az rest against the Fabric REST API. Pairs with the existing bind connection to dataflow workflows under authoring-cli-quickref.md § Connection Binding Quick Patterns and connection discovery/validation under authoring-cli-quickref.md § Connection Discovery and Validation.

Scope. This reference covers GET /v1/connections, GET /v1/connections/{id}, GET /v1/connections/supportedConnectionTypes, POST /v1/connections, POST /v1/connections/{id}/testConnection, and the discover → inspect → create → bind → test → refresh flow needed when a Dataflow Gen2 references a connection that does not yet exist. Sharing, role assignments, PATCH, and DELETE of connections are related lifecycle tasks but are out of scope here.

>

API-only. Every step uses az rest against the Fabric REST API. Portal/UI-driven connection creation (Fabric portal "New connection" dialog, OAuth2 browser consent flows) is out of scope — this reference targets headless, scriptable, CI-friendly automation.

Authoritative API spec. All schemas, field names, defaults, and credential shapes in this document are taken from Microsoft Learn:

- List Connections

- Get Connection

- List Supported Connection Types

- Create Connection

- Test Connection

Table of Contents

Decision Tree

Need a connection for a dataflow?
│
├─► (1) GET /v1/connections — list and filter by displayName / path / connectionDetails.type
│       │
│       ├─ FOUND → (1a) GET /v1/connections/{id}
│       │           Inspect connectivityType, connectionDetails.type, credentialDetails, gatewayId.
│       │           │
│       │           ├─ Matches required source + network path → reuse. Take `id`, skip to (4).
│       │           └─ Wrong type / wrong network path / wrong credentials → create a new one.
│       │
│       └─ NOT FOUND → continue.
│
├─► (2) GET /v1/connections/supportedConnectionTypes
│       (filter to the type you need; capture parameter names + supported credential types)
│
├─► (3) POST /v1/connections
│       (use a `connectivityType` matching the network path: ShareableCloud / OnPremisesGateway / VirtualNetworkGateway)
│       Capture `id` from the 201 response.
│
├─► (3b) POST /v1/connections/{id}/testConnection   (optional)
│       LRO. Sanity-check credentials/network before binding. Skip for connection types where
│       `skipTestConnection` was used at create time or `supportsSkipTestConnection: false`.
│
├─► (4) Bind it to the dataflow definition (`queryMetadata.json connections[]`) + `updateDefinition`,
│
└─► (5) Verify `connections[]` survived the save and trigger refresh.

Why list-types-before-create? Each connection type (SQL, AzureBlobs, Lakehouse, Warehouse, Web, Dataverse, …) has a different set of required parameters and supports a different set of credential types. The same connection type may also expose multiple creationMethod variants (e.g., several for Web). Step 2 below covers the exact endpoint.

Required Permissions

Delegated scopes (or service-principal equivalents):

Additional caller requirements:

• Service principals creating connections require Fabric tenant admin enablement: see *Service principals can create workspaces, connections, and deployment pipelines*.
• Gateway connections require the caller to have permission on the target gateway.

For az login recipes and token audience rules, see COMMON-CLI.md § Authentication Recipes and COMMON-CORE.md § Authentication & Token Acquisition.

Concept Model

A Fabric connection is a credential-bearing object that data sources reference for authenticated access. Every connection is defined along the following dimensions — getting any of them wrong is the most common cause of InvalidInput on POST:

A cloud connection identity has two IDs: the plain Fabric GUID (.id from the POST/GET response) and a Power BI gateway ClusterId. They're embedded differently depending on context — see Connection ID Format Cheat Sheet.

Step 1 — List Supported Connection Types

GET /v1/connections/supportedConnectionTypes returns, per type:

• creationMethods[].parameters[] — the names + dataTypes + required flag for connectionDetails.parameters
• supportedCredentialTypes[] — which credentialDetails.credentials.credentialType values are valid
• supportedConnectionEncryptionTypes[] — which connectionEncryption values are valid
• supportsSkipTestConnection
• supportedCredentialTypesForUsageInUserControlledCode — subset usable from notebooks (allowUsageInUserControlledCode)

Cloud (no gateway):

RESOURCE="https://api.fabric.microsoft.com"
API="https://api.fabric.microsoft.com/v1"

az rest --method get \
  --resource "$RESOURCE" \
  --url "$API/connections/supportedConnectionTypes" \
  --query "value[?type=='SQL']"

For a specific gateway (cloud + on-prem types may differ):

GW_ID="<gatewayId>"
az rest --method get \
  --resource "$RESOURCE" \
  --url "$API/connections/supportedConnectionTypes?gatewayId=$GW_ID&showAllCreationMethods=true"

Pagination: response includes continuationToken + continuationUri; reuse the continuationToken query parameter to fetch the next page. Page until continuationToken is null — stopping early hides connector types that may only appear on later pages.

`showAllCreationMethods=false` (default) returns only the recommended creation methods for each type. Set to true if you need a less-common method.

⚠ Send `dataType` correctly per parameter. Each parameter's dataType (Text, Boolean, Number, Date, DateTime, DateTimeZone, Duration, …) is part of the connector's contract. The Fabric POST /v1/connections endpoint is lenient about type coercion at create time — most connectors accept dataType: "Text" for a numeric / boolean param and the create returns 201. The failures surface later: at refresh / executeQuery time the engine binds parameters by declared type, and a string passed where a Boolean / Number was expected produces silent NULL substitution or EntityUserFailure. Read creationMethods[].parameters[].dataType from this Step 1 response and pass it back exactly — that is the only forward-compatible behavior.

Casing matters. REST connectionDetails.type is SQL. The corresponding M connector kind in queryMetadata.json connections[].kind is `Sql` (camel case). Do not assume 1-to-1 casing between the REST type and the dataflow definition.

Step 2 — Create Connection (Cloud)

POST /v1/connections — body shape depends on the value of connectivityType:

Cloud body — minimum fields

{
  "connectivityType": "ShareableCloud",
  "displayName": "ContosoSqlConnection",
  "connectionDetails": {
    "type": "SQL",
    "creationMethod": "SQL",
    "parameters": [
      { "dataType": "Text", "name": "server",   "value": "contoso.database.windows.net" },
      { "dataType": "Text", "name": "database", "value": "sales" }
    ]
  },
  "privacyLevel": "Organizational",
  "credentialDetails": {
    "singleSignOnType": "None",
    "connectionEncryption": "Encrypted",
    "skipTestConnection": false,
    "credentials": {
      "credentialType": "Basic",
      "username": "admin",
      "passwordReference": {
        "connectionId": "<keyVaultConnectionId>",
        "secretName": "sql-admin-password"
      }
    }
  }
}

Important defaults and optional fields:

Bash — create cloud SQL connection (Basic auth via Key Vault reference)

#!/usr/bin/env bash
set -euo pipefail

RESOURCE="https://api.fabric.microsoft.com"
API="https://api.fabric.microsoft.com/v1"

DISPLAY_NAME="${DISPLAY_NAME:?Set DISPLAY_NAME}"
SQL_SERVER="${SQL_SERVER:?Set SQL_SERVER}"
SQL_DATABASE="${SQL_DATABASE:?Set SQL_DATABASE}"
SQL_USER="${SQL_USER:?Set SQL_USER}"
KV_CONN_ID="${KV_CONN_ID:?Set KV_CONN_ID — Fabric Key Vault connection ID}"
KV_SECRET_NAME="${KV_SECRET_NAME:?Set KV_SECRET_NAME}"

BODY=$(jq -n \
  --arg name "$DISPLAY_NAME" \
  --arg server "$SQL_SERVER" --arg db "$SQL_DATABASE" \
  --arg user "$SQL_USER" \
  --arg kvConn "$KV_CONN_ID" --arg secret "$KV_SECRET_NAME" \
  '{
    connectivityType: "ShareableCloud",
    displayName: $name,
    connectionDetails: {
      type: "SQL",
      creationMethod: "SQL",
      parameters: [
        { dataType: "Text", name: "server",   value: $server },
        { dataType: "Text", name: "database", value: $db }
      ]
    },
    privacyLevel: "Organizational",
    credentialDetails: {
      singleSignOnType: "None",
      connectionEncryption: "Encrypted",
      skipTestConnection: false,
      credentials: {
        credentialType: "Basic",
        username: $user,
        passwordReference: { connectionId: $kvConn, secretName: $secret }
      }
    }
  }')

az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/connections" \
  --body "$BODY" \
  --query "{id:id, name:displayName, type:connectionDetails.type, path:connectionDetails.path}"

Plaintext fallback. Replace passwordReference with "password": "$SQL_PASSWORD" only for local testing. Never commit plaintext credentials. Inject via read -s or env from a secret store.

⚠ Unknown credential keys are silently dropped. Fabric does not return 400 for unknown keys inside credentialDetails.credentials — it discards them and proceeds with the recognized fields that remain. A typo like passWord instead of password produces a 201 Created with no usable credential, and the first refresh then fails with a generic IncorrectCredentials. Validate your request body's credential keys against the discovered schema from Step 1 before POSTing.

Caching guidance. supportedConnectionTypes is stable per tenant — cache aggressively (24h+). gatewayClusterDatasources updates whenever a connection is created — cache ~5 min per process. The /v1/connections list changes whenever anyone with overlapping visibility creates a connection — cache short (<1m) or not at all if precision matters.

Credential Type Schemas

The full union accepted by `CreateCredentialDetails.credentials` for ShareableCloud and VirtualNetworkGateway connections. Always check the type's supportedCredentialTypes from Step 1 first — not every type accepts every credential.

OAuth2 is not creatable via `POST /v1/connections`. It can appear in supportedCredentialTypes for some connector types, but the Create Connection schema does not define an OAuth2 credentials body. Live-verified — the API rejects any OAuth2 payload with:

>

```jsonc

400 InvalidInput

{

"errorCode": "InvalidInput",

"message": "The request has an invalid input",

"moreDetails": [{

"errorCode": "InvalidParameter",

"message": "The CredentialType input is not supported for this API"

}],

"isRetriable": false

}

```

>

Use an existing OAuth2-backed connection that was authored interactively in the portal/UI, or pick a different credential type from the connector's supportedCredentialTypes.

Key Vault reference shape (KeyVaultSecretReference)

{
  "connectionId": "<keyVaultConnectionIdAsUuid>",  // not a Key Vault URL — a Fabric KV connection ID
  "secretName": "<secret-name-in-vault>",
  "version": "<optional secret version>"
}

Chicken-and-egg. A Key Vault reference points at another Fabric connection (a Key Vault connection) that already exists. If you do not have one, you must either (a) create the Key Vault connection first via this same API or (b) use the plaintext field for one-off / dev usage.

Service principal example

"credentials": {
  "credentialType": "ServicePrincipal",
  "tenantId": "<tenantId>",
  "servicePrincipalClientId": "<appId>",
  "servicePrincipalSecretReference": {
    "connectionId": "<kvConnectionId>",
    "secretName": "sp-client-secret"
  }
}

Workspace identity example (Lakehouse / Warehouse / Eventhouse common case)

"credentials": { "credentialType": "WorkspaceIdentity" }

KeyPair example (Snowflake-style)

"credentials": {
  "credentialType": "KeyPair",
  "identifier": "admin",
  "privateKey": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n-----END ENCRYPTED PRIVATE KEY-----",
  "passphrase": "<passphrase>"
}

Connection Type Examples

Examples only — always confirm via `supportedConnectionTypes`. Connector parameters and supported credential types vary by tenant, gateway, and over time. Casing of connectionDetails.type may also differ from the M kind recorded in queryMetadata.json.

For Fabric-source connections (Lakehouse, Warehouse, Eventhouse) the workspace ID is mandatory because artifact names are scoped to the parent workspace and would otherwise be ambiguous.

Step 3 — Verify and Get the Connection ID

The POST /v1/connections 201 Created response includes the new connection's id. Capture it directly:

NEW_CONN_ID=$(az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/connections" \
  --body @body.json \
  --query "id" --output tsv)

echo "Created connection: $NEW_CONN_ID"

Inspect an existing connection — GET /v1/connections/{id}

When the caller already knows the connection's GUID (e.g., captured at create time, copied from another script, or extracted from queryMetadata.json connections[].connectionId's DatasourceId), prefer GET /v1/connections/{id} over GET /v1/connections + filter. It is a direct lookup, returns the full connectivityType / connectionDetails / credentialDetails shape, and surfaces EntityNotFound clearly:

az rest --method get \
  --resource "$RESOURCE" \
  --url "$API/connections/$NEW_CONN_ID" \
  --query "{id:id, name:displayName, connectivity:connectivityType, type:connectionDetails.type, path:connectionDetails.path, credType:credentialDetails.credentialType, gw:gatewayId}"

Use this to:

• Confirm the connection still exists and is reachable for this caller (403/404 ⇒ no access or deleted).
• Check connectivityType before binding — prefer ShareableCloud / PersonalCloud for cloud-reachable sources over OnPremisesGateway / VirtualNetworkGateway to avoid an unnecessary gateway-online failure surface (see Picking between PersonalCloud and OnPremisesGateway when both exist).
• Verify credentialDetails.credentialType matches the M kind recorded in queryMetadata.json.

Discover by name — GET /v1/connections + filter

When the GUID is unknown (e.g., from another shell, or after POST without capturing the response), list-and-filter via the standard recipe in authoring-cli-quickref.md § Connection Discovery and Validation:

az rest --method get \
  --resource "$RESOURCE" \
  --url "$API/connections" \
  --query "value[?displayName=='ContosoSqlConnection'].id" --output tsv

Per-caller visibility. GET /v1/connections only returns connections the caller has at least read permission on; an empty result is not proof the connection is absent from the tenant. Request access from the connection owner if expected results are missing.

Step 3b — Test the Connection (optional)

After create (or before binding a connection whose credentials may have rotated), call POST /v1/connections/{id}/testConnection as a pre-bind sanity check. This catches IncorrectCredentials, offline gateways, and network-path issues before they surface as a generic EntityUserFailure on the next refresh.

This endpoint is LRO — a successful test may return either 200 OK (synchronous, with ConnectionStatusResponse) or 202 Accepted with the following headers (live-verified):

Body of the 202 is null; all state lives on the LRO resource at Location. Final ConnectionStatusResponse payload is at ${Location}/result once status is terminal.

# az rest cannot capture response headers — use curl for the initial call so we can read Location.
TOKEN=$(az account get-access-token --resource "$RESOURCE" --query accessToken -o tsv)
HDR=$(mktemp); BODY=$(mktemp)
CODE=$(curl -sS -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "$API/connections/$NEW_CONN_ID/testConnection" \
  -D "$HDR" -o "$BODY" -w "%{http_code}")

if [ "$CODE" = "200" ]; then
  jq '.' "$BODY"
elif [ "$CODE" = "202" ]; then
  LOC=$(tr -d '\r' < "$HDR" | grep -i "^location:" | awk '{print $2}')
  RETRY=$(tr -d '\r' < "$HDR" | grep -i "^retry-after:" | awk '{print $2}'); RETRY=${RETRY:-5}
  # Poll the LRO until terminal status, then GET /result for the ConnectionStatusResponse payload.
  # Full polling helper: authoring-cli-quickref.md § LRO Polling Helper.
  while :; do
    sleep "$RETRY"
    OP=$(az rest --method get --resource "$RESOURCE" --url "$LOC")
    case "$(echo "$OP" | jq -r '.status // empty')" in
      Succeeded) az rest --method get --resource "$RESOURCE" --url "${LOC%/}/result"; break ;;
      Failed|Cancelled) echo "ERROR: testConnection $(echo "$OP" | jq -r '.status')" >&2; exit 1 ;;
    esac
  done
else
  echo "ERROR: testConnection HTTP $CODE" >&2; cat "$BODY" >&2; exit 1
fi
rm -f "$HDR" "$BODY"

Skip Step 3b when:

• The connection was created with skipTestConnection: true and the connector advertises supportsSkipTestConnection: false for the chosen credential type (call will return 400).
• The source is rate-limited and a probe call is unsafe.
• The agent records an explicit skip reason (e.g., test traffic billed per call).

Common failures: IncorrectCredentials (rotate or re-create), EntityNotFound (connection deleted or no access), gateway-offline (the response will surface the gateway error).

Step 4 — Bind to Dataflow and Refresh

Once you have the connection's id GUID, bind it into queryMetadata.json and update the dataflow definition. The bind mechanics already live in this skill — do not reinvent:

• authoring-cli-quickref.md § Connection Binding Quick Patterns — az rest snippets to fetch ClusterId, edit queryMetadata.json, and updateDefinition.
• authoring-script-templates.md § Connection Binding Templates — full end-to-end Bash/PowerShell flow.

After updateDefinition, always verify that queryMetadata.json connections[] still contains your binding (see Operational Pitfalls below). Then trigger refresh and poll using the existing helpers.

Connection ID Format Cheat Sheet

Three different ID forms appear in this flow. Confusing them is the most common cause of "connection not found" at refresh.

Rule of thumb:

• REST `POST` / `GET` / `PATCH` / `DELETE` operations on `/v1/connections/...` want the plain GUID (the .id from any of the above responses).
• Dataflow definition `queryMetadata.json connections[].connectionId` wants the stringified composite {"ClusterId":"…","DatasourceId":"…"}.

Resolving ClusterId (Power BI v2)

The ClusterId value embedded in the composite comes from the Power BI v2 control-plane endpoint myorg/me/gatewayClusterDatasources. Use the list-and-filter pattern — list the user's gateway-cluster datasources, then filter the response by the connection's plain GUID (value[?id=='{connId}'].clusterId):

Don't use the per-id route. GET .../gatewayClusterDatasources/{datasourceId} returns PowerBIEntityNotFound for cloud connections (verified live against connectivityType: ShareableCloud + Web). Use list+filter only.

Token audience. The list endpoint accepts the Power BI audience (https://analysis.windows.net/powerbi/api — no trailing slash; the slashed form fails AADSTS500011). The Fabric token (https://api.fabric.microsoft.com/) is also accepted in current tenants — for scripts that already hold a Fabric token, no separate token acquisition is needed.

PBI_RESOURCE="https://analysis.windows.net/powerbi/api"
CONN_ID="<datasourceId>"

CLUSTER_ID=$(az rest --method get \
  --resource "$PBI_RESOURCE" \
  --url "https://api.powerbi.com/v2.0/myorg/me/gatewayClusterDatasources" \
  --query "value[?id=='$CONN_ID'] | [0].clusterId" --output tsv)

Empty result after retries. If the filter still returns empty after ~30s of retries, the connection is not visible to PBI v2 — that's a connection-lifecycle problem (e.g., orphaned record after a failed create), not a ClusterId-lookup problem. Verify the connection exists via GET /v1/connections/{id}; if Fabric shows it but PBI v2 doesn't, recreate the connection.

Picking between PersonalCloud and OnPremisesGateway when both exist

When multiple connections target the same source (e.g., a public OData feed registered both as connectivityType: PersonalCloud and connectivityType: OnPremisesGateway), prefer the PersonalCloud connection for cloud-only / publicly reachable sources:

If a cloud-reachable source happens to be registered against a gateway connection, picking that connection forces an unnecessary gateway hop and an unnecessary failure surface — the preview and refresh will fail in non-obvious ways if the gateway is cold or the cluster is unhealthy. Inspect each candidate connection with GET /v1/connections/{id} (see Step 3 — Inspect an existing connection) and check connectivityType before binding.

Operational Pitfalls

These are observed pitfalls, not formal API guarantees — verify each one in your environment before treating it as load-bearing.

Verify connections survived updateDefinition

updateDefinition is a full replacement: any 3-part payload that omits or scrubs queryMetadata.json connections[] will leave the dataflow with no bindings. If a workflow modifies mashup.pq and re-builds queryMetadata.json from a stale snapshot, bindings get silently dropped.

Mandatory step after every `updateDefinition`:

⚠ LRO caveat: the getDefinition call below assumes a synchronous 200 response. For production code, handle 202 + Location with the LRO-aware curl pattern (see authoring-cli-quickref.md § Validate All Connections in a Dataflow or authoring-script-templates.md § Bash — Read-Modify-Write Dataflow Definition).

# Re-fetch and confirm bindings survived (happy-path 200 only — see caveat)
RESULT=$(az rest --method post \
  --resource "$RESOURCE" \
  --url "$API/workspaces/$WS_ID/dataflows/$DF_ID/getDefinition" \
  --headers "Content-Length=0")

echo "$RESULT" \
  | jq -r '.definition.parts[] | select(.path=="queryMetadata.json") | .payload' \
  | base64 -d \
  | jq '.connections // []'

If connections[] is empty or missing the entry you expected, re-bind and updateDefinition again before triggering refresh.

Multi-source dataflows

If a dataflow reads from multiple distinct sources, each source needs its own connection bound. The M section also needs to opt in to combining queries:

[AllowCombine = true]
section Section1;

Without [AllowCombine = true], the engine refuses to fold queries that touch multiple sources and refresh fails with privacy-level errors.

For multiple Lakehouse reads in the same workspace, consolidate into one `Lakehouse.Contents([...])` call rather than calling it once per table — the engine treats each call as a separate source for combine-rule purposes.

Don't convert published single-source dataflows to multi-source in place

Editing a published single-source dataflow to add a second source frequently leaves the dataflow in an inconsistent binding state. Create a fresh dataflow that is multi-source from the start, then retire the old one.

Duplicate connection names

POST /v1/connections with a displayName that already exists returns 409 DuplicateConnectionName. Recovery:

# 1. List existing connections with the same name
az rest --method get --resource "$RESOURCE" --url "$API/connections" \
  --query "value[?displayName=='ContosoSqlConnection']"

# 2. Either reuse the existing id, or pick a unique name (e.g. add an environment suffix)
DISPLAY_NAME="ContosoSqlConnection-$(date +%Y%m%d-%H%M)"

Gateways (Appendix)

List gateways

az rest --method get \
  --resource "$RESOURCE" \
  --url "$API/gateways" \
  --query "value[].{id:id, name:displayName, type:type}"

VNet gateway connection

VNet gateway connections use the same CreateCredentialDetails schema as ShareableCloud, plus a top-level gatewayId:

{
  "connectivityType": "VirtualNetworkGateway",
  "gatewayId": "<vnetGatewayId>",
  "displayName": "ContosoVnetSqlConnection",
  "connectionDetails": { /* same as cloud */ },
  "privacyLevel": "Organizational",
  "credentialDetails": {
    "singleSignOnType": "None",
    "connectionEncryption": "Encrypted",
    "skipTestConnection": false,
    "credentials": { /* Basic / ServicePrincipal / WorkspaceIdentity / etc. */ }
  }
}

Creating the VNet gateway itself (POST /v1/gateways) requires a Fabric capacity, an Azure subscription, resource group, VNet, subnet, and subnet delegation — that's gateway provisioning, outside the dataflows authoring scope. Treat the VNet gateway as a prerequisite that exists before you author the connection.

On-premises gateway connection — different credential flow

OnPremisesGateway connections use CreateOnPremisesCredentialDetails, where credentials are not plaintext but RSA-encrypted with each gateway member's public key:

"credentialDetails": {
  "singleSignOnType": "None",
  "connectionEncryption": "NotEncrypted",
  "skipTestConnection": false,
  "credentials": {
    "credentialType": "Windows",
    "values": [
      { "gatewayId": "<gatewayMember1>", "encryptedCredentials": "<RSA-encrypted-blob>" },
      { "gatewayId": "<gatewayMember2>", "encryptedCredentials": "<RSA-encrypted-blob>" }
    ]
  }
}

Out of scope for ready-to-run templates. Generating encryptedCredentials requires fetching the gateway member's RSA public key and serializing the credential payload according to Microsoft's encryption format. See *Configure credentials programmatically* for the algorithm. Plaintext templates do not work for on-prem.

For Dataflow Gen2 use, prefer either ShareableCloud (when the source is reachable from the cloud) or VirtualNetworkGateway over on-prem when feasible.

Troubleshooting

Out of Scope

The following endpoints and flows are live-confirmed to exist at the API level, but their lifecycle, governance, and integration surfaces differ from the discover → create → bind path covered here:

• Update a connection (PATCH /v1/connections/{id}) — rotate credentials, change displayName, adjust privacyLevel. Endpoint exists (returns typed InvalidInput on bad payload, not 405).
• Delete a connection (DELETE /v1/connections/{id}) — destructive; not idempotent across active bindings. Endpoint exists (returns EntityNotFound on missing id, not 405).
• Connection sharing / role assignments — granting other principals access to use the connection.
• OAuth2 connection authoring — interactive only (portal / UI); the API rejects OAuth2 in POST /v1/connections with InvalidInput (see Troubleshooting).
• On-premises gateway credential encryption — see Microsoft's *Configure credentials programmatically* for the RSA encryption format.
• Creating gateways — VNet/on-prem gateway provisioning is an admin/network task, not a dataflows authoring task.

For dataflow → connection binding (where you already have a connection ID), see:

• authoring-cli-quickref.md § Connection Binding Quick Patterns
• authoring-script-templates.md § Connection Binding Templates

M Language Semantics for Dataflow Gen2 Authoring

The language-side companion to connectors.md (source connectors), output-destinations.md (destination M), and mashup-preview.md (live execution via executeQuery). Documents the M language pitfalls and semantics that bite during Dataflow Gen2 authoring — error wrapping, optional access, per-cell error propagation, scoping inside each, identifier escaping. Every claim below was reproduced live against a Fabric Dataflow Gen2 via the executeQuery Arrow contract.

Not in scope. Basic syntax (let / in, primitive types, function definition), source connectors (see connectors.md), output-destination annotations (see output-destinations.md), the executeQuery REST contract (see mashup-preview.md).

try and try ... otherwise

try EXPR always returns a record. Field set differs by outcome:

Verified shapes (try (1 + "a") returns the error form; try (40 + 2) returns the success form):

let
    r = try (1 + "a")
    // r[Error][Reason]  = "Expression.Error"
    // r[Error][Message] = "We cannot apply operator + to types Number and Text."
in
    r

try EXPR otherwise FALLBACK short-circuits to the fallback value directly — no record wrapper. Use it when you only want a safe value and do not need the error details.

let
    n = try Number.FromText("abc") otherwise 0
in
    n        // n = 0 (a number, not a record)

Per-cell errors in column transformations

Two functions, identical per-cell error behaviour:

Both produce error-valued cells, NOT nulls. Errored cells serialize as `null` in Arrow / preview output — that is what makes them look like silent data loss.

Probe against {"1", "abc", "3"} cast to Int64.Type:

let
    t       = #table(type table [A = text], {{"1"}, {"abc"}, {"3"}}),
    Conv    = Table.TransformColumnTypes(t, {{"A", Int64.Type}}),
    Row0    = try Conv{0}[A],      // [HasError = false, Value = 1]
    Row1    = try Conv{1}[A]       // [HasError = true,  Error[Message] = "We couldn't convert to Number."]
in
    {Row0, Row1}

Implications for downstream operators:

• Table.RowCount(Conv) works — returns 3. It does not read cell values.
• Conv{1}[A] raises the cell error — surfaces as in-band {"Error":"..."} via executeQuery.
• Table.SelectRows(Conv, each [A] > 0) raises on row 1 — the predicate reads [A].
• Aggregations that read the column (List.Sum(Conv[A])) raise on first errored cell.

Recovery patterns:

each scoping

each EXPR is sugar for (_) => EXPR. What _ means depends on the calling context.

[Col] is shorthand for _[Col] in every context (per the M spec), so the two forms are always equivalent — Microsoft's own Table.Group example uses the shorthand (each List.Sum([price])). The thing that bites is the type, not the syntax: in the row contexts above [Col]/_[Col] is a scalar cell, but inside Table.Group it is the column as a list. For a single cell, index the sub-table first: _{0}[Col]. (Item access {N} has no implicit-_ shorthand — only field access [F] does — so write _{0}[Col], not {0}[Col] which parses as a list literal.)

Verified sub-table context (Table.Group) — the case that bites:

let
    t = #table(type table [G = text, V = Int64.Type],
               {{"a", 1}, {"a", 2}, {"a", 3}, {"b", 10}, {"b", 20}}),
    G = Table.Group(t, {"G"}, {
        {"RowCount", each Table.RowCount(_),     Int64.Type},
        {"SumV",     each List.Sum(_[V]),         Int64.Type},
        {"FirstV",   each _{0}[V],                Int64.Type}
    })
    // Row "a": RowCount=3, SumV=6,  FirstV=1
    // Row "b": RowCount=2, SumV=30, FirstV=10
in
    G

Optional vs required field access

let
    r           = [a = 1, b = 2],
    safe1       = r[c]?,                              // null
    safe2       = Record.FieldOrDefault(r, "c", -1),  // -1
    raisesError = r[c]                                // error
in
    {safe1, safe2}

Use [?] when scanning records that may have absent keys (common when extracting JSON or navigating connector nav tables — see connectors.md § Lakehouse navigation). Use Record.FieldOrDefault when you want a non-null fallback.

Quoted identifiers

Use #"..." to wrap any identifier that contains whitespace, punctuation, or collides with a reserved keyword (type, section, error, let, if, etc.).

let
    r = [#"weird name" = 1, #"type" = "x", #"section" = "y", normal = 2],
    a = r[#"weird name"],
    b = r[#"type"]
in
    {a, b}    // {1, "x"}

Constructing errors

// 1. Shorthand: text only -> Reason auto-set to "Expression.Error"
let r = try (error "boom!") in r
// r = [HasError = true,
//      Error    = [Reason = "Expression.Error", Message = "boom!", Detail = null]]

// 2. Record form: full control
let r = try (error [Reason  = "MyReason",
                    Message = "MyMessage",
                    Detail  = [extra = "data", n = 99]]) in r
// r[Error][Reason]  = "MyReason"
// r[Error][Message] = "MyMessage"
// r[Error][Detail][extra] = "data"
// r[Error][Detail][n]     = 99

Use the record form when downstream code (try ... otherwise chains, Table.ReplaceErrorValues) needs to discriminate on Reason or read structured detail.

File.Contents — exposed but unusable

File.Contents is registered in #shared, but invoking it raises:

Credentials are required to connect to the File source. (Source at <path>.)

There is no File credential type for Fabric Dataflow Gen2 cloud refresh. Use a Lakehouse, Warehouse, OData feed, or Web.Contents instead — see connectors.md § Function inventory. For runtime-disabled Web.Page / Web.BrowserContents, see connectors.md § Runtime-disabled functions.

MUST / PREFER / AVOID

MUST

1. Wrap any expression that may produce a cell error with try before reading the value (Conv{i}[Col], List.Sum(Conv[Col]), predicates that reference [Col]). 2. Use [?] or Record.FieldOrDefault for any optional field — r[key] raises and propagates as in-band {"Error":"The field 'key' of the record wasn't found."}. 3. Quote any identifier that contains whitespace, punctuation, or a reserved keyword — #"...". 4. When aggregating with Table.Group, remember [Col] (= _[Col], equivalent shorthand) is the column as a list, not a scalar — for a single cell index the sub-table explicitly with _{0}[Col] (item access {N} has no implicit-_ shorthand, so {0}[Col] is wrong).

PREFER

1. try ... otherwise FALLBACK when you only need a safe value and not the error. 2. Table.ReplaceErrorValues(t, {{"Col", null}}) over per-row try wrapping when you have already projected a column and want to clean the result.

AVOID

1. Treating an Arrow-null cell value as "the column has nulls" without testing — it may be an errored cell. Probe with try Conv{i}[Col] to disambiguate. 2. File.Contents, Web.Page, Web.BrowserContents — see § `File.Contents` and connectors.md § Runtime-disabled functions. 3. Discriminating on Error.Reason without setting it explicitly. Both built-in errors and error "msg" use Reason = "Expression.Error". For custom reasons use the record form: error [Reason = "...", Message = "..."].

See also