12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions

12.4 Data Warehouse Modeling: Requirements, Normalization, Star Schemas, and Slowly Changing Dimensions

Exam Focus: Blueprint topic 3.2, "Planning for using a data warehouse," is tested as design judgment, not syntax. You must be able to translate a stated business requirement into a data model, decide how far to normalize for a columnar engine, choose between a star schema and BigQuery's nested and repeated fields, and handle dimension attributes that change over time without corrupting historical reporting.

Partitioning and clustering (sections 12.2 and 12.3) make a table fast to scan. Modeling decides what the table is. A badly modeled warehouse cannot be rescued by tuning: if the grain is wrong, every downstream number is wrong, and if history is overwritten, no query can reconstruct it.

---

1. From Business Requirements to a Model

Modeling starts with requirements, not tables. Four questions produce the model:

1. What business process are we measuring? Each process — order placement, shipment, claim adjudication, meter reading — becomes one fact table.
2. What is the grain? State it as a sentence: "one row per order line per shipment." Declaring the grain first is what prevents the fan-out errors that silently double revenue when a one-to-many join is added later.
3. What do users filter and group by? Those become dimensions: date, customer, product, store, channel.
4. What do users measure? Those become facts: quantity, amount, cost, duration. Additive measures (revenue) behave differently from non-additive ones (a margin percentage, an account balance) — store the additive components and derive ratios at query time or in a rollup, never store a pre-divided ratio you cannot re-aggregate.

---

2. Deciding the Degree of Normalization

Normalization theory was built for row-stores where a repeated string wastes disk on every row and joins are cheap index lookups. BigQuery inverts both assumptions: columnar storage compresses repeated values extremely well, and large joins require a distributed shuffle. That shifts the correct answer toward denormalization — but not to zero normalization.

The decision rule the exam rewards:

• Dimensions that are small and change independently (a few million rows of customer or product attributes) stay as dimension tables in a star schema. BigQuery broadcasts a small dimension to every slot, so the join is nearly free, and a changed attribute is one small update rather than a petabyte rewrite.
• Relationships that are intrinsically one-to-many and always queried with the parent (order line items, event parameters, page hits in a session) become nested and repeated fields. There is no join and no fan-out risk.
• Attributes that are tiny, stable, and always needed (country code, channel) can simply be denormalized onto the fact table.
• Do not flatten a large, frequently-updated dimension into a petabyte fact table. A single corrected customer address would trigger a full-table rewrite.

-- Nested and repeated: one row per order, line items inside it. No join, no fan-out.
CREATE TABLE `sales.orders` (
  order_id      STRING   NOT NULL,
  order_ts      TIMESTAMP,
  customer_key  INT64,                         -- surrogate key into the customer dimension
  line_items    ARRAY<STRUCT<
                  sku            STRING,
                  quantity       INT64,
                  unit_price     NUMERIC,
                  discount_pct   NUMERIC>>
)
PARTITION BY DATE(order_ts)
CLUSTER BY customer_key;

-- Correct aggregation: UNNEST expands children only for the referenced columns
SELECT o.order_id, SUM(li.quantity * li.unit_price) AS order_total
FROM `sales.orders` o, UNNEST(o.line_items) AS li
GROUP BY o.order_id;

The fan-out trap, restated: if you model line items as a separate table and join it to an order-header table, SUM(order_header_amount) multiplies the header amount by the number of line items. With ARRAY<STRUCT<...>> the header columns exist once per order and cannot be double-counted. This is the same arithmetic failure that Looker's symmetric aggregates exist to work around (section 13.3) — nesting avoids it at the model layer instead of patching it at the BI layer.

---

3. Surrogate Keys and Dimension Design

Dimension tables should carry a surrogate key — a warehouse-generated integer or GENERATE_UUID() string — rather than reusing the source system's natural key.

