BQBrains | Dimensional Modeling in BigQuery: Star Schemas, Denormalization, and Type 2 History with MERGE

Most BigQuery estates grow by accident. A migration lands a Kimball star schema built for a row-store, an analyst adds a 300-column "one big table" next to it, a dbt project bolts on a third shape, and two years later nobody can say which customer is the real one. This tutorial is about choosing a shape on purpose: when a star schema still earns its keep in BigQuery, when denormalization wins, how to build surrogate keys and Type 2 history with MERGE, and how to declare the constraints that let the optimizer — and your AI tooling — understand the model.

Why the old rules don't transfer

Dimensional modeling was invented for systems where joins were expensive and storage was not free. BigQuery inverts both assumptions in ways that matter:

Columnar storage means unused columns are free to query. A 300-column table costs nothing extra when a query touches four of them. Width is cheap; bytes scanned are what you pay for.
Joins are not free, but they are cheap when one side is small. A broadcast join against a dimension of a few hundred thousand rows barely registers. A shuffle join between two multi-billion-row tables is where your slots go to die.
Nested and repeated fields remove whole join classes. An ARRAY<STRUCT<...>> of order lines inside an order row is a pre-joined one-to-many that costs one scan.
Partitioning and clustering, not indexes, decide performance. The model that prunes well beats the model that normalizes well.

So the question is not "star or flat" in the abstract. It is: for each join in your model, is the small side small enough to broadcast, and does the resulting table still prune?

A decision rule you can actually apply

Use a star schema when:

Dimensions change independently of facts and you need conformed definitions reused across many facts (customer, product, store).
Dimensions are small relative to the fact table — roughly, under a few hundred MB, so BigQuery can broadcast them.
Multiple teams consume the same dimensions, and a single place to fix an attribute is worth a join.

Denormalize into a wide fact when:

The dashboard or extract is hot and latency-sensitive, and the join is repeated thousands of times a day.
The attributes you are folding in are effectively immutable at event time (the price as charged, the address as shipped to).
You are feeding BI Engine or a materialized view, both of which prefer simple scans.

Nest instead of joining when the relationship is strictly one-to-many and children are only ever queried with their parent: order lines, page hits in a session, line-level tax rows.

The pragmatic answer in most warehouses is layered: conformed star schema in the curated layer, denormalized or nested marts generated from it for consumption. You model once and materialize many times — which is exactly what a tool like Dataform or dbt is for.

Building the dimension: surrogate keys

Natural keys from source systems get reused, recycled, and reformatted. Generate a stable surrogate key by hashing the natural key, so the same input always yields the same key and you never need a sequence generator:

CREATE OR REPLACE TABLE curated.dim_customer
PARTITION BY DATE(valid_from)
CLUSTER BY customer_sk AS
SELECT
  TO_HEX(SHA256(CONCAT(source_system, '|', CAST(source_customer_id AS STRING)))) AS customer_sk,
  source_system,
  source_customer_id,
  customer_name,
  country,
  segment,
  CURRENT_TIMESTAMP() AS valid_from,
  TIMESTAMP '9999-12-31 00:00:00 UTC' AS valid_to,
  TRUE AS is_current
FROM staging.customers;

Two habits worth keeping:

Hash the source system plus the natural key, never the natural key alone. The day a second CRM arrives you will be glad.
Hash the attributes you track for change into a separate row_hash, so change detection is one string comparison instead of a twelve-column IS DISTINCT FROM chain.

TO_HEX(SHA256(TO_JSON_STRING(STRUCT(customer_name, country, segment)))) AS row_hash

TO_JSON_STRING over a STRUCT is the trick: it is null-safe, order-stable, and survives adding a column at the end of the struct without rewriting the expression.

Type 2 history with a single MERGE

Slowly changing dimension Type 2 — close the old row, insert a new one — is where most hand-rolled pipelines break, because MERGE can only act on a matched row once. The standard pattern uses a staged set that contains each changed record twice: once to close the current row, once to open the new one.

MERGE curated.dim_customer AS tgt
USING (
  -- row 1: matches the current record, closes it
  SELECT
    s.customer_sk AS merge_key,
    s.* EXCEPT (merge_key_dummy)
  FROM staging.dim_customer_incoming s
  JOIN curated.dim_customer d
    ON d.customer_sk = s.customer_sk
   AND d.is_current
   AND d.row_hash != s.row_hash

  UNION ALL

  -- row 2: NULL merge key never matches, so it inserts
  SELECT CAST(NULL AS STRING) AS merge_key, s.* EXCEPT (merge_key_dummy)
  FROM staging.dim_customer_incoming s
  LEFT JOIN curated.dim_customer d
    ON d.customer_sk = s.customer_sk AND d.is_current
  WHERE d.customer_sk IS NULL        -- brand new customer
     OR d.row_hash != s.row_hash     -- changed customer
) AS src
ON tgt.customer_sk = src.merge_key AND tgt.is_current

WHEN MATCHED THEN UPDATE SET
  valid_to   = src.effective_from,
  is_current = FALSE

WHEN NOT MATCHED THEN INSERT (
  customer_sk, source_system, source_customer_id, customer_name,
  country, segment, row_hash, valid_from, valid_to, is_current
) VALUES (
  src.customer_sk, src.source_system, src.source_customer_id, src.customer_name,
  src.country, src.segment, src.row_hash, src.effective_from,
  TIMESTAMP '9999-12-31 00:00:00 UTC', TRUE
);

