Sqldw CLI

<!-- Mode reference for the `sqldw-cli` skill. Loaded on demand from `skills/sqldw-cli/SKILL.md` when the request matches the `authoring` mode. -->

MODE-CRITICAL NOTES (authoring mode)

1. Table DDL, DML, COPY INTO / OPENROWSET ingestion, transactions and time travel are Warehouse-only. A Lakehouse SQL analytics endpoint and a Mirrored Database are read-only for table data — only views, functions, procedures and schemas can be authored there.

2. Reading the reference is not authoring. Every mutation ends with a fabric-sqlendpoint-execute_query call that runs the DDL/DML, followed by a readback query proving it landed.

sqldw-cli authoring mode — Fabric Warehouse and SQL Endpoint T-SQL Authoring

⚠️ 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

Tool Stack

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.

Agent preflight — verify before first 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 tool matches, the user must register the Fabric SQL Endpoint MCP server. See MCP setup.

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

Returns: CSV resource (RFC 4180) with tabular results + metadata text.

Batch guidance: Multiple statements without GO are allowed in one call (e.g., CREATE TABLE ...; INSERT INTO ...). However, only the last result set is returned, and an error in any statement fails the entire batch. Prefer separate fabric-sqlendpoint-execute_query calls for independent DDL/DML operations — this gives clearer error messages and lets you verify each step succeeded before proceeding.

MCP Limits

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.

Authoring Scope by Item Type

Fabric DW DDL Constraints

These constraints are hard requirements — violating them produces errors:

MERGE and ALTER COLUMN are NOT hard errors. Per T-SQL surface area, MERGE is a generally available Warehouse feature and ALTER TABLE ... ALTER COLUMN is in preview. Prefer an explicit DELETE+INSERT when snapshot-conflict isolation matters, and CTAS + sp_rename for production-critical column-type changes — as a robustness choice, not because the syntax is blocked.

Correct CREATE TABLE pattern:

CREATE TABLE dbo.Orders (
    OrderID INT NOT NULL,
    CustomerName VARCHAR(100) NULL,
    Amount DECIMAL(19,4) NULL,
    CreatedAt DATETIME2(6) NULL
)

ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderID) NOT ENFORCED

Additional supported patterns:

• CREATE TABLE [dbo].[clone] AS CLONE OF [dbo].[source] — duplicate table structure + data

• COPY INTO — highest-throughput ingestion from external storage

Connection

Discover workspaceId and itemId

You need the workspace GUID and item GUID to call fabric-sqlendpoint-execute_query:

# 1. Find workspace ID by name (capture into WS_ID for the next call)
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 SQL endpoint, 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

fabric-sqlendpoint-execute_query(
  workspaceId: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee",
  itemId: "11111111-2222-3333-4444-555555555555",
  query: "CREATE TABLE dbo.FactSales (SaleID bigint NOT NULL, Amount decimal(19,4) NOT NULL)"
)

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

Verifying DDL/DML Results

For DDL (CREATE/ALTER/DROP), the tool returns success with metadata. Always verify:

# After CREATE TABLE, verify it exists
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'FactSales'")

# After DML, check row count
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT COUNT(*) AS row_count FROM dbo.FactSales")

Agentic Workflows

Schema Discovery Before Authoring

Before any write operation, discover the target schema:

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

# 2. Check columns
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT column_name, data_type, is_nullable FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='FactSales' ORDER BY ordinal_position")

# 3. Sample data
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT TOP 5 * FROM dbo.FactSales")

