sqldw-consumption-cli

name

sqldw-consumption-cli

description

Execute read-only T-SQL queries against Fabric Data Warehouse, Lakehouse SQL Endpoints, and Mirrored Databases via the MCP `fabric-sqlendpoint-execute_query` tool. Default skill for any lakehouse data query (row counts, SELECT, filtering, aggregation) unless the user explicitly requests PySpark or Spark DataFrames. Use when the user wants to: (1) query warehouse/lakehouse data, (2) count rows or explore lakehouse tables, (3) discover schemas/columns, (4) generate T-SQL scripts, (5) monitor SQL performance, (6) export results to CSV/JSON. For a Fabric notebook cell (%%sql or other notebook magics), use `spark-authoring-cli`, not this skill. Triggers: "warehouse", "SQL query", "T-SQL", "query warehouse", "show warehouse tables", "show lakehouse tables", "query lakehouse", "lakehouse table", "how many rows", "count rows", "SQL endpoint", "describe warehouse schema", "generate T-SQL script", "warehouse performance", "export SQL data", "connect to warehouse", "lakehouse data", "explore lakehouse".

Telemetry — MANDATORY. Every api.fabric.microsoft.com call must carry x-ms-fabric-skill: sqldw-consumption-cli (az rest: --headers "x-ms-fabric-skill=sqldw-consumption-cli"), including every LRO poll, fabric_lro and retry. Snippets omit it — add it anyway.

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

SQL Endpoint Consumption — CLI Skill

⚠️ SQL Execution Override: For SQL data-plane execution, this skill supersedes COMMON-CLI SQL/TDS guidance. Use MCP fabric-sqlendpoint-execute_query (see Tool Stack) unless explicitly using Legacy CLI Fallback.

Table of Contents

Task	Reference	Notes
Finding Workspaces and Items in Fabric	COMMON-CLI.md § Finding Workspaces and Items in Fabric	Mandatory — READ link first [needed for finding workspace id by its name or item id by its name, item type, and workspace id]
Fabric Topology & Key Concepts	COMMON-CORE.md § Fabric Topology & Key Concepts
Environment URLs	COMMON-CORE.md § Environment URLs
Authentication & Token Acquisition	COMMON-CORE.md § Authentication & Token Acquisition	Wrong audience = 401; read before any auth issue
Core Control-Plane REST APIs	COMMON-CORE.md § Core Control-Plane REST APIs	Includes pagination, LRO polling, and rate-limiting patterns
OneLake Data Access	COMMON-CORE.md § OneLake Data Access	Requires storage.azure.com token, not Fabric token
Job Execution	COMMON-CORE.md § Job Execution
Capacity Management	COMMON-CORE.md § Capacity Management
Gotchas, Best Practices & Troubleshooting	COMMON-CORE.md § Gotchas, Best Practices & Troubleshooting
Tool Selection Rationale	COMMON-CLI.md § Tool Selection Rationale
Authentication Recipes

---

Tool Stack

Tool	Role	Install
fabric-sqlendpoint-execute_query MCP tool	Primary: Execute T-SQL queries against Fabric SQL Endpoints. Returns CSV results. Auth handled by MCP protocol.	No install — server-side. Requires MCP server registration (see below).
az CLI	Auth (az login), Fabric REST for workspace/item discovery.	Pre-installed in most dev environments
jq	Parse JSON from az rest	Pre-installed or trivial

IMPORTANT — MCP vs sqlcmd: This skill uses the fabric-sqlendpoint-execute_query MCP tool for all T-SQL execution. Do not use COMMON-CLI SQL/TDS/sqlcmd sections for query execution. Those references apply only for az rest control-plane patterns.

Agent preflight — verify before first SQL operation:

1. Confirm the fabric-sqlendpoint-execute_query tool is available in your tool list. This tool is provided by the fabric-sqlendpoint MCP server, which is registered either by installing a Fabric skills plugin (the path for end users) or via this repo's .mcp.json — other MCP clients may register it through their own configuration.
2. If no matching tool is found, the user must register the Fabric SQL Endpoint MCP server. See mcp-setup/ for registration instructions.
   • Global URL: https://api.fabric.microsoft.com/v1/mcp/dataPlane/sqlEndpoint
   • Item-scoped URL: https://api.fabric.microsoft.com/v1/mcp/dataPlane/workspaces/{workspaceId}/items/{itemId}/sqlEndpoint

