Star Schemas and Materialized Views for Analytical Workloads

1.Facts, dimensions and the star schema

Analysts and BI tools query efficiently when the gold layer is organised by what each table is responsible for. Dimensional modeling splits the data two ways. A fact table records events: each row is one order, click or sale, described mostly by keys and numeric measures. A dimension table holds the descriptive context for those events, such as customers, products or dates, with one row per business entity.

The star schema puts one fact table at the centre and joins it out to its dimensions through their keys. Each table has a single, clear job, so the model is easy to query and easy to reason about. In the medallion architecture the star schema lives in the gold layer: bronze and silver handle ingestion and cleaning, and gold materialises the facts and dimensions that downstream consumers read.

Sources1

2.Materialized views for dimensions, streaming tables for facts

Match each dataset type to the way the table changes. Dimensions are built as materialized views that recompute efficiently from cleaned silver data whenever inputs change. If you need to keep a dimension's change history, use a streaming table with SCD Type 2 instead (AUTO CDC with STORED AS SCD TYPE 2). Facts are built as streaming tables that read the silver stream incrementally.

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

Keys. Use a natural key, an identifier that already exists in the source such as an order number, when it is stable, because it clusters and joins well. Use a surrogate key only when a source reuses or changes IDs. Even then, avoid a hash surrogate such as sha2(natural_key). Hash values scatter physically adjacent rows across files, which hurts liquid clustering and Z-order. Instead, derive an order-preserving surrogate deterministically from the natural key. Because the same entity always gets the same surrogate, the key survives a full refresh of the dimension. An IDENTITY column works only if the upstream table is append-only and never full-refreshed, since a rebuild can assign new IDs to existing entities and silently break fact joins.

Date dimension. Don't ingest dim_date from a source. Generate it as a small materialized view using sequence() and explode() over a date range. It is static reference data, cheap to compute, and makes date-based joins simpler throughout the model.

Sources1

3.How materialized views pre-compute and refresh

You query a materialized view just as you would a table. Because its results are already computed, those queries can run much faster than queries against a regular view. A materialized view is a declarative pipeline object made of a defining query, a flow that updates it, and the cached results. When you create one outside a Lakeflow pipeline, Databricks creates a pipeline to refresh it, which appears with type MV/ST under Jobs & Pipelines. Unity Catalog stores the view's metadata. Some backing data lives in the __databricks_internal catalog.

Refresh is often incremental. Databricks tracks upstream changes and processes only what it needs to. In the docs' sales_report example, new customers and transactions in three countries cause just three rows to update, not the whole report. Correctness comes before speed, though: a materialized view always returns the correct result, even when that means recomputing from scratch. That gives you the following limitations to design around:

- Some input changes force a full recomputation, which can be expensive. - Refresh latency is seconds to minutes, not milliseconds. - Not every computation can be done incrementally. - If a UDF's behaviour changes in a way Databricks can't detect, such as an upgraded library, you must run a full refresh yourself. - Materialized views can't be the source or target of CLONE.

With these limits in mind, the docs recommend that BI tools query gold materialized views directly. Because the pipeline refreshes them incrementally, you get near-real-time results without a separate reporting ETL step.

Sources21

Exam traps

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

1. 1.Because facts get aggregated, fact tables should be materialized views and dimensions should be streaming tables.Why is that wrong?

   It's the other way around. Facts are streaming tables fed incrementally from silver. Dimensions are materialized views, or SCD Type 2 streaming tables when you need history.

   Covered in Materialized views for dimensions, streaming tables for facts

2. 2.A sha2() hash of the natural key makes a good surrogate key because it is deterministic.Why is that wrong?

   Hash values are deliberately random, so physically adjacent rows end up scattered across files, which defeats liquid clustering and Z-order. Use an order-preserving surrogate derived deterministically from the natural key.

   Covered in Materialized views for dimensions, streaming tables for facts