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.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.
Last updated
Your data engineering team is analyzing BigQuery query plans and Cloud Billing reports. They notice that analytics queries against a highly normalized snowflake schema are incurring high costs and slow performance due to massive join operations and full table scans on specific date and region columns.
You need to minimize query costs and optimize performance for these analytical workloads.
Which optimization strategy should you implement?
Transition the tables to active storage to reduce query execution costs, and use the INFORMATION_SCHEMA.TABLE_STORAGE view to monitor the query plans
Rebuild the table indexes on the date and region columns, and configure distribution keys to optimize the location of data blocks across query workers
Denormalize the schema using nested and repeated fields to reduce joins, and configure clustering on the frequently filtered date and region columns
Maintain the normalized snowflake schema to minimize storage costs, and manually run the VACUUM command after large data loads to sort the table data
Transition the tables to active storage to reduce query execution costs, and use the INFORMATION_SCHEMA.TABLE_STORAGE view to monitor the query plans
Rebuild the table indexes on the date and region columns, and configure distribution keys to optimize the location of data blocks across query workers
Denormalize the schema using nested and repeated fields to reduce joins, and configure clustering on the frequently filtered date and region columns
Denormalization in BigQuery often involves using nested and repeated fields (represented as STRUCT and ARRAY data types) to store related records within a single table, rather than splitting them across multiple relational tables. Clustering is a technique where BigQuery automatically sorts the underlying data blocks based on the values of up to four specified columns.
BigQuery is a massively parallel processing (MPP) system that thrives on wide, denormalized datasets. While it supports normalized schemas, the overhead of shuffling data during massive joins degrades performance and increases costs. Combining denormalization with clustering directly addresses both the join overhead and the full table scan inefficiencies identified in the query plans.
Maintain the normalized snowflake schema to minimize storage costs, and manually run the VACUUM command after large data loads to sort the table data
StudyCerts
StudyCerts