What you will be able to do
- Explain why Databricks recommends star-like models over heavily normalized 3NF for analytical workloads
- Build a star schema with a fact table and dimension tables, and pick keys that cluster and join well
- Recognise a snowflake schema and decide when its storage savings are worth the extra joins
- Place a data vault model in the silver layer and dimensional models in the gold layer
Key concept
Fewer joins, wider tables — On Databricks, the main cost of an analytical model is the joins a typical query has to perform. Models that keep descriptive attributes together in a few wide tables (star, then snowflake) usually beat heavily normalized designs, because the optimizer has fewer tables to combine and more columns it can use to skip files.
1.Why Databricks favours denormalized analytical models
A data model is a decision about how many tables a question has to touch, and on Databricks that decision drives query performance, compute cost and storage cost. Three industry-standard patterns cover most analytical work. The star schema puts a fact table at the centre of denormalized dimensions. The snowflake schema normalizes those dimensions further. The data vault is an integration model built to absorb change. Before comparing them, it helps to know what the engine rewards.
Joins are the bottleneck. The query optimizer tries to plan joins well, but it can struggle when a single query has to combine results from many tables. It can also fail to skip records in one table when the filter is on a column of another table, and the result is a full table scan. A 3NF model, where every attribute sits in its own normalized table, runs into both problems on almost every report.
Wide tables help in two ways. Star and snowflake schemas need fewer joins in standard queries and have fewer keys to keep in sync. Putting more fields in one table also lets the optimizer skip large amounts of data using file-level statistics, so a filter on region can prune files directly instead of waiting for a join.
None of this means you must rebuild an existing model. Databricks can work with any data model, and if you are migrating one, you should measure its performance before you re-architect it. The recommendation against 3NF applies to new lakehouses and to new datasets added to an existing one.
Checkpoint 1 of 9· Check yourself
A new lakehouse uses a 3NF model. The sales table stores only a region_id. The region name sits in a separate regions table. A dashboard filters sales on region name = 'EMEA'. What does Databricks warn can happen to the sales table?
Data skipping works on a table's own columns. If the filter column is in another table, the optimizer may not be able to skip any sales records and can scan the whole table. This is why wide star and snowflake tables, which hold the filter column in the same table, perform better.
“The optimizer can also fail to skip records in a table when filter parameters are on a field in another table”Source: docs.databricks.com
Sources1
2.Star schema: one fact table, denormalized dimensions
A star schema separates data into two kinds of table. Fact tables hold the events or measurements you care about, such as orders, clicks or sales. Each row is one occurrence, described mostly by keys and numeric measures. Dimension tables hold the descriptive context around those events, such as customers, products or dates, with one row per business entity. You place one fact table in the middle and join it to several dimensions through their keys. Each table has one clear job, which makes the model easy for analysts and BI tools to query.
In a Lakeflow pipeline, the star schema lives in the gold layer. Each dataset's type follows how the data changes. Dimensions are usually materialized views recomputed from cleaned silver data. When you need a dimension's change history, build it as a streaming table using AUTO CDC with STORED AS SCD TYPE 2 so that older attribute values are kept rather than overwritten.
CREATE OR REFRESH MATERIALIZED VIEW dim_customer
COMMENT "Customer dimension"
AS SELECT customer_id, customer_name, region, signup_date
FROM customers_silver;Facts are streaming tables fed incrementally from silver, so gold-layer aggregates stay close to real time. Keep facts narrow, with mostly keys and measures. A fact points to its dimensions by key instead of copying their descriptive attributes, and those attributes are joined in at query time.
CREATE OR REFRESH STREAMING TABLE fact_orders
COMMENT "One row per order line, keyed to dimensions"
AS SELECT
order_id,
customer_id, -- foreign key to dim_customer
product_id, -- foreign key to dim_product
order_date, -- foreign key to dim_date
quantity,
amount
FROM STREAM(orders_silver);Key choice decides whether those joins stay fast and correct. Use the source's natural key, such as an order number, when it is stable, because natural keys cluster and join well. Add a surrogate key only when a source reuses or changes IDs. Even then, avoid a hash like sha2(natural_key). Hash values are deliberately random, so physically adjacent rows end up scattered across files, which hurts liquid clustering and Z-order. Derive an order-preserving surrogate deterministically from the natural key instead. An IDENTITY column is safe only if the upstream table is append-only and never full-refreshed. Otherwise a rebuild can assign new IDs to the same entities and silently break every fact row that carries the old ones.
Primary and foreign keys on Databricks are informational and are not enforced, so a star schema does not guarantee referential integrity on its own. Declared keys still help performance. If customer has a primary key with the RELY option, the optimizer can drop the join entirely from a query that left-joins store_sales to customer but only sums ss_quantity. Each sales row matches at most one customer, so the join cannot change the result.
SELECT
SUM(ss_quantity)
FROM
store_sales ss
LEFT JOIN customer c ON ss.customer_sk = c.c_customer_sk;The rebuild reassigned IDENTITY values, so the same customers now have different keys. Fact rows still carry the old keys, so they join to the wrong customers or to none at all. A deterministic surrogate derived from the natural key would have survived the rebuild.
Checkpoint 2 of 9· Fill the gap
Gold-layer facts should be fed incrementally from silver so that aggregates stay close to real time. Which keyword completes this fact table definition?
CREATE OR REFRESH ? TABLE fact_orders
COMMENT "One row per order line, keyed to dimensions"
AS SELECT
order_id,
customer_id, -- foreign key to dim_customer
product_id, -- foreign key to dim_product
order_date, -- foreign key to dim_date
quantity,
amount
FROM STREAM(orders_silver);Facts are built as streaming tables fed incrementally from silver. Dimensions are usually materialized views. The FROM STREAM(orders_silver) clause also shows that this dataset reads its source incrementally.
Source: docs.databricks.comCheckpoint 3 of 9· Check yourself
Your CRM reuses customer IDs, so dim_customer needs a surrogate key. The dimension uses liquid clustering and is sometimes fully rebuilt. Which key does Databricks recommend?
Hash values are random, so related rows end up scattered across files and clustering suffers. An IDENTITY column can assign new IDs when the dimension is rebuilt. Declared primary keys are not enforced. A deterministic, order-preserving surrogate both clusters well and survives a rebuild.
“derive an order-preserving surrogate deterministically from the stable natural key, so the same business entity always maps to the same surrogate.”Source: docs.databricks.com
3.Snowflake schema: normalizing dimensions into subdimensions
A snowflake schema is a star schema whose dimensions have been split into related subdimensions. It still has a central fact table connected to dimensions by foreign keys. The difference is that a product dimension, for example, no longer carries its category and department on every row. Those attributes move into their own linked tables. Drawn as an entity-relationship diagram, the branching tables look like a snowflake, which is where the name comes from.
The TPC-H sample data shows the pattern in three tables. orders is the fact. customer is a dimension. nation holds country data and is reached only through customer, so it is a subdimension of customer. To get revenue by country, a query has to go from fact to dimension to subdimension, which takes two joins where a star schema with n_name stored on customer would need one.
| Table | Role | Joins to |
|---|---|---|
| orders | Fact table (order transactions) | customer on o_custkey = c_custkey |
| customer | Dimension table (customer details) | nation on c_nationkey = n_nationkey |
| nation | Subdimension of customer (country or region reference) | — |
Metric views model this chain directly. The orders table is the source and sets the grain. The nation join is nested inside the customer join, and fields reach it with chained dot notation such as customer.nation.n_name.
source: SELECT * FROM samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
'on': o_custkey = c_custkey
joins:
- name: nation
source: samples.tpch.nation
'on': c_nationkey = n_nationkeyChoosing between the two comes down to storage against reads. Normalizing the dimensions cuts redundancy and storage, because a department name is stored once instead of on every product row. You pay for that in joins, and the extra joins make queries more complex and usually slower for read-heavy analytics. A star schema accepts duplicated values in return for faster queries. If a dimension's repeated hierarchy has become expensive to store and maintain, snowflaking it is reasonable. If the gold tables feed dashboards that are refreshed all day, keep them as a star.
| Concern | Star schema | Snowflake schema |
|---|---|---|
| Dimension structure | Denormalized, one table per dimension | Dimensions split into related subdimensions |
| Data redundancy | More duplication of data | Less, through normalization |
| Storage | Larger | More storage-efficient |
| Joins per query | Fewer | More |
| Read query performance | Faster | Not as good as denormalized models |
Checkpoint 4 of 9· Fill the gap
This metric view needs to model nation as a subdimension that is reached only through customer, as in a snowflake schema. Which key, placed under the customer entry, completes the definition?
source: SELECT * FROM samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
'on': o_custkey = c_custkey
? :
- name: nation
source: samples.tpch.nation
'on': c_nationkey = n_nationkeyA snowflake chain is modeled by putting the nation join inside the customer join. That nesting makes the query go from the orders fact to the customer dimension and then to the nation subdimension, and fields reach it as customer.nation.n_name.
Source: docs.databricks.comCheckpoint 5 of 9· Exam question
A retailer's dim_product table repeats the full category, subcategory, and department hierarchy on every product row, and the table has grown large enough that storage cost and duplicate-value maintenance have become a real concern. The analytics team decides to split that hierarchy into separate dim_category, dim_subcategory, and dim_department tables linked by foreign keys. What modeling pattern does this change produce?
Correct answer: A — A snowflake schema, since the dimension is normalized into related subdimension tables that must be joined together to reconstruct the full hierarchy.
- A. Breaking a denormalized dimension into linked subdimension tables to remove repeated hierarchy values is the defining move of a snowflake schema, which trades storage redundancy for additional joins. This scenario describes exactly that normalization step.
- B. A star schema keeps dimensions denormalized in a single table per dimension, so splitting one dimension into three linked tables moves away from a star pattern and adds joins rather than removing them.
- C. Data vault modeling requires the specific hub, link, and satellite table roles built around business keys and load-date history, not merely splitting a dimension hierarchy into related lookup tables.
- D. A fact constellation describes multiple fact tables sharing conformed dimensions, but this scenario only describes restructuring one dimension table, with no second fact table introduced.
4.Data vault: change-tolerant integration in the silver layer
Star and snowflake schemas are built for reading. A data vault is built for integration. When you need to merge data from many disparate source systems into an enterprise data warehouse that matches your business processes, the silver layer of a lakehouse often uses a 3NF or Data Vault model. This is a write-performant model. It is optimized for change, so the warehouse can be modified quickly when business processes or source systems change.
That makes a data vault a good fit for a bank pulling customer and account data from a dozen systems that gain new product lines every quarter. The integration layer has to absorb new sources and attributes without rework, and it has to act as the single source of truth. A data vault does not replace the star schema. It feeds it: the silver warehouse is the trusted base that gold dimensional data marts are built from.
Several alternatives fail that bank in different ways. A star dimension that is fully overwritten on every load throws away earlier attribute values. When history matters in a dimension, the star-schema answer is an SCD Type 2 streaming table, not an overwrite. A flat reporting table that joins every source and is rebuilt every night is a read-optimized endpoint and has no integration layer behind it. A snowflake schema is a normalized form of a reporting model, not an integration model.
Any model that writes one source record into several tables also runs into Databricks transaction scoping. By default, a transaction covers a single table. Loading one record into two or more tables therefore means several independent transactions, and each can succeed or fail separately. Downstream queries must tolerate the mismatch until every table has caught up. The exception is tables with catalog commits enabled, which can take part in transactions that span multiple statements and tables. Declaring primary and foreign keys in the silver warehouse documents how its tables relate to each other in Unity Catalog, but those keys are informational only and enforce nothing.
Checkpoint 6 of 9· Match them up
Match each modeling choice to the goal it serves in a Databricks lakehouse.
Tap a term, then the definition that fits it.
A data vault is a write-performant integration model built for change, and it feeds gold data marts. Star schemas minimize joins for reads. Snowflake schemas normalize dimensions to save storage. An SCD Type 2 streaming table is how a star dimension keeps its history.
“build a data warehouse in alignment with your existing business processes. Often, this data follows a Third Normal Form (3NF) or Data Vault model.”Source: docs.databricks.com
Checkpoint 7 of 9· Exam question
A BI team is building a Gold-layer table set to power an AI/BI dashboard that sales managers will refresh throughout the day. They want the dashboard's queries to run with as few joins as possible, even if that means storing some dimension attributes redundantly. Which modeling approach best fits this goal?
Correct answer: A — A star schema with one central fact table and denormalized dimension tables joined directly to it, trading storage redundancy for fewer joins per query.
- A. A star schema denormalizes dimension tables so each one joins directly to the fact table with a single hop, which is exactly the low-join, read-optimized structure a frequently refreshed dashboard needs. The redundancy tradeoff is intentional and matches the team's stated priority.
- B. Splitting dimensions into subdimensions reduces redundancy but adds extra join hops for every query that needs the normalized attributes, which works against the team's goal of minimizing joins for a fast-refreshing dashboard.
- C. Data vault's hub/link/satellite structure is built for historical tracking and adapting to changing source systems, not for minimizing joins on read-heavy reporting queries, so it does not fit this dashboard's requirement.
- D. A transactional third-normal-form layout is designed to minimize write anomalies and redundancy, not to serve fast analytical reads, so it would require more joins than the team wants for dashboard queries.
Checkpoint 8 of 9· Exam question
A bank ingests customer and account data from a dozen source systems that change frequently as new product lines are added, and compliance requires a full, queryable audit trail of every historical value each attribute ever held. The data engineering team needs a Silver-layer design that absorbs source-system changes with minimal rework and preserves that history natively. Which modeling technique should they apply?
Correct answer: A — Data vault modeling, using hub tables for business keys, link tables for relationships, and satellite tables to capture time-stamped historical attributes.
- A. Data vault's hub, link, and satellite structure is purpose-built for this situation: hubs isolate stable business keys, links capture evolving relationships, and satellites record time-stamped attribute history, so new source systems and changing schemas can be absorbed without redesigning existing tables.
- B. A star schema optimized for dashboard reads is not designed for this requirement, and overwriting a denormalized dimension on every load discards the historical attribute values compliance needs to audit.
- C. Snowflake schemas normalize dimensions for storage efficiency in reporting, but they have no built-in mechanism for tracking every historical attribute change, so manual fact-table versioning would be needed and is not native to the pattern.
- D. A single flat table sourced from twelve rapidly changing systems would need constant redesign as source schemas evolve, and full reloads overwrite prior values instead of preserving the audit trail compliance requires.
5.Matching the model to the medallion layer
The medallion architecture refines data from raw bronze through validated silver to enriched gold. Its layers are taught in Medallion architecture (bronze, silver, gold). For modeling, the main point is that the layer decides the model. The data warehouse is modeled in silver and feeds specialized data marts in gold. Silver favours write-performant models that absorb change. Gold uses denormalized, read-optimized models with fewer joins, because business analysts, BI developers and executives query it directly.
| Layer | Role of the data | Typical model |
|---|---|---|
| Bronze | Landing space for raw data in its original format, converted to Delta tables | No modeling; raw ingestion |
| Silver | Integrate disparate sources into a data warehouse, the single source of truth | 3NF or Data Vault |
| Gold | Presentation layer of one or more data marts | Dimensional models such as a star schema |
Because each gold data mart presents one business perspective, an organization can run several of them side by side, for example one each for HR, finance and IT, all fed by the same silver warehouse. If a question is about who queries a table and how often, start from the layer, and the model usually follows from it.
Checkpoint 9 of 9· Match them up
In a Databricks pipeline, the layer decides the model. Pair each medallion layer with its role and the way it is typically modeled.
Tap a term, then the definition that fits it.
Bronze holds raw ingested data without modeling. Silver holds the write-performant warehouse (3NF or Data Vault) that absorbs change. Gold holds read-optimized dimensional data marts fed by that warehouse.
“The data warehouse is modeled in the silver layer and feeds specialized data marts in the gold layer.”Source: docs.databricks.com
Sources
(9)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The optimizer can also fail to skip records in a table when filter parameters are on a field in another table”
↩︎ Why Databricks favours denormalized analytical models“having more data fields in a single table allows the query optimizer to skip large amounts of data using file-level statistics”
↩︎ Why Databricks favours denormalized analytical models“Databricks can work well with any data model.”
↩︎ Why Databricks favours denormalized analytical models“Each of these transactions can succeed or fail independent of other transactions”
↩︎ Data vault: change-tolerant integration in the silver layer“as there are fewer joins present in standard queries and fewer keys to keep in sync”
↩︎ Key concept“Databricks recommends against using a heavily normalized model such as third normal form (3NF).”
↩︎ Exam trap 1“Databricks recommends against using a heavily normalized model such as third normal form (3NF).”
↩︎ Prediction - 2.
“place one fact table in the middle and join it out to several dimension tables through their keys”
↩︎ Star schema: one fact table, denormalized dimensions“In Lakeflow pipelines, the star schema fits naturally at the gold layer of the medallion architecture.”
↩︎ Star schema: one fact table, denormalized dimensions“Build dimension tables as materialized views (or as streaming tables with slowly changing dimension (SCD) Type 2 when you need history).”
↩︎ Star schema: one fact table, denormalized dimensions“Build fact tables as streaming tables fed incrementally from silver, so gold-layer aggregates stay close to real time.”
↩︎ Star schema: one fact table, denormalized dimensions“a rebuild can reassign different IDs to the same entity and silently break the fact-to-dimension joins”
↩︎ Star schema: one fact table, denormalized dimensions“A hash is deliberately random, which is bad for liquid clustering and Z-order performance”
↩︎ Exam trap 3“derive an order-preserving surrogate deterministically from the stable natural key, so the same business entity always maps to the same surrogate.”
↩︎ Checkpoint - 3.
“Using the primary key or unique constraint with RELY gives the query optimizer the information it needs to eliminate the join.”
↩︎ Star schema: one fact table, denormalized dimensions - 4.https://www.databricks.com/blog/what-is-snowflake-schemaSecondary source
“dimension tables are broken into related subdimensions, creating a more normalized, snowflake shaped model”
↩︎ Snowflake schema: normalizing dimensions into subdimensions“which makes query performance faster at the cost of duplicated data”
↩︎ Snowflake schema: normalizing dimensions into subdimensions“a snowflake schema saves space but usually requires more joins”
↩︎ Exam trap 4 - 5.
“The nested nation join demonstrates a snowflake schema pattern, joining through customer to reach geographic data, where nation is a subdimension of customer.”
↩︎ Snowflake schema: normalizing dimensions into subdimensions - 6.
“Often, this data follows a Third Normal Form (3NF) or Data Vault model.”
↩︎ Data vault: change-tolerant integration in the silver layer“It is optimized for change, so you can quickly modify the data warehouse”
↩︎ Data vault: change-tolerant integration in the silver layer“Your data warehouse should serve as the single source of truth for your data marts.”
↩︎ Data vault: change-tolerant integration in the silver layer“The data warehouse is modeled in the silver layer and feeds specialized data marts in the gold layer.”
↩︎ Matching the model to the medallion layer“build a data warehouse in alignment with your existing business processes. Often, this data follows a Third Normal Form (3NF) or Data Vault model.”
↩︎ Checkpoint - 7.https://www.databricks.com/blog/what-is-medallion-architectureSecondary source
“Data Vault-like, write-performant data models can be used in this layer.”
↩︎ Data vault: change-tolerant integration in the silver layer“The Gold layer is for reporting and uses more de-normalized and read-optimized data models with fewer joins.”
↩︎ Matching the model to the medallion layer - 8.
“Because the gold layer models a business domain, some customers create multiple gold layers to meet different business needs, such as HR, finance, and IT.”
↩︎ Matching the model to the medallion layer
Also cited
“Primary key, foreign key, and unique constraints are informational only and aren't enforced.”
↩︎ Exam trap 2