Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Partitioning in BigQuery

Snowflake, BigQuery & Databricks · BigQuery

Partitioning in BigQuery

Easywarehouses-23
bigquerypartitioningrequire-partition-filterpruning

Question

What partitioning options does BigQuery offer, and what are the limits?

Solution

BigQuery lets you partition a table by a time column, by ingestion time, or by an integer range. Queries that filter on the partition column read only the matching partitions, which lowers both cost and time.

Three kinds

  • Time-unit column partitioning: on a DATE, TIMESTAMP or DATETIME column, with hourly, daily, monthly or yearly granularity. The usual choice, for example partition events by event_date.
  • Ingestion-time partitioning: BigQuery assigns the partition by load time, and exposes it as the pseudo-column _PARTITIONTIME. Useful when the data has no reliable date.
  • Integer-range partitioning: on an integer column with a start, end and interval, for example a customer_id bucket.
CREATE TABLE analytics.events
PARTITION BY DATE(event_ts)
OPTIONS (require_partition_filter = TRUE, partition_expiration_days = 400)
AS SELECT * FROM staging.events;

Useful options

  • require_partition_filter: a query without a filter on the partition column is rejected. This protects people from scanning the whole table by accident.
  • partition_expiration_days: old partitions are deleted automatically, which controls storage and supports retention rules.

The limits

A table can have at most 10,000 partitions. Daily partitioning gives about 27 years of days, so it is fine, but hourly partitioning over a year is around 8,760 and can hit the cap if you keep several years. In that case use daily. Very small partitions (a few MB each) are also wasteful, and clustering often serves better there.

Getting pruning to work

The filter must be on the partition column directly, compared with a constant or a value BigQuery can evaluate before running the query. Wrapping the column in functions, or filtering through a join to another table, may prevent pruning (a join filter can still prune in some cases at runtime, but do not rely on it). Check the "bytes processed" estimate in the dry run, or the job details, to confirm.

Also note that the number of partitions modified per day by load jobs and DML has quotas. Plan the write pattern, and prefer partition-aligned batch loads.

PreviousNext