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.
An enterprise retail analytics platform stores multi-terabyte transactional order data in Google Cloud BigQuery. Historical workload analysis reveals two primary query patterns:
order_date, with selective filtering on customer_id and store_id.COUNT and SUM of sales_amount) grouped by order_date, store_id, and product_category throughout the business day, causing slot contention.The base table is configured with daily partition expiration to automatically purge data older than two years. You need to design a storage and query acceleration architecture that optimizes query pruning, accelerates dashboard rendering, minimizes slot consumption, and ensures analytical caches do not get completely invalidated when base table partitions expire.
Which architecture should you implement?
order_date and cluster by customer_id. Create a non-partitioned materialized view for the aggregate dashboard queries and configure a manual 730-day partition expiration policy directly on the materialized view.This option sets up a partitioned and clustered base table but pairs it with an unpartitioned materialized view and attempts to set explicit partition expiration directly on the materialized view definition.
store_id from the clustering keys leaves ad-hoc queries filtering on store identifiers to scan broader data blocks within partitions.The base table had no partition expiration configured, was non-partitioned, and dashboard queries spanned all historical records without benefit from partition pruning.
order_date at daily granularity and cluster it by customer_id and store_id. Create a materialized view that precomputes the aggregated metrics grouped by order_date, store_id, and product_category, partitioned by order_date and clustered by store_id.This architecture combines table partitioning, multi-column clustering, and an aligned partitioned materialized view in BigQuery to optimize both granular ad-hoc filtering and recurring aggregate dashboard queries while preserving compute resources.
order_date allows BigQuery to prune unneeded partitions during scans. Clustering by customer_id and store_id sorts the data blocks within each partition, allowing fine-grained block skipping during selective filtering.COUNT and SUM aggregations grouped by order_date, store_id, and product_category. When dashboards run, BigQuery uses smart tuning and incremental delta computation to serve results from precomputed storage rather than scanning raw base tables, drastically reducing slot consumption.order_date, the materialized view partitions align synchronously with base table partitions. When a base table partition expires, only that specific partition is dropped in the view, avoiding full view cache invalidation and expensive full refreshes.customer_id.This design targets both access tiers: selective base table scans benefit from partition pruning and clustering block-skipping, while repeated dashboard aggregations leverage zero-maintenance, partitioned materialized views.
customer_id with daily clustering on order_date. Create a standard logical view that encapsulates dashboard queries and allocate a BI Engine reservation to the dataset.This configuration partitions the base table using an integer range on customer_id, clusters by order_date, encapsulates the dashboard SQL in a standard logical view, and assigns BigQuery BI Engine in-memory capacity.
customer_id forces time-series queries spanning multiple years to evaluate every integer partition, scanning excessive data and increasing slot runtime.The predominant query pattern filtered on tight contiguous numeric customer ID ranges rather than time ranges, and the base table had no time-based data lifecycle requirements.
orders_YYYYMMDD) clustered by customer_id and store_id. Configure a Cloud Composer DAG to execute scheduled queries that populate summary aggregation tables for dashboards.This approach uses legacy table sharding (creating discrete daily tables like orders_20240101) combined with scheduled ELT pipelines orchestrating batch aggregations into physical summary tables.
orders_*), which incur significant planning overhead and slow down slot allocation compared to native partitioned tables.The organization were operating on legacy data systems that lack native partitioned table support and required distinct access control lists per daily file.
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