BigQuery Cost Optimization
Why marketing analytics bills spike without anyone noticing
BigQuery's on-demand pricing charges per byte scanned, not per query executed or per hour running. A dashboard that re-runs a query against raw GA4 events_* tables every time a stakeholder opens it can scan terabytes repeatedly, and because no single query feels expensive in isolation, the cost compounds silently until a monthly bill triggers a Slack message nobody wants to answer. The fix isn't fewer queries — it's structuring the underlying tables so each query scans a fraction of what it used to.
Partitioning: scan only the days you need
A partitioned table physically splits data into segments — almost always by date — so a query that filters on that date column only reads the matching partitions instead of the whole table. GA4's own events_* export is already date-sharded by table (one table per day), which is a crude form of partitioning, but any custom table you build from it should use native time-unit column partitioning instead of more daily tables, since native partitioning supports partition filters, partition expiration, and clustering together in one object.
The habit that matters: never run a query against a partitioned table without a filter on the partitioning column. BigQuery can enforce this with require_partition_filter = true on the table definition, which turns an accidental full-table scan into an error instead of a bill.
Clustering: narrow the scan within a partition
Clustering sorts data within each partition by up to four columns you specify, so a query that filters or aggregates on those columns reads less of each partition's data even after partition pruning has already cut the date range. For marketing tables, clustering on event_name, source/medium, or campaign_id — whichever columns your most frequent queries filter on — is usually the highest-leverage clustering choice, since those are the columns dashboards and ad-hoc analysis filter on constantly.
-- Build a cost-efficient summary table from raw GA4 export
CREATE TABLE `project.dataset.sessions_daily`
PARTITION BY event_date
CLUSTER BY source, medium, campaign_id
OPTIONS (require_partition_filter = true)
AS
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
traffic_source.source AS source,
traffic_source.medium AS medium,
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'campaign') AS campaign_id,
user_pseudo_id,
event_name
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'Partitioning and clustering are free to create, not free to skip
Estimate before you run, every time
The BigQuery console and bq CLI both support a dry run that computes bytes-to-be-scanned without actually executing the query or incurring cost. The console shows this automatically above the query editor the moment a valid query is typed; the CLI exposes the same number with a flag. Making dry-run estimation a habit — especially before running anything against a wildcard table or an unfamiliar dataset — catches an accidental SELECT * or missing date filter before it becomes a line item.
-- bq CLI: estimate bytes scanned without running the query
-- bq query --dry_run --use_legacy_sql=false '
SELECT event_name, COUNT(*) AS events
FROM `project.dataset.sessions_daily`
WHERE event_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY event_name
-- '
-- Console equivalent: the editor shows
-- "This query will process X MB/GB when run" before executionMaterialized views: stop re-scanning raw events for the same aggregate
A materialized view precomputes and caches the result of a query, then automatically and incrementally refreshes as the underlying table changes — queries against the view only read the delta since the last refresh plus the cached result, instead of re-scanning the full base table every time. For marketing use cases, the right candidate is almost always a daily-grain aggregate that many downstream queries and dashboards share: sessions by channel, conversions by campaign, revenue by source/medium.
The difference from a scheduled query writing to a destination table is that a materialized view stays automatically in sync with the base table (BigQuery handles the incremental refresh), whereas a scheduled query only updates on its own schedule and can silently drift stale between runs if the schedule and refresh logic aren't carefully matched.
Scheduled query vs. materialized view for recurring aggregates
| Aspect | Scheduled query → table | Materialized view |
|---|---|---|
| Freshness | As fresh as the last scheduled run | Auto-incrementally refreshed, near real-time |
| Maintenance | You manage the cron and the write logic | BigQuery manages refresh automatically |
| Cost model | Pay for the full query each run | Pay only for the incremental delta on refresh |
| Flexibility | Any SQL, any destination/transform | Restricted SQL subset (no non-deterministic functions) |
| Best for | Complex multi-step transforms, cross-dataset joins | Simple aggregates queried very frequently |
Materialized views don't support every query shape
CURRENT_TIMESTAMP(), limited join support in some configurations, and a cap on how complex the aggregation can be. For a transform too complex for a materialized view (multi-table joins across ad platform exports and GA4, for instance), a scheduled query writing to a partitioned destination table is still the right tool — just pair it with the partitioning and clustering habits above.What's next
Cost control only matters once you've got GA4's raw export landing in BigQuery in the first place — the GA4 schema and query patterns that these optimizations sit on top of are covered in depth separately.
Next: BigQuery for Marketers →
I build these systems professionally.
Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.