# Monitoring BigQuery jobs and costs with INFORMATION_SCHEMA.JOBS

> Ready-to-run queries on INFORMATION_SCHEMA.JOBS for cost per user, the most expensive queries, repeated queries, cache hit rate, failures, the tables that cost the most, and slot usage over time, plus the traps that double-count script jobs.

Source: https://docs.querylab.io/bigquery/monitoring-jobs-and-costs/

Updated: 2026-10-27

Every query, load, export and copy is a job, and `INFORMATION_SCHEMA.JOBS` keeps 180 days of them per project and location: who ran it, the SQL, bytes processed and billed, slot time, whether it hit the cache, and any error. It is the place to find out where the BigQuery bill comes from. Querylab.io's [Query history](https://docs.querylab.io/writing-sql/query-history/) and Usage statistics tabs show part of this without SQL: cost per job, cost by user, the ten most expensive queries and the cache-hit rate for a period (see [Seeing usage over a period](https://docs.querylab.io/writing-sql/query-history/#seeing-usage-over-a-period)). Their SQL source runs a billed `INFORMATION_SCHEMA` query of the kind below. The queries below are ready to paste; each was checked against BigQuery. They read real job history, so they aren't run on this page.

Change `region-us` to the location of your data. A region qualifier covers one location, and `region-us` doesn't include `us-central1`. See [INFORMATION_SCHEMA](https://docs.querylab.io/bigquery/information-schema/).

## Two rules before you sum anything

- **Exclude script parent jobs.** A [script](https://docs.querylab.io/bigquery/scripting/) is a parent job with `statement_type = 'SCRIPT'` whose totals include all its child jobs, which appear as rows of their own. Summing every row counts script costs twice. Filter `statement_type != 'SCRIPT'` (or `parent_job_id IS NULL` to count each script once by its parent).
- **Filter on `creation_time`.** The view is partitioned by it, so a filter limits what the query reads. Without one, a busy project's job history is a large table to scan.

A `!= 'SCRIPT'` filter drops rows where `statement_type` is NULL, because `NULL != 'SCRIPT'` is NULL and `WHERE` keeps only true. Jobs have a NULL type when they aren't statements: load and copy jobs, and queries that failed before BigQuery classified them. In 30 days of one project's history, `statement_type != 'SCRIPT'` kept 30,330 jobs, `statement_type IS DISTINCT FROM 'SCRIPT'` kept 31,146, and 1,712 were scripts. The 816 rows in between were load and copy jobs and failed queries. For a cost sum it rarely matters, since failed queries bill nothing, but a count of queries or a count of failures needs `IS DISTINCT FROM` (or `statement_type IS NULL OR statement_type != 'SCRIPT'`). The queries below use `!=` for brevity.

`total_bytes_billed` is what on-demand pricing charges for; on capacity pricing it is informational and the cost is in `total_slot_ms`.

## Cost per user, last 30 days

```sql
SELECT
  user_email,
  COUNT(*) AS queries,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS approx_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY tib_billed DESC;
```

$6.25 per TiB is the US on-demand price; the free 1 TiB a month isn't subtracted. Service accounts show up as users too, which is how you find a pipeline or a BI tool that scans too much.

## The most expensive queries, last 7 days

```sql
SELECT
  job_id,
  user_email,
  creation_time,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gib_billed,
  ROUND(total_slot_ms / 1000 / 60, 1) AS slot_minutes,
  LEFT(query, 200) AS query_start
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
ORDER BY total_bytes_billed DESC
LIMIT 20;
```

## The same query run again and again

A single expensive query is easy to spot. A dashboard tile that runs a moderately expensive query every few minutes is not. `query_info.query_hashes.normalized_literals` is a hash of the query text that ignores comments, parameter values and literals, so it groups every run of the same query shape:

```sql
SELECT
  query_info.query_hashes.normalized_literals AS query_hash,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 1) AS gib_billed,
  ANY_VALUE(LEFT(query, 200)) AS example
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
GROUP BY query_hash
ORDER BY gib_billed DESC
LIMIT 20;
```

The fix for a repeated query is usually a [materialized view](https://docs.querylab.io/bigquery/materialized-views/), a smaller summary table refreshed on a schedule, or making the query cacheable (see below).

## Cache hit rate

```sql
SELECT
  DATE(creation_time) AS day,
  COUNTIF(cache_hit) AS cached,
  COUNT(*) AS queries,
  ROUND(COUNTIF(cache_hit) / COUNT(*), 2) AS cache_hit_ratio
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
GROUP BY day
ORDER BY day;
```

A low ratio on dashboards often means the queries use `CURRENT_DATE()` or change their text on each run. See [Query cache](https://docs.querylab.io/bigquery/query-cache/).

## Which tables cost the most to read

`referenced_tables` lists the tables each query read. Attributing the query's bytes to each of its tables is approximate when a query joins several, but it shows which tables need partitioning or clustering:

```sql
SELECT
  t.project_id || '.' || t.dataset_id || '.' || t.table_id AS table_path,
  COUNT(*) AS queries,
  ROUND(SUM(j.total_bytes_billed) / POW(1024, 3), 1) AS gib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS j,
  UNNEST(j.referenced_tables) AS t
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
GROUP BY table_path
ORDER BY gib_billed DESC
LIMIT 20;
```

## Failures

```sql
SELECT
  error_result.reason,
  COUNT(*) AS failures,
  ANY_VALUE(error_result.message) AS example_message
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result IS NOT NULL
GROUP BY reason
ORDER BY failures DESC;
```

The reasons and what to do about each are in [Common errors](https://docs.querylab.io/bigquery/troubleshooting-errors/).

## Slot usage over time

`JOBS` has one row per job. `JOBS_TIMELINE` has one row per job per second, which shows how many slots were busy at each moment. Average slots per minute over the last day:

```sql
SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  ROUND(SUM(period_slot_ms) / 1000 / 60, 1) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY minute
ORDER BY minute;
```

Slot-milliseconds in a minute divided by 60,000 is the average number of slots in use. This is the number to compare with a reservation size when deciding between on-demand and capacity pricing. See [Slots](https://docs.querylab.io/bigquery/slots/) and [On-demand vs capacity pricing](https://docs.querylab.io/bigquery/on-demand-vs-capacity-pricing/).

## Which reservation a job ran on

`reservation_id` names the reservation a job used, as `admin_project:location.reservation_name`, and `edition` is the edition of that reservation. For a query that ran on-demand, both are NULL. A NULL `edition` alone doesn't prove on-demand: jobs on `default-pipeline`, the free shared pool that load jobs use, have a `reservation_id` and a NULL `edition`. Test `reservation_id IS NULL` instead.

```sql
SELECT reservation_id, COUNT(*) AS jobs, ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND statement_type IS DISTINCT FROM 'SCRIPT'
GROUP BY reservation_id
ORDER BY jobs DESC;
```

A NULL group is the on-demand jobs. To see how much of a reservation's capacity each workload used, add `WHERE reservation_id = 'admin_project:US.my_reservation'`. See [Reservations](https://docs.querylab.io/bigquery/reservations/).

## Views and permissions

| View                                     | Shows                                | Needs                                              |
| ---------------------------------------- | ------------------------------------ | -------------------------------------------------- |
| `JOBS_BY_USER`                           | your own jobs                        | `bigquery.jobs.list`                               |
| `JOBS` / `JOBS_BY_PROJECT`               | all jobs in the project              | `bigquery.jobs.listAll` (BigQuery Resource Viewer) |
| `JOBS_BY_FOLDER`, `JOBS_BY_ORGANIZATION` | all jobs in a folder or organization | organization-level permissions                     |
| `JOBS_TIMELINE...`                       | the same, per second                 | the same as its `JOBS` counterpart                 |

Each query on these views is billed at least 10 MB and isn't cached.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [JOBS view](https://cloud.google.com/bigquery/docs/information-schema-jobs)

- [JOBS_TIMELINE view](https://cloud.google.com/bigquery/docs/information-schema-jobs-timeline)

- [Estimate and control costs](https://cloud.google.com/bigquery/docs/best-practices-costs)