Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer needs to create a Silver Delta table that contains only distinct, non-null `customer_id` values from a Bronze table, and the result must be refreshed idempotently each night. Which statement best satisfies the requirement?
⚠ Common exam trap
The trap here is choosing INSERT INTO or MERGE for a full-refresh snapshot, when those patterns accumulate rows and break the idempotent nightly rebuild.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
CREATE OR REPLACE TABLE silver AS SELECT DISTINCT customer_id FROM bronze WHERE customer_id IS NOT NULL
CREATE OR REPLACE TABLE with a DISTINCT and null-filtered SELECT atomically rebuilds the Silver table each night, guaranteeing the same result on every run. This satisfies both the distinct non-null requirement and idempotent refresh. Appending via INSERT INTO, incremental MERGE, or relying on a pre-existing target for INSERT OVERWRITE does not meet all the stated conditions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
CREATE OR REPLACE TABLE silver AS SELECT DISTINCT customer_id FROM bronze WHERE customer_id IS NOT NULL
Why this is correct
CREATE OR REPLACE TABLE atomically replaces the table contents on each run, so the result is always the current distinct set of non-null customer IDs. Re-running the job produces the same final state, satisfying idempotency. It also keeps the output as a Delta table with transactional guarantees, which is appropriate for a Silver layer.
- ✗
INSERT OVERWRITE silver SELECT DISTINCT customer_id FROM bronze WHERE customer_id IS NOT NULL
Why it's wrong here
INSERT OVERWRITE replaces table contents but requires the target table to already exist with a matching schema. In many Databricks workflows it also does not register a new Delta table with full metadata if the table is unmanaged or missing. The scenario asks to create the table, so a CTAS is more appropriate and self-contained.
- ✗
MERGE INTO silver AS t USING bronze AS s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN INSERT (customer_id) VALUES (s.customer_id)
Why it's wrong here
The MERGE inserts only new customer IDs and never removes IDs that disappeared from Bronze, so the Silver table drifts from the source. It also does not filter nulls unless added to the ON or insert condition. While idempotent for inserts, it does not produce the required distinct snapshot each night.
- ✗
INSERT INTO silver SELECT DISTINCT customer_id FROM bronze WHERE customer_id IS NOT NULL
Why it's wrong here
Using INSERT INTO appends rows on every run, so duplicate customer IDs accumulate nightly. The table grows without bound and no longer contains distinct values. Idempotency fails because re-running the job changes the result set. This option addresses the null filter but not the distinctness or refresh semantics required.
About these practice questions
This Databricks-DE-Assoc question is part of Courseiva's 276-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-DE-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-DE-Assoc exam.