Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Question
An analyst is building a dimensional model in Databricks SQL and needs to create a table that stores slowly changing dimension type 2 (SCD2) history for customers. The table must track valid_from and valid_to timestamps and a current flag. Which table type in Databricks SQL is best suited for this purpose?
⚠ Common exam trap
The trap here is thinking that Change Data Feed automatically provides SCD2 history; it only records changes and does not maintain valid_from/valid_to or current flags.
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
✓
A Delta table with appropriate columns and MERGE operations to maintain history
Delta tables with SCD2 columns and MERGE operations are the standard way to implement slowly changing dimensions type 2 in Databricks SQL. Delta Lake's ACID transactions and MERGE support allow you to update existing rows (expire old records) and insert new versions atomically. Other options like change data feed or temporary views do not provide the necessary persistent history structure.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A Delta table with change data feed enabled
Why it's wrong here
Change Data Feed (CDF) records row-level changes for a Delta table, which can be useful for auditing or incremental processing. However, CDF does not inherently provide SCD2 history with valid_from, valid_to, and current flag columns. You would still need to design and maintain those columns manually. CDF is a complementary feature, not a replacement for an SCD2 table structure.
- ✗
A temporary view that joins the current customer table with a history table
Why it's wrong here
A temporary view is session-scoped and does not persist data. It cannot store SCD2 history because it does not materialize data; it only provides a query interface. While you could create a view over an SCD2 table, the view itself is not the storage mechanism for history. Thus, it is not suitable for this requirement.
- ✗
A managed table with partitioning by customer_id
Why it's wrong here
Partitioning by customer_id can improve query performance when filtering by customer, but it does not provide SCD2 history tracking. You still need to design the table schema with valid_from, valid_to, and current flag, and implement MERGE logic. Partitioning alone does not address the historical tracking requirement. Therefore, this option is incomplete.
- ✓
A Delta table with appropriate columns and MERGE operations to maintain history
Why this is correct
To implement SCD2, you need a table that includes columns like valid_from, valid_to, and is_current, and you use MERGE operations to insert new versions and expire old ones. Delta Lake supports ACID transactions and MERGE, making it ideal for maintaining SCD2 history. This approach gives full control over the history tracking logic.
About these practice questions
Courseiva writes every Databricks-DA-Assoc question from scratch — 291 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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-DA-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-DA-Assoc exam.