What you will be able to do
- Explain how the AUTO CDC APIs relate to the older APPLY CHANGES APIs and which pipeline configurations support them
- Declare a target streaming table and an AUTO CDC flow in both Python and SQL, choosing keys, sequencing, delete and truncate conditions, and column selection
- Predict the contents of an SCD Type 1 target when change events arrive out of order
Key concept
Sequenced upsert — An AUTO CDC flow matches each change event to a target row by its keys and decides which event wins by the sequencing column, not by arrival order. Inserts and updates become upserts; deletes and truncates happen only when you say which events mean that.
1.AUTO CDC: the new name for APPLY CHANGES
Applying a stream of change records (inserts, updates and deletes from a source database) to a table is harder than it looks. Events arrive late or out of order, deletes have to be honoured, and if you want history kept, someone has to open and close version rows. Lakeflow pipelines handle this for you with the AUTO CDC APIs, which compute slowly changing dimensions (SCD) Type 1 and Type 2 for you.
If you learned this feature under its old name, nothing about the syntax has changed. The AUTO CDC APIs replace the APPLY CHANGES APIs, and the two have the same syntax. In Python, create_auto_cdc_flow() replaces apply_changes() with an identical signature. The old names still work, but Databricks recommends the new ones.
Which API you use depends on what the source gives you:
- AUTO CDC: the source has a CDC feed. Available in both pipeline SQL (AUTO CDC ... INTO) and Python (create_auto_cdc_flow()).
- AUTO CDC FROM SNAPSHOT: there is no feed, only snapshots, and the API works out changes by comparing them. It is supported only in the Python interface.
This lesson covers the first. Two environment constraints matter. The pipeline must run on serverless Lakeflow pipelines or the Pro or Advanced edition. And the AUTO CDC APIs are a Databricks feature: they are not supported by open-source Apache Spark Declarative Pipelines.
Checkpoint 1 of 5· Check yourself
A team maintains a pipeline that still calls apply_changes(). What is the accurate statement about that code?
AUTO CDC is a rename. The APPLY CHANGES APIs are still available with the same syntax, but Databricks recommends moving to the new name.
“The APPLY CHANGES APIs are still available, but Databricks recommends using the AUTO CDC APIs in their place.”Source: docs.databricks.com
Sources1
2.Anatomy of an AUTO CDC flow
An AUTO CDC flow always takes two declarations. First, declare the target streaming table: create_streaming_table() in Python, or CREATE OR REFRESH STREAMING TABLE in SQL. The flow does not create it for you. Second, declare the flow that writes into it. In SQL that is CREATE FLOW ... AS AUTO CDC INTO, and the source must be a streaming source read with the STREAM keyword.
By default, INSERT and UPDATE events are upserted into the target. A row that matches the keys is updated; otherwise a new row is inserted. Everything else is controlled by the clauses below.
| Python parameter | SQL clause | What it controls |
|---|---|---|
| keys (required) | KEYS (required) | Column(s) that uniquely identify a row, used to match CDC events to target rows |
| sequence_by (required) | SEQUENCE BY (required) | Logical order of events; must be a sortable type; use struct() / STRUCT to break ties |
| apply_as_deletes | APPLY AS DELETE WHEN | Condition that turns an event into a DELETE instead of an upsert |
| apply_as_truncates | APPLY AS TRUNCATE WHEN | Condition that truncates the whole target table |
| column_list / except_column_list | COLUMNS (...) / COLUMNS * EXCEPT (...) | Which source columns land in the target (default: all) |
| stored_as_scd_type | STORED AS SCD TYPE 1 | SCD TYPE 2 | Overwrite in place (Type 1, the default) or keep history (Type 2) |
A few details here come up on the exam.
- Sequencing with ties. If one column can't fully order events, combine columns in a struct, for example sequence_by = struct("timestamp_col", "id_col") or SEQUENCE BY STRUCT(timestamp_col, id_col). The first field is compared first, and later fields only break ties.
- Deletes are kept as tombstones. So that a late event can't bring back a deleted row, the deleted row is kept for a while as a tombstone in the underlying Delta table, and a view hides it. The retention period defaults to two days and is set with the pipelines.cdc.tombstoneGCThresholdInSeconds table property. If Auto Loader feeds the flow, raise that threshold above the longest delay you expect, because Auto Loader does not guarantee file order.
- Dropping metadata columns. Operation codes and sequence numbers are usually excluded from the target with except_column_list / COLUMNS * EXCEPT.
Checkpoint 2 of 5· Match them up
Match each AUTO CDC option to its role
Tap a term, then the definition that fits it.
keys match events to rows, sequence_by decides which event wins, apply_as_deletes picks out deletes, and except_column_list removes columns from the target.
“This is used to identify which CDC events apply to specific records in the target table.”Source: docs.databricks.com
3.SCD Type 1 in practice: out-of-order updates
SCD Type 1 updates records in place, and no history is kept for updated rows. It is also the default: if you omit stored_as_scd_type / STORED AS, you get Type 1. The documentation's example feed has users 123–126 with an operation column and a sequenceNum. Two rows matter here. Mercedes (125) moves to Guadalajara at sequenceNum 6. Then an update to Mexicali at sequenceNum 5 arrives *after* it.
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_current")
dp.create_auto_cdc_flow(
target = "users_current",
source = "users",
keys = ["userId"],
sequence_by = col("sequenceNum"),
apply_as_deletes = expr("operation = 'DELETE'"),
apply_as_truncates = expr("operation = 'TRUNCATE'"),
except_column_list = ["operation", "sequenceNum"],
stored_as_scd_type = 1
)The target ends with three rows: 124 Raul/Oaxaca, 125 Mercedes/Guadalajara, 126 Lily/Cancun. Isabel (123) is gone because of her DELETE at sequence 6. Her Chihuahua update at sequence 5 was older than the delete, so it didn't bring her back. Mercedes has a single row, because Type 1 overwrites previous values.
The sample can also include a TRUNCATE event at sequenceNum 3. With that row enabled, the table is cleared at sequence 3, so 124 and 126 disappear. Only 125/Guadalajara remains, because its updates at sequence 5 and 6 come after the truncate.
Checkpoint 3 of 5· Fill the gap
Complete the SQL version of the SCD Type 1 flow. Which clause tells the pipeline how to order change events?
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"
?
sequenceNum
COLUMNS * EXCEPT
(operation, sequenceNum)
STORED AS
SCD TYPE 1;SEQUENCE BY is the required clause that gives the logical order of CDC events. ORDER BY and PARTITION BY are not AUTO CDC clauses, and TRACK HISTORY ON selects columns for SCD Type 2 history.
Source: docs.databricks.comCheckpoint 4 of 5· Check yourself
A flow omits stored_as_scd_type. The source sends three updates for the same key. How many rows exist for that key in the target?
stored_as_scd_type is optional and defaults to Type 1, which overwrites the row in place.
“The default is SCD type 1.”Source: docs.databricks.com
Checkpoint 5 of 5· Exam question
A CDC feed for an `orders` table sometimes contains multiple change events with an identical `updated_at` timestamp, produced by a nightly bulk-correction job that stamps every touched row with the same value. A data engineer needs the AUTO CDC flow to deterministically pick the latest event among ties, using a secondary `event_seq` integer column as the tiebreaker. Which `sequence_by` configuration achieves this in the Python API?
Correct answer: A — `sequence_by=struct(col("updated_at"), col("event_seq"))`, since Lakeflow Declarative Pipelines resolve a struct passed to `sequence_by` column-by-column, only consulting `event_seq` when `updated_at` values tie.
- A. Wrapping the columns in a struct is correct because Lakeflow Declarative Pipelines compares struct fields in order, so ties on the primary timestamp are broken by the next field, giving the deterministic ordering the scenario needs.
- B. A plain Python list is not an accepted form for combining multiple ordering columns into one comparable key; `sequence_by` needs a single column expression, and a bare list does not produce column-by-column tie-breaking.
- C. Adding a timestamp and an integer together produces a value with no meaningful ordering guarantee, since the arithmetic can collide or invert the intended precedence between the two source columns.
- D. Using only `event_seq` discards the timestamp entirely, and there is no basis in the scenario for assuming `event_seq` alone is globally monotonic across the whole feed rather than just within a single load batch.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.APPLY CHANGES was removed, so pipelines using apply_changes() or APPLY CHANGES INTO break.Why is that wrong?
AUTO CDC is a rename with the same syntax. The old APIs still work; Databricks only recommends switching to the new names.
Covered in AUTO CDC: the new name for APPLY CHANGES
2.create_auto_cdc_flow() creates its own target table, so declaring one is optional.Why is that wrong?
You must declare the target streaming table first, with create_streaming_table() or CREATE OR REFRESH STREAMING TABLE, and the flow then writes into it.
Covered in Anatomy of an AUTO CDC flow
3.In SCD Type 1 the last event to arrive wins.Why is that wrong?
The event with the highest sequence value wins. A lower-sequenced update that arrives late is dropped.
Covered in SCD Type 1 in practice: out-of-order updates
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.”
↩︎ AUTO CDC: the new name for APPLY CHANGES“your pipeline must be configured to use serverless Lakeflow pipelines or the Lakeflow pipelines Pro or Advanced editions.”
↩︎ AUTO CDC: the new name for APPLY CHANGES“The AUTO CDC APIs are not supported by Apache Spark Declarative Pipelines.”
↩︎ AUTO CDC: the new name for APPLY CHANGES“It is supported only in the Python interface.”
↩︎ AUTO CDC: the new name for APPLY CHANGES“Use SCD Type 1 to update records directly. History is not retained for updated records.”
↩︎ SCD Type 1 in practice: out-of-order updates“User 125 (Mercedes) shows only the latest city (Guadalajara) because SCD Type 1 overwrites previous values.”
↩︎ SCD Type 1 in practice: out-of-order updates“This means that records 124 and 126 are not in the table”
↩︎ SCD Type 1 in practice: out-of-order updates“The earlier UPDATE at sequenceNum=5 was dropped because a later update at sequenceNum=6 arrived.”
↩︎ Exam trap 3“The APPLY CHANGES APIs are still available, but Databricks recommends using the AUTO CDC APIs in their place.”
↩︎ Checkpoint“The earlier UPDATE at sequenceNum=5 was dropped because a later update at sequenceNum=6 arrived.”
↩︎ Prediction - 2.
“The source must be a streaming source.”
↩︎ Anatomy of an AUTO CDC flow - 3.
“the default behavior for INSERT and UPDATE events is to upsert CDC events from the source”
↩︎ Anatomy of an AUTO CDC flow“it orders by the first struct field first, then by the second field if there's a tie, and so on.”
↩︎ Anatomy of an AUTO CDC flow“The retention interval defaults to two days, and can be configured with the pipelines.cdc.tombstoneGCThresholdInSeconds table property.”
↩︎ Anatomy of an AUTO CDC flow“If you use Auto Loader as the source for your CDC pipeline, Auto Loader does not guarantee file processing order.”
↩︎ Anatomy of an AUTO CDC flow“Lakeflow pipelines use this sequencing to handle change events that arrive out of order.”
↩︎ Key concept“This function replaces the previous function apply_changes(). The two functions have the same signature.”
↩︎ Exam trap 1“You must declare a target streaming table to apply changes into.”
↩︎ Exam trap 2“This is used to identify which CDC events apply to specific records in the target table.”
↩︎ Checkpoint“The default is SCD type 1.”
↩︎ Checkpoint