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.
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?
A snowflake schema is a highly normalized data model where dimension tables are broken down into further sub-dimensions, resembling a snowflake. The VACUUM command is an administrative operation used in some databases (like PostgreSQL or Redshift) to reclaim storage space and fully sort table data after large bulk loads or deletions.
Maintaining a snowflake schema in BigQuery forces the engine to perform complex, multi-stage join operations, which increases slot consumption and query costs. Furthermore, BigQuery does not support or require a manual VACUUM command to sort data.
You were operating a traditional, self-managed data warehouse that requires explicit maintenance windows and manual commands to sort data blocks and reclaim fragmented disk space.
Indexes (like B-trees) and distribution keys are traditional database optimization techniques. Indexes provide fast row lookups, while distribution keys dictate how data is physically distributed across distinct compute nodes in clustered databases like Amazon Redshift or traditional RDBMS.
BigQuery is a serverless data warehouse that separates compute and storage. It does not use indexes to query data, nor does it use distribution keys. Attempting to configure these will fail, as the concepts do not exist within the BigQuery architecture.
You were migrating to or optimizing an Amazon Redshift cluster, where configuring compound sort keys and distribution keys is a mandatory step for tuning query performance across compute nodes.
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.
Active storage is the default pricing tier in BigQuery for any table or partition that has been modified within the last 90 days. The INFORMATION_SCHEMA.TABLE_STORAGE view provides metadata regarding the physical and logical bytes stored within a dataset.
Transitioning data to active storage does not reduce costs; in fact, active storage is approximately twice as expensive as long-term storage. Furthermore, the TABLE_STORAGE view is designed to audit storage footprint, not to analyze query plans or execution performance.
INFORMATION_SCHEMA.JOBS view or the Query execution graph, not storage views.TABLE_STORAGE_USAGE_TIMELINE is recommended over the standard TABLE_STORAGE view.The scenario asked how to monitor the historical growth of physical bytes stored in a dataset over time to audit storage billing, in which case using the TABLE_STORAGE_USAGE_TIMELINE view would be appropriate.
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