This post explains how BigQuery table partitioning works, how partition pruning affects query cost, when partitioning saves money (and when it does not), and how to pick a partition column that matches how your workloads actually query the data.

Most BigQuery cost guides mention partitioning in passing. Google’s documentation covers the mechanics well. What teams still struggle with is when partitioning actually pays off on small tables, the metadata overhead usually is not worth it. On large tables, a partitioned table that nobody filters correctly scans the same bytes as an unpartitioned one. Granularity and column choice matter too, but those tradeoffs are easier to reason about once the table is big enough that pruning can move the needle. The examples below focus on dollars and bytes scanned, not just DDL syntax.

What is BigQuery table partitioning?

A partitioned table splits one logical table into physical segments called partitions. BigQuery stores each partition separately and tracks metadata about which partition holds which values.

When a query includes a qualifying filter on the partition column, BigQuery can prune partitions: it reads only the segments that match the filter and skips the rest. Pruned partitions are excluded from the bytes-processed calculation that drives on-demand query pricing.

On capacity-based pricing, pruning still matters. Fewer bytes scanned usually means less slot time, even though you are billed per slot-hour rather than per byte.

Partitioning is a table design choice at creation time. You cannot flip an existing non-partitioned table to partitioned in place; you create a new partitioned table (often with CREATE TABLE ... AS SELECT or a load job) and cut queries over.

What are the main BigQuery partition types?

BigQuery supports three partitioning models. The choice affects both how data lands in partitions and how your queries must filter to get pruning.

Time-unit column partitioning (recommended default)

You partition on a DATE, TIMESTAMP, or DATETIME column in the table schema. BigQuery routes each row to a partition based on that column’s value. Granularity can be hourly, daily, monthly, or yearly (daily is the default for DATE columns).

This is the pattern most analytics pipelines want: event_date, created_at, order_ts, and similar columns that queries already filter on.

CREATE TABLE `my_project.analytics.events`
PARTITION BY event_date
AS
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS event_date,
  user_pseudo_id,
  event_name
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

Cost implication: Pruning works when BigQuery can evaluate the filter on the partition column at query-plan time. A literal bound like WHERE event_date >= '2026-01-01' qualifies, and so do many built-in expressions whose values are known before execution starts, such as WHERE event_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY). What blocks pruning is a filter whose value depends on another table column, a subquery, or any expression BigQuery cannot resolve until runtime.

If you need a dynamic value from elsewhere, you can sometimes rewrite the query as a short script: compute the value once in a DECLARE, then reference that variable in the main SELECT. That turns one statement into two and can add a little latency, but partition pruning often makes the job much cheaper overall.

-- Does not prune: subquery in the filter
SELECT event_date, description
FROM analytics.events
WHERE event_date = (SELECT MAX(reporting_date) FROM analytics.reports);

-- Prunes: resolve the value at plan time via a script variable
DECLARE reporting_date DATE DEFAULT (SELECT MAX(reporting_date) FROM analytics.reports);
SELECT event_date, description
FROM analytics.events
WHERE event_date = reporting_date;

Daily granularity is a sensible default for event data spanning months or years. Hourly partitions multiply partition count, and each table is limited to 10,000 partitions, so hourly partitioning caps out at roughly one year of history. Monthly or yearly partitions reduce partition count but widen each scan when you only need a few days of data.

Special partitions exist for edge cases: __NULL__ holds rows where the partition column is null; __UNPARTITIONED__ holds out-of-range values (for example dates before 1960-01-01 on time-unit tables, per Google’s docs).

Ingestion-time partitioning

BigQuery assigns rows to partitions based on when data is loaded, not a business timestamp in the row. Ingestion-time tables expose the pseudocolumns _PARTITIONTIME (TIMESTAMP) and, for daily tables, _PARTITIONDATE (DATE).

Cost implication: Queries must filter on _PARTITIONTIME or _PARTITIONDATE to prune — not on an arbitrary timestamp column in the schema unless that column is also the partition key. This model made sense when load time was the only reliable date signal. For most new pipelines, column-based partitioning on an event timestamp is easier to query and reason about.

