professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
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?
Denormalization in BigQuery involves flattening relational star and snowflake schemas by embedding related dimension attributes directly into fact records at event creation time. By utilizing nested and repeated fields (STRUCT and ARRAY types), BigQuery allows complex hierarchical relationships to be preserved within a single table, eliminating the need for relational normalization and multi-table joins.
StudyCerts
StudyCertsJOIN operations across separate dimension tables.STRUCT fields minimizes data shuffling across BigQuery slot workers, optimizing execution speed and reducing query slot consumption on massive datasets.BETWEEN valid_from AND valid_to) that degrade analytical query performance at scale.STRUCT fields maintain clean logical groupings of attributes (e.g., customer.address, customer.sales_rep) rather than polluting the table namespace.Traditional relational data warehouses rely on normalized SCD Type 2 tables with surrogate keys. In modern columnar data warehouses like BigQuery, denormalizing dimensional state at transaction time—supported by nested fields—is the architectural best practice for maximizing read throughput, reducing billing costs, and preserving historical fidelity.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.