BigQuery: Datasets, Tables, Loading and Queries

GCP Basics · Data on GCP – BigQuery, Pub/Sub, Dataflow and a First Pipeline · Lesson 1 of 5

BigQuery is a serverless data warehouse: data lives in tables inside datasets, you query it with standard SQL, and you pay for storage plus either bytes scanned (on-demand) or slot capacity (editions).

BigQuery is Google’s serverless data warehouse. There are no servers or indexes to manage: you load data and run SQL over gigabytes to petabytes.

flowchart LR
  SRC["CSV / JSON / Parquet<br/>in gs://dl-raw"] -- "bq load" --> T["Table shop.orders"]
  STR["Streaming inserts<br/>Storage Write API"] --> T
  DS["Dataset shop<br/>location asia-southeast1"] --> T
  T -- "SQL query" --> R["Results / new table"]
  R --> BI["Looker Studio, Sheets, notebooks"]
  %% step SRC: Batch files in Cloud Storage are loaded with bq load; loading is free.
  %% step STR: Apps and Dataflow can also stream rows in with the Storage Write API.
  %% step DS: A dataset is a container with a location, default expiration and IAM; tables live inside it.
  %% step T: Tables store data in columnar format, so a query reads only the columns it uses.
  %% step R: Queries run in standard SQL; results can be saved as tables or views.
  %% step BI: BI tools and notebooks connect to the results for dashboards and analysis.

Resource model

project.dataset.table, for example dl-data-prod.shop.orders.

Object	Notes
Dataset	Has a location (region like `asia-southeast1` or multi-region `US`/`EU`) that cannot change; IAM is often set here
Table	Native table, external table (data stays in Cloud Storage), or BigLake table
View / materialized view	Saved query; materialized views store pre-computed results
Routine	User-defined functions and stored procedures

IAM roles: roles/bigquery.dataViewer (read tables), dataEditor, jobUser (run queries in a project), admin. To query, a user needs both read access on the data and permission to run jobs.

Pricing

Part	Model
Storage	Per GiB per month; long-term (90 days unchanged) costs about half
Compute: on-demand	Per TiB scanned (first 1 TiB per month free)
Compute: editions	Standard, Enterprise, Enterprise Plus: pay for slots (virtual CPUs) per hour, with autoscaling and commitments
Loading, copying, exporting	Batch loads are free; streaming inserts are billed

On-demand is simple for small, irregular usage; editions give predictable cost for heavy workloads.

The bq CLI

bq mk --dataset --location=asia-southeast1 dl-data-prod:shop
bq load --source_format=CSV --autodetect shop.orders "gs://dl-raw/orders/*.csv"
bq query --use_legacy_sql=false   'SELECT DATE(created_at) AS day, SUM(total) AS revenue FROM shop.orders GROUP BY day ORDER BY day'
bq ls shop
bq show --schema --format=prettyjson shop.orders

Always use GoogleSQL (--use_legacy_sql=false); legacy SQL is old syntax.

Good habits

Put datasets in the same location as the Cloud Storage buckets you load from.
Give each team a dataset and grant roles there, not on the whole project.
Set a default table expiration on scratch datasets.
Use SELECT with the columns you need; SELECT * scans every column and costs more.

Command	Meaning
`bq ls`	Datasets in the default project
`bq head -n 5 shop.orders`	Peek at rows without a query (free)
`bq rm -r -d shop_tmp`	Delete a dataset and its tables

Traps: dataset location is permanent and queries cannot join tables in different locations; LIMIT does not reduce bytes scanned in on-demand pricing; jobUser alone cannot read data.