How to Create and Query Ingestion-Time Partitioned Tables in Google BigQuery

Scanning a full table in BigQuery when you only need yesterday's data is the most common way teams inflate their query bills. Ingestion-time partitioned tables solve this by automatically organizing rows into daily partitions based on when they arrived, so a query that filters on the partition date reads only the relevant slices instead of everything.

This guide walks through creating such a table, loading data, verifying partition pruning actually works, and the limits you need to design around.

What ingestion-time partitioning gives you

BigQuery offers several partitioning strategies: by a date/timestamp column in your data, by integer range, or by ingestion time. Ingestion-time partitioning is the right choice when your data has no reliable event-time column, or when arrival time is what you actually query by (log pipelines and streaming event capture are typical cases).

Each day of incoming data lands in its own partition. You address partitions through the pseudo column _PARTITIONTIME, a TIMESTAMP that exists on every row but is not part of your schema. Filtering on it lets BigQuery's query engine skip partitions entirely — this is called partition pruning, and it directly reduces bytes scanned, which is what on-demand pricing charges for.

Prerequisites

• A Google Cloud project with BigQuery enabled and billing configured.
• An existing dataset to hold the table (bq mk --dataset PROJECT:DATASET if needed).
• Permissions: bigquery.tables.create on the dataset, plus bigquery.jobs.create to run queries. The predefined roles/bigquery.dataEditor and roles/bigquery.jobUser roles cover this.
• The bq CLI (bundled with the Google Cloud SDK) or access to the BigQuery console SQL editor.

Creating the table

You can create the table with DDL in the console SQL editor, or with the bq CLI.

Option A: DDL

CREATE TABLE mydataset.events (
  event_id STRING,
  payload JSON,
  severity STRING
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS (
  partition_expiration_days = 90,
  description = 'Ingestion-time partitioned event log'
);

The partition_expiration_days option is worth setting deliberately: it automatically deletes partitions older than the given number of days, which controls storage cost for append-only log data. Omit it if you need indefinite retention.

Option B: bq CLI

bq mk --table \
  --time_partitioning_type=DAY \
  --time_partitioning_expiration=7776000 \
  mydataset.events \
  event_id:STRING,payload:JSON,severity:STRING

The expiration flag takes seconds (7,776,000 seconds = 90 days). Run this from any machine with the Cloud SDK authenticated; no special shell environment is required beyond gcloud auth.

Optional: add clustering

If you frequently filter on a high-cardinality column like severity within a date range, add clustering at creation time:

CREATE TABLE mydataset.events (...)
PARTITION BY DATE(_PARTITIONTIME)
CLUSTER BY severity;

Clustering sorts data within each partition, letting BigQuery skip blocks inside a partition as well. It complements partitioning rather than replacing it.

Loading and querying data

No special syntax is needed on the write path. Batch loads, INSERT DML statements, and streaming inserts all work; rows are assigned to partitions by arrival time automatically. For high-volume ingestion, prefer batch load jobs over streaming — streaming into partitioned tables carries higher per-row latency and is subject to a per-project streaming throughput limit (documented at 1 GB/s per project; check current quotas for your region).

To take advantage of pruning, always filter on _PARTITIONTIME:

SELECT severity, COUNT(*) AS n
FROM mydataset.events
WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2026-10-01') AND TIMESTAMP('2026-10-14')
GROUP BY severity;

A query without this filter scans the whole table, exactly as if partitioning did not exist. Partitioning only saves money when queries are written to exploit it.

Verifying the setup

Three checks confirm everything works as intended:

1. Schema check. Run bq show --format=prettyjson mydataset.events and confirm the timePartitioning block shows "type": "DAY" and your expected expirationMs.
2. Partition listing. After loading data, run SELECT DISTINCT DATE(_PARTITIONTIME) FROM mydataset.events LIMIT 10; to see which daily partitions actually exist.
3. Pruning check. In the console, run your filtered query and open the job details. Compare "Bytes processed" against the table's total logical size — it should be roughly proportional to the number of days in your filter, not the full table. This is the check that matters for cost.

Limits and design constraints

• Partition count cap. A table supports up to 4,000 partitions. Daily ingestion-time partitioning gives you roughly a decade of runway, but if you backfill historical data with synthetic ingestion dates or combine partitioning with long retention, you can hit the limit and writes will fail.
• No repartitioning in place. Changing the partitioning scheme (for example, moving from ingestion-time to column-based partitioning) requires creating a new table and copying data over. Plan the scheme before loading terabytes; the copy costs both time and query/storage charges.
• UTC boundaries. Partitions are cut on UTC day boundaries. If your business reporting runs on a local timezone, a "yesterday" filter on _PARTITIONTIME may not align with your local day. Column-based partitioning on a local-time date column may be a better fit in that case.
• Streaming caveats. Data in the streaming buffer is not yet assigned to a partition; queries using _PARTITIONTIME IS NULL or unfiltered scans pick it up, but strict partition filters can temporarily miss freshly streamed rows.

Recovery options

Creating the table is low-risk and reversible: bq rm mydataset.events drops it. The riskier operation is loading data into the wrong scheme. If you discover the partitioning choice was wrong after loading, create a correctly partitioned table and copy with CREATE TABLE new_table PARTITION BY ... AS SELECT * FROM old_table, verify row counts match, then drop the original. Keep the old table until the new one passes your verification queries — the copy is a full scan and will be billed as such.

Summary

Ingestion-time partitioning is a one-line addition at table creation that pays off every time a query filters on _PARTITIONTIME. Create the table with a partition expiration appropriate to your retention needs, prefer batch loads for volume, and always confirm pruning via bytes-processed in job statistics before assuming you are saving money.