Sample questions
Wh Bigquery Storage Partitioning ClusteringDifficulty 1
Which three strategies can you use to partition a table in BigQuery?
- aHash key, range key, and composite key
- bPrimary key, foreign key, and surrogate key
- cTime-unit, ingestion time, integer range✓
- dRow-level, column-level, and block-level
Explanation:BigQuery offers three partitioning strategies: a time-unit column (DATE/TIMESTAMP/DATETIME), ingestion time (based on load time), and integer range on an INTEGER column.
Wh Bigquery Storage Partitioning ClusteringDifficulty 1
For a table using ingestion-time partitioning with no explicit partitioning column, which pseudocolumns does BigQuery expose to reference the partition?
- a
_PARTITIONTIME and _PARTITIONDATE✓ - b
_INGESTED_AT and _LOAD_DATE - c
_ROW_TIMESTAMP and _PARTITION_KEY - d
_EVENT_TIME and _BATCH_ID
Explanation:Ingestion-time partitioned tables expose the pseudocolumns _PARTITIONTIME (TIMESTAMP) and _PARTITIONDATE (DATE) so queries can filter on the load-time partition.
Wh Bigquery Storage Partitioning ClusteringDifficulty 1
What three values do you specify to define the ranges of an integer-range partitioned table?
- aA minimum value, a maximum value, and a partition count
- bA start value, an end, and an interval✓
- cA seed value, a hash bucket count, and a salt
- dA base value, a growth rate, and a ceiling
Explanation:Integer-range partitioning is defined by a start (inclusive), an end (exclusive), and an interval, e.g. RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10)).
Wh Bigquery Storage Partitioning ClusteringDifficulty 1
At most how many columns can you specify when clustering a BigQuery table?
Explanation:BigQuery allows at most four clustering columns per table, specified with CLUSTER BY.
Wh Bigquery Storage Partitioning ClusteringDifficulty 2
Why does the order of columns matter when you define clustering on a BigQuery table?
- aA filter benefits only when it covers a leading prefix of the clustered columns✓
- bThe first clustered column determines which region the table's storage lives in
- cColumn order only changes how the table looks in the console UI, not query cost
- dBigQuery re-sorts the given columns alphabetically regardless of the order you list
Explanation:Clustering benefit is prefix-based: a query filtering on the first N clustered columns (in the declared order) can prune blocks, but filtering only on a later column skips the benefit of the earlier ones.
Wh Bigquery Storage Partitioning ClusteringDifficulty 2
What does BigQuery actually do when you cluster a table by one or more columns?
- aIt builds a secondary B-tree index over the clustered columns
- bIt moves the clustered columns into a separate, smaller physical table
- cIt orders storage blocks by the clustered values, so matching queries scan less✓
- dIt caches the clustered columns in memory for the duration of the session
Explanation:Clustering physically co-locates and sorts rows into storage blocks by the clustered column values, so a query filtering or aggregating on those columns only needs to read the relevant blocks.