Picture this. Your pipeline ran in 20 minutes six months ago. Today it takes 6 hours. Nobody touched the code. The data just kept growing.
That is the most common complaint data engineers have when a pipeline goes from dev to production and stays there long enough. The source table that had 10 million rows now has 500 million. Every night, the job reads all 500 million rows, transforms them, and writes them back out. But here is the thing: only about 10,000 rows actually changed since yesterday. The other 499,990,000 rows are exactly the same as they were.
You just paid to process half a billion rows to find 10,000 changes.
That is what a full load does. It does not care what changed. It reads everything, every single time, no matter what.
Incremental loading is the fix. You teach your pipeline to ask a smarter question: what changed since the last time I ran? Then you process only that. The 10,000 changed rows get handled. The other 499 million get skipped entirely.
This article explains the three ways Delta Lake on Databricks handles incremental loading, how Change Data Feed tracks row-level changes inside Delta tables, and how the AUTO CDC API handles slowly changing dimensions without writing 150 lines of custom MERGE logic.
This article is part of the Modern Data Engineering: The Complete Guide series. The Delta Lake mechanics behind all of these patterns are covered in Delta Lake Explained for Data Engineers. How incremental processing fits into the Bronze-Silver-Gold design is in Medallion Architecture in Databricks.
Why Full Loads Fail at Scale
Before covering the incremental patterns, it helps to understand specifically what breaks with full loads as data grows.
A full load reads every row in the source, applies transformation logic, and writes the output. At 10GB this is fast. At 10TB it is the bottleneck that defines the team's SLA.
Three costs accumulate with full loads:
- Compute cost: More rows mean more DBUs consumed per run. A pipeline that costs $4 per run at 10GB costs $4,000 per run at 10TB, assuming linear scaling, which is optimistic.
- Pipeline latency: Data freshness is limited by how long the full load takes. A 6-hour full load means data is always at least 6 hours stale, regardless of how frequently you schedule it.
- Downstream contention: A large write job holds Delta Lake locks longer. Downstream readers queue behind it. Reports that need fresh data wait for the full load to release its write lock before querying.
As noted in a February 2026 Azure Databricks incremental ingestion guide, loading 10,000 changed rows is orders of magnitude faster than loading 100 million. The compute savings are significant, the data freshness improves, and contention on shared tables drops.
The Three Incremental Patterns in Delta Lake
Delta Lake supports three approaches to incremental loading. Each fits a different source type and data change pattern. Choosing the wrong one adds complexity without benefit.
| Pattern | How It Identifies Changes | Best For | Key Limitation |
|---|---|---|---|
| Watermark filtering | Timestamp or ID column in the source | Append-only sources with a reliable sequence column | Cannot detect updates or deletes, only new rows |
| MERGE INTO | Match on key, apply insert/update/delete | Sources that send a full CDC payload with operation flags | Performance degrades with high-frequency small upserts |
| Change Data Feed | Delta Lake tracks row-level changes internally | Downstream Silver and Gold consumers of Delta tables | Only works on Delta source tables, not external systems |
Pattern 1: Watermark Filtering
Watermark filtering reads rows where a timestamp or auto-increment ID column is greater than the last processed value.
# Read the last processed timestamp from a checkpoint table
last_processed = spark.sql("SELECT MAX(processed_at) FROM pipeline.checkpoint").collect()[0][0]
# Load only rows newer than the checkpoint
new_rows = spark.read.table("bronze.orders")
.filter(f"created_at > '{last_processed}'")This works well for append-only sources where every new row gets a timestamp and rows are never updated after insertion. It breaks the moment your source updates existing rows. An updated row has its created_at timestamp from when it was first inserted, not when it was updated. Watermark filtering misses it entirely.
Use watermark filtering only when updates and deletes do not exist in the source. Event streams, log tables, and click data fit this pattern. Customer records, order status tables, and inventory do not.
Pattern 2: MERGE INTO for Direct Upserts
MERGE INTO handles inserts, updates, and deletes in one atomic operation. The source sends a payload that includes new rows, updated rows, and delete markers. MERGE matches each record against the target table by a key and applies the appropriate action.
MERGE INTO silver.customers AS target
USING (
SELECT * FROM bronze.customers_cdc
WHERE batch_id = current_batch_id()
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.operation = 'DELETE'
THEN DELETE
WHEN MATCHED
THEN UPDATE SET *
WHEN NOT MATCHED
THEN INSERT *MERGE is powerful but has a known performance problem at scale. High-frequency upserts on large tables create many small files in Delta Lake. Each MERGE operation writes new Parquet files for updated rows. Over time, a Silver table receiving frequent MERGEs accumulates thousands of small files. Query performance degrades. OPTIMIZE and VACUUM jobs take longer.
As J. Hopkins' January 2026 guide on CDC strategies in Databricks points out directly: you need to keep an eye on merge performance, since lots of upserts on big tables can create too many small files if you do not occasionally run OPTIMIZE and VACUUM.
Schedule OPTIMIZE on heavily merged Silver tables at least daily. Enable Predictive Optimization for Unity Catalog managed tables to have Databricks handle this automatically.
Pattern 3: Change Data Feed
Change Data Feed (CDF) is a Delta Lake feature that tracks every row-level change in a Delta table and exposes those changes as a readable stream.
When CDF is enabled on a table, Delta Lake logs every INSERT, UPDATE, and DELETE with a _change_type metadata column. A downstream pipeline reads only the changes since the last version it processed, not the full table.
Enable CDF on a Silver table that downstream consumers need to track:
ALTER TABLE silver.customers
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);Read the change feed in a downstream pipeline:
spark.readStream \
.format("delta") \
.option("readChangeData", True) \
.option("startingVersion", 10) \
.table("silver.customers")The _change_type column in the output carries one of four values: insert, update_preimage (the row before the update), update_postimage (the row after), and delete.
CDF is the right pattern for connecting Silver to Gold when the Silver table receives high-volume updates. Instead of reprocessing the full Silver table to refresh a Gold aggregation, the Gold pipeline reads only what changed in Silver since the last run.
The full CDF mechanics, including transaction log integration and retention configuration, are covered in Delta Lake Explained for Data Engineers.
AUTO CDC: From 150 Lines to 7 Lines
Writing MERGE INTO logic manually for a CDC pipeline is tedious. A correct implementation handles inserts, updates, deletes, out-of-order events, and late-arriving records. As noted in the Databricks community technical blog, this logic typically runs to 150 or more lines of fragile SQL, and it still does not reliably handle out-of-order records or deletes.
AUTO CDC replaces all of that with a declarative API inside Lakeflow Declarative Pipelines.
SCD Type 1: Keep Only the Latest Value
SCD Type 1 overwrites existing records with the latest values. No history is preserved. Use this for dimensions where only the current state matters: current address, current status, current product category.
CREATE OR REFRESH STREAMING TABLE silver.customers;
CREATE FLOW customers_cdc AS
AUTO CDC INTO silver.customers
FROM STREAM(bronze.customers_raw)
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY event_timestamp
STORED AS SCD TYPE 1;SCD Type 2: Preserve Full History
SCD Type 2 adds a new row for every change, with __START_AT and __END_AT columns that define when each version of a record was active. The current version has a null __END_AT. Use this for any dimension where business users or ML models need to know what a record looked like at a specific historical point.
CREATE OR REFRESH STREAMING TABLE silver.customers_history;
CREATE FLOW customers_history_cdc AS
AUTO CDC INTO silver.customers_history
FROM STREAM(bronze.customers_raw)
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY event_timestamp
STORED AS SCD TYPE 2
TRACK HISTORY ON * EXCEPT (updated_at, batch_id);The TRACK HISTORY ON * EXCEPT clause controls which columns trigger a new historical row. Excluding audit columns like updated_at prevents unnecessary row proliferation when those fields change without meaningful business state changes.
The SEQUENCE BY Clause and Late-Arriving Data
The SEQUENCE BY clause is what handles out-of-order events. It tells AUTO CDC which column defines the correct event order. When a late-arriving record shows up with a lower sequence number than records already processed, AUTO CDC slots it into the correct historical position for SCD Type 2, or drops it for SCD Type 1 if a later update already exists.
As the Databricks AUTO CDC API documentation confirms: AUTO CDC automatically handles data that arrives out of order. For SCD Type 2 changes, Lakeflow Declarative Pipelines propagates the appropriate sequencing values to the target table's __START_AT and __END_AT columns.
This matters for real-world event streams from Kafka or Kinesis, where network conditions or consumer group rebalancing can cause events to arrive out of sequence. Manual MERGE logic typically does not handle this. AUTO CDC handles it automatically based on the sequence column value. As Gambill Data Engineering's April 2026 guide on SCD implementation confirms, the AUTO CDC flow API handles change data capture including inserts, updates, and deletes, and maintains history automatically, eliminating the need for manual MERGE logic or custom code.
AUTO CDC FROM SNAPSHOT: When You Have No CDC Feed
Many source databases do not expose a CDC feed. Legacy systems, vendor SaaS applications, and some relational databases only support periodic full exports. You get a snapshot of the current state, not a stream of changes.
AUTO CDC FROM SNAPSHOT solves this. It compares consecutive snapshots, generates a synthetic change feed by detecting which rows were added, modified, or removed between snapshots, and applies SCD Type 1 or Type 2 logic to the target table.
As the Databricks change data capture documentation explains: AUTO CDC FROM SNAPSHOT is designed for snapshot-based ingestion. It compares consecutive snapshots, generates a synthetic change feed, and applies SCD logic. The target table can provide a CDF of either SCD Type 1 or Type 2 for downstream queries.
# Python-only API, not available in SQL
import dlt
@dlt.create_target_table("silver.products_history")
@dlt.apply_changes_from_snapshot(
target="silver.products_history",
source=lambda: spark.read.table("bronze.products_snapshot"),
keys=["product_id"],
stored_as_scd_type=2
)
def products_snapshot_flow(): passTwo constraints to know before using it: snapshots must be processed in ascending order by version, and the API is Python-only. Out-of-order snapshot processing is not supported. If a snapshot arrives late, it is ignored.
For most teams moving off legacy systems where CDC feeds are not available, this is the path from full loads to incremental processing without requiring infrastructure changes on the source side.
Three Incremental Pipeline Failures and How to Prevent Them
Small file accumulation from high-frequency MERGEs. A Silver table receiving hundreds of small MERGE operations per hour accumulates thousands of small Parquet files. Query performance on that table degrades steadily over weeks. The fix is OPTIMIZE scheduled at least daily, or Predictive Optimization for Unity Catalog managed tables. Teams that discover this six months in face a table with millions of small files and a multi-hour OPTIMIZE job to run during a maintenance window.
Forgetting to account for deletes in watermark-based pipelines. Watermark filtering picks up new rows. It never sees deleted rows. A customer who cancels their subscription has their record deleted from the source system. The Silver table still shows them as active. The business reports wrong retention numbers for months. If your source system deletes records, watermark filtering alone is not enough. You need either a CDC feed or a tombstone pattern where deletes are marked with a flag column rather than physically removed.
Reading CDF before it is enabled on the source table. CDF captures changes from the moment it is enabled. It does not backfill historical changes. A downstream pipeline that reads CDF from a Silver table where CDF was enabled after the table already existed sees only an initial snapshot on first read, then changes going forward. Teams that enable CDF and then try to read historical row-level changes find only the current state of the table at activation time. Enable CDF before a table goes into production if downstream consumers need to track changes from the beginning.
How This Series Continues
- Lakeflow Pipelines for Data Engineering covers the full Lakeflow Declarative Pipelines framework, including streaming tables, materialized views, and how AUTO CDC fits into the Bronze-Silver-Gold pipeline execution model.
- Designing Scalable ETL Pipelines on Databricks covers the performance patterns around MERGE at scale, including OPTIMIZE scheduling and how liquid clustering reduces small file impact on frequently updated tables.
- How to Build Production-Grade Data Pipelines on Databricks covers the operational layer: idempotency, monitoring, and runbooks for CDC pipelines that run 24/7.
- Data Quality and Reliability Patterns for Databricks Pipelines covers how to enforce data quality expectations on CDC outputs, including how to detect when a CDC feed has stalled or started producing anomalous delete rates.
