Task 1.7: 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.

• AAdd last_login to the COLUMNS * EXCEPT list of the flow

  Incorrect

  That drops the column from the target, so last_login would no longer show its latest value.

• BChange the flow to STORED AS SCD TYPE 1

  Incorrect

  Type 1 keeps no history at all, which breaks the auditors' need for address and segment versions.

• CAdd TRACK HISTORY ON * EXCEPT (last_login) to the flow

  Correct

  Columns left out of history tracking are updated in place on the current row, so only address and segment changes create new versions.

• DAdd IGNORE NULL UPDATES to the flow

  Incorrect

  This does not limit which columns create history rows, so each sign-in still changes last_login and creates a version.

By default, SCD type 2 creates a version whenever any column changes. A TRACK HISTORY clause limits history to a set of columns; changes to the other columns overwrite the current version instead of adding a row.

• AContinuous pipeline mode instead of the default triggered mode

  Incorrect

  AUTO CDC works in triggered and continuous mode.

• BServerless, or the Pro or Advanced edition on classic compute

  Correct

  The CDC APIs need serverless or the Pro or Advanced editions.

• CChange data feed turned on for the target streaming table

  Incorrect

  The target is managed by the flow; this is not the requirement.

• DThe Advanced edition, because the Pro edition does not include CDC

  Incorrect

  Both the Pro and Advanced editions support the CDC APIs.

To use the AUTO CDC APIs, a pipeline must use serverless or the Lakeflow pipelines Pro or Advanced edition.

• AWHERE __START_AT IS NULL

  Incorrect

  Every version has a __START_AT value.

• BWHERE _change_type = 'insert'

  Incorrect

  _change_type is a change data feed column, not an SCD target column.

• CWHERE __END_AT = __START_AT

  Incorrect

  That does not identify the active version.

• DWHERE __END_AT IS NULL

  Correct

  Active versions have a null __END_AT; ended versions have a value.

SCD type 2 targets keep each version of a record with __START_AT and __END_AT columns. Rows with a null __END_AT are currently active.

• AAUTO CDC INTO with the snapshot table as the source

  Incorrect

  AUTO CDC expects a change feed with operations and sequencing, not whole snapshots.

• BA materialized view that selects from the latest snapshot

  Incorrect

  That shows only the current state, with no history.

• CAUTO CDC FROM SNAPSHOT over the nightly snapshots

  Correct

  This API compares in-order snapshots to find inserts, updates and deletes, and supports SCD type 1 and type 2.

• DChange data feed on the snapshot table

  Incorrect

  Overwriting the snapshot nightly produces no row-level history of customer changes.

Use AUTO CDC when the source has a change feed and AUTO CDC FROM SNAPSHOT when only snapshots are available. The snapshot API compares snapshots to determine changes and then applies them as SCD type 1 or type 2.

• AThe values from sequence 6; the late update at sequence 5 is dropped

  Correct

  Records are ordered by the SEQUENCE BY column, so an older change that arrives late does not overwrite a newer one.

• BThe values from sequence 5, because it was processed last by the flow

  Incorrect

  Arrival order does not decide the result; the sequence column does.

• CBoth versions, with __START_AT and __END_AT columns added

  Incorrect

  Only SCD type 2 keeps versions with those columns.

• DNo row, because out-of-order events fail the flow update

  Incorrect

  AUTO CDC handles out-of-order events instead of failing.

AUTO CDC uses the SEQUENCE BY column to order changes per key. In the documented example, an UPDATE at sequence 5 that arrived after one at sequence 6 was dropped from the SCD type 1 target.