Sqldw 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

SQL Endpoint Authoring — 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 |

| Definition Envelope | ITEM-DEFINITIONS-CORE.md § Definition Envelope | Definition payload structure |

| Per-Item-Type Definitions | ITEM-DEFINITIONS-CORE.md § Per-Item-Type Definitions | Support matrix, decoded content, part paths — REST specs, CLI recipes |

| Job Execution | COMMON-CORE.md § Job Execution ||

| Capacity Management | COMMON-CORE.md § Capacity Management ||

| Gotchas, Best Practices & Troubleshooting (Platform) | COMMON-CORE.md § Gotchas, Best Practices & Troubleshooting ||

| Tool Selection Rationale | COMMON-CLI.md § Tool Selection Rationale ||

| Authentication Recipes | COMMON-CLI.md § Authentication Recipes | az login flows and token acquisition |

| Fabric Control-Plane API via az rest | COMMON-CLI.md § Fabric Control-Plane API via az rest | Always pass --resource; includes pagination and LRO helpers |

| OneLake Data Access via curl | COMMON-CLI.md § OneLake Data Access via curl | Use curl not az rest (different token audience) |

| SQL / TDS Data-Plane Access | COMMON-CLI.md § SQL / TDS Data-Plane Access | Legacy sqlcmd reference (MCP is primary — see Tool Stack) |

| Job Execution (CLI) | COMMON-CLI.md § Job Execution ||

| OneLake Shortcuts | COMMON-CLI.md § OneLake Shortcuts ||

| Capacity Management (CLI) | COMMON-CLI.md § Capacity Management ||

| Composite Recipes | COMMON-CLI.md § Composite Recipes ||

| Gotchas & Troubleshooting (CLI-Specific) | COMMON-CLI.md § Gotchas & Troubleshooting (CLI-Specific) | az rest audience, shell escaping, token expiry |

| Quick Reference | COMMON-CLI.md § Quick Reference | az rest template + token audience/tool matrix |

| Item-Type Capability Matrix | SQLDW-CONSUMPTION-CORE.md § Item-Type Capability Matrix | Shows read-only (SQLEP) vs read-write (DW) |

| Connection Fundamentals | SQLDW-CONSUMPTION-CORE.md § Connection Fundamentals | TDS, port 1433, Entra-only, no MARS |

| Supported T-SQL Surface Area (Consumption Focus) | SQLDW-CONSUMPTION-CORE.md § Supported T-SQL Surface Area | Read before writing T-SQL — includes data types (no nvarchar/datetime/money) |

| Read-Side Objects You Can Create | SQLDW-CONSUMPTION-CORE.md § Read-Side Objects You Can Create | Views, TVFs, scalar UDFs, procedures |

| Temporary Tables | SQLDW-CONSUMPTION-CORE.md § Temporary Tables ||

| Cross-Database Queries | SQLDW-CONSUMPTION-CORE.md § Cross-Database Queries | 3-part naming, same workspace only |

| Security for Consumption | SQLDW-CONSUMPTION-CORE.md § Security for Consumption | GRANT/DENY, RLS, CLS, DDM |

| Monitoring and Diagnostics | SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics | Includes query labels; DMVs (live) + queryinsights.* (30-day history) |

| Performance: Best Practices and Troubleshooting | SQLDW-CONSUMPTION-CORE.md § Performance: Best Practices and Troubleshooting | Statistics, caching, clustering, query tips |

| REST API: Refresh SQL Endpoint Metadata | SQLDW-CONSUMPTION-CORE.md § REST API: Refresh SQL Endpoint Metadata | Force metadata sync when SQLEP is stale after ETL |

| System Catalog Queries (Metadata Exploration) | SQLDW-CONSUMPTION-CORE.md § System Catalog Queries | sys.tables, sys.columns, sys.views, sys.stats |

| Common Consumption Patterns | SQLDW-CONSUMPTION-CORE.md § Common Consumption Patterns | Reporting views, cross-DB analytics, temp table staging |

| Gotchas and Troubleshooting (Consumption) | SQLDW-CONSUMPTION-CORE.md § Gotchas and Troubleshooting Reference | 18 numbered issues with cause + resolution |

| Quick Reference: Consumption Capabilities | SQLDW-CONSUMPTION-CORE.md § Quick Reference: Consumption Capabilities ||

| Authoring Capability Matrix | SQLDW-AUTHORING-CORE.md § Authoring Capability Matrix | Read first — DW vs SQLEP authoring scope |

| Table DDL (DW Only) | SQLDW-AUTHORING-CORE.md § Table DDL (DW Only) | CREATE, CTAS, ALTER, sp_rename, DROP, constraints, schema evolution, IDENTITY |

| DML Operations (DW Only) | SQLDW-AUTHORING-CORE.md § DML Operations (DW Only) | INSERT...SELECT, UPDATE, DELETE, TRUNCATE, MERGE |

| Data Ingestion (DW Only) | SQLDW-AUTHORING-CORE.md § Data Ingestion (DW Only) | COPY INTO, OPENROWSET, method comparison |

| Transactions (DW Only) | SQLDW-AUTHORING-CORE.md § Transactions (DW Only) | Snapshot isolation only; write-write conflict rules |

| Stored Procedures (Authoring Patterns) | SQLDW-AUTHORING-CORE.md § Stored Procedures (Authoring Patterns) | ETL procs, upsert, CTAS swap, cursor replacement |

| Time Travel and Warehouse Snapshots | SQLDW-AUTHORING-CORE.md § Time Travel and Warehouse Snapshots (DW Only) | FOR TIMESTAMP AS OF; 30-day retention; snapshots GA |

| Source Control and CI/CD | SQLDW-AUTHORING-CORE.md § Source Control and CI/CD (DW Only — Preview) | Git integration, SQL DB projects, deployment pipelines |

| Authoring Permission Model | SQLDW-AUTHORING-CORE.md § Authoring Permission Model | Contributor minimum for DDL/DML; Admin for GRANT |

| Authoring Gotchas and Troubleshooting | SQLDW-AUTHORING-CORE.md § Authoring Gotchas and Troubleshooting | 17-row issue/cause/resolution table |

| Common Authoring Patterns | SQLDW-AUTHORING-CORE.md § Common Authoring Patterns | Incremental load, SCD Type 1, SQLEP view layer |

| Quick Reference: Authoring Decision Guide | SQLDW-AUTHORING-CORE.md § Quick Reference: Authoring Decision Guide | Scenario → recommended approach lookup |

| Core Authoring via MCP | authoring-cli-quickref.md § Core Authoring via MCP | Table DDL, DML, data ingestion via execute_query |

| Advanced Authoring Patterns via MCP | authoring-cli-quickref.md § Advanced Authoring Patterns via MCP | Transactions, schema evolution, stored procedures, time travel |

| MCP Workflow Templates | authoring-script-templates.md § MCP Workflow Templates | COPY INTO, ELT pipeline, upsert with retry, schema migration, time travel recovery |

| Tool Stack | SKILL.md § Tool Stack | fabric-sqlendpoint-execute_query MCP tool + az CLI; verify before first op |

| Connection | SKILL.md § Connection | workspaceId/itemId discovery, execute example |

| Query Execution | authoring-cli-quickref.md § Query Execution | MCP tool call format, batch considerations |

| Agentic Workflows | SKILL.md § Agentic Workflows | Start here — discover schema before any write |

| Monitoring Authoring Operations | authoring-cli-quickref.md § Monitoring Authoring Operations | Active DML/DDL, recent ETL, failed writes |

| Gotchas, Rules, Troubleshooting | SKILL.md § Gotchas, Rules, Troubleshooting | MUST DO / AVOID / PREFER checklists |

| Agent Integration Notes | authoring-cli-quickref.md § Agent Integration Notes | Platform-specific tips (Copilot CLI, Claude Code) |

Tool Stack

| Tool | Role | Install |

|---|---|---|

| fabric-sqlendpoint-execute_query MCP tool | Primary: Execute DDL/DML T-SQL queries against Fabric SQL Endpoints. Returns CSV results. Auth handled by MCP protocol. | No install — server-side. Requires MCP server registration. |

