What partition types does a BigQuery table support, and how is each declared?

## What a partition is in BigQuery A partitioned BigQuery table is one logical table that the service splits into physical segments called partitions. Each partition holds the rows whose partitioning value falls in one bucket, and BigQuery tracks each partition's boundaries in table metadata. When a query carries a predicate on the partitioning column, the planner can decide *before running anything* which partitions can possibly contain matching rows and read only those. Under on-demand pricing you are billed for bytes read, so the partitions BigQuery skips are partitions you do not pay for. That is the whole point. The partitioning specification is chosen once, in the `CREATE TABLE` statement, and it cannot be altered afterwards — changing it means creating a new table and rewriting the data (typically `CREATE TABLE ... PARTITION BY ... AS SELECT * FROM old_table`). ## Type 1 — ingestion-time partitioning BigQuery assigns each row to a partition based on the time the row was ingested, not on anything in the row itself. The assignment is exposed by two pseudo-columns: `_PARTITIONTIME`, a TIMESTAMP truncated to the partition boundary in UTC, and `_PARTITIONDATE`, the DATE equivalent. You declare it as: ```sql CREATE TABLE ds.raw_logs (payload STRING) PARTITION BY _PARTITIONDATE; ``` Hourly, monthly and yearly ingestion-time granularity is available too, e.g. `PARTITION BY TIMESTAMP_TRUNC(_PARTITIONTIME, HOUR)`. Queries prune by filtering the pseudo-column: `WHERE _PARTITIONDATE = '2026-08-01'`. Ingestion-time partitioning suits raw landing tables where arrival time is the only time you trust, and it is what you get with append-only log dumps. Its weakness is that arrival time and event time diverge: a late-arriving event for yesterday lands in today's partition, so a business query on event date has to scan more than one partition. ## Type 2 — time-unit column partitioning You nominate a DATE, TIMESTAMP or DATETIME column that already exists in the row, and a granularity of hour, day, month or year: ```sql CREATE TABLE ds.events (event_ts TIMESTAMP, user_id STRING, amount NUMERIC) PARTITION BY DATE(event_ts); ``` For a DATE column the expression can be the column itself (`PARTITION BY event_date`) or a truncation (`PARTITION BY DATE_TRUNC(event_date, MONTH)`). For TIMESTAMP/DATETIME you use `DATE()`, `TIMESTAMP_TRUNC()` or `DATETIME_TRUNC()` with the granularity you want. This is the default recommendation: analysts filter on the business timestamp, so pruning happens without anyone thinking about it, and a late-arriving row still lands in the partition it logically belongs to. Rows whose partitioning column is NULL are collected in a dedicated `__NULL__` partition. ## Type 3 — integer range partitioning You bucket an INT64 column into fixed-width ranges using `RANGE_BUCKET` over a `GENERATE_ARRAY(start, end, interval)`: ```sql CREATE TABLE ds.customers (customer_id INT64, name STRING) PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000)); ``` Rows whose value falls outside `[start, end)` go to the `__UNPARTITIONED__` partition. This type is much rarer in practice. It fits a table whose dominant filter is a bounded numeric key — a customer or tenant id space you control — and it is a poor fit for an unbounded, growing id space, because the range is fixed at creation time and ids beyond `end` all pile into one place. ## Choosing between them Ask what the dominant `WHERE` clause looks like. If it filters on an event timestamp, partition by that column. If the table is a raw dump where nothing in the payload is a trustworthy time, ingestion time is honest. If the workload is keyed on a bounded integer, integer range can work. Only one axis is available per table, so the second-most-important filter column is not a partitioning candidate at all — that is what `CLUSTER BY` is for, and combining a date partition with clustering on the high-cardinality filter columns is the standard production layout. Two table options are worth setting alongside the partitioning: `partition_expiration_days`, which drops partitions automatically once they age out (a free, metadata-only delete), and `require_partition_filter = TRUE`, which rejects queries that do not filter on the partitioning column, so nobody accidentally scans five years of history.