Model a BigQuery table of store transactions (transaction time, items, store ID, city, state) queried frequently for item counts over the past 30 days and trends by state/city/store. How should you partition and cluster for best performance?

Partitioning by transaction time is crucial because queries frequently filter by time (e.g., "past 30 days"). This significantly reduces the amount of data BigQuery needs to scan. Clustering by state, then city, then store ID is optimal because the queries also analyze trends by these geographical and organizational dimensions. Clustering sorts data within partitions, ensuring that rows with similar state, city, and store ID values are stored together. This co-location speeds up queries that group or filter by these columns, as BigQuery can efficiently read contiguous blocks of relevant data, minimizing I/O and improving performance. The order of clustering columns should reflect the typical query granularity, moving from broader (state) to narrower (store ID) for effective pruning.