Points that save debugging time later:

Close with the new row's effective_from, not CURRENT_TIMESTAMP(). Reruns must be idempotent, and wall-clock timestamps are not.
Use an open-ended sentinel (9999-12-31) rather than NULL for valid_to. Range predicates stay simple and the optimizer gets a real value to work with.
Keep is_current even though it is derivable. Every downstream query filters on it, and one boolean beats a BETWEEN on two timestamps.
Cluster the dimension by the surrogate key, so point lookups during the merge prune hard.

Joining a fact to Type 2 history is then an "as of" join:

SELECT f.order_id, f.order_ts, d.segment
FROM curated.fct_orders f
JOIN curated.dim_customer d
  ON d.customer_sk = f.customer_sk
 AND f.order_ts >= d.valid_from
 AND f.order_ts <  d.valid_to;

If that inequality join turns out to be the expensive part of your nightly run, stop joining at query time: resolve the dimension key once, when the fact is loaded, and store the resolved surrogate key on the fact row. That is Type 2 done the warehouse way.

Telling BigQuery about your keys

BigQuery supports unenforced primary key and foreign key constraints. They do not validate anything — you remain responsible for uniqueness — but the optimizer uses them to eliminate redundant joins and reorder plans, and metadata tooling uses them to infer relationships:

ALTER TABLE curated.dim_customer
  ADD PRIMARY KEY (customer_sk) NOT ENFORCED;

ALTER TABLE curated.fct_orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_sk) REFERENCES curated.dim_customer(customer_sk) NOT ENFORCED;

Declare them. They are free, and they are the cheapest possible documentation of your model — readable by the query planner, by catalog tools, and by the LLM-backed assistants that increasingly write SQL against your warehouse. A model with declared keys and populated column descriptions gets dramatically better machine-generated SQL than one without; the constraints tell the tool which join is legitimate.

Add the uniqueness test yourself, since nothing enforces it:

SELECT customer_sk, COUNT(*) AS n
FROM curated.dim_customer
WHERE is_current
GROUP BY customer_sk
HAVING n > 1;

Any row returned is a broken merge. Run it as an assertion in Dataform or a dbt test on every build.

Physical design for the fact table

Modeling decisions mean little if the fact table scans badly. For a typical event-grained fact:

CREATE TABLE curated.fct_orders (
  order_id STRING,
  order_ts TIMESTAMP,
  customer_sk STRING,
  store_sk STRING,
  line_items ARRAY<STRUCT<sku STRING, qty INT64, unit_price NUMERIC>>,
  order_total NUMERIC
)
PARTITION BY DATE(order_ts)
CLUSTER BY store_sk, customer_sk
OPTIONS (require_partition_filter = TRUE);

Partition on the business date the users actually filter by, not the ingestion date.
Cluster by the one or two dimension keys that appear most often as equality filters, highest-cardinality-first within reason.
require_partition_filter = TRUE on large facts. It is the single most effective guard against a new analyst scanning five years of history by accident.
Nest the line items if nobody queries them standalone; split them into their own fact if they are analyzed independently.

Degenerate, junk, and date dimensions

Degenerate dimensions (order number, invoice number) stay on the fact as plain columns. Do not build a dimension table for an identifier with no attributes.
Junk dimensions — the handful of low-cardinality flags — are often better as a STRUCT of booleans on the fact in BigQuery than as a separate table. Columnar storage makes the width free, and you save a join.
Date dimensions still earn their place when the business has fiscal calendars, holiday flags, or retail 4-5-4 weeks that DATE_TRUNC cannot express. If all you need is year/month/weekday, use date functions and skip the table.

A build order that works

Stage raw source data, append-only, with ingestion timestamps.
Build conformed dimensions with hashed surrogate keys and Type 2 history where the business genuinely asks "what did it look like then?" — and Type 1 overwrite where it does not.
Build facts at the lowest grain you can afford, resolving dimension keys at load time.
Declare unenforced primary and foreign keys, and write descriptions on every table and column.
Materialize denormalized or nested marts for the hot consumption paths, generated from the curated layer rather than hand-maintained.
Test: uniqueness on every dimension key, referential coverage on every fact key, row counts against source.

Steps 2 and 3 are where modeling discipline lives. Steps 5 and 6 are what keep it from rotting.

Where teams go wrong

Type 2 on everything. History is a cost — in merge complexity, in join complexity, in storage. Apply it to the attributes the business will actually ask about historically, and overwrite the rest.
Surrogate keys as INT64 sequences. They force ordering and serialization in a system designed for parallelism. Hash instead.
Wide tables with no partition filter. Denormalization without physical design is just a bigger scan.
Rebuilding dimensions in full every night because the merge was too fiddly. It works at ten million rows and collapses at one billion — and it destroys the history you built the Type 2 table for.

Getting the layer boundaries right is the difference between a warehouse that absorbs new sources easily and one that needs rebuilding every eighteen months. If you want a second pair of eyes on your model before it hardens, that is the core of our data architecture consulting and data engineering work — get in touch with your current schema and the queries that hurt.