The $1 Million BigQuery Query That Never Ran

While building a data pipeline for a new marketing tool, a Shopify engineering team caught a cost problem before it became a production incident. The pipeline, built on Apache Flink, was already ingesting one billion rows into internal state managed by RocksDB and handling streaming requests from Apache Kafka. The tool had launched to a limited group of merchants, and plans for general availability meant significantly more data.

The team decided to offload ingestion to an external SQL data warehouse that could handle atomic loads of parquet datasets, accept roughly 60 requests per minute without significant queuing, and export results back to Google Cloud Storage. BigQuery was the natural choice: Shopify already used it, and it could store petabytes and query in seconds. But the first test query revealed a serious issue.

The Cost Calculation

After loading the dataset with an existing BigQuery loader, the first query returned this billing log:

total bytes processed: 75462743846, total bytes billed: 75462868992

That was roughly 75 GB billed per query. With BigQuery’s on-demand pricing model, the team did the math for the estimated 60 requests per minute at general availability:

60 RPM x 60 minutes/hour x 24 hours/day x 30 days/month = 2,592,000 queries/month 

At 75 GB per query, that came to approximately 194,400,000 GB scanned per month, translating to about $949,218.75 USD monthly. The query was effectively a million-dollar line item.

Clustering Cuts Cost by 150x

The team’s first move was to look at BigQuery table clustering, which sorts data based on one or more columns so the engine scans only relevant portions of the table. They examined the query’s WHERE clauses and identified columns that would make effective cluster keys.

Creating a clustered table on two feature columns from the WHERE clause changed the billing picture dramatically. Running the same query against the clustered table dropped the billed data to 508.1 MB, roughly 150 times less than the unclustered version. With the clustered table, the query scanned only 108.3 MB:

2,592,000 queries/month x 0.1 GB of data = 259,200 GB of data scanned/month

That reduced the projected monthly cost to approximately $1,370.67 USD, a far more sustainable figure for a GA launch.

Additional Cost Controls

Beyond clustering, the team recommends three practical habits for keeping BigQuery bills in check:

  • Avoid SELECT *: Selecting only the columns you need limits the engine’s scan to those columns, directly lowering the bytes billed.
  • Partition tables: Dividing tables into segments by time-unit, ingestion time, or integer range further restricts the data each query scans.
  • Skip exploratory queries: Running queries to preview data costs money needlessly. Use the table preview option instead, which is free.

For teams handling high-volume data, these practices are not just about cost savings. They are essential for building a data architecture that can scale without financial surprises.