Session

Building a Production: Scale Dimensional Data Mart With Lakeflow Spark Declarative Pipelines and AUTO CDC

Overview

Experience	In Person
Track	Data Engineering & Streaming
Industry	Retail & Consumer Goods
Technologies	Databricks SQL, Lakeflow, Unity Catalog
Skill Level	Intermediate
DOWNLOAD SESSION SLIDES

At 84.51˚, we replaced legacy ETL processing in our retail sales data mart with Lakeflow Spark Declarative Pipelines to simplify development while improving reliability at scale. In this session, we’ll walk through how a low-code, declarative approach enables dimensional modeling for both slowly changing dimensions and high-volume fact data.

You’ll see how to use AUTO CDC to handle data updates across batch and streaming pipelines, supporting continuously arriving sales transactions alongside rapidly changing product, store and customer attributes. Our ETL crunches approximately 8 million sales transactions and over 80 million purchased items per day in support of our 84.51˚ Stratum platform.

Attendees will leave with practical guidance for designing and operating dimensional data marts using Spark Declarative Pipelines, including patterns for CDC, schema evolution, and hybrid batch/streaming workloads that can be applied immediately in production environments.

Session Speakers

Scott Gordon

/Lead Data Engineer
84.51˚

Shu Li

/Sr. Specialist Solutions Architect
Databricks

Full Summary

AutoCDC: The Easy Button for Change Data Capture in Streaming and Batch Pipelines

Change data capture is notoriously brittle to implement by hand. AutoCDC, delivered through Lakeflow Declarative Pipelines on the Databricks Platform, replaces hundreds of lines of MERGE logic with a small, declarative configuration. Teams building modern data engineering workflows on the Databricks Platform can use it to unify streaming and batch CDC patterns, reduce operational risk, and improve performance. Engineers looking for a broader view of how these capabilities fit together will find the Databricks Platform product overview a useful starting point for understanding how AutoCDC sits within the unified data and AI ecosystem.

The session explains what AutoCDC does, how its snapshot-based variant infers changes when no change feed exists, and how it guards against classic pitfalls such as deletes, nulls, duplicates, and out-of-order arrivals. Real production examples illustrate when to choose streaming versus batch and how SCD Type 1 or Type 2 behavior becomes a single parameter.

What AutoCDC delivers in practice

AutoCDC is a declarative API that performs upserts, deletes, deduplication, sequencing, and slowly changing dimensions out of the box. Engineers define a primary key, a sequence column, and SCD behavior, then let the engine handle reordering late events and eliminating duplicates so the target always reflects the correct state.

Because the implementation is optimized rather than hand-coded, the speakers report higher throughput, less storage scanning, lower cost, and improved SLAs compared to equivalent MERGE statements. The main payoff is shorter code with fewer edge-case bugs.

Two operating modes and how deletes are determined

AutoCDC runs in two modes:

Streaming mode consumes change records or raw events and relies on an operation tag for deletes. When a record carries an apply-as-delete marker, SCD Type 1 removes it, while SCD Type 2 closes it with a deleted timestamp.
Snapshot mode compares a full source snapshot to the target to infer inserts, updates, and deletes. Records present in target but missing from source are treated as deletes, with the same SCD1 versus SCD2 outcomes.

Behavior for SCD Type 1 and Type 2 deletions is consistent across modes. The difference lies in how deletes are detected, and the documentation covers nuanced differences in sequencing and timestamping between streaming and batch runs.

Guardrails for nulls, duplicates, and out-of-order events

Nulls frequently corrupt targets when upstream systems send partial data. AutoCDC provides two controls to prevent unintentional null overwrites:

Declare columns where null updates must be ignored, protecting fields that should never be set to null by accident.
Use a per-row status column so each source only overwrites the columns it is authoritative for, enabling multi-source, row-level control.

Sequencing is handled by a declared sequence column, such as a transaction timestamp. Out-of-order arrivals across partitions do not overwrite newer values, and duplicates are eliminated using the defined keys.

When to choose streaming or batch, with production patterns

Streaming fits continuous, high-volume facts like sales transactions. Structured Streaming checkpoints provide guaranteed single delivery semantics for writes, which removes the usual anxiety around incremental batch loads. One example ingests 8 to 10 million sales events per day via Kafka and Azure Event Hubs, then uses create_auto_cdc_flow to upsert by transaction ID with SCD Type 1.

Batch fits small, slowly changing reference data or multi-input joins where streaming is impractical. A store dimension example uses Delta Sharing across tenants to join several shared views, then applies create_auto_cdc_from_snapshot_flow to maintain an SCD Type 2 history. Both approaches use the same declarative pattern, with minor, documented differences in sequencing and timestamp behavior.

Platform-managed CDC reduces risk and code while improving throughput

Expressing CDC declaratively moves the hard parts to the platform. AutoCDC collapses MERGE complexity, handles streaming and batch consistently, and bakes in SCD semantics, sequencing, and delete behavior. Teams get simpler pipelines, fewer edge-case failures, and better performance characteristics without bespoke logic.

FAQ

Use AutoCDC when you need conditional inserts, updates, deletes, deduplication, and SCD behavior without hand-coding edge cases. The speakers emphasize that AutoCDC's optimized engine outperforms typical MERGE implementations while reducing code and operational risk. Custom MERGE may still be required for highly specialized logic.

Yes. If a change feed exists, AutoCDC can consume it in streaming mode. If not, AutoCDC from snapshot compares the full source snapshot to the target to infer inserts, updates, and deletes, which suits sources that only expose full extracts.

Streaming uses create_auto_cdc_flow with spark.readStream, and delete detection relies on an operation tag. Snapshot mode uses create_auto_cdc_from_snapshot_flow with spark.read, inferring deletes by comparing snapshots. Sequencing and SCD Type 2 timestamps also differ subtly, as documented by the product.

You can declare specific columns where null updates are ignored, or rely on a status column that indicates which source is authoritative for which fields. Both patterns help avoid accidental nulls from partial or heterogeneous upstream feeds.

Yes, it works for facts. SCD Type 1 can be used as a straightforward upsert pattern on fact tables, as shown in the sales example, while SCD Type 2 is appropriate for dimensions that require full historical tracking.