BigQuery cost audit: 4 INFORMATION_SCHEMA queries that find waste

The GCP invoice tells you what BigQuery cost last month, not which table, user or query drove it. A BigQuery cost audit answers that with plain SQL on INFORMATION_SCHEMA, reading metadata only.

What we keep finding on customer projects:

• A modest query can become expensive through repetition. Dashboards, scheduled queries and internal tools can rerun it all day.

• Physical storage can cost more than logical, even with great compression. A nightly CREATE OR REPLACE keeps up to 14 days of history, and physical billing charges for all of it.

• Fluid scaling changed the on-demand vs slots math. Per-second billing with no one-minute minimum removes the scale-down billing floor, so scan-heavy, low-CPU work now often runs cheaper on slots. One customer saved about €4,300 a month by moving two workloads and keeping a third on-demand.

• Many ad hoc backup copies duplicate time travel. The default recovery window is 7 days, and bq cp can restore a table even after a DROP. Longer retention and independent backups still need a separate plan.

Below: one INFORMATION_SCHEMA query per finding, and how to act on the result.

Before you run the queries

• Project and region: replace <your-project> with the project that runs the jobs or owns the tables. All queries use the EU multi-region (region-eu) and EU list prices in USD; change both if your data lives elsewhere.

• Several projects: organization views need project-aware grouping, not just a view-name substitution. For checks 2 and 4, use TABLE_STORAGE_BY_ORGANIZATION and include project_id in the results and dataset grouping. For check 3, use JOBS_BY_ORGANIZATION and include destination_table.project_id in table-based workload keys; include execution project_id in user-based keys if projects should stay separate. Check 1 needs JOBS_BY_PROJECT, because the organization view does not expose query text.

• Cost: these queries read metadata only and usually cost cents to a few dollars. Under on-demand pricing, INFORMATION_SCHEMA queries have a 10 MB billing minimum; under capacity pricing, they consume slots.

• Storage views: TABLE_STORAGE is off by default on new projects, and checks 2 and 4 fail with instructions until you enable it (per project, or with ALTER ORGANIZATION for all of them). History takes about a day to backfill.

ALTER PROJECT `<your-project>`
SET OPTIONS (`region-eu.enable_info_schema_storage` = TRUE)

ALTER PROJECT `<your-project>`
SET OPTIONS (`region-eu.enable_info_schema_storage` = TRUE)

ALTER PROJECT `<your-project>`
SET OPTIONS (`region-eu.enable_info_schema_storage` = TRUE)

• Forecast vs invoice: TABLE_STORAGE is a current snapshot, good for forecasting. To reconcile with the invoice, TABLE_STORAGE_USAGE_TIMELINE gives daily billable usage per table, a few days behind; the billing export remains the reference.

EU multi-region list prices used in this article, from the BigQuery pricing page as of September 2026:

1. Repeated queries

Start with the jobs that run repeatedly:

• A Looker or Looker Studio dashboard with auto refresh every 5 minutes, left open on an office screen. Refreshes can trigger repeated BigQuery jobs when they are not served by the BI tool’s cache.

• A scheduled query running every 15 minutes on data that changes once a day.

• An internal tool that queries BigQuery on every page load.

This query groups identical SQL run at least 20 times in the past week. It counts each multi-statement script once and applies on-demand list prices, including to jobs run on slots.

-- Most expensive repeated queries over the last 7 days
SELECT
  LEFT(query, 150) AS query_start,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  COUNTIF(cache_hit) AS cache_hits,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS on_demand_usd,
  ROUND(SUM(total_slot_ms) / 3.6e6, 1) AS slot_hours
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND parent_job_id IS NULL
GROUP BY query, query_start
HAVING COUNT(*) >= 20
ORDER BY tib_billed DESC
LIMIT 50

-- Most expensive repeated queries over the last 7 days
SELECT
  LEFT(query, 150) AS query_start,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  COUNTIF(cache_hit) AS cache_hits,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS on_demand_usd,
  ROUND(SUM(total_slot_ms) / 3.6e6, 1) AS slot_hours
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND parent_job_id IS NULL
GROUP BY query, query_start
HAVING COUNT(*) >= 20
ORDER BY tib_billed DESC
LIMIT 50

