PDE - Storing Data (20% of the exam) - Section 3.2

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

Design BigQuery warehouse schemas by choosing between normalised and denormalised models, and apply partitioning and clustering strategies to optimise query performance and reduce slot consumption for the expected access patterns.

BigQuerySchema designPartitioningClustering

Practice question for this objective

Free sampleStoring Datamedium

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. 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.

Choose date partitioning combined with clustering on a frequently filtered dimension when access patterns mix date range queries with single value lookups. Partitioning is best used on the column whose filters cut the table into manageable slices, typically a date or ingestion time column, because partition metadata is checked first and discards entire partitions cheaply. Clustering complements this by sorting rows inside each partition on the clustering columns, so additional filters such as a single store identifier can still skip blocks even when the query spans many partitions.

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.

See more PDE practice questions, answers explained.

Exam traps in Storing Data

Answers that look right on this material and are not. Each one is a distractor from a different question in the PDE bank for this domain.

Partition the table by ingestion time and cluster on region_code, then require a partition filter on the ingestion pseudo-column for every query.
Why it is wrong: Tempting because ingestion-time partitioning is simple to apply, but analysts filter on call_start_time, which can differ from ingestion time for late or backfilled records, so pruning would be unreliable and could exclude valid rows.
Partition by ingestion time using _PARTITIONTIME and cluster on campaign_id so that the less common access pattern is fastest, accepting full scans for advertiser_id queries.
Why it is wrong: Ingestion-time partitioning does not align with the impression_date filter analysts use, and clustering on the rare access pattern penalises the dominant workload.
Drop the customer dimension entirely and store plan_type and region on every call record, then re-derive any historical customer attributes from operational backups when needed.
Why it is wrong: Eliminating the dimension destroys the ability to manage attribute changes cleanly and forces full table rewrites whenever a plan or region is updated for any customer.

Examworthy is not affiliated with or endorsed by Google Cloud. Original, blueprint-aligned practice material only.