Fabric Warehouse

Fabric Warehouse T-SQL surface area

Note: This skill applies to Fabric Warehouse only — the distributed Synapse-engine warehouse. Fabric SQL Database uses the full Azure SQL Database engine and does NOT have these restrictions. See the fabric-database skill.

Unsupported Data Types — Use These Alternatives

| Unsupported Type | Use Instead | Notes | |---|---|---| | nvarchar / nchar | varchar / char | UTF-8 collation handles Unicode | | money / smallmoney | decimal(19,4) | | | datetime / smalldatetime | datetime2(6) | | | datetimeoffset | datetime2(6) | Timezone offset is lost | | xml | varchar(max) | XML functions lost | | ntext / text | varchar(max) | | | image | varbinary(max) | | | tinyint | smallint | | | geometry / geography | varbinary (WKB) or varchar (WKT) | Cast as needed | | sql_variant | No equivalent | | | hierarchyid | No equivalent | |

Unsupported T-SQL Features

• FOR XML — use FOR JSON instead (and only as last operator, not in subqueries)
• Recursive CTEs
• SET ROWCOUNT / SET TRANSACTION ISOLATION LEVEL
• Materialized views
• Triggers
• Cursors — replace with WHILE + ROW_NUMBER(). Row-by-row is slow on a distributed engine; prefer set-based whenever possible.
• CREATE USER — users auto-created on GRANT/DENY
• Multi-column manual statistics
• PREDICT
• Schema/table names with / or \
• MARS (Multiple Active Result Sets) — remove from connection strings

Supported Features

• Standard and nested CTEs
• Window functions (ROWNUMBER, RANK, DENSERANK, NTILE, LAG, LEAD, aggregates OVER)
• CROSS APPLY / OUTER APPLY
• PIVOT / UNPIVOT
• FOR JSON (last operator only)
• COALESCE, NULLIF, IIF, CHOOSE
• Cross-database queries via 3-part naming (same workspace AND same region only)
• Session-scoped #temp tables (prefer distributed with WITH (DISTRIBUTION = ROUND_ROBIN))

Table Constraints and Limits

• 8,060-byte row limit (error 511 / 611 on violation)
• 128-char limit on table/column names
• 1,024-column max per table
• No default value constraints; no computed columns (use views)
• PK / UNIQUE / FK supported only as NONCLUSTERED + NOT ENFORCED — metadata-only; the engine does not enforce them at DML time. They serve as optimizer hints, and Power BI uses FK relationships for automatic relationship detection.
• DEFAULT / CHECK not supported
• NOT NULL only via CREATE TABLE (cannot be added via ALTER TABLE)

Schema Evolution

| Operation | Status | Syntax | |---|---|---| | Add nullable column | ✅ | ALTER TABLE t ADD col type NULL | | Drop column | ✅ April 2025+ | ALTER TABLE t DROP COLUMN col (metadata-only) | | Rename column | ✅ April 2025+ | EXEC sp_rename 't.OldCol', 'NewCol', 'COLUMN' | | Rename table | ✅ | EXEC sp_rename 'OldName', 'NewName' | | Add / drop NONCLUSTERED NOT ENFORCED PK / UNIQUE / FK | ✅ | ALTER TABLE t ADD CONSTRAINT ... NONCLUSTERED NOT ENFORCED / DROP CONSTRAINT | | ALTER TABLE inside BEGIN TRAN ... COMMIT | ✅ April 2026+ GA | All supported ALTER TABLE variants run atomically; any failure rolls every schema change back | | ALTER COLUMN — widen type (metadata-only) | 🔶 Preview | ALTER TABLE t ALTER COLUMN col wider_type. Metadata-only type-widening only (see subsection below). No narrowing, no NULL→NOT NULL. | | ALTER COLUMN — narrow type / NULL→NOT NULL / retype IDENTITY / change collation | ❌ | Not supported even in preview. CTAS workaround: create new table with desired schema, DROP TABLE, sp_rename, re-add constraints/security |

CTAS workaround destroys time-travel history and security (GRANT/DENY) on the original table — re-apply security after the swap.

ALTER COLUMN (Preview)

ALTER TABLE ... ALTER COLUMN is in preview. It supports metadata-only schema evolution — only changes that don't require validating or rewriting the underlying Parquet files (i.e. type widening compatible with existing stored data). Takes a Sch-M lock for the duration (blocks/blocked by concurrent workloads).

Supported conversions (widening / interchange only):

| Category | Source → Target | |---|---| | Integer widening | smallint→int/bigint; int→bigint | | Floating-point widening | real→float; smallint/int→float | | Decimal widening | decimal(p,s)→decimal(p+k1, s+k2) where k1 ≥ k2 ≥ 0; smallint/int→decimal(10+k1, k2) | | Decimal / numeric interchange | decimal(p,s) ↔ numeric(p,s) | | Float / real interchange | float(n 0), then branch into a notebook that runs OPTIMIZE. Note before VACUUM: removing old files permanently shortens the time-travel window.

Source Control and CI/CD (Preview)

Source control for Fabric Warehouse is a preview feature — both Git integration and deployment pipelines.

• Git integration (workspace-level, Azure DevOps or GitHub): commit/sync warehouse objects, branch out to feature workspaces, revert, bi-directional sync; automatable via Fabric REST APIs. Warehouse appears as a supported item (preview) in the Source control panel.
• Deployment pipelines: promote across Dev → Test → Prod stages.
• IDE / local: VS Code with DacFx (SQL database projects) for schema management, SSMS for interactive dev; external CI/CD via SQLPackage CLI, DacFx tasks, and REST APIs.
• Use SQL database projects + Git for incremental object-level change and history; use deployment pipelines for environment promotion.
• Collation-mismatch gotcha: promoting/branching/merging when source and target warehouses were created with different collations is not supported — deployment may succeed but dataset collation isn't reconciled. Fix with the dw-collation-error-update-tmsl script in the Fabric toolbox.

Default Collation

Latin1_General_100_BIN2_UTF8 — case-sensitive, binary. Case-insensitive alternative: Latin1_General_100_CI_AS_KS_WS_SC_UTF8. Use explicit COLLATE in comparisons if case-insensitive is needed.

Pipeline Integration

• Use the Script activity (with a Warehouse connection) to invoke Warehouse stored procedures from Fabric Data Pipelines.
• The Stored Procedure activity does NOT support Fabric Warehouse — it only supports Azure SQL / SQL MI. Common pitfall when wiring up DW from pipelines.

Reference

• Microsoft Learn: What is Fabric Data Warehouse?
• Microsoft Learn: Performance guidelines
• Microsoft Learn: Secure your Fabric Data Warehouse
• Comprehensive MS Learn link bundle (concept / connect / tables / ingestion / performance / monitoring / security / backup-restore / source control & CI/CD): [references/REFERENCE.md](references/REFERENCE.md)

See also

• fabric-database skill — full Azure SQL engine inside Fabric, none of these restrictions apply
• fabric-monitoring skill — Query Insights, query labels, DMVs, KILL, Result Set Caching, statistics
• fabric-security skill — GRANT/DENY/RLS/CLS/DDM SQL syntax for Warehouse
• fabric-auth skill — TDS connection essentials (port 1433, Initial Catalog vs FQDN, Encrypt=Yes)
• fabric-gotchas skill — cross-cutting error index

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: wardawgmalvicious
• Source: wardawgmalvicious/claude-config
• License: MIT

Install and usage instructions live in the source repository linked above.