What you will be able to do
- Choose between AUTO CDC and AUTO CDC FROM SNAPSHOT based on what the source system provides, and name the pipeline editions that support them
- Write an AUTO CDC flow in SQL or Python with KEYS, SEQUENCE BY, delete and truncate conditions, and a column list
- Predict the contents of an SCD Type 1 and an SCD Type 2 target table from the same change feed, including late-arriving events
- Configure tombstone retention, truncates and one-time backfill flows correctly
Key concept
Keyed, sequenced upsert — AUTO CDC treats every change record as an upsert or delete against the row identified by KEYS, and uses the SEQUENCE BY value, not arrival order, to decide which change wins. Without both, the pipeline cannot rebuild correct current state or history from a change feed.
1.Which CDC API, and what the pipeline needs
Change data capture usually starts outside Databricks. A tool such as Debezium reads a transactional database and emits one record per changed row. In the Databricks CDC tutorial, Debezium "typically sends the history of data changes to Kafka topics or saves them as files." Your Lakeflow pipeline then has to turn that stream of inserts, updates and deletes into a table that reflects the source.
Lakeflow Pipelines offers two APIs for this, and the choice depends on what the source can provide. AUTO CDC is for sources that emit a change feed. AUTO CDC FROM SNAPSHOT is for sources that only give you full snapshots: it compares consecutive snapshots to work out what changed. AUTO CDC works in both the SQL and Python pipeline interfaces. FROM SNAPSHOT works only in Python.
| API | Use when | Interfaces |
|---|---|---|
| AUTO CDC (AUTO CDC ... INTO / create_auto_cdc_flow) | The source database has a CDC feed enabled | SQL and Python |
| AUTO CDC FROM SNAPSHOT (create_auto_cdc_from_snapshot_flow) | CDC is not enabled and only snapshots are available | Python only |
Two naming details come up on the exam. First, AUTO CDC is the current name for what used to be APPLY CHANGES: "The AUTO CDC APIs replace the APPLY CHANGES APIs and have the same syntax." The old APIs still work, but Databricks recommends the new names. In Python, apply_changes() became create_auto_cdc_flow(). Second, these APIs are a Databricks Lakeflow feature. They are not supported by open-source Apache Spark Declarative Pipelines.
Checkpoint 1 of 6· Check yourself
A source database has CDC disabled, and the team only has nightly full extracts. The team wants to write the whole pipeline in SQL. What is the problem?
Without a change feed you need AUTO CDC FROM SNAPSHOT, and that API exists only in Python. APPLY CHANGES is just the old name for AUTO CDC and doesn't add snapshot support in SQL.
“It is supported only in the Python pipeline interface.”Source: docs.databricks.com
2.Anatomy of an AUTO CDC flow
An AUTO CDC pipeline always has two parts. To set it up, you "create a streaming table and then use the AUTO CDC ... INTO statement in SQL or the create_auto_cdc_flow() function in Python". The streaming table is the target. The flow says how changes are applied to it. Here is the full SQL grammar:
CREATE OR REFRESH STREAMING TABLE table_name;
CREATE FLOW flow_name AS AUTO CDC [ONCE] INTO table_name
FROM source
KEYS (keys)
[IGNORE NULL UPDATES [ON {columnList | * EXCEPT (exceptColumnList)}]]
[APPLY AS DELETE WHEN condition]
[APPLY AS TRUNCATE WHEN condition]
SEQUENCE BY orderByColumn
[SYSTEM SEQUENCE BY systemOrderByColumn]
[COLUMNS {columnList | * EXCEPT (exceptColumnList)}]
[STORED AS {SCD TYPE 1 | SCD TYPE 2 | BITEMPORAL}]
[TRACK HISTORY ON {columnList | * EXCEPT (exceptColumnList)}]
[COLUMNS TO UPDATE columnName]KEYS names the column or columns that uniquely identify a row. By default every INSERT and UPDATE event is an upsert: AUTO CDC will "update any rows in the target table that match the specified key(s) or insert a new row". Deletes happen only when you add an APPLY AS DELETE WHEN condition.
SEQUENCE BY gives the logical order of events. The column must be sortable: "Specified columns must be sortable data types. This clause is required." When one column can tie, such as two events with the same timestamp, wrap several columns in a STRUCT, for example SEQUENCE BY STRUCT(timestamp_col, id_col). The API compares the first field and only falls back to the next field on a tie.
FROM must be a streaming source, so you use the stream(...) keyword. If the stream hits a change or deletion to an existing record, it throws an error, so "It is safest to read from static or append-only sources." **COLUMNS * EXCEPT keeps operation metadata, such as the operation code and sequence number, out of the target. STORED AS** selects the history model and defaults to SCD Type 1.
Checkpoint 2 of 6· Fill the gap
Which keyword completes the clause that orders change events in this SCD Type 1 flow?
CREATE OR REFRESH STREAMING TABLE users_current;
CREATE FLOW apply_cdc AS AUTO CDC INTO
users_current
FROM
stream(main.cdc_tutorial.users_cdf)
KEYS
(userId)
APPLY AS DELETE WHEN
operation = "DELETE"
APPLY AS TRUNCATE WHEN
operation = "TRUNCATE"
? BY
sequenceNum
COLUMNS * EXCEPT
(operation, sequenceNum)
STORED AS
SCD TYPE 1;AUTO CDC orders events with the required SEQUENCE BY clause. ORDER BY is a query clause and does not control how changes are applied.
Source: docs.databricks.comThey describe the change, not the entity. The flow uses them to decide deletes and ordering, so copying them into the target table would add no business value. COLUMNS * EXCEPT (operation, sequenceNum) keeps them out.
3.SCD Type 1 vs Type 2 on the same feed
The Databricks examples run one small change feed through both history models. Notice that the two sequence-5 updates are listed after the sequence-6 events: they arrived late.
| userId | name | city | operation | sequenceNum |
|---|---|---|---|---|
| 124 | Raul | Oaxaca | INSERT | 1 |
| 123 | Isabel | Monterrey | INSERT | 1 |
| 125 | Mercedes | Tijuana | INSERT | 2 |
| 126 | Lily | Cancun | INSERT | 2 |
| 123 | null | null | DELETE | 6 |
| 125 | Mercedes | Guadalajara | UPDATE | 6 |
| 125 | Mercedes | Mexicali | UPDATE | 5 |
| 123 | Isabel | Chihuahua | UPDATE | 5 |
SCD Type 1 keeps only the latest version of each key. The result has three rows: 124 Raul/Oaxaca, 125 Mercedes/Guadalajara and 126 Lily/Cancun. Isabel was deleted, and Mercedes shows one city because "SCD Type 1 overwrites previous values."
SCD Type 2 "preserves a complete history of changes by creating new rows for each version of a record". The pipeline adds __START_AT and __END_AT columns, filled from the sequencing values. Here is the Python version. The only differences from Type 1 are the stored_as_scd_type value and the missing truncate condition.
from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr
@dp.view
def users():
return spark.readStream.table("main.cdc_tutorial.users_cdf")
dp.create_streaming_table("users_history")
dp.create_auto_cdc_flow(
target = "users_history",
source = "users",
keys = ["userId"],
sequence_by = col("sequenceNum"),
apply_as_deletes = expr("operation = 'DELETE'"),
except_column_list = ["operation", "sequenceNum"],
stored_as_scd_type = "2"
)| userId | city | __START_AT | __END_AT |
|---|---|---|---|
| 123 | Monterrey | 1 | 5 |
| 123 | Chihuahua | 5 | 6 |
| 124 | Oaxaca | 1 | null |
| 125 | Tijuana | 2 | 5 |
| 125 | Mexicali | 5 | 6 |
| 125 | Guadalajara | 6 | null |
| 126 | Cancun | 2 | null |
Mercedes has three versions, including Mexicali, because SCD Type 2 places the late event at its sequence position instead of discarding it. Isabel's history ends at 6, when she was deleted. If you declare an explicit schema for an SCD Type 2 target, you must include __START_AT and __END_AT with the same data type as the sequence column.
You don't have to version every column. TRACK HISTORY ON limits which columns create a new history row when they change:
CREATE FLOW flow
AS AUTO CDC INTO
target
FROM stream(cdc_data.users)
KEYS (userId)
APPLY AS DELETE WHEN operation = "DELETE"
SEQUENCE BY sequenceNum
COLUMNS * EXCEPT (operation, sequenceNum)
STORED AS SCD TYPE 2
TRACK HISTORY ON * EXCEPT (city);A third option, STORED AS BITEMPORAL, tracks both business time and system time. It is in Beta and requires SYSTEM SEQUENCE BY.
Checkpoint 3 of 6· Match them up
Match each setting or column to its effect
Tap a term, then the definition that fits it.
Type 1 overwrites and Type 2 versions. TRACK HISTORY ON narrows versioning to chosen columns, and an open __END_AT marks the current row.
“Use SCD Type 2 to retain a history of records, either on all updates or on updates to a specified set of columns.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A bronze streaming table receives CDC events with columns `customer_id`, `op`, `updated_at`, and other attributes. Events sometimes arrive out of order because of network retries. An engineer defines: ```sql CREATE OR REFRESH STREAMING TABLE dim_customer; CREATE FLOW customer_cdc AS AUTO CDC INTO dim_customer FROM stream(bronze_customer_changes) KEYS (customer_id) STORED AS SCD TYPE 1; ``` During testing, an update carrying an earlier `updated_at` value overwrites a row that had already been updated with a later `updated_at`. Which change fixes this?
Correct answer: A — Add `SEQUENCE BY updated_at` to the flow so it resolves conflicting events for the same key by comparing that timestamp column.
- A. `SEQUENCE BY` is the clause that tells the flow which column determines chronological order for a given key, so it can discard or supersede events that arrive late relative to one already applied. Without it, the flow has no way to know that an out-of-order event should not overwrite a newer state.
- B. `APPLY AS TRUNCATE WHEN` clears the whole target table when a truncate signal is present; it has nothing to do with ordering conflicting updates for the same key and would not stop a stale event from being applied.
- C. Switching to SCD Type 2 changes how history is stored but does not by itself establish which of two conflicting events is newer — without a sequencing column, a late-arriving stale event can still be treated as the most recent version.
- D. `IGNORE NULL UPDATES` addresses partial-column update events that carry nulls for unchanged fields; it does not address which of two full, out-of-order events should win for a given key.
4.Late events, deletes, truncates and backfills
Deletes need special handling, because an older update could arrive after the delete. To prevent that update from bringing the row back, "the deleted row is temporarily retained as a tombstone in the underlying Delta table". A view in the metastore hides tombstones from readers. "The retention interval defaults to two days", and you can change it with the pipelines.cdc.tombstoneGCThresholdInSeconds table property.
This matters most with Auto Loader. If Auto Loader reads your CDC files, keep in mind that "Auto Loader does not guarantee file processing order". The documentation's rule is to set the threshold "to a value that exceeds the maximum expected delay between event arrival and pipeline execution."
Checkpoint 5 of 6· Check yourself
CDC files land in cloud storage and are read with Auto Loader. Some DELETE events reach the pipeline up to four days late, and deleted customers occasionally reappear. What is the fix?
Tombstones are kept for two days by default. Late events that arrive after a tombstone is collected can no longer be matched against the delete, so retention must exceed the worst-case delay.
“Set pipelines.cdc.tombstoneGCThresholdInSeconds to a value that exceeds the maximum expected delay between event arrival and pipeline execution.”Source: docs.databricks.com
APPLY AS TRUNCATE WHEN clears the whole target table when a matching event arrives. In the sample data, a TRUNCATE at sequence 3 leaves only Mercedes/Guadalajara, because her sequence-6 update is newer than the truncate. Truncation is a blunt tool, and "The APPLY AS TRUNCATE WHEN clause is supported only for SCD type 1."
The last piece is the initial load. A change feed usually begins after the source table already holds data. "For initial hydration from a source with a change feed, use AUTO CDC with a once flow and then continue processing the change feed." Use AUTO CDC ONCE in SQL or once=True in Python. A once flow reads a batch DataFrame, "not a streaming DataFrame", and it runs again only after a full refresh.
Checkpoint 6 of 6· Exam question
A streaming table target receives events with an `op` column set to `'INSERT'`, `'UPDATE'`, or `'DELETE'`. After deploying: ```sql CREATE OR REFRESH STREAMING TABLE orders_silver; CREATE FLOW orders_cdc AS AUTO CDC INTO orders_silver FROM stream(bronze_orders_changes) KEYS (order_id) SEQUENCE BY event_ts STORED AS SCD TYPE 1; ``` rows marked as deletes in the source still appear in `orders_silver` after the flow runs. Which addition resolves this?
Correct answer: A — Add `APPLY AS DELETE WHEN op = 'DELETE'` so the flow removes the matching key from the target table instead of upserting the row.
- A. `APPLY AS DELETE WHEN` is the clause that tells the flow which incoming rows represent deletions of an existing key, so the flow removes rather than upserts the matching row. Without it, every event — including deletes — is treated as an insert or update.
- B. Mapping a per-row delete flag to a table-wide truncate would wipe out every unrelated row in the target on any single delete event, which is both wrong in effect and not what a `DELETE` operation code represents.
- C. Dropping the `op` column from the output schema only changes what gets written to the target table; it does not tell the flow to interpret rows differently, so deletes would still be merged in as upserts.
- D. Switching to SCD Type 2 changes how history is retained, but a delete event is still merged as an upsert unless `APPLY AS DELETE WHEN` explicitly identifies which rows represent deletions.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Any Lakeflow pipeline edition can run AUTO CDC.Why is that wrong?
The CDC APIs need serverless pipelines or the Pro or Advanced edition.
Covered in Which CDC API, and what the pipeline needs
2.APPLY AS TRUNCATE WHEN can clear an SCD Type 2 history table.Why is that wrong?
Truncate handling is supported only for SCD Type 1. SCD Type 2 does not support truncate operations.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/ldp/cdcOfficial docs
“The AUTO CDC APIs replace the APPLY CHANGES APIs and have the same syntax.”
↩︎ Which CDC API, and what the pipeline needs“The AUTO CDC APIs are not supported by Apache Spark Declarative Pipelines.”
↩︎ Which CDC API, and what the pipeline needs“create a streaming table and then use the AUTO CDC ... INTO statement in SQL or the create_auto_cdc_flow() function in Python”
↩︎ Anatomy of an AUTO CDC flow“The API orders by the first field first, and in the event of a tie, considers the second field, and so on.”
↩︎ Anatomy of an AUTO CDC flow“SCD Type 2 preserves a complete history of changes by creating new rows for each version of a record”
↩︎ SCD Type 1 vs Type 2 on the same feed“Records with __END_AT = null are currently active.”
↩︎ SCD Type 1 vs Type 2 on the same feed“For initial hydration from a source with a change feed, use AUTO CDC with a once flow and then continue processing the change feed.”
↩︎ Late events, deletes, truncates and backfills“your pipeline must be configured to use serverless Lakeflow pipelines or the Lakeflow pipelines Pro or Advanced editions.”
↩︎ Exam trap 1“your pipeline must be configured to use serverless Lakeflow pipelines or the Lakeflow pipelines Pro or Advanced editions.”
↩︎ Prediction“It is supported only in the Python pipeline interface.”
↩︎ Checkpoint“Use SCD Type 2 to retain a history of records, either on all updates or on updates to a specified set of columns.”
↩︎ Checkpoint“The earlier UPDATE at sequenceNum=5 was dropped because a later update at sequenceNum=6 arrived.”
↩︎ Prediction - 2.
“It typically sends the history of data changes to Kafka topics or saves them as files.”
↩︎ Which CDC API, and what the pipeline needs - 3.
“update any rows in the target table that match the specified key(s) or insert a new row”
↩︎ Anatomy of an AUTO CDC flow“Specified columns must be sortable data types. This clause is required.”
↩︎ Anatomy of an AUTO CDC flow“It is safest to read from static or append-only sources.”
↩︎ Anatomy of an AUTO CDC flow“Bitemporal requires SYSTEM SEQUENCE BY and is in Beta.”
↩︎ SCD Type 1 vs Type 2 on the same feed“The APPLY AS TRUNCATE WHEN clause is supported only for SCD type 1.”
↩︎ Exam trap 2 - 4.
“you must include the __START_AT and __END_AT columns with the same data type as the sequence_by fields.”
↩︎ SCD Type 1 vs Type 2 on the same feed“the deleted row is temporarily retained as a tombstone in the underlying Delta table”
↩︎ Late events, deletes, truncates and backfills“Auto Loader does not guarantee file processing order”
↩︎ Late events, deletes, truncates and backfills“must be a batch DataFrame in this case, not a streaming DataFrame”
↩︎ Late events, deletes, truncates and backfills“Lakeflow pipelines use this sequencing to handle change events that arrive out of order.”
↩︎ Key concept“Set pipelines.cdc.tombstoneGCThresholdInSeconds to a value that exceeds the maximum expected delay between event arrival and pipeline execution.”
↩︎ Checkpoint