Python BigQuery Partition Census — the scan that is not the table

The WHERE clause can be honest. The engine can still read the whole table.

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

The WHERE clause can be honest. The engine can still read the whole table.

§I — Frame

Monday named a host. COS_CONTAINERD sat under the kubelet. Shielded Nodes wrapped the VM. Coin of that day: the host that is not the cluster. The cloud seat was GKE because the Cert seat was CKS System Hardening.

Sunday named a subnet latch. private_ip_google_access on google_compute_subnetwork. Coin: the access that is not a public IP. Saturday named a bus. Coin: the schedule that is not a crontab. Those three leftovers still sit in the room. None of them is today's leftover.

Today the Cert seat is GCP PCA, the third Google visit. 08-08 opened the hierarchy: organization, folders, projects, Org Policy, Shared VPC. 08-20 opened Compute: MIGs, HTTP(S) load balancing, autohealing, the instance that is not a name. The PCA blueprint still has a data-processing door the last two visits left shut. The name of that door is BigQuery. The concrete object an ops tool can ask without redrawing the 08-20 instance group is a table, its partition spec, its clustering fields, and a dry-run byte count.

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

A table is a name in a dataset. A scan is the bytes the engine actually reads. Reis and Housley put the trap in one paragraph: SELECT * with no predicates scans the entire table and retrieves every row and column; BigQuery lets you partition a table into smaller segments so a query reads specific partitions instead of the entire table; a cluster key orders the data so a later filter can skip blocks inside a partition (Fundamentals of Data Engineering, The Query Optimizer, printed pp. 277-278). Partitioning is not a WHERE. Clustering is not an index you CREATE INDEX on a row store. Both exist so the scan can be smaller than the table.

The Python ops tool talks to google.cloud.bigquery.Client. It lists tables in a dataset. It get_table for each. It prints time_partitioning and clustering_fields and num_bytes. It submits the candidate SQL with QueryJobConfig(dry_run=True, use_query_cache=False) and prints total_bytes_processed. It does not query() for real. It does not update_table. It does not load_table_from_uri. If a table has no partitioning and a query would read num_bytes, the tool says so, because a warehouse table with no partition is a crontab that always runs the whole file.

08-20 already taught a census that refuses to resize a MIG. 08-26 already taught a census that refuses change_resource_record_sets. 08-29 already taught a census that refuses put_rule. This lesson does not reopen those APIs. The leftover here is a memorized SELECT *, or a WHERE on a column the table was never partitioned by.

CLUSTER BY is a BigQuery clause. It is not a GKE cluster. After 08-31, say the clause out loud before you read the word.

§II — Foundations: four facts about the scan

Fact one. Bytes billed follow the scan, not the name. BigQuery on-demand charges for bytes processed. The table's num_bytes is the ceiling. total_bytes_processed on a dry run is the number the engine would actually read. If those two numbers match on a filtered query, the filter did not prune. Reis names the rule of thumb: query only the data you need. The PCA exam will dress that rule as a cost question. The ops tool prints both numbers on one line so the dressing is optional.

Fact two. Partitioning cuts the table into addressable segments. Time-unit column partitioning (PARTITION BY DATE(event_ts) or PARTITION BY event_date) makes a segment per day, hour, month, or year. Ingestion-time partitioning (PARTITION BY _PARTITIONTIME) makes a segment per ingest window. Chapter-13 of the Ultimate PCA guide writes both shapes against web_logs.analytics and says the quiet part: with partitioning by event_date and a predicate event_date = '2025-03-15', BigQuery only scans the March 15 partition. A predicate on country without a predicate on event_date still walks every partition. The column in the PARTITION BY is the only column that can shrink the scan to a segment. Every other column is a filter inside whatever segments remain.

Fact three. Clustering orders blocks inside a partition. CLUSTER BY country, device_type sorts rows so that a later AND country = 'US' AND device_type = 'mobile' can skip blocks that cannot match. Clustering without a partition on a multi-terabyte table is legal and usually the wrong first move. Reis's own caution sits in the same paragraph: inappropriate clustering and key distribution can degrade performance. The ops tool prints clustering_fields next to time_partitioning.type. A clustered unpartitioned table is a finding, not a virtue.

