Weekly BigQuery cost review with Slack digest and Linear tickets

Every Monday at 8am on a cron trigger, audit last week's BigQuery spend, post a ranked cost summary to my data engineering Slack channel, and turn the worst offenders into tickets in Linear.

Start by pulling the query history from BigQuery with the Run Query (Synchronous) operation. Query INFORMATION_SCHEMA.JOBS_BY_PROJECT for the trailing 7 days, selecting at minimum total_bytes_billed, user_email, the query text, job_id, creation_time and job state. The view must be region qualified, for example `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, and the job has to run in the same location as the data, so expose the region as an obvious setting at the top of the workflow with region-us as the default. Filter to completed query jobs and exclude failed jobs and cache hits so the cost figures reflect real billed usage.

Run the same query again for the prior 7 day window, meaning days 8 through 14, so you have a baseline to compare against.

If the audit query is slow or times out, fall back to Insert Job (Async Query, Load, Extract, Copy), poll Get Job until status.state is DONE, and then read the results.

Numeric fields such as total_bytes_billed come back as JSON strings, so parse them before doing any arithmetic. Convert bytes billed into dollars using a clearly labelled and easily editable constant for the on demand price per TiB, defaulting to 6.25 dollars, calculated as bytes divided by 2^40 multiplied by that price.

Group near identical queries into patterns. This is the judgement step that matters most: literal query text differs from run to run by dates, IDs and other parameters, so normalize before ranking. Strip or placeholder out date literals, numeric IDs, quoted string values and whitespace differences, then treat every query sharing the same normalized shape as one pattern. For each pattern, total the weekly cost, count the runs, and collect the distinct user_email values that ran it.

Rank the patterns by total weekly cost and compare each against the prior week's cost for the same normalized pattern to get a week over week change. Flag sharp increases as spikes, and treat any pattern with no prior week match as new.

Post the summary to my data engineering Slack channel using the Send a Message operation. Open with total weekly spend and how it compares with the prior week, then list the top 10 costliest patterns. For each one show the weekly cost in dollars, the run count, who ran it, the week over week change as a percentage or as new, and a short readable snippet of the query. Keep it skimmable rather than dumping raw SQL.

For any pattern whose weekly cost is above a configurable threshold, defaulting to 50 dollars per week and defined as an obvious editable setting near the price per TiB, file it as work in Linear. Before creating anything, call Search Issues to check whether that pattern already has a ticket. Search on a stable identifier for the pattern, such as a distinctive normalized query fragment or the main table name, combined with a consistent title prefix like BigQuery cost:. Only when there is no match should you call Create Issue.

That dedupe check is the important nuance. A recurring expensive query must not open a fresh ticket every single week. When a matching ticket already exists, leave it alone and note in the Slack summary that the pattern is already being tracked, along with the existing ticket reference.

Each new ticket should include the query, its weekly cost, the run count, who runs it, and a concrete optimization suggestion based on what that query actually does. Examples include adding a partition or cluster filter, replacing SELECT * with the named columns actually used, materializing a repeated subquery into a table, or narrowing the scanned date range. Use a consistent title format so later runs can reliably find the ticket again.

If the week's history comes back empty or the audit query fails outright, post a short Slack note saying the cost review could not run and why, rather than failing silently or posting an empty summary.