This hub covers essential BigQuery SQL patterns across five categories: query optimization, nested data handling, window functions, dbt incremental strategies, and marketing analytics. Notes are ordered so earlier ones provide foundations for later ones.
Prerequisites
You should understand how BigQuery works under the hood — columnar storage, slots, the separation of compute and storage. The cost model follows directly from the architecture and motivates most of the optimization patterns below.
Reading Order
Query Optimization
These patterns affect BigQuery bill and query performance.
- BigQuery Partition Pruning Patterns — Combining partitioning and clustering for maximum scan reduction, plus anti-patterns that silently defeat pruning.
- BigQuery Materialized Views — Precomputing repeated aggregations with automatic incremental refresh and transparent query rewriting.
- BigQuery HyperLogLog Sketches — Composable approximate distinct counts for rolling window metrics at a fraction of the cost.
Nested Data
Essential for anyone working with GA4, Firebase, or event data.
- GA4 Event Data Structure — How GA4 structures event data in BigQuery, including the inline UNNEST subquery pattern for clean parameter extraction without row explosion.
Window Functions
- Window Function Patterns for Analytics SQL — ROW_NUMBER for deduplication, QUALIFY for elegant filtering, LEAD/LAG for row comparison, running totals, FIRST_VALUE/LAST_VALUE for attribution, and gap-and-island detection for streaks.
dbt Incremental Strategies
- Incremental Strategy Decision Framework — When to use merge, delete+insert, insert_overwrite, or microbatch, with warehouse-specific guidance and performance benchmarks.
- Incremental Models in dbt — How incremental models work, core configuration, late-arriving data handling, and common pitfalls.
Marketing Analytics
Patterns specific to marketing data: custom session definitions and attribution modeling.
- Custom Sessionization Patterns — Building custom sessions from raw events with configurable timeouts, campaign-based splits, and session metrics.
- SQL Attribution Patterns — First-touch, last-touch, linear, position-based, time-decay, and algorithmic attribution models in pure SQL.