Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Question
A data analyst is designing a dimension table in Databricks SQL that will be used in a star schema. The table contains a natural business key (e.g., product_code) and a surrogate key (e.g., product_sk). The analyst wants to ensure that the surrogate key is unique and automatically generated for each new row, while also enforcing that the natural key is unique. Which approach best achieves these requirements?
⚠ Common exam trap
The trap here is assuming that any function that generates numbers, such as MONOTONICALLY_INCREASING_ID(), guarantees uniqueness and persistence in a table, when in fact it does not.
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 product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
The requirement is for an automatically generated, unique surrogate key and a unique natural key. The IDENTITY column property in Delta Lake automatically generates unique, sequential values for new rows. Combining it with a PRIMARY KEY constraint on the surrogate key and a UNIQUE constraint on the natural key enforces both uniqueness rules. Other options either misuse functions, lack persistence, or do not enforce uniqueness correctly.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Define product_sk as BIGINT and set a default value using the UUID() function, then add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
Why it's wrong here
UUID() returns a STRING, not a BIGINT, so assigning it to a BIGINT column would fail or require casting, which could produce collisions. While UUIDs are unique, they are not sequential and are inefficient as surrogate keys. This approach does not meet the requirement for an automatically generated numeric surrogate key.
- ✗
Use a Delta table with a CHECK constraint that validates product_code IS NOT NULL, and generate product_sk using the ROW_NUMBER() window function in a view.
Why it's wrong here
A CHECK constraint only validates a condition; it does not enforce uniqueness. Generating the surrogate key in a view does not persist it in the table, and ROW_NUMBER() would recompute values on each query, leading to inconsistent keys. This fails to provide a stable, unique surrogate key.
- ✗
Define product_sk as STRING and use the MONOTONICALLY_INCREASING_ID() function during inserts to populate it, then add a PRIMARY KEY constraint on product_sk.
Why it's wrong here
MONOTONICALLY_INCREASING_ID() generates monotonically increasing numbers but does not guarantee uniqueness across concurrent transactions and is not persisted; it is also not a column property. Using a STRING type for a surrogate key is inefficient. This approach does not reliably enforce uniqueness and lacks automatic generation.
- ✓
Define product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
Why this is correct
Using GENERATED ALWAYS AS IDENTITY automatically generates unique surrogate keys. Adding a PRIMARY KEY on product_sk enforces uniqueness and non-nullability, while a UNIQUE constraint on product_code enforces the natural key's uniqueness. This combination meets both requirements and is supported in Delta Lake tables.
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.