---
title: "Reducing BigQuery Costs: How We Fixed A $1 Million Query"
description: "During the infrastructure work for a marketing tool, Shopify's team found a BigQuery query that would have processed about 75,462,743,846 bytes per request and cost nearly $1 million monthly at an estimated 60 requests per minute. The query served a pipeline ingesting one billion rows through Apache Flink, with state managed by RocksDB and streaming requests from Apache Kafka; scaling beyond the release made ingestion unsustainable. To support general availability, the team evaluated an external SQL warehouse that could load Parquet atomically, handle 60 requests per minute, and export results to Google Cloud Storage, then clustered a dataset on two feature columns used in WHERE clauses. Running the same query on the clustered table reduced billed data to 508.1 MB and identified 108.3 MB scanned, lowering estimated monthly cost to about $1,370.67; the post also recommends selecting needed columns, partitioning tables, and using free previews instead of exploratory queries."
---

# Reducing BigQuery Costs: How We Fixed A $1 Million Query

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Nov 3, 2022

**Type:** Problem & solution

## Summary

During the infrastructure work for a marketing tool, Shopify's team found a BigQuery query that would have processed about 75,462,743,846 bytes per request and cost nearly $1 million monthly at an estimated 60 requests per minute. The query served a pipeline ingesting one billion rows through Apache Flink, with state managed by RocksDB and streaming requests from Apache Kafka; scaling beyond the release made ingestion unsustainable. To support general availability, the team evaluated an external SQL warehouse that could load Parquet atomically, handle 60 requests per minute, and export results to Google Cloud Storage, then clustered a dataset on two feature columns used in WHERE clauses. Running the same query on the clustered table reduced billed data to 508.1 MB and identified 108.3 MB scanned, lowering estimated monthly cost to about $1,370.67; the post also recommends selecting needed columns, partitioning tables, and using free previews instead of exploratory queries.

## Context

The existing Apache Flink pipeline had ingested one billion rows into RocksDB-managed internal state for a limited merchant release. Expanding to general availability would substantially increase ingestion, making the approach unsustainable. A test BigQuery query billed roughly 75 GB per request, which at an estimated 60 requests per minute implied a monthly cost of approximately $949,218.75, or nearly $1 million.

## Approach / What changed

The team evaluated an external SQL warehouse that could atomically load a Parquet dataset, handle 60 requests per minute, and export results to Google Cloud Storage. They used BigQuery and created a clustered dataset on two feature columns appearing in the query's WHERE clauses. They also recommend selecting only required columns, partitioning tables, and using free table previews instead of running exploratory queries.

## Takeaways

- Clustering two feature columns from the query's WHERE clause reduced billed data for the same query to 508.1 MB, described as 150 times less than the unclustered table.
- The estimated workload was 2,592,000 queries per month at 60 requests per minute, making per-query bytes scanned a major cost driver.
- Selecting only needed columns, partitioning tables, and using free table previews are presented as additional ways to reduce BigQuery charges.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Performance](https://yomu.fyi/topic/performance), [Scalability](https://yomu.fyi/topic/scalability)

- Source: [Shopify](https://shopify.engineering/reducing-bigquery-costs)
- Source URL: https://shopify.engineering/reducing-bigquery-costs
- Ingested by Yomu: 2026-08-30T13:37:52.004Z

[Read original post](https://shopify.engineering/reducing-bigquery-costs)