BigQuery Table Storage by Last Query Date

Why This Matters

Large organisations can accumulate thousands of tables that are no longer used — leftover from old pipelines, one-off analyses, or deprecated features. These tables silently accrue storage charges every month. By identifying tables that haven't been queried in weeks or months, you can archive, delete, or convert them to cheaper storage tiers.

How It Works

The query joins INFORMATION_SCHEMA.JOBS_BY_PROJECT (to find the last query time per table) with TABLE_STORAGE (for size data) and TABLE_OPTIONS (to determine logical vs physical billing model). It calculates storage cost based on active vs long-term bytes using Google's published per-GiB pricing.

SQL Query

-- Find stale tables sorted by last query date with storage cost

DECLARE lookback_days INT64 DEFAULT 30;

-- US multi-region pricing ($/GiB/month)
DECLARE logical_active_price NUMERIC DEFAULT 0.02;
DECLARE logical_longterm_price NUMERIC DEFAULT 0.01;
DECLARE physical_active_price NUMERIC DEFAULT 0.04;
DECLARE physical_longterm_price NUMERIC DEFAULT 0.02;

WITH last_access AS (
  SELECT
    ref.project_id, ref.dataset_id, ref.table_id,
    MAX(j.creation_time) AS last_query_at
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
    UNNEST(j.referenced_tables) ref
  WHERE j.state = 'DONE' AND j.job_type = 'QUERY'
    AND j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
  GROUP BY 1, 2, 3
),
storage_info AS (
  SELECT
    s.project_id,
    s.table_schema AS dataset_id,
    s.table_name AS table_id,
    IFNULL(opt.option_value, 'LOGICAL') AS model,
    IF(opt.option_value = 'PHYSICAL', s.total_physical_bytes, s.total_logical_bytes) AS size_bytes,
    IF(opt.option_value = 'PHYSICAL', s.active_physical_bytes, s.active_logical_bytes) AS active_bytes,
    IF(opt.option_value = 'PHYSICAL', s.long_term_physical_bytes, s.long_term_logical_bytes) AS lt_bytes
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE s
...

Query Explanation

The query cross-references two data sources: job metadata (for last query time per table) and table storage metadata (for size and billing model). Tables that were never queried in the lookback window appear first (NULLS FIRST). Storage cost is calculated using active vs long-term bytes with the appropriate pricing for logical or physical billing model.

Key Insights

• lightbulb

  Tables at the top of the list (never queried or queried long ago) are prime candidates for deletion or archival.

• lightbulb

  Large tables that haven't been queried may still be referenced by other systems — verify before deleting.

• lightbulb

  Tables older than 90 days automatically get long-term pricing (50% cheaper), but deleting unused tables saves 100%.

• lightbulb

  Physical billing model tables cost 2x per GiB but benefit from compression — check if your tables are highly compressible.

Best Practices

1. 1

   Set up a monthly review of tables not queried in the past 30 days.

2. 2

   Implement table expiration policies (default_table_expiration_ms on datasets) for temporary data.

3. 3

   Archive important but unused tables to Google Cloud Storage (cheaper than BigQuery storage).

4. 4

   Switch datasets with high compression ratios to physical billing model for up to 70% storage savings.

Related Guides