Finding repeated and redundant queries in BigQuery

Most query reviews optimize the scariest-looking SQL. Most BigQuery bills are dominated by boring SQL that runs on a schedule. A 120 GB incremental model that runs every hour is about 86 TB/month, or roughly $540 at on-demand list, before any full-refresh accident. The same model run twice because two Airflow DAGs own it is $1,080, and nothing in the SQL itself looks wrong.

-- 120 GB incremental, every hour, two DAGs
SELECT *
FROM `proj.analytics.stg_events`
WHERE event_date = @run_date;
-- 24 × 30 × 120 GB ≈ 86 TB/month ≈ $540
-- Clone the DAG and you just doubled it. The SQL is unchanged.

The real unit of cost control is the query pattern over a window: same shape, many jobs, many dollars. That requires fingerprinting (stripping literals, normalizing whitespace, hashing) plus summing bytes billed. Doing this once in a notebook is easy. Doing it continuously, without storing the PII that was sitting in the query string, is why SlashPeak treats fingerprinting as infrastructure rather than a one-off script.

A messy query that ran once in a console costs little. A clean query that runs 24 times a day for 30 days is a product, and it is the one worth ranking by sum(bytes) times count rather than by how the JOIN reads. Shuffle-heavy SQL is the one exception still worth a look, since it occupies slots, but on on-demand it has no honest dollar figure.

Literals in the query are why a naive GROUP BY on raw query text fails. WHERE event_date = '2026-07-01' and the same clause with a different date are the same pattern. Fingerprints replace literals with placeholders, lowercase the text, and strip comments, which is how you see that Looker emitted the same explore 400 times with different dates.

PII in query strings is why JOBS.query should not get dumped into a shared sheet. Emails, phone numbers, and customer ids show up in WHERE clauses routinely. Hash or redact before fingerprinting for a wiki; SlashPeak masks those inside BigQuery before it reads the text, so neither the finding key nor the stored SQL is a leak.

Cache hits break naive frequency counts. A dashboard that hits cache 19 times and misses once is not 20 paid scans. Count paid, non-cache occurrences separately, since materialization savings apply to runs 2 through N of the paid series; the first run still has to execute regardless.

When the same pattern shows up dozens of times in 30 days with a large bytes total, the options are: run it less often, cache it (BI tool or materialized view), or persist an intermediate table that downstream jobs read instead of rescanning the sources.

CREATE MATERIALIZED VIEW `proj.analytics.events_daily`
PARTITION BY event_date AS
SELECT
  event_date,
  event_name,
  COUNT(*) AS events,
  COUNT(DISTINCT user_id) AS users