MCP Tool Signature

fabric-sqlendpoint-execute_query(workspaceId, itemId, query)

Tool name may differ: execute_query is the logical operation. Depending on how the server is registered, the concrete tool name in your tool list may be prefixed (e.g. fabric-sqlendpoint-execute_query or sqlendpoint-global-execute_query). Invoke the concrete name shown in your tool list, always passing workspaceId, itemId, and query.

Parameter	Type	Description
workspaceId	string (UUID)	The workspace GUID containing the target item
itemId	string (UUID)	The Fabric item GUID to query. For a Warehouse or Mirrored Database, use the item id. For a Lakehouse, use its SQL analytics endpoint id (properties.sqlEndpointProperties.id) — not the Lakehouse item id.
query	string	T-SQL query text (single batch — no GO separators or sqlcmd meta-commands)

Returns: CSV resource (RFC 4180) with tabular results + metadata text ("Query returned N rows.").

Batch guidance: Multiple statements (e.g., SET NOCOUNT ON; SELECT ...) are allowed in a single call as long as there are no GO separators. Only the last result set is returned. For independent read queries, prefer separate fabric-sqlendpoint-execute_query calls for clearer error handling.

MCP Limits

Limit	Value	Notes
Max rows	10,000	Results are truncated beyond this. Use TOP, filters, or aggregations.
Query timeout	300 seconds	Long-running queries fail with timeout error.
Rate limit	20 requests/min per identity	HTTP 429 returned when exceeded. Retry after backoff.

These values are observed defaults, not a documented contract — the MCP service can change them. Treat them as guidance and confirm the current behavior from live 429 / timeout / truncation responses (or Microsoft Learn, if/when published) rather than relying on the exact numbers.

Supported Item Types

Item Type	itemId Source	Read Queries	DML (INSERT/UPDATE/DELETE)
Warehouse	GET /v1/workspaces/{wId}/warehouses → item id	✅	✅
Lakehouse SQL Endpoint	GET /v1/workspaces/{wId}/lakehouses → properties.sqlEndpointProperties.id (not the lakehouse id)	✅	❌ (read-only)
Mirrored Database	GET /v1/workspaces/{wId}/mirroredDatabases → item id	✅	❌ (read-only)

---

Connection

Discover workspaceId and itemId

You need the workspace GUID and item GUID to call fabric-sqlendpoint-execute_query. Discover them via the Fabric REST API:

# 1. Find workspace ID by name (capture into WS_ID for the next calls)
WS_ID=$(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)
echo "Workspace ID: $WS_ID"

# 2. Find warehouse item ID by name
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses" \
  --query "value[?displayName=='MyWarehouse'].id" --output tsv
# For a Lakehouse, pass its SQL analytics endpoint id — NOT the lakehouse item id
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/lakehouses" \
  --query "value[?displayName=='MyLakehouse'].properties.sqlEndpointProperties.id" --output tsv

Execute a Query

Once you have workspaceId and itemId, call the MCP tool:

fabric-sqlendpoint-execute_query(
  workspaceId: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
  itemId: "11111111-2222-3333-4444-555555555555",
  query: "SELECT TOP 10 * FROM dbo.FactSales"
)

No additional connection setup needed — authentication is handled transparently by the MCP protocol.

---

Agentic Exploration ("Chat With My Data")

Schema Discovery Sequence

Run these in order to understand what's in the endpoint. See references/discovery-queries.md for extended discovery queries.

# 1. List schemas
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY schema_name")

# 2. List tables and views
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT table_schema, table_name, table_type FROM INFORMATION_SCHEMA.TABLES ORDER BY table_schema, table_name")

# 3. Columns for a table
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT column_name, data_type, character_maximum_length, is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='dbo' AND table_name='FactSales' ORDER BY ordinal_position")

# 4. Preview rows
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT TOP 5 * FROM dbo.FactSales")

# 5. Row counts
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC")

