BigQuery Python Cheat Sheet

1Install & Clientauth

pip install "google-cloud-bigquery[pandas]"★
The [pandas] extra adds DataFrame support; add bigquery-storage for fast reads (card 11). Python 3.10+.
gcloud auth application-default login # local dev auth★
Sets up Application Default Credentials. On GCP (Cloud Run/GCE/GKE) the attached service account is used automatically.
from google.cloud import bigquery client = bigquery.Client(project="my-project")★
The client. project is the billing project. Client.from_service_account_json("key.json") for an explicit key.
client = bigquery.Client(project="p", location="US")
Set the dataset location (region) to avoid "dataset not found" errors on non-US data.

2Run a query.query()

job = client.query("SELECT name, count FROM `p.ds.t` LIMIT 10") rows = job.result() # waits for completion★
query() starts an async QueryJob immediately; .result() blocks until done and returns a RowIterator. Backtick-quote `project.dataset.table`.
for row in rows: print(row.name, row.count, row["count"])★
Rows support attribute and key access. dict(row) to convert.
df = client.query_and_wait(sql).to_dataframe()★3.x
query_and_wait() is the newer one-call helper (run + wait) — returns the RowIterator directly.
job.total_bytes_processed · job.job_id · job.state
Inspect the job: bytes billed, id (to fetch later with client.get_job), and state.

3Query parameterssafe SQL

from google.cloud import bigquery cfg = bigquery.QueryJobConfig(query_parameters=[ bigquery.ScalarQueryParameter("min", "INT64", 100)]) client.query("SELECT * FROM t WHERE n > @min", job_config=cfg)★
Always parameterize user input with @name placeholders — safe against injection. Never f-string values into SQL.
bigquery.ArrayQueryParameter("ids", "INT64", [1,2,3])
Array & struct parameters too. Use @ids with UNNEST(@ids) or n IN UNNEST(@ids).
cfg = bigquery.QueryJobConfig( default_dataset="p.ds", use_query_cache=True)
QueryJobConfig carries all query options: default dataset (skip qualifying tables), cache, priority, labels, destination.

4Query → DataFrameread

df = client.query(sql).to_dataframe()★
The everyday pattern: SQL in, DataFrame out. Needs the [pandas] extra.
df = client.query(sql).to_dataframe(create_bqstorage_client=True)★
Use the Storage Read API for a much faster, parallel download of large results (needs google-cloud-bigquery-storage).
df = client.list_rows("p.ds.t", max_results=1000).to_dataframe()
Read a table directly (no query, no scan cost) — list_rows pages through table data.
it = client.query(sql).result(page_size=10000) for page in it.to_dataframe_iterable(): ...
Stream results as DataFrame pages for results too big for memory.

5Load DataFrame → tablewrite

job = client.load_table_from_dataframe(df, "p.ds.t") job.result() # wait for the load★
Load a DataFrame into a table (via Parquet under the hood). Always .result() to surface errors.
cfg = bigquery.LoadJobConfig( write_disposition="WRITE_TRUNCATE", schema=[bigquery.SchemaField("id","INT64")]) client.load_table_from_dataframe(df, "p.ds.t", job_config=cfg)★
write_disposition: WRITE_APPEND (default), WRITE_TRUNCATE (replace), WRITE_EMPTY (fail if data). Give an explicit schema for tricky dtypes.
# pandas object/NaN columns can infer the wrong BQ typegotcha
Let BigQuery infer, but pass an explicit schema= for dates, nullable ints, and JSON to avoid surprises.

6Datasets & tablesDDL via API

ds = bigquery.Dataset("p.my_ds"); ds.location = "US" client.create_dataset(ds, exists_ok=True)★
Create a dataset (the container for tables). exists_ok=True makes it idempotent.
t = client.get_table("p.ds.t") t.num_rows · t.schema · t.modified★
Fetch table metadata: row count, schema, size, partitioning, timestamps.
client.delete_table("p.ds.t", not_found_ok=True) client.list_tables("p.ds") · client.list_datasets()
Delete & list. You can also just run CREATE TABLE/CREATE OR REPLACE DDL via client.query().
table = bigquery.Table("p.ds.t", schema=schema) table.time_partitioning = bigquery.TimePartitioning(field="ts") client.create_table(table)
Create partitioned/clustered tables — essential for cost & performance at scale.

7Load from files / GCSingest

cfg = bigquery.LoadJobConfig(source_format="CSV", skip_leading_rows=1, autodetect=True) client.load_table_from_uri("gs://bucket/*.csv", "p.ds.t", job_config=cfg)★
Load from Cloud Storage (wildcards allowed). Formats: CSV, NEWLINE_DELIMITED_JSON, PARQUET, AVRO, ORC.
with open("data.parquet", "rb") as f: client.load_table_from_file(f, "p.ds.t", job_config=cfg).result()
Load from a local file object — good for small/one-off uploads.
source_format="PARQUET" # self-describing — no schema needed
Parquet/Avro carry their own schema, so you can skip schema=/autodetect.

8Schemafield types

schema = [ bigquery.SchemaField("id", "INT64", mode="REQUIRED"), bigquery.SchemaField("tags", "STRING", mode="REPEATED")]★
mode: NULLABLE (default), REQUIRED, REPEATED (an array). Types: INT64, FLOAT64, STRING, BOOL, TIMESTAMP, DATE, NUMERIC, JSON, GEOGRAPHY.
bigquery.SchemaField("addr", "RECORD", fields=[ bigquery.SchemaField("city", "STRING")])
RECORD/STRUCT with nested fields= — BigQuery's nested/repeated model.
t.schema = t.schema + [bigquery.SchemaField("new","STRING")] client.update_table(t, ["schema"])
Additive schema changes via update_table with a field mask.

9Dry run & cost controlbytes = money

cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False) job = client.query(sql, job_config=cfg) print(job.total_bytes_processed) # estimate, $0 to run★
Dry run validates the SQL & estimates bytes scanned without running or billing. Do this before expensive queries.
bigquery.QueryJobConfig(maximum_bytes_billed=10**9) # 1 GB cap★
Hard cap: the query fails instead of scanning more than N bytes — a guardrail against a runaway SELECT *.
# SELECT * on a big table scans EVERY column you don't needcost
BigQuery is columnar & bills by columns scanned. Select only needed columns, filter on the partition column, and prefer clustered fields.
bigquery.QueryJobConfig(priority="BATCH") # vs INTERACTIVE
BATCH priority queues the query (no concurrency-slot pressure) — cheaper on flat-rate, fine for non-urgent jobs.

10Streaming & write APIreal-time in

errors = client.insert_rows_json("p.ds.t", [{"id":1}, {"id":2}]) if errors: print(errors)★
Legacy streaming inserts: rows appear almost immediately. Returns a list of per-row errors (empty = success).
# prefer: Storage Write API (google-cloud-bigquery-storage)
The Storage Write API is the modern, cheaper, higher-throughput path for streaming ingestion — use it over insert_rows_json for new work.
job = client.query("CREATE TABLE ... AS SELECT ..."); job.result()
For big transforms, run DDL/DML (CREATE TABLE AS, MERGE, INSERT) server-side rather than round-tripping data.

BigQuery Python cheat sheet