FROM `proj.analytics.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2;
-- Downstream jobs read events_daily instead of rescanning events.

Materialized views in BigQuery help when the pattern is an aggregation over a stable base table with a partition filter. They do not help when every run uses a different 400-line explore. Scheduled queries that rebuild the same 1 TB snapshot every four hours because 'the dashboard must be fresh' are usually a product conversation about freshness SLA, not a SQL conversation.

The volume bar should keep one-off notebooks out: a handful of runs, at least two paid, combined cost above a small monthly floor. Only reads count: a MERGE or CREATE TABLE AS SELECT already writes its result, and the result cache never serves a write. Savings are about 85% of paid runs 2 through N in the window, not extrapolated out to a year, since someone will change the dashboard next month anyway.

Materialize at the grain the consumers need. If 12 Looker explores all aggregate events to daily event_name counts, one events_daily table is the fix. If they all need different nested fields, a thin contract table still beats 12 stars. Do not materialize the 400-line explore; materialize the 8-column aggregate.

BI Engine and result cache are materialization you do not own. They expire. They miss on filter change. A scheduled table with partition expiration is an SLA you can write down. 'It was fast this morning' is not.

BigQuery does not guarantee that a CTE is computed once. WITH base AS (SELECT * FROM events WHERE event_date = CURRENT_DATE()) followed by two downstream CTEs that both read base can become two storage reads of today's events. You will not see that in the SQL. You will see it in job_stages as two READ operators against the same table.

-- Often two READ operators against events
WITH base AS (
  SELECT * FROM `proj.analytics.events`
  WHERE event_date = CURRENT_DATE()
),
a AS (SELECT user_id, COUNT(*) FROM base GROUP BY 1),
b AS (SELECT event_name, COUNT(*) FROM base GROUP BY 1)
SELECT * FROM a JOIN b USING (/* … */);

-- Force one read:
CREATE TEMP TABLE base AS
SELECT user_id, event_name
FROM `proj.analytics.events`
WHERE event_date = CURRENT_DATE();

The fix is sometimes a temp table, sometimes a materialized view, sometimes just inlining into one pass. The diagnosis has to be plan-based. SlashPeak only flags jobs that have plan data, and it uses the minimized source-table READ stages rather than the full stage tree, so the signal stays about base-table re-reads rather than shuffle noise.

BigQuery may also report 'N similar table(s) omitted' on a READ step, the same signal: the engine noticed a repeated read. Treat omitted similar reads as part of the effective N. Savings scale as (N-1)/N of billed bytes: a double scan saves about 50% in theory, a triple about 67%, if you actually collapse to one read.

CREATE TEMP TABLE base AS SELECT ... is the blunt instrument that forces one read for the rest of the script. It costs a write, which on a 4 TB CTE is cheaper than two 4 TB reads, but on a 20 MB CTE is just noise; temp-tabling every WITH clause for style is not worth it.

Inlining into one aggregation pass is better when both downstream shapes can be computed with GROUPING SETS or a single scan plus two conditional aggregates. Temp tables are better when the shapes are irreconcilable joins. EXPLAIN plus the billed plan after a rewrite tells you which you got.

Views inside CTEs multiply the confusion. A CTE that SELECT * FROM events_recent twice may be two reads of the view's underlying table, or one, depending on inlining. job_stages against the base table name is still the scoreboard.

MERGE is the standard way to upsert a partition. MERGE that matches on a business key without a partition predicate on the target is the standard way to rewrite a 3 TB table every night. UPDATE and DELETE have the same shape. The bytes billed look like a full table scan because they are one.

MERGE `proj.analytics.events` AS t
USING (
  SELECT * FROM `proj.staging.events_delta`
  WHERE event_date = @run_date          -- source is filtered
) AS s
ON t.event_id = s.event_id             -- target is not
WHEN MATCHED THEN UPDATE SET t = s
WHEN NOT MATCHED THEN INSERT ROW;
-- Target READ has no partition predicate → 3 TB rewrite.

A source-side WHERE event_date = @run_date does not protect you if the target read is unfiltered. Rule authors who only parse SQL miss this; SlashPeak inspects the target READ in the plan (inspectTableReadFilter) so a source filter cannot hide a full rewrite.

The rewrite is usually: add the partition column to the MERGE ON / UPDATE WHERE, or replace MERGE with a partition overwrite (DELETE partition + INSERT, or CREATE OR REPLACE TABLE … PARTITION). Re-scan should show the MERGE finding collapsing.

MERGE `proj.analytics.events` AS t
USING `proj.staging.events_delta` AS s
ON t.event_date = s.event_date         -- partition column
AND t.event_id = s.event_id
WHEN MATCHED THEN UPDATE SET
  t.event_name = s.event_name
WHEN NOT MATCHED THEN INSERT ROW;

-- Or skip MERGE:
-- DELETE FROM events WHERE event_date = @run_date;
-- INSERT INTO events SELECT * FROM events_delta WHERE …

Detection needs recurrence. Three runs in the window, majority looking like a wholesale rewrite: bytes ≥ ~70% of target size (and the target is the largest table in the job, so a small target joining a huge source does not misfire), or the target READ has no partition filter. Cache hits skipped. Full-scan SELECT rules skip MERGE so you do not get two tickets for one job.

dbt's incremental merge strategy hides this well. unique_key = event_id without incremental_predicates on event_date compiles to ON T.event_id = S.event_id: clean YAML, 3 TB target READ. Fix the compiled SQL first, then update the YAML so it cannot regress.

Partition overwrite is often cheaper than MERGE for 'replace this day.' DELETE WHERE event_date = @run_date + INSERT, or a CREATE TABLE ... PARTITION BY that replaces the partition. MERGE wins when you must upsert sparse keys inside many partitions. If you are replacing the whole day, stop pretending it is an upsert.

A repeated query with no owner just sits on a list with nobody to call. SlashPeak attaches the same attribution block to every query finding: principal type (human vs. .gserviceaccount.com), environment inferred from dataset name tokens (prod / staging / qa / dev), and workload source classified from job-id prefixes and SQL comment headers (dbt JSON, Looker Query Context, Dataform, Airflow, Connected Sheets, console bquxjob_).

That classification happens in TypeScript on the fetched jobs, not in BigQuery SQL, so you can change it without a warehouse deploy. The finding details keep uniqueUsers, topUsers, workloadSourceBreakdown, environmentBreakdown, and principalTypeBreakdown. The conversation in Slack becomes 'Looker on prod.events, three explores, two users' instead of 'someone should fix SQL.'

Service accounts are not 'the platform team.' dbt Cloud, Looker, Airflow, and a custom connector each have their own SA. Breakdown by email prefix / SA name is how you pick a Slack channel. Human INTERACTIVE in prod during business hours is a different channel than the SA that runs merge_events at 6am.

Environment tokens misfire on datasets named production_sandbox. That is fine. Unknown is better than pretending. The point is to stop paging staging owners for a prod Looker explore.

If attribution says console bquxjob_ and a human, do not materialize it until you know they will rerun it. Frequency plus principal type together decide whether this is a pipeline or a person.

Some of the repeated spend is not a pipeline. It is analysts running INTERACTIVE queries against prod during local business hours, colliding with the warehouse run, driving slot contention, and billing on-demand bytes that a BATCH scheduled job could have produced once.

Peak-hour ad-hoc spend does not belong in total estimated monthly waste. There is no honest dollar figure for 'please run this at 7pm.' There is a very honest operational figure: share of billed cost in business hours that is human INTERACTIVE. Combined with reservation contention and queuing, it tells you whether you have a SQL problem or a working-hours problem.

A useful bar: human INTERACTIVE is a large share (on the order of 30%+) of billed cost in the local peak window, enough jobs and enough dollars that it is not three queries. Past half, it is the main problem. Timezone matters; UTC-labeled 'business hours' on a US team is how you look at the wrong hours.

Fixes are operational: a scheduled mart at 7am they can query cheaply, BATCH for reconstructions, a sandbox project on-demand, Looker explores on a BI reservation. Do not raise the ETL baseline so a notebook is snappy.

This overlaps with repeated expensive query because the same explore might be both: a fingerprint with 400 runs, and a peak-hour spike. Materialize the explore; also stop running the unmaterialized version INTERACTIVE at 9:05. One without the other leaves half the mess.

A single scan gives you the ranked list. The value shows up when you fix a MERGE, re-scan the next day, and watch that group's estimated savings fall while a new Connected Sheets extract appears in the same report. Automatic scans every few hours (every 6 on Starter, every 3 on Growth, hourly on Pro), grouped findings, and the metrics dashboard exist for that loop. Slack and webhook alerts exist so the loop is not 'open SlashPeak when finance pings.'

The access model stays the same the whole time: metadata-only, one read-only custom IAM role, no table contents. The partition and storage work covered in the other posts runs alongside this behavioral half of the same invoice.

Fingerprints are stable enough to group across days if literals are stripped. They are not stable if someone reformats the SQL or Looker changes codegen. When a finding 'disappears' and a new twin appears, diff the query text. Often you fixed nothing and Looker shipped a comment.

Do not treat the ranked list as a backlog of 40 equals. The top MERGE and the top repeated explore are the week. A heavy shuffle on a 2 GiB job is a code-review comment, not a sprint.

Failed and cancelled jobs still consumed slots and sometimes bytes. They are operational, not savings. Time them out, fix them, but do not book their slot-ms as recovered dollars on on-demand.

Model stg_events: 120 GiB billed, hourly, 24 × 30 = 720 runs/month. 720 × 120 GiB ≈ 86.4 TiB × $6.25 ≈ $540. Two Airflow DAGs with the same fingerprint: $1,080. SQL is a simple partition-filtered SELECT. The fix is delete one DAG, or materialize once and have both read the table. You do not need a rewrite.

-- 120 GiB * 24 * 30 = 86.4 TiB * $6.25 ≈ $540
-- two DAGs: $1,080
SELECT
  120 / 1024 * 24 * 30 * 6.25 AS usd_one_dag,
  2 * 120 / 1024 * 24 * 30 * 6.25 AS usd_two_dags;

Looker explore on events, 15-minute refresh, business hours only (8 × 20 days = 160 paid misses after cache). Each miss is a 1.2 TB star: 160 × 1.2 × $6.25 = $1,200/month. Materialize events_daily (40 GB rebuild once an hour) at ~$7.50/day = $225/month and point the explore at it. Delta ~$975 plus the slot contention you stop causing at 9:05.

Nightly MERGE into a 3 TB target, unfiltered target READ, 30 runs. 30 × 3 TiB × $6.25 = $562.50 if every run is a full rewrite. Savings ratio ~70% on runs 2..N: you still need one rewrite-shaped job to apply the day. ~$380 of the window is the recoverable part, plus the 3 TB you stop turning over every night after you add event_date to ON. First night after the fix should bill a handful of partitions, not 3 TB.

SELECT
  3 * 6.25 * 30 AS usd_30_full_rewrites,     -- $562.50
  3 * 6.25 * 29 * 0.7 AS usd_recoverable;    -- runs 2..N at 70%

CTE double-read of today's 10 GiB partition, 40 times a day: each job bills ~20 GiB instead of 10. 40 × 10 GiB extra = 400 GiB/day ≈ $2.40/day. Small next to the MERGE. Fix it when you are in that SQL anyway; do not lead the sprint with it. N=2 savings is 50% of those jobs, not 50% of the invoice.

Add them up without double-counting: kill the duplicate DAG ($540), materialize the explore (about $975), partition the MERGE (about $380 or more). The CTE fix is rounding error. Peak-hour ad-hoc might be the same Looker jobs, so do not add the $1,200 twice; attribution tells you whether it is the same owner.

Pull JOBS with query, total_bytes_billed, cache_hit, statement_type, destination_table, user_email, job_id, job_stages, creation_time. Exclude the scanner.

Normalize query text (lowercase, strip comments, replace string/number literals and query parameters with a placeholder). GROUP BY that fingerprint. HAVING COUNT(*) >= 3 and SUM(bytes) large. That is the repeated list. Split cache_hit.

SELECT
  FARM_FINGERPRINT(REGEXP_REPLACE(LOWER(query), r"'[0-9-]+'", "'?'"))
    AS approx_fp,
  COUNT(*) AS runs,
  COUNTIF(cache_hit = FALSE) AS paid_runs,
  ROUND(SUM(IF(cache_hit, 0, total_bytes_billed)) / POW(1024, 4), 2)
    AS paid_tib,
  ANY_VALUE(user_email) AS sample_user
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
GROUP BY approx_fp
HAVING paid_runs >= 2
ORDER BY paid_tib DESC
LIMIT 30;

For CTE rescans, only jobs with job_stages. Count READ steps per table name. Flag table when count ≥ 2 or similar-omitted ≥ 1. Bytes ≥ 1 GiB. Group by fingerprint so 40 copies of the same script are one finding.

For MERGE/UPDATE/DELETE, require destination_table. Compare total_bytes_processed to target table size from TABLE_STORAGE. Inspect the plan READ whose table is the destination. Source filters do not count. Majority of a fingerprint's runs must look wholesale.

SELECT
  job_id,
  statement_type,
  destination_table,
  ROUND(total_bytes_billed / POW(1024, 4), 2) AS tib,
  user_email
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND statement_type IN ('MERGE', 'UPDATE', 'DELETE')
  AND cache_hit = FALSE
ORDER BY total_bytes_billed DESC
LIMIT 30;

Attribution: job_id prefixes (sheets_dataconnector_, scheduled_query_, bquxjob_), comment headers, user_email domain. Do this in the application layer if you can; SQL regex on comments is brittle but better than nothing.

Materializing a pattern that actually ran three times with different business questions is a mistake, since fingerprints can collide after aggressive literal stripping. Read the sample SQL first; if the GROUP BY grain changes between runs, it is not one mart.

Wrapping a 5 MB CTE in CREATE TEMP TABLE 'for the plan' costs more than the second read would have. CTE_RESCAN_MIN_BYTES exists so 1 GiB is the floor for a finding, not every WITH clause.

Replacing MERGE with a partition overwrite backfires when you upsert sparse keys across 30 days of partitions every hour; overwriting every touched day can be worse. MERGE with a partition predicate on the target is the actual fix in that case.

Adding event_date to ON is unsafe if event_date can disagree between source and target for the same event_id, as with late-arriving corrections. That needs a lookback window on the target (event_date >= run_date minus N) that still prunes, plus the key; incremental_predicates in dbt is that window.

Killing a duplicate DAG before confirming it is actually duplicate is risky, since two similar-looking fingerprints might be hourly vs. daily grain. Check destination_table first.

Booking peak-hour ad-hoc as compute savings overstates the win: moving a human to 7pm does not delete bytes, it may only reduce contention. Treat it as an operational fix, not a dollar line.

dbt: incremental_predicates plus a unique_key that includes or accompanies the partition column. Inspect the compiled MERGE in target/; if ON is only the business key, the YAML was misleading. full_refresh=true weekly is a MERGE_REWRITE on purpose, so schedule it BATCH and off-peak, or stop running it weekly.

-- dbt model config
-- unique_key = 'event_id'
-- incremental_predicates = [
--   "DBT_INTERNAL_DEST.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)"
-- ]
-- Compiled ON should still include event_date AND event_id.

Looker: persist_for / datagroup refresh that re-runs a 400-line explore every 15 minutes is the $1,200 example. Lengthen the datagroup, or persist a PDT from a thin aggregate. Looker Query Context comments are how attribution flags it.

Sheets: each open/refresh can be a paid job. Custom queries with parameters still fingerprint as repeats. Replace with a scheduled table. sheets_dataconnector_ job ids are the giveaway.

Airflow: two DAGs, one task copied. The fingerprint will show two user_emails or the same SA with two job labels. Labels help; fingerprints still win. Delete the clone.

Dataform and scheduled queries: same as dbt without the YAML. The partition predicate has to be in the MERGE text. A comment that says 'incremental' is not a predicate.

Day 1: rank fingerprints by paid TiB. Rank MERGEs by bytes vs target size. Note CTE double-reads only on jobs ≥ 1 GiB. Pick one repeat and one MERGE. Read attribution.

Day 2: delete or disable the duplicate DAG / lengthen the Looker datagroup. This is often a config change with no SQL. Measure paid run count tomorrow.

Day 3: add partition column to MERGE ON (or overwrite the day) in staging. Inspect target READ in the plan. Ship if today's partition billed like a partition.

Day 4: temp-table or single-pass the CTE only if it is in the same SQL you already have open. Otherwise skip. Peak-hour: move one human explore to a mart.

Days 5 through 7: re-scan. Repeat $/mo and MERGE $/mo should drop. If a new Sheets fingerprint appeared, that is the loop working. Skip a shuffle-tuning project this week unless the MERGE is done and something is still pinning slots.