Which partitioned tables have no partition expiration set?
Partitioned tables that will grow forever because nothing ages out.
Free to run — INFORMATION_SCHEMA scans no billable bytesChecked 4 Aug 2026
This recipe reads both region-scoped and dataset-scoped views, so it needs both.
-- Partitioned tables lacking partition_expiration_days.
WITH partitioned AS (
SELECT DISTINCT table_schema, table_name
FROM `my-project.my_dataset`.INFORMATION_SCHEMA.COLUMNS
WHERE is_partitioning_column = 'YES'
),
expiring AS (
SELECT DISTINCT table_schema, table_name
FROM `my-project.my_dataset`.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name = 'partition_expiration_days'
)
SELECT
p.table_schema,
p.table_name,
ROUND(s.total_logical_bytes / POW(1024, 3), 2) AS logical_gib,
s.total_partitions
FROM partitioned p
LEFT JOIN expiring e USING (table_schema, table_name)
LEFT JOIN `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE s
USING (table_schema, table_name)
WHERE e.table_name IS NULL
ORDER BY s.total_logical_bytes DESC;What it returns
- total_partitions
- How many partitions exist. A large number with no expiry is unbounded growth.
How to read it
- No expiration is often correct — it is only a finding when the data has a real retention limit that nobody encoded.
- Setting partition_expiration_days deletes data permanently on a schedule. Confirm the retention requirement with whoever owns the data before setting it.
- A table with thousands of partitions and no expiry is also a query-performance problem, not only a storage one.
Run this once, or have it run every day.
Finitizer evaluates this class of question continuously against your live BigQuery estate, tracks how each number moves between runs, and turns findings into assigned tasks. Read-only and keyless.