Modernizing SQL ETL in Lakehouse with Declarative Patterns

Summary

Databricks is extending declarative ETL patterns into the Lakehouse SQL Editor so SQL practitioners can define recurring transformations without a dedicated pipelines-oriented environment. The initial operations cover append-only updates, change data capture through AUTO CDC, and targeted batch overwrites through REPLACE WHERE; flows can be scheduled, triggered by upstream updates, run on demand, or orchestrated through SQL tasks in Jobs. APPEND tracks processed source data, AUTO CDC handles keys, sequencing, deletes, and SCD Type 1 or Type 2 results, while Enzyme can incrementally process changed data for REPLACE WHERE. In Lakehouse benchmark testing, Enzyme-powered REPLACE WHERE ran 3.4x faster and 2.5x cheaper than traditional REPLACE WHERE, while users can retain procedural SQL and later move to the Lakeflow Pipelines Editor for multi-file development, dependencies, validation, and deployment.

Context

Many recurring ETL patterns, including appending records, applying CDC changes, and refreshing changed data, are easy to describe but difficult to operate because they require custom SQL logic, manual scheduling, and orchestration glue.

Approach / What changed

Declarative flows in the Lakehouse SQL Editor let users define append-only updates, AUTO CDC, and targeted REPLACE WHERE batch refreshes. Databricks handles state tracking, incremental processing, update logic, scheduling, orchestration, and schema evolution where applicable, while existing procedural SQL can remain in use.

Takeaways

• APPEND flows track new versus previously processed source data and incrementally append records without requiring a separately managed pipeline.
• AUTO CDC supports keys, sequencing, delete handling, and SCD Type 1 or SCD Type 2 results through declarative SQL.
• In Lakehouse benchmark testing, Enzyme-powered REPLACE WHERE ran 3.4x faster and 2.5x cheaper than traditional REPLACE WHERE.

Related reading