Question

A retail analytics team is designing a BigQuery dataset for daily sales reporting. Analysts frequently query aggregated revenue by product category, region, and date. The team debates whether to keep normalized dimension tables (products, stores) separate from a large fact table or to consolidate the data. Query cost and performance on large scans are the primary concerns, and the dimension tables change infrequently. Which schema approach best fits BigQuery's architecture?

Accepted Answer

Denormalize the dimensions into the fact table (optionally using nested/repeated fields), producing a wide, flattened schema optimized for scans.

Answer

Maintain a strict third-normal-form star schema and rely on runtime JOINs across many small dimension tables for every query.

Answer

Store the fact table in BigQuery but keep dimension tables in Cloud SQL and use federated queries to join them at read time.

Answer

Split the fact table into one physical table per product category to eliminate the need for filtering.