Skip to content

DATABRICKS 2026-08-20

Read original ↗

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

Summary

Databricks walks through migrating a composite nightly stored procedure — the procedural core of a classic Oracle/data-warehouse workload (staging temp tables, cursor loops over validation failures, regional-revenue rollups, all wrapped in a rollback-on-failure transaction) — onto the Lakehouse by translating the procedure rather than rewriting it in Python/Spark. Most of the post is a syntax-mapping how-to (legacy BEGIN ... EXCEPTION → DECLARE EXIT HANDLER FOR SQLEXCEPTION, CREATE TEMP TABLE, native SQL-scripting cursors OPEN/FETCH/CLOSE since Runtime 18.1, the full procedural toolkit IF/WHILE/FOR/LOOP/LEAVE/SIGNAL), which is product-usage material. The one genuinely architectural nugget — and the reason this source is ingested — is the transaction concurrency model: Databricks' BEGIN ATOMIC ... END gives the same all-or-nothing commit/rollback semantics as a legacy transaction but with row-level conflict detection (optimistic concurrency), whereas Oracle and Snowflake use table-level locking that forces concurrent batches to serialize. The mechanism is gated on the catalogManaged Delta table feature.

Key takeaways

  1. BEGIN ATOMIC ... END is the transaction primitive. It provides automatic commit on success and automatic rollback on failure — the same contract as a legacy implicit transaction with explicit COMMIT — so the explicit COMMIT disappears from migrated code. (Source: this article)

  2. Row-level conflict detection is the differentiator vs Oracle/Snowflake. Per the source, "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." This is the table-level vs row-level locking trade-off stated as a head-to-head comparison, and it is why the migrated nightly job stops serializing concurrent batch jobs against each other. (Source: this article)

  3. The concurrency guarantee is gated on a Delta table feature. "Every table defined within an atomic block must have the catalogManaged table feature enabled." It is enabled in place on existing Delta tables via ALTER TABLE <name> SET TBLPROPERTIES('delta.feature.catalogManaged'='supported'). This ties the transaction semantics to catalog-managed commits — the catalog is the commit coordinator that makes row-granular, multi-statement atomicity safe. (Source: this article)

  4. BEGIN ATOMIC must sit at the top level — a SQL script, a notebook cell, or a SQL job task — not nested arbitrarily inside other constructs. An operational placement constraint worth noting when porting deeply-nested procedural code. (Source: this article)

  5. MERGE migrates as-is. The upsert-style MERGE statement that the batch job uses to update regional-revenue summaries needs no rewrite on Databricks. (Source: this article)

  6. Registration in Unity Catalog is the post-migration payoff, not the code. "The big difference is not in the code. It is what happens after deployment." The translated procedure is registered in Unity Catalog with SQL SECURITY { INVOKER | DEFINER }, gaining access controls, column-level lineage, and cross-workspace discoverability — versus the legacy reality of "a schema that three people had the password to." (Source: this article)

  7. Temp tables have a re-runnability caveat. Session-scoped CREATE TEMP TABLE is the direct replacement for legacy staging scratch space, but CREATE OR REPLACE TEMP TABLE is not yet supported — you must drop first to be re-runnable within the same session. (Source: this article)

  8. Claimed migration-timeline reduction: 50–75%, even for complex stored procedures with heavy PL/SQL package dependencies, attributed to a mechanical translation process that preserves business logic so the SQL team can keep maintaining it. This is a vendor efficiency claim, not a measured serving-infra metric. (Source: this article)

Systems / concepts extracted

  • Systems: Delta Lake (the catalogManaged table feature backs atomic-block transactions), Unity Catalog (procedure registry + commit coordinator + governance), Databricks SQL (the engine that runs SQL-scripting procedures and atomic blocks).
  • Concepts: table-level-vs-row-level-locking (new — the head-to-head locking-granularity trade-off), row-level-concurrency (the underlying property), optimistic concurrency control (the mechanism BEGIN ATOMIC relies on), concepts/open-table-format (the feature-gate that makes it safe).

Operational numbers / caveats

  • Cursors supported natively since Databricks Runtime 18.1 (OPEN/FETCH/ CLOSE; %NOTFOUND → CONTINUE HANDLER FOR NOT FOUND; loop labels + LEAVE replace EXIT WHEN).
  • Migration-timeline reduction: 50–75% (vendor claim, unqualified by workload size or measurement method).
  • Caveat — scope. This is a Tier-3 product/how-to post. The bulk (SQL-syntax mapping tables, temp-table/cursor/control-flow translation) is product-usage material with no distributed-systems internals. Only the transaction-concurrency section carries citable architecture; the row-level-vs-table-level locking comparison and the catalogManaged gate are the load-bearing claims. The post does not disclose the conflict-detection implementation (deletion vectors, row tracking) — those are inferred from sibling Delta concept pages.
  • Caveat — comparison basis. The "Oracle and Snowflake use table-level locking" claim is stated by Databricks (a competitor) without benchmark or citation; treat as a vendor-framed characterization of competitors' concurrency models, not an independently verified fact.

Source

Last updated · 766 distilled / 2,225 read