08.3 Incremental Loads in Databricks

Architect goal: learn how to move from expensive full scans to stateful, restartable, incremental data processing that remains correct when data arrives late, jobs fail, records are updated or deleted, and historical data must be replayed.

Incremental loading means processing only the portion of source data that is new or changed since the previous successful processing boundary.

The difficult part is not reading less data. The difficult part is knowing exactly what changed, exactly how far you processed, and exactly how to recover without losing or duplicating data.

1. The Incremental Processing Contract

Every incremental pipeline should define five things:

CHANGE DETECTION PROCESSING BOUNDARY STATE APPLY SEMANTICS RECOVERY

Example:

` Change detection ↓ updated_at

Boundary ↓ last_successful_timestamp

State ↓ watermark table

Apply ↓ MERGE by business key

Recovery ↓ replay bounded interval `

If one of these is undefined, the pipeline is incomplete.

2. Incremental Patterns

There is no single incremental strategy.

Pattern	Typical source	Change signal
New files	S3 / ADLS / GCS	file arrival
Cursor	SaaS / query source	increasing column
Timestamp	database table	updated_at
Sequence	transactional source	monotonically increasing ID
CDC	database / SAP replication	insert/update/delete events
Snapshot comparison	source without CDC	compare current vs previous
Event stream	Kafka / Kinesis	event offset

The source determines which pattern is safe.

Do not choose a watermark column simply because it is convenient.

3. File Incremental Loading

Suppose files arrive:

s3://landing/orders/ orders_001.json orders_002.json orders_003.json

The incremental problem is: which files have already been processed?

Databricks Auto Loader uses the cloudFiles source to incrementally discover and process new files in cloud object storage. Databricks recommends Auto Loader for incremental cloud-object-storage ingestion, particularly at large file volumes.

For production workloads, Auto Loader can also be scheduled using Trigger.AvailableNow, which processes the backlog available when the query starts and then exits.

4. COPY INTO vs Auto Loader

COPY INTO

Useful when:

• file volumes are relatively modest
• SQL-based ingestion is preferred
• users want explicit file loading commands
• selective reprocessing is important

Auto Loader

Prefer when:

• file volumes are large
• files continuously arrive
• schema evolution matters
• scalable file discovery is required
• incremental processing is part of a production pipeline

Databricks documents COPY INTO as idempotent incremental ingestion and recommends Auto Loader for workloads expected to grow to very large file counts.

5. Timestamp Watermark

Suppose a source table contains:

order_id customer_id amount updated_at

Previous state:

watermark = 2026-11-07 23:00:00

Next extraction:

updated_at > watermark

This works only if the source guarantees that updates reliably advance updated_at.

Potential causes of missed data include:

• clock precision
• source transaction timing
• late commits
• timezone differences
• backdated updates
• ETL delays

6. Sliding Windows

A common solution is a lookback window.

Instead of:

updated_at > 10:00

use:

updated_at >= 09:55

and then deduplicate/upsert downstream.

Architecture:

previous watermark ↓ subtract lookback ↓ read overlap ↓ deduplicate ↓ MERGE ↓ advance watermark

The overlap intentionally reprocesses some records. That is acceptable because the target operation is idempotent.

Controlled overlap is often safer than a perfectly sharp but fragile boundary.

7. Monotonic Cursors

A cursor can be:

sequence_id

For example:

1001 1002 1003 ...

Previous cursor:

1000

Next extraction:

sequence_id > 1000

This is strong for append-only sources.

But an integer ID is not automatically a safe cursor. If a row can be updated later without changing its ID, the pipeline can miss the update.

For managed query-based incremental ingestion, Databricks recommends a monotonically increasing cursor; timestamps such as updated_at are generally preferable when updates must be captured.

8. CDC

CDC means Change Data Capture.

Instead of asking what the current source state is, the pipeline receives what changed.

Example:

INSERT customer 101 UPDATE customer 102 DELETE customer 103

CDC events might contain:

business_key operation before_value after_value sequence commit_timestamp

The sequence is critical because changes can arrive out of order.

Typical architecture:

SOURCE ↓ CDC ↓ BRONZE CHANGE LOG ↓ ORDER / DEDUP ↓ APPLY CHANGES ↓ SILVER CURRENT STATE

9. CDC vs Timestamp Extraction

Requirement	Timestamp extraction	CDC
New rows	Yes	Yes
Updates	Depends on timestamp	Yes
Deletes	Difficult	Yes
Before-image	No	Often
Ordering	Source dependent	Usually explicit
Source overhead	Query workload	Replication workload
Complexity	Lower	Higher
Historical change semantics	Limited	Strong

