Most engineers assume that cloud data warehouses charge based on the actual data processed. In BigQuery, that assumption can lead to massive, unexpected bills – sometimes in a matter of seconds.
An article by Detlife Engineering (source) highlighted a case where a straightforward data export query resulted in an unexpected $10,000 bill in just 22 seconds.
The Costly Query
EXPORT DATA
OPTIONS (
uri = 'gs://xxxxx/*.json',
format = 'JSON',
overwrite = true)
AS (
SELECT *
FROM `bigquery-public-data.crypto_solana_xxxxx.Instructions`
LIMIT 1000000
);This query extracts 1,000,000 rows from the Instructions table in the crypto_solana dataset (part of BigQuery’s public datasets) and exports them in JSON format to a Google Cloud Storage bucket.
It looks straightforward. There’s a LIMIT clause to restrict the output. It ran in seconds. But when the invoice arrived, the charges were staggering:
- Total cost: $9,847.24
- Total data “scanned”: 1,576.56 TB
- Each query billed for: 509.89 TB, even though only 1M rows were exported
- Execution time: 22 seconds
A simple oversight in query optimization resulted in a staggering and potentially ruinous expense.
Why Did BigQuery Charge So Much?
BigQuery does not bill based on actual data scanned. Instead, charges are based on the total data referenced in the query.
If your query touches a 1 PB table, you could be charged for 1 PB, regardless of how much data is returned.
This pricing model leads to unexpected costs:
- LIMIT does not reduce costs – Querying a small subset of a large table still incurs charges for the full dataset.
- Partition pruning is unreliable – Even when partitions exist, the query may still scan (and bill for) the entire table.
- Cost estimation is unintuitive – Engineers expect charges to reflect actual compute and storage usage, but BigQuery bills based on table size, not query efficiency.
A single bad query can wipe out cloud credits overnight or run up an unexpected bill that shocks finance teams.
ClickHouse: An Open-Source, Reliable, and Predictable Alternative
To compare, we ran a similar query (but designed to scan 10x more data) on ClickHouse, using the dataset hosted at crypto.clickhouse.com
ClickHouse Query:
SELECT COUNT() FROM (SELECT * FROM solana.instructions LIMIT 10000000 SETTINGS use_query_cache = false)Results:
- Query duration: ~0.03 seconds
- Bytes read: 185.18 MiB
Unlike BigQuery, ClickHouse – an open-source analytical database -follows a completely transparent and cost-effective model:
- No billing model – ClickHouse itself is free to use. The only costs are the underlying infrastructure and, optionally, DBA or support services.
- LIMIT actually limits processing – The query stops execution after retrieving the specified rows, ensuring no wasted resource consumption.
- No query-based charges – You are not billed based on the amount of data scanned or queried.
- Predictable pricing – Since you control your infrastructure, there are no unexpected charges or opaque pricing models.
- Fully open-source – ClickHouse is free from vendor lock-in, allowing businesses to deploy and scale on their own terms.
For organizations looking for a reliable, high-performance alternative to BigQuery, ClickHouse offers an open-source solution without hidden costs or unpredictable billing traps.
Looking for a Cost-Effective Alternative? Quantrail Can Help
If you’re concerned about BigQuery’s unpredictable pricing and want a more transparent, high-performance alternative, ClickHouse is a compelling choice. However, migrating from BigQuery to ClickHouse requires careful planning and expertise.
At Quantrail, we specialize in:
- Self-managed ClickHouse solutions – Get full control over your data with on-premise or self-hosted ClickHouse deployments.
- Managed ClickHouse with enterprise support – We handle setup, scaling, and performance tuning, so you can focus on your business.
- Seamless migration from BigQuery to ClickHouse – Whether you want to stay within Google Cloud or move to AWS or Azure, we ensure a smooth transition.
- Optimized infrastructure – We help you run ClickHouse efficiently, minimizing operational costs while maximizing performance.
Don’t let hidden cloud pricing drain your budget. Reach out to Quantrail for a cost-effective, high-performance ClickHouse deployment tailored to your needs.
References
Photo by olia danilevich: https://www.pexels.com/photo/us-dollar-bills-5466788/