Data Warehouse — PDE Study Guide

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Professional Data Engineer premium course — no separate purchase.

Included in this chapter:

Why warehouse design flips the OLTP rules
Shaping the data: denormalize with nested and repeated fields
Physical layout: partition, cluster, materialize
Mapping requirements to the model, and exam traps

How each design lever reduces cost or latency

Design lever	What it does	Cuts bytes scanned?	Best for
Denormalize into wide table	Folds related data into one table so reads avoid joins	Indirectly (fewer joins, less shuffle)	The default analytic table
Nested / repeated fields	Models one-to-many in a single row via ARRAY of STRUCT	Yes (prunes to referenced sub-fields)	Parent with child records read together
Partitioning	Splits a table by date / timestamp / integer range	Yes (skips non-matching partitions)	Queries that filter on the partition column
Clustering	Sorts rows within a partition by up to 4 columns	Yes (reads fewer blocks)	Filter / group on high-cardinality columns
Materialized view	Precomputes and incrementally refreshes an aggregation	Yes (scans the small precomputed result)	The same heavy aggregation run repeatedly
Star schema (kept dimensions)	Joins facts to separate dimension tables	No (adds a join)	Large dimensions that change often

Cheat sheet

Cheat sheet

Denormalize for BigQuery, because the join costs more than the storage
Model one-to-many with an ARRAY of STRUCT, not a child table
Nesting is not flattening, flattening duplicates the parent
Partition on one date, timestamp, or integer-range column
Cluster on up to four columns, ordered left to right
Partition to skip data, cluster to sort within it, and stack both
Cluster, do not partition, on a high-cardinality column
Set require_partition_filter to stop accidental full scans
A materialized view precomputes an aggregation and refreshes incrementally
BigQuery reroutes base-table queries to a matching materialized view
A logical view stores query text, a materialized view stores results
Keep an independently-changing dimension as a joined table
Set the table grain to the dominant query
Let the access pattern pick the partition and cluster columns
Layout changes fix a single slow query, more slots do not
Leave small lookup tables flat
A table's partition expiration overrides the dataset default
Load files into BigQuery on arrival with an event-driven transfer

Unlock with Premium — includes all practice exams and the complete study guide.

References