Skip to main content

08.4 Change Data Capture (CDC)

CDC architecture

Architect goal: understand CDC as an end-to-end consistency pattern — not simply a connector feature.

Change Data Capture (CDC) means capturing changes made to source data rather than repeatedly extracting the complete source.

The fundamental event model is:

INSERT UPDATE DELETE

A production CDC architecture must answer:

  1. How are changes captured?
  2. How are changes ordered?
  3. How are duplicate events handled?
  4. How are deletes represented?
  5. How is current state produced?
  6. How is history preserved?
  7. How are changes replayed?
  8. How is correctness proven?

1. CDC vs Full Extraction​

Imagine an operational table containing 500 million rows and only 5 million rows change during the day.

A full extraction repeatedly scans 500 million rows. CDC attempts to move the 5 million changes.

` FULL EXTRACTION

SOURCE ↓ 500M rows ↓ transform ↓ target

CDC

SOURCE ↓ 5M changes ↓ transform ↓ target `

CDC therefore reduces source workload, network transfer, processing volume and latency.

2. CDC Event Model​

A CDC event normally needs more metadata than the operation itself.

FieldPurpose
business_keyidentifies the entity
operationINSERT / UPDATE / DELETE
sequenceestablishes event order
event_timebusiness/source time
commit_timesource transaction time
source_systemprovenance
transaction_idtransaction correlation
before_imageprevious values where available
after_imagenew values where available

The sequence is especially important. Without reliable ordering, CDC correctness becomes difficult.

3. Enterprise CDC Architecture​

Operational Source ↓ CDC Capture ↓ Change Feed ↓ Bronze CDC ↓ Sequence + Dedup ↓ AUTO CDC ↓ Silver Current / Historical State ↓ Gold Analytics

The Bronze layer preserves raw change evidence. Silver applies business semantics. Gold serves consumers.

4. CDC Source Technologies​

CDC can originate from:

  • relational database logs
  • SAP replication
  • Debezium
  • Oracle GoldenGate
  • managed ingestion connectors
  • Delta Change Data Feed
  • application event streams

CDC is the change contract; the connector is only the mechanism that transports it.

5. CDC Event Ordering​

Suppose one customer receives:

sequence 100 → UPDATE sequence 101 → UPDATE sequence 102 → DELETE

The final state should be DELETE.

Now suppose events arrive as 102, 100, 101. Network arrival order is not necessarily source event order.

Therefore:

SOURCE SEQUENCE ↓ ORDER ↓ APPLY

Do not use ingestion timestamp as a substitute for source sequence unless the source guarantees that equivalence.

Databricks AUTO CDC uses a sequencing column to order changes and handle out-of-sequence records. The sequencing value must represent the correct event order.

6. Duplicate Events​

CDC systems can produce duplicate delivery.

Example:

101 UPDATE seq=500 101 UPDATE seq=500

The pipeline must be replay-safe.

Possible approaches:

  • source sequence + key
  • transaction ID + row key
  • event ID
  • deterministic deduplication
  • target idempotency

At-least-once delivery requires idempotent processing.

7. Deletes​

Deletes are a major reason to prefer CDC over timestamp extraction.

Representations include:

operation = DELETE

or:

_is_deleted = true

depending on source and target design.

Decide explicitly between:

Hard delete​

Remove the current row.

Soft delete​

Keep the row and mark it deleted.

Historical delete​

Preserve the prior version and close its validity interval.

8. SCD Type 1​

SCD Type 1 keeps the latest state.

Before:

customercity
101Bengaluru

CDC:

UPDATE 101 → Mumbai

After:

customercity
101Mumbai

Use Type 1 when current state is sufficient.

9. SCD Type 2​

SCD Type 2 preserves historical versions.

customercityvalid_fromvalid_tocurrent
101BengaluruJan 1Nov 8false
101MumbaiNov 8nulltrue

Now you can answer point-in-time questions.

Databricks AUTO CDC supports SCD Type 1 and Type 2 targets.

10. AUTO CDC​

Current Databricks Lakeflow pipelines provide AUTO CDC APIs for CDC processing.

AUTO CDC replaces the older APPLY CHANGES APIs; APPLY CHANGES remains available, but Databricks recommends AUTO CDC for new implementations.

Conceptually:

CDC SOURCE ↓ AUTO CDC ↓ STREAMING TABLE ↓ SCD1 / SCD2

The API handles much of the sequencing and change-application logic that previously required custom MERGE code.

11. AUTO CDC SQL Mental Model​

