Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Partitioning and Clustering

Partition Types and Expiration

Compare BigQuery's three partition types, choose daily, monthly or hourly granularity, and set partition expiration to drop old data automatically.

BigQuery offers three ways to partition a table: by a time-unit column, by ingestion time, or by an integer range. They differ in where the partition value comes from and in what the pseudo-columns and limits look like. Picking the right one, and the right granularity, decides how well pruning works and whether you stay under the partition limit.

This lesson covers each type, shows the matching DDL (read-only, because the BigQuery service cannot run here), and models the bucket arithmetic in SQLite so you can run it. It ends with partition expiration, the simplest way to keep storage from growing forever.

Time-unit column partitioning

This is the type you will use most. A column of your own, of type DATE, TIMESTAMP or DATETIME, decides the partition. Granularity can be hourly, daily, monthly or yearly:

SQL
-- Daily (the default for a DATE column)
PARTITION BY order_date

-- Daily from a timestamp
PARTITION BY DATE(created_at)

-- Hourly: good for high-volume logs queried by hour
PARTITION BY TIMESTAMP_TRUNC(created_at, HOUR)

-- Monthly: avoids the 10,000-partition limit on long histories
PARTITION BY DATE_TRUNC(order_date, MONTH)

-- Yearly
PARTITION BY DATE_TRUNC(order_date, YEAR)

The data decides the partition, so late-arriving rows land in the correct old partition. A row whose partition column is NULL goes to a special __NULL__ partition, and a row with a value outside the allowed range (before 0001-01-01 or after 9999-12-31, which is rare) goes to __UNPARTITIONED__.

Choose granularity from how you query and how much data you have. Daily partitions are right when a day holds at least a few hundred megabytes. If a day is tiny, monthly partitions avoid thousands of near-empty segments. If one day is enormous and queries ask about single hours, hourly partitions help, but remember 10,000 hourly partitions is only about 14 months.

Ingestion-time partitioning

Here the partition is the moment BigQuery received the row. You do not need a date column at all. The table exposes two pseudo-columns, _PARTITIONTIME (a timestamp) and _PARTITIONDATE (a date):

SQL
CREATE TABLE shop.raw_logs (
  payload STRING
)
PARTITION BY _PARTITIONDATE;

SELECT COUNT(*)
FROM shop.raw_logs
WHERE _PARTITIONDATE = '2026-03-02';

Ingestion-time tables are handy for logs with no reliable event date. Their drawback is that the partition reflects arrival, not when the thing happened: a delayed batch lands in today's partition. When your data has a trustworthy event date, prefer a column partition so queries by event date prune correctly.

Integer-range partitioning

Partition by an INT64 column using RANGE_BUCKET, giving a start, an end and an interval:

SQL
CREATE TABLE shop.customers (
  customer_id INT64,
  name        STRING
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000));

This makes 100 partitions of 10,000 IDs each. Values below the start or at or above the end go to the __UNPARTITIONED__ partition. Range partitioning suits data with a natural numeric key such as customer, tenant or store IDs, as long as queries filter on ranges or equality of that key.

Modelling the bucket arithmetic

The runnable example below reproduces the three partition functions with SQLite math: daily from a timestamp, monthly truncation, and integer buckets.

SQL Which partition does each row land in?

Press Run (or Ctrl + Enter) to see the result here.

The integer division gives the bucket start (SQLite divides integers exactly). BigQuery's RANGE_BUCKET returns a bucket number instead, but the grouping is the same.

Comparing the types

Pick a partition type

Time-unit column Your own DATE or TIMESTAMP
Ingestion time When the row arrived
Integer range Buckets of an INT64 key

Prefer a column you actually filter by.

1 / 2

Partition expiration

Old partitions often lose value. Set an expiration and BigQuery deletes each partition automatically once its date is older than the limit:

SQL
ALTER TABLE shop.events
SET OPTIONS (partition_expiration_days = 90);

The age is measured against the partition's own date (UTC), not against when you ran the statement. With partition_expiration_days = 90, the partition for 2026-03-02 is removed after 2026-05-31. A table default expiration applies to every partition; you can also set a dataset-wide default so new tables inherit it. Expired partitions are deleted without a query charge, and the storage stops billing.

SQL Which partitions would expire?

Press Run (or Ctrl + Enter) to see the result here.

Use expiration for logs and staging data. Do not set it on tables of record, because deletion is permanent apart from time travel (usually seven days) that can recover a recent deletion.

Common mistakes

  • Using ingestion-time partitioning and then filtering by an event date column: pruning cannot help because the partition column is a different field.
  • Hourly partitions on a multi-year table, which exceed 10,000 partitions.
  • Forgetting that partition expiration is permanent for data past the time-travel window.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which partition type uses pseudo-columns _PARTITIONDATE and _PARTITIONTIME?

    Ingestion-time tables expose the arrival time through those pseudo-columns.
  2. 2. A table needs ten years of history and each day is small. Which granularity is the best fit?

    Daily partitions for ten years would exceed 10,000 partitions; monthly stays well below the limit.
  3. 3. What does RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000)) create?

    From 0 to 1,000,000 in steps of 10,000 gives 100 buckets.
  4. 4. What happens to a row whose partition column is NULL in a time-unit column table?

    BigQuery keeps such rows in a special NULL partition.

Summary

  • Time-unit column partitioning (hourly, daily, monthly, yearly) is the usual choice when rows have an event date.
  • Ingestion-time partitioning uses arrival time through _PARTITIONTIME and _PARTITIONDATE.
  • Integer-range partitioning uses RANGE_BUCKET over an INT64 key.
  • Choose granularity so you stay far below 10,000 partitions.
  • partition_expiration_days deletes old partitions automatically, measured by each partition's own date.

Next: Clustered Tables