# 4. Check constraints
fabric-sqlendpoint-execute_query(workspaceId, itemId, "SELECT constraint_name, constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='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
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–4 to understand available tables/columns.

2. Sample → SELECT TOP 5 on relevant tables.

3. Formulate → Select pattern from SQLDW-AUTHORING-CORE.md (Table DDL through Common Authoring Patterns).

4. Execute → Call fabric-sqlendpoint-execute_query(workspaceId, itemId, query). For multi-batch operations (e.g., CREATE PROCEDURE with BEGIN/END), use a single batch without GO.

5. Verify → Query affected table (SELECT COUNT(*), SELECT TOP 5).

6. Optionally follow up → Run additional queries to confirm schema changes.

Gotchas, Rules, Troubleshooting

For full authoring gotchas: SQLDW-AUTHORING-CORE.md Authoring Gotchas and Troubleshooting. For CLI-specific issues: COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Verify workspace has capacity before creating warehouse — call GET /v1/workspaces/{id} and check capacityId.

• Verify fabric-sqlendpoint-execute_query MCP tool is available — check the tool list before the first operation. If unavailable, instruct the user to register the MCP server.

• Discover workspaceId and itemId first — resolve the target Warehouse via az rest; the tool takes GUIDs, not an FQDN or -d <DatabaseName>.

• az login first (for discovery) — the az rest workspace/warehouse lookups need an Azure CLI session. The fabric-sqlendpoint MCP server itself ships headerless and authenticates via your MCP client's native Fabric session, not the Azure CLI token; no signed-in session → auth failure on either path.

• SET NOCOUNT ON; in scripts — suppresses row-count messages that corrupt output.

• Send a single T-SQL batch per call — no GO separators and no -i file.sql; split multi-batch work (CREATE PROCEDURE, multi-step transactions) into separate fabric-sqlendpoint-execute_query calls.

• Label authoring queries with OPTION (LABEL = 'ETL_description').

• Use explicit CAST() in CTAS to control output types.

• Keep transactions short — long transactions increase conflict window.

AVOID

• GO separators — the MCP tool accepts only a single T-SQL batch. Combine related DDL in one statement or call fabric-sqlendpoint-execute_query multiple times.

• sqlcmd meta-commands (:setvar, :r, -i) — not available in MCP tool. Inline all SQL in the query parameter.

• Unbounded SELECT * — 10,000 row limit. Always use TOP N or WHERE to limit result sets.

• Singleton INSERT ... VALUES at scale — creates tiny Parquet files. Use INSERT...SELECT, CTAS, or COPY INTO.

• DROP TABLE IF EXISTS + CREATE TABLE to refresh — loses time-travel history. Use TRUNCATE TABLE + INSERT INTO.

• MERGE in production — GA, but table-level snapshot-conflict detection makes concurrent writers likely to fail. Prefer DELETE + INSERT when isolation matters.

• ALTER COLUMN in production — in preview; prefer CTAS + sp_rename for production-critical column-type changes (Schema Evolution).

• Variables in CTAS — not allowed. Wrap in dynamic SQL: EXEC sp_executesql N'CREATE TABLE ...'.

• DML on Lakehouse/Mirrored DB SQLEP — read-only for table data. Only views/funcs/procs can be authored.

• Concurrent UPDATE/DELETE on same table — snapshot isolation conflicts at table level. Serialize writes.

• Rapid-fire MCP calls — rate limit is 20 req/min. Consolidate multiple statements into one batch where possible.

• MARS — not supported. Remove MultipleActiveResultSets from connection strings.

PREFER

• CTAS over CREATE TABLE + INSERT — parallel, single-operation.

• INSERT ... SELECT over singleton INSERTs.

• COPY INTO for external file ingestion — highest throughput.

• DELETE + INSERT over MERGE for upserts in production.

• TRUNCATE TABLE over DELETE FROM without WHERE — faster, preserves history.

• Consolidating related DDL into a single fabric-sqlendpoint-execute_query call when no GO is required between statements.

• CTAS + sp_rename for large-scale transforms instead of UPDATE.

• fabric-sqlendpoint-execute_query MCP tool over sqlcmd for all T-SQL operations.

• SET NOCOUNT ON; prefix — reduces metadata noise in results.

• TOP N or WHERE clauses — stay within 10K row limit.

TROUBLESHOOTING