GCP PCA BigQuery Partitioning and Clustering — the scan that is not the table

A WHERE can be true of every row you wanted. The engine can still read every row you stored.

<!-- hal:authoritative:yaml -->

A WHERE can be true of every row you wanted. The engine can still read every row you stored.

§I — Frame: third PCA visit, data door

08-08 opened the hierarchy. Organization, folders, projects, Org Policy, Shared VPC, cross-project IAM. That lesson is the landing zone. Leave it.

08-20 opened Compute. Instance template, MIG, health check, autohealing, URL map, forwarding rule. That lesson is the instance that is not a name. Leave it.

Today the Cloud rotation counter reads 6. Six mod 4 is 2. Seat 2 is GCP PCA again. The leftover is not a node image (08-31, K8s day). The leftover is not Private Google Access (08-30, TF day). The leftover is the warehouse. PCA Section 1.3 asks you to choose data processing solutions. Section 2.2 asks you to configure storage, growth, and lifecycle. Section 4.2 asks you to optimize cost. BigQuery sits in all three, and the exam will not award you for knowing the product name. It will award you for knowing which bytes get billed.

Name the duty. Coin it: the scan that is not the table.

Reis and Housley, Query Optimizer, printed pp. 277-278: SELECT * with no predicates scans the entire table; BigQuery can partition a table into smaller segments so a query reads specific partitions; a cluster key orders a large table so later filters skip blocks. Chapter-13 of the Ultimate PCA guide writes the SQL you will actually see: PARTITION BY event_date, CLUSTER BY country, device_type, a count query that names all three, and the claim that March 15 plus US plus mobile scans the March 15 partition and then zooms. The zoom is clustering. The segment is partitioning. The WHERE that names only country still walks every segment.

CLUSTER BY is a BigQuery clause. It is not a GKE cluster. After 08-31, say the clause.

§I.B — Exam facts, currency first

Verified 2026-09-01 against the public certification page and the standard exam guide PDF.

Standard exam: 2 hours, 50-60 multiple choice and multiple select, $200 plus tax, English and Japanese, 2 year validity, no official prerequisites. Each exam includes 2 case studies, 20-30 percent of the exam, split-screen. Four published standard case studies: Altostrat Media, Cymbal Retail, EHR Healthcare, KnightMotives Automotive.

08-08 named EHR Healthcare, Mountkirk Games, and Helicopter Racing League as the published set. EHR remains. Mountkirk Games and Helicopter Racing League are absent from the current standard exam guide. Altostrat Media, Cymbal Retail, and KnightMotives Automotive are present. Study the current four. Do not drill Mountkirk as if it still sits on the split screen.

Domain weights in the current guide: architecture design and planning ~25 percent; manage and provision ~17.5 percent; security and compliance ~17.5 percent; analyze and optimize processes ~15 percent; manage implementation ~12.5 percent; operations excellence ~12.5 percent. The gcp-ace-pca.md cheatsheet still prints ~24 / ~15 / ~18 / ~18 / ~11 / ~14. That is the same class of corpus-currency finding 08-11 named on Entra versus Azure AD. The lesson uses the guide's numbers. The clone is a map, not the exam.

The Well-Architected Framework is now named as a key requirement: operational excellence, security, reliability, performance optimization, cost optimization, sustainability. Today's coin is the cost pillar with a performance aftertaste. A smaller scan is cheaper and usually faster. The exam will offer you an index on a warehouse, a larger machine, and a partition. Pick the partition.

Renewal is a different exam: 1 hour, 25 questions, one generative-AI case study, 90-100 percent case study, $100. Do not mix renewal facts into a first-sit plan. This seat is the standard exam.

§II — Domain foundations: billed bytes

BigQuery stores columnar. Reis's prune for a column store starts with "select only the columns you need." SELECT * is a scan of every column of every row in the segments you failed to skip. On-demand billing follows bytes processed, not rows returned. A COUNT(*) that cannot prune still reads the segments. A LIMIT 10 without a partition predicate is not a 10-row bill.

