Star and Snowflake Schemas for Analytical Workloads on Databricks

1.Why Databricks favours star-like models over 3NF

The data model you pick affects query performance, compute costs and storage costs. Databricks says it can work well with any data model. If you are migrating an existing model, it advises you to check its performance before you redesign it. For new lakehouses, though, the guidance is clear: avoid heavily normalized designs such as 3NF.

The reason is joins. The Databricks optimizer tries to plan joins well, but it can struggle when one query has to join results from many tables. There is a second, less obvious cost. When a filter is on a column in a *different* table, the optimizer may fail to skip records and scan the whole table instead. A heavily normalized model spreads attributes across many tables, so it causes both problems more often.

Star and snowflake schemas run well on Databricks because standard queries hit fewer joins and there are fewer keys to keep in sync. Wider tables also help with data skipping. When more fields sit in one table, the optimizer can use file-level statistics to skip large amounts of data.

Sources1

2.The star schema: one fact table, many dimensions

Dimensional modeling uses 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 of the 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.

Put one fact table in the middle and join it out to several dimensions through their keys, and you have a star schema. Analysts and BI tools find it easy to query, and engineers find it easy to reason about, because each table has one clear job.

Databricks advises keeping facts narrow: mostly keys and numeric measures. Descriptive detail comes in through joins at query time. In the fact table below, each _id or date column is a foreign key to a dimension, and quantity and amount are the measures.

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

Sources2

3.The snowflake schema: dimensions split into subdimensions

A snowflake schema extends a star schema. It still has a central fact table connected to dimensions through foreign keys. The difference is that dimension tables are split into related subdimensions, which makes the model more normalized. For example, product, category and department attributes can each go into their own linked table. This reduces redundancy and storage. The name comes from the shape of its entity-relationship diagram.

The Databricks metric-view tutorial shows the pattern on the TPC-H sample data. orders is the fact table and customer is a dimension. nation, a country or region reference, is not joined to orders directly. You reach it *through* customer on c_nationkey = n_nationkey. Nesting the nation join inside the customer join is what makes nation a subdimension of customer.

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_nationkey

Sources34

4.Choosing between star and snowflake

Both schemas perform well on Databricks next to 3NF, because both keep joins and key synchronisation manageable. Between the two, the trade-off is redundancy against joins. A star schema is denormalized: it repeats descriptive data across dimension rows, and in return queries run faster. A snowflake schema stores data more efficiently but adds joins to read-heavy queries.

Sources13

Exam traps

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

1. 1.Because a snowflake schema is more normalized, it is the faster choice for read-heavy BI analytics.Why is that wrong?

   Normalization saves storage, but it usually adds joins, which makes read-heavy queries more complex and slower. The denormalized star schema is the faster option.

   Covered in Choosing between star and snowflake

2. 2.A traditional 3NF model is the recommended starting point for a new Databricks lakehouse.Why is that wrong?

   For new lakehouses, Databricks recommends against heavily normalized models such as 3NF. Star and snowflake schemas perform better because queries need fewer joins and there are fewer keys to keep in sync.

   Covered in Why Databricks favours star-like models over 3NF