We discovered a query that nearly cost us $1 million USD a month in BigQuery. Below, we’ll share our tips for lowering costs in BigQuery.
Overview
The article discusses how a team at Shopify discovered a query in BigQuery that could potentially cost them nearly $1 million per month and outlines the steps they took to reduce this cost significantly. Key strategies include clustering tables to minimize data scanned and other best practices for optimizing BigQuery usage.
What You'll Learn
1
How to reduce BigQuery costs through table clustering
2
Why selecting specific columns in queries can lower costs
3
When to use partitioning in BigQuery for cost efficiency
Prerequisites & Requirements
- Basic understanding of SQL and data warehousing concepts
- Familiarity with BigQuery and Google Cloud Platform(optional)
Key Questions Answered
How can clustering tables in BigQuery reduce costs?
Clustering tables in BigQuery organizes data based on specific columns, allowing the query engine to scan only relevant data instead of the entire table. This significantly reduces the amount of data processed, leading to lower costs. In the article, clustering reduced the data scanned from 75 GB to 508.1 MB.
What was the estimated cost of running a query before optimization?
Before optimization, the estimated cost of running a query in BigQuery was approximately $949,218.75 per month, based on the assumption of processing 194,400,000 GB of data from 2,592,000 queries.
What are some best practices for reducing BigQuery costs?
Best practices include avoiding the SELECT * statement to limit scanned columns, partitioning tables to restrict data scans, and using table preview options instead of running exploratory queries. These strategies help manage costs effectively.
Key Statistics & Figures
Estimated monthly cost before optimization
$949,218.75
This cost was based on processing approximately 194,400,000 GB of data from 2,592,000 queries.
Data scanned after clustering
508.1 MB
This was the amount of data scanned after implementing clustering, compared to 75 GB before.
Estimated monthly cost after optimization
$1,370.67
This is the reduced cost after clustering, based on scanning approximately 259,200 GB of data.
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Bigquery
Used as the data warehouse for storing and querying large datasets.
Stream Processing
Apache Flink
Used to build the data pipeline for a marketing tool at Shopify.
Stream Processing
Apache Kafka
Handled streaming requests in the data pipeline.
Storage
Google Cloud Storage
Used for exporting query results from BigQuery.
Key Actionable Insights
1Implement table clustering in BigQuery to optimize query performance and reduce costs.By clustering tables based on frequently queried columns, you can significantly decrease the amount of data scanned, which directly lowers your BigQuery bill.
2**Avoid using SELECT * in queries to limit the data processed.** Selecting only the necessary columns minimizes the data scanned, which can lead to substantial cost savings, especially when running frequent queries.
3Consider partitioning your BigQuery tables based on time or other criteria.Partitioning helps manage large datasets by dividing them into smaller, more manageable segments, which can reduce the amount of data scanned during queries.
Common Pitfalls
1
Running exploratory queries without considering cost implications can lead to unexpected high bills.
Many users may not realize that querying large datasets can incur significant costs. Instead, using table preview options can provide insights without the associated costs.