Busting SQL Migration Myths: How New SQL Features Make Lift-and-Shift to Lakehouse Easier

Legacy SQL stored procedures, often decades old and poorly documented, underpin critical business operations such as revenue reporting and financial close. These procedures frequently use cursors, temporary tables, and multi-statement transactions, making them difficult to migrate to modern platforms. Databricks now supports these procedural SQL features natively, allowing such procedures to be moved with minimal rewrites while maintaining their original logic and dependencies.

The migration process preserves key elements like error handling, temporary tables, and control flow. For example, error handling transitions from legacy syntax like BEGIN...EXCEPTION...END to Databricks’ DECLARE EXIT HANDLER FOR SQLEXCEPTION. Temporary tables are recreated using session-scoped CREATE TEMP TABLE, and cursors are supported directly in SQL scripting. These changes reduce the need for extensive rewrites, which previously required converting SQL to Python or Spark and introduced new bugs.

Control flow structures such as IF/ELSE, WHILE, and labeled loops are fully supported, enabling direct translation of legacy procedural logic. Transactions are handled using BEGIN ATOMIC...END, which provides automatic commit on success and rollback on failure, along with row-level conflict detection. This contrasts with legacy systems that use table-level locking, forcing serial execution. The MERGE statement and other SQL constructs can be migrated as-is, further simplifying the process.

Databricks reports that migration timelines for complex stored procedures can be reduced by 50-75% by preserving the original business logic. The migrated procedures are registered in Unity Catalog, gaining access controls, lineage tracking, and discoverability. Teams are encouraged to test the migration process using the Agentic Code Convertor on a small, low-risk stored procedure to validate the translation.