optimizing-bigquery-querieslisted

# Optimizing BigQuery Queries ## When to use - BigQuery queries cost too much (bytes billed) or run slowly. - A query scans full tables or ignores partitions. - Choosing partitioning/clustering, or sizing slots/reservations. - Do NOT use for query logic correctness (this assumes correct results). ## Workflow ``` - [ ] Estimate bytes: query validator or --dry_run BEFORE running - [ ] Partition by date/timestamp; cluster by most-filtered columns - [ ] Select only needed columns; filter on the partition column - [ ] Replace exact-distinct/full scans with approx / incremental - [ ] Materialize repeated aggregates ``` 1. **Estimate first.** On-demand billing = bytes processed. Use the editor's validator or `bq query --dry_run` to see bytes billed before spending. 2. **Partition + cluster.** Partition large tables by date/timestamp; cluster by the columns you filter/join on most. Filtering on the partition column prunes scanned bytes dramatically. 3. **Read fewer columns.** BigQuery is columnar — `SELECT *` reads every column's bytes. List only what you need. 4. **Avoid full scans.** Filter on the partition column with literals/ranges (not wrapped in functions) so pruning applies. 5. **Approximate + materialize.** Use `APPROX_COUNT_DISTINCT` for big cardinality; use materialized views for common aggregates. ## Patterns **Partitioned + clustered table:** ```sql CREATE TABLE sales.orders PARTITION BY DATE(ordered_at) CLUSTER BY customer_id, status AS SELECT