Conceptually:

` CREATE OR REFRESH STREAMING TABLE customer;

CREATE FLOW customer_changes AS AUTO CDC INTO customer FROM customer_cdc KEYS (customer_id) SEQUENCE BY sequence_id; `

Important inputs are:

  • target
  • source
  • keys
  • sequencing
  • delete condition where required
  • SCD mode
  • columns

Always verify exact syntax against the current Lakeflow SQL reference.

12. AUTO CDC Python Mental Model​

The Python API is conceptually:

` from pyspark import pipelines as dp

dp.create_auto_cdc_flow( target="customer", source="customer_cdc", keys=["customer_id"], sequence_by="sequence_id" ) `

The current API is create_auto_cdc_flow(); it replaces the older apply_changes() function.

13. Why AUTO CDC Matters​

Before managed CDC APIs, engineers commonly built:

read CDC ↓ deduplicate ↓ window ↓ MERGE ↓ handle deletes ↓ handle ordering ↓ handle SCD2 ↓ handle retries

This can become a large amount of custom logic.

AUTO CDC moves much of that change-management behavior into Lakeflow pipeline semantics.

This does not eliminate the need to understand CDC. It makes understanding the semantics more important.

14. Initial Hydration​

CDC only tells you about changes after the capture boundary.

Typical approach:

Historical Full Load ↓ Baseline Target ↓ CDC Feed ↓ Ongoing Sync

Databricks AUTO CDC supports a once flow for initial hydration before continuing with triggered or continuous CDC processing.

15. Initial Load Cutover​

A robust sequence is:

  1. establish CDC capture
  2. record the source change boundary
  3. perform initial load
  4. retain changes generated during the load
  5. apply changes after the baseline
  6. reconcile
  7. switch consumers

This avoids a gap between initial load and CDC.

16. Delta Change Data Feed​

Delta Lake has its own change-data mechanism: Change Data Feed (CDF).

CDF exposes row-level changes from Delta tables.

It can identify:

insert update_preimage update_postimage delete

Conceptually:

Delta Table ↓ Change Data Feed ↓ downstream incremental consumer

This allows Databricks tables themselves to become CDC producers.

17. Reading Delta CDF​

A streaming reader can request change data:

changes = ( spark.readStream .option("readChangeFeed", "true") .table("catalog.schema.source") )

This is useful when one Delta table feeds another incrementally.

Databricks recommends consuming a Delta table's change feed rather than directly streaming a mutable table when downstream processing needs inserts, updates and deletes.

18. CDF Is Not an Infinite Archive​

Change Data Feed is tied to retained table history and is not intended to be a permanent archive of every historical change.

If older versions are removed, those historical changes may no longer be available through CDF.

If permanent audit history is required:

CDF ↓ Archive / History Table

Do not assume CDF itself is your permanent audit store.

19. CDC vs CDF​

CapabilitySource CDCDelta CDF
Originoperational sourceDelta table
Captures insertsYesYes
Captures updatesYesYes
Captures deletesYesYes
Source transaction semanticssource-specificDelta transaction semantics
Primary purposereplicate changes into lakehousepropagate Delta changes
Permanent historyseparate designseparate design

A common enterprise chain is:

S/4HANA ↓ source CDC ↓ Bronze ↓ AUTO CDC ↓ Silver ↓ CDF ↓ Gold / downstream systems

20. AUTO CDC FROM SNAPSHOT​

What if the source has no CDC?

Databricks provides AUTO CDC FROM SNAPSHOT. It compares ordered snapshots and derives inserts, updates and deletes, then applies SCD Type 1 or Type 2 semantics.

Architecture:

Snapshot N-1 + Snapshot N ↓ Difference ↓ Synthetic CDC ↓ AUTO CDC

This is valuable when the source cannot expose a native change feed.

21. Snapshot Limitation​

Snapshot-derived CDC cannot see changes that occur between snapshots.

Example:

08:00 Bengaluru 12:00 Mumbai 18:00 Delhi

If snapshots are only 08:00 and 18:00, the system may only observe Bengaluru → Delhi.

The intermediate Mumbai state is invisible.

Therefore:

Snapshot comparison creates inferred changes, not a complete event history.

22. Partial Updates​

Some CDC systems send only changed columns.

Example:

customer_id = 101 city = Mumbai

They do not send name, phone or email.

A naive replacement could null out missing fields.

Partial-update semantics must distinguish:

column absent

from:

column explicitly changed to NULL

Databricks AUTO CDC supports partial-update patterns.

