Skip to content
Finitizer
To investigate

BigQuery repeated scans: queries that run every day

A query shape that runs daily or more, such as a dashboard or a scheduled report, bills its bytes every time.

Together these shapes are often most of an on-demand bill.

Run this check on your own account

Run read-only queries in your own console and paste the results. The report is worked out in your browser, with no access to your account.

What the check looks at

The heaviest query shapes over 30 days, from INFORMATION_SCHEMA.JOBS: runs, bytes billed, slot time and distinct users. The shape is a hash; no query text leaves your project.

Why it costs money

  • On-demand bills the bytes each run reads, so a query that runs 24 times a day bills 24 times.
  • The cache serves an identical query for free only if the text and the tables are unchanged, which is rare for tables that load all day.

How the tool measures it

To investigate. The report shows what this costs now and where to look. It claims no saving, because the results cannot prove one.

The report groups shapes that ran at least once a day on average, and shows what they bill a month at list price, or their slot-hours on a reservation. It claims no saving: a dashboard may need that data.

When the report raises it. Query 2 is provided and repeated shapes bill at least $10 a month, or at least 5% of slot time on a reservation.

In the sample report

Review 5 query shapes that ran daily or more

  • These shapes billed about $5,603 a month at list price before the free allowance, 69% of your on-demand bytes.

$5,603 a month now. No saving is claimed.

From the tool's own on-demand sample: made-up results for one project over 30 days, at US multi-region list prices. The SQL above is for the US multi-region at project scope; the tool writes it for any location.

What to do

  1. 1Copy the lookup query for the top shape from the tool page and read its text in your own console.
  2. 2Check whether a partition filter prunes, whether it selects only the columns it needs, and whether a rollup or an earlier result could serve it.
  3. 3Materialized views, BI Engine and incremental tables are options where your edition allows them.

How to tell it worked. Dry run the query before and after a change, then compare total_bytes_billed on real runs.

Trade-offs. Rewrites need testing. Materialized views and incremental tables add storage and upkeep.

The read-only query behind it

The same SQL the tool gives you. Each reads usage views and changes nothing.

Query 2: the heaviest query shapes
-- Finitizer BigQuery Cost Check, query 2 of 3: the heaviest query shapes.
-- A shape is BigQuery's hash of a query with literals and comments removed, so
-- the same report run with different dates is one shape. Returns the hash, never
-- the query text, and a COUNT of users, never who they are.
-- Lists the top 50 by on-demand bytes and the top 50 by slot time (up to 100
-- rows), with totals for the whole workload, so coverage can be measured.
-- Window: the last 30 complete UTC days. Run it unchanged.
WITH runs AS (
  SELECT
    query_info.query_hashes.normalized_literals                AS pattern,
    reservation_id,
    user_email,
    total_bytes_billed,
    total_bytes_processed,
    total_slot_ms
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY)
    AND creation_time < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
    AND job_type = 'QUERY'
    AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
),
totals AS (
  SELECT
    COUNT(DISTINCT pattern)                                    AS shapes_total,
    COUNT(*)                                                   AS all_runs,
    IFNULL(SUM(IF(reservation_id IS NULL, total_bytes_billed, 0)), 0)
                                                               AS all_on_demand_bytes_billed,
    IFNULL(SUM(total_slot_ms), 0)                              AS all_slot_ms,
    COUNTIF(pattern IS NULL)                                   AS unhashed_runs,
    IFNULL(SUM(IF(pattern IS NULL AND reservation_id IS NULL, total_bytes_billed, 0)), 0)
                                                               AS unhashed_on_demand_bytes_billed,
    IFNULL(SUM(IF(pattern IS NULL, total_slot_ms, 0)), 0)      AS unhashed_slot_ms
  FROM runs
),
shapes AS (
  SELECT
    pattern,
    COUNT(*)                                                   AS runs,
    COUNTIF(reservation_id IS NOT NULL)                        AS reserved_runs,
    IFNULL(SUM(total_bytes_billed), 0)                         AS bytes_billed,
    IFNULL(SUM(IF(reservation_id IS NULL, total_bytes_billed, 0)), 0)
                                                               AS on_demand_bytes_billed,
    IFNULL(SUM(total_bytes_processed), 0)                      AS bytes_processed,
    IFNULL(SUM(total_slot_ms), 0)                              AS slot_ms,
    IFNULL(SUM(IF(reservation_id IS NOT NULL, total_slot_ms, 0)), 0)
                                                               AS reserved_slot_ms,
    COUNT(DISTINCT user_email)                                 AS users
  FROM runs
  WHERE pattern IS NOT NULL
  GROUP BY pattern
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY on_demand_bytes_billed DESC, pattern) AS rank_bytes,
    ROW_NUMBER() OVER (ORDER BY slot_ms DESC, pattern)         AS rank_slots
  FROM shapes
)
SELECT
  r.pattern,
  r.runs,
  r.reserved_runs,
  r.bytes_billed,
  r.on_demand_bytes_billed,
  r.bytes_processed,
  r.slot_ms,
  r.reserved_slot_ms,
  r.users,
  t.shapes_total,
  t.all_runs,
  t.all_on_demand_bytes_billed,
  t.all_slot_ms,
  t.unhashed_runs,
  t.unhashed_on_demand_bytes_billed,
  t.unhashed_slot_ms,
  COUNT(*) OVER ()                                             AS result_rows,
  TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY) AS window_start,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)                     AS window_end,
  'us'                                                AS location,
  'project'                                             AS scope,
  '2026-11-07'                                         AS query_version
FROM ranked r
CROSS JOIN totals t
WHERE r.rank_bytes <= 50 OR r.rank_slots <= 50
ORDER BY r.on_demand_bytes_billed DESC, r.slot_ms DESC;

Other checks in the report

All checks

What BigQuery charges in each location: BigQuery pricing in 2026.

Run this check every day, not once.

Finitizer reads your BigQuery job history and storage directly, raises these findings as they appear, and tracks each fix on the bill.

See how Finitizer works