-- Most expensive repeated queries over the last 7 days
SELECT
  LEFT(query, 150) AS query_start,
  ANY_VALUE(user_email) AS example_user,
  COUNT(*) AS runs,
  COUNTIF(cache_hit) AS cache_hits,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS on_demand_usd,
  ROUND(SUM(total_slot_ms) / 3.6e6, 1) AS slot_hours
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND parent_job_id IS NULL
GROUP BY query, query_start
HAVING COUNT(*) >= 20
ORDER BY tib_billed DESC
LIMIT 50

Observed output, anonymized (29 October 2026). The 7-day check returned 45 SQL groups. The largest by billed bytes was:

Frequent does not automatically mean expensive. This result alone does not justify a materialization project. The dollar column is a list-price byte equivalent, not allocated reservation spend; check slot consumption separately for jobs running on capacity.

How to read it:

• Sort by the total: a cheap query run 2,000 times matters more than an expensive one run once.

• Same result every time: materialize it once (scheduled table or materialized view) and point the dashboard at it.

• cache_hits near zero: CURRENT_TIMESTAMP() or CURRENT_DATE() in the SQL, or a source table written between runs, disables the cache.

• Large scans on a partitioned table: check whether the filter enables partition pruning and whether the requested date range is necessary. ALTER TABLE ... SET OPTIONS (require_partition_filter = TRUE) rejects queries without a filter usable for partition elimination, but does not cap how many partitions a valid filter reads. A per-user daily query quota caps what anyone can scan, and maximum_bytes_billed caps a single query.

• Several rows with the same query_start: inspect the full SQL before combining them. Queries differing only in literals may belong to the same workload.

2. Logical vs physical storage

BigQuery always stores data compressed. The dataset’s billing model only decides which bytes you pay for:

• Logical (the default) bills the uncompressed size. Time travel and fail-safe are included.

• Physical bills the compressed size at roughly 2.2× the price per GiB. Time travel and fail-safe are billed on top, at the active physical rate.

Without history overhead, the listed rates put the compression break-even at about 2.2×. Repeated values often compress well, but actual compression and retained history matter more than the column types. Compare the forecast costs below. The trap is churn. When a MERGE, UPDATE, DELETE or CREATE OR REPLACE changes or removes data, affected old blocks are retained for the time-travel window (7 days by default, configurable down to 2), then 7 days of fail-safe. Logical billing includes that history; physical billing charges it. The query measures it as churn_ratio: time-travel plus fail-safe bytes divided by active physical bytes.

-- Logical vs physical forecast per dataset (EU multi-region list prices)
WITH t AS (
  SELECT
    table_schema AS dataset,
    IF(NOT deleted, active_logical_bytes, 0) / POW(1024, 3) AS active_logical_gib,
    IF(NOT deleted, long_term_logical_bytes, 0) / POW(1024, 3) AS long_term_logical_gib,
    (active_physical_bytes - time_travel_physical_bytes) / POW(1024, 3) AS active_physical_gib,
    long_term_physical_bytes / POW(1024, 3) AS long_term_physical_gib,
    (time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 3) AS history_gib
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
  WHERE table_type = 'BASE TABLE'
)
SELECT
  dataset,
  ROUND(SUM(active_logical_gib * 0.02 + long_term_logical_gib * 0.01)) AS logical_usd_month,
  ROUND(SUM(active_physical_gib * 0.044 + long_term_physical_gib * 0.022
            + history_gib * 0.044)) AS physical_usd_month,
  ROUND(SAFE_DIVIDE(SUM(active_logical_gib + long_term_logical_gib),
                    SUM(active_physical_gib + long_term_physical_gib)), 1) AS compression_ratio,
  ROUND(SAFE_DIVIDE(SUM(history_gib), SUM(active_physical_gib)), 2) AS churn_ratio
FROM t
GROUP BY dataset
ORDER BY logical_usd_month - physical_usd_month DESC

-- Logical vs physical forecast per dataset (EU multi-region list prices)
WITH t AS (
  SELECT
    table_schema AS dataset,
    IF(NOT deleted, active_logical_bytes, 0) / POW(1024, 3) AS active_logical_gib,
    IF(NOT deleted, long_term_logical_bytes, 0) / POW(1024, 3) AS long_term_logical_gib,
    (active_physical_bytes - time_travel_physical_bytes) / POW(1024, 3) AS active_physical_gib,
    long_term_physical_bytes / POW(1024, 3) AS long_term_physical_gib,
    (time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 3) AS history_gib
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
  WHERE table_type = 'BASE TABLE'
)
SELECT
  dataset,
  ROUND(SUM(active_logical_gib * 0.02 + long_term_logical_gib * 0.01)) AS logical_usd_month,
  ROUND(SUM(active_physical_gib * 0.044 + long_term_physical_gib * 0.022
            + history_gib * 0.044)) AS physical_usd_month,
  ROUND(SAFE_DIVIDE(SUM(active_logical_gib + long_term_logical_gib),
                    SUM(active_physical_gib + long_term_physical_gib)), 1) AS compression_ratio,
  ROUND(SAFE_DIVIDE(SUM(history_gib), SUM(active_physical_gib)), 2) AS churn_ratio
