CertSafari
    Databricks Certified Data Engineer Professional - October 9 onwards· Lessons

    Domain 1 · Lesson 7/45

    AUTO CDC in Lakeflow pipelines: keys, sequencing and SCD Type 1 vs Type 2

    Implement CDC pipelines using AUTO CDC APIs (formerly APPLY CHANGES) in Lakeflow Pipelines, including SCD Type 1 and Type 2 via stored_as_scd_type.

    18 min read
    1.92% of exam
    4 sources
    Published 22 Oct 2026
    Docs as of 17 Oct 2026

    What you will be able to do

    • Write an AUTO CDC flow in SQL or Python that applies a change feed to a streaming table using keys and sequence_by
    • Break sequencing ties with a struct and explain how late-arriving events and deletes are handled
    • Choose between stored_as_scd_type 1 and 2, and read the __START_AT and __END_AT columns of a Type 2 target
    • Limit which columns open a new Type 2 version with track_history_column_list or TRACK HISTORY ON

    Key concept

    Sequenced upsert by key — AUTO CDC matches each change event to a target row through its key columns. It uses the sequence_by value, not arrival order, to decide which event is newest. With stored_as_scd_type 1 that newest event overwrites the row; with type 2 it closes the current version and opens a new one.

    1.What an AUTO CDC flow does

    A change feed arrives as a stream of events, such as insert this customer, update that address or delete this account, and a dimension table must end up showing their net effect. Writing that by hand means a MERGE per micro-batch, deduplication, and logic for events that arrive late. In Lakeflow Declarative Pipelines the AUTO CDC API does that work: you name a source, the key columns and a sequencing column, and the pipeline keeps a target streaming table in sync.

    AUTO CDC is the new name for APPLY CHANGES. In SQL the statement is AUTO CDC ... INTO, the replacement for APPLY CHANGES INTO. In Python it is dp.create_auto_cdc_flow(), the replacement for apply_changes(). The two Python functions have the same signature. The old names still work, but exam questions and current code use the new ones.

    By default, every insert and update event is an upsert. If the event's key matches a row in the target, that row is updated. If nothing matches, a new row is inserted. Deletes are not inferred. You declare which events count as deletes, which is covered below. The target must be a streaming table that you declare first, with CREATE OR REFRESH STREAMING TABLE in SQL or dp.create_streaming_table() in Python. The source must be read as a stream, so SQL uses FROM stream(...).

    The full create_auto_cdc_flow() signature. Only target, source, keys and sequence_by are required.python
    dp.create_auto_cdc_flow(
      target = "<target-table>",
      source = "<data-source>",
      keys = ["key1", "key2", "keyN"],
      sequence_by = "<sequence-column>",
      system_sequence_by = None, # optional
      ignore_null_updates = False, # optional
      ignore_null_updates_column_list = None, # optional
      ignore_null_updates_except_column_list = None, # optional
      columns_to_update = None, # optional
      apply_as_deletes = None, # optional
      apply_as_truncates = None, # optional
      column_list = None, # optional
      except_column_list = None, # optional
      stored_as_scd_type = "1", # optional
      track_history_column_list = None, # optional
      track_history_except_column_list = None, # optional
      name = None, # optional
      once = False # optional
    )

    The API has three practical limits. First, the pipeline must run on serverless Lakeflow pipelines or on the Pro or Advanced edition. Second, AUTO CDC needs a source that emits change events. If the source system can only provide periodic full snapshots, use the sibling API AUTO CDC FROM SNAPSHOT, which compares consecutive snapshots to work out the changes. Third, the AUTO CDC APIs are not available in open-source Apache Spark Declarative Pipelines. That matters when you choose between engines in Structured Streaming vs Spark Declarative Pipelines. For the wider ingestion design, such as Delta or Iceberg targets, see incremental CDC pipelines.

    Checkpoint 1 of 8· Fill the gap

    This is the full create_auto_cdc_flow() signature. A flow that leaves out stored_as_scd_type overwrites matching rows and keeps no history. Which default value fills the blank?

    dp.create_auto_cdc_flow(
      target = "<target-table>",
      source = "<data-source>",
      keys = ["key1", "key2", "keyN"],
      sequence_by = "<sequence-column>",
      system_sequence_by = None, # optional
      ignore_null_updates = False, # optional
      ignore_null_updates_column_list = None, # optional
      ignore_null_updates_except_column_list = None, # optional
      columns_to_update = None, # optional
      apply_as_deletes = None, # optional
      apply_as_truncates = None, # optional
      column_list = None, # optional
      except_column_list = None, # optional
      stored_as_scd_type =  ? , # optional
      track_history_column_list = None, # optional
      track_history_except_column_list = None, # optional
      name = None, # optional
      once = False # optional
    )

    Sources123

    2.Keys and sequence_by: matching rows and ordering events

    **keys identifies the row an event belongs to.** It is the column, or combination of columns, that uniquely identifies a record in the source, for example ["userId"] or ["userId", "orderId"]. A single key column is fine for both SCD types. You can pass plain strings or col() expressions, but a col() argument cannot be qualified: col(userId) works and col(source.userId) does not.

    **sequence_by decides which event wins, whatever order the events arrive in.** It must be a sortable type. An integer sequence number works as well as a timestamp. The pipeline compares sequence values per key, so an event that arrives late with a lower value than one already applied does not overwrite the newer state. In SQL the clause is SEQUENCE BY, and it is required.

    Sample change feed for user 125. The last two rows arrive in the order shown.
    userIdcityoperationsequenceNum
    125TijuanaINSERT2
    125GuadalajaraUPDATE6
    125MexicaliUPDATE5

    In a Type 1 target, user 125 ends up in Guadalajara. The Mexicali update arrives after the Guadalajara update, but its sequence number 5 is lower than 6, so the target ignores it. Applying events in arrival order would leave the row showing Mexicali, which is wrong.

    Ties are broken with a struct, not a list. sequence_by accepts three forms: a string such as "sequenceNum", a col(), or a struct(). A struct like struct("timestamp_col", "id_col") sorts on the first field, and looks at the second only when the first ties. In SQL the same idea is SEQUENCE BY STRUCT(timestamp_col, id_col). Adding two columns together is no substitute. A large id paired with an older timestamp can produce a bigger sum than a newer event, which reorders events that were never tied.

    Checkpoint 2 of 8· Check yourself

    A Type 1 AUTO CDC flow uses keys = ["orderId"] and sequence_by = "seq". For order 42, three events arrive in this order: status placed (seq 3), then shipped (seq 9), then packed (seq 7). What does the target show for order 42 after processing?

    Checkpoint 3 of 8· 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?

    Sources21

    3.Deletes, truncates and choosing columns

    **apply_as_deletes turns matching events into deletes instead of upserts.** You pass a condition, either as a string such as "Operation = 'DELETE'" or as expr(...). In SQL the clause is APPLY AS DELETE WHEN operation = "DELETE". Out-of-order deletes are handled with a tombstone. The deleted row stays in the underlying Delta table, and a view filters it out. This way a late update with a lower sequence number cannot bring the record back.

    Tombstones are kept for two days by default. You change this with the pipelines.cdc.tombstoneGCThresholdInSeconds table property. This setting matters when the source is Auto Loader, because Auto Loader does not guarantee the order in which it processes files. If a feed can deliver an event three days late, a two-day threshold means the tombstone may already be gone when the late event arrives. Set the threshold above the longest delay you expect between an event arriving and the pipeline running.

    **apply_as_truncates works only on SCD Type 1.** It treats a matching event as a command to clear the whole target table. A Type 2 target keeps historical versions, and it does not support truncate. A flow that combines APPLY AS TRUNCATE WHEN with STORED AS SCD TYPE 2 is therefore invalid. Use truncate sparingly even on Type 1, because one matching event empties the table.

    **column_list and except_column_list control which source columns reach the target.** Use one or the other. Change feeds usually carry bookkeeping columns, such as the operation code and the sequence number, that do not belong in a dimension table. except_column_list = ["operation", "sequenceNum"] removes them. The SQL equivalent is COLUMNS * EXCEPT (operation, sequenceNum). If you set neither, every column is kept.

    Some sources send only the columns that changed and leave the rest null. By default those nulls overwrite the existing values in the target. Setting ignore_null_updates = True (SQL: IGNORE NULL UPDATES) keeps the existing value whenever an incoming column is null.

    Checkpoint 4 of 8· Check yourself

    In a code review you find a SQL flow that declares STORED AS SCD TYPE 2 and also APPLY AS TRUNCATE WHEN operation = "TRUNCATE". What happens with this flow definition?

    Checkpoint 5 of 8· Exam question

    A pipeline defines the following SQL flow to capture changes into a dimension table: CREATE OR REFRESH STREAMING TABLE dim_products; CREATE FLOW products_cdc_flow AS AUTO CDC INTO dim_products FROM STREAM(cdc_products) KEYS (product_id) APPLY AS TRUNCATE WHEN operation = "TRUNCATE" SEQUENCE BY sequence_num STORED AS SCD TYPE 2; Pipeline validation rejects this flow definition. What is the cause?

    Sources2

    4.stored_as_scd_type: overwrite in place or keep history

    **stored_as_scd_type decides what an update does to the existing row.** Set it to 1 for SCD Type 1 or 2 for SCD Type 2. In SQL the clause is STORED AS SCD TYPE 1 or STORED AS SCD TYPE 2. If you omit it, you get Type 1. Type 1 overwrites the row, so only the latest state survives. Type 2 keeps every version, and you can still see that user 125 lived in Tijuana before Guadalajara. A third value, "bitemporal", extends Type 2 with a second time axis, system time, and is in Beta.

    How the two SCD types store the same change feed
    AspectSCD Type 1SCD Type 2
    Effect of an updateOverwrites the matching rowCloses the current version and opens a new one
    HistoryNot retainedRetained on every update, or only on changes to tracked columns
    Extra columnsNone__START_AT and __END_AT, holding sequence_by values
    Current rowThe only row per keyThe version whose __END_AT is null
    Primary keykeyskeys plus coalesce(__START_AT, __END_AT)
    apply_as_truncatesSupportedNot supported

    **__START_AT and __END_AT mark when each version was valid, measured in sequence values.** The pipeline copies the sequence_by value of the event that opened a version into __START_AT. When a later event supersedes or deletes that version, its __END_AT is set. The current version is the one whose __END_AT is null. With the sample feed, user 123 ends up with two versions, the last one closed at sequence 6 by the delete. User 125 ends up with three versions, one for each city.

    These columns have a type requirement. If you declare a schema for a Type 2 target, you must include __START_AT and __END_AT with the same data type as sequence_by, for example BIGINT for an integer sequence number. When sequence_by is a struct, the columns must be the same struct type. If downstream consumers expect names like valid_from and valid_to, put a view over the target and alias the columns there. Do not rename them in the table.

    If you change a Type 2 target directly with DML, which is allowed for targets published to Unity Catalog, you must write these columns yourself. Use values that keep the versions in the right order. In the statement below, the inserted row starts at sequence 5 and its null __END_AT makes it the active version.

    Manually inserting an active SCD Type 2 row: start sequence 5, no endsql
    INSERT INTO my_streaming_table (id, name, __START_AT, __END_AT) VALUES (123, 'John Doe', 5, NULL);

    Checkpoint 6 of 8· Fill the gap

    To add a row by hand to an SCD Type 2 target published to Unity Catalog, you write the sequence columns yourself. The row must start at sequence 5 and be the current version of user 123. Which value fills the blank?

    INSERT INTO my_streaming_table (id, name, __START_AT, __END_AT) VALUES (123, 'John Doe', 5,  ? );

    Sources2134

    5.Track history on selected columns only

    By default, a Type 2 target opens a new version when any column changes. That includes audit columns, such as a batch id or an ingestion timestamp, that change on every load. If the feed re-sends unchanged business data with a new etl_batch_id, every reload adds a version, and the history fills up with versions that differ only in audit columns.

    **track_history_column_list and track_history_except_column_list choose which columns open a new version.** Use the first to list the columns that should be tracked, or the second to list the columns that should not. The SQL equivalent is TRACK HISTORY ON (...) or TRACK HISTORY ON * EXCEPT (...). Omitting the clause is the same as TRACK HISTORY ON *, which means every column is tracked. Type 2 works without it.

    An untracked column is still written to the target. When only untracked columns change, the current version is updated in place instead of a new version being added. In the question above, the active row gets the new etl_batch_id and the version count stays the same. Tracking affects only whether a new version is created. SEQUENCE BY is still required, because the pipeline still needs to know which event is newest.

    SCD Type 2 with deletes, bookkeeping columns dropped, and city changes updated in place without a new versionsql
    CREATE OR REFRESH STREAMING TABLE target;
    
    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);

    COLUMNS and TRACK HISTORY ON do different jobs. COLUMNS * EXCEPT (operation, sequenceNum) removes those columns from the table entirely. TRACK HISTORY ON * EXCEPT (city) keeps city in the table but stops a city change from creating a new version. In Python the same flow sets except_column_list and track_history_except_column_list. The data quality rules you would put in front of a flow like this belong to pipeline expectations.

    Checkpoint 7 of 8· Fill the gap

    In this SCD Type 2 flow, operation and sequenceNum must not appear in the target at all. city must stay in the table, but a change to city should not open a new version. Which keyword fills the blank?

    CREATE OR REFRESH STREAMING TABLE target;
    
    CREATE FLOW flow
    AS AUTO CDC INTO
      target
    FROM stream(cdc_data.users)
      KEYS (userId)
      APPLY AS DELETE WHEN operation = "DELETE"
      SEQUENCE BY sequenceNum
       ?  * EXCEPT (operation, sequenceNum)
      STORED AS SCD TYPE 2
      TRACK HISTORY ON * EXCEPT (city);

    Checkpoint 8 of 8· Exam question

    A team implements SCD Type 2 for an employees dimension table. The CDC source occasionally emits updates that only change an etl_batch_id audit column, with no business-meaningful change to the row. The team wants a new history version created only when a column other than etl_batch_id or _ingested_at changes. Which statements correctly describe how to achieve this and how history tracking behaves? (Select all that apply)(Select 3)

    0 of 3 selected

    Sources13

    Sources

    (4)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “The AUTO CDC APIs replace the APPLY CHANGES APIs and have the same syntax.”
      ↩︎ What an AUTO CDC flow does
      “To use the CDC APIs, your pipeline must be configured to use serverless Lakeflow pipelines or the Lakeflow pipelines Pro or Advanced editions.”
      ↩︎ What an AUTO CDC flow does
      “AUTO CDC FROM SNAPSHOT: Use this when CDC is not enabled on the source database and only snapshots are available.”
      ↩︎ What an AUTO CDC flow does
      “The AUTO CDC APIs are not supported by Apache Spark Declarative Pipelines.”
      ↩︎ What an AUTO CDC flow does
      “In the SCD Type 1 example, the last UPDATE operations arrive late and are dropped from the target table, demonstrating out-of-order event handling.”
      ↩︎ Keys and sequence_by: matching rows and ordering events
      “Use SCD Type 1 to update records directly. History is not retained for updated records.”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “Records with __END_AT = null are currently active.”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “User 123 has two versions (ended at sequence 6 when deleted). User 125 has three versions showing city changes.”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “By default, SCD Type 2 creates a new version whenever any column value changes.”
      ↩︎ Track history on selected columns only
      “changes to other columns update the current version in place rather than generating a new history record”
      ↩︎ Track history on selected columns only
      “changes to other columns update the current version in place rather than generating a new history record”
      ↩︎ Exam trap 3
    2. 2.
      “This function replaces the previous function apply_changes(). The two functions have the same signature.”
      ↩︎ What an AUTO CDC flow does
      “update any rows in the target table that match the specified key(s) or insert a new row”
      ↩︎ What an AUTO CDC flow does
      “The column or combination of columns that uniquely identify a row in the source data.”
      ↩︎ Keys and sequence_by: matching rows and ordering events
      “Lakeflow pipelines use this sequencing to handle change events that arrive out of order. The specified column must be a sortable data type.”
      ↩︎ Keys and sequence_by: matching rows and ordering events
      “it orders by the first struct field first, then by the second field if there's a tie, and so on.”
      ↩︎ Keys and sequence_by: matching rows and ordering events
      “the deleted row is temporarily retained as a tombstone in the underlying Delta table”
      ↩︎ Deletes, truncates and choosing columns
      “The retention interval defaults to two days, and can be configured with the pipelines.cdc.tombstoneGCThresholdInSeconds table property.”
      ↩︎ Deletes, truncates and choosing columns
      “If you use Auto Loader as the source for your CDC pipeline, Auto Loader does not guarantee file processing order.”
      ↩︎ Deletes, truncates and choosing columns
      “The apply_as_truncates parameter is supported only for SCD type 1. SCD type 2 does not support truncate operations.”
      ↩︎ Deletes, truncates and choosing columns
      “Use column_list to specify the complete list of columns to include. Use except_column_list to specify the columns to exclude.”
      ↩︎ Deletes, truncates and choosing columns
      “Set to True when source events only include the columns that changed, so unchanged columns are not overwritten with null.”
      ↩︎ Deletes, truncates and choosing columns
      “Set to 1 for SCD type 1, 2 for SCD type 2”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “Lakeflow pipelines use this sequencing to handle change events that arrive out of order. The specified column must be a sortable data type.”
      ↩︎ Key concept
      “A struct() combining multiple columns to break ties: struct("timestamp_col", "id_col")”
      ↩︎ Exam trap 1
      “The apply_as_truncates parameter is supported only for SCD type 1. SCD type 2 does not support truncate operations.”
      ↩︎ Exam trap 2
      “Set to 1 for SCD type 1, 2 for SCD type 2”
      ↩︎ Exam trap 4
      “Lakeflow pipelines use this sequencing to handle change events that arrive out of order.”
      ↩︎ Checkpoint
    3. 3.
      “The source must be a streaming source.”
      ↩︎ What an AUTO CDC flow does
      “you must also include the __START_AT and __END_AT columns with the same data type as the sequence_by field”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “The default is to track history for all the output columns when there are any changes, equivalent to TRACK HISTORY ON *.”
      ↩︎ Track history on selected columns only
      “The APPLY AS TRUNCATE WHEN clause is supported only for SCD type 1. SCD type 2 does not support the truncate operation.”
      ↩︎ Checkpoint
    4. 4.
      “you must ensure that DML statements use valid values for these columns to maintain the proper ordering of records”
      ↩︎ stored_as_scd_type: overwrite in place or keep history
      “The primary key is the keys parameter or KEYS clause plus the return value from the coalesce(__START_AT, __END_AT) operation”
      ↩︎ stored_as_scd_type: overwrite in place or keep history

    Spotted a mistake, or was something unclear? Tell us.