If deletes and exact update semantics matter, CDC is usually the stronger architecture.

10. Incremental MERGE

Delta Lake supports MERGE for inserts, updates and deletes.

Example:

` MERGE INTO target t USING changes s ON t.customer_id = s.customer_id

WHEN MATCHED AND s.operation = 'UPDATE' THEN UPDATE SET *

WHEN MATCHED AND s.operation = 'DELETE' THEN DELETE

WHEN NOT MATCHED AND s.operation != 'DELETE' THEN INSERT * `

The target key must represent the business identity correctly.

11. Deduplication Before MERGE

A common failure is multiple source changes for the same key in one batch.

Example:

customer_id | sequence | operation 101 | 100 | UPDATE 101 | 101 | UPDATE 101 | 102 | DELETE

Instead:

changes ↓ partition by business key ↓ order by source sequence DESC ↓ keep latest event ↓ MERGE

For CDC pipelines, event ordering is part of correctness.

12. Watermark State Table

For enterprise workloads, keep state explicitly where appropriate.

source	object	watermark	run_id	status
SAP	EKPO	983420	R1042	SUCCESS
CRM	CUSTOMER	2026-11-08T01:00	R1043	SUCCESS

A richer state model may contain:

pipeline_id source_system source_object watermark_type previous_watermark current_watermark run_id started_at completed_at status record_count error_message

This becomes an operational control table.

13. Never Advance State Too Early

Incorrect:

read source ↓ advance watermark ↓ write target ↓ FAIL

The next run now believes the data was processed.

Correct:

read source ↓ transform ↓ validate ↓ write target ↓ SUCCESS ↓ commit state

State should represent successful processing, not attempted processing.

14. State Is Different from Data

This distinction is fundamental.

Data

Business records:

customer order invoice purchase order

State

Pipeline progress:

last_file last_sequence last_timestamp checkpoint run_id

Treat state as a first-class platform concern.

15. Idempotency

Suppose batch 500 contains 1,000 changes.

The job succeeds in writing the target but fails before reporting success.

The orchestrator retries.

The source returns the same 1,000 changes.

A correct incremental design must safely process them again.

Patterns:

• MERGE by stable key
• deterministic replacement
• batch-aware writes
• deduplication
• bounded partition replacement
• transactional publication

Incremental processing without idempotency is fragile.

16. Exactly-Once: Be Precise

Do not casually claim that the whole pipeline is exactly once.

There are multiple boundaries:

source ↓ discovery ↓ processing ↓ Delta write ↓ consumer

Exactly-once guarantees can exist at one boundary while the end-to-end system still has duplicate or replay considerations.

A stronger statement is:

Define exactly-once semantics for each interface and design the complete pipeline around replay-safe operations.

Auto Loader maintains discovery state and checkpoints so it can resume after failures and provide exactly-once processing when writing to Delta Lake.

17. Late-Arriving Data

Imagine:

` Day 1: order 100 created

Day 2: order 100 arrives in pipeline

Day 3: order 100 correction arrives `

A strict daily partition strategy can miss corrections.

Solutions include:

• sliding window
• CDC
• reconciliation
• event-time processing

The correct solution depends on source semantics.

18. Deletes

Deletes are often forgotten.

Suppose the source contains customer 101 and then deletes it.

A timestamp-based extraction may not return the deleted row.

The target still contains customer 101.

Solutions:

• CDC delete events
• source tombstones
• soft-delete flags
• periodic reconciliation
• snapshot comparison

If deletion semantics matter, design them explicitly.

19. Snapshot Comparison

If the source does not expose CDC:

Current source snapshot ↓ compare ↓ Previous snapshot ↓ identify INSERT UPDATE DELETE

This can work, but it may be expensive because large portions of the source may need to be compared.

Use it when:

• source has no CDC
• data volume is manageable
• correctness is more important than extraction efficiency

Do not use snapshot comparison blindly at billion-row scale.

20. Incremental Processing with Lakeflow

Databricks currently recommends Lakeflow pipelines for many new ETL workloads. Lakeflow pipelines can manage dependencies and incremental processing through pipeline flows, while supporting streaming tables, materialized views and change-processing patterns.

A flow can process data incrementally rather than rebuilding the complete result on every update when the pipeline semantics support it.

This changes the architecture from:

JOB ├── read everything ├── transform everything └── write everything

toward:

PIPELINE ├── managed state ├── incremental flows ├── dependencies └── target refresh semantics

21. Incremental Materialized Views

Incremental processing is not only for ingestion.

