BigQuery large scans: queries reading 100 GiB or more
A query shape that reads 100 GiB or more per run costs about $0.61 or more each time on US on-demand pricing.
Most of these are full scans that a partition filter or fewer columns would cut.
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, and the bytes each one reads per run.
Why it costs money
- BigQuery bills every byte of every column a query reads, whatever the LIMIT or the rows returned.
- A query on a partitioned table with no filter on the partition column reads the whole table.
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 read 100 GiB or more per run on average and ran less than daily, and shows what they bill a month. It claims no saving.
When the report raises it. Query 2 is provided and large scans bill at least $10 a month.
In the sample report
Review 1 query shape that read 100 GiB or more per run
- This shape billed about $134 a month at list price before the free allowance, 2% of your on-demand bytes.
$134 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
- 1Read the shape's text with the lookup query and check its filters and columns.
- 2Add a filter on the partition column, select only the columns needed, and check the bytes in a dry run.
How to tell it worked. Compare total_bytes_billed for the shape before and after.
Trade-offs. Some analysis really does need the whole table. Those are better on a reservation than rewritten.
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
- BigQuery on-demand vs Editions: which costs less for your queriesPriced
- BigQuery repeated scans: queries that run every dayTo investigate
- BigQuery physical storage billing: when it costs lessPriced
- BigQuery logical storage billing: when to move backPriced
- BigQuery time travel window: the cost on physical billingTo investigate
- BigQuery idle reservation capacity: slots billed, not usedTo investigate
- BigQuery storage billing model: when the report cannot tellTo investigate
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