{"id":8822,"date":"2026-08-15T13:37:00","date_gmt":"2026-08-15T11:37:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/blog\/?p=8820"},"modified":"2026-08-13T14:29:53","modified_gmt":"2026-08-13T12:29:53","slug":"ga4-bigquery-cost-optimization-stopping-budget-burn","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-analytics\/ga4-bigquery-cost-optimization-stopping-budget-burn\/","title":{"rendered":"GA4 BigQuery Cost Optimization: Stopping Budget Burn on Raw Exports"},"content":{"rendered":"\r\n
GA4 BigQuery Cost Optimization: Stopping Budget Burn on Raw Exports<\/h2>\r\n\r\n\r\n\r\n
Working with the native Google Analytics 4 (GA4) event export in Google Cloud BigQuery provides unmatched analytical flexibility. However, unoptimized queries against raw event tables can rapidly inflate cloud infrastructure bills. Since BigQuery charges based on the amount of data processed during query execution, understanding the underlying columnar storage architecture is critical for sustainable data engineering.<\/p>\r\n\r\n\r\n\r\n
1. The Architectural Pitfall of SELECT * on events_* Tables<\/h2>\r\n\r\n\r\n\r\n
BigQuery separates query execution from storage: Dremel is the distributed query engine, while the data itself is held in Capacitor, BigQuery's columnar storage format. In a traditional row-oriented database, querying a single row reads the entire record. In a columnar database, each column is stored individually across distributed storage blocks. Therefore, query costs depend entirely on the total data volume of the specific columns referenced in the query.<\/p>\r\n\r\n\r\n\r\n
Executing a generic SELECT * FROM `project.analytics_12345.events_*`<\/code> forces the engine to read every single column across all available shards\u2014including massive nested RECORD arrays such as event_params<\/code>, user_properties<\/code>, and items<\/code>. A single query across several months of raw data can easily scan terabytes, generating substantial unnecessary costs.<\/p>\r\n\r\n\r\n\r\n\n\nEach step cuts what the query has to read: first the time window and the columns, then the raw table itself, which the dashboard stops touching altogether.<\/figcaption>\n<\/figure>\n\n
2. Date Partitioning and Table Suffix Filtering<\/h2>\r\n\r\n\r\n\r\n
GA4 exports data into sharded tables formatted as events_YYYYMMDD<\/code>. The primary mechanism to reduce scanned bytes is strict date partitioning via the _TABLE_SUFFIX<\/code> pseudo-column. Without this filter, BigQuery reads the entire history of the dataset before applying WHERE clauses.<\/p>\r\n\r\n\r\n\r\n
-- HIGH COST (Scans entire dataset history):\r\nSELECT event_name, user_pseudo_id \r\nFROM `project.analytics_12345.events_*`\r\nWHERE event_name = 'purchase';\r\n\r\n-- OPTIMIZED (Restricts scan to a specific 7-day window):\r\nSELECT event_name, user_pseudo_id \r\nFROM `project.analytics_12345.events_*`\r\nWHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260807'\r\n AND event_name = 'purchase';<\/code><\/pre>\r\n\r\n\r\n\r\n
3. Clustering by event_name and user_pseudo_id<\/h2>\r\n\r\n\r\n\r\n
While partitioning filters data at the table-shard level, clustering sorts storage blocks internally based on specific column values. When custom analytics pipelines frequently filter by event types or user identifiers, applying clustering to downstream staging tables dramatically reduces execution overhead.<\/p>\r\n\r\n\r\n\r\n
When a query filters by a clustered column, BigQuery skips scanning irrelevant storage blocks automatically. For analytical workloads, ordering clustering keys by cardinality\u2014starting with event_name<\/code> followed by user_pseudo_id<\/code>\u2014delivers maximum scanning efficiency.<\/p>\r\n\r\n\r\n\r\n
4. Building Intermediate Tables with Incremental Scheduled Queries<\/h2>\r\n\r\n\r\n\r\n
Repetitively querying raw nested GA4 tables for dashboards or reporting tools is highly inefficient. Best practices dictate implementing an ETL (Extract, Transform, Load) workflow using Incremental Scheduled Queries<\/strong>. By processing only the most recent day’s data and appending it to a flat, clustered summary table, historical scan costs are reduced by up to 95%.<\/p>\r\n\r\n\r\n\r\n
-- One-off setup: create the target table (run once)\r\nCREATE TABLE IF NOT EXISTS `project.analytics_12345.daily_user_metrics`\r\n(\r\n event_date DATE,\r\n event_name STRING,\r\n user_pseudo_id STRING,\r\n total_events INT64,\r\n sessions INT64\r\n)\r\nPARTITION BY event_date\r\nCLUSTER BY event_name, user_pseudo_id;\r\n\r\n-- Daily incremental aggregation query (the scheduled job)\r\nDELETE FROM `project.analytics_12345.daily_user_metrics`\r\nWHERE event_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);\r\n\r\nINSERT INTO `project.analytics_12345.daily_user_metrics`\r\nSELECT\r\n PARSE_DATE('%Y%m%d', event_date) AS event_date,\r\n event_name,\r\n user_pseudo_id,\r\n COUNT(1) AS total_events,\r\n COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS sessions\r\nFROM `project.analytics_12345.events_*`\r\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))\r\nGROUP BY 1, 2, 3;<\/code><\/pre>\r\n\r\n\r\n\r\n
Summary<\/h2>\r\n\r\n\r\n\r\n
Cost-effective GA4 BigQuery architecture relies on three foundational rules: eliminating explicit wildcard column selections, restricting scanning boundaries through strict _TABLE_SUFFIX<\/code> rules, and offloading heavy reporting workloads to flattened, clustered intermediate tables populated via daily scheduled runs.<\/p>\r\n\n\n
![\"Three](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/hand-bqkosten-en.png\")
\n