8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions

8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions

Sub-section 3.2 of the exam guide, Planning for using a data warehouse, is worth roughly a quarter of the Storing the data domain and asks four things of you: design the data model, decide the degree of data normalization, map business requirements, and define an architecture that supports the data access patterns. Notice what it does not ask — it does not ask which storage engine to pick (that is 3.1) or how partitioning works mechanically (that is covered separately). It asks how to shape tables so the warehouse answers the questions the business actually has.

---

Declare the Grain Before You Design Anything

Every defensible warehouse model starts with one sentence: "one row in this fact table represents ______." One row per order line. One row per sensor reading per minute. One row per claim adjudication event. Until the grain is fixed, nothing downstream can be evaluated — you cannot judge whether a measure is additive, you cannot size the table, and you cannot tell whether a join will fan out and silently double your revenue totals.

Measures then fall into three categories that determine which aggregations are legal:

The classic exam-adjacent mistake is storing a non-additive measure such as margin_percent in a fact table. Summing it produces nonsense; the fix is to store revenue and cost and compute the ratio at query time or in a view.

---

Three Modeling Shapes, and What BigQuery Says About Each

Google's documented guidance is specific and worth carrying verbatim into the exam: use nested and repeated fields to denormalize data storage and increase query performance, because denormalization "localizes the data to individual slots, so that execution can be done in parallel" and avoids the shuffle that a join requires. But the same guidance adds the crucial caveat that star schemas are typically already optimized for analytics, so attempting to denormalize a star further into nested structures "might not" produce a significant difference.

That pair of statements resolves most modeling questions:

• Coming from a normalized OLTP source with many joins? Denormalize — either into a star or with nested and repeated fields.
• Already on a star schema and being asked whether to flatten dimensions into the fact as arrays? The performance case is weak; do not churn the model for it.
• Data is genuinely hierarchical and always queried together (an order and its line items, a session and its events)? Nested and repeated fields are the idiomatic BigQuery answer, and they keep the parent-child relationship in a single storage block.

Deciding the Degree of Normalization

---

Primary and Foreign Keys in BigQuery Are Optimizer Hints

BigQuery supports PRIMARY KEY and FOREIGN KEY table constraints, but only as NOT ENFORCED declarations. This is a high-value exam fact with two halves:

CREATE TABLE mydataset.dim_customer (
  customer_id STRING PRIMARY KEY NOT ENFORCED,
  customer_name STRING,
  segment STRING
);

CREATE TABLE mydataset.fact_sales (
  sale_id STRING PRIMARY KEY NOT ENFORCED,
  customer_id STRING REFERENCES mydataset.dim_customer(customer_id) NOT ENFORCED,
  sale_amount NUMERIC
);

• The benefit: the optimizer uses the declarations to rewrite queries. The headline case is inner join elimination — if a query joins the fact to a dimension on a declared primary key but selects no columns from the dimension, BigQuery can drop the join entirely, because the constraint guarantees exactly one matching row.
• The obligation: BigQuery does not verify the constraint. You are responsible for keeping the data conformant, and queries over tables with violated constraints might return incorrect results. Declaring a primary key on a column that actually contains duplicates is worse than declaring nothing, because it authorizes a rewrite that silently drops rows.

The correct pairing is therefore: declare the constraints for the optimizer, and enforce them yourself with Dataform assertions or ASSERT checks on every load. Where you need genuine guaranteed uniqueness for a surrogate key, use an identity column.

---

Slowly Changing Dimensions

Dimension attributes change — a customer moves to a new region, a product is reclassified. How you absorb that change determines whether last year's report still reproduces last year's numbers.

Type 2 is the one that gets tested, and MERGE is how BigQuery implements it in a single atomic statement:

MERGE mydataset.dim_customer AS target
USING mydataset.staging_customer AS source
ON target.customer_id = source.customer_id
   AND target.is_current = TRUE
WHEN MATCHED AND target.segment != source.segment THEN
  UPDATE SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (customer_id, segment, valid_from, valid_to, is_current)
  VALUES (source.customer_id, source.segment, CURRENT_TIMESTAMP(), NULL, TRUE);

Two operational notes the exam likes. First, a Type 2 MERGE cannot both expire the old row and insert the replacement in one WHEN MATCHED branch, so production implementations either run two statements inside a multi-statement transaction or union a "change" row into the source so the insert arrives through WHEN NOT MATCHED. Second, a Type 2 dimension joined to a fact must be joined on the key and the validity window, not on the key alone — joining on the key alone against a Type 2 dimension fans the fact out by the number of historical versions, which is one of the fastest ways to inflate a revenue figure.

---

Architecting for the Access Patterns

The fourth bullet — defining architecture to support data access patterns — is where modeling meets physical layout:

• Partition the fact table on the column that filters every query, which is almost always the event or transaction date. require_partition_filter turns "please filter by date" into an enforced contract.
• Cluster on the highest-selectivity filter and join columns, ordering the clustering keys from coarsest to finest so block pruning compounds.
• Add aggregate tables or materialized views for the repeated rollups. A dashboard that asks for daily revenue by region a thousand times a day should not rescan the fact table a thousand times; a materialized view refreshes incrementally and can be rewritten into automatically.
• Keep dimensions small enough to broadcast. A dimension that fits in memory is broadcast-joined rather than shuffle-joined, which is why a slim, well-pruned dimension outperforms a wide one carrying rarely used attributes.
• Match retention to the question. Partition expiration on a fact table is a modeling decision as much as a cost one: if nobody asks questions older than 25 months, the model should not carry 10 years.

---

Exam Traps and Antipatterns Summary