Overview

At first the issue didn’t seem critical. Queries were slow but functional, and dashboards eventually loaded. Over time the cracks showed: users stopped waiting for dashboards, costs crept up, and basic questions took too long to answer. Performance had stopped being a nice-to-have and become a real problem.

The goal was to make the system fast, efficient, and scalable without breaking what already worked.

Architecture

The optimization focused on how data was stored and queried:

  • Identified high-cost and slow-running queries
  • Reduced unnecessary data scans using partitioning
  • Improved query structure by avoiding SELECT *
  • Introduced clustering for frequently filtered columns
  • Optimized joins and aggregations
  • Standardized query patterns for consistency

Instead of adding more resources, the work centered on using existing resources efficiently.

Technical details

The biggest gains came from reducing data scanned and simplifying query logic.

Example of an inefficient query:

SELECT *
FROM sales_data
WHERE DATE(timestamp) BETWEEN '2024-01-01' AND '2024-01-31'

Optimized version:

SELECT order_id, product_id, revenue
FROM sales_data
WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'

Key improvements applied:

  • Avoided unnecessary column scans
  • Leveraged partitioned columns directly
  • Reduced expensive transformations inside WHERE clauses

Additional optimizations included pre-aggregated tables for heavy dashboards, materialized views for repeated queries, and query caching strategies. A key decision was to prioritize both readability and performance, so future queries stay efficient.

Results

  • Query execution time reduced from ~2-5 minutes to under 10 seconds
  • Data scanned reduced by up to 70-90%
  • Lower BigQuery costs, driven directly by the 70-90% reduction in data scanned
  • Improved dashboard responsiveness and user experience

Closing notes

This was not a full rewrite. It came down to understanding how the system behaved under real query patterns and making small, high-impact changes: partition and cluster on the columns that filters actually use, stop scanning columns and rows the query does not need, and push repeated work into pre-aggregated tables and materialized views. Those changes moved dashboards from minutes to seconds and cut scanned data by 70-90%, and the same patterns keep new queries efficient as data volume grows.