Getting started with BigQuery: what to do first
Project owners set quotas, location, access and defaults before the team arrives. Analysts learn which project pays, what a query costs and which SQL behaves differently. Each has a checklist, and both list the defaults that surprise people.
Starting with BigQuery is two jobs, usually done by different people. Whoever owns the Google Cloud project sets the limits, the location and the access, before anyone runs a query, because some of those choices can’t be changed later. Everyone who writes queries learns how BigQuery bills: by bytes read, so the shape of a query decides what it costs. Each side has its own checklist, and this page lists both.
Who does what
| Task | Owner | Analyst |
|---|---|---|
| Choose the projects and the location | yes | |
| Set daily query quotas and a budget | yes | |
| Grant access, to groups | yes | |
| Set project and dataset defaults | yes | |
| Partition the big tables, require a date filter | yes | |
| Know which project pays for a query | yes | |
| Check the estimate before running | yes | |
| Filter on the partition column | yes | |
| Put a byte limit on queries | yes | |
| Watch spend | yes | yes |
In a small team one person does both. Do the owner list first.
The defaults that surprise people
BigQuery’s defaults are made for getting started quickly. These are the ones to know on the first day.
| Default | What it means | Change it in |
|---|---|---|
| Project query quota: 200 TiB a day | about $1,250 a day on on-demand pricing | owner step 3 |
| Per-user query quota: unlimited | one person or job can use the whole project’s day | owner step 3 |
| Billing budgets only notify | nothing stops when a budget is passed | owner step 4 |
New datasets give READER to project Viewers | the basic Viewer role reads every new dataset | owner step 6 |
bq mk --dataset with no location is US | and the location can never be changed | owner step 2 |
LIMIT doesn’t lower the bytes read | unclustered, LIMIT 10 bills the whole table | analyst step 3 |
| No byte limit on a query | a query bills whatever it reads | analyst step 6 |
The project owner checklist
Before you invite the team. The full checklist has the commands for each step.
- Separate the projects that run queries from the projects that hold data.
- Pick one location and pass it every time you create a dataset.
- Lower
QueryUsagePerDayand setQueryUsagePerUserPerDay. - Add a billing budget with alerts, knowing it doesn’t stop anything.
- Turn on the billing export to BigQuery now. It only goes back to the start of the previous month.
- Grant
jobUseron query projects anddataVieweron datasets, to groups. Avoid basic roles. - Set a default query timeout and time zone with
ALTER PROJECT. - Give scratch datasets a default table expiration, and choose the storage billing model.
- Partition and cluster big tables, with
require_partition_filter. - Start on on-demand pricing and compare with capacity pricing once spend is steady.
- Ask every tool and pipeline to label its jobs.
- Look at
INFORMATION_SCHEMA.JOBSonce a week. - Learn how to restore rows, tables and datasets before you need to.
The analyst checklist
Before your first real query. The full checklist has a recorded example for each rule.
- Find out which project pays for your queries, and get
jobUserthere anddataVieweron the data. - Find out which location your data is in.
- Read the byte estimate before running. Name columns, don’t count on
LIMIT. - Use the free Preview tab to look at a table.
- Filter on the partition column with a constant.
- Set a byte limit on every query.
- Learn the SQL that differs:
/returns a float,= NULLis an error, strings are case-sensitive, timestamps are UTC. - Check your own spend in
INFORMATION_SCHEMA.JOBS_BY_USER. - Write your own tables to a scratch dataset that expires them.
- Read the error message before changing the query.
Where to go next
How BigQuery works explains the storage and compute split that the billing rules come from. How a query is billed and Controlling costs go deeper on cost, and How BigQuery SQL differs on the dialect. To try the editor before connecting a project, the Playground is Querylab.io on a sample BigQuery project, with no Google account needed.