Databricks can incrementally refresh supported materialized-view queries by detecting source changes and recomputing affected results instead of rebuilding the complete result.

This can significantly reduce compute for large analytical transformations.

However, unsupported query constructs can cause a full recompute. Always verify the actual refresh behavior rather than assuming every materialized view is incremental.

22. Incremental vs Full Refresh

Situation	Preferred approach
Initial migration	Full
Small reference table	Full may be acceptable
Large append-only source	Incremental
Large frequently updated source	CDC / incremental
Source without change signal	Snapshot / reconciliation
Historical rebuild	Full or bounded backfill
Complex logic invalidating incremental state	Full refresh
New pipeline development	Prefer incremental-capable design

A full refresh should be an explicit decision, not the accidental default.

23. Initial Load → Incremental Transition

A mature pipeline often starts with:

PHASE 1 Full historical load

then transitions to:

PHASE 2 Incremental processing

Architecture:

Historical Source ↓ Full Load ↓ Baseline Target ↓ Capture changes ↓ Incremental

The cutover boundary must be carefully defined. Otherwise, changes occurring during the initial load can be missed.

24. Cutover Strategy

A safer migration approach:

1. establish source change tracking
2. record the initial boundary
3. perform historical load
4. capture changes after the boundary
5. reconcile overlap
6. apply pending changes
7. validate totals
8. switch consumers

This is especially important for SAP and other high-volume transactional systems.

25. SAP Architecture

For SAP:

S/4HANA │ ├── CDC / replication │ ▼ Change Stream / Landing │ ▼ Bronze CDC │ ├── operation ├── sequence ├── commit time └── source metadata │ ▼ Dedup / Order │ ▼ Silver Current State │ ├── SCD1 └── SCD2 │ ▼ Gold Analytics

For an SAP professional, the useful mental mapping is:

` SLT / CDC ↓ incremental change feed

ODP / delta extraction ↓ incremental extraction boundary

BW delta process ↓ stateful incremental processing

Databricks Bronze ↓ raw change evidence

Delta MERGE / APPLY CHANGES ↓ current target state `

The mechanisms differ, but the architectural concept is similar:

Track what changed, preserve processing state, apply changes deterministically, and recover safely.

26. Incremental Pipeline Failure Scenarios

Failure A — Watermark advanced too early

Result: records skipped.

Fix: advance state only after successful target commit.

Failure B — Duplicate retry

Result: duplicate rows.

Fix: idempotent MERGE or deterministic replacement.

Failure C — Late record

Result: record never processed.

Fix: lookback window, CDC or reconciliation.

Failure D — Delete not captured

Result: stale target row.

Fix: CDC delete, tombstone or reconciliation.

Failure E — Cursor not monotonic

Result: records missed.

Fix: choose a correct cursor or use CDC.

Failure F — Source clock precision

Result: boundary collisions.

Fix: overlap plus deduplication.

27. Performance Engineering

Incremental processing reduces the input volume, but the remaining work can still be expensive.

Optimize:

• source filtering
• predicate pushdown
• partition pruning
• data skipping
• file sizes
• clustering
• join strategy
• shuffle
• MERGE scope
• target layout

A useful sequence:

Reduce source volume ↓ Reduce target scan ↓ Reduce shuffle ↓ Reduce write amplification ↓ Tune compute

Do not optimize the cluster before optimizing the data movement.

28. MERGE Performance

A poorly designed MERGE can still scan a large target.

Suppose:

Target = 10 billion rows Changes = 2 million rows

If the target cannot be efficiently pruned by the merge condition, a small incremental batch may still cause substantial work.

Architect for:

• selective keys
• good data layout
• clustering
• bounded processing windows
• partition/date predicates where appropriate
• avoiding unnecessary target scans

The goal is:

small source + small target search space

29. Backfill Design

A backfill should reuse the same incremental logic.

Example:

` normal: 2026-11-08

backfill: 2026-10-01 → 2026-10-07 `

Use parameters:

start_boundary end_boundary run_mode

Do not fork the production code into a separate backfill notebook.

The same logic should support:

• normal processing
• retry
• replay
• backfill

30. Reconciliation

Incremental systems need reconciliation because they deliberately process only a subset of source data.

Track:

source change count bronze change count silver applied count rejected count gold affected count

For financial systems also compare:

• amount totals
• document counts
• key counts
• period totals

Every incremental boundary should have a measurable reconciliation boundary.

31. Incremental Pipeline Observability

Monitor at least:

State

• previous watermark
• current watermark
• lag
• cursor progression

Data

• input changes
• inserted
• updated
• deleted
• rejected
• duplicates

