fabric-warehouse
Skill1 starsChanged 34 days agoMITView on GitHub
Use for T-SQL against Fabric Warehouse (NOT Fabric SQL Database — see fabric-database). Covers unsupported types (nvarchar/datetime/money/xml/tinyint/hierarchyid), unsupported features (FOR XML, recursive CTEs, triggers, CREATE USER, cursors), MERGE (GA Jan 2026), ALTER COLUMN (preview), schema evolution (ADD nullable / DROP COLUMN / sp_rename, IDENTITY GA Aug 2026 (bigint, RESEED), transactional ALTER TABLE GA April 2026, CTAS workaround), PK/UNIQUE/FK NONCLUSTERED+NOT ENFORCED only, 8060-byte row limit, CTAS Synapse-vs-Fabric rules (no DISTRIBUTION/CCI/variables), COPY INTO with AUTO_CREATE_TABLE + bcp (preview), OPENROWSET surface, snapshot-only isolation (24556/24706 retry), DDL in transactions (Sch-M blocks reads), Time Travel (UTC, single per SELECT; SQLEP preview) + Warehouse Snapshots (GA), sp_get_table_health_metrics (SQLEP), GPU acceleration (preview), Recycle-bin recovery, Git/CI-CD 2.0 (DacFx DataLoss sync block, .sqlproj SDK pin, SQLEP), pipeline calls via Script activity (NOT Stored Procedure).
- Deletes or force-pushes
In this repository38 other files it gives its agents.
- claude/CLAUDE.mdCLAUDE.md
- CLAUDE.mdCLAUDE.md
- author-skillSkill
- drift-auditSkill
- drift-handoffSkill
- drift-updateSkill
- test-skillSkill
- triageSkill
- fabric-iqSkill
- fabric-catalog-governanceSkill
- fabric-deploySkill
- fabric-rest-apiSkill
- fabric-securitySkill
- fabric-variable-librarySkill
- fabric-databaseSkill
- fabric-dataflowSkill
- fabric-data-pipelineSkill
- fabric-sparkSkill
- fabric-warehouse-monitoringSkill
- fabric-activatorSkill
- fabric-eventhouseSkill
- fabric-event-schema-setSkill
- fabric-eventstreamSkill
- fabric-realtime-dashboardSkill
- learnSkill
- fabric-semantic-model-auditSkill
- fabric-tmdlSkill
- pbip-project-structureSkill
- pbir-report-workflowSkill
- linkedin-highlightsSkill
- msix-packagingSkill
- code-reviewSkill
- commitSkill
- find-sessionSkill
- landSkill
- prune-branchesSkill
- recreate-repoSkill
- run-briefsSkill
The file
We split this skill into the rules it gives an agent
4 rules. About 0 are common, 2 are distinctive, and 2 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈2
Distinctive · estimate
This author's own choices. Worth a look.
2
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 50
No default value constraints.
- Very specific: line 110
Must be bigint; anything else errors.
- Very specific: line 150
Keep them outside anything Fabric syncs.
- Distinctive: line 213
No compute charge for retention.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
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— useFOR JSONinstead (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
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/CHECKnot supportedNOT NULLonly viaCREATE TABLE(cannot be added viaALTER TABLE)
Schema Evolution
Adding a nullable column, dropping a column, and sp_rename on a column or
table all work (April 2025+), as does adding/dropping NONCLUSTERED NOT
ENFORCED constraints. What constrains you:
| Operation | Status | Syntax |
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.
What a Git sync does to a populated table depends on the warehouse's
definition version, told by the item folder: xmla.json means 1.0, and
config.version in .platform reads 2.0 either way. Under 2.0 (Aug
2026) DacFx deploys with BlockOnPossibleDataLoss = true, so a synced DDL
change dropping a column from a populated table refuses: DataLoss: The
column [s].[t].[c] is being dropped, then Msg 50000 ... Rows were
detected. The schema update is terminating. The generated guard is
IF EXISTS (SELECT TOP 1 1 FROM [s].[t]) RAISERROR(...) — it fires on any
row in the table, so nulling the column first changes nothing. Run the
destructive half by hand (ALTER TABLE s.t DROP COLUMN c on the live
warehouse), then sync: the plan then holds no drop and no guard is
generated. Nothing else is protecting that data, so capture it first.
Documented for deployment pipelines and VS Code publish; observed on Git
update Sept 2026, with the pre-drop remedy field-confirmed. The rest of the
2.0 surface — the upgrade, the fixed DacFx settings, and the build-time
failures that pass sqlcmd — is in
references/platform-features.md.
Under 1.0 the failure ran the other way: sync could drop the data
silently. Adding a nullable column to a table's DDL file and syncing from
Git wiped the table's rows (observed 2026-09-03; sibling tables in the same
schema untouched). The docs describe sync as DacFx incremental deployment
but warn of "limitations with adding table constraints or columns", and the
IDENTITY_INSERT bullet under Limitations in Git integration implies a
table rebuild with re-insert — mechanism not documented. On a 1.0 warehouse
treat any synced DDL change to a populated table as destructive: capture
what you need first (for a control table, MAX(Watermark) per entity from
its run log — only statuses that actually advance the cursor, not ones that
merely park a pending window), then re-register from that, not from the
script's seed values. Corollary: a register proc whose UPDATE branch assigns
every column unconditionally NULLs any column the caller omits, so
re-registration scripts must carry every column they mean to keep.
The ALTER COLUMN preview conversion matrix (what widening is actually allowed,
and the Delta type-widening consequence for external readers) is in
references/schema-evolution.md.
IDENTITY Columns
GA August 2026.
- Must be
bigint; anything else errors. Cannot be added to an existing table viaALTER TABLE— use CTAS orSELECT ... INTO(both preserve the IDENTITY property on the target). It need not be the first column in the definition. - No custom seed or increment. The system manages values and always produces positive integers.
- Values are unique but not sequential — gaps are normal and expected. The
distributed engine allocates ranges per compute node, so even two sequential,
successful ingestion tasks get non-contiguous ranges. Surrogate keys only:
never read the value as an ordering or a row count. A used value is never
reissued unless
IDENTITY_INSERTis involved. sys.identity_columnslies here —seed_value/increment_valueare alwaysNULL, andlast_valueflips permanently to-1after the first identity insert. UseMAX()for a high-water mark.
Declaration syntax, and inserting explicit values (SET IDENTITY_INSERT plus a
mandatory DBCC CHECKIDENT(..., RESEED)):
references/schema-evolution.md.
Warehouse Authoring Rules
- Snapshot isolation only — write-write conflicts are detected at the table level. Serialize writes to the same table.
- MERGE is GA (January 2026) — it takes an Intent Exclusive (
IX) lock like other DML, but under snapshot isolation it conflicts with any concurrent DML on the same table, even append-only. Serialize writes, or fall back to DELETE + INSERT where concurrency is high. - CTAS over CREATE TABLE + INSERT — parallel, single-operation, faster. It
differs from Synapse dedicated pools: no
DISTRIBUTIONor columnstore hints (both engine-managed), no explicit column definitions, no variables;WITH (CLUSTER BY (...))is supported. Full porting delta in references/t-sql-surface.md. - TRUNCATE TABLE over DELETE FROM (without WHERE) — faster, and preserves time-travel history.
- INSERT...SELECT over singleton INSERT...VALUES at scale — singletons
create tiny Parquet files. Remediate existing fragmentation with
CREATE TABLE T_Clean AS SELECT * FROM T; DROP TABLE T; EXEC sp_rename 'T_Clean', 'T'; - Never put a hand-authored
.sqlinside a*.Warehousefolder. Fabric treats that folder as the serialization of the live warehouse, so a script the warehouse does not contain is drift to be deleted — 13 of them (~3,300 lines) left a trunk in a single portal commit. Keep them outside anything Fabric syncs. See thefabric-git-serializationrule. - Keep transactions short to shrink the conflict window. Error 24556 / 24706 = snapshot conflict → serialize and retry with exponential backoff.
- Ingestion:
COPY INTOfor external files (highest throughput),OPENROWSETin-engine.bcpis preview;BULK LOADis not supported, whileBULK INSERTworks for compatibility, mapped toCOPY INTO(Learn, 2026-10-06). Options and file-size guidance: references/t-sql-surface.md.
Snapshot Isolation Conflict Matrix
| Scenario | Outcome |
| INSERT vs INSERT (same table) | Usually safe (appends new Parquet files) |
| UPDATE / DELETE vs UPDATE / DELETE | First committer wins; others fail with error 24556 / 24706 |
| MERGE vs any DML | Always conflicts (even append-only MERGE) |
| DML vs background compaction | Compaction can trigger conflict if it commits first |
Mitigation: serialize writes per table, or use INSERT-only patterns (append then reconcile). Retry with TRY/CATCH around the DML and exponential backoff.
Transactions
- ACID via snapshot isolation exclusively (
SET TRANSACTION ISOLATION LEVELis ignored). - DDL is allowed inside transactions —
CREATE/DROP/TRUNCATE TABLE, CTAS,sp_rename, and every supportedALTER TABLEvariant (including on distributed temp tables, and several in one transaction). GA April 2026: any failure rolls every schema change back atomically. - That atomicity is yours only when you run the T-SQL. Fabric's own Git
sync and deployment pipelines generate DacFx scripts with
IncludeTransactionalScripts = false, so a multi-statement schema change applied by sync can stop half-way. (Thedeploy-pipelinespage still says warehouses "don't support wrapping DDL scripts inside transactions" — stale against the April 2026 GA, but the setting is real.) - DDL takes a Sch-M lock at table level, blocking concurrent DML and
SELECT — including queries against
sys.tables/sys.objects. Schedule schema changes for maintenance windows; inspect contention withsys.dm_tran_locks. - Cross-database transactions work within a workspace; rollbacks are fast (metadata-only).
- Not supported: savepoints, named transactions, distributed transactions, nested transactions.
-- Atomic multi-step schema migration (April 2026 GA)
BEGIN TRAN;
ALTER TABLE dbo.FactSales ADD UnitCostUSD decimal(19,4) NULL;
ALTER TABLE dbo.FactSales DROP COLUMN LegacyCost;
COMMIT;Time Travel
SELECT * FROM dbo.FactSales
OPTION (FOR TIMESTAMP AS OF '2026-03-01T08:00:00.000');- 30 days of history by default — configurable 1–120 per warehouse (preview); the windows for all three mechanisms are in references/time-travel-and-recovery.md. No compute charge for retention; storage does bill.
- Timestamp must be UTC.
- Appears once per SELECT — all tables see the same point in time.
- Cannot be used in
CREATE VIEWdefinitions (you can query views with it). - Returns the current schema — dropped columns won't appear. Drop + recreate resets history.
- DML time travel (the hint on
INSERT...SELECT/ CTAS /SELECT INTO) is Warehouse-only.
Named Warehouse Snapshots, SQL analytics endpoint time travel (preview, different retention rules), and dropped-warehouse recovery via the workspace Recycle bin are three separate mechanisms with three different windows — see references/time-travel-and-recovery.md.
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.
Because it is case-sensitive, write built-in type names lowercase in
item definitions — sysname, never SYSNAME. See coding-tsql —
Casing.
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.
Beyond T-SQL authoring
- Table maintenance — check-then-act
OPTIMIZEviasys.sp_get_table_health_metrics: references/table-health-metrics.md. - Feature/GA matrix, GPU query acceleration, source control and CI/CD —
references/platform-features.md. Query
Acceleration is a workspace-wide toggle on a higher billing meter that
cancels running queries when flipped. Source control covers definition
2.0 (Aug 2026): the upgrade, the fixed DacFx deployment settings, and the
build-time failures that pass
sqlcmdand fail the sync.
Reference
- Microsoft Learn: What is Fabric Data Warehouse?
- Full MS Learn link bundle (concept / connect / tables / ingestion / performance / monitoring / security / backup-restore / CI-CD): references/REFERENCE.md
See also
- fabric-database skill — full Azure SQL engine inside Fabric, none of these restrictions apply
- fabric-warehouse-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-rest-api skill — TDS connection essentials (port 1433, Initial Catalog vs FQDN, Encrypt=Yes) in its
references/auth.md, and the cross-cutting error index in itsreferences/gotchas.md
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