| az CLI | Auth (az login), Fabric REST for workspace/item discovery, snapshot management. | 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.

> 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 matching tool is found, 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.

| Parameter | Type | Description |

|-----------|------|-------------|

| workspaceId | string (UUID) | The workspace GUID containing the target item |

| itemId | string (UUID) | The Fabric item GUID to target. For a Warehouse, 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.

> 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

| Limit | Value | Notes |

|-------|-------|-------|

| Max rows returned | 10,000 | For DDL/DML, row count metadata indicates affected rows |

| Query timeout | 300 seconds | Long-running operations may timeout |

| Rate limit | 20 requests/min per identity | HTTP 429 returned when exceeded |

> 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

| Capability | Warehouse (DW) | Lakehouse/Mirrored DB SQLEP |

|---|---|---|

| Table DDL (CREATE/ALTER/DROP) | ✅ | ❌ |

| DML (INSERT/UPDATE/DELETE/MERGE) | ✅ | ❌ |

| COPY INTO, OPENROWSET (ingest) | ✅ | OPENROWSET read-only |

| Transactions | ✅ | ❌ |

| Time travel, snapshots | ✅ | ❌ |

| CREATE VIEW/FUNCTION/PROCEDURE | ✅ | ✅ |

| CREATE SCHEMA | ✅ | ✅ |

Fabric DW DDL Constraints

These constraints are hard requirements — violating them produces errors:

| Constraint | Details |

|---|---|

| No DEFAULT in CREATE TABLE | Default values not supported. Set defaults in application/INSERT logic. |

| No PRIMARY KEY inside CREATE TABLE | Must add via ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY NONCLUSTERED (col) NOT ENFORCED |

| DATETIME2 requires precision | Always use DATETIME2(6), never bare DATETIME2 |

| Unsupported data types | NCHAR, NVARCHAR, TEXT, IMAGE, MONEY, SMALLMONEY, DATETIME — use VARCHAR, DECIMAL, DATETIME2(6) instead |

| No WITH DISTRIBUTION | Distribution is automatic in Fabric |

| Constraints must be NOT ENFORCED | PRIMARY KEY NONCLUSTERED NOT ENFORCED, UNIQUE NONCLUSTERED NOT ENFORCED, FOREIGN KEY NOT ENFORCED |

| PK columns must be NOT NULL | Declare PK columns as NOT NULL in CREATE TABLE before adding PK constraint |

| ALTER TABLE scope | Add/drop nullable columns (must specify NULL) and add/drop constraints (NOT ENFORCED only) are GA; ALTER COLUMN type changes are in preview (see note below) |

> 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

• Discover → Run steps 1–4 to understand available tables/columns.
• Sample → SELECT TOP 5 on relevant tables.
• Formulate → Select pattern from SQLDW-AUTHORING-CORE.md (Table DDL through Common Authoring Patterns).
• 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.
• Verify → Query affected table (SELECT COUNT(*), SELECT TOP 5).
• 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

| Symptom | Fix |

|---|---|

| Error 24556/24706 snapshot conflict | Serialize writes to same table; retry with backoff |

| COPY INTO auth error | Grant Storage Blob Data Reader on ADLS; or SAS in CREDENTIAL |

| COPY INTO from OneLake fails | Provision workspace identity; check firewall rules |

| CTAS unexpected types | Use explicit CAST() in SELECT |

| Singleton INSERT poor perf | Remediate: CTAS + drop + rename to consolidate Parquet |

| fabric-sqlendpoint-execute_query tool not available | MCP server not registered — user must add Fabric SQL Endpoint MCP server |

| HTTP 429 rate limit exceeded | Wait 60s and retry; consolidate queries into fewer calls |

| Query timeout (300s) | Break into smaller operations; for COPY INTO, check source file sizes |

| sp_rename on SQLEP fails | Only available on Warehouse, not Lakehouse/Mirrored DB |

| Deploy drops/recreates table | Avoid ALTER TABLE in DB project; apply manually |

| Only last result set returned | MCP returns only the final SELECT. Split multi-SELECT batches into separate calls. |

| Binary columns unreadable | Columns with [base64] suffix are base64-encoded. Decode if needed. |