BigQuery cost console with team budgets and query fixes

Build me an interactive BigQuery spend console that I open repeatedly through the month, not a weekly report that lands in Slack. I own our warehouse bill, and what I need mid month is to see who is about to blow through their budget and then do something about it from the same screen. Nothing in this app runs on a schedule or on an event; a person opens it.

The landing view is month to date cost. Show total bytes billed and estimated dollars for the current month, then break that out three ways: by team, by individual user, and by the labels we tag jobs with. Everything here comes from Run Query (Synchronous) in Google BigQuery against the region qualified job history view, for example `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, summing total_bytes_billed and grouping by user_email, by the labels array, and by whatever field carries team. Estimated dollars is total_bytes_billed converted to TiB multiplied by an on demand price per TiB I can set in app settings, defaulting to 6.25 dollars. Let me choose the project with List Projects and remember my choice, and let me map users to teams in settings when the label is missing.

Each team is drawn as a progress bar against a monthly budget that finance keeps in a Google Sheet. Read it with Get Values in Google Sheets from a spreadsheet ID and range I configure, expecting a team name column and a monthly dollar budget column. Show spend against budget, and because it is mid month, also show pace: if a team is 40 percent through the month but 80 percent through its budget, that bar should be visually loud. Teams listed in the sheet with no spend still appear, and spend from a team that is not in the sheet lands in an unbudgeted bucket rather than silently disappearing.

Underneath the budget view, two ranked tables, both built with Run Query (Synchronous) over the same job history. First, the twenty most expensive individual queries this month: bytes billed, estimated dollars, who ran it, when it ran, and how long it took. Second, the tables that got scanned the most: bytes scanned per table, total dollars, and a repeat count, so a cheap query firing every ten minutes sits next to one giant nightly scan and I can see which one actually costs more. Group repeated queries by a normalized hash of the SQL text so a scheduled query rolls up into one row with a run count instead of twenty near identical rows.

Clicking a row in the expensive queries table opens a detail card showing the full SQL, who ran it, how many times it repeated this month, and the total spend across all of those runs. The card has an Optimize button that kicks off a background agent. The agent reads the SQL, works out which tables it touches, calls Get Table in BigQuery on each one to inspect partitioning, clustering, row count and size, and proposes a rewrite: adding a partition filter, selecting fewer columns, replacing a full scan with a clustered lookup, whatever the SQL and the table metadata actually justify. It then validates the saving by submitting the rewritten SQL through Insert Job with dryRun set to true, which returns the bytes that would be processed without running the query or billing for it. The agent writes its result back into the app so the card reports real numbers: original bytes, rewritten bytes, the dollar difference at our price per TiB, the proposed SQL, and a plain explanation of what it changed and why. If the dry run errors or the rewrite saves nothing, it says so instead of inventing a win. Show the card as pending while the agent works, fill in the recommendation when it lands, and keep past recommendations on the card so I can see what was already suggested.

From that same card I want a button that posts the finding to our platform channel using Send a Message in Slack Bot: who owns the query, what it costs per month, the proposed change, and the before and after bytes. Let me pick the channel and remember the last one I used.

There is also a live jobs panel. Use List Jobs in BigQuery to show what is running right now with the user, elapsed time and bytes processed so far, refreshing while the panel is open, and Get Job to pull full detail on a single job. If something is running away, a Cancel Job button kills it, behind a confirmation step that shows the job's user and query text so I do not stop the wrong thing.

Two traps to bake in, because they will bite otherwise. First, queries against INFORMATION_SCHEMA are themselves billed, so every job history query must filter on creation_time to a bounded window, this month for the rollups and the last few days for the rankings, and the day's rollup must be cached in app storage and reused rather than re-scanned on every page load. Give me a Refresh button and show when the cached numbers were last computed, so recomputing is my choice rather than something that happens every time I open a tab. The live jobs panel and the dry runs are cheap and can stay live. Second, List Jobs returns only the caller's own jobs by default; org wide attribution needs allUsers set to true plus project owner rights. When we cannot see other people's jobs, say so plainly in a banner explaining that these are only my own queries and what access is needed, rather than quietly showing a small number that reads like low spend.