Novuz

Guide

How to move SQL Server stored procedures to Databricks

To move SQL Server stored procedures to Databricks, translate the ones that are already set-based and redesign the ones built on cursors, temp tables and row-by-row logic. Most statements translate directly to Spark SQL. A few T-SQL constructs do not, and two differences change results without causing an error. This guide gives the decision, the pattern map, the steps, and how Novuz converts and proves each procedure on Azure Databricks or any other Databricks cloud.

The decision

When should you translate a procedure, and when redesign it?

The procedure…Do thisWhy
Is set-based SQL with joins and aggregatesTranslate directlySpark SQL has an equivalent for most statements
Uses temp tables for stagingTranslate, with temp views or Delta tablesThe pattern survives; the object type changes
Loops with a cursorRedesign as set-based SQLRow-by-row logic is slow and unnatural on Spark
Builds SQL from strings at run timeRedesign, with parametersDynamic SQL hides logic from any converter
Uses linked servers, CLR or Service BrokerReplace or retireThese have no Databricks equivalent
Is not called by anythingDo not move itDead code should leave the scope

Pattern map

What does each T-SQL pattern become on Databricks?

T-SQLOn Databricks
Temp tables (#temp)Temporary views or Delta tables
Cursors and WHILE loopsSet-based SQL, or PySpark where order matters
SELECT INTOCREATE TABLE AS
TOPLIMIT
TRY … CATCHError handling rebuilt in the job, with retries in Databricks Workflows
MERGE and identity columnsMERGE on Delta tables; identity columns or generated keys
Three-part cross-database namesUnity Catalog three-level names
Table hints such as NOLOCKRemoved; Delta reads a consistent snapshot
SQL Agent jobsDatabricks Workflows with the same dependencies

Silent differences

Which differences change results without an error?

  • Case sensitivity. T-SQL comparisons are case-insensitive by default; Spark’s are not. A join on a text key can silently lose rows.
  • NULL and empty-string handling in comparisons and concatenation.
  • Implicit conversions. T-SQL converts types quietly where Spark may return NULL or fail.
  • Rounding and decimal precision in calculations.
  • Row order. Code that relied on an order it never stated can return different rows.

These are why a converted procedure has to be run against the original. Code that compiles can still be wrong.

The steps

How do you convert SQL Server stored procedures to Databricks, step by step?

  1. Inventory every procedure, function and SQL Agent job, with what calls each one.
  2. Remove the dead procedures from scope.
  3. Band each procedure: translate, redesign or replace.
  4. Run a free converter, such as Databricks Lakebridge, on the plain T-SQL.
  5. Convert the procedural code: cursors, error handling, dynamic SQL and temp tables.
  6. Rebuild the SQL Agent schedules as Databricks Workflows.
  7. Run each converted procedure and its original on the same inputs, and compare the outputs.
  8. Cut over one domain at a time, when its procedures all pass.

Tools

Which tools convert SQL Server procedures to Databricks?

  • Databricks Lakebridge: free. Its Morpheus transpiler is the one Databricks recommends for T-SQL.
  • Databricks Genie Migrate: an AI agent converts batches of T-SQL files in the workspace; test the results on real data.
  • Partner platforms such as Next Pathway SHIFT and LeapLogic: many sources, with their own testing products.
  • Novuz: converts procedures, SSIS packages and SQL Agent schedules by fixed rules, and reconciles every object.

With Novuz

How does Novuz convert stored procedures without rebuilding every workflow?

Novuz parses each procedure and converts it by fixed, versioned rules into Databricks SQL or PySpark, so the same input gives the same output. About 95% of objects convert this way with no model call. Edge cases go to a resolver, and an engineer approves each proposal.

Every converted procedure then runs against the original on the same inputs and gets a result: pass, fail or review. The SQL Agent schedules are rebuilt as Databricks Workflows in their original order. Novuz is a Databricks validated technology partner and runs inside your own cloud account.

The 95% is the share converted by rules. An object counts as automated only if it needs no manual edit, and a one-line fix counts as manual.

FAQ

Frequently asked questions

What is the best way to convert SQL Server stored procedures to Databricks?

Translate the set-based procedures directly, and redesign the ones built on cursors and dynamic SQL. Run the free Lakebridge transpiler on the plain T-SQL first. Then convert the procedural code and schedules, and run each converted procedure against the original on the same inputs. Novuz does the conversion by fixed rules and reconciles each procedure.

Does Databricks support stored procedures like SQL Server?

T-SQL procedures do not run on Databricks unchanged. Set-based statements translate to Databricks SQL, cursors become set-based logic or PySpark, and error handling such as TRY … CATCH is rebuilt in the job. Each converted procedure should be run against the original to confirm it.

Which partner should we use to migrate SQL Server to Databricks?

There is no single answer. A systems integrator usually runs the program, and a conversion product converts the code. Novuz is a Databricks validated technology partner whose software converts stored procedures, SSIS packages and Agent jobs and proves each one; it works alongside your integrator or your own team.

Do we have to rebuild every workflow when moving to Databricks?

No. SQL Agent jobs and their dependencies can be converted into Databricks Workflows. Novuz rebuilds them in their original run order, so old and new can run in parallel before cutover.

Does this work on Azure Databricks?

Yes. The converted code is native Databricks SQL, PySpark and Workflows, so it runs on Azure Databricks, and on Databricks on AWS or Google Cloud.

How we measure: Methodology

Written by Ted Ghose, Co-founder and CTO, Novuz

Last reviewed:

Find out what your logic debt is costing you.

Every object counted, lineage, dead code and a fixed price in three business days. $3,000, credited.

Take the logic debt scorecard

Not sure which door? The scorecard takes ten minutes and tells you.

Scorecard