How to build star schema in Databricks

Summary

• A star schema puts one fact table (events or measurements, mostly keys and numeric measures) in the center and joins it to dimension tables (customers, products, dates) through keys.
• Databricks documents the star schema as performing well and recommends against heavily normalized (3NF) models for new lakehouses, because standard queries need fewer joins.
• Build the star in the gold layer with Lakeflow Pipelines: dimensions as materialized views (or streaming tables with SCD Type 2 when you need history) and facts as streaming tables fed from silver.
• Primary and foreign keys on Databricks are informational and not enforced, so validate key integrity in your load and use NOT NULL, CHECK, or expectations for row-level rules.
• Query the gold tables from SQL and BI tools, define governed measures in metric views, and let Genie Agents answer natural-language questions.

How to build star schema in Databricks

Build the star schema in the gold layer of a medallion pipeline in Lakeflow Pipelines. Model dimensions as materialized views, model facts as streaming tables, declare informational primary and foreign keys, and query the result directly. Databricks recommends against a heavily normalized (3NF) design for a new lakehouse: models like the star schema need fewer joins in standard queries, and wider tables let the optimizer skip data using file-level statistics. The tables are Delta Lake tables, the default format on Databricks.

What is a star schema?

Table	Each row is	Holds
Fact table	One occurrence of an event, such as an order line, a click, or a sale	Foreign keys to dimensions and numeric measures such as quantity and amount
Dimension table	One business entity, such as a customer, a product, or a date	Descriptive attributes used to filter and group

Facts reference dimensions by key instead of copying descriptive attributes, which keeps fact tables narrow.

What are the steps to build a star schema in Databricks?

1. Choose the business process and its grain. Decide what one fact row means (one order line, one session) before you choose dimensions.
2. Clean the inputs first. In the medallion pattern, bronze is raw, silver is validated, and gold holds dimensional models and aggregations.
3. Build dimensions as materialized views, one row per business entity. Generate dim_date with sequence() and explode() instead of ingesting it.
4. Build facts as streaming tables fed incrementally from silver, so each new record is processed once.
5. Keep history only where it matters. For a dimension that needs history, use AUTO CDC with STORED AS SCD TYPE 2. Type 1 updates records in place and keeps no history.

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

CREATE OR REFRESH STREAMING TABLE fact_orders
COMMENT "One row per order line, keyed to dimensions"
AS SELECT order_id, customer_id, product_id, order_date, quantity, amount
FROM STREAM(orders_silver);

How do keys and constraints work?

Primary key and foreign key constraints are informational and not enforced. Declare them anyway: they document relationships, and with the RELY option on a verified primary key the optimizer can drop unnecessary DISTINCT operations and unneeded outer joins to the dimension on Photon-enabled compute. Relying on a constraint that is not satisfied can return incorrect results, so check uniqueness and referential integrity in your load. NOT NULL and CHECK constraints are enforced, and an expectation can enforce a row-level rule such as a non-null key.
Prefer a stable natural key from the source, such as an order number. Use a surrogate key only when a source reuses or changes IDs, and avoid hash surrogates like sha2(natural_key), which scatter adjacent rows across files. Derive an order-preserving surrogate deterministically from the natural key instead. An IDENTITY column works only when the upstream table is append-only and never fully refreshed, because a rebuild can reassign IDs and silently break joins.
By default, Databricks scopes transactions to a single table, so a fact load and its dimension load commit separately. Make downstream queries tolerant of a dimension that lags a fact.

How do I serve a star schema to BI tools and Genie Agents?

BI tools can query the gold tables directly. Unity Catalog privileges inherit downward, so granting SELECT on a catalog lets users read all tables in it, given the usage privileges.
Metric views define measures once and let users group by any field. A metric view whose source is the fact table joins dimension tables with LEFT OUTER JOIN semantics, which is the star schema pattern, and you can query it from SQL editors, notebooks, dashboards, Genie Agents, and alerts.
A Genie Agent is a natural-language chat interface where users ask questions and get back SQL, result tables, and visualizations. Data analysts curate each agent with Unity Catalog datasets, example SQL queries, SQL expressions for business semantics, and text instructions.

FAQs

Do I need a separate reporting database?

Not for the star schema itself. Databricks SQL and BI tools query the gold tables in place, and the pipeline keeps them incrementally refreshed, so the BI layer needs no separate ETL step.

How do I handle incremental fact loads?

Streaming tables process each new record once, so appends reach the fact table without recomputing history. For changed records, use AUTO CDC, which handles out-of-order change events. The docs warn that MERGE INTO can produce incorrect results when records arrive out of sequence.

Are foreign keys enforced?

No. They are informational, so validate referential integrity in your load logic.