--- name: bigquery-cost-by-user-and-query description: "Every Monday: BigQuery bytes billed and estimated on-demand cost for the last 7 days, with the users and repeated queries behind the biggest share. Use when the user asks for this, or when it runs on a schedule." --- # BigQuery cost by user and query Every Monday: BigQuery bytes billed and estimated on-demand cost for the last 7 days, with the users and repeated queries behind the biggest share. On-demand BigQuery bills by bytes scanned, so one `select *` on an unpartitioned events table can cost more than a day of dashboards. The billing console shows the total for the project; it does not say that most of it came from one analyst's notebook or a BI tile with no date filter. The agent reads the `INFORMATION_SCHEMA.JOBS` view for the last 7 days, turns bytes billed into dollars at the on-demand rate, and ranks users and repeated queries by cost. The top line usually has an obvious fix: a partition filter, a clustered column or a cached result. ## Two ways to get the data Use the API or MCP route when you have an MCP server, a CLI or an API token for the app. Otherwise, if you can drive a browser the user is signed in to (Claude in Chrome, a Playwright or Chrome DevTools MCP server), use the browser route. Say which one you used. ## Route A: API or MCP server ### What you need - A role with `bigquery.jobs.listAll` on the project, such as BigQuery Resource Viewer, to read everyone's jobs; `bigquery.jobs.create` to run the query. - Access through the `bq` CLI (`bq query --use_legacy_sql=false`), the BigQuery API, or a BigQuery MCP server. If something is missing, say what and stop; do not invent numbers. ### Steps 1. **Sum bytes billed for the week** (BigQuery): Query `` `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT `` (use your region) where `creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)`, `job_type = 'QUERY'` and `statement_type != 'SCRIPT'` (script parents would double count their children). Sum `total_bytes_billed` and divide by 1024^4 for TiB. 2. **Turn it into money**: Multiply TiB by the on-demand rate, $6.25 per TiB in the US as of October 2026 (first TiB each month free). Jobs with a `reservation_id` run on capacity pricing; report their `total_slot_ms` separately instead of bytes. 3. **Rank users** (BigQuery): Group the same rows by `user_email` (service accounts included) for bytes, cost and query count, top 10. 4. **Rank repeated queries** (BigQuery): Group by `query_info.query_hashes.normalized_literals`, which treats queries that differ only in literal values as one; keep a sample `query`, the run count, the user and the cost, top 10. Note `cache_hit` share for BI service accounts. 5. **Report**: Total TiB and cost against the 7 days before, the top users and queries, and for the most expensive query one concrete fix (partition filter, clustering, fewer columns, a materialized table). ## Route B: the browser ### What you need - Signed in to the Google Cloud console with a role that includes `bigquery.jobs.listAll` on the project (BigQuery Resource Viewer or Admin) to see everyone's jobs, and `bigquery.jobs.create` to run the query. If something is missing, say what and stop; do not invent numbers. ### Steps Start at https://console.cloud.google.com/bigquery. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears. 1. **Open BigQuery Studio on the project**: The console asks for a project if none is selected; pick the one whose jobs you pay for. Click **SQL query** (the plus button) to get an empty editor tab. The **Job history** panel at the bottom only lists recent jobs with a few columns, so the report comes from a query instead. 2. **Sum the week**: Paste and run a query on `` `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT `` (your region) with `creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)`, `job_type = 'QUERY'` and `statement_type != 'SCRIPT'`, summing `total_bytes_billed` per week. Divide by 1024^4 for TiB and multiply by $6.25 for the on-demand estimate. The editor shows the bytes it will scan before you run. 3. **Rank users and repeated queries**: Run the same rows grouped by `user_email` (top 10), then grouped by `query_info.query_hashes.normalized_literals` with `ANY_VALUE(query)`, run count, user and bytes (top 10). Note the share of `cache_hit` for BI service accounts. Jobs with a `reservation_id` go in a separate line by `total_slot_ms`. 4. **Keep the results**: Results show in pages below the editor; use **Save results** > CSV or Google Sheets if you want them kept, and sort the user table by the cost column before reading it off. 5. **Report**: Total TiB and cost for the last 7 days against the 7 before, the top users and repeated queries, slot hours for reservation jobs, and one concrete fix for the most expensive query. Cross-check the total in **Billing** > **Reports** filtered to the BigQuery Analysis SKU if the numbers look off. Note: The INFORMATION_SCHEMA.JOBS views keep 180 days and are free to query, but the query runs under your account, so it appears in next week's job history. The results panel shows up to a few hundred rows per page; the top 10 tables fit, the raw jobs do not. The on-demand rate differs by region; the first TiB each month is free and is not deducted here. ## The report - **Bytes billed** in the last 7 days and the estimated on-demand cost, with the change. - **By user**: TiB, cost and query count. - **By repeated query**: a sample, runs, user and cost. - **Capacity jobs**: slot hours, if you use reservations. - One fix for the most expensive query. ## Post it to Briefkin When `BRIEFKIN_TOKEN` is set, or `~/.config/briefkin/env` exists (the installer from https://briefkin.dev/docs writes it), also send the result to the user's Briefkin board as one card. Keep the `item_key` the same on every run so the card updates in place, use a new `event_id` per run, and put today's real numbers in place of the example ones. If the request fails, say so once and carry on. ```bash [ -n "${BRIEFKIN_TOKEN:-}" ] || . ~/.config/briefkin/env curl -sS -X POST "${BRIEFKIN_URL:-https://briefkin.dev}/v1/events" \ -H "authorization: Bearer $BRIEFKIN_TOKEN" \ -H "content-type: application/json" \ -d @- <<'JSON' { "v": 1, "event_id": "bigquery-cost-by-user-and-query/", "item_key": "bigquery-cost-by-user-and-query", "project": "acme-analytics", "source": "BigQuery cost by user and query skill", "kind": "metric", "title": "$186 of BigQuery queries this week, up 13%", "metric": { "label": "Estimated on-demand cost, last 7 days", "value": 186.38, "prefix": "$", "better": "lower", "period_start": "", "period_end": "" }, "glance": [ { "type": "Grid", "props": { "columns": 3 }, "children": [ { "type": "Stat", "props": { "label": "Billed", "value": 29.82, "unit": "TiB" } }, { "type": "Stat", "props": { "label": "Queries", "value": 18460 } }, { "type": "Stat", "props": { "label": "Top user", "value": "dbt-prod" } } ] } ], "widget": [ { "type": "Table", "props": { "columns": [ "User", "TiB", "Cost", "Queries" ], "align": [ "left", "right", "right", "right" ], "rows": [ [ "dbt-prod@acme-analytics.iam", 12.4, "$77.50", 1204 ], [ "looker@acme-analytics.iam", 8.1, "$50.63", 15890 ], [ "ana@example.com", 4.95, "$30.94", 212 ], [ "ben@example.com", 2.61, "$16.31", 388 ], [ "Everyone else", 1.76, "$11.00", 766 ] ] } }, { "type": "Table", "props": { "columns": [ "Repeated query", "Runs", "Cost" ], "align": [ "left", "right", "right" ], "rows": [ [ "select * from analytics.events where user_id = ...", 26, "$24.38" ], [ "select date, count(*) from analytics.sessions group by 1", 2410, "$19.06" ], [ "merge analytics.fct_orders using staging.orders ...", 168, "$14.75" ] ] } }, { "type": "Callout", "props": { "text": "The events lookup scans the whole 150 GiB table each time. Adding a filter on event_date, the partition column, would cut it to a few GiB per run.", "tone": "warn" } } ], "size": "md" } JSON ``` Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report. Set `period_start` and `period_end` to the window the number covers: the tile names it from them ("Last 7 days", "Today", "This month"). For a count right now (open items, a balance), send `"period_label": ""` so no period shows; for another name, send it as `period_label` (at most 24 characters). ## Run it on a schedule Suggested: Mondays at 8:00 (`0 8 * * 1`). From cron, with the agent signed in on that machine: ```bash 0 8 * * 1 claude -p "Run the bigquery-cost-by-user-and-query skill" # Codex: codex exec, Gemini CLI: gemini -p, Copilot CLI: copilot -p, OpenCode: opencode run ``` Or use the agent's own scheduler (Claude Code routines, Codex automations, Claude or ChatGPT scheduled tasks): https://briefkin.dev/skills/bigquery-cost-by-user-and-query#schedule Source: https://briefkin.dev/skills/bigquery-cost-by-user-and-query