Migrate T-SQL to Microsoft Fabric.

T-SQL onto the Microsoft Fabric Warehouse: continuity, with real edges

Of every legacy Microsoft pairing, T-SQL to Microsoft Fabric has the most continuity. The Fabric Warehouse is primarily developed with T-SQL, shares a large surface area with the SQL Database Engine, and gives you full multi-table ACID transactions, views, functions and stored procedures over Delta tables in OneLake. So the instinct (“it’s T-SQL on both sides, just copy it across”) is half right, and the half that’s wrong is where migrations quietly fail.

The Warehouse is a subset of the T-SQL you know, and the gaps are exactly the constructs procedural estates lean on. There are no triggers, no materialized views, no synonyms, no recursive queries, no SELECT ... FOR XML, no SET ROWCOUNT, no SET TRANSACTION ISOLATION LEVEL, and no manually created multi-column statistics. ALTER TABLE is limited to a subset: add nullable columns, drop columns, add or drop keys, all NOT ENFORCED. CLR is not available, so any procedure with EXTERNAL NAME has no in-engine home. A lift-and-shift that ignores this list compiles right up until the object that depends on one of these features, and then it’s a runtime surprise instead of a design decision.

That is why the fleet documents the logic, not just the labels. The T-SQL Documenter dissects the procedural layer (stored procedures, views, functions) separating business logic from plumbing, tracing dependency chains across objects and databases, and flagging the dialect quirks and dynamic SQL that need careful handling on a new engine. The Architect then decides what each object becomes on Fabric and writes it into the Target Design Spec your experts sign off at the design gate. Nothing in the surface-area gap reaches a build without a human having seen it first. Autonomous in the loop, accountable at the gates.

What a naive lift-and-shift breaks

Four things go wrong most often, and all four are visible in the iteration log for this pair.

Procedural flow assumed, not modelled. A 400-line stored procedure encodes execution order inside IF/WHILE/BEGIN...END. Copy it verbatim and you’ve copied the order too, but none of the testability. The dbt flavor takes the more deliberate path: it decomposes the batch into ordered, idempotent models wired together with ref() and source(). Those calls are the edges of a DAG dbt builds in topological order, so an incremental fact model that ref()s its staging models always runs after them, with no manual sequencing. The procedure stops being a black box and becomes a graph you can test node by node.

Constraints presumed enforced. On a SQL Server box a FOREIGN KEY rejects an orphan row. On the Warehouse the same key is metadata only - NOT ENFORCED - so it documents intent without policing inserts. The fix is to move that guarantee into tests: unique, not_null, accepted_values and relationships on the dbt side (built from tsql-utils, since dbt-utils isn’t supported by the adapter), or equivalent assertions in your framework. Integrity becomes something checked every run, not something the engine silently stopped doing.

Dynamic SQL transliterated. Dynamic SQL is the most common place for undocumented behaviour - the executed statement isn’t present statically, so it has to be traced through the variable that builds it. The fleet splits it by when the SQL is decided. Compile-time variation (column lists, environment suffixes) becomes a dbt Jinja macro, so the generated statement is reviewable in the build artifact. Runtime variation stays as dynamic T-SQL inside a Warehouse procedure. Both are reconstructed and documented; neither is left as an opaque EXEC(@sql).

Triggers and recursion carried over. A DML trigger that writes an audit row has no Warehouse equivalent, and a recursive CTE that walks a hierarchy won’t run. The Operator catches both in the build-test-run loop: the run fails, the failure routes back to the builders, the trigger becomes an explicit step and the recursion becomes bounded staged models - and the loop re-runs until the suite is green.

MERGE, SCD2, and the two flavors

Upserts land cleanly: MERGE is generally available in the Warehouse, so a SCD Type 1 upsert ports almost as-is on the your-framework path, wrapped in BEGIN TRAN / COMMIT TRAN for atomicity. On the dbt path the same intent becomes an incremental model whose default merge strategy generates that T-SQL MERGE for you, keyed on your unique_key. History is where the flavors diverge most usefully: hand-rolled SCD Type 2 history tables become dbt snapshots, which close the old row and insert the new one with dbt_valid_from / dbt_valid_to / dbt_scd_id maintained for you, or, in your framework, a MERGE-based effective-dating procedure that does the same thing in T-SQL you can read.

The Fabric Test Agent then writes the suite (schema parity, row-count reconciliation, SCD effective dating, null and key integrity) and the Reconciler runs legacy and rebuilt side by side until the delta is zero or explained in writing. The deliverable is the same whichever flavor you pick: the same source logic, the same tests, proven against the procedure it replaced.

If your estate also has a Spark-shaped future, the same source logic can target the other engine, see Migrate T-SQL to Databricks for how the procedural layer maps onto Delta and Unity Catalog instead.