Runtime

• duration
• compute
• shuffle
• retries

Business

• totals
• reconciliation
• SLA

A pipeline that is fast but silently misses records is not a successful pipeline.

32. Hands-On Lab 1 — Timestamp Incremental Load

Create a source table:

customer_id name city updated_at

Implement:

1. initial full load
2. store watermark
3. add new records
4. update existing records
5. run incremental extraction
6. MERGE into target
7. advance watermark only after success

Then deliberately fail the target write and verify that the watermark does not advance.

33. Hands-On Lab 2 — Sliding Lookback

Create a five-minute overlap.

Example:

watermark = 10:00 lookback = 5 minutes read >= 09:55

Insert duplicate source changes inside the overlap.

Verify that target deduplication produces the correct final state.

34. Hands-On Lab 3 — CDC MERGE

Create change events:

101 INSERT 102 UPDATE 103 DELETE

Then create multiple events for the same key.

Use sequence ordering to select the latest event before applying the changes.

Verify:

• insert
• update
• delete
• duplicate events
• out-of-order events

35. Hands-On Lab 4 — Auto Loader AvailableNow

Place multiple files into a cloud-storage landing path.

Build an Auto Loader pipeline using cloudFiles.

Then run it with Trigger.AvailableNow.

Add more files and run again.

Verify that previously processed files are not reprocessed as new input.

36. Hands-On Lab 5 — Backfill and Reconciliation

Process:

2026-10-01 → 2026-10-07

Then compare:

• source count
• change count
• target count
• business totals

Verify that the backfill is repeatable.

37. Architect Exercise

Design incremental processing for:

500 million SAP records, 5 million daily changes, updates and deletes, occasional late-arriving records, 15-minute freshness target and strict financial reconciliation.

You must decide:

1. CDC or timestamp?
2. What is the source sequence?
3. Where is state stored?
4. What is the replay boundary?
5. How are deletes represented?
6. How are duplicates handled?
7. How is late data handled?
8. How is MERGE optimized?
9. How is initial-load cutover performed?
10. How is reconciliation implemented?
11. What metrics indicate data loss?
12. What happens when the pipeline fails after target commit?

38. Decision Framework

Use this sequence:

Does source expose CDC? │ YES │ NO │ ▼ CDC │ └───────────────┐ ▼ Is there a reliable monotonic cursor? │ YES │ NO │ ▼ Cursor │ └───────────────┐ ▼ Is updated_at reliable? │ YES │ NO │ ▼ Timestamp │ └───────────┐ ▼ Snapshot / reconciliation

Then ask:

Can late data occur? Can deletes occur? Can records be updated? Can the job be retried? Can historical replay be required?

These answers determine the final architecture.

39. Interview Questions

Beginner

What is incremental loading?

Processing only new or changed data instead of repeatedly processing the entire source.

Intermediate

What is a watermark?

A persisted processing boundary that records how far the pipeline has successfully processed.

Advanced

Why can timestamp-based incremental loading lose records?

Because source updates can arrive late, timestamps can collide, clocks can have limited precision, and records can be backdated.

Advanced

Why use a lookback window?

To intentionally overlap extraction boundaries and then deduplicate, reducing the risk of missing late-arriving records.

Architect

How would you design incremental processing for an SAP system?

Prefer a reliable CDC or delta mechanism when available, preserve raw change evidence in Bronze, maintain explicit processing state, order and deduplicate changes, apply them idempotently to governed Delta targets, handle deletes explicitly, support bounded replay and backfill, and continuously reconcile source and target.

40. Architect Mental Model

Remember the five questions:

`

1. WHAT CHANGED? ↓
2. HOW FAR HAVE I PROCESSED? ↓
3. HOW DO I APPLY THE CHANGE? ↓
4. WHAT IF I RUN IT AGAIN? ↓
5. HOW DO I PROVE NOTHING WAS LOST? `

That is incremental architecture.

The implementation technology may change:

• Auto Loader
• COPY INTO
• CDC
• MERGE
• Lakeflow
• SQL
• PySpark
• managed connectors

But the architecture remains:

CHANGE ↓ BOUNDARY ↓ STATE ↓ APPLY ↓ COMMIT ↓ RECONCILE ↓ RECOVER

That mental model is essential before moving into CDC, SCD patterns and production ingestion.

Official Databricks references

• Choose an ETL approach on Databricks
• Lakeflow pipeline flows
• Auto Loader
• Auto Loader production configuration
• Cloud object storage ingestion options
• Query-based incremental ingestion
• Delta MERGE
• Lakeflow Jobs