DP-900 Describe core data concepts Practice Question
Exhibit
Refer to the exhibit.
{
"name": "SalesFact",
"columns": [
{"name": "OrderDate", "dataType": "datetime"},
{"name": "SalesAmount", "dataType": "decimal"},
{"name": "ProductKey", "dataType": "int"},
{"name": "CustomerKey", "dataType": "int"}
]
}Refer to the exhibit. You are designing a fact table for a data warehouse. The table will store sales transactions with daily granularity. Which column would be most appropriate as the distribution column in a hash-distributed table in Azure Synapse Analytics?
⚠ Common exam trap
Microsoft often tests the misconception that any column with high cardinality is suitable for hash distribution, but the trap here is that the column must also be frequently used in joins and evenly distribute data, not just have many unique values.
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
✓
CustomerKey
CustomerKey (B) is the most appropriate distribution column because it has high cardinality and is frequently used in joins with dimension tables, ensuring data is evenly distributed across distributions in Azure Synapse Analytics. A hash-distributed table requires a column with many unique values to avoid data skew, and CustomerKey is a natural key for sales transactions that meets this requirement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SalesAmount
Why it's wrong here
SalesAmount is a numeric measure stored in the fact table, not a dimension key or distribution column. Hash distribution requires a column with high cardinality that is consistently used in joins, such as a surrogate key. Distributing by a monetary value would scatter transactions across distributions arbitrarily, cause uneven distribution due to few high-value orders, and provide no query collocation benefit.
- ✓
CustomerKey
Why this is correct
CustomerKey has high cardinality and is frequently used in joins, making it a good distribution column. Each customer has many sales transactions, so hashing on CustomerKey spreads rows evenly across distributions and reduces data skew. In Azure Synapse dedicated SQL pool, this also enables collocated joins with the customer dimension, minimizing data movement during queries.
- ✗
ProductKey
Why it's wrong here
ProductKey distributes data by product, but daily sales transactions require distribution by a column with high cardinality and even access patterns, such as OrderDateKey, to avoid data skew and parallelise loads across distributions. It is tempting because ProductKey is a natural dimension key for joining to a product dimension table, and would be correct in a star-schema fact table where queries frequently filter or aggregate by product rather than by date.
- ✗
OrderDate
Why it's wrong here
OrderDate has low cardinality (e.g., a distinct value for each day) and can cause data skew because sales volume is not uniform across dates—peak days create distribution hotspots. Distributing a fact table by OrderDate also limits parallelism when loading or querying a narrow date range, since only a few distributions hold all relevant data. For a large fact table, a column like CustomerKey provides far more even distribution and better join performance.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data warehouse
A data warehouse is a central repository that stores large amounts of structured data from multiple sources, optimized for querying and analysis rather than day-to-day transactions.
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft 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 DP-900 exam.