BigQuery
BigQuery is Google's serverless analytical data warehouse. You write standard SQL; BigQuery executes it in parallel across a fleet of workers and returns results in seconds to minutes on datasets measured in terabytes or petabytes. No clusters to provision, no traditional indexes to build.
BigQuery is not a transactional database. Queries scan large column ranges across potentially billions of rows; individual lookups take seconds, not milliseconds. Do not use it as a backend for user-facing queries, high-frequency writes, or row-level updates — use Cloud SQL or Firestore for those.
Storage Architecture
Data is stored in a columnar format. Queries read only the columns they reference. You are billed per byte scanned, not per row — selecting a few columns from a wide table scans far fewer bytes than SELECT *, since the unreferenced columns are never read.
Partitioning and Clustering
This is partition pruning — a separate lever from column pruning (selecting only the columns you need, since BigQuery storage is columnar). Both cut bytes scanned, and they stack.
Partitioning divides a table by a date, timestamp, or integer column. Queries filtering on the partition column skip irrelevant partitions, dramatically reducing scan cost. Clustering sorts data within partitions by additional columns, further reducing bytes scanned for matching queries.
For any table larger than a few gigabytes queried on a date or timestamp: partition by that column. For columns appearing frequently in WHERE and GROUP BY: cluster on them. These are required practices, not optional.
Pricing
On-demand pricing charges per byte scanned by your queries, with the first 1 TB per month free. A query that scans 500 GB of data costs roughly the same whether it runs in 5 seconds or 5 minutes — you pay for bytes read, not time. Capacity pricing (BigQuery Editions) bills dedicated processing capacity per slot-hour, with autoscaling between a baseline and a maximum and optional one- or three-year slot commitments at a discount — more cost-effective at predictable high query volumes where the per-byte bill would otherwise be unpredictable.
Materialized Views and Scheduled Queries
Materialized views precompute results and refresh automatically. Scheduled queries run SQL on a schedule and write results to destination tables, useful for pre-aggregating dashboards.
- Running
SELECT *on wide tables — scans all columns, inflating cost. - Not partitioning time-range tables.
- Using BigQuery for millisecond-latency operational queries.
- Not previewing estimated query cost before running large queries.
- Treating BigQuery as the system of record for operational data — keep the source of truth in Cloud SQL or Firestore and load or stream into BigQuery for analysis.
- Partition every time-range table.
- Cluster on columns used in WHERE and GROUP BY.
- Preview cost before running large queries.
- Use materialized views for expensive recurring aggregations.
- Set table expiration policies on temporary tables.
Knowledge Check
Why should you avoid running SELECT * on wide tables in BigQuery?
- SELECT * is rejected as invalid syntax in BigQuery's GoogleSQL dialect and returns a parse error before the query even runs at all
- It takes an exclusive lock on the table and blocks other queries from running against it simultaneously
- BigQuery bills per byte scanned — selecting all columns on a wide table costs far more than selecting only the columns you need
- SELECT * returns columns in an unpredictable order that can change between query runs
What does partitioning a BigQuery table by a date column achieve?
- It compresses each date partition independently on disk to reduce the table's overall storage cost
- Queries filtering on that date column skip irrelevant partitions, dramatically reducing bytes scanned and cost
- It splits the table into separate physical files that can each be queried in parallel by independent worker slots
- It automatically migrates old partitions to a lower-cost archival storage class after a set age
Which use case is NOT appropriate for BigQuery?
- Analytical queries aggregating terabytes of clickstream event data across many months
- Scheduled reports that pre-aggregate dashboard data nightly for business analysts
- User-facing product detail pages requiring millisecond lookup latency
- Ad-hoc SQL exploration of a petabyte-scale data lake by analysts
What does clustering add on top of partitioning in BigQuery?
- Clustering compresses the data within each partition more aggressively to reduce overall storage costs
- Clustering builds B-tree secondary indexes on the clustered columns to speed up exact-match lookups
- Clustering sorts data within partitions by additional columns, further reducing bytes scanned for matching queries
- Clustering replicates each partition across multiple zones in the region to provide higher availability and durability
What is a Materialized View in BigQuery used for?
- Caching query results in a Memorystore for Redis instance so that applications get sub-millisecond read access to them
- Precomputing expensive query results that refresh automatically, reducing cost and latency for recurring aggregations
- Creating an automatically synced read-only copy of a table in a different geographic region
- Enforcing fine-grained row-level access controls on tables that hold sensitive columns
You got correct