Overview

At first, the issue didn’t seem critical.

Queries were slow — but they worked.
Dashboards took time — but they eventually loaded.

But over time, the cracks started to show.

Users stopped waiting for dashboards.
Costs started creeping up.
And simple questions began taking too long to answer.

That’s when performance stopped being a “nice to have” —
and became a real problem.

The goal was simple:
make the system fast, efficient, and scalable — without breaking what already works.

Architecture

The optimization focused on improving how data was stored and queried:

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

Instead of adding more resources, the focus was 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
  • Query caching strategies

A key decision was to prioritize readability + performance, ensuring future queries remain efficient.

Results

  • Query execution time reduced from ~2–5 minutes to <10 seconds
  • Data scanned reduced by up to 70–90%
  • Significant reduction in BigQuery costs
  • Improved dashboard responsiveness and user experience

Final Thoughts

This wasn’t about rewriting everything.

It was about understanding how the system behaves —
and making small, high-impact changes.

Because in data systems, performance isn’t just technical.

It directly affects how people experience data.

And when things become fast and reliable,
people stop avoiding data — and start depending on it.