Databricks-DE-Pro Data Modelling Practice Question
An engineer is building a Gold-layer star schema for a sales analytics workload. The business wants to analyze revenue by product, by store, and by promotion independently, and also drill down through a hierarchy of region to country to city. Which dimensional modeling structure best supports these requirements?
⚠ Common exam trap
The trap here is equating normalization with good modeling and choosing a snowflake or 3NF design, when the stated need for simple, consistent drill-down favors a star schema with conformed dimensions.
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 star schema with conformed dimensions that include hierarchical attributes for drill-down
Independent analysis by product, store, and promotion with hierarchical drill-down is the classic use case for a star schema built on conformed dimensions. Denormalized dimensions keep joins few, conformed dimensions keep definitions consistent across facts, and embedded hierarchy attributes like region, country, and city enable drill-down without additional tables.
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 fully normalized third-normal-form schema with separate tables for each attribute level
Why it's wrong here
A fully normalized model splits dimensions into many related tables, which increases the number of joins analysts must write and complicates the intuitive drill-down path. While it reduces redundancy, it undermines the simplicity and query performance expected of a Gold-layer star schema, and it does not naturally express the region-to-country-to-city hierarchy as a single navigable dimension.
- ✓
A star schema with conformed dimensions that include hierarchical attributes for drill-down
Why this is correct
A star schema places a central fact table joined to denormalized dimensions, and including hierarchical attributes such as region, country, and city within the geography dimension lets analysts drill down without extra joins. Conformed dimensions shared across facts like sales and promotions allow consistent analysis by product, store, and promotion independently while keeping queries simple.
- ✗
A snowflake schema that normalizes each hierarchy level into its own dimension table
Why it's wrong here
Snowflaking normalizes hierarchy levels into separate tables, which preserves storage but adds joins for every drill-down step and complicates the model for analysts. It can still support the required analysis, but it is a poorer fit than a star schema for a Gold layer where query simplicity and performance are prioritized, and it introduces more points of failure in join logic.
- ✗
A single wide denormalized table that embeds all dimensional attributes directly in the fact rows
Why it's wrong here
Flattening dimensions into the fact table duplicates descriptive attributes across many rows, bloating storage and making updates to a dimension attribute require rewriting large portions of the fact table. It also loses the conformed-dimension benefit that lets the same product or geography definition be reused across multiple fact tables, so independent analysis by product, store, and promotion becomes harder to govern.
About these practice questions
One of 267 original Databricks-DE-Pro practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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-Pro 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-Pro exam.