Google recommends partitioned tables over date-sharded tables (events_20260101, events_20260102, …) because sharded tables carry more schema/metadata overhead and permission checks per table.

Integer range partitioning

You define an INTEGER column and a start, end, and interval (for example customer IDs 0–99 in buckets of 10). Filters like WHERE customer_id BETWEEN 30 AND 50 prune to the matching ranges.

Cost implication: Useful when workloads filter on numeric keys that are not dates (user IDs, shard keys, version numbers). Integer-range partitioning does not prune when the filter wraps the column in a function or an expression (for example WHERE customer_id + 1 BETWEEN 30 AND 50 scans the whole table, per Google’s querying guide). High-cardinality columns that do not align with range filters are a poor fit; clustering is often better there.

How does partition pruning reduce BigQuery query cost?

Partition pruning is BigQuery’s mechanism for skipping irrelevant partitions before the query runs. Pruned data is not counted in total_bytes_processed for on-demand billing.

Consider a simplified table: 365 daily partitions, ~1 GB of data per day, 365 GB total. Two queries against the same schema:

-- No pruning: scans all partitions
SELECT event_date, event_type, description
FROM `my_project.analytics.events`;

-- Pruning: scans ~7 partitions (last 7 days)
SELECT event_date, event_type, description
FROM `my_project.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

At on-demand pricing of $6.25/TiB in US regions, that is a ~98% reduction in bytes billed for this pattern — before any SQL tuning beyond adding the filter. Run that query on a schedule and the gap compounds quickly.

Pruning is not automatic just because the table is partitioned. The filter must:

1. Let BigQuery isolate the partition column (or a pseudocolumn for ingestion-time tables) on one side of the comparison. Mixing it with other columns prevents pruning, as in _PARTITIONTIME + field1 = ....
2. Use constant or parameterizable expressions where possible — dynamic subqueries that depend on other tables force a full scan.
3. For ingestion-time tables, keep _PARTITIONTIME or _PARTITIONDATE on the left side of comparisons when you can; Google notes that _PARTITIONTIME > TIMESTAMP_SUB(...) often prunes better than TIMESTAMP_ADD(_PARTITIONTIME, ...) > ....

You can confirm pruning before you pay: run a dry run and compare totalBytesProcessed with and without the partition filter.

Learn more about rewriting scheduled queries
from full reloads to incremental MERGE statements:
BigQuery Optimization: From Full Reload to Incremental Processing

Incremental pipelines depend on pruning on the read side and on how DML bills against the target table. INSERT into a partitioned table does not scan existing partitions on the write side; DML pricing charges only for the bytes the statement itself processes (q').

MERGE is where write-side cost bites. On a partitioned target, Google bills MERGE as q' when the statement contains only INSERT clauses, but as q' + t' when any UPDATE or DELETE clause is present. Here, t' is the total size of every target partition the statement updates, before modifications. If you only need “insert rows that are not yet in the table,” a MERGE with insert-only WHEN clauses avoids the t' charge that an unnecessary UPDATE clause would add.

Partition pruning applies to MERGE too, but you have to put the partition filter in the right place. Google documents pruning via a filtered subquery in USING, a partition predicate in each WHEN clause’s search_condition, or the partition column in the merge_condition. A WHEN NOT MATCHED BY SOURCE clause without a partition filter can force a full target scan even when other clauses are bounded.

As an example, let’s look at this simple MERGE query. Assume target.events is partitioned by DATE(event_time), and we join staging rows to the matching day:

MERGE target.events AS tgt
USING staging.delta AS src
  ON tgt.id = src.id
  AND DATE(tgt.event_time) = DATE(src.event_time)  -- matches days, but does NOT prune
WHEN MATCHED THEN
  UPDATE SET status = src.status
WHEN NOT MATCHED THEN
  INSERT (id, event_time, status)
  VALUES (src.id, src.event_time, src.status)
;

This looks bounded — surely, BigQuery only needs the days present in src? But DATE(src.event_time) isn’t known until the join actually runs, so the planner can’t statically choose partitions and falls back to scanning the entire target table.

Adding an explicit constant predicate on the target helps here:

MERGE target.events AS tgt
USING staging.delta AS src
  ON tgt.id = src.id
  AND DATE(tgt.event_time) = DATE(src.event_time)
  AND DATE(tgt.event_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)  -- this is what prunes
WHEN MATCHED THEN
  UPDATE SET status = src.status
WHEN NOT MATCHED THEN
  INSERT (id, event_time, status)
  VALUES (src.id, src.event_time, src.status)
;

The join condition is unchanged; we’ve only added a predicate comparing the target’s partition column to a constant (CURRENT_DATE()). That’s what the planner can evaluate up front, so it reads only the last 3 days instead of the whole table. The lesson: if you know the bound, state it explicitly against the partition column, don’t assume the source-side equality will prune for you. (The same applies to a WHEN NOT MATCHED BY SOURCE clause: it scans every partition unless you repeat that constant predicate in its search_condition.)

When does BigQuery table partitioning save money?

Partitioning pays off when all of the following are true:

• The table is large enough that scanning it whole is expensive (think tens of gigabytes upward, not megabytes)
• Most queries filter on a predictable column (usually time) over a subset of the data
• You can enforce or habitually write those filters in application SQL, BI tools, and scheduled jobs
• Partition granularity matches query patterns (daily filters on daily partitions, not monthly partitions for dashboards that always drill to one day)

Additional cost levers beyond pruning:

• Partition expiration deletes entire partitions after a TTL. That reduces long-term storage charges for data you no longer need, something a non-partitioned table cannot do at partition granularity.
• Selective load jobs can target a single partition (table$20260527) without rewriting the full table.

When does BigQuery partitioning not help (or make things worse)?

Partitioning is not free metadata, and it is not a substitute for query discipline.

Small tables. Google suggests that if partitioning yields roughly less than ~10 GB per partition, you may end up with many small partitions, higher metadata overhead, and little pruning benefit. A 5 GB table queried end-to-end is cheap either way; skip the operational complexity.

Queries that ignore the partition column. A partitioned fact table used for open-ended SELECT * reporting scans like an unpartitioned table. This is one of the most common “we partitioned but costs did not move” outcomes.

Wrong partition column. Partitioning on load_timestamp when every query filters on order_date gives you a partitioned table with zero pruning. The column must match actual filter patterns, not whichever date field was convenient at table creation.

Too-fine granularity at scale. Daily partitions over 27+ years approach the 10,000 partitions per table limit. Hourly partitions over a long history hit that ceiling even faster. Coarser granularity (monthly) or clustering may fit better.

Heavy cross-partition DML. Jobs that append or overwrite many partitions count toward partition modification quotas (for example up to 30,000 modifications per day on column-partitioned tables). A single job cannot modify more than 4,000 partitions. Frequent full-table rewrites on a wide partitioned table can hit these limits and force redesign.

Partitioning does not reduce storage cost by itself. You still pay for bytes stored at the same per-GB rates. Unmodified data does qualify for long-term storage pricing after 90 days, at roughly half the active rate. Partitioning makes that lever more useful: old partitions that nobody touches can age into long-term pricing independently, whereas an unpartitioned table only gets that discount when the entire table has gone 90+ days without modification. Savings also come from scanning less data per query and from expiration policies that drop old partitions entirely.

Learn more:
How to Choose the Right BigQuery Storage Pricing Model (Logical vs Physical)

Storage pricing model choice (logical vs physical) is independent of partitioning, but both affect total BigQuery spend.

How do you choose the right partition column?

Use query history, not schema convenience alone.

1. List candidate columns: typically date/timestamp fields and high-level integer keys used in WHERE clauses.
2. Check filter frequency: from INFORMATION_SCHEMA.JOBS or your query audit logs, see which columns appear in filters on queries that touch the table.
3. Match granularity to the narrowest common filter: if 90% of queries filter to the last 7–30 days, daily partitions are appropriate. If most queries need a single day, daily (not monthly) partitions minimize scanned bytes.
4. Prefer the business event time over load time unless ingestion time is the only trustworthy signal.
5. Enable require_partition_filter on large production tables so ad hoc SELECT * without a date predicate fails fast instead of scanning the full history. You can set this at creation:

CREATE TABLE `my_project.analytics.events`
(
  event_date DATE,
  user_id STRING,
  event_name STRING
)
PARTITION BY event_date
OPTIONS (require_partition_filter = TRUE);

Queries must then include a predicate that BigQuery can use for partition elimination, such as WHERE event_date = '2026-05-01' or a bounded range.

What is the difference between BigQuery partitioning and clustering?

Partitioning and clustering solve related but different problems.

Google’s guidance: consider clustering instead of partitioning when cardinality is very high, you filter on many columns, partitions would be tiny (< ~10 GB), or you would exceed partition count limits. In practice, daily partition on event_date plus clustering on user_id or product_id is a common pattern for large fact tables: the partition filter drops days you do not need; clustering reduces bytes read within each day.

Partitioning vs clustering is not either/or for most large analytics tables: it is partition first for time scope, cluster second for access paths inside that scope.

How can you tell if partition pruning is working?

Three practical checks:

1. Dry run comparison

-- Replace with your table
SELECT COUNT(*) FROM `my_project.analytics.events`;
-- Note totalBytesProcessed from dry run

SELECT COUNT(*)
FROM `my_project.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);
-- Compare totalBytesProcessed

