Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Question
An analyst is designing a Delta table in Databricks SQL that will store customer transactions. The table must enforce that the 'transaction_amount' is always positive and that 'customer_id' is not null. The analyst wants to ensure that any future inserts or updates that violate these rules are rejected. Which approach should the analyst use?
⚠ Common exam trap
It's easy for candidates to confuse data validation constraints with data filtering or masking mechanisms that only affect query results.
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
✓
Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.
Delta Lake's CHECK and NOT NULL constraints are enforced at write time, ensuring that any data violating the rules is rejected. A CHECK constraint on 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id' will prevent invalid inserts or updates, maintaining data integrity directly in the table definition.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a MERGE statement with a condition that only updates rows where 'transaction_amount' > 0 and 'customer_id' IS NOT NULL.
Why it's wrong here
A MERGE statement can conditionally update rows, but it does not prevent inserts of invalid data. If the source data contains rows that violate the rules, the MERGE could still insert them if the conditions are not properly applied to inserts. Moreover, this approach requires all write paths to use the same MERGE logic, which is error-prone and not enforced at the table level.
- ✗
Add a column mask that returns NULL for 'transaction_amount' when it is not positive.
Why it's wrong here
Column masks are used for dynamic data masking to control access, not for data validation. They do not prevent invalid data from being stored; they only alter how data is presented to certain users. This would not reject writes that violate the positivity rule, and it would not enforce non-null on 'customer_id'.
- ✓
Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.
Why this is correct
Delta Lake supports CHECK constraints and NOT NULL constraints. CHECK constraints enforce a boolean expression on new data, and NOT NULL ensures a column cannot contain nulls. These constraints are enforced on all writes, including inserts, updates, and merges, and will reject violating rows. This directly meets the requirement to reject invalid data at write time.
- ✗
Create a view on top of the table that filters out rows where 'transaction_amount' is not positive or 'customer_id' is null.
Why it's wrong here
A view can filter data for reads, but it does not prevent invalid data from being written to the underlying table. The requirement is to reject invalid writes, not to hide them. Using a view would allow bad data to persist, which could cause issues for other consumers or downstream processes that access the table directly.
About these practice questions
This Databricks-DA-Assoc question is part of Courseiva's 291-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-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.