Solutions Log

Backfilling a Specific _PARTITIONTIME Partition in BigQuery

3 min read
BigQuerysql

Preamble

Every so often a backfill job needs to land in a specific partition — not "today," not "whenever the load job runs," but a fixed historical date. This came up recently while loading a batch of client data that needed to sit in the 2026-04-01 partition of an ingestion-time-partitioned table.

On paper this sounds trivial: insert the rows, make sure the date is right. In practice, BigQuery's ingestion-time partitioning (partitioned on the pseudo-column _PARTITIONTIME rather than a real column in the schema) has a few sharp edges, particularly when it comes to clearing a partition before reloading it. This post walks through the working approach, plus a syntax trap that's easy to fall into along the way.

Issue Description

The setup: a staging table (client-data-staging-20260714) held rows that needed to be inserted into a production table (client-data-incoming) partitioned on _PARTITIONTIME, with all rows required to land specifically in the 2026-07-14 partition.

Because the table uses ingestion-time partitioning rather than a column-based scheme, there's no user-facing date column to just overwrite in a SELECT. The partition assignment is driven by the pseudo-column _PARTITIONTIME itself, which meant:

The insert needed to explicitly set _PARTITIONTIME rather than relying on default ingestion behavior. Before reloading, the existing 2026-04-01 partition needed to be cleared — and the obvious-looking approach for that ran into a wall.

The first instinct for clearing a partition was to use the $ partition decorator directly inside a SQL statement:

TRUNCATE TABLE project.dataset.table$20260401

This fails with:

Table "project.dataset.table$20260401" cannot include decorator

The decorator syntax is real, it's just not valid inside standard SQL text. It's recognized by the bq CLI and API-level job configs, not by the SQL parser.

Resolution

Clearing the partition first

DELETE FROM `project.dataset.client-data-incoming`
WHERE DATE(_PARTITIONTIME) = '2026-07-14'

Getting the ordered field list

Because _PARTITIONTIME has to be matched positionally as the first column, SELECT * isn't an option — the full column list has to be spelled out explicitly, in order, on both sides of the INSERT. Rather than typing that by hand (and risking a typo or an out-of-sync order between source and destination), pull it straight from INFORMATION_SCHEMA:

SELECT STRING_AGG(column_name, ', ' ORDER BY ordinal_position) AS cols
FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'client-data-incoming'

This returns a single comma-separated string, ordered by ordinal_position, ready to paste directly into both the INSERT INTO (...) column list and the SELECT clause below.

Two things worth checking before pasting it in:

  • Source and destination schemas should match — same column names, same types, same order. If the destination table has columns the source doesn't (or vice versa), the positional list needs to be reconciled by hand rather than pasted as-is. This includes casting types as required.
  • Re-run this against the destination table too if there's any doubt the two schemas have drifted — comparing the two lists side by side is cheap insurance against a silent column-shift bug (values landing in the wrong columns without an error).

Inserting into the target partition

Since _PARTITIONTIME can be assigned directly in an INSERT ... SELECT, columns are listed explicitly (no SELECT *, since the pseudo-column must be matched positionally):

INSERT INTO `project.dataset.client-data-incoming`
  (_PARTITIONTIME, col1, col2, col3, ...)
SELECT
  TIMESTAMP('2026-07-14'),
  col1, col2, col3, ...
FROM `project.dataset.client-data-staging-20260714`

This runs entirely server-side as a single DML job — no export, no reload, no streaming buffer to wait out.

← Solutions Log