Modernizing SQL ETL in Lakehouse with Declarative Patterns
Databricks is introducing declarative SQL ETL patterns in Lakehouse, enabling SQL analysts to define append, CDC, and batch overwrite operations directly in their queries without dedicated pipeline tools.
Databricks is expanding declarative ETL capabilities within Lakehouse, allowing SQL practitioners to define common transformation patterns such as append operations, change data capture (CDC), and batch overwrites directly in their SQL queries. This initiative aligns with the company’s broader effort to integrate the declarative execution model of Apache Spark™ Declarative Pipelines into more authoring environments. Previously, such patterns required manual scheduling, custom SQL logic, or separate pipeline tools, but now users can describe the desired outcome in SQL while Databricks manages scheduling, refresh, and incremental processing automatically.
The new declarative operations include append-only updates for incrementally adding new records, AUTO CDC for handling inserts, updates, and deletes with minimal code, and REPLACE WHERE for targeted batch recomputation. Append flows track new versus processed data automatically, while AUTO CDC simplifies complex MERGE INTO logic by specifying keys, sequencing, and delete handling. REPLACE WHERE enables selective reprocessing of subsets of data, such as date ranges or partitions, with Databricks’ Enzyme engine optimizing performance by processing only changed data, reducing runtime and costs.
These declarative patterns are accessible directly in the Lakehouse SQL Editor, where users can define flows that refresh on a schedule, trigger from upstream updates, or run on demand. The approach supports incremental state management, dependency handling, and schema evolution without requiring a full rewrite of existing SQL workflows. Teams can continue using fine-tuned procedural SQL for custom tasks while leveraging declarative operations for recurring, maintenance-heavy patterns, offering a practical path to modernization.
For larger or more complex projects, Databricks continues to recommend the Lakeflow Pipelines Editor, which provides a project-oriented environment with multi-file development, dependency management, pipeline visualization, and integrated validation. This dual approach allows SQL teams to start small with declarative primitives in their existing workflows and scale up as needed, blending traditional SQL control with automated, low-friction ETL management.