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.
A data engineering team at an enterprise IoT company is architecting a high-volume BigQuery data warehouse table that ingests 2 TB of telemetry data daily. The data contains an event_timestamp, a high-cardinality device_id, a low-cardinality device_type, and sensor metric payloads.
The analytical workload has the following characteristics:
device_id values.device_type.Which table design strategy should the data engineer implement to achieve optimal query performance and cost efficiency?
device_type and cluster the table by event_timestamp and device_id.This configuration attempts to partition the table on a categorical string attribute (device_type) and apply clustering to the time dimension (event_timestamp) alongside device_id.
device_type.event_timestamp in clustering rather than partitioning forces queries to evaluate cluster block metadata rather than skipping entire partitions.The workload was primarily categorized by an integer tenant ID requiring integer-range partitioning with secondary ad-hoc time filtering.
event_timestamp and cluster the table by device_id and device_type.Table partitioning divides a large table into smaller, manageable segments based on a date, timestamp, or integer range column. Clustering sorts the underlying columnar data based on one or more user-specified columns (up to four) within each partition, colocating related data into granular storage blocks.
event_timestamp ensures that queries filtering on recent date windows (e.g., past 7–30 days) perform partition pruning, eliminating unnecessary partition scans entirely.device_id): Clustering by device_id sorts and organizes records into distinct storage blocks, enabling BigQuery to skip scanning irrelevant data blocks (block pruning).device_type): Including device_type in the clustering specification colocates identical device types together within the blocks, speeding up aggregate calculations and group-by operations.Combining time-based partitioning with multi-column clustering leverages BigQuery's two primary storage organization mechanisms simultaneously. This provides the lowest possible bytes scanned for time-series analytical workloads.
event_timestamp, device_id, and device_type without configuring table partitioning.This configuration relies exclusively on table clustering across the timestamp and device columns, leaving the table as a single non-partitioned dataset.
The table was under 1 GB in size or if query filters lacked a predictable timestamp/date range filter.
This option keeps raw CSV files inside Cloud Storage buckets and creates a BigLake external table over the bucket directory hierarchy using Hive partitioning.
The business requirement mandated storing data exclusively in open formats within Cloud Storage for external multi-cloud tools without loading it into BigQuery storage.
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