待翻译：Modernizing SQL ETL in Lakehouse with Declarative Patterns

AI 服务暂时不可用，以下为来源正文，待恢复后补全翻译。

Modernizing SQL ETL in Lakehouse with Declarative Patterns | Databricks Blog Skip to main content 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 procedural SQL queries for custom tasks while seamlessly plugging in declarative operations like append-only ingestion, AUTO CDC, or targeted batch overwrites for recurring, maintenance-heavy patterns. This gives you the best of both worlds: full control over your traditional SQL logic alongside automated state management, dependency handling, and schema evolution where you want it, all directly within Lakehouse. From declarative primitives to full declarative pipelines Mixing declarative primitives into your everyday SQL workflows provides a practical, low-friction starting point for managing individual tables and incremental logic. As your project grows in scale and complexity, your development workflow can naturally evolve alongside it. For teams managing multiple related transformations, shared dependencies, and production workflows, the Lakeflow Pipelines Editor provides a richer, project-oriented development experience for declarative ETL, with support for multi-file development, dependency management, pipeline visualization, integrated validation, and production deployment. The Lakeflow Pipelines Editor This is especially useful for teams managing many related transformations across domains, data products, or business units. Instead of maintaining disconnected scripts or centralizing all logic in one large project, teams can organize declarative flows into governed, team-owned pipelines on Databricks. With Unity Catalog, each team can build on shared data assets, manage permissions consistently, and understand lineage across pipelines and downstream consumers. SQL practitioners can start with declarative flows in the familiar SQL Editor, then move into the Pipelines Editor when they need a more structured environment for larger projects, deeper pipeline management, and team-based development. Learn more about building declarative ETL workflows with the Lakeflow Pipelines Editor. Use Genie Code to get started faster Genie Code makes it easier for SQL practitioners to discover and apply these declarative ETL patterns in the workflows they already use. Instead of starting from a blank page or manually translating existing SQL into a production-ready pattern, users can ask Genie Code to help generate, explain, and refine declarative flows. For example, a user working with CDC data can ask Genie Code to help create an AUTO CDC flow, including the appropriate keys, sequence column, delete handling, and SCD Type 1 or Type 2 behavior. A user working with recurring batch logic can ask Genie Code to help convert existing overwrite logic into an incremental REPLACE WHERE flow. As declarative ETL becomes available across more authoring experiences, Genie Code can help guide users toward the right declarative pattern for the task at hand. To get started, explore the documentation linked in each section above - and use Genie Code in the SQL Editor to identify where APPEND flows, AUTO CDC flows, or REPLACE WHERE flows can simplify your existing ETL logic. 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