Mercivensproc-to-lakehouse
A tool that reads legacy SQL Server stored procedures, decides what can move to Databricks automatically, writes the Databricks code (a dbt project and notebooks), and then proves the new code returns the same data as the old.
Everything on this page comes from running the tool on 12 synthetic stored procedures for a fictional specialty pharmacy. No client, company, drug, prescriber or patient data is used, and no number here is an estimate.
From stored procedure to proven Databricks code
Each procedure is parsed and every construct is classified before any code is written. Anything the tool cannot convert safely becomes a marked manual step, not a guess.
- T-SQL inStored procedures plus the table definitions (DDL)
- Analyze41 rules mark each construct: automatic, automatic with a note, or needs a person
- PlanOrdered steps, with fixes for what a plain translation gets wrong
- GenerateA dbt project and one Databricks notebook per procedure
- Spark checkEvery generated statement runs or resolves on Apache Spark
- Parity testOld and new code run on the same data; results compared row by row
Same data in, same data out
The original procedure ran on Microsoft SQL Server 2022. The generated code ran on Apache Spark 4.0.1 with Delta Lake. Both started from the same 3,576 synthetic rows across 16 tables. Every table each procedure writes was compared on row counts, per-column checksums and row-level differences by primary key.
| Procedure and table | Rows (SQL Server / Spark) | Columns matching | Changed by the run | With fixes | Fixes switched off |
|---|---|---|---|---|---|
| Loading results… | |||||
“Changed by the run” counts rows the procedure inserted (+), updated (~) and deleted (−) on SQL Server. A match on rows the procedure actually changed is real evidence; a match on untouched data would prove nothing. The six partly converted procedures are not run until a person converts their manual steps.
What converted, and what needs a person
Select a procedure to see its original T-SQL next to the generated dbt model and Databricks notebook, with every finding and the reason for it.
| Procedure | Verdict | Findings | Parity | |
|---|---|---|---|---|
| Loading procedures… | ||||
6 procedures convert fully. The other 6 hold 9 manual steps in total, for constructs such as cursors, dynamic SQL, transactions and error handling, where a person should decide how the logic belongs on Databricks.
What a plain SQL translation gets wrong
The tool uses sqlglot, an open-source SQL translator, for single statements. Translated as-is, these cases fail or give different answers on Databricks. Each error was reproduced on Apache Spark; four of them are why the plain translation matched only 2 of 6 procedures.
| T-SQL | Plain translation on Spark | What the tool does | Procedures |
|---|---|---|---|
'ID-' + CAST(n AS VARCHAR) | treated as numeric addition: DATATYPE_MISMATCH | rewritten to CONCAT(...) | 06, 11 |
TRY_CONVERT(DATE, s, 101) | becomes TO_DATE, which fails on bad input: CANNOT_PARSE_TIMESTAMP | CAST(TRY_TO_TIMESTAMP(s, 'M/d/yyyy') AS DATE) | 03 |
DATEPART(ISO_WEEK, d) | becomes EXTRACT(WEEKISO ...): INVALID_EXTRACT_FIELD | EXTRACT(WEEK FROM d) | 07 |
UPDATE t ... FROM t JOIN s | Databricks UPDATE has no FROM: PARSE_SYNTAX_ERROR | MERGE INTO, de-duplicating the source when safe | 02, 08, 09 |
TRUNCATE TABLE t | open-source Delta Lake rejects it: Table does not support truncates | DELETE FROM t | 04, 06 |
flag_bit = 1 | BIT arrives as BOOLEAN: DATATYPE_MISMATCH | flag_bit = TRUE, CAST(... AS BOOLEAN), using the table DDL | 03, 04, 05, 07, 10, 11, 12 |
ISNULL(d, '19000101') | string cannot be cast to DATE: CAST_INVALID_INPUT | CAST('1900-01-01' AS DATE), using the table DDL | 03 |
STUFF(s, 4, 3, '***') | passed through unchanged: UNRESOLVED_ROUTINE | flagged for a person (needs review) | 06, 11 |
DATEDIFF(DAY, 23:00, 01:00 next day) | returns 0 on Spark; SQL Server returns 1 | converted with a review note | 04, 07 |
What this does not prove yet
A real Databricks workspace
Testing used open-source Apache Spark and Delta Lake on a single machine. The code has not yet run in a Databricks workspace or through a real dbt build.
Production data
Parity was tested on 3,576 seeded synthetic rows that include awkward cases (bad dates, duplicates, missing values). Real data will hold cases this data does not.
The six partly converted procedures
They are flagged with the exact steps a person must convert. Their parity is not tested until that work is done.
Constraints
Delta Lake does not enforce primary keys the way SQL Server does. Testing caught this; a migration needs explicit checks to replace them.
Moving stored procedures to Databricks?
We can assess your procedures, convert what is safe to convert, and prove the results match before anything goes live.