Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 37 ยท Partitioning and Clustering

Partitioned Tables

Learn how BigQuery partitioned tables split data into segments, how partition pruning lowers the bytes a query scans, and how to create and filter them.

BigQuery bills on-demand queries by the number of bytes they read. A table with three years of events can hold terabytes, yet most questions only need last week. Partitioning divides one table into many smaller segments, called partitions, so a query that filters on the partitioning column reads only the segments it needs. The rest are never touched and never billed.

This lesson explains what a partition is, how to create a partitioned table, and how to write queries that actually benefit. The real BigQuery service cannot run inside this page, so the CREATE TABLE ... PARTITION BY statements are shown read-only, and the runnable examples use SQLite to model the same idea: a table whose rows are grouped into daily segments with a known size.

What a partition is

A partitioned table is still one table with one schema. Internally, BigQuery stores the rows of each partition separately and keeps metadata about each one: its partition value, row count and size. When a query has a filter on the partitioning column, the engine compares the filter with that metadata and skips every partition that cannot match. This is called partition pruning.

flowchart LR
  Q["Query:<br/>WHERE event_date = '2026-03-02'"] --> M["Partition metadata"]
  M -->|skip| P1["2026-03-01"]
  M -->|read| P2["2026-03-02"]
  M -->|skip| P3["2026-03-03"]
  M -->|skip| P4["2026-03-04"]

Only the partition whose value matches the filter is read; the others are pruned from the scan.

Partitioning helps in three ways: cost (fewer bytes scanned), speed (less data to process) and management (you can expire or delete a single partition without touching the rest).

Creating a partitioned table

Choose a column of type DATE, TIMESTAMP or DATETIME and name it in PARTITION BY. This statement is BigQuery GoogleSQL, shown read-only:

SQL
CREATE TABLE shop.events (
  event_id   STRING,
  user_id    STRING,
  event_name STRING,
  event_ts   TIMESTAMP
)
PARTITION BY DATE(event_ts)
OPTIONS (
  description = "Web events, one partition per day"
);

DATE(event_ts) converts each timestamp to its calendar day, so every day of data lands in its own partition. If the column is already a DATE, write PARTITION BY event_date. You can also create a partitioned table from a query result:

SQL
CREATE TABLE shop.events_2026
PARTITION BY event_date
AS
SELECT *, DATE(event_ts) AS event_date
FROM shop.raw_events
WHERE event_ts >= TIMESTAMP '2026-01-01';

Each table can have at most 10,000 partitions, and a single load or query job can write to at most 4,000 of them. Daily partitions for ten years would exceed the first limit, so very long histories use monthly granularity (next lesson: partition types and expiration).

Writing queries that prune

Pruning only happens when the filter uses the partitioning column in a way BigQuery can evaluate before reading data. Compare these filters, all read-only:

SQL
-- Prunes: direct comparison with constants
SELECT COUNT(*) FROM shop.events
WHERE DATE(event_ts) = '2026-03-02';

-- Prunes: a range of dates
SELECT COUNT(*) FROM shop.events
WHERE event_ts >= TIMESTAMP '2026-03-01'
  AND event_ts <  TIMESTAMP '2026-03-08';

-- Does NOT reliably prune: the filter depends on another table
SELECT COUNT(*) FROM shop.events e
JOIN shop.campaigns c ON e.user_id = c.user_id
WHERE DATE(e.event_ts) = c.start_date;

The third query compares the partition column with a value that comes from a join, so BigQuery cannot know at planning time which partitions it needs. Filter with constants, query parameters or simple expressions of them. A good habit is to check the dry-run estimate before running (see controlling query cost); if the estimated bytes equal the whole table, pruning did not happen.

Seeing pruning with a model

The example below models a table of three weeks of events. Each row of partitions is one daily partition with its size in megabytes. The query adds up the size of the partitions a filter would touch, which is what BigQuery bills for.

SQL Bytes read with and without a partition filter

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

Notice that the cost depends on the partitions touched, not on how many rows the query returns. A query for one day that needs two columns can still read the whole day's data for those columns.

Counting rows per partition

Partition-level statistics are available in INFORMATION_SCHEMA.PARTITIONS in BigQuery. Here is the same idea on a model table, so you can practice the GROUP BY shape you would use there.

SQL Partition sizes from raw rows

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

Notice that 2026-03-03 has no rows, so it has no partition at all: BigQuery creates partitions only when data arrives.

Requiring a partition filter

A forgotten WHERE on a huge table is an expensive mistake. Set require_partition_filter and BigQuery rejects any query that does not filter on the partition column:

SQL
ALTER TABLE shop.events
SET OPTIONS (require_partition_filter = TRUE);

You can also set it at creation time inside OPTIONS. Analysts then get an immediate, free error instead of a surprise bill.

Common mistakes

  • Partitioning a small table. A table under a few gigabytes gains little, and many tiny partitions add metadata overhead.
  • Choosing a column no query filters on. Partition on what you filter by most, usually an event date.
  • Wrapping the partition column in a function that hides it, such as WHERE CAST(event_date AS STRING) = '2026-03-02'.
  • Expecting LIMIT to cut cost. It limits rows returned, not bytes scanned.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does partition pruning do?

    Pruning compares the filter with partition metadata and reads only matching partitions, which lowers both cost and run time.
  2. 2. Which column types can a time-unit column partition use?

    Time-unit column partitioning works on DATE, TIMESTAMP and DATETIME columns.
  3. 3. Why might a query that joins on a date from another table fail to prune?

    Pruning needs a filter BigQuery can evaluate before reading data, such as constants or query parameters.
  4. 4. What does require_partition_filter do?

    Queries without a filter on the partitioning column fail, which protects against accidental full scans.

Summary

  • Partitioning splits a table into segments by a DATE, TIMESTAMP, DATETIME, ingestion time or integer column.
  • Queries that filter on the partition column read only matching partitions (pruning), lowering bytes billed.
  • A table can hold up to 10,000 partitions; a job can modify up to 4,000.
  • Filter with constants or parameters, not values from joins, so pruning can happen.
  • require_partition_filter blocks accidental full-table scans.

Next: Partition Types and Expiration