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

AI 服務暫時不可用，以下為來源正文，待恢復後補全翻譯。

Busting SQL Migration Myths: How New SQL Features Make Lift-and-Shift to Lakehouse Easier | Databricks Blog Skip to main content The cursor resides in the procedure scheduled to run every night. The temp table no one documented. The transaction that bundles three updates and rolls back if any one fails. All of it migrates now, line for line. You translate the procedure. You don't rewrite it. PL/SQL maps to Databricks SQL Scripting piece by piece, same business logic, same control flow, same SQL team. The procedure ends up in Unity Catalog with lineage and access control. Governance the original schema never had. Somewhere in your warehouse, hundreds of stored procedures wake up every night and quietly keep the business running. They were written years ago by a bunch of SQL developers who have long since left the company. They have nested cursors. They create temporary tables on the fly. They bundle updates across multiple tables into a single transaction. And somewhere around line 47, there is a comment that simply says: “Do not change this.” No one fully understands these procedures anymore. Yet everyone depends on them. The revenue dashboard, the finance close, the operations report, all of them, in one way or another, trace back to these layers of procedural SQL business logic. Moving data to the lakehouse is well-understood. The friction has been the procedural core of any data warehouse migration: the stored procedures, transaction handling, temp tables, control flow, and the fact that much of the enterprise still runs on SQL skills. Every time a migration came up, these procedures became the first thing everyone pointed to: “We cannot move until we can run that with minimal changes. Our enterprise is still heavily SQL-driven.” So we decided to take a use case like the one you are probably thinking of right now, a composite procedure we have seen across migrations, and demonstrate it, piece by piece, on Lakehouse. This example is based on an Oracle migration use case, but it can be applied to any data warehouse (legacy or cloud-based). Take the original business logic This example procedure processes daily orders. It stages unprocessed orders into a temp table, validates them against the customer master, loops through failures to log each rejection individually, then updates regional revenue summaries and marks all orders as processed, all within a transaction that rolls back on failure. One nightly job that cannot be broken. Earlier, migrating this meant rewriting it entirely, in Python and Spark. Weeks of work, new bugs to find, and a SQL team that could no longer maintain their own business logic. We did not rewrite it. We translated it. Now lay the foundation on Databricks Every procedure starts with a signature and a safety net. The legacy wrapped the body in BEGIN ... EXCEPTION ... END. Databricks uses DECLARE EXIT HANDLER FOR SQLEXCEPTION instead; same idea, slightly different syntax. Let’s assume the appropriate catalog and the schema have been set in the session. The big difference is not in the code. It is what happens after deployment. On Databricks, the procedure is registered in Unity Catalog. It gets access controls, column-level lineage, and discoverability across every workspace. In the current system, it lived in a schema that three people had the password to. Legacy Databricks CREATE OR REPLACE PROCEDURE name IS CREATE OR REPLACE PROCEDURE [IF NOT EXISTS] .. ( [ procedure_parameter [, ...] ] ) [ characteristic [...] ] LANGUAGE SQL SQL SECURITY { INVOKER | DEFINER } AS BEGIN v_id NUMBER; before BEGIN DECLARE v_id INT; inside BEGIN EXCEPTION WHEN OTHERS THEN DECLARE EXIT HANDLER FOR SQLEXCEPTION Reference: docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-procedure Then we tackled the temporary tables: the easy win in a data warehouse migration The original procedure creates two temporary tables for staging and validation failures. They are the scratch space that the rest of the logic depends on. On Databricks, this becomes one of the simplest parts of the migration. No EXECUTE IMMEDIATE. No ON COMMIT PRESERVE ROWS. The session-scoped CREATE TEMP TABLE is the direct replacement with one small caveat: CREATE OR REPLACE TEMP TABLE is not yet supported, so drop first if you need to be re-runnable in the same session. Reference: docs.databricks.com/aws/en/tables/temporary-tables The cursor was the hard part — or so we thought This was the piece everyone assumed would require a rewrite. The original procedure loops through validation failures one by one, rejects each bad order, and logs the reason. Classic cursor pattern. Decades of legacy (Oracle, for example) muscle memory. Databricks’ SQL scripting supports cursors natively, OPEN, FETCH, and CLOSE since Runtime 18.1. The %NOTFOUND attribute becomes a CONTINUE HANDLER FOR NOT FOUND. Loop labels and LEAVE replace EXIT WHEN. The scripting logic was a non-event The conditional check, if there are no rows to process, skip and log, barely changed. SELECT ... INTO becomes SET var = (SELECT ...). Everything else is identical. Our SQL scripting supports the full procedural toolkit: IF/ELSE, WHILE, FOR, LOOP, REPEAT, LEAVE, ITERATE, SIGNAL/RESIGNAL. If your codebase contains Teradata BTEQ scripts with .GOTO and .LABEL directives map to labelled loops using LEAVE and ITERATE. Reference: docs.databricks.com/aws/en/sql/language-manual/sql-ref-scripting The transaction was the moment it became real This was the last piece, the one that made the migration actually viable. The original procedure updates regional_revenue, marks orders as processed, and logs the batch. If any part fails, everything rolls back. On the legacy system, this is an implicit transaction with an explicit COMMIT. On Databricks, BEGIN ATOMIC ... END provides the same semantics, automatic commit on success, automatic rollback on failure, with one significant advantage: row-level conflict detection. Concurrent batches writing to the same table only conflict if they touch the same rows. For instance, Oracle and Snowflake both use table-level locking, which forces serial execution. The MERGE statement can be migrated to Databricks as is. The explicit COMMIT disappeared as BEGIN ATOMIC handles it. And the team stopped worrying about concurrent batch jobs stepping on each other. Two practical notes when you adopt this pattern: Every table defined within an atomic block must have the catalogManaged table feature enabled. You can enable it on existing Delta tables in place: ALTER TABLE SET TBLPROPERTIES('delta.feature.catalogManaged'= 'supported'); BEGIN ATOMIC belongs at the top level — in a SQL script, a notebook cell, or a SQL job task. Reference: docs.databricks.com/aws/en/transactions/ The complete migrated procedure Same business logic. Same control flow. Governed by Unity Catalog. To run it inside a transaction, wrap the call: What we learned Migration timelines for these programs can be slashed by 50-75%, even for complex stored procedures with heavy PL/SQL package dependencies. This efficiency stems from a mechanical translation process that preserves the original business logic, ensuring the SQL team can seamlessly continue their maintenance work. Beyond the migration itself, teams gain a powerful new advantage: a unified platform where the same governed data powers their dashboards, machine learning models, and AI initiatives. The only way to know if your procedures translate is to try one. Pick the smallest stored procedure in your batch, preferably one no one loves debugging. Create a migration project in your workspace and get started with the Agentic Code Convertor! Get the latest posts in your inbox Subscribe to our blog and get the latest posts delivered to your inbox. Sign up View all blogs