AutoGineer summary
Written by AutoGineer from the skill’s own files, not by the author
The sqldw-cli skill provides a structured interface for interacting with Microsoft Fabric data storage items, including Warehouses, Lakehouse SQL analytics endpoints, and Mirrored Databases. It operates through three distinct modes—authoring, consumption, and operations—to handle T-SQL execution, data ingestion, and performance diagnostics. The skill relies on the fabric-sqlendpoint-execute_query MCP tool for data-plane operations and requires users to consult specific mode references to ensure correct T-SQL syntax and operational constraints.
What it does
- Executes DDL and DML statements for schema management and data manipulation in Fabric Warehouses.
- Supports data ingestion via COPY INTO and OPENROWSET patterns.
- Provides read-only querying capabilities for data exploration and metadata discovery.
- Enables performance diagnostics using Query Insights and capacity metrics correlation.
- Facilitates Lakehouse table health monitoring and diagnostic reporting.
- Dispatches requests to specialized modes based on user intent to ensure safe and accurate execution.
When to use it
- When you need to create, modify, or drop tables and schemas within a Fabric Warehouse.
- When performing bulk data ingestion from external storage into a Warehouse.
- When querying data or metadata from a Warehouse, Lakehouse SQL endpoint, or Mirrored Database.
- When investigating performance issues, such as slow queries or CU spikes, using diagnostic views.
- When correlating capacity consumption with specific SQL queries or users.
Advantages
- Centralizes management of diverse Fabric SQL-based data items under a single skill.
- Enforces mode-specific safety boundaries to prevent accidental mutations in read-only contexts.
- Provides clear diagnostic paths for performance tuning and capacity management.
- Standardizes T-SQL execution through a dedicated MCP tool interface.
- Includes mandatory telemetry headers for consistent tracking across Fabric API calls.
Usage examples
- “How many rows are in the nyctlc table in SkillsTestWarehouse?”
- The agent selects the consumption mode, reads the consumption reference, and executes a SELECT COUNT(*) query via the MCP tool to return the exact row count.
- “Create dbo.Orders in the warehouse with OrderId, CustomerId and OrderDate.”
- The agent selects the authoring mode, reads the authoring reference, executes the CREATE TABLE statement, and performs a readback query to confirm the table exists.
- “Which queries are slowest, and which tables should I cluster?”
- The agent selects the operations mode, reads the operations reference, and executes diagnostic queries against queryinsights to identify slow-running patterns.
Install
Download the skill as a ZIP:
https://autogineer.com/agent-skills/microsoft/sqldw-cli/downloadUnzip it into the cross-client skills folder of your project:
mkdir -p .agents/skills && unzip sqldw-cli.zip -d .agents/skills
Documentation by Microsoft
Telemetry — MANDATORY. Every
api.fabric.microsoft.comcall must carryx-ms-fabric-skill: sqldw-cli(az rest:--headers "x-ms-fabric-skill=sqldw-cli"), including every LRO poll,fabric_lroand retry. Snippets omit it — add it anyway. This covers all three modes; the mode references inherit it.
CRITICAL NOTES
To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
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
Skill disambiguation: use
sqldw-clifor T-SQL sent to a Warehouse, a Lakehouse SQL analytics endpoint, or a Mirrored Database, including plain Lakehouse table SELECT, row-count, filtering, and aggregation requests. Target Warehouse SQL executed within an end-to-end Azure Synapse or Dedicated SQL Pool migration belongs tosynapse-migration; standalone Warehouse work before or after migration returns tosqldw-cli. Any notebook-cell or PySpark DataFrame work isspark-cli; a Fabric SQL database (OLTP) issqldb-cli.
Fabric Warehouse and SQL Endpoints — CLI Skill
This one skill owns Fabric Warehouse, Lakehouse SQL analytics endpoints and Mirrored Databases: T-SQL authoring and ingestion, read-only querying, and warehouse performance diagnostics.
It is a mode dispatcher and contains NO procedures. Pick the mode that matches the request from the table below, then read the matching references/<mode>.md file end to end with your file-reading tool BEFORE issuing a single command. That file holds the T-SQL surface area, DDL constraints, query templates and gotchas; acting without it produces invalid T-SQL and wrong results.
Mode selection
| Mode | Use when the request ... | Example triggers | Read this first |
|---|---|---|---|
authoring | changes warehouse state: table DDL, DML, ingestion, transactions, procedures, schema evolution, time travel | create warehouse table, COPY INTO, OPENROWSET, INSERT/UPDATE/DELETE, warehouse MERGE, CTAS, sp_rename, create T-SQL procedure, warehouse time travel | references/authoring.md |
consumption | reads data or metadata: SELECT, row counts, filtering, aggregation, schema/object discovery, CSV export | query warehouse, count rows lakehouse, SELECT lakehouse, show tables, describe warehouse schema, export SQL data | references/consumption.md |
operations | diagnoses performance, failures, capacity consumption, SQL pool usage, or health through queryinsights and supported endpoint diagnostics | failed or canceled warehouse queries, CU spike, Capacity Metrics app, expensive SQL users, custom SQL pool recommendation, queryinsights CPU, pressure events, cache warmth, Lakehouse tables needing attention | references/operations.md |
Operations reference index
Read references/operations.md first for any operations request, then open the matching leaf directly from this index. Do not chain from links inside a leaf; for composite requests, follow scenarios.md only to the additional leaves that this index links directly.
| Request | Read this leaf reference |
|---|---|
| composite operations scenarios such as why is my warehouse slow, performance degradation, optimization, or what are people running | references/operations/scenarios.md |
| slow-query summaries, top users, recent queries, pattern search, runtime profiles, cluster-key candidates | references/operations/query-reference.md |
| failed or canceled requests, error codes, recurring non-successful query shapes | references/operations/failure-analysis.md |
| SQL pool pressure windows and overlapping requests | references/operations/pool-pressure.md |
| CPU concentration, regressions, repeated expensive queries, cache interpretation | references/operations/resource-consumers.md |
| Lakehouse Delta file health and tables needing maintenance | references/operations/lakehouse-health.md |
| Capacity Metrics CU spike, costly item, SQL query/user correlation | references/operations/capacity-metrics-correlation.md — start at section 1 |
| custom SQL pool recommendation for an already identified SQL endpoint | references/operations/capacity-metrics-correlation.md — skip FabricIQ and start at section 6 |
Mode boundary rule
Classify by intent, not by endpoint — all three modes issue the same execute_query call.
A schema-discovery
SELECTrun to plan aCREATE TABLEbelongs toauthoring, even though it only reads.A
SELECTthat answers the user's question isconsumption.A query against
queryinsights.*or a supported diagnostic such as Lakehousesys.sp_get_table_health_metricsisoperations; aSELECTagainst user tables is not, however slow it is.
consumption and operations are read-only. If a request genuinely spans modes, handle them one at a time and read each reference before you start that part. If the mode is ambiguous after reading this table, ask one short clarifying question instead of guessing.
Terminal write — the step you must not skip
Reading the reference and drafting the T-SQL is NOT completing the task. If you did not send the statement, nothing changed — say so explicitly rather than reporting success.
| Mode | Terminal write |
|---|---|
authoring | The DDL/DML itself, sent through execute_query. Follow it with a readback in a second call (SELECT ... FROM INFORMATION_SCHEMA.TABLES after CREATE, SELECT COUNT(*) after DML) and report the object you created or changed under the name the user asked for. Only a Warehouse accepts table DDL/DML — see the mode reference for what a Lakehouse SQL endpoint and a Mirrored Database allow. |
consumption | none — this mode is read-only |
operations | none — read-only, but you must still run the diagnostic statements: every figure comes from a SELECT or documented read-only diagnostic procedure executed in the turn you report it, cited inline with its source view or procedure, never carried forward from an earlier turn. The only allowed diagnostic EXEC is sys.sp_get_table_health_metrics on a Lakehouse SQL analytics endpoint. Never execute ALTER, CREATE or DROP yourself, even when the diagnosis is certain. |
consumption and operations reporting
Neither read-only mode has a terminal write, so its deliverable is the answer itself. Run the query against the live endpoint and report the real rows — a summary of the reference does not answer the request.
In operations, name the source view, catalog, or procedure each figure came from right next to it (for example 2,140 ms (queryinsights.long_running_queries) or 268 files (sys.sp_get_table_health_metrics)), including when the answer is zero rows. Re-run the diagnostic in the turn you report it rather than restating an earlier turn's output. Never fabricate, assume or infer diagnostic numbers.
Shared essentials (all modes)
Every mode reaches the data plane the same way. Resolve the workspace and item first, then send T-SQL through the MCP tool.
Execution surface — fabric-sqlendpoint-execute_query
All T-SQL runs through the fabric-sqlendpoint-execute_query MCP tool. For SQL data-plane execution this skill supersedes the COMMON-CLI SQL/TDS guidance — use the MCP tool, not sqlcmd, unless you are explicitly on the documented Legacy CLI Fallback path (see the mode reference). az rest stays the right tool for control-plane discovery.
fabric-sqlendpoint-execute_query(workspaceId, itemId, query)Preflight, before the first operation of any mode: confirm a tool whose name ends in
execute_queryis in your tool list. It comes from thefabric-sqlendpointMCP server, registered by a Fabric skills plugin or this repo's.mcp.json. The concrete name may be prefixed (fabric-sqlendpoint-execute_query,sqlendpoint-global-execute_query) — invoke the name you actually see. If none is present, say so, then fall back to the Legacy CLI Fallback (TDS client) documented in the mode reference; tell the user they can register the server for the primary path — see mcp-setup/. Exception: the Capacity Metrics correlation workflow is intentionally limited to the existing FabricIQ and SQL Endpoint MCP surfaces; if SQL Endpoint MCP is unavailable, report that phase as blocked and do not use the legacy fallback.itemIdis a GUID, never an FQDN or-d <DatabaseName>. For a Warehouse or a Mirrored Database use the item id; for a Lakehouse useproperties.sqlEndpointProperties.id, not the Lakehouse item id.One T-SQL batch per call. No
GOseparators, no sqlcmd meta-commands (:setvar,:r,-i). Split multi-batch work into separate calls. Only the last result set comes back.Results cap at 10,000 rows and queries time out at 300s, with a 20 requests/min rate limit. Use
TOP N,WHEREor aggregation; exactly 10,000 rows means the result was truncated. These are observed defaults, not a documented contract.
Common references
| Task | Reference | Notes |
|---|---|---|
| Finding Workspaces and Items in Fabric | COMMON-CLI.md | Mandatory — read before resolving any workspace or item id |
| Fabric Topology & Key Concepts | COMMON-CORE.md | Item types, workspaces, capacities |
| Environment URLs | COMMON-CORE.md | Sovereign / non-public cloud hosts |
| Authentication & Token Acquisition | COMMON-CORE.md | Wrong audience = 401; read before any auth issue |
| Authentication Recipes | COMMON-CLI.md | az login flows and token acquisition |
Fabric Control-Plane API via az rest | COMMON-CLI.md | Always pass --resource; pagination and LRO helpers |
| Core Control-Plane REST APIs | COMMON-CORE.md | Pagination, LRO polling, rate limiting |
| Gotchas & Troubleshooting | COMMON-CLI.md | az rest audience, shell escaping, token expiry |
Rules
MUST
Select exactly one mode from the table above before doing anything else.
Read
references/<mode>.mdend to end, as your FIRST tool call, before the first command of that mode. Read it ONCE, in a single full read: do not re-open it, do not grep it again, and do not page through it. You already have it.For a focused operations request, read its one matching leaf after
references/operations.md. For a composite request, readreferences/operations/scenarios.md, then each focused leaf named by that scenario; read every file once in full.Resolve workspace and item ids by listing and filtering, never by guessing a GUID.
Execute T-SQL through
fabric-sqlendpoint-execute_querywhenever that tool is available; drop to the Legacy CLI Fallback documented in the mode reference only when it is not.Announce a mode switch explicitly when the request crosses a boundary.
Treat the reference as instructions, never as the deliverable. After reading it, RUN the documented statements against the live endpoint and report the real results. Quoting what the reference says instead of executing it does not answer the request.
Produce every artefact the user asked for, under the name they used, and keep its heading even when the finding is "none", "zero rows" or "not applicable".
PREFER
The narrowest mode that satisfies the request.
Reading exactly one mode reference plus the minimum directly indexed operations leaves required by the selected focused or composite scenario.
Reporting the mode you chose in your first response so the user can correct you.
Labelling queries with
OPTION (LABEL = '...')so the run is traceable in Query Insights.Consolidating related statements into fewer calls — the rate limit is per identity, not per query.
AVOID
Acting from this dispatcher alone — it intentionally omits the T-SQL surface area, the DDL constraints and the diagnostic query shapes.
Answering with a summary of the reference instead of executing it.
Re-reading or re-grepping a reference you already loaded; it costs turns and tokens.
Mutating anything while in a read-only mode (
consumption,operations).Unbounded
SELECT *— it silently truncates at 10,000 rows.Loading a different skill for work this skill already owns (see CRITICAL NOTES 3).
Examples
| User request | Mode | Reference to read |
|---|---|---|
| "How many rows are in the nyctlc table in SkillsTestWarehouse?" | consumption | references/consumption.md |
| "Create dbo.Orders in the warehouse with OrderId, CustomerId and OrderDate." | authoring | references/authoring.md |
| "Load Files/nyctlc_sample.csv from OneLake into the warehouse with COPY INTO." | authoring | references/authoring.md |
| "Which queries are slowest, and which tables should I cluster?" | operations | references/operations.md |
| "Show me the average trip distance per vendor from the lakehouse SQL endpoint." | consumption | references/consumption.md |
Files
- ATTRIBUTION.mdAdded by AutoGineer863 B
- LICENSELicence file added by AutoGineer1.1 KiB
- SKILL.md14.2 KiB
- apm.yml608 B
- references/authoring.md26.7 KiB
- references/authoring/authoring-cli-quickref.md9.6 KiB
- references/authoring/authoring-script-templates.md7.3 KiB
- references/consumption.md20.6 KiB
- references/consumption/consumption-cli-quickref.md3.9 KiB
- references/consumption/discovery-queries.md5.9 KiB
- references/consumption/script-templates.md4.9 KiB
- references/operations.md18.6 KiB
- references/operations/capacity-metrics-correlation.md19.2 KiB
- references/operations/failure-analysis.md7.0 KiB
- references/operations/lakehouse-health.md2.6 KiB
- references/operations/pool-pressure.md4.0 KiB
- references/operations/query-reference.md17.8 KiB
- references/operations/resource-consumers.md7.6 KiB
- references/operations/scenarios.md8.5 KiB
references/authoring/authoring-script-templates.md
Authoring Workflow Templates
MCP fabric-sqlendpoint-execute_query workflow templates for common authoring operations. Each template shows the sequence of fabric-sqlendpoint-execute_query calls an agent should perform.
Prerequisites:
fabric-sqlendpoint-execute_queryMCP tool available (verify in tool list)workspaceIdanditemIdknown (discover viaaz rest— see SKILL.md § Connection)
Placeholders: The storage URLs (https://storageacct.dfs.core.windows.net/...), dates
(2025-06-15, 2025-06-01, etc.), and LABEL values (ETL_Transform_20250615) below are
illustrative — replace them with your own <storage_path>, <YYYY-MM-DD>, and label values
before running. Recommended LABEL convention: <process>_<step>_<YYYYMMDD> (e.g. ETL_Stage_20250615)
so loads are traceable in queryinsights without hardcoding a stale date.
MCP Workflow Templates
Template 1: COPY INTO Ingestion
# Step 1: Load data from ADLS
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"COPY INTO dbo.StagingData
FROM 'https://storageacct.dfs.core.windows.net/container/sales/*.parquet'
WITH (FILE_TYPE = 'PARQUET')")
# Step 2: Verify load
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT COUNT(*) AS row_count FROM dbo.StagingData")Notes:
User must have Storage Blob Data Reader on the ADLS account
For OneLake paths, ensure workspace identity is provisioned
COPY INTO is the highest-throughput ingestion method
Template 2: Full ELT Pipeline (Stage → Transform → Load)
# Step 1: Load raw data into staging
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"COPY INTO dbo.Staging_RawSales
FROM 'https://storageacct.dfs.core.windows.net/container/raw/*.parquet'
WITH (FILE_TYPE = 'PARQUET')")
# Step 2: Transform and load into fact table
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"INSERT INTO dbo.FactSales (SaleID, ProductID, CustomerID, SaleDate, Amount)
SELECT SaleID, ProductID, CustomerID,
CAST(SaleTimestamp AS date) AS SaleDate,
CAST(RawAmount AS decimal(19,4)) AS Amount
FROM dbo.Staging_RawSales
WHERE RawAmount > 0 AND SaleID IS NOT NULL
OPTION (LABEL = 'ETL_Transform_20250615')")
# Step 3: Clean staging
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"TRUNCATE TABLE dbo.Staging_RawSales")
# Step 4: Verify
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT COUNT(*) AS fact_rows FROM dbo.FactSales")Template 3: Incremental Upsert (DELETE + INSERT with Retry)
# Step 1: Execute transactional upsert
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM dbo.FactSales WHERE SaleDate >= '2025-06-01';
INSERT INTO dbo.FactSales
SELECT * FROM SalesLakehouse.dbo.ProcessedSales WHERE SaleDate >= '2025-06-01';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;")
# If step 1 fails with snapshot conflict (error 24556/24706):
# Wait 5-10 seconds and retry. Max 3 attempts.
# Space retries: 5s, 10s, 15s.
# Step 2: Verify
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT COUNT(*) AS updated_rows FROM dbo.FactSales WHERE SaleDate >= '2025-06-01'")Error handling:
Error 24556/24706 (snapshot conflict): Retry with exponential backoff
HTTP 429 (rate limit): Wait 60s before retry
Timeout (300s): Break into smaller date ranges
Template 4: Schema Migration (CTAS Workaround)
ALTER TABLE ... ALTER COLUMN is in preview; for a robust, GA-only path use CTAS + rename:
# Step 1: Create new table with updated schema
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"CREATE TABLE dbo.FactSales_V2 AS
SELECT SaleID, ProductID, CustomerID, SaleDate,
CAST(Amount AS decimal(19,2)) AS Amount, Quantity
FROM dbo.FactSales")
# Step 2: Rename the original out of the way (keeps a rollback copy)
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"EXEC sp_rename 'dbo.FactSales', 'FactSales_Old'")
# Step 3: Rename the new table into place
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"EXEC sp_rename 'dbo.FactSales_V2', 'FactSales'")
# Step 4: Re-apply constraints
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"ALTER TABLE dbo.FactSales
ADD CONSTRAINT PK_FactSales PRIMARY KEY NONCLUSTERED (SaleID) NOT ENFORCED")
# Step 5: Verify
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT column_name, data_type, numeric_precision, numeric_scale
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'FactSales' AND column_name = 'Amount'")
# Step 6: Drop the original only after the rename + verification succeed
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"DROP TABLE dbo.FactSales_Old")WARNING: CTAS + rename loses time-travel history and security. Re-apply GRANT/DENY after swap.
Template 5: Time Travel Recovery
# Step 1: Preview data at point in time
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT TOP 10 * FROM dbo.FactSales
OPTION (FOR TIMESTAMP AS OF '2025-06-14T23:59:59.999')")
# Step 2: Create recovery table
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"CREATE TABLE dbo.FactSales_Recovered AS
SELECT * FROM dbo.FactSales
OPTION (FOR TIMESTAMP AS OF '2025-06-14T23:59:59.999')")
# Step 3: Count recovered rows
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT COUNT(*) AS recovered_rows FROM dbo.FactSales_Recovered")
# Step 4: Merge back missing rows (adjust PK column as needed)
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"INSERT INTO dbo.FactSales
SELECT r.* FROM dbo.FactSales_Recovered r
WHERE NOT EXISTS (SELECT 1 FROM dbo.FactSales t WHERE t.SaleID = r.SaleID)")
# Step 5: Cleanup
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"DROP TABLE dbo.FactSales_Recovered")Template 6: Create Stored Procedure
# Step 1: Create/update procedure (single batch, no GO)
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"CREATE OR ALTER PROCEDURE dbo.sp_IncrementalLoad
@CutoffDate date
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM dbo.FactSales WHERE SaleDate >= @CutoffDate;
INSERT INTO dbo.FactSales
SELECT * FROM SalesLakehouse.dbo.ProcessedSales WHERE SaleDate >= @CutoffDate;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
END")
# Step 2: Verify creation
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"SELECT name, type_desc, create_date, modify_date FROM sys.procedures WHERE name = 'sp_IncrementalLoad'")
# Step 3: Execute
fabric-sqlendpoint-execute_query(workspaceId, itemId,
"EXEC dbo.sp_IncrementalLoad @CutoffDate = '2025-06-01'")Legacy CLI Fallback
If the fabric-sqlendpoint-execute_query MCP tool is unavailable (e.g., MCP server not registered), fall back to sqlcmd:
# Install: winget install sqlcmd (Windows) / brew install sqlcmd (macOS)
# Auth: az login
# Connect: sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DB>" -G -Q "<sql>"For the full CLI patterns, refer to COMMON-CLI.md.
Requirements and compatibility
Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta
“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.
Needs: Needs an MCP server
Security
No issues found by our static scan· No issues found
A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.
Attribution and licence
- Published by
- Microsoft
- Publisher
- Microsoft (Official)
- Source
- microsoft/skills-for-fabric · skills/sqldw-cli · commit f8fac11
- Licence
- MIT License (redistribution allowed)
- LICENSE (added by AutoGineer from LICENSE)
- ATTRIBUTION.md (written by AutoGineer)
- Synced
- Also distributed in
- microsoft/skills-for-fabric
AutoGineer is not affiliated with or endorsed by Microsoft or Microsoft.
Download ZIP
The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download sqldw-cli.zip