Bigquery Denormalization Nested Repeated Fields

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

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?

Denormalize the dataset into a single orders table using nested and repeated fields (ARRAY of STRUCT) for line items and promotions, accessing sub-elements using UNNEST.

Flatten the schema into a single wide table by duplicating parent order attributes across individual flat rows for every line item and promotion combination.

Convert the snowflake schema into a traditional star schema with surrogate keys and cluster all fact and dimension tables on their join keys.

Retain the normalized snowflake schema across separate tables and create a materialized view with an inner join between orders and line items.

Professional Cloud Data Engineer

Bigquery Denormalization Nested Repeated Fields

Related questions