Star vs Snowflake Schema in the Gold Layer on Databricks

1.Fact tables, dimension tables and the star shape

Dimensional modeling organizes Gold-layer data into two kinds of tables so that analysts and BI tools can query it efficiently. Fact tables hold the events or measurements you care about, such as orders, clicks or sales. Each row is one occurrence of an event, described mostly by keys and numeric measures. Dimension tables hold the descriptive context around those events, such as customers, products or dates. Each row is one business entity.

A star schema is what you get when you put one fact table in the middle and join it out to several dimension tables through their keys. Each table has one clear job, so analysts find it easy to query and engineers find it easy to reason about. Databricks places it in the medallion architecture explicitly: Bronze and Silver handle ingestion and cleaning, and Gold materializes the fact and dimension tables that downstream consumers query directly.

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);

The fact table keeps no customer name or region. It stores customer_id as a foreign key to dim_customer, and descriptive detail is joined in at query time. A dimension table is usually built from cleaned Silver data, with one row per business entity. Both tables read from Silver, which shows the layer handoff from the previous page: Silver supplies validated records, and Gold reshapes them into a star.

CREATE OR REFRESH MATERIALIZED VIEW dim_customer
COMMENT "Customer dimension"
AS SELECT customer_id, customer_name, region, signup_date
FROM  ? ;

Sources1

2.Snowflake schema: a normalized star, and what it costs

A snowflake schema extends the star schema. Like a star, it has a central fact table connected to dimension tables through foreign keys. The difference is that dimension tables are broken into related subdimensions. A product dimension, for example, might be split into separate linked tables for product, category and department. Its entity-relationship diagram then branches out like a snowflake, which is where the name comes from. Snowflake schemas are common for BI and reporting in OLAP data warehouses, data marts and relational databases. That makes both shapes candidates for the data marts in a Gold layer.

The trade-off is redundancy against joins. Denormalized models like the star schema duplicate data, and that duplication is what makes queries faster. The same idea explains the medallion placement from the other direction: Gold favors de-normalized, read-optimized models with fewer joins, so the star is the default Gold shape. A snowflake is the normalized variant you accept when storage or the structure of a dimension calls for it.

Sources23

3.Why Databricks favors star and snowflake over heavily normalized models

The Databricks data modeling guidance gives a clear recommendation. If you are building a new lakehouse or adding datasets, avoid heavily normalized models such as third normal form (3NF). Star and snowflake schemas perform well on Databricks for three reasons. Standard queries need fewer joins, and there are fewer keys to keep in sync. Having more fields in a single table also lets the query optimizer skip large amounts of data using file-level statistics.

The joins point matters most. The optimizer can struggle when a single query has to join results from many tables. It can also fail to skip records when the filter is on a field in a different table, which may lead to a full table scan. Primary and foreign keys on Databricks are informational and not enforced, so the database won't police relationships across many normalized tables. None of this means other models are banned. Databricks can work well with any data model, and if you are migrating an existing model, evaluate its performance before rearchitecting it.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A snowflake schema is faster than a star schema because its normalized tables are smaller.Why is that wrong?

   Snowflaking saves space but adds joins. Read-heavy queries are usually slower than on the denormalized star.

   Covered in Snowflake schema: a normalized star, and what it costs

2. 2.Databricks recommends a fully normalized 3NF model for new lakehouse datasets because foreign keys keep the tables consistent.Why is that wrong?

   Databricks recommends against heavily normalized models for new datasets. Primary and foreign keys are informational and not enforced.

   Covered in Why Databricks favors star and snowflake over heavily normalized models