BigQuery Essentials

BigQuery is Google Cloud's serverless, fully-managed data warehouse. There's no cluster to size or manage — you write SQL, Google's Dremel-based engine spreads the scan across thousands of machines, and you're billed for bytes scanned (or, on the newer editions, compute slots). It's built for analytical (OLAP) queries over huge tables, not for transactional (OLTP) workloads with lots of small writes — those belong in Cloud SQL or Spanner instead.

GoogleSQL Basics & Data Loading

BigQuery's default dialect, GoogleSQL, is ANSI-SQL-compliant with extensions for nested/repeated fields (STRUCT and ARRAY), which let you model one-to-many relationships without a join — a distinctly BigQuery way of denormalizing data for scan efficiency. Data is organized as project → dataset → table, and every query, even a SELECT *, is billed by the bytes it reads, so understanding what you're scanning matters from day one.

-- Nested/repeated fields — no join needed to model one order with many line items
CREATE TABLE `my_project.sales.orders` (
  order_id STRING NOT NULL,
  customer STRUCT<id STRING, name STRING, country STRING>,
  order_date DATE NOT NULL,
  line_items ARRAY<STRUCT<sku STRING, qty INT64, unit_price NUMERIC>>
)
PARTITION BY order_date
CLUSTER BY customer.country;

-- Querying nested/repeated data with UNNEST
SELECT
  o.order_id,
  o.customer.name,
  li.sku,
  li.qty * li.unit_price AS line_total
FROM `my_project.sales.orders` AS o,
UNNEST(o.line_items) AS li
WHERE o.order_date >= '2026-01-01';

-- Loading data from Cloud Storage (batch load — free, no scan charge)
LOAD DATA INTO `my_project.sales.orders`
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://my-bucket/orders/*.parquet']
);

Partitioning & Clustering

Partitioning splits a table into segments — most commonly by a DATE/TIMESTAMP column, or an integer range — so a query with a filter on that column only scans the matching partitions instead of the whole table. Clustering then sorts data within each partition by up to four columns, so BigQuery can skip whole blocks that don't match a filter on those columns. Together they're the single biggest lever for both cost and speed: an unpartitioned multi-terabyte table scanned daily is a common source of runaway BigQuery bills.

-- Well-filtered query on the partitioned column below only scans matching partitions
SELECT customer.country, SUM(li.qty * li.unit_price) AS revenue
FROM `my_project.sales.orders` AS o,
UNNEST(o.line_items) AS li
WHERE o.order_date BETWEEN '2026-08-01' AND '2026-08-31'  -- partition pruning
  AND customer.country = 'UA'                              -- cluster pruning
GROUP BY customer.country;

-- Require a partition filter so nobody accidentally scans the entire table
ALTER TABLE `my_project.sales.orders`
SET OPTIONS (require_partition_filter = true);

-- Ingestion-time partitioning when you don't have a natural date column
CREATE TABLE `my_project.logs.events` (
  event_type STRING,
  payload JSON
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS (partition_expiration_days = 90);  -- auto-delete old partitions

Cost & Query Optimization

On-demand pricing bills per byte scanned across all columns referenced in the query — including in a WHERE clause you never SELECT. That's why columnar storage rewards narrow SELECTs and punishes SELECT *. Before running an expensive query, the query validator (shown in the BigQuery console, or via a dry run) tells you exactly how many bytes it will process — check it before hitting run on anything touching a big table.

-- Bad: scans every column in the table, including ones we don't need
SELECT * FROM `my_project.sales.orders` WHERE order_date = CURRENT_DATE();

-- Good: only the referenced columns are read off disk
SELECT order_id, customer.name, order_date
FROM `my_project.sales.orders`
WHERE order_date = CURRENT_DATE();

-- Materialized view — precomputes and incrementally refreshes an aggregate,
-- so repeated dashboard queries hit a small, cheap result instead of re-scanning raw data
CREATE MATERIALIZED VIEW `my_project.sales.daily_revenue` AS
SELECT order_date, customer.country, SUM(li.qty * li.unit_price) AS revenue
FROM `my_project.sales.orders`, UNNEST(line_items) AS li
GROUP BY order_date, customer.country;

-- Approximate aggregation functions trade small accuracy loss for large speed/cost wins
SELECT APPROX_COUNT_DISTINCT(customer.id) AS unique_customers
FROM `my_project.sales.orders`;

from google.cloud import bigquery

client = bigquery.Client(project="my_project")

# Dry run — validates the query and reports bytes that WOULD be scanned,
# without actually running it or incurring cost
job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
query_job = client.query(
    "SELECT order_id, order_date FROM `my_project.sales.orders` WHERE order_date = CURRENT_DATE()",
    job_config=job_config,
)
print(f"This query will process {query_job.total_bytes_processed / 1e9:.2f} GB")

# Parameterized query — avoids SQL injection and improves query cache hit rate
safe_config = bigquery.QueryJobConfig(
    query_parameters=[bigquery.ScalarQueryParameter("country", "STRING", "UA")]
)
results = client.query(
    "SELECT * FROM `my_project.sales.orders` WHERE customer.country = @country",
    job_config=safe_config,
).result()
for row in results:
    print(row.order_id, row.customer["name"])

Streaming Inserts

For low-latency ingestion (analytics events, IoT telemetry) where waiting for a batch load job isn't acceptable, the Storage Write API (the modern replacement for the older legacy streaming insert / tabledata.insertAll) streams rows in and makes them queryable within seconds. It's billed differently than batch loads — there's a cost per byte streamed — and rows written this way sit briefly in a memory buffer before being committed to the table's storage.

from google.cloud import bigquery

client = bigquery.Client()
table_id = "my_project.logs.events_stream"

rows_to_insert = [
    {"event_type": "page_view", "user_id": "u_123", "ts": "2026-08-27T10:00:00"},
    {"event_type": "click", "user_id": "u_123", "ts": "2026-08-27T10:00:05"},
]

# Legacy streaming insert — simple, but not free and has quota limits
errors = client.insert_rows_json(table_id, rows_to_insert)
if errors:
    print(f"Encountered errors: {errors}")

# For high-throughput production pipelines, prefer the Storage Write API
# (google-cloud-bigquery-storage) which supports exactly-once semantics
# and is significantly cheaper at scale than legacy streaming inserts.

Gotchas & Tips

• SELECT * always scans every column's bytes, even ones you immediately discard — always project only the columns you need, especially on wide tables.

• A query without a filter on the partitioning column scans the entire table — use require_partition_filter on large tables so this can't happen by accident.

• Identical queries hit BigQuery's automatic 24-hour results cache and cost nothing to re-run — but the cache is invalidated the moment the underlying table changes, and disabled if the query is non-deterministic (e.g. uses CURRENT_TIMESTAMP()).

• For predictable, high query volume, flat-rate or autoscaling capacity (slots) pricing can be far cheaper than on-demand per-byte billing — model both before committing to a pricing tier.

• Streaming-inserted rows are held in a buffer briefly (typically minutes) before certain operations like UPDATE/DELETE/MERGE can target them — plan around this if you need immediate mutability, not just immediate queryability.

• Set a partition_expiration_days or table expiration on staging/log tables so old data ages out automatically instead of silently growing your storage bill forever.