Modernizing SQL ETL in Lakehouse with Declarative Patterns

Modernizing SQL ETL in Lakehouse with Declarative Patterns | Databricks Blog Skip to main content

Summary

Declarative ETL is coming directly into Lakehouse as part of a broader Declarative Everywhere strategy.

SQL analysts and analytics engineers can now leverage declarative flows for APPEND, AUTO CDC, and REPLACE WHERE batch updates without writing complex procedural code.

Practitioners can easily execute query-level ETL tasks inside their standard SQL workflows, or transition to the Lakeflow Pipelines Editor for multi-step, project-oriented development.

Databricks is bringing declarative ETL to data warehousing workflows in Lakehouse , making it easier for SQL practitioners to simplify complex transformation logic in familiar places they already work. This is part of a broader strategy to bring the declarative execution model behind Apache Spark™ Declarative Pipelines into more authoring experiences across Databricks. Instead of needing to work in a dedicated pipelines-oriented environment, SQL users can now define common ETL patterns directly within their SQL queries in Databricks Lakehouse. Simplifying recurring ETL patterns in Lakehouse Declarative SQL ETL on Databricks is not new. Today, thousands of SQL-first users already rely on declarative primitives like Materialized Views and Streaming Tables to simplify recurring transformations, keep downstream tables up to date, and accelerate BI workloads. Many recurring ETL patterns are easy to describe but difficult to operate, requiring custom SQL logic, manual scheduling, and orchestration glue. These patterns include appending new records, applying CDC changes, and refreshing only the data that changed. Declarative primitives work because they let users describe the table or view they want, instead of hand-coding every step required to keep it up to date. Databricks handles scheduling, refresh, and incremental processing where applicable, so users do not have to hand-code the logic required to keep tables up to date. We’re now extending that same declarative approach beyond the Lakeflow Pipelines Editor to more recurring ETL patterns for data warehouse and SQL practitioners. Now, Lakehouse users can define the ETL pattern they want - directly in the SQL Editor for example - while Databricks handles the incremental processing, update logic, scheduling, and orchestration needed to run it reliably.

Incremental REPLACE WHERE flows bring targeted refreshes to Lakehouse

The first declarative primitives available in Lakehouse The first declarative operations available in the Lakehouse SQL editor map to three common recurring ETL patterns: append-only updates, change data capture, and batch overwrites. Many of these patterns are already available via declarative APIs like AUTO CDC in Lakeflow ; the shift here is making them accessible directly in Lakehouse for SQL analysts. These flows can be refreshed on a schedule, triggered by upstream updates, run on demand, or orchestrated through SQL tasks in Jobs . Append-Only Updates Append-only updates are the standard pattern behind many streaming tables today; used for incrementally appending new records from a source into a target table. They are commonly used for ingestion workloads, such as loading new records from cloud object storage with Auto Loader. Instead of writing and scheduling repeated insert logic, SQL users can define a simple APPEND flow that automatically tracks new versus previously processed data in the source. Databricks handles state tracking and incrementally appends new records as they arrive, managing the underlying serverless pipeline automatically. This gives SQL users a simple way to operationalize append-style ingestion without manually creating, scheduling, or managing a separate pipeline. See how to define APPEND flows in Lakehouse . Change Data Capture CDC pipelines are among the most common — and most complex — patterns in SQL ETL. Teams often use MERGE INTO to process inserts, updates, and deletes, but CDC data can arrive out of order, requiring additional logic to avoid incorrect results. AUTO CDC lets SQL users define CDC logic with a few lines of declarative code in Lakehouse. With AUTO CDC, it’s easy to specify keys, sequencing, delete handling, and whether to store results as SCD Type 1 or SCD Type 2 - without hand-writing complex merge pipelines. “At bsport, SQL AUTO CDC has given us a much simpler and more modular way to manage data ingestion in Databricks. By decoupling table loads from a single pipeline, we’ve improved availability and data freshness across our platform. It allows us to process data from third parties independently, which gives us better failure management, reduces orchestration complexity, and makes the overall setup easier to operate and scale. For our team, this has created a cleaner, more flexible SQL-based workflow with stronger reliability in production.” —Adrien Marteau, Head of Data, bsport See how to create AUTO CDC flows for SCD Type 1 and Type 2 . Batch Overwrites Some batch ETL workloads only need to refresh a specific subset of data, such as a date range, partition, or business segment. Traditionally, teams often handle this with expensive full recomputes or custom overwrite logic. REPLACE WHERE flows bring a declarative pattern for targeted incremental batch recomputation into Lakehouse. Users define a predicate on the target table, and Databricks refreshes that region automatically. With Enzyme, Databricks’ automatic incrementalization engine, Databricks can identify and process only the data that changed within the specified predicate where possible, instead of recomputing the full target table or rewriting the entire matching slice. In Lakehouse benchmark testing, Enzyme-powered REPLACE WHERE ran 3.4x faster and 2.5x cheaper than traditional REPLACE WHERE. This is useful for selective reprocessing, schema evolution, backfills, and iterating on a small window of data before processing a larger historical range. See how to use REPLACE WHERE flows to refresh a targeted subset of a table (and read the community blog here ). Tl;dr: Why this matters for SQL practitioners Modernizing your ETL doesn't require a total rewrite or an all-or-nothing commitment to complex pipeline frameworks. Bringing declarative semantics into your existing Lakehouse SQL operations allows you to mix and match your existing code with modernized declarative SQL where it makes the most sense . You can keep your existing, fine-tuned...