23. Sequence Design​

Good candidates:

  • source log position
  • transaction sequence
  • commit sequence
  • monotonically increasing event ID

Risky candidates:

  • ingestion timestamp
  • worker timestamp
  • file modification timestamp
  • local machine clock

The sequence must answer:

Which change happened after which other change for the same business key?

24. Multi-Row Transactions​

Consider:

Order 100 Order Item 100-1 Order Item 100-2

A transaction may update all three.

If CDC processing exposes transaction metadata, preserve:

transaction_id commit_sequence commit_timestamp

This helps reconstruct source transaction boundaries and diagnose partial processing.

25. Bronze CDC Design​

A strong Bronze CDC table should preserve source evidence.

Conceptually:

source_system source_table business_key operation sequence transaction_id event_timestamp commit_timestamp before_image after_image ingestion_timestamp batch_id

Avoid aggressively transforming Bronze.

Bronze should be your replay boundary.

26. Silver CDC Design​

Silver should turn source change semantics into business state.

Typical responsibilities:

  • ordering
  • deduplication
  • delete handling
  • schema normalization
  • data quality
  • SCD logic
  • business keys
  • survivorship rules

27. SCD Type 2 Design​

A Type 2 target commonly contains:

customer_id name city valid_from valid_to is_current

Example:

IDCityValid FromValid ToCurrent
101Bengaluru2026-01-012026-11-08false
101Mumbai2026-11-08nulltrue

28. Tracking Only Important Columns​

Not every change should necessarily create a new Type 2 version.

Suppose city is history-worthy but last_seen_at is operational metadata.

If every metadata update creates a new historical version, the table grows unnecessarily.

Databricks AUTO CDC supports tracking a selected subset of columns for SCD Type 2 history.

History should reflect business meaning, not merely technical change frequency.

29. Bitemporal CDC​

Some enterprise systems need two time dimensions:

Business time​

When the fact is valid in the business domain.

System time​

When the platform learned about the fact.

Example:

` Business: customer moved to Mumbai on Nov 1

System: Databricks received the correction on Nov 8 `

Databricks AUTO CDC supports bitemporal storage as a Beta capability.

Use bitemporal patterns only when the business requirement genuinely requires them.

30. CDC Failure Scenarios​

Failure A — Missing sequence​

Result: wrong final state.

Fix: establish a reliable source ordering field.

Failure B — Duplicate event​

Result: repeated update.

Fix: deterministic idempotency.

Failure C — Missing delete​

Result: stale target.

Fix: explicit delete semantics.

Failure D — CDC starts after initial load​

Result: gap between baseline and ongoing changes.

Fix: controlled cutover boundary.

Failure E — CDF retention expires​

Result: historical replay unavailable.

Fix: archive required changes before retention expires.

Failure F — Snapshot skips intermediate state​

Result: incomplete history.

Fix: native CDC when point-in-time event history matters.

31. CDC Recovery​

A production recovery strategy should define:

checkpoint source sequence last committed target state replay range deduplication key

If the consumer fails at sequence 5000, replay 5001 through current.

The target must safely tolerate replay.

This is why:

CDC + idempotency

is more important than CDC alone.

32. Monitoring CDC​

Track:

Throughput​

  • changes per minute
  • inserts
  • updates
  • deletes

Lag​

source commit time - Databricks processing time

Correctness​

  • duplicate events
  • out-of-order events
  • rejected events
  • missing sequences
  • reconciliation failures

Target​

  • upsert count
  • delete count
  • SCD2 versions created

Operations​

  • checkpoint
  • retries
  • failures
  • compute
  • cost

Databricks AUTO CDC exposes metrics such as upserted and deleted row counts for monitoring.

33. CDC Security​

CDC can contain highly sensitive information.

Therefore:

CDC landing ↓ Unity Catalog ↓ least privilege ↓ masking / filtering ↓ audited access

Do not give every analyst access to raw CDC.

Bronze CDC should usually have stricter access controls than curated Gold data.

34. CDC and Unity Catalog​

A governed architecture:

` catalog └── source_bronze └── customer_cdc

catalog └── curated └── customer `

Use Unity Catalog for:

  • ownership
  • privileges
  • lineage
  • audit
  • classification
  • masking
  • workspace isolation

35. CDC and SAP​

For SAP-centric enterprises:

S/4HANA ↓ SLT / CDC / supported extraction ↓ CDC Landing ↓ Bronze ↓ AUTO CDC ↓ Silver ↓ SCD1 / SCD2 ↓ Gold