Fact four. A dry run is a compile that does not bill. QueryJobConfig(dry_run=True) asks the service to plan the SQL and return total_bytes_processed without executing. use_query_cache=False keeps a cached prior result from lying about the plan. The dry run is the census of the scan. A real query() is a write against the billing account. This tool does not do that. If the candidate SQL cannot prune, the number comes back equal to the table, and that is the finding.

Four facts, one coin. The table is the object in the dataset. The scan is the bytes the plan would read. They are allowed to differ. When they do not, the warehouse is doing crontab work: everything, every time.

§III — Mechanism: the census, not the rewrite

Construct the client once, with a project. The default dataset is an argument, not a guess. list_tables returns stubs. get_table is the call that carries time_partitioning, range_partitioning, clustering_fields, num_bytes, num_rows, and require_partition_filter. Print them. Do not pretty-print a schema dump unless a column named in a candidate WHERE is missing from the schema. Missing column is a finding; a 200-field schema print is not.

require_partition_filter is the table's own belt. When it is true, a query that omits the partition column fails at compile. When it is false, the engine will scan every partition and bill for it. The ops tool treats require_partition_filter is False on a partitioned table as a leftover, the same class as 08-29's scheduled rule with zero targets: the mechanism exists and nobody required it.

For each candidate SQL the operator already intends to run, submit a dry run. Print total_bytes_processed next to num_bytes. Compute the ratio. A ratio of 1.0 on a filtered query is the coin spent badly. A ratio near 0.0 on a day-shaped predicate against a daily partition is the coin spent well. Do not round the ratio into a letter grade. Print the two integers.

Do not update_table to set partitioning. Partitioning of an existing table is a copy into a new table, not a flag flip. Do not load_table_from_uri. Do not insert_rows_json. The census that starts rewriting the warehouse is no longer a census.

The client library is practitioner surface. Reis and Chapter-13 ground the bytes and the SQL. Bootcamp clones do not carry google.cloud.bigquery.Client. That empty is the same class as 08-31's missing COS_CONTAINERD cheatsheet knob: the concept is on the shelf, the method name is not. Do not log a two-corpus gap for a method name.

from google.cloud import bigquery

def census(project, dataset_id, candidates):
    client = bigquery.Client(project=project)
    dataset = client.dataset(dataset_id)
    rows = []
    for stub in client.list_tables(dataset_id):
        table = client.get_table(dataset.table(stub.table_id))
        part = table.time_partitioning
        rows.append(
            {
                "id": table.full_table_id,
                "bytes": table.num_bytes,
                "partition_type": None if part is None else part.type_,
                "partition_field": None if part is None else part.field,
                "require_partition_filter": table.require_partition_filter,
                "clustering": list(table.clustering_fields or []),
            }
        )
    plans = []
    cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
    for sql in candidates:
        job = client.query(sql, job_config=cfg)
        plans.append({"sql": sql, "bytes_processed": job.total_bytes_processed})
    return rows, plans

The function returns two lists. The caller prints them. The caller does not loop the plans into a real query. Explanation sits in this paragraph, not in a hash-comment inside the block.

§IV — Worked example: one dataset, two SQLs, two numbers

Take Chapter-13's table as the named object: web_logs.analytics, PARTITION BY event_date, CLUSTER BY country, device_type. Imagine num_bytes reports 12 TiB. The operator has two SQLs already in a dashboard.

SQL A selects a count with event_date = '2025-03-15' AND country = 'US' AND device_type = 'mobile'. SQL B selects the same count with only country = 'US' AND device_type = 'mobile'.

Dry-run A. total_bytes_processed comes back in the low tens of GiB, because March 15 is one segment and the clustering can skip blocks that are not US/mobile. Dry-run B. total_bytes_processed comes back near 12 TiB, because no partition column was named, so every daily segment is in play. The clustering still helps inside each segment. It does not delete the segments. The scan is the table.

The leftover is SQL B sitting in a Looker tile, or in a scheduled query, or in a colleague's notebook, with a comment that "we filter country." The filter is real. The prune is not. Reis's sentence still holds: all queries scan data, but not all scans are created equal.

A third leftover shows up on get_table: time_partitioning is None, clustering_fields is None, num_bytes is already in the terabytes. That table is a heap. SQL A and SQL B will both report the same total_bytes_processed. Partitioning cannot be retrofitted in place. The finding is "new table, copy, cut over," and this tool does not perform the copy. It prints the None.

