A data engineering team is building a CDC pipeline using Lakeflow Spark Declarative Pipelines. The source is a Kafka topic that emits change events with the following schema: operation STRING (values: INSERT, UPDATE, DELETE), customer_id LONG (the business key), name STRING, email STRING, and updated_at TIMESTAMP (the sequencing column). The team wants to maintain a current-state Silver table silver_customers that reflects the latest version of each customer, including deletes. Which AUTO CDC INTO statement (the current recommended syntax, formerly APPLY CHANGES INTO) correctly implements this CDC pattern?
Answer & explanation
Correct answer: D
WHY D: This is the correct minimal AUTO CDC INTO syntax for a SCD Type 1 CDC target (default behavior, meaning the latest value overwrites the previous). FROM STREAM(bronze_cdc_events) reads from the upstream streaming table. KEYS (customer_id) specifies the business key for matching. APPLY AS DELETE WHEN operation = 'DELETE' instructs the engine to delete rows when the operation column is 'DELETE'. SEQUENCE BY updated_at uses the timestamp to order out-of-order events correctly. COLUMNS * EXCEPT (operation) writes all columns except the CDC metadata column. Note: AUTO CDC INTO is the current recommended syntax (replacing the legacy APPLY CHANGES INTO alias); both have identical clause structure. WHY NOT E: APPLY AS TRUNCATE is not a valid AUTO CDC clause. SCD TYPE 2 is a valid option but is not required here (and changes the semantics to historical tracking rather than current-state), and was not specified in the requirements. WHY NOT A: The clause order is invalid — in the documented AUTO CDC grammar, SEQUENCE BY must appear before the optional COLUMNS clause (KEYS → APPLY AS DELETE/TRUNCATE WHEN → SEQUENCE BY → COLUMNS → STORED AS). Option A places SEQUENCE BY after COLUMNS, which does not parse. (STORED AS SCD TYPE 1 by itself is legal but redundant, since SCD Type 1 is the default.) The minimal correct statement is option D. WHY NOT C: AUTO CDC INTO fully supports streaming sources from Kafka via upstream Streaming Tables. MERGE INTO is a batch SQL operation, not a streaming CDC mechanism. WHY NOT B: UPDATE events should NOT be declared as deletes. AUTO CDC INTO handles UPDATE events by matching on the key and updating the target row automatically — declaring them as deletes would incorrectly remove rows that should be updated.