If the filtered query reports nearly the same bytes as the full scan, pruning is not happening. Inspect the filter shape against the pruning rules.

2. Job statistics after execution

For recent jobs, inspect total_bytes_processed in INFORMATION_SCHEMA.JOBS_BY_PROJECT (or organization-level views). Compare scheduled job versions before and after adding partition filters.

3. Execution plan (advanced)

For stubborn cases, run the query and inspect the Execution details tab in the console (or the queryPlan field from the Jobs API). Look for table-scan stages that list partition filters in the WHERE clause. Dynamic SQL from BI tools is a frequent culprit: the tool emits a partition column in the SELECT list but not in the WHERE clause.

If you use ingestion-time partitioning, confirm filters target _PARTITIONDATE or _PARTITIONTIME, not only a business-date column.

How does Rabbit help with partitioning decisions?

Choosing a partition column from gut feel is how teams end up with partitioned tables that never prune. The manual path is to audit INFORMATION_SCHEMA job history, identify tables with high bytes scanned, check whether queries filter on the partition key, and refactor SQL or table DDL where they do not.

Rabbit analyzes query patterns against table metadata and surfaces table-level recommendations for partitioning and clustering — including cases where tables are unpartitioned or queries omit partition filters (PARTITION_NOT_USED-style patterns in the product). Recommendations are review-first: your team decides what to apply. Rabbit does not alter table definitions without your workflow.

For query-side waste, Rabbit also flags SQL antipatterns (such as SELECT * on wide tables) that inflate scans even when partitions exist. Table layout and query shape interact: partitioning only compounds savings when the SQL actually uses it.

Learn more:
Comparing BigQuery Pricing Models: On-demand vs Capacity-based Reservations

On-demand pricing makes pruning savings show up directly in bytes billed. On reservations, the same pruning reduces slot pressure. Both levers matter for total BigQuery spend.

Partitioning is one of the highest-leverage table design choices for BigQuery cost, but only when the partition column, granularity, and query filters align. Start with daily column-based partitions on the timestamp your workloads already filter on, validate pruning with dry runs, and combine with clustering when queries need finer filtering inside each partition.

Sign up to try Rabbit free to see which tables and queries in your project are scanning more data than they need, or book a demo to walk through partitioning and clustering opportunities with our team.