FROM t
GROUP BY dataset
ORDER BY logical_usd_month - physical_usd_month DESC

-- Logical vs physical forecast per dataset (EU multi-region list prices)
WITH t AS (
  SELECT
    table_schema AS dataset,
    IF(NOT deleted, active_logical_bytes, 0) / POW(1024, 3) AS active_logical_gib,
    IF(NOT deleted, long_term_logical_bytes, 0) / POW(1024, 3) AS long_term_logical_gib,
    (active_physical_bytes - time_travel_physical_bytes) / POW(1024, 3) AS active_physical_gib,
    long_term_physical_bytes / POW(1024, 3) AS long_term_physical_gib,
    (time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 3) AS history_gib
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
  WHERE table_type = 'BASE TABLE'
)
SELECT
  dataset,
  ROUND(SUM(active_logical_gib * 0.02 + long_term_logical_gib * 0.01)) AS logical_usd_month,
  ROUND(SUM(active_physical_gib * 0.044 + long_term_physical_gib * 0.022
            + history_gib * 0.044)) AS physical_usd_month,
  ROUND(SAFE_DIVIDE(SUM(active_logical_gib + long_term_logical_gib),
                    SUM(active_physical_gib + long_term_physical_gib)), 1) AS compression_ratio,
  ROUND(SAFE_DIVIDE(SUM(history_gib), SUM(active_physical_gib)), 2) AS churn_ratio
FROM t
GROUP BY dataset
ORDER BY logical_usd_month - physical_usd_month DESC

active_physical_bytes already includes time-travel bytes, so the query subtracts them to avoid counting them twice. Deleted tables contribute no logical charges, but their retained physical history still counts in the physical forecast.

Observed output, anonymized (29 October 2026). Selected rows from the 852 dataset results; monthly forecasts hold current storage usage constant, not measured savings:

Dataset A remains cheaper on physical in the forecast even though retained history is larger than current active physical storage. A churn threshold alone would have given the wrong recommendation. Neither row establishes savings from switching: first check which billing model the dataset already uses. This query covers base tables only.

How to read it:

• physical_usd_month clearly lower: switch with ALTER SCHEMA my_dataset SET OPTIONS (storage_billing_model = 'PHYSICAL');

• High churn_ratio: rewrite history can outweigh the compression gain. Use the forecast cost columns to decide; churn alone does not tell you which billing model wins.

• Churny but still worth physical: ALTER SCHEMA my_dataset SET OPTIONS (max_time_travel_hours = 48); cuts time travel to 2 days (fail-safe stays at 7). Make sure 2 days of recovery is enough.

• Append-only logs mixed with heavily mutated tables: split the dataset first. The billing model is set per dataset, not per table.

After a switch you must wait 14 days to switch back, and billing takes about 24 hours to reflect it.

3. On-demand vs slots

Decide per workload, not per project.

Slot billing now tracks real work

Earlier slot models meant paying for fixed capacity or, with autoscaling, a one-minute minimum per scale-up. Fluid scaling removes that minimum: capacity still scales in 50-slot steps, but billing follows it per second. Enable it per reservation from the admin project:

ALTER PROJECT `ADMIN_PROJECT_ID`
SET OPTIONS (`region-eu.preflight_fluid_autoscaling_reservations` = ['RESERVATION_NAME'])

ALTER PROJECT `ADMIN_PROJECT_ID`
SET OPTIONS (`region-eu.preflight_fluid_autoscaling_reservations` = ['RESERVATION_NAME'])

ALTER PROJECT `ADMIN_PROJECT_ID`
SET OPTIONS (`region-eu.preflight_fluid_autoscaling_reservations` = ['RESERVATION_NAME'])

If you settled on-demand vs slots two years ago, the answer may have changed.

Break-even: 142 slot-hours per TiB

