Storing the data Study Guide — Professional Data Engineer

Storing the Data

This domain tests your ability to select the right GCP storage product for a given workload, configure it optimally, and understand the pricing and performance implications of those choices. It constitutes 20% of the exam, making it the joint-largest domain. Expect scenario-based questions where you must distinguish between multiple plausible storage services, justify schema design decisions, and predict the cost or performance impact of configuration changes.

BigQuery Storage: Partitioning, Clustering, and Materialized Views

Partitioning

Partitioning divides a table into segments called partitions, allowing BigQuery to skip irrelevant partitions during a query (called partition pruning). This reduces both query cost (on-demand billing is per bytes scanned) and latency.

Key limits:

• Max 4,000 partitions per table.
• Partition expiration can be set at the table or partition level.
• A query must filter on the partition column to benefit from pruning; filtering on a non-partition column still scans all partitions.
• Require partition filter setting forces queries to include a partition predicate, preventing costly full-table scans.

Clustering

Clustering sorts table data by up to 4 columns (in order). BigQuery uses block-level metadata to skip blocks that cannot contain matching rows — called block pruning.

Partitioning + clustering together is the recommended pattern: partition on date, cluster on frequently filtered dimensions (e.g., customer_id, region).

Materialized Views

A materialized view precomputes and stores query results. BigQuery automatically refreshes them when the base table changes (within 5 minutes for standard refresh, or on a manual refresh). They can serve queries directly or be used by the query optimizer to rewrite queries against the base table (smart tuning).

• Materialized views are read-only.
• Supported aggregations: COUNT, SUM, AVG, MIN, MAX, COUNTIF, HLL_COUNT.
• Joins are not supported in materialized view definitions (as of current exam version).
• Billing: you pay for storage of the materialized view plus any refresh compute.

Common traps:

• Choosing clustering when the column is low-cardinality (e.g., boolean): clustering is only beneficial for high-cardinality columns.
• Believing partition pruning works when the WHERE clause uses a non-partition column.
• Assuming materialized views support arbitrary SQL including JOINs — they do not.
• Confusing _PARTITIONTIME (ingestion-time) with a column-based partition; the pseudo-column cannot be filtered with a standard column name.

Worked scenario: A table has 5 years of daily sales data (~500 GB). Analysts always filter by sale_date and often also filter by store_id (10,000 unique values). Correct choice: Partition on sale_date (daily), cluster on store_id. This combination prunes whole date partitions first, then prunes blocks within those partitions by store.

BigQuery Slots, Pricing Models, and BI Engine

Slots

A BigQuery slot is a unit of computational capacity. On-demand queries use a shared slot pool; capacity pricing gives you dedicated slots.

Legacy terms you may still see: Flat-rate (now replaced by Reservations under the new editions model). The exam may use either vocabulary.

Reservations and Commitments:

• A Reservation allocates a slot count to a project/folder.
• A Commitment is a 1-year or 3-year purchase that discounts the per-slot-hour cost vs. the flex (pay-as-you-go) rate.
• Idle slots in a reservation can be shared with other reservations in the same organization.

BI Engine

BigQuery BI Engine is an in-memory analysis service that accelerates SQL queries and BI tool dashboards (Looker Studio, Looker).

• You purchase BI Engine capacity in GB (memory reservation, not slots).
• BI Engine caches table data in memory and serves queries without hitting BigQuery compute slots for simple aggregations.
• If a query is too complex for BI Engine, it falls back to regular BigQuery execution transparently.
• Billed per GB-hour of reserved memory.
• Best for: high-concurrency dashboards with repetitive queries on moderate-sized tables.

Common traps:

• Confusing BI Engine GB (memory) with BigQuery slots (compute).
• Assuming BI Engine eliminates all query costs — complex queries still use slots.
• Choosing BI Engine for large ETL workloads; it is optimized for BI/dashboard query patterns, not batch ETL.