Ingestion-time partitioning (_PARTITIONTIME) is a fourth leftover when the dashboard filters event_date and the table was partitioned on ingest time. Chapter-13 writes that shape too. The predicate that prunes is _PARTITIONTIME, or _PARTITIONDATE, not the business column the analyst named. The census prints partition_field. If that field is None on an ingestion-time table, the prune column is the pseudo-column. SQL that never names it is SQL B in costume.

§IV.B — Five leftovers the census is for

The first leftover is SQL that filters a clustering column and never names the partition column. The dashboard looks selective. The dry run matches num_bytes. Chapter-13's SQL B in other clothes.

The second leftover is a table whose time_partitioning is None at a size where Reis would already have reached for segments. There is no prune to perform. The finding is a new table, not a better WHERE.

The third leftover is ingestion-time partitioning behind a business-date filter. _PARTITIONTIME is the prune column. event_date is a filter. If those two clocks drifted, the scan is still the table plus a lie about which day you meant.

The fourth leftover is require_partition_filter is False on a partitioned table. The engine will accept the unpruned query. Billing will too. The belt is on the chair.

The fifth leftover is a SELECT * inside a view that the dashboard then filters. The view is compiled first. The filter arrives too late to prune the inner scan. Dry-run the view's SQL, not the outer tile alone. The number that matters is the inner one.

Print all five. Fix none of them from this process. Maghrib owns drills. This hour owns the list.

§V — Connection to prior lessons

08-20 listed MIG instances and refused to treat name as the identity. Today's table has a name too. The name is not the scan. full_table_id is how you file the finding. total_bytes_processed is how you know whether the finding is expensive. Identity of the instance was a compute lesson. Identity of the scan is a warehouse lesson. Keep them apart.

08-26 listed Route 53 health checks and refused to write a failover record. A health check that is not attached is a mechanism with no listener. A partition spec that is not required (require_partition_filter is False) is the same shape: the mechanism exists, the next query is free to ignore it. The census names that freedom. It does not flip the flag.

08-29 listed EventBridge buses, rules, and targets, and refused put_rule. A scheduled rule with zero targets is a clock nobody hears. A dry-run that reports the full table is a clock that always hears everything. Both are leftovers you can print without mutating the account.

08-30's PGA latch is adjacent in the cloud and off-topic for this tool. Private Google Access is how a VM without a public IP reaches bigquery.googleapis.com. This lesson assumes the client can already reach the API. Do not reopen the subnet.

08-31's COS host is adjacent in the week and off-topic for this tool. Do not describe a GKE node. Do not say cluster unless you mean CLUSTER BY.

§VI — Connection to today's Dev lesson

The Dev lesson takes the same coin into the language. A list comprehension is SELECT *: it walks the iterable now and materializes the whole result. A generator expression is the partition prune: it yields the next matching item when asked, and it does not build the list. Ramalho's gen_AB demo is the dry run you can perform without a billing account. Square brackets finish the work before you iterate. Parentheses hand you a plan.

If the Ops tool built a list of every row to decide whether a partition would have helped, it would have billed the table to avoid billing the table. The language has the same trap. [row for row in table if row.date == day] is SQL B written in Python. (row for row in table if row.date == day) is still a full walk if table is already materialized, but it does not add a second copy. The warehouse prune only happens when the engine can skip segments. The language prune only happens when you never built the list. Today's two lessons are one coin at two altitudes.

§VII — Closing

Construct the client once, with a project. List the tables. Print partition type, partition field, require-filter, clustering, bytes. Dry-run the candidate SQL with the cache off. Print total_bytes_processed next to num_bytes. Do not query for real. Do not rewrite the table.

Reis's sentence is short: prune, or you scan the table. Chapter-13's sentence is shorter: partition by the date you filter, cluster by the columns you filter next. The ops sentence is shorter still. The process that listed them billed no bytes.

Examine the next scheduled query in the project. If it filters a column the table was never partitioned on, and the dry run matches num_bytes, the coin is already spent.

Related

• Prior arc: the schedule that is not a crontab (2026-08-29)
• Domain hub: Cross-References/domains/01-Earth-DevOps
• Grounding: Reis/Housley — Query Optimizer, prune or scan the table · PCA Guide Ch.13 PARTITION BY / CLUSTER BY