Bigquery Scd Nested Denormalized Temporal Modeling

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 designing a high-volume retail data warehouse in BigQuery. The data modeling team must capture slowly changing customer attributes (such as address, loyalty status, and assigned sales representative) to ensure accurate point-in-time historical analysis for sales orders.

The analytical workload requires fast query performance across billions of sales records while minimizing expensive multi-table JOIN operations and computational shuffle overhead.

Which schema design and temporal modeling strategy should the team implement?

Denormalize the customer dimension attributes into the sales fact table at transaction time, utilizing nested STRUCT fields to preserve entity relationships and capture point-in-time historical state without runtime joins

Implement an SCD Type 1 model on a centralized customer dimension table that overwrites updated fields, and use BigQuery table snapshots to reconstruct historical dimensions

Maintain a normalized SCD Type 2 customer table with valid_from and valid_to timestamps, requiring sales analytical queries to execute non-equi joins on date ranges

Design a Third Normal Form (3NF) relational schema with transactional change logs, and reconstruct historical customer states at query time using recursive Common Table Expressions (CTEs)

Professional Cloud Data Engineer

Bigquery Scd Nested Denormalized Temporal Modeling

Related questions