Lesson 2: Console, Projects, Datasets, Tables and the Sandbox

Finding your way around before you spend anything

In Level 1 you saw that BigQuery keeps storage and compute apart. You pay to store data, and you pay separately for the bytes your queries scan. This level is about the place where that work happens: the console, the containers your data sits in, and the free sandbox, where you can do all of it without a credit card.

If you come from SQL Server or Postgres, this comparison helps:

Every table has a three-part address: project.dataset.table. Keep that pattern in your head. You'll type it every day.

Step 1: Turn on the sandbox

The sandbox needs no billing account. You sign in with a Google Account, accept the terms of service and create a project.

1. Go to console.cloud.google.com/bigquery and sign in with a Google Account
2. Accept the terms of service if asked
3. Create a project, e.g. "bq-learning"
   -> Google adds a suffix if needed, so you get a project ID like bq-learning-471203
4. A SANDBOX badge appears near the top of BigQuery Studio. That means no billing is attached

Make a note of the project ID, not the display name. The ID is the one that goes into table names.

Step 2: Know the sandbox limits before you rely on it

These come from Google's BigQuery sandbox page. Check it again from time to time, because the limits can change:

• Storage: 10 GiB. Deleting data does not give quota back, so don't load and delete big files over and over.
• Queries: 1 TiB of processed data each month. This is the same as the normal free tier.
• Expiry: 60 days. Every dataset has a default table expiration, and all tables, views and partitions are deleted automatically after 60 days.
• Not supported: streaming data, DML statements (INSERT, UPDATE, DELETE) and the BigQuery Data Transfer Service.
• All the standard BigQuery quotas and limits still apply.

Gotcha: the 60-day expiry is easy to forget. Treat sandbox tables as scratch work. Keep the source files and the SQL that built each table, so you can rebuild it. If you later enable billing, the old default expirations stay in place until you change them.

Step 3: Create a dataset and choose its location carefully

In the Explorer pane, click the ⋮ next to your project, then Create dataset.

Dataset ID:     analyst_sandbox    # letters, numbers and underscores only. No hyphens or spaces
Location type:  Multi-region -> US # or a single region such as europe-west2
Advanced:       default table expiration (in the sandbox it is held at 60 days)
Click CREATE DATASET

Pay most attention to location, for two reasons:

1. You can't change it later. Google's docs say the dataset location can only be set at creation time. To move a dataset, you have to copy or recreate it.
2. Queries need matching locations. A query job runs in one location, and the datasets it reads from and writes to must match that location. The sample public datasets are stored in the US multi-region. A dataset in europe-west2 won't simply join to them.

For this course, choose US multi-region. Then your tables and the public datasets sit in the same place. In real work, data residency rules may require a different choice, so ask before you pick one.

Step 4: Use full names

In the console you'll find the public datasets in a project called bigquery-public-data. Here's how a full name breaks down:

-- project              . dataset   . table
`bigquery-public-data.usa_names.usa_1910_current`

-- your own table would look like this:
`bq-learning-471203.analyst_sandbox.my_table`

The backticks matter. Project IDs often contain hyphens, and without backticks the parser reads bq-learning as subtraction. Always quote the full path in backticks and you won't hit this. If you leave out the project and write analyst_sandbox.my_table, BigQuery assumes the project you're working in. That's fine on your own laptop, but it can confuse someone you share the SQL with.

Step 5: Inspect a table without running a query

Say you want to know what's in a table. Don't write SELECT * ... LIMIT 10. On an ordinary table, BigQuery still bills you for scanning every column the query names, even though LIMIT returns only a few rows. Click the table in Explorer instead. It opens with three tabs:

SCHEMA   -> column names, types and modes (e.g. name STRING, number INTEGER)
DETAILS  -> table ID, size in bytes, row count, created date, expiration, location
PREVIEW  -> a sample of real rows

Google's cost guidance says previewing data this way costs nothing and doesn't count against quotas. The same applies to bq head on the command line and the tabledata.list API.

Try it on usa_names.usa_1910_current. Schema shows columns such as state, gender, year, name and number. Details tells you how large the table is. That's your first hint of what a full scan would cost.

Your exercise: one query, then check its cost

Type this into a new query tab, but don't run it yet:

-- Most popular names in Texas in 2000
SELECT name, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE state = 'TX' AND year = 2000
GROUP BY name
ORDER BY total DESC
LIMIT 5;

Check the cost: look at the top right of the editor. The validator shows a green tick and a message like "This query will process N MB when run." Write that number down. Now add gender to the SELECT and GROUP BY and watch the estimate go up. You're reading one more column. Notice that deleting the LIMIT doesn't change the estimate at all.

Check the answer: run the query. Then open Job information under the results and compare Bytes processed with your note. Click Preview on the table and look for Texas rows from 2000 to confirm the names look sensible. Megabytes used against a 1 TiB monthly allowance is a tiny amount.

Key takeaways

• The hierarchy is project → dataset → table. Write full names as `project.dataset.table` in backticks.
• A dataset's location is fixed at creation, and a query's datasets must share a location. Use US to work alongside the public datasets.
• Sandbox limits: 10 GiB storage (not refunded when you delete data), 1 TiB of queries a month, 60-day expiry on tables, views and partitions, and no streaming, DML or Data Transfer Service.
• The Schema, Details and Preview tabs are free. SELECT * ... LIMIT is not.
• Read the validator's bytes estimate before you click Run.