Map the concepts:

SAP conceptDatabricks concept
Delta extractionincremental boundary
SLT replicationCDC transport
change recordsCDC events
BW delta processincremental state
DSO/ADSO current statecurated Delta target
change historySCD2 / history table
downstream deltaCDF / CDC feed

The technologies differ, but the data-engineering principle is familiar.

36. CDC vs Incremental Timestamp​

RequirementTimestampCDC
New recordsYesYes
UpdatesConditionalYes
DeletesWeakStrong
Event orderingWeakStrong
Exact historyWeakStrong
Source complexityLowHigher
Source workloadQueryReplication
Best forSimple sourcesTransactional systems

Choose CDC when the business semantics justify the additional complexity.

37. CDC vs Snapshot​

RequirementSnapshotCDC
Current stateYesYes
Deletesinferredexplicit
Intermediate changesNoYes
Historical eventsincompletestrong
Source supportbroadsource-dependent
Processing costpotentially highlower change volume
Complexitymediumhigher

38. Hands-On Lab 1 — Build a CDC Event Stream​

Create a CDC dataset:

customer_id operation sequence_id name city event_time

Generate:

101 INSERT 100 101 UPDATE 101 101 UPDATE 102 101 DELETE 103

Then intentionally deliver events out of order.

Verify:

  • source sequence
  • deduplication
  • final state
  • delete behavior

39. Hands-On Lab 2 — AUTO CDC SCD Type 1​

Create a Lakeflow pipeline.

Source: customer_cdc

Target: customer_current

Configure:

  • business key
  • sequence column
  • delete semantics
  • Type 1 behavior

Verify that updates replace current state.

40. Hands-On Lab 3 — AUTO CDC SCD Type 2​

Generate:

Bengaluru ↓ Mumbai ↓ Delhi

Verify:

  • historical versions
  • current flag
  • effective intervals
  • point-in-time query

41. Hands-On Lab 4 — Delta CDF​

Create a Delta table and perform:

  • INSERT
  • UPDATE
  • DELETE

Read the Change Data Feed and inspect:

_change_type

including preimage/postimage records.

Then create a downstream incremental consumer.

42. Hands-On Lab 5 — Snapshot CDC​

Create:

snapshot_001 snapshot_002 snapshot_003

Introduce inserts, updates and deletes.

Use AUTO CDC FROM SNAPSHOT.

Then create a change that occurs between snapshots and explain why it cannot be observed.

43. Architect Exercise​

Design CDC for:

A global S/4HANA environment with 20 million daily changes, financial documents requiring audit history, 10-minute freshness, deletes, late-arriving changes, regional workspaces and strict data residency.

Decide:

  1. CDC mechanism
  2. source sequence
  3. Bronze retention
  4. SCD1 vs SCD2
  5. delete semantics
  6. replay strategy
  7. region architecture
  8. Unity Catalog governance
  9. reconciliation
  10. disaster recovery
  11. downstream CDF
  12. cost controls

44. Enterprise CDC Reference Architecture​

S/4HANA Operational Source | CDC Capture | v Change Feed key / op / sequence | v Bronze CDC raw + replayable | sequence / dedup | v AUTO CDC Lakeflow | +------+------+ | | v v SCD Type 1 SCD Type 2 Current State History | | +------+------+ | v Gold / BI SAC / SQL / Apps

45. Production Checklist​

Source​

  • CDC capture is reliable
  • sequence semantics documented
  • deletes supported
  • source transaction behavior understood

Bronze​

  • raw change evidence retained
  • source metadata preserved
  • replay strategy defined

Processing​

  • deterministic ordering
  • duplicate handling
  • idempotency
  • late-data strategy
  • delete semantics

Target​

  • SCD strategy selected
  • business keys validated
  • target performance tested

Operations​

  • lag monitored
  • counts reconciled
  • failures alerted
  • recovery tested
  • backfill tested

Governance​

  • Unity Catalog ownership
  • least privilege
  • sensitive-data classification
  • audit access
  • lineage

46. Architect Mental Model​

CDC is not simply:

connect database → copy changes

CDC is:

CAPTURE ↓ ORDER ↓ DEDUPLICATE ↓ APPLY ↓ PRESERVE HISTORY ↓ RECONCILE ↓ RECOVER

The key architect question is:

If the pipeline stops at any point and starts again tomorrow, can I prove that the target will converge to the correct source state without losing history?

If yes, you are designing CDC as an architect rather than merely configuring a connector.

Official Databricks references​