Overview
Data Modeling with Databricks SQL is worth 5% and has two objectives. The first asks you to apply industry-standard techniques to analytical workloads: the star schema, with a central fact table joined to denormalised dimension tables; the snowflake schema, which normalises dimensions into further sub-tables; and data vault, which separates business keys (hubs), relationships (links) and descriptive history (satellites) for flexible, auditable integration.
The second objective links these models to the Medallion Architecture. Bronze holds raw ingested data, silver holds cleaned, conformed and integrated data, and gold holds business-level, consumption-ready tables. Data vault structures commonly sit in silver, while star schemas and aggregated marts typically live in gold to serve dashboards and Genie. In Databricks SQL you support these models with primary and foreign key constraints, identity columns for surrogate keys, and clustering on common join and filter keys.
How it's tested
Questions are short and definition-driven. A table design or a modelling goal is described, and you identify the schema type or the medallion layer where it belongs. Look for clues such as denormalised dimensions, hubs and satellites, raw history or business-ready aggregates.
-
Name the model
A set of fact, dimension, hub, link or satellite tables is described and you identify star, snowflake or data vault.
-
Place it in a layer
A model or table purpose is described, and you assign it to bronze, silver or gold in the Medallion Architecture.
-
Choose for the workload
A need such as fast BI queries or auditable integration of many sources is given, and you choose the modelling approach.
A company integrates customer data from six source systems that change often and must keep full history for audit. A star schema in gold is ideal for reporting, but it is rigid for integrating changing sources. Data vault keeps business keys in hubs, relationships in links and history in satellites, so new sources add tables without redesign. Data vault in the silver layer is the stronger answer.
Objectives 2
Official objectives from the Databricks outline, in outline order.
8.1 Apply industry-standard data modeling techniques, such as star, snowflake, and data vault schemas, to analytical workloads.Star, snowflake and data vault models in practice.
- What it means
- A star schema has one fact table of measurable events, such as sales lines, surrounded by denormalised dimension tables like date, product and customer. A snowflake schema normalises those dimensions into related sub-tables, such as product to category. Data vault models data as hubs for business keys, links for relationships and satellites for descriptive attributes with history.
- What to understand
- Star schemas need fewer joins, so they are the usual choice for BI performance and simple SQL. Snowflake schemas reduce redundancy and ease dimension maintenance at the cost of extra joins. Data vault favours flexible, auditable integration and parallel loading, but it needs a downstream star schema for easy reporting. In Databricks SQL, declare primary and foreign keys as informational constraints, use identity columns for surrogate keys, and cluster large fact tables on frequent filter and join keys.
- How it shows up
- A schema description or a workload goal is given and you name or choose the modelling technique.
- Common confusion
- Calling a model with normalised dimensions a star schema.
8.2 Understand how industry-standard models align with the Medallion Architecture.Which layer each model serves.
- What it means
- The Medallion Architecture organises data into bronze (raw, as ingested), silver (validated, cleaned, conformed and integrated) and gold (business-level, curated for consumption). Modelling choices map to these layers according to their purpose.
- What to understand
- Bronze usually keeps source structure with little modelling. Silver is where integration happens, so normalised or data vault models fit there, often with history. Gold is where dimensional star schemas, aggregates, metric views and data marts serve dashboards, Genie and BI tools. Each layer builds on the previous one, which supports lineage and reprocessing. The exam expects you to place a model in the right layer rather than to design a full warehouse.
- How it shows up
- Expect to choose the layer for a star schema, a data vault or raw ingested data.
- Common confusion
- Placing data vault in gold or a reporting star schema in bronze.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Fact table
Records measurable events with foreign keys.
- Remember
- Holds numeric measures.
- Tested as
- Schema identification.
- Trap
- Storing descriptive attributes there.
-
Dimension table
Descriptive context such as product or date.
- Remember
- Joined to facts by keys.
- Tested as
- Schema identification.
- Trap
- Confusing with facts.
-
Star schema
Fact table plus denormalised dimensions.
- Remember
- Fewest joins, BI friendly.
- Tested as
- Most common gold model.
- Trap
- Mistaken for snowflake.
-
Snowflake schema
Star with normalised dimension hierarchies.
- Remember
- More joins, less redundancy.
- Tested as
- Schema identification.
- Trap
- Assuming it is faster for BI.
-
Data vault
Hubs, links and satellites.
- Remember
- Flexible, auditable integration.
- Tested as
- Silver layer modelling.
- Trap
- Using it directly for dashboards.
-
Hub
Table of unique business keys.
- Remember
- Core of data vault.
- Tested as
- Data vault questions.
- Trap
- Storing attributes in it.
-
Satellite
Descriptive attributes with history.
- Remember
- Tracks change over time.
- Tested as
- Data vault questions.
- Trap
- Confusing with links.
-
Medallion Architecture
Bronze, silver and gold layers.
- Remember
- Quality improves at each layer.
- Tested as
- Layer placement.
- Trap
- Treating layers as separate products.
-
Gold layer
Business-ready, curated data.
- Remember
- Star schemas and aggregates live here.
- Tested as
- Layer placement.
- Trap
- Putting raw data here.
-
Informational constraints
Primary and foreign keys not enforced.
- Remember
- Document relationships and aid optimisation.
- Tested as
- Modelling in Databricks SQL.
- Trap
- Expecting enforcement on insert.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Modelling techniques
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Star schema | Fact plus flat dimensions. | Simple, fast queries. | Redundant attributes worry candidates unnecessarily. |
| Snowflake schema | Fact plus normalised dimensions. | More joins, easier maintenance. | Called a star schema. |
| Data vault | Hubs, links, satellites. | Built for integration and history. | Served directly to BI users. |
Medallion layers
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Bronze | Raw ingested data. | Source fidelity. | Modelled as a star. |
| Silver | Cleaned and integrated data. | Normalised or data vault models. | Skipped entirely. |
| Gold | Consumption-ready data. | Star schemas, aggregates, metric views. | Holding unvalidated records. |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Normalised dimensions as star
- What candidates pick
- Star schema.
- Why it looks right
- There is a central fact table.
- Why it's wrong
- Once dimension tables are split into further related tables, the model is a snowflake schema, not a star.
Clue to watch for Product links to a separate category table.
-
Data vault for dashboards
- What candidates pick
- Point dashboards at hubs and satellites.
- Why it looks right
- Data vault holds the full history.
- Why it's wrong
- Data vault is designed for integration; querying it needs many joins, so a gold star schema should serve BI.
Clue to watch for Business users need fast, simple reporting.
-
Star schema in bronze
- What candidates pick
- Model raw ingested data as a star.
- Why it looks right
- Modelling early sounds efficient.
- Why it's wrong
- Bronze preserves source data as ingested; dimensional modelling belongs after cleaning and integration.
Clue to watch for Raw data straight from sources.
-
Enforced foreign keys
- What candidates pick
- Rely on foreign keys to reject orphan rows.
- Why it looks right
- Other databases enforce them.
- Why it's wrong
- Primary and foreign keys in Databricks are informational; data quality must be enforced with checks or pipeline expectations.
Clue to watch for Orphan fact rows still load.
-
Snowflake for speed
- What candidates pick
- Snowflake schema for the fastest BI queries.
- Why it looks right
- It sounds more organised.
- Why it's wrong
- Extra joins usually slow queries; star schemas are preferred for BI performance.
Clue to watch for Dashboard performance is the priority.
Answer framework
Use these steps to identify a model and place it in the medallion layers.
- Look for a central fact table; if dimensions are flat it is a star, if split further it is a snowflake.
- If the design uses hubs, links and satellites to track keys, relationships and history, it is data vault.
- Match the goal: fast, simple BI favours star; reduced redundancy favours snowflake; flexible auditable integration favours data vault. The exam often states one goal and asks for the model that serves it.
- Place raw source data in bronze, integrated or data vault structures in silver, and star schemas or aggregates in gold.
- Remember Databricks key constraints are informational, so data quality comes from checks and pipeline expectations, not keys. Primary and foreign keys still help BI tools and readers understand join paths.
Practice question
Want more like this? The Databricks DA Assoc practice test has questions for every domain.
Checklist
0 of 10
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Keep this section short and definition-focused. Draw three diagrams on paper: a star schema for retail sales with date, store, product and customer dimensions; the same model as a snowflake with product split into category and department; and a data vault version with customer and product hubs, a sales link and satellites holding changing attributes. Label which one you would place in silver and which in gold.
Then create a small star schema in Databricks SQL. Add a date dimension, a product dimension with an identity column as surrogate key, and a fact table. Declare primary and foreign key constraints and insert an orphan row to see that it loads anyway, which shows they are informational. Write one query that joins the fact to two dimensions and aggregates by month.
Read the Databricks documentation pages on the Medallion Architecture and on dimensional modelling in the lakehouse. Finally, practise five quick identification questions in which you name the model and the layer. Thirty minutes of review before the exam is enough to keep these definitions sharp.
FAQ
How much is Data Modeling with Databricks SQL worth on the Databricks Data Analyst exam?
Data Modeling with Databricks SQL is 5% of the exam, about two of 45 scored questions, tied with Importing Data as the smallest section.
Which model is best for dashboards?
Usually a star schema in the gold layer, because flat dimensions mean fewer joins and simpler SQL for BI tools, dashboards and Genie.
Where does data vault belong in the Medallion Architecture?
Typically in the silver layer, where data from many sources is integrated with history before gold models are built.
Does Databricks enforce foreign keys?
No. Primary and foreign key constraints are informational. They document relationships and can help the optimiser, but they do not reject rows.
Do I need to design a full warehouse for the exam?
No. Expect identification and placement questions rather than full design tasks.
What is a snowflake schema?
A dimensional model whose dimensions are normalised into further related tables, such as product linked to category and department.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 8 knowledge
Confirm you can identify star, snowflake and data vault models and place them in the right medallion layer with quick practice questions.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Databricks DA Assoc AI tutor Get any concept explained your way