Slots exist. Reservations exist. Capacity mode exists. Today's leftover is on-demand scan shape, because the PCA question that names a 10 TB table and a dashboard filter is testing whether you know the scan can be smaller than the table. Do not hide in slot pricing to avoid the partition.

Cloud SQL is OLTP. Spanner is global SQL. Bigtable is wide-column low latency. BigQuery is the warehouse. PCA-Notes: looking for horizontal scale on relational, use Spanner rather than Cloud SQL; Bigtable can be used for analytics and offers lower latency than BigQuery. That split is the product-choice half. Today's half is: once you chose BigQuery, you still have to partition it, or you chose a heap.

VPC Service Controls wrap BigQuery because BigQuery does not sit behind a VPC firewall the way a VM does (PCA-Notes, Security). That fact is adjacent. It is not today's center. Do not spend the hour on perimeters.

Dataflow can transform before load (PCA-Notes, Data Workflow). That fact is adjacent. A partitioned table that you load without the partition column populated is still a heap with extra steps. The load job is part of the prune. The query is the other part. Storage Transfer Service and Transfer Appliance (PCA-Notes, Data Transfer) move bytes in. They do not partition them. A 10 TB lift into a heap is a 10 TB scan waiting for the first dashboard. Partition at load, or pay at query. There is no third time. The exam will offer the lift as the architecture and leave the heap unnamed. Name the heap. Name the date column. Name the filter the dashboard will actually send.

§III — PARTITION BY: the segment

Time-unit column partitioning: PARTITION BY event_date or PARTITION BY DATE(event_ts). The prune column is that column. A predicate that can be reduced to a set of partition values skips the rest. A predicate on a different column does not.

Ingestion-time partitioning: PARTITION BY _PARTITIONTIME. Chapter-13 writes this form too. The prune column is the pseudo-column. A dashboard that filters event_date against an ingestion-time table is SQL B. The business clock and the ingest clock are allowed to differ. When they do, you scan the wrong segments and still miss the prune.

require_partition_filter = TRUE is the belt. Queries that omit the partition column fail at compile. The default is off. An architect who partitions and never requires the filter has built a door and left it propped. The exam will offer that as the cheap-looking option. It is the 08-29 rule with zero targets.

You cannot ALTER an unpartitioned table into a partitioned one in place the way you flip a MIG autoscaler. The pattern is a new table, a copy or a scheduled query, a cutover. PCA will dress that as a migration question. The answer is not "add a partition flag."

Partition count has a ceiling. Daily partitions over decades on a tiny table is the opposite leftover: too many segments, too little inside each. Reis's "several terabytes or greater" is the size at which clustering even enters the paragraph. Do not partition a 2 GB table into 2000 empty days and call it architecture.

§IV — CLUSTER BY: the order inside the segment

CLUSTER BY country, device_type sorts blocks inside each partition. A later filter on those columns can skip blocks. The skip is not a B-tree lookup. It is block pruning after segment pruning. If you never pruned the segment, clustering still helps inside every segment, and you still paid to open every segment.

Column order in CLUSTER BY matches filter order. Country then device is right for country = 'US' AND device_type = 'mobile'. Device then country is weaker for that query. The exam will swap the order and wait.

Clustering without partitioning on a multi-terabyte table is legal. Reis warns that inappropriate clustering can degrade performance. The architect's first move on a dated fact table is still the date partition. Clustering is the second move, on the columns the dashboards actually filter.

Do not answer "create an index." BigQuery does not sell you a row-store index on this surface. Do not answer "put it in Cloud SQL so we can index it" unless the access pattern is OLTP, which a 10 TB analytics scan is not.

§V — Worked example: Chapter-13 SQL, two bills

Chapter-13's table:

CREATE TABLE `web_logs.analytics` (
  event_date DATE,
  user_id STRING,
  page_url STRING,
  country STRING,
  device_type STRING
)
PARTITION BY event_date
CLUSTER BY country, device_type;

Query A names event_date, country, and device_type. The engine opens March 15, then skips blocks that are not US/mobile. The scan is not the table.

Query B names country and device_type only. The engine opens every daily segment. Clustering helps inside each. The scan is the table, plus some skipped blocks that do not save the segments.

