# Bigquery Optimization

> BigQuery Optimization

- Skill: `kienbui1995/bigquery-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add kienbui1995/bigquery-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kienbui1995/bigquery-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: kienbui1995 (https://skillmd.com/u/kienbui1995)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/kienbui1995/bigquery-optimization

---


# BigQuery Optimization

## When to Use
- Designing tables for an analytics use case on GCP
- Queries are slow or unexpectedly expensive
- Planning partitioning/clustering strategy
- Preparing for GCP Professional Data Engineer exam

## Core Jobs

### 1. Schema Design
- Prefer **denormalized** schemas for analytics (avoid JOINs at scale)
- Use **ARRAY** and **STRUCT** for nested/repeated data instead of separate tables
- Choose correct column types: INT64 over STRING for IDs, TIMESTAMP over STRING for dates
- Avoid nullable columns on high-cardinality fields

### 2. Partitioning Strategy
- **Time-unit partitioned** (DAY, HOUR, MONTH, YEAR) — most common, use for time-series data
- **Integer range partitioned** — for numeric IDs with predictable ranges
- **Ingestion-time partitioned** — auto-partitions on load time (_PARTITIONTIME pseudo-column)
- Always add partition filter in queries (`WHERE date = '2024-01-01'`) to reduce bytes billed

### 3. Clustering
- Apply after partitioning; up to 4 clustering columns
- Order columns by highest cardinality first (most selective → least selective)
- Best for filter columns: user_id, country, event_type
- Clustering improves query performance but does NOT reduce bytes billed (unlike partitioning)

### 4. Query Optimization
- Avoid `SELECT *` — select only needed columns (columnar storage)
- Filter early with `WHERE` on partitioned/clustered columns
- Use `WITH` CTEs for readability; avoid deep nesting
- Avoid self-joins; use window functions instead
- Use `APPROX_COUNT_DISTINCT` for large cardinality estimates

### 5. Cost Management
- **On-demand pricing** — pay per bytes scanned (good for irregular workloads)
- **Capacity pricing (slots)** — flat-rate, good for predictable high-volume workloads
- Monitor with INFORMATION_SCHEMA.JOBS_BY_PROJECT
- Use BI Engine for in-memory acceleration of dashboard queries
- Materialized views for repeated aggregations (auto-refresh on DML)

### 6. Access Control
- Use **Authorized Views** for row/column-level security without data duplication
- Apply dataset-level IAM before table-level (principle of least privilege)
- Column-level security via **Policy Tags** (BigQuery column security + Data Catalog)

## Key Concepts
- **Slot** — unit of BigQuery compute (1 slot = 1 CPU thread for query processing)
- **Shuffle** — cross-node data transfer; minimize with partition pruning
- **Materialized View** — pre-computed results, auto-refreshed; use for repeated aggregations
- **INFORMATION_SCHEMA** — metadata views for monitoring jobs, tables, partitions

## Checklist
- [ ] Table partitioned on a date/timestamp column?
- [ ] Clustering applied (up to 4 cols, high cardinality first)?
- [ ] All queries use partition filter in WHERE clause?
- [ ] SELECT * avoided — only needed columns selected?
- [ ] Authorized views used for sensitive data instead of data copies?
- [ ] Materialized views created for repeated aggregations?
- [ ] Cost monitoring via INFORMATION_SCHEMA.JOBS?

## Output Format
- 🔴 **Critical** — full table scan with no partition filter (unbounded cost)
- 🟡 **Warning** — missing clustering, suboptimal JOIN order, no partition pruning
- 🟢 **Suggestion** — materialized view opportunity, slot reservation candidate

## Exam Tips
- **Partitioning reduces cost** (fewer bytes scanned); **clustering improves performance** (not cost)
- Authorized views → share data without duplicating; row-level security without VPC-SC
- `INFORMATION_SCHEMA.JOBS_BY_PROJECT` → monitor slot usage and bytes billed
- Flat-rate (capacity) pricing → predictable cost for high-volume; on-demand → pay-per-query
- BI Engine → in-memory cache for Looker/Data Studio; does NOT replace Bigtable for OLTP
- Cross-region BigQuery JOIN → significant data transfer cost; prefer co-located datasets