Plan data warehouse schemas in BigQuery, including data model design, normalisation decisions, and architecture that supports required data access patterns.

A retailer is modelling a 6 TB sales fact table in BigQuery that will grow by roughly 30 GB per day. Analysts run two query patterns: ad hoc dashboards that filter on order_date for a single calendar month, and ad hoc investigations that filter on store_id for a single store across all history. The team needs to control on-demand query cost for both patterns and is choosing between partitioning and clustering. Which design best fits these access patterns?

• ACluster the table by order_date and store_id without partitioning, so both filters benefit from block pruning across the full table.
• BPartition the table by store_id and cluster by order_date, so each store has its own partition and date filters use block pruning within the store.
• CPartition the table by order_date and cluster by store_id, so date filters use partition pruning and store_id filters use block pruning within partitions.check_circle Correct
• DAvoid partitioning and clustering on a table this large because the storage overhead of maintaining clustering metadata will exceed the on-demand query savings from pruning.

Why A is wrong: Tempting because clustering does enable block pruning on both columns, but without partitioning BigQuery cannot prune at the partition level and date filters on a 6 TB table will scan substantially more data than necessary. Partitioning by date is the stronger lever for the monthly dashboard pattern.

Why B is wrong: Tempting because partitioning by store seems to mirror the store_id access pattern, but BigQuery limits a table to 10,000 partitions and integer or date partitioning, so per store partitioning is not supported as such and would not match the date-range dashboards either.

Why C is correct: Correct. Partitioning by order_date matches the monthly dashboard pattern and removes the bulk of partitions from the scan. Clustering by store_id organises blocks within each daily partition so that a query filtering on a single store across history still benefits from block pruning, even though it spans all partitions.

Why D is wrong: Tempting because clustering does involve background reclustering work, but BigQuery performs that work at no additional storage charge and clustering metadata is not separately billed. Avoiding both would leave every query scanning the full 6 TB.