Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
An enterprise is migrating a large retail data warehouse to BigQuery. The legacy data warehouse uses a normalized snowflake schema consisting of separate tables for orders, line_items, and item_promotions. Analytical workloads frequently execute multi-table joins across these tables, resulting in extensive distributed data shuffling, high slot consumption, and slow query execution.
The data engineering team needs to redesign the schema to optimize query execution and minimize join overhead while preserving the granular one-to-many hierarchical relationships between orders, items, and promotions.
Which schema optimization strategy should the data engineering team implement?
Nested and repeated fields (ARRAY>) in BigQuery allow hierarchical, one-to-many, and many-to-many relationships to be preserved inside a single table row rather than being split across normalized relational tables.
JOIN operations in star and snowflake schemas.UNNEST operator (such as LEFT JOIN UNNEST(...)) without scanning external tables or performing cross-worker network communication.Compared to flat wide tables or traditional snowflake schemas, nested and repeated fields provide the optimal balance in BigQuery by eliminating costly multi-node joins while preventing data redundancy anomalies.
A star schema collapses normalized snowflake dimensions into centralized dimension tables surrounding a fact table, using surrogate integer keys and table clustering on join keys to co-locate related blocks on storage.
The target analytical system were an on-premises relational data warehouse with traditional B-tree indexing where surrogate keys provide performance advantages.
Retaining the normalized snowflake schema keeps data separated into individual relational tables and attempts to accelerate recurring queries by defining a precomputed materialized view that joins the base tables.
The primary goal was accelerating a specific, static aggregated dashboard query over an existing legacy schema without altering table DDL or ingestion pipelines.
Flat denormalization involves collapsing relational tables into a single wide table where parent row attributes (such as order ID, customer ID, order date) are repeated across multiple flat rows for every related child entry.
DISTINCT aggregates, increasing query complexity and compute cost.RECORD/STRUCT and ARRAY), which solve the join bottleneck without requiring flat row duplication.The destination system were a legacy database engine that lacked native support for arrays, structs, and complex object types.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated