Starting with BigQuery as an analyst: the first-week checklist
Before your first real BigQuery queries, check which project pays and what access you have. LIMIT does not lower the cost, one partition filter can turn 2 TiB into 6 GiB, and a byte limit stops a mistake before it bills. Some SQL also behaves differently from other databases.
BigQuery bills a query by the bytes it reads, not by how long it runs or how many rows it returns. Most surprises in the first week come from that one rule. LIMIT 10 doesn’t lower the estimate, and on a table that isn’t clustered it doesn’t lower the bill either. Naming two columns instead of SELECT * can. A date filter on a partitioned table can turn a 2 TiB query into a 6 GiB one. This page is the list to work through before you query real data, in the order you’ll need it. Each step links to the full article.
If you also set up the project, read the project owner checklist as well. Getting started with BigQuery shows both lists on one page.
1. Find out which project pays
Every query runs as a job in one project, and that project pays for it. The tables can live in another project. A team usually has a project to run queries in and reads data from a separate project, so a query names the table in full and is billed to wherever it was started:
SELECT order_id, amount
FROM `acme-data.sales.orders` -- the data lives here
-- the job runs, and is billed, in the project you picked to run it
You need two different permissions for this. roles/bigquery.jobUser (or bigquery.user) on the project that runs the query, and roles/bigquery.dataViewer on the dataset you read. Neither one is enough alone. With only dataViewer, you can see the table but every query fails with User does not have bigquery.jobs.create permission in project. Ask for both, and ask which project to run queries in. See Access control and Projects, datasets and tables.
In Querylab.io, the Run on field in the status bar’s ⚙ popover picks the project a tab bills to (see Cost limits).
2. Find out where your data lives
Every dataset has one location, a region such as europe-west1 or a multi-region such as US or EU. A query runs where its data is. Joining a US table to an EU table fails unless global queries are turned on for the project. Location also changes how you query metadata: project-wide views such as JOBS_BY_USER need the region in the name, as `region-eu`.INFORMATION_SCHEMA.JOBS_BY_USER.
Ask which location your team uses, and create any dataset of your own in the same one. A dataset’s location can’t be changed later. See Locations.
3. Check the cost before you run
On on-demand pricing a query costs $6.25 per TiB read in the US multi-region, and the first 1 TiB each month is free. BigQuery stores each column separately, so the bytes read are the sizes of the columns you name, across every row of the tables you touch. Here is SELECT * over a GitHub events sample table:
SELECT * FROM `bigquery-public-data.samples.github_timeline`
This query will process 3.5 GiB when run.
The same table with two columns named:
SELECT repository_name, type FROM `bigquery-public-data.samples.github_timeline`
This query will process 148.6 MiB when run.
That is 3.5 GiB against 148.6 MiB, 24 times less, for the same rows. Now SELECT * with LIMIT 10:
SELECT * FROM `bigquery-public-data.samples.github_timeline` LIMIT 10
This query will process 3.5 GiB when run.
The estimate doesn’t move. On a table that isn’t clustered, like this one, LIMIT cuts what comes back and the bill stays the same. On a clustered table the scan can stop early, so LIMIT can bill less than the estimate. A WHERE on an ordinary column doesn’t reduce the bytes either: every row still has to be read to test it. What cuts the bytes is fewer columns, a filter on the partition column (step 5), or a filter on the columns a table is clustered by.
Before a query runs, BigQuery can dry-run it and report the bytes. Querylab.io shows that estimate in the status bar as you type. In the BigQuery console it is the “This query will process” line. Look at it before every run on a table you don’t know. See How a query is billed.
4. Preview a table, don’t select it
To see what is in a table, don’t run SELECT * ... LIMIT 10. Step 3 shows that it can bill the whole table. Open the table’s Preview tab instead. Previews are free and use no quota. Querylab.io shows 50 rows per page (see Table information), and the BigQuery console has the same tab. On the command line, bq head does the same.
The schema and the table’s size are also free to look at. Check the size before you write a query against a table: a 40 TiB table needs a different query than a 40 MiB one.
5. Filter on the partition column
Large tables are usually partitioned: split by a date or timestamp column into one partition per day, hour or month. A filter on that column lets BigQuery skip whole partitions, and you pay only for the ones it reads. Wikipedia’s 2020 pageviews table is partitioned by datehour. One day of it:
SELECT title, views FROM `bigquery-public-data.wikipedia.pageviews_2020` WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
This query will process 5.8 GiB when run.
The whole year:
SELECT title, views FROM `bigquery-public-data.wikipedia.pageviews_2020` WHERE datehour >= '2020-01-01'
This query will process 2.0 TiB when run.
5.8 GiB against 2.0 TiB. At $6.25 per TiB, one query costs about 4 cents, the other $12.41. Some tables refuse to run without the filter at all, because their owner set require_partition_filter. This table is one of them:
SELECT title, views FROM `bigquery-public-data.wikipedia.pageviews_2020` WHERE views > 1000
Cannot query over table 'bigquery-public-data.wikipedia.pageviews_2020' without a filter over column(s) 'datehour' that can be used for partition elimination
Add a filter on the column the error names. Querylab.io flags a partitioned table queried without a filter on its partition column before you run anything, and marks a filter BigQuery would refuse as an error.
To find the partition column, open the table’s details (Querylab.io shows it with the schema), or ask INFORMATION_SCHEMA, where this query bills the 10 MiB minimum:
SELECT table_name, column_name, is_partitioning_column, clustering_ordinal_position
FROM `bigquery-public-data.wikipedia`.INFORMATION_SCHEMA.COLUMNS
WHERE is_partitioning_column = 'YES' OR clustering_ordinal_position IS NOT NULL;
Compare the partition column with a constant. A bound that comes from a subquery, like WHERE datehour >= (SELECT MIN(datehour) ...), can’t prune before the query starts: in our test, reading only the views column, it read 146 GiB where the same day written as a literal read 2.4 GiB. See Partitioning and Clustering.
6. Put a ceiling on every query
A byte limit stops a query before it starts if it would bill more than you set, and the stopped query costs nothing. In bq it is --maximum_bytes_billed, in the API maximumBytesBilled. In the BigQuery console it is under Edit, Query settings, Advanced options. In Querylab.io every tab has a limit, 100 GiB by default, and a query over it shows a warning with the estimate and your limit before anything runs (see Cost limits).
One trap: the limit is checked against billed bytes, and BigQuery bills at least 10 MiB for every table a query reads. A limit below 10,485,760 bytes refuses every query that reads table data, including tiny ones. See Controlling costs.
7. Learn the SQL that differs
BigQuery’s SQL dialect is GoogleSQL. Most of it reads like any other SQL, but a few rules catch people from Postgres or SQL Server in the first days. Dividing two integers returns FLOAT64, so 7 / 2 is 3.5, not 3:
SELECT 7 / 2 AS slash, DIV(7, 2) AS div, SAFE_DIVIDE(7, 0) AS safe_divide
| Row | slash | div | safe_divide |
|---|---|---|---|
| 1 | 3.5 | 3 | null |
DIV gives integer division, and SAFE_DIVIDE returns NULL instead of failing on a zero divisor. In most databases x = NULL quietly matches no rows. BigQuery refuses to run it:
WITH customers AS ( SELECT 'Ana' AS name, CAST(NULL AS STRING) AS phone UNION ALL SELECT 'Ben', '555-0101' ) SELECT name FROM customers WHERE phone = NULL
Operands of = cannot be literal NULL at [7:15]
Write phone IS NULL. String comparison is case-sensitive unless a collation was set on the column, table or dataset:
WITH customers AS ( SELECT 'Ana' AS name, 'Germany' AS country UNION ALL SELECT 'Ben', 'germany' UNION ALL SELECT 'Cleo', 'France' ) SELECT COUNTIF(country = 'Germany') AS exact_match, COUNTIF(LOWER(country) = 'germany') AS lowered FROM customers
| Row | exact_match | lowered |
|---|---|---|
| 1 | 1 | 2 |
A TIMESTAMP is a point in time stored in UTC, and functions that take a date from it use UTC unless you pass a time zone. A sale at 6 pm in Los Angeles on February 28 is on March 1 in UTC:
SELECT DATE(TIMESTAMP '2026-03-01 02:00:00+00') AS utc_day, DATE(TIMESTAMP '2026-03-01 02:00:00+00', 'America/Los_Angeles') AS los_angeles_day
| Row | utc_day | los_angeles_day |
|---|---|---|
| 1 | 2026-03-01 | 2026-02-28 |
If your reports are by local day, pass the time zone to DATE, EXTRACT and TIMESTAMP_TRUNC every time. A failed cast stops the whole query. Use SAFE_CAST for messy columns, and the rows that don’t parse become NULL:
WITH raw_orders AS ( SELECT '42' AS amount UNION ALL SELECT '17.5' UNION ALL SELECT 'n/a' ) SELECT amount, SAFE_CAST(amount AS NUMERIC) AS amount_numeric FROM raw_orders ORDER BY amount
| Row | amount | amount_numeric |
|---|---|---|
| 1 | 17.5 | 17.5 |
| 2 | 42 | 42 |
| 3 | n/a | null |
Two more that show up in the first hour: table and dataset names are case-sensitive, and a project id with a dash needs backticks, as in `my-project.sales.orders`. How BigQuery SQL differs has the full list, and NULL handling and SAFE functions cover the last examples in depth.
8. Check what you have spent
INFORMATION_SCHEMA.JOBS_BY_USER lists your own jobs from the last 180 days with the bytes each one billed. This query gives your on-demand spend per day at the US price:
SELECT
DATE(creation_time) AS day,
COUNT(*) AS queries,
ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND job_type = 'QUERY'
AND statement_type != 'SCRIPT'
GROUP BY day
ORDER BY day DESC;
Change region-us to your data’s location. The statement_type != 'SCRIPT' line stops a script from being counted twice: its parent job reports the bytes of its child statements. The total ignores the free TiB and any discounts, so it is an upper bound on your bill. Querylab.io’s query history shows the same jobs with their cost, from any tool. See Monitoring jobs and costs for more queries.
9. Keep your own tables out of the way
Query results go to a temporary table that BigQuery keeps for about 24 hours. Running the same query again in that time is usually free, because BigQuery returns the cached result (see Query cache). To keep a result longer, write it to a table, but not into a production dataset. Ask for a scratch dataset of your own, ideally one with a default table expiration, so forgotten tables delete themselves (see Table expiration).
For intermediate steps inside one piece of work, use CREATE TEMP TABLE in a script or session. A temp table needs no dataset of your own, and BigQuery deletes it within 24 hours (see Temporary tables). If several queries repeat the same expensive step, write that step to a table once and query the table.
10. Read the error before you edit
Two errors confuse people early on. Access Denied on a table you are sure exists can mean the name is wrong: BigQuery gives the same answer for a dataset you can’t see and one that doesn’t exist (see Access control). Not found: Dataset ... was not found in location usually means the query ran in a different location from the data (see Not found: dataset).
A query that fails is not billed, so trying again costs nothing. A query you cancel can still be billed for what it read. The BigQuery errors section explains the messages people hit most.