What you will be able to do
- Explain that an AUTO CDC target is a Delta-backed streaming table, and configure the UniForm properties that make it readable by Iceberg clients
- Use AUTO CDC FROM SNAPSHOT when a source provides only snapshots, and predict its insert, update and delete behaviour
- Choose between IGNORE NULL UPDATES ON and COLUMNS TO UPDATE for partial updates
- Run DML against a CDC target, read its change data feed and interpret its metrics
1.The target table: Delta underneath, Iceberg on read
Whichever CDC API you use, the target is a streaming table that the pipeline manages, and its storage is Delta. The reference docs say so directly: deleted rows are kept as tombstones "in the underlying Delta table". You can also consume a CDC target's changes "in the same way that you read a change data feed from other Delta tables".
Iceberg enters through read compatibility. The sources here describe making a pipeline's streaming table readable by Iceberg clients through UniForm table properties. They do not describe a separate Iceberg-native streaming table target.
To make a dataset readable by Iceberg clients, you "add the following UniForm Iceberg V3 properties in addition to the external metadata property". You set them in the TBLPROPERTIES of CREATE OR REFRESH STREAMING TABLE:
| Property | Purpose |
|---|---|
| 'pipelines.externalMetadata.enabled' = 'true' | Enable external access for the table; overrides the pipeline-level setting |
| 'delta.columnMapping.mode' = 'name' | Column mapping is required for Iceberg |
| 'delta.enableRowTracking' = 'true' | Enable row tracking for Iceberg reads |
| 'delta.universalFormat.enabledFormats' = 'iceberg' | Enable Iceberg reads |
| 'delta.enableIcebergCompatV3' = 'true' | Use Iceberg V3 for Iceberg reads |
The external metadata property can be set for the whole pipeline or for one table: "This table-level setting takes precedence over the pipeline configuration when both are set." The UniForm properties do the Iceberg-specific work. For example, "Column mapping is required for Iceberg."
Checkpoint 1 of 5· Check yourself
A team sets only 'pipelines.externalMetadata.enabled' = 'true' on its SCD Type 1 streaming table, and an Iceberg client still can't read it as Iceberg. What is missing?
External metadata enables external access. Iceberg reads also need the UniForm properties.
“add the following UniForm Iceberg V3 properties in addition to the external metadata property”Source: docs.databricks.com
2.No change feed: AUTO CDC FROM SNAPSHOT
Some sources have no CDC feed, only periodic full copies. For these, AUTO CDC FROM SNAPSHOT works out the changes itself: "This API compares snapshots to determine changes and then processes them." It is available only in Python, through create_auto_cdc_from_snapshot_flow(). "You can read snapshots from a Delta table, cloud storage files, or JDBC directly."
from pyspark import pipelines as dp
dp.create_auto_cdc_from_snapshot_flow(
target = "<target-table>",
source = Any,
keys = ["key1", "key2", "keyN"],
stored_as_scd_type = "1",
track_history_column_list = None,
track_history_except_column_list = None
)There is no sequence_by and no delete condition, because the order of the snapshots is the sequence. The comparison has three outcomes. A key that is new gets inserted. A key that already exists is updated only if any of its values changed. A key that has disappeared is deleted: "Rows with keys present in the target but no longer present in the source are deleted." Like the feed-based API, it supports SCD Type 1 and Type 2.
One constraint matters if you are thinking of combining the two APIs: "You cannot target the same streaming table with both create_auto_cdc_from_snapshot_flow() and create_auto_cdc_flow()."
Checkpoint 2 of 5· Check yourself
Yesterday's snapshot contained customer 42. Today's snapshot does not. What does AUTO CDC FROM SNAPSHOT do with customer 42 in an SCD Type 1 target?
Snapshot processing treats a key that disappears as a delete. It has no apply_as_deletes parameter.
“Rows with keys present in the target but no longer present in the source are deleted.”Source: docs.databricks.com
Checkpoint 3 of 5· Exam question
A team replaces a hand-written `MERGE INTO` upsert job with: ```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) APPLY AS DELETE WHEN op = 'DELETE' SEQUENCE BY change_version STORED AS SCD TYPE 2; ``` Which statements correctly describe the resulting `dim_customer` table? (Select all that apply)(Select 3)
Correct answers: A, B, C — Each version of the customer row is retained as a separate record, with `__START_AT` and `__END_AT` columns marking its validity window.; The row representing the customer's current state has its `__END_AT` column set to null, while every closed-out prior version has a non-null end value.; `dim_customer` must be declared as a streaming table, since AUTO CDC INTO writes only to streaming table targets, never to materialized views.
- A. SCD Type 2 storage keeps one row per version of a key, and the `__START_AT`/`__END_AT` pair records exactly which period each version was the active state, which is the mechanism that preserves full history.
- B. A null `__END_AT` is the documented marker for the currently active version of a key; every superseded version instead carries the sequencing value at which it was closed out, which is how a query can filter to only current rows.
- C. AUTO CDC INTO applies changes incrementally against a target that supports row-level upserts and history tracking, which is why the target must be declared as a streaming table rather than a materialized view.
- D. AUTO CDC INTO has no built-in time-based pruning of SCD Type 2 history, and `VACUUM` retention governs physical file cleanup for time travel, not the logical historical rows a CDC flow writes — this pairing of mechanisms is invented.
- E. The sequencing column is not automatically stripped from the output; whether a column appears in the target is controlled by `COLUMNS`/`except_column_list`, so claiming sequencing columns are always dropped misstates how output columns are chosen.
- F. For SCD Type 2, a delete event closes out the row that was already active by setting its `__END_AT`, using a tombstone mechanism, rather than inserting a fresh row that starts out already ended — that would defeat the purpose of tracking when a version stopped being valid.
3.Partial updates: absent column or explicit null?
Feed-based AUTO CDC has one more choice to make when a source sends only the columns that changed. By default, a null in an incoming update overwrites the target value: "The default is to overwrite existing columns with null values." Plain IGNORE NULL UPDATES goes to the other extreme. It treats every null as "leave unchanged", "so it cannot apply an explicit null". Two refinements let you tell the two cases apart:
| Method | When to use | Behavior |
|---|---|---|
| IGNORE NULL UPDATES ON columnList | A small, fixed set of columns should ignore nulls | Listed columns keep their target value on null; others apply explicit nulls |
| IGNORE NULL UPDATES ON * EXCEPT (exceptColumnList) | Most columns should ignore nulls | Listed columns apply explicit nulls; all others keep their target value on null |
| COLUMNS TO UPDATE | Each record updates a different set of columns | A source array<string> column names the columns to write, nulls included |
Rule of thumb: choose COLUMNS TO UPDATE when the producer knows which columns changed in each record, for example when several producers write to the same source. Choose IGNORE NULL UPDATES ON when the pipeline owner knows the fixed set of updatable columns in advance. The two can't be mixed, and "COLUMNS TO UPDATE cannot be combined with IGNORE NULL UPDATES, and is not supported for bitemporal tables."
Checkpoint 4 of 5· Match them up
Match each partial-update method to its behaviour
Tap a term, then the definition that fits it.
The two IGNORE NULL UPDATES ON forms are mirror images. COLUMNS TO UPDATE moves the decision into the data itself.
“A source column names the columns to update for each change record.”Source: docs.databricks.com
4.Operating the target: DML, change feed and metrics
If the pipeline publishes to Unity Catalog, you "can use data manipulation language (DML) statements, including insert, update, delete, and merge statements, to modify the target streaming tables". There are two limits. Schema-changing DML is not supported. On an SCD Type 2 target, you also own the ordering columns: "you must ensure that DML statements use valid values for these columns to maintain the proper ordering of records".
INSERT INTO my_streaming_table (id, name, __START_AT, __END_AT) VALUES (123, 'John Doe', 5, NULL);DML on the target affects anything that streams from it downstream. Streaming requires append-only sources, so a downstream reader needs skipChangeCommits, which makes it ignore commits that delete or modify records. If downstream consumers expect different names for __START_AT and __END_AT, put a view over the target instead of renaming the columns.
To pass the CDC target's own changes downstream, read its change data feed. Two requirements apply: "The target streaming table must be published to Unity Catalog", and you need Databricks Runtime 15.2 or above. Updates usually appear as update_preimage and update_postimage. If a primary key changes, they appear as insert and delete. For SCD Type 2, the primary key includes coalesce(__START_AT, __END_AT).
Finally, monitoring. AUTO CDC flows report num_upserted_rows and num_deleted_rows. The usual "num_output_rows metric, output for non-CDC flows, is not captured for AUTO CDC queries." These metrics come only from AUTO CDC queries, not from AUTO CDC FROM SNAPSHOT.
Checkpoint 5 of 5· Check yourself
A dashboard built on num_output_rows shows nothing for your AUTO CDC flow. Which metrics should it use?
AUTO CDC queries report upserted and deleted row counts in place of num_output_rows.
“num_upserted_rows: The number of output rows upserted into the dataset during an update.”Source: docs.databricks.com
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Adding USING ICEBERG to CREATE OR REFRESH STREAMING TABLE is how you make an AUTO CDC target readable as Iceberg.Why is that wrong?
The USING ICEBERG shortcut is documented for materialized views. Streaming tables get Iceberg reads from UniForm Iceberg V3 TBLPROPERTIES plus the external metadata property.
Covered in The target table: Delta underneath, Iceberg on read
2.You can seed a target with snapshot-based CDC and then keep it current with a feed-based AUTO CDC flow on the same table.Why is that wrong?
One streaming table cannot be the target of both create_auto_cdc_from_snapshot_flow() and create_auto_cdc_flow(). For initial hydration from a feed source, use an AUTO CDC once flow.
Covered in No change feed: AUTO CDC FROM SNAPSHOT
Practise it for real
Run the documented users change feed through SCD Type 1 and SCD Type 2 AUTO CDC flows and confirm the documented results.
1.In your pipeline's explorations folder (not transformations), run the sample-data statements that create main.cdc_tutorial.users_cdf with the eight INSERT/UPDATE/DELETE rows.
Why: The sample data is setup, not part of the pipeline definition, so it must not run on every update.
You should see: A users_cdf table with eight change records, including two sequence-5 updates listed after the sequence-6 events.
2.Add the SQL SCD Type 1 flow (CREATE OR REFRESH STREAMING TABLE users_current; CREATE FLOW apply_cdc AS AUTO CDC INTO ...) to the transformations folder and run a pipeline update on serverless or the Pro/Advanced edition.
Why: The CDC APIs need serverless or the Pro or Advanced edition.
You should see: users_current holds three rows: 124 Oaxaca, 125 Guadalajara, 126 Cancun. User 123 is gone.
3.Uncomment the TRUNCATE row at sequenceNum 3 in the sample data, recreate it, and run a full refresh.
Why: This shows that truncation is also ordered by SEQUENCE BY.
You should see: Only 125 Mercedes/Guadalajara remains.
4.Add the Python SCD Type 2 flow targeting users_history and run the pipeline.
Why: This compares history retention with in-place updates on identical input.
You should see: Seven rows with __START_AT/__END_AT. Rows 124, 125 Guadalajara and 126 have __END_AT null.
Stuck? Get a nudge
If the Type 2 table shows fewer rows than expected, check that operation and sequenceNum are excluded and that stored_as_scd_type is "2".
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“the deleted row is temporarily retained as a tombstone in the underlying Delta table”
↩︎ The target table: Delta underneath, Iceberg on read - 2.https://docs.databricks.com/aws/en/ldp/cdc-advancedOfficial docs
“in the same way that you read a change data feed from other Delta tables”
↩︎ The target table: Delta underneath, Iceberg on read“so it cannot apply an explicit null”
↩︎ Partial updates: absent column or explicit null?“COLUMNS TO UPDATE cannot be combined with IGNORE NULL UPDATES, and is not supported for bitemporal tables.”
↩︎ Partial updates: absent column or explicit null?“can use data manipulation language (DML) statements, including insert, update, delete, and merge statements, to modify the target streaming tables”
↩︎ Operating the target: DML, change feed and metrics“you must ensure that DML statements use valid values for these columns to maintain the proper ordering of records”
↩︎ Operating the target: DML, change feed and metrics“The target streaming table must be published to Unity Catalog.”
↩︎ Operating the target: DML, change feed and metrics“num_output_rows metric, output for non-CDC flows, is not captured for AUTO CDC queries.”
↩︎ Operating the target: DML, change feed and metrics“A source column names the columns to update for each change record.”
↩︎ Checkpoint“num_upserted_rows: The number of output rows upserted into the dataset during an update.”
↩︎ Checkpoint - 3.
“add the following UniForm Iceberg V3 properties in addition to the external metadata property”
↩︎ The target table: Delta underneath, Iceberg on read“This table-level setting takes precedence over the pipeline configuration when both are set.”
↩︎ The target table: Delta underneath, Iceberg on read“Column mapping is required for Iceberg.”
↩︎ The target table: Delta underneath, Iceberg on read“For materialized views, you can use the equivalent USING ICEBERG syntax instead.”
↩︎ Exam trap 1“For materialized views, you can use the equivalent USING ICEBERG syntax instead.”
↩︎ Prediction - 4.https://docs.databricks.com/aws/en/ldp/cdcOfficial docs
“This API compares snapshots to determine changes and then processes them.”
↩︎ No change feed: AUTO CDC FROM SNAPSHOT“You can read snapshots from a Delta table, cloud storage files, or JDBC directly.”
↩︎ No change feed: AUTO CDC FROM SNAPSHOT - 5.https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes-from-snapshotOfficial docs
“it is updated only if any of the values in the row have changed”
↩︎ No change feed: AUTO CDC FROM SNAPSHOT“You cannot target the same streaming table with both create_auto_cdc_from_snapshot_flow() and create_auto_cdc_flow().”
↩︎ Exam trap 2“Rows with keys present in the target but no longer present in the source are deleted.”
↩︎ Checkpoint - 6.
“The default is to overwrite existing columns with null values.”
↩︎ Partial updates: absent column or explicit null?