Designing a BigQuery Data Warehouse

What it is

Planning a warehouse means choosing a data model that answers the business questions, deciding how far to normalize, and shaping tables so the common queries read as little data as possible. In BigQuery, storage is cheap and scans cost money or slots, so the design favours fewer joins and pruned scans.

How it works

Mapping business requirements

Start with the questions, not the source tables.

Designing the data model

CREATE TABLE sales.orders (
  order_id STRING,
  order_ts TIMESTAMP,
  customer_id STRING,
  items ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>
)
PARTITION BY DATE(order_ts)
CLUSTER BY customer_id
OPTIONS (require_partition_filter = TRUE);

-- revenue per SKU for one week, without a join
SELECT i.sku, SUM(i.qty * i.price) AS revenue
FROM sales.orders, UNNEST(items) AS i
WHERE DATE(order_ts) BETWEEN '2026-10-01' AND '2026-10-07'
GROUP BY i.sku;

Deciding the degree of normalization

A common compromise is a star schema with nested child data in the fact. Small dimension tables join cheaply, and nesting removes the most expensive one-to-many join.

Defining architecture to support access patterns

Prefer one partitioned table over many date-sharded tables such as events_20261016. Partitioned tables have one schema, less metadata overhead and simpler queries.

A worked example

A subscription business reports revenue by day, plan and country, and support staff look up one customer's history. Customers change plans, and finance needs the plan as it was at the time of each invoice.

The engineer builds an invoice fact table partitioned by invoice date and clustered by customer ID. A customer dimension uses type 2 history so each invoice joins to the version valid on its date. Invoice lines are nested in the fact to avoid a large join.

How the exam asks about this

• "Queries always filter on event_date but scan the whole table." Partition the table by event_date and require a partition filter.
• "Orders and their line items are always read together and the join is slow." Store line items as a nested repeated field in the orders table.
• "Queries filter by customer_id within a date range on a 50 TB table." Partition by date and cluster by customer_id.