On-demand bills bytes scanned; capacity pricing bills allocated slot capacity over time. The useful number is slot-hours per TiB. At EU list prices, a workload under about 142 slot-hours per TiB (6.25 / 0.044) is cheaper on Standard edition slots, before autoscaling overhead. On Enterprise ($0.066) the break-even drops to about 95. Slots you already pay for (baseline, commitments) are cheaper still to fill.

For example, on one customer project (savings in EUR, unlike the USD prices elsewhere):

• Data integrity checks (counts, COUNT(DISTINCT), simple aggregations over full tables) scan many TB with little CPU. Moving them to slots saved about €2,800 per month.

• Client-facing filtering jobs read the full data, but their filters shrink the input almost immediately: about €1,500 per month saved on slots.

• Complex joins and heavy aggregations burn a lot of CPU per TiB, came out more expensive on slots, and stayed on-demand.

All three shared projects and often a service account. Moving a whole project to slots would have offset some savings with higher costs for heavy joins.

Measure each workload

This first pass groups jobs by destination table, falling back to the execution identity for anonymous results. If several workloads share an identity, refine the grouping with destination tables, referenced_tables, labels or SQL patterns before deciding where to route them. A source table can also serve several workloads, so validate the groups rather than treating any single clue as definitive.

-- Compute intensity and slot estimate per workload, last 30 days, on-demand jobs only
WITH jobs AS (
  SELECT
    -- plain SELECTs write to an anonymous "_..." dataset: fall back to the user
    IF(destination_table.dataset_id IS NULL OR STARTS_WITH(destination_table.dataset_id, '_'),
       user_email,
       CONCAT(destination_table.dataset_id, '.', destination_table.table_id)) AS workload,
    total_bytes_billed,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND statement_type != 'SCRIPT' -- script parents duplicate their children and have no reservation_id
    AND reservation_id IS NULL     -- currently billed on-demand
),
per_workload AS (
  SELECT
    workload,
    COUNT(*) AS jobs,
    SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed,
    SUM(total_slot_ms) / 3.6e6 AS slot_hours,
    -- Pessimistic rounding allowance: one extra 50-slot block per running job
    SUM(total_slot_ms + 50 * duration_ms) / 3.6e6 AS slot_hours_with_overhead
  FROM jobs
  GROUP BY workload
)
SELECT
  workload,
  jobs,
  ROUND(tib_billed, 2) AS tib_billed,
  ROUND(SAFE_DIVIDE(slot_hours, tib_billed)) AS slot_hours_per_tib,
  ROUND(tib_billed * 6.25) AS on_demand_usd,
  ROUND(slot_hours_with_overhead * 0.044) AS standard_slots_estimate_usd
FROM per_workload
WHERE tib_billed * 6.25 > 10
ORDER BY on_demand_usd - standard_slots_estimate_usd DESC

-- Compute intensity and slot estimate per workload, last 30 days, on-demand jobs only
WITH jobs AS (
  SELECT
    -- plain SELECTs write to an anonymous "_..." dataset: fall back to the user
    IF(destination_table.dataset_id IS NULL OR STARTS_WITH(destination_table.dataset_id, '_'),
       user_email,
       CONCAT(destination_table.dataset_id, '.', destination_table.table_id)) AS workload,
    total_bytes_billed,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND statement_type != 'SCRIPT' -- script parents duplicate their children and have no reservation_id
    AND reservation_id IS NULL     -- currently billed on-demand
),
per_workload AS (
  SELECT
    workload,
    COUNT(*) AS jobs,
    SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed,
    SUM(total_slot_ms) / 3.6e6 AS slot_hours,
    -- Pessimistic rounding allowance: one extra 50-slot block per running job
    SUM(total_slot_ms + 50 * duration_ms) / 3.6e6 AS slot_hours_with_overhead
  FROM jobs
  GROUP BY workload
)
SELECT
  workload,
  jobs,
  ROUND(tib_billed, 2) AS tib_billed,
  ROUND(SAFE_DIVIDE(slot_hours, tib_billed)) AS slot_hours_per_tib,
  ROUND(tib_billed * 6.25) AS on_demand_usd,
  ROUND(slot_hours_with_overhead * 0.044) AS standard_slots_estimate_usd
FROM per_workload
WHERE tib_billed * 6.25 > 10
ORDER BY on_demand_usd - standard_slots_estimate_usd DESC