A Cymbal Retail case-study flavour: a 12 TB click table, a dashboard for yesterday's US mobile conversion. The cheap-looking answer is a bigger reservation. The right answer is partition by event date, cluster by country (and the next filter), require the partition filter, and stop the dashboard from querying through a SELECT * view. Altostrat Media's analytics half will rhyme. EHR's clinical events will rhyme with a stricter belt: require the filter, because an unpruned query is a bill and a scan of PHI-adjacent rows you did not need on screen.

KnightMotives automotive telemetry is a time series. Date partition first. VIN or model as clustering next, if the dashboards filter that way. Do not put VIN in the partition key unless you like a segment per car.

§VI — Pairing to today's Ops and Dev

Ops prints time_partitioning, clustering_fields, require_partition_filter, num_bytes, and a dry-run total_bytes_processed. That is the exam, executed. The Python client is not on the exam. The two numbers are.

Dev prints the same coin in the language. Square brackets finish the work. Parentheses hand you a plan. The warehouse can skip a segment Python cannot skip unless you built groups first. Do not claim a genexp partitions BigQuery. Do claim that a listcomp is SELECT * in muscle memory.

08-26's Route 53 failover is a different vendor's leftover: a mechanism that looks like a replica and is not. Today's leftover is a mechanism that looks like a filter and is not. Cross-vendor reach stops at that rhyme. Do not import health checks into BigQuery.

§VII — Practice questions

A. Move the table to Cloud SQL and add an index on country. B. Partition by a date column the query never names, and cluster by country. C. Partition by ingestion time, cluster by country, and require the partition filter. D. Cluster by country, and rewrite the query to also filter the partition column you will add on event date.

D. Clustering alone on a heap still opens the heap. A date partition plus a predicate on that column is the segment skip. The country cluster then skips blocks inside yesterday. A is the OLTP door. B partitions on a column the query never names, so the prune never fires. C requires a filter the query does not have, so the query starts failing rather than shrinking.

A. Only mobile blocks across all dates are read, because clustering is global. B. Every date partition is opened, then mobile blocks can be skipped inside each. C. The query fails, because require_partition_filter defaults to true. D. BigQuery rewrites the query to add event_date = CURRENT_DATE().

B. Clustering is inside the segment. The default require-filter is off, so the query runs and bills for every partition it opens. There is no automatic date predicate.

A. Clustering expired at midnight. B. The prune column is ingest time, not event_date; the business filter does not skip segments. C. _PARTITIONTIME cannot be used with CLUSTER BY. D. On-demand billing ignores dry runs, so the number is ornamental.

B. Chapter-13 writes both partition styles. Mixing them is SQL B. Dry runs report the plan's bytes. They are not ornamental.

A. They partition the table for you. B. They stop a SELECT * from billing. C. They wrap APIs that do not live as VMs behind a project firewall, to limit data exfiltration. D. They replace IAM on datasets.

C. PCA-Notes: VPC SC for services that do not support firewall rules (Cloud Storage, BigQuery). They do not prune scans and they do not replace dataset IAM.

A. Mountkirk Games, Helicopter Racing League, EHR Healthcare, Dress4Win. B. Altostrat Media, Cymbal Retail, EHR Healthcare, KnightMotives Automotive. C. Altostrat Media, Mountkirk Games, Cymbal Retail, EHR Healthcare. D. EHR Healthcare only; the rest retired.

B. Verified against the current standard exam guide. Mountkirk and Helicopter Racing League are absent. Dress4Win is older still. EHR remains. Study B.

§VIII — Closing

Partition by the column the query will name. Cluster by the columns it names next. Require the filter. Dry-run the dashboard SQL. If total_bytes_processed matches the table, the WHERE was a costume.

Reis: prune, or you scan the table. Chapter-13: March 15, then US mobile. The exam sentence is shorter still. The scan is not the table.

Examine the next case-study warehouse. If the cheap answer is a larger reservation and the query never names a date, the coin is already spent.

Related

• Prior arc: the instance that is not a name (2026-08-20)
• Domain hub: Cross-References/domains/01-Earth-DevOps
• Grounding: Reis/Housley — Query Optimizer · PCA Guide Ch.13