Migrate T-SQL to Databricks.

Why T-SQL to Databricks is a dialect crossing, not a copy

Moving T-SQL onto Databricks looks deceptively simple (it’s SQL going to a SQL engine), but it crosses a dialect boundary. T-SQL and Spark SQL share keywords and diverge underneath them. A naive lift-and-shift that pastes a stored procedure into a notebook will fail on the first GETDATE(), the first ISNULL, the first TOP n, the first [bracketed] identifier. Worse, the failures that don’t throw (the ones that run and return subtly wrong numbers) are the expensive ones. The agent fleet treats this pair as a rebuild that preserves behavior, not a syntax find-and-replace, and it proves equivalence with a test suite rather than asserting it.

The bulk of the business logic in a Microsoft estate usually sits in the T-SQL layer: stored procedures, views, scalar and table-valued functions, and ad-hoc batches. The Documenter reads that layer for what it does, not just what it is named. It separates business logic from plumbing, traces dependency chains across objects and databases, and flags the constructs that will not survive the move unchanged: dynamic SQL, cursors and procedural loops, cross-database and linked-server references, and dialect-specific built-ins. That documentation becomes the contract the builders work against and the Reconciler checks against.

What actually breaks in a lift-and-shift

Three categories drive the rewrite, and each is in the legacy code precisely because it was convenient in SQL Server.

Procedural control flow. Long imperative procedures - cursors, WHILE loops, IF/ELSE branching, multi-step transactions, temp tables - are the costliest to convert. Historically Spark SQL had no procedural form at all, which is why such procedures often map to notebooks or PySpark. That has changed: Databricks now supports SQL scripting (the SQL/PSM standard) inside BEGIN...END blocks with IF, CASE, WHILE, REPEAT, LOOP, FOR, LEAVE/ITERATE, local variables and condition handlers that SIGNAL/RESIGNAL. It also supports native CREATE PROCEDURE stored procedures in Unity Catalog. But these arrived in recent Databricks Runtime versions, and cursor processing (OPEN/FETCH/CLOSE) is gated to an even more recent one. The fleet never assumes a Runtime; the Architect confirms it at the design gate, and where the feature isn’t present, set-based rewrites and PySpark fill the gap.

Dynamic SQL. Strings assembled at runtime and run through EXEC() or sp_executesql are the single most common home for undocumented behavior, because the executed statement is never present statically. The Documenter traces how each string is built before anything is rebuilt. Where the dynamic shape was really a template, it becomes compile-time Jinja on the dbt path; where it must run at runtime, the your-framework path uses EXECUTE IMMEDIATE in Spark SQL or spark.sql(...) in PySpark. The parameterization and security model differ from sp_executesql, so this is reviewed case by case rather than mechanically translated.

Constructs with no clean equivalent. A T-SQL MERGE maps to Delta Lake MERGE INTO - semantically close, but Delta-specific and without the T-SQL OUTPUT clause. Triggers have no equivalent and must be re-expressed as explicit pipeline or job steps. Scalar UDFs and multi-statement table-valued functions become SQL UDFs, Python UDFs, or set-based logic, with different semantics and performance. Three- and four-part / linked-server names become Unity Catalog’s catalog.schema.table three-level namespace and federation - a redesign, not a port. IDENTITY and sequences map to Delta GENERATED ... AS IDENTITY or hash-based surrogate keys, which behave differently from their T-SQL counterparts.

Two flavors, one provable result

Both delivery flavors land on Delta Lake under Unity Catalog; what differs is the shape of the deliverable. On the dbt path, each transformation is a SELECT-based model and dbt owns materialization, run order via ref(), and schema management. Upserts become incremental models with incremental_strategy='merge' (the Delta default), which compiles to MERGE INTO on a unique_key, SCD Type 1 overwrite semantics. History-preserving SCD Type 2 becomes a dbt snapshot, which maintains dbt_valid_from, dbt_valid_to and dbt_scd_id and requires the Delta file format on Databricks. Imperative procedures are decomposed into ordered, idempotent models, with Python models reserved for logic pure SQL can’t express. Runtime control flow lives in the orchestrator, never in a model: Jinja is compile-time templating, not runtime branching.

On the your-framework path, the fleet builds into the way your team already ships on Databricks: Spark SQL and PySpark notebooks, SQL scripting blocks, native stored procedures, and Lakeflow Jobs for the orchestration that nested procedures used to carry. The before/after above shows the same hand-rolled SCD2 procedure two ways, as a declarative dbt snapshot, and as an effective-dating MERGE INTO in a notebook task.

Whichever flavor you choose, the loop is the same. The Operator deploys to DEV and runs for real; the Test Agent asserts schema parity, row-count reconciliation, SCD effective dating, and null and key integrity; the Reconciler runs legacy and rebuilt side by side until the delta is zero or explained. A failure goes back to the builders, not onto a backlog, and the loop re-runs, iterating until green. Autonomous in the loop, accountable at the gates: your experts hold the assessment, design and promotion gates, and the build-test-run loop does the iterating in between. If your target is Microsoft Fabric instead, the same approach is described in Migrate T-SQL to Microsoft Fabric.