Professional Data Engineer

By Google · Question Mode

QuestionQ53

Designing data processing systems

You are migrating a table to BigQuery and are choosing its data model. The table contains information about purchases made across multiple store locations, including the transaction time, purchased items, store ID, and the city and state where the store is located. You frequently query the table to determine how many of each item sold during the past 30 days and to analyze purchasing trends by state, city, and individual store. How should you model this table for optimal query performance?

Explanation

Partitioning by transaction time allows BigQuery to prune partitions outside the 30-day reporting window. Clustering within each time partition by state, then city, then store ID aligns with the geographic filtering hierarchy. BigQuery gives precedence to earlier clustering columns and achieves the best block pruning when filters follow the clustering-column order.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!