Home / DE Professional practice test / Developing Code for Data Processing using Python and SQL / Question

Databricks Data Engineer Professional · free practice question

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…

Exam-style question on Developing Code for Data Processing using Python and SQL from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Developing Code for Data Processing using Python and SQL Hard

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?

  1. AAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' COLUMNS * EXCEPT (operation) SEQUENCE BY updated_at STORED AS SCD TYPE 1;
  2. BAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation IN ('DELETE', 'UPDATE') SEQUENCE BY updated_at COLUMNS name, email; — UPDATE events must also be declared as deletes followed by inserts to correctly reflect row-level changes in the target table.
  3. CMERGE INTO silver_customers USING bronze_cdc_events ON silver_customers.customer_id = bronze_cdc_events.customer_id WHEN MATCHED AND operation = 'DELETE' THEN DELETE WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *; — The AUTO CDC API is only available for batch sources; for Kafka streaming CDC, MERGE INTO is the required approach.
  4. DAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY updated_at COLUMNS * EXCEPT (operation);
  5. EAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' APPLY AS TRUNCATE WHEN operation = 'TRUNCATE' SEQUENCE BY updated_at STORED AS SCD TYPE 2 COLUMNS * EXCEPT (operation);
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.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →