Google recommends denormalizing in BigQuery because reading a wide, flat table or a table with nested fields is often faster and simpler than joining many tables, and storage costs less than compute. But joins are still perfectly fine in many cases.
Why denormalizing helps
A join needs rows with the same key to meet, which means moving data between workers (a shuffle). Reading one table that already holds the related data avoids that. Columnar storage means unused columns cost nothing to read, so a wide table is cheap if you select only what you need.
Nested and repeated fields
BigQuery can store an order and its items together:
CREATE TABLE sales.orders ( order_id INT64, customer_id INT64, items ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>> );
The items live inside the order row, so reading an order with its items needs no join. You query them with UNNEST. This keeps related data together and still compresses well.
When joins are fine
- A big fact table joined to a small dimension: BigQuery can broadcast the small side to all workers, which is cheap.
- A classic star schema works well in BigQuery, and BI tools expect it. Many teams keep facts and dimensions and denormalize only a few hot attributes.
- Attributes that change often. Copying them into every fact row means rewriting huge amounts of data on each change, whereas a dimension table changes in one place.
When to avoid joins
Joining two huge fact tables on a high-cardinality key moves enormous volumes of data and is slow. Pre-join them in a pipeline, or aggregate one side first.
How to decide
Look at the query patterns. If the same join runs thousands of times a day, doing it once at load time and storing the result pays off. If the join is rare, or the data changes often, keep it normalized. Test both on realistic volumes, comparing bytes billed and slot time, instead of following a rule blindly.