# 6. Programmability objects (views, functions, procedures)
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF') ORDER BY type_desc, name")

Agentic Workflow

1. Discover → Run Steps 1–3 to understand available tables/columns.
2. Sample → SELECT TOP 5 on relevant tables.
3. Formulate → Write T-SQL using SQLDW-CONSUMPTION-CORE.md Supported T-SQL Surface Area.
4. Execute → Call fabric-sqlendpoint-execute_query(workspaceId, itemId, query).
5. Iterate → Refine based on results.
6. Present → Show results or generate follow-up queries.

---

Gotchas, Rules, Troubleshooting

For full T-SQL/platform gotchas: SQLDW-CONSUMPTION-CORE.md Gotchas and Troubleshooting Reference.

MUST DO

• Verify fabric-sqlendpoint-execute_query MCP tool is available — check tool list before first operation. If unavailable, instruct user to register the MCP server.
• Always use TOP or WHERE filters — the MCP tool returns a maximum of 10,000 rows. If exactly 10,000 rows are returned, results are likely truncated.
• Use COUNT(*) first for large tables — check row counts before running unbounded SELECTs.
• SET NOCOUNT ON; at the start of multi-statement queries — suppresses row-count messages.
• Label queries with OPTION (LABEL = 'AGENTCLI_...') for Query Insights tracing.
• Send valid T-SQL only — no GO batch separators, no :setvar, no sqlcmd meta-commands. Each fabric-sqlendpoint-execute_query call is a single T-SQL batch.
• Use multiple tool calls for multi-batch operations — if you need GO separators, split into separate fabric-sqlendpoint-execute_query calls.

AVOID

• sqlcmd — use the fabric-sqlendpoint-execute_query MCP tool instead. Do not shell out to sqlcmd for query execution.
• Unbounded SELECT * — will hit the 10,000 row cap. Always use TOP N or WHERE filters.
• Rapid-fire sequential queries — rate limit is 20 req/min per identity. Space out calls or consolidate with JOINs/UNION ALL.
• DML on Lakehouse/Mirrored DB — these are read-only. DML only works on Warehouse items.
• GO separators in query text — not supported. Use separate tool calls for each batch.
• MARS — not supported. Each query runs independently.
• Hardcoded item IDs — discover via REST API (Connection section).

PREFER

• fabric-sqlendpoint-execute_query MCP tool over any CLI tool for T-SQL execution.
• TOP N on exploration queries — avoid hitting row limits.
• Consolidating related queries into single SELECTs with JOINs to reduce rate-limit pressure.
• az rest for Fabric REST API operations — workspace/item discovery, capacity management.
• Aggregate queries (COUNT, SUM, AVG, GROUP BY) over full table scans.
• ORDER BY with TOP for deterministic results.

TROUBLESHOOTING

Symptom	Cause	Fix
MCP tool not available	MCP server not registered	Register https://api.fabric.microsoft.com/v1/mcp/dataPlane/sqlEndpoint in MCP client config
HTTP 401 / Unauthorized	Auth token expired or invalid	Re-authenticate (depends on MCP client — may need az login refresh)
HTTP 403 / Forbidden	Insufficient permissions on workspace/item	Verify user has Viewer+ role on the workspace/item
HTTP 404 / Not Found	Wrong workspaceId/itemId, or feature not enabled	Verify IDs via REST API; check if MCP feature is enabled for the tenant
HTTP 429 / Too Many Requests	Rate limit exceeded (20 req/min)	Wait and retry with backoff; consolidate queries
Query timeout (300s)	Query too complex or data too large	Simplify query, add filters, use TOP
Exactly 10,000 rows returned	Result truncation	Add TOP N or WHERE filters; use COUNT(*) to check total
"Invalid workspaceId/itemId"	Malformed UUID	Verify UUIDs are correct format (8-4-4-4-12 hex digits)
SQL error in response	T-SQL syntax error or invalid object	Fix T-SQL; verify table/column names via schema discovery
No rows but data exists	RLS filtering	Check USER_NAME(), verify RLS policies
Invalid object name 'queryinsights...'	New warehouse < 2 min old	Wait ~2 minutes