• Natural keys get reused, reformatted, or collide after a merger or acquisition; surrogate keys are stable forever.
• A surrogate key is what makes Type 2 history (below) possible at all: the same customer can occupy several dimension rows, each with a different surrogate key, each valid for a different time span.
• Keep the natural/source key as an attribute so lineage back to the operational system remains queryable.
• BigQuery does not enforce primary or foreign keys. PRIMARY KEY (...) NOT ENFORCED and FOREIGN KEY (...) NOT ENFORCED constraints are declarative only: they inform the query optimizer (enabling join eliminations) but never reject a duplicate. Uniqueness must be enforced by the pipeline — a Dataform uniqueKey assertion, or QUALIFY ROW_NUMBER() OVER (PARTITION BY ...) = 1 during the load.

---

4. Slowly Changing Dimensions (SCD)

A customer moves from the Bronze to the Gold loyalty tier. What should last quarter's report show? The answer determines the SCD type, and exam scenarios always contain the deciding phrase.

Type 2 is the one worth implementing correctly:

-- Type 2 dimension: one row per customer per attribute-version
CREATE TABLE `dw.dim_customer` (
  customer_key   INT64   NOT NULL,   -- surrogate key, unique per version
  customer_id    STRING  NOT NULL,   -- natural key, repeats across versions
  loyalty_tier   STRING,
  region         STRING,
  valid_from     TIMESTAMP NOT NULL,
  valid_to       TIMESTAMP,          -- NULL while current
  is_current     BOOL    NOT NULL
)
CLUSTER BY customer_id, is_current;

-- Close the outgoing version and open a new one
MERGE `dw.dim_customer` AS d
USING `staging.customer_changes` AS s
ON  d.customer_id = s.customer_id AND d.is_current
WHEN MATCHED AND d.loyalty_tier != s.loyalty_tier THEN
  UPDATE SET valid_to = s.changed_at, is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT (customer_key, customer_id, loyalty_tier, region, valid_from, valid_to, is_current)
  VALUES (FARM_FINGERPRINT(CONCAT(s.customer_id, CAST(s.changed_at AS STRING))),
          s.customer_id, s.loyalty_tier, s.region, s.changed_at, NULL, TRUE);

Two rules follow, and both appear as distractors:

1. The fact table stores customer_key, not customer_id. Storing the natural key silently converts a Type 2 dimension back into Type 1, because every historical fact would join to whichever row happens to be current.
2. Current-state queries filter WHERE is_current; point-in-time queries join on the validity window. Omitting either predicate multiplies every fact row by the number of dimension versions — a fan-out disguised as a correct join.

-- Point-in-time: the tier as it stood when the order was placed
SELECT f.order_id, d.loyalty_tier, f.revenue
FROM `dw.fact_orders` f
JOIN `dw.dim_customer` d
  ON  f.customer_key = d.customer_key
  AND f.order_ts >= d.valid_from
  AND (d.valid_to IS NULL OR f.order_ts < d.valid_to);

A note on BigQuery's time travel: time travel (default 7 days, configurable 2-7) and fail-safe are disaster-recovery features for recovering a table you damaged. They are not a substitute for Type 2 history — they expire, they are table-wide rather than row-scoped, and they cannot answer "what tier was this customer in three years ago." Choosing time travel to satisfy a historical-reporting requirement is a reliable wrong answer.

---

5. Architecture to Support the Access Patterns

The final blueprint bullet — "defining architecture to support data access patterns" — is the layered warehouse:

Dataset placement carries the governance: separate datasets per layer, IAM granted per dataset, policy tags on sensitive columns, and — because a BigQuery join cannot cross regions — every dataset in the layer stack in the same location. A requirement for EU-resident data means the whole stack is EU, not just the curated layer.

Exam Trap: "Query the raw landing tables directly to avoid duplicating storage." BigQuery storage is inexpensive relative to repeated compute, and raw data has no conformed grain, no deduplication and no history. The curated layer exists precisely so that every consumer gets the same, correct numbers.