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

```text
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:

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

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

```sql
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 user_id then event_type, so filters on those columns skip blocks. Clustering is fine pruning inside partitions.

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

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

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

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

5. Know the maintenance differences:

```text
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