-- Compute intensity and slot estimate per workload, last 30 days, on-demand jobs only
WITH jobs AS (
  SELECT
    -- plain SELECTs write to an anonymous "_..." dataset: fall back to the user
    IF(destination_table.dataset_id IS NULL OR STARTS_WITH(destination_table.dataset_id, '_'),
       user_email,
       CONCAT(destination_table.dataset_id, '.', destination_table.table_id)) AS workload,
    total_bytes_billed,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms
  FROM `<your-project>.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND statement_type != 'SCRIPT' -- script parents duplicate their children and have no reservation_id
    AND reservation_id IS NULL     -- currently billed on-demand
),
per_workload AS (
  SELECT
    workload,
    COUNT(*) AS jobs,
    SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed,
    SUM(total_slot_ms) / 3.6e6 AS slot_hours,
    -- Pessimistic rounding allowance: one extra 50-slot block per running job
    SUM(total_slot_ms + 50 * duration_ms) / 3.6e6 AS slot_hours_with_overhead
  FROM jobs
  GROUP BY workload
)
SELECT
  workload,
  jobs,
  ROUND(tib_billed, 2) AS tib_billed,
  ROUND(SAFE_DIVIDE(slot_hours, tib_billed)) AS slot_hours_per_tib,
  ROUND(tib_billed * 6.25) AS on_demand_usd,
  ROUND(slot_hours_with_overhead * 0.044) AS standard_slots_estimate_usd
FROM per_workload
WHERE tib_billed * 6.25 > 10
ORDER BY on_demand_usd - standard_slots_estimate_usd DESC

Observed output, anonymized (29 October 2026). Selected rows from the 7 workload groups above the $10 filter, over the preceding 30 days:

A and B are candidates for a reservation trial; C points the other way. These are measured on-demand job statistics priced under two models, not a completed migration or realized savings. Workload names are aliases for the query’s first-pass groups.

How to read it:

• Compare the dollar columns: slot_hours_per_tib measures consumed slot time; standard_slots_estimate_usd adds a pessimistic allowance for capacity rounding. A workload just under the break-even ratio can still be cheaper on-demand.

• Worst-case rounding allowance: assume capacity follows concurrent consumption instantly, rounded up in 50-slot blocks. The query adds a full 50 slots for every second each job runs. Rounding wastes less than 50 slots for the shared reservation at any instant; charging that allowance to every concurrent job deliberately overcounts it.

• Validate before moving: this is a worst case for rounding under those assumptions, not a ceiling on the full bill. Baseline capacity, scaling delays and different execution times remain outside the model, as does the free first TiB. Validate shortlisted workloads in a trial reservation, checking billed capacity and latency.

• Small workloads hidden: WHERE tib_billed * 6.25 > 10 drops workloads under $10 of on-demand spend over the 30 days.

Why the rounding allowance is pessimistic

Illustration, not production measurements. Job A uses 30 slots from 0–20 seconds; Job B uses 40 from 10–30 seconds. During the overlap, consumption is 70 slots and ideal shared capacity rounds to 100. The query allows 170: 70 consumed plus 50 for each of the two jobs. The dashed line is the query’s allowance, not a measured autoscaler trace.

Route workloads to slots or on-demand

• Two execution projects: one assigned to the reservation, one left on-demand. Each query runs in the project that suits it.

• Assign only QUERY jobs to the reservation: load and extract jobs are free on the shared slot pool. They consume reservation slots only if the project also has a PIPELINE assignment.

• With dbt: our open-source dbt-adapter-bigquery-project-selector picks the execution project per model or tag: integrity tests and filtering models on slots, heavy joins on-demand.

Define workloads without perfect labels

Destination and referenced tables can identify useful workload groups without labels. When integrity checks and BI dashboards touch the same tables under the same service account, combine those clues with SQL patterns or job labels.

Each job carries small clues: user or service account, project, destination table, tables read, statement type and job labels (dbt can add the model name). None is enough alone. Combined, they usually are.

Costory is a FinOps platform for allocating cloud costs to teams and workloads. Its BigQuery jobs connector attaches job clues to billing lines; a virtual dimension applies rules to those clues to assign a workload label:

What you get:

• Allocation without a labelling project: per workload, team or product, refined as labels appear.

• Slot cost per job: GCP bills a reservation as one line, so Costory reallocates it to each job from its slot time (query_compute_ms).

• One view for both models: on-demand and slot spend per workload, with slot-hours per TiB as a tracked formula. Projects without job access show as Unlabelled, so you see where coverage is missing.

4. Quick housekeeping: forgotten backup tables

Finish with a simpler housekeeping check: tables such as orders_backup_20240312, customers_old or tmp_fix_revenue, copied “just in case” and left in storage. Their names flag candidates for review, not proof that they can be deleted:

-- Tables that look like forgotten backups or temp copies
SELECT
  table_schema AS dataset,
  table_name,
  DATE(creation_time) AS created,
  DATE(storage_last_modified_time) AS last_modified,
  ROUND(total_logical_bytes / POW(1024, 3), 1) AS logical_gib,
  ROUND((active_logical_bytes * 0.02 + long_term_logical_bytes * 0.01)
        / POW(1024, 3), 2) AS usd_month_logical
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE NOT deleted
  AND REGEXP_CONTAINS(LOWER(table_name),
        r'(backup|bkp|_bak$|_old$|_copy|_save|^tmp_|_tmp$|^temp_)')
ORDER BY total_logical_bytes DESC

-- Tables that look like forgotten backups or temp copies
SELECT
  table_schema AS dataset,
  table_name,
  DATE(creation_time) AS created,
  DATE(storage_last_modified_time) AS last_modified,
  ROUND(total_logical_bytes / POW(1024, 3), 1) AS logical_gib,
  ROUND((active_logical_bytes * 0.02 + long_term_logical_bytes * 0.01)
        / POW(1024, 3), 2) AS usd_month_logical
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE NOT deleted
  AND REGEXP_CONTAINS(LOWER(table_name),
        r'(backup|bkp|_bak$|_old$|_copy|_save|^tmp_|_tmp$|^temp_)')
ORDER BY total_logical_bytes DESC

-- Tables that look like forgotten backups or temp copies
SELECT
  table_schema AS dataset,
  table_name,
  DATE(creation_time) AS created,
  DATE(storage_last_modified_time) AS last_modified,
  ROUND(total_logical_bytes / POW(1024, 3), 1) AS logical_gib,
  ROUND((active_logical_bytes * 0.02 + long_term_logical_bytes * 0.01)
        / POW(1024, 3), 2) AS usd_month_logical
FROM `<your-project>.region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE NOT deleted
  AND REGEXP_CONTAINS(LOWER(table_name),
        r'(backup|bkp|_bak$|_old$|_copy|_save|^tmp_|_tmp$|^temp_)')
ORDER BY total_logical_bytes DESC

Observed output, anonymized (29 October 2026). The name pattern returned three candidates:

The largest candidate was written the day before the audit. A matching name does not prove abandonment. The other two have old write timestamps, but that says nothing about recent reads or retention requirements.

How to read it:

• Adjust the pattern to your naming habits.

• Date suffixes are left out on purpose: GA4 and other exports shard tables by date.

• Logical rates only: usd_month_logical is a logical-price comparison, not the actual cost or deletion saving for datasets on physical billing.

Before deleting a copy, confirm its owner and retention requirements. For short-term recovery, BigQuery retains changed or deleted table data for 7 days by default, unless the dataset uses a shorter time-travel window. Copy jobs are free within a region, though the resulting table incurs storage charges. For longer retention, keep a deliberate backup or snapshot policy.

For recovery within the time-travel window, restore into a new table with a time decorator:

# Restore orders as it was 2 hours ago, including after a DROP
bq cp

# Restore orders as it was 2 hours ago, including after a DROP
bq cp

# Restore orders as it was 2 hours ago, including after a DROP
bq cp

For a deliberate longer-lived restore point, use an expiring snapshot. It shares unchanged blocks with its base table, but retained blocks after updates, deletion or reclustering can still incur substantial storage charges. Fail-safe adds 7 days after time travel and requires Google Cloud Customer Care access; it is an emergency recovery path, not a backup policy.

Keep the audit running

These checks are a snapshot, and the results drift. A new dbt model can add nightly full refreshes to a dataset you just moved to physical. A workload that was cheap on slots can gain a heavy join and flip back.

With Costory:

See how to set this up in 5 minutes

• A dashboard shows the potential savings from these checks per workload, on the bill itself.

• Costory Scout reruns them on a schedule and flags new repeating queries or churning datasets.

• The same checks are open source as agent skills in costory-finops-mcp-skills, if you prefer running them with your own agent.

Costory is free for 30 days, then from €250 per month (billed annually) for up to €10M of annual cloud and AI spend.