Partitioning and Clustering, Explained With One Marketing Table

Your marketing events table is 3 TB. Your "last week's ROAS by campaign" query scans all 3 TB to answer a question about 40 GB. It runs for two minutes, costs $19, and will run again tomorrow, and the day after, forever.

Partitioning and clustering stop exactly this. They are not performance magic — they are layout instructions telling BigQuery which parts of the table to skip. One marketing table teaches both, because every marketing query filters on date first and something else second.

Partitioning: pruning by date before scanning anything

Partitioning splits a table into independent segments — almost always by day — so a query with a date filter opens only the segments it needs. Everything else is never read, never billed, never waited for. Without partitioning, every query is a full scan by definition, no matter how narrow its WHERE clause.

For marketing data the partition column is nearly always the event or report date, because every marketing question is a date-range question: last 7 days, this month, quarter to date. Partition by the column your WHERE clause names most — event_date on events, spend_date on spend, report_date on rollups. One line of DDL changes the cost profile of every future query:

-- Partition by the date column every query filters on.
-- BigQuery stores each day separately and prunes the rest at plan time.
CREATE TABLE analytics.marketing_events (
  event_date  DATE,
  event_name  STRING,
  campaign_id STRING,
  channel     STRING,
  user_id     STRING,
  revenue     NUMERIC,
  cost        NUMERIC
)
PARTITION BY event_date
OPTIONS (
  require_partition_filter = TRUE
);

require_partition_filter = TRUE deserves emphasis: BigQuery rejects any query that forgets the date filter, with a clear error instead of a full-table bill. The cheapest insurance in the warehouse — it belongs on every large marketing table from day one.

The cost math is linear. At on-demand rates each terabyte scanned costs roughly $6.25: a 3 TB dashboard query costs ~$19 per refresh, partitioned to 7 days it scans ~26 GB at ~$0.16. Run hourly and the unpartitioned version costs ~$13,600 a month against ~$115 partitioned. Same question, same answer, two orders of magnitude apart — decided by layout.

Clustering: sorting within a partition for cheaper filters

Partitioning answers the date question. But the query also filters on campaign_id = 'X' or channel = 'meta' — and one day's partition can still hold 50 GB. Clustering sorts rows inside each partition by up to four columns, so BigQuery skips blocks that cannot contain matching rows.

Think of a partition as a drawer labelled with a date, clustering as the dividers inside it. Without dividers you read every folder; with them you open only the M section. The DDL adds one clause:

-- Cluster on the columns your WHERE and JOIN clauses name after the date:
-- channel, campaign, event type. Order matters: most-filtered first.
CREATE TABLE analytics.marketing_events (
  event_date  DATE,
  event_name  STRING,
  campaign_id STRING,
  channel     STRING,
  user_id     STRING,
  revenue     NUMERIC,
  cost        NUMERIC
)
PARTITION BY event_date
CLUSTER BY channel, campaign_id, event_name
OPTIONS (
  require_partition_filter = TRUE
);

Three rules for cluster columns. First, cluster on what you filter and join on, not what you group by — clustering accelerates row selection, not aggregation. For marketing tables that is usually channel, campaign_id, event_name, in filter-frequency order. Second, low-to-medium cardinality first: channel with a dozen values prunes better leading than user_id with millions. Third, two or three columns in practice — each extra column dilutes the earlier ones, and BigQuery re-sorts on write so there is no maintenance, only the initial choice.

What defeats partition pruning (and how to avoid it)

Partitioning works only if BigQuery can see at plan time which partitions your filter needs. Several innocent patterns hide that information and silently re-enable the full scan. Each has a trivial fix.

Functions on the partition column. WHERE DATE_TRUNC(event_date, MONTH) = DATE '2026-08-01' forces evaluation on every partition — worst case, BigQuery gives up and reads all of them. Compare raw ranges instead: WHERE event_date >= DATE '2026-08-01' AND event_date < DATE '2026-09-01'. Same rows, prunable predicate.

Filtering on a different date than the partition key. Partitioned by event_date but filtering on created_at? The partitioner sees no constraint it understands and scans everything. The WHERE clause must name the partition column directly. When ingestion and event time differ, partition by the one you query — usually event time.

Joins that hide the filter. A filter on a dimension table's date does not prune the fact table. With events e JOIN campaigns c ... WHERE c.launch_date > X, the events side still scans fully. Push an explicit event_date predicate onto every large table in the query, even when a join feels constraining. To the pruner, it is not.

Wildcard and suffix mistakes on sharded tables. On GA4-style date-sharded tables, wrapping _TABLE_SUFFIX in date functions — or forgetting the suffix filter — reopens full history. Keep a plain string comparison: _TABLE_SUFFIX BETWEEN '20260801' AND '20260807'. It prunes before any data is touched.

Before/after: bytes scanned on the same query

The same question — last week's Meta spend and revenue by campaign — against the same 3 TB table, before and after layout. The question is fixed; only organisation and filter discipline change.

The table is the whole argument: layout is a ~1000x cost variable that changes no answers. It stays cheap permanently — every future analyst, dashboard, and AI-generated query inherits the pruning, provided the filter stays required and cluster columns match how people filter.

Frequently asked questions

Should I partition by ingestion time or event time?

Event time — the date queries filter on. Ingestion-time partitioning answers nobody's question: marketers ask about the day the click happened, not the day the row arrived. Handle late-arriving data with a two-day reprocessing window, not arrival-time partitions.

How many cluster columns should I use?

Two or three, ordered by filter frequency, lower cardinality first. CLUSTER BY channel, campaign_id, event_name covers nearly all marketing filter patterns. A fourth column rarely adds pruning and weakens the first three. Never lead with a unique id — it sorts beautifully and prunes nothing.

Does clustering help joins?

Yes, when the join key is a cluster column. Joining spend to events on campaign_id reads far less of the events side if clustered by it, because matching rows sit in adjacent blocks. Campaign and channel belong in the cluster key partly because they are join keys across marketing tables.

What about small tables — should I partition everything?

No. Below ~1 GB, partitioning adds metadata overhead without meaningful pruning — scanning 200 MB costs a fraction of a cent. Partition the events, spend, and orders tables dominating your bytes scanned; leave dimensions and lookups alone.

Can I partition and cluster an existing table?

Yes, by rewriting, not altering — partitioning cannot be added in place on a populated table. Create the new partitioned table with the desired keys, copy data with SELECT, verify counts and sample aggregates, then swap dashboards. Over a terabyte, copy one month at a time to stay small and resumable.

The summary

• An unpartitioned marketing table makes every query a full scan: a 7-day question reads 800 days and bills for all of it.
• Partitioning by the date queries filter on skips whole days before reading anything — typically the first 100x saving.
• Clustering by channel, campaign, and event name sorts rows within each partition so secondary filters skip most of what the date range kept — typically another 3–10x.
• Pruning dies when the filter wraps the partition column in a function, names a different date, lives only on a joined table, or is forgotten — require_partition_filter makes forgetting impossible.
• The same query went from ~3 TB and ~$19 per run to ~3 GB and ~$0.02, with an identical answer — layout is cost.