Setting up BigQuery: the project owner checklist
A new BigQuery project allows 200 TiB of queries a day by default, budgets only warn, and the location can't be changed later. Set quotas, location, default dataset access, project and dataset defaults, billing export and monitoring before the team starts querying.
A new BigQuery project has almost no limits on spend or access. On on-demand pricing it can bill 200 TiB of queries a day, about $1,250 a day at the US price, and no single person has a limit at all. A billing budget sends notifications but stops nothing. Everyone with the basic Viewer role on the project can read every dataset you create. Each dataset’s location is fixed when it is created. This checklist is what to set before you invite the team. Steps 1, 2 and 5 are hard or impossible to fix later, so do them first.
The people who will run queries have their own list: the analyst checklist. Getting started with BigQuery shows both on one page.
1. Split query and data projects
A query is billed to the project it runs in, and it can read tables in any project it has access to. Use that to separate the two jobs a project does:
- Data projects hold datasets and pay for storage. Pipelines write there, and people get read access to datasets.
- Query projects are where people and tools run jobs. They pay for the queries, and their quotas limit the queries.
A query project per team (or per tool: one for dashboards, one for dbt, one for analysts) gives you a cost per team on the invoice with no extra work, and lets you give each one its own daily quota and its own pricing model. Separate projects for production and development keep a test from writing over production tables. See Projects, datasets and tables and, for how one query uses both, Access control.
2. Pick one location first
Every dataset has a location, and a query reads datasets in one location. A table in US can’t be joined with a table in EU unless global queries are turned on (see Locations). Choose the location from where your other data is (Cloud Storage buckets, the GA4 export, Cloud SQL) and from any data residency rules, then use it for every dataset.
The location can’t be changed after the dataset is created. Moving means copying every table to a new dataset. bq makes this easy to get wrong. We created a dataset with bq mk --dataset and no --location: it was created in US. Then we ran bq update --location=EU on it. bq printed Dataset ... successfully updated and the dataset stayed in US. So always pass the location:
bq mk --dataset --location=EU my-project:sales
CREATE SCHEMA `my-project`.sales OPTIONS (location = 'EU');
You can also set a default location for jobs that can’t infer one, such as a query that names no dataset. It is a project option without a region prefix:
ALTER PROJECT `my-project` SET OPTIONS (`default_location` = 'EU');
With the usual region-eu. prefix it fails, with default_location is a global project option and cannot be set on an individual region. See Locations.
3. Lower the daily query quota
On on-demand pricing, custom quotas are the only setting that caps a whole day’s spend, and they stop every tool. There are two:
| Quota | What it caps | Default |
|---|---|---|
QueryUsagePerDay | bytes processed by everyone in the project together, per day | 200 TiB |
QueryUsagePerUserPerDay | bytes processed by each user or service account, separately | unlimited |
200 TiB a day is about $1,250 a day, or $37,500 over a 30-day month, at $6.25 per TiB. That default only stops a true runaway. Set both to numbers that fit your real use, with room for a heavy day. Days reset at midnight Pacific Time. A lower quota takes effect within a few minutes. A higher one goes through an approval.
In the Cloud console, open IAM & Admin, Quotas & System Limits, filter by BigQuery API, select Query usage per day and Query usage per day per user, and edit them. The console takes TiB. You need the Quota Administrator role (roles/servicemanagement.quotaAdmin).
From the command line, read the current value with:
gcloud beta quotas info describe QueryUsagePerUserPerDay \
--service=bigquery.googleapis.com --project=my-project
The value is in MiB (metricUnit: MiBy), not TiB. On our project the per-user quota came back as 9223372036854775807, the largest 64-bit integer, which is how “unlimited” is written. Set a new value with gcloud beta quotas preferences create, also in MiB, so 1 TiB is 1048576:
gcloud beta quotas preferences create \
--service=bigquery.googleapis.com --project=my-project \
--quota-id=QueryUsagePerUserPerDay --preferred-value=1048576
gcloud beta quotas preferences list --project=my-project shows every override you have set. A quota that runs out fails queries with Custom quota exceeded, and the project quota is shared. One runaway job can use up the day and stop your dashboards until midnight Pacific. So set the per-user quota lower than the project one, and run dashboards and pipelines as their own service accounts or from their own project. See Controlling costs.
4. Add a budget, knowing it only warns
A Cloud Billing budget emails the billing admins when spend passes the thresholds you choose. It doesn’t stop queries. Set one per billing account, or per project with --filter-projects:
gcloud billing budgets create --billing-account=XXXXXX-XXXXXX-XXXXXX \
--display-name="BigQuery monthly" --budget-amount=500USD \
--threshold-rule=percent=0.5 \
--threshold-rule=percent=0.9 \
--threshold-rule=percent=1.0,basis=forecasted-spend
The forecasted-spend rule fires when the month is on track to pass the amount, which usually fires before the 100% spent threshold. See Budgets and alerts only warn.
5. Turn on the billing export today
The Cloud Billing export to BigQuery writes every cost line of the billing account, with discounts and credits, to a table you can query. It isn’t retroactive beyond the start of the previous month, so turn it on before you need the history: Cloud Billing console, Billing export, BigQuery export. Write it to a dataset of its own. See Labels and billing export.
6. Grant access with groups and roles
A query needs two roles in two places: roles/bigquery.jobUser on the query project to run jobs, and roles/bigquery.dataViewer on each dataset to read it. Grant both to Google groups, not to people, so access follows team membership:
GRANT `roles/bigquery.dataViewer`
ON SCHEMA `acme-data`.sales
TO "group:[email protected]";
Grant jobUser on the query project in IAM, in the Cloud console or with gcloud projects add-iam-policy-binding acme-analytics --member=group:[email protected] --role=roles/bigquery.jobUser. Avoid the basic roles Viewer, Editor and Owner on data projects. We created a dataset and read its access list. Without being asked, it gave READER to projectReaders, WRITER to projectWriters and OWNER to projectOwners. In other words, anyone with Viewer on the project can read every new dataset, and Editors can change it. Give people BigQuery roles on datasets instead, and keep roles/bigquery.admin for the people who run the project.
Run each pipeline as its own service account with dataEditor on the datasets it writes, so its jobs are easy to find and its access is easy to take away. dataViewer also lets a person export a table to Cloud Storage and snapshot it, so for sensitive datasets look at authorized views, row-level security and column-level security. See Access control.
7. Set project defaults
A few settings apply to every query in a project and region. They are set with ALTER PROJECT and need bigquery.config.update:
ALTER PROJECT `my-project` SET OPTIONS (
`region-eu.default_query_job_timeout_ms` = 3600000, -- one hour
`region-eu.default_time_zone` = 'Europe/Berlin'
);
The timeout cancels a query that runs longer than this, and it must be between 5 minutes and 48 hours. A smaller value fails with timeout should be between 5 minutes and 48 hours. Without one, a query can run up to the 6-hour limit (see Quotas and limits). On capacity pricing, a long query costs slot time, so a timeout is a cost control there. Google’s example for this statement spells the option default_job_query_timeout_ms. That name fails with default_job_query_timeout_ms is not a valid option for ALTER PROJECT. The correct name is default_query_job_timeout_ms.
default_time_zone changes what CURRENT_DATE() and date functions without a time zone return, so set it once, early, before reports depend on the old UTC behavior. To see what is set, query `region-eu`.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS. It returns no rows until you set something.
8. Set dataset defaults
Three dataset options can lower the storage bill. Set them when you create the dataset:
CREATE SCHEMA `my-project`.scratch OPTIONS (
location = 'EU',
default_table_expiration_days = 7,
max_time_travel_hours = 48,
storage_billing_model = 'PHYSICAL' -- only if the forecast below says so
);
default_table_expiration_days deletes a table a set time after it is created, which is right for scratch and staging datasets where people forget what they made. It applies to tables created after it is set. See Table expiration.
max_time_travel_hours is how long deleted and changed data can be recovered, 7 days by default. A new dataset showed maxTimeTravelHours: 168. The allowed values are 48 to 168, in steps of 24, and anything else fails with Please use a value between "48" and "168", in multiples of 24. A shorter window lowers the bill only on the physical storage model, and only for tables that are changed often. Keep 7 days on production data: it is how you restore a table someone deleted. See Time travel.
storage_billing_model is LOGICAL unless you change it. PHYSICAL bills the compressed bytes at a higher rate per GiB ($0.040 against $0.023 for active storage in us-central1), so it is cheaper when your data compresses more than about 1.7 to 1, and it also bills time travel and fail-safe bytes. After a change you must wait 14 days before changing it again. Forecast both for your own tables before you switch, with the query in Storage pricing.
9. Partition and cluster the big tables
Any table of tens of GiB or more that is mostly read by date should be partitioned by that date, clustered by the columns people filter on, and set to refuse queries that skip the date filter:
CREATE TABLE `acme-data`.sales.orders (
order_id INT64,
order_date DATE,
customer_id STRING,
amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_id
OPTIONS (require_partition_filter = TRUE);
require_partition_filter stops the most common expensive mistake, a full scan of a large table, for every tool that queries it. It doesn’t make sure the filter is narrow. See Partitioning vs clustering and what counts as a filter.
10. Start on on-demand pricing
On-demand pricing charges per TiB read and needs no setup. Capacity pricing (editions) charges per slot-hour and needs a reservation. Start on on-demand while you learn what your workload is. Once spend is steady for a month or two, compare the two with your own job history. See On-demand vs capacity pricing and Reservations. On capacity pricing the byte quotas in step 3 no longer apply. The reservation’s maximum slots is the cap there.
11. Label jobs and datasets
A label on a job (team:growth, pipeline:nightly-orders) reaches the billing export and INFORMATION_SCHEMA.JOBS, so you can split the bill by team or pipeline. Dataset labels reach only storage costs, and table labels reach neither. Ask each tool and pipeline to set job labels from the start: adding them later doesn’t relabel past jobs. See Labels and billing export.
12. Look at the jobs every week
INFORMATION_SCHEMA.JOBS_BY_PROJECT keeps 180 days of every job: who ran it, what it billed, and its SQL. Save a few queries and look once a week at cost per user, the most expensive queries and the same query repeated many times. Monitoring jobs and costs has them ready to run, and they need bigquery.resourceViewer or a role that includes jobs.listAll. Data Access audit logs are on by default and record who read what (see Audit logs).
Querylab.io’s query history lists every job in the project with its cost, from any tool.
13. Know how to undo a mistake
Learn the restore steps before anyone needs them. All of them work only inside the time travel window from step 8.
- Rows deleted or overwritten: copy the old rows into a temp table with
FOR SYSTEM_TIME AS OFa moment before the change, then insert them back. Inserting straight from the old version into the same table is refused (see Restore deleted rows). - A dropped table: you can’t query it any more, but a copy job with a time decorator brings it back, as
bq cp sales.orders@-3600000 sales.orders_restoredfor an hour ago. Partitioning and other table settings don’t come back with it. - A deleted dataset:
UNDROP SCHEMA sales OPTIONS (location = 'EU'). Without the location, BigQuery looks inUS.
Before a risky migration, take a table snapshot. It is cheap, because it stores only the data that changes afterwards, and it lasts as long as you keep it. See Time travel.
Later, when you need them
Some settings are for a specific need, not for day one: customer-managed encryption keys (default_kms_key_name, see Encryption), VPC Service Controls, cross-region replication, reservations and BI Engine. Each one is easier to add to a project that already has the groups, labels and quotas above.