bigquery clustering vs partitioning decision

TL;DR

Partition by the column you always filter on (usually a date), cluster by the columns you additionally filter or group by. Partitioning prunes whole chunks; clustering sorts within chunks. Use both together far more often than either alone.

bigquery clustering vs partitioning decision

Use this when

• Designing a new BigQuery table layout
• Queries scan far more bytes than they need
• Choosing partition vs cluster columns

Not for this skill when

• Queries queue on slot contention
• Snowflake queries spill to disk
• You are configuring dbt incremental models

Steps

1. Partition on the time column you filter in nearly every query:

CREATE TABLE events (
  event_date DATE, user_id INT64, amount NUMERIC
)
PARTITION BY event_date;

Expected output: queries with WHERE event_date >= ... skip entire partitions. Partitioning is coarse pruning: it eliminates storage chunks before reading.

1. Cluster on the next most-filtered columns, up to 4:

CREATE TABLE events (
  event_date DATE, user_id INT64, event_type STRING
)
PARTITION BY event_date
CLUSTER BY user_id, event_type;

Expected output: within each partition, rows sort by userid then eventtype, so filters on those columns skip blocks. Clustering is fine pruning inside partitions.

1. Decide with the query pattern, not the data shape:

Always filter by date range -> partition by date.
Filter by user_id without dates -> cluster by user_id (partitioning
by user_id would create millions of tiny partitions, which is worse).
Filter by both -> partition by date, cluster by user_id.

Expected output: the right layout for the access pattern. The mistake is partitioning by a high-cardinality column like user_id.

1. Check that pruning actually happens with a dry run:

bq query --dry_run --use_legacy_sql=false \
  'SELECT count(*) FROM proj.ds.events WHERE event_date >= "2026-01-01" AND user_id = 42'

Expected output: the estimated bytes processed. Compare against the full table size; good pruning shows a small fraction. If the estimate barely drops, your filters do not match the layout.

1. Know the maintenance differences:

Partitioning: 4000 partition limit per table; partition expiration
can auto-drop old data; slight metadata overhead per partition.
Clustering: automatic re-clustering on write; no partition limits;
works on top of partitioning or standalone.

Expected output: operational awareness. Time-unit partitioning with expiration is the standard for event tables with retention policies.

Variant phrasings

bigquery partition by vs cluster by

Partition prunes chunks, cluster sorts within chunks (steps 1-2). They compose; the question is rarely either/or.

bigquery clustering not reducing bytes scanned

The query filters do not match the cluster columns, or the table is too small for pruning to matter. Check with a dry run (step 4).

bigquery partition by ingestion time

PARTITION BY _PARTITIONDATE (or the newer partition by ingestion pseudo-column) when the event time is unreliable or missing. Useful for raw landing tables.

Why it happens

BigQuery is columnar and charges by bytes scanned. Partitioning lets the engine skip whole storage partitions; clustering colocates related rows so block-level metadata skips the rest. Without either, every query scans everything, which is correct but expensive.

Edge cases

• Over-partitioning (millions of tiny partitions) hurts: metadata overhead and the 4000-partition cap. Prefer clustering for high-cardinality keys.
• Clustering degrades if data arrives unsorted and is never rewritten; periodic rewrites restore it.
• Partition filters are required by default on partitioned tables (require_partition_filter=true) to prevent accidental full scans; keep it on.
• Streaming inserts buffer before they are partitioned; freshly streamed rows may not prune until flushed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9wA6IvkUfX5ESZUVzGvPEA