nishaant dixit

Posted on May 7 • Originally published at sivaro.in

ClickHouse vs BigQuery Cost: The Real Numbers

I spent six months building a real-time analytics pipeline for a fintech startup. We processed 50 million events daily. Our BigQuery bill hit $40,000 in month three. That's when I started digging into ClickHouse.

Here's the hard truth about clickhouse vs bigquery cost: most teams choose wrong because they optimize for the wrong metric. They look at query cost per terabyte. They should be looking at total system cost per query per user.

What you'll learn: the actual cost models for both systems, where BigQuery's serverless model hides expensive surprises, and when ClickHouse's self-hosted approach saves you 10x. According to ClickHouse vs BigQuery, the answer depends entirely on your query patterns and data volume.

What is clickhouse vs bigquery cost? It's the total financial comparison between Google BigQuery's serverless data warehouse and ClickHouse's self-managed (or cloud) columnar database. The difference isn't just per-byte pricing. It's workload shape, concurrency patterns, and hidden infrastructure costs.

BigQuery charges per query. You pay for data scanned. ClickHouse charges for compute resources. You pay for what you provision.

This seems simple. It's not.

BigQuery's pricing: $6.25 per TB of data scanned. Plus flat-rate reservations starting at $2,000/month for 100 slots. Plus storage at $0.02/GB/month. The catch? Every column you query, every full scan of a partitioned table, every SELECT * — it all counts.

ClickHouse's pricing: self-hosted means paying for servers. Cloud hosting via ClickHouse Cloud starts at roughly $0.75/hour per replica. The catch? You're responsible for sizing, scaling, and managing infrastructure.

According to Pricing BigQuery VS Self-hosted ClickHouse, a user processing 1TB of daily queries spent $18,000/month on BigQuery. Their self-hosted ClickHouse setup with equivalent performance cost $1,200/month. That's a 15x difference.

The devil is in the middle.

I've seen three patterns that destroy BigQuery budgets:

Every query you run against BigQuery scans the total data. Even with clustering and partitioning. A single analyst running SELECT COUNT(*) FROM events against a 10TB table costs $62.50. Do that fifty times per day? $3,125/month. On one query.

-- This costs $62.50 on BigQuery with a 10TB events table
SELECT COUNT(*)
FROM events
WHERE event_type = 'purchase';

Compare with ClickHouse:

-- ClickHouse only scans relevant partitions
SELECT count(*)
FROM events
WHERE event_type = 'purchase'
  AND toDate(created_at) = today();

ClickHouse reads only the event_type column (a few GB) and the partition date (a single day). The difference: $62.50 vs $0.02.

BigQuery adds slot contention under load. You buy flat-rate slots ($4,000-8,000/month for steady 500 slots) or you pay on-demand. During peak hours, queries slow to a crawl.

A real scenario from my experience: thirty concurrent dashboards running every five minutes. Each query scanned 100GB. BigQuery on-demand cost: $9,375/month. Flat-rate at 500 slots: $6,000/month. ClickHouse on three c5.4xlarge instances: $1,800/month.

According to In-depth: ClickHouse vs BigQuery, PostHog moved from BigQuery to self-hosted ClickHouse. Their query latency dropped from 3-5 seconds to 200ms. Their costs dropped by 80%.

BigQuery charges for streaming inserts ($0.05 per 200MB). If you're inserting millions of rows per second, that adds up.

ClickHouse handles inserts via batch merges. Zero per-row cost. Your compute handles everything.

Let me be direct: ClickHouse isn't free. It just has different costs.

You need at least 3 replicas for production. Each replica on AWS c5.4xlarge costs about $400/month. Factor in EBS volumes ($100/month for 500GB SSD). Plus networking, monitoring, backup. Total: $1,500-2,000/month minimum.

I've fixed two ClickHouse deployments where teams provisioned 8x the compute they needed. They assumed "more is better." ClickHouse is memory-hungry. Each query eats available RAM. If you provision 48GB per node and your queries only need 8GB, you're paying for idle resources.

profiles:
  default:
    max_memory_usage: 32000000000      max_query_size: 1048576

profiles:
  default:
    max_memory_usage: 8000000000      max_query_size: 262144

According to ClickHouse vs BigQuery Cost and Performance, poorly optimized ClickHouse deployments waste 40-60% of compute resources on idle query capacity.

Based on real data from multiple production deployments, here's the rough break-even:

Choose BigQuery when:

Your query volume is under 500GB scanned per day
Your team has zero DevOps capabilities
You need instant, elastic scaling with zero management
You run fewer than 10 concurrent analytic queries

Choose ClickHouse when:

You scan more than 1TB per day across queries
You need sub-second analytics dashboards
You have high concurrency (50+ concurrent queries)
Your data grows faster than 1TB/month

According to ClickHouse vs BigQuery: Performance and Cost Comparison, at 2TB daily scan volume, BigQuery costs $13,000/month. Equivalent ClickHouse Cloud costs $4,500/month. Self-hosted ClickHouse costs $2,100/month.

The spread widens as data grows.

This is where most analysis gets it wrong. They compare query speed alone. They ignore the cost of making queries fast.

BigQuery's advantage: zero tuning. Write the query, get results. But that "easy" query on a 5TB table costs $31.25 per execution. Run it 100 times per day? $3,125/month.

ClickHouse requires upfront schema design. Primary keys, partition keys, sorting orders — all matter. But once optimized, queries on the same 5TB table cost pennies.

-- Optimized ClickHouse table for cost-efficient analytics
CREATE TABLE events (
    event_id UUID,
    event_type String,
    user_id UInt64,
    created_at DateTime,
    properties JSON
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (event_type, user_id, created_at)
SETTINGS index_granularity = 4096;

This table only scans relevant partitions and sort orders. A query filtering by event_type and a date range might read 2GB instead of 5TB.

The performance difference? BigQuery query: 2.5 seconds, $31.25. ClickHouse query: 150ms, $0.02.

I've deployed ClickHouse in five production environments. Here's what I learned the hard way.

First, disk IO matters. ClickHouse is IO-sensitive. Run it on network-attached EBS and your queries slow down during contention. Use local NVMe if budget allows.

Second, memory isn't just for caching. ClickHouse stores column data in memory for merge operations. If you don't have enough RAM, merges stall. Queries freeze. The cluster falls over.

Third, replication is mandatory. I've seen a single ClickHouse node with 100% uptime for 11 months. Then the disk failed. Without replication, you lose everything.

According to BigQuery vs Clickhouse (2025), self-hosted ClickHouse requires 70% more operational effort than managed BigQuery in the first 6 months. After 12 months, the effort gap shrinks to 25%.

Many teams ask me about hybrid approaches. Can you use both?

Yes. And it works well.

The pattern: BigQuery for ad-hoc analytics and exploration. ClickHouse for real-time production workloads.

Here's a real architecture I built for an e-commerce client:

def route_query(sql: str, 
                execution_time_ms: int,
                user_role: str) -> str:
    if user_role == 'analyst' and execution_time_ms > 1000:
        return 'bigquery'
    if user_role == 'dashboard' or execution_time_ms < 200:
        return 'clickhouse'
    return 'bigquery'

The cost savings: BigQuery handled long-running exploratory queries (2TB/month scanned). ClickHouse handled production dashboard queries (50TB/month scanned). Total monthly cost dropped from $45,000 to $12,000.

According to How the 5 major cloud data warehouses compare on cost-performance, hybrid deployments reduce total cost of analytics by 40-60% compared to single-platform approaches.

BigQuery egress to the internet: $0.12/GB. If your dashboards are on a different cloud or on-premise, data transfer can double your bill.

ClickHouse data transfer: whatever your compute provider charges.

BigQuery includes 7-day backup retention for free. Longer retention costs $0.01/GB/month.

ClickHouse requires manual backup configuration. Table snapshots, S3 backups, point-in-time recovery — all additional engineering effort.

clickhouse-client --query "
    BACKUP TABLE events TO S3(
        'https://s3.us-east-1.amazonaws.com/bucket/backups/events'
    )
" --settings azure_storage_account_access_key='...'

I've watched a team spend 3 weeks optimizing ClickHouse queries. They saved $8,000/month. That's a 4-month payback.

BigQuery's "optimization" is different: partition on date, cluster on frequently filtered columns, limit columns selected. Fifteen minutes of work saves $2,000/month.

The trade-off: ClickHouse optimization compounds. BigQuery optimization plateaus.

Moving from BigQuery to ClickHouse isn't free. Schema redesign. Query rewrites. Pipeline reconfigurations.

But BigQuery lock-in is real. Your queries use BigQuery SQL, which supports 400+ functions. Many don't have ClickHouse equivalents.

Critical differences:

ARRAY_AGG vs groupArray
UNNEST vs arrayJoin
Window functions (BigQuery has them, ClickHouse is catching up)

According to BigQuery vs Clickhouse — How we decided, migrating from BigQuery to ClickHouse took Userbird's team 6 weeks and required rewriting 40% of their queries.

Don't underestimate the migration cost.

Based on actual deployments and published pricing:

Workload Pattern	BigQuery Monthly	ClickHouse Self-Hosted	ClickHouse Cloud
100GB/day, 5 concurrent queries	$1,950	$1,200	$1,800
1TB/day, 20 concurrent queries	$19,500	$2,400	$4,500
10TB/day, 100 concurrent queries	$195,000	$8,400	$18,000

According to ClickHouse® vs BigQuery: real-time analytics comparison, at 5TB daily query volume, BigQuery costs $97,500/month. ClickHouse Cloud costs $9,000/month. Self-hosted costs $4,200/month.

Here's how I decide for client projects:

Step 1: Calculate your daily scanned data volume.
Don't estimate. Look at actual BigQuery usage reports. Or project based on row counts and column usage.

Step 2: Count concurrent queries during peak hours.
BigQuery adds slot contention above 20 concurrent queries. ClickHouse handles hundreds.

Step 3: Check query latency requirements.
Sub-second dashboard requires ClickHouse. Exploration queries >5 seconds are fine on BigQuery.

Step 4: Calculate total infrastructure costs.
Include compute, storage, data transfer, backup, and operational labor.

According to ClickHouse vs BigQuery Cost, 70% of organizations save money by switching from BigQuery to ClickHouse when their query volume exceeds 500GB/month.

Start with BigQuery if your team is small and your data is under 500GB/month. The serverless experience saves engineering time.

Switch to ClickHouse when you hit three triggers:

Monthly query cost exceeds $5,000
Query latency exceeds 2 seconds under load
You need 10+ concurrent dashboards

The hardest part is knowing when to switch. Most teams wait too long. I've seen companies burn $100,000/month on BigQuery when ClickHouse would cost $10,000.

Don't be that team.

Q: Is ClickHouse always cheaper than BigQuery?
No. For query volumes under 500GB/month, BigQuery's on-demand pricing with no infrastructure management costs makes it more economical.

Q: Does BigQuery have hidden costs?
Yes. Streaming inserts, data transfer out of GCP, and slot contention under high concurrency are common surprise costs.

Q: Can I use both BigQuery and ClickHouse together?
Yes. BigQuery for ad-hoc analytics exploration. ClickHouse for real-time production workloads. Hybrid deployments save 40-60%.

Q: How much does self-hosted ClickHouse cost?
Minimum $1,500-2,000/month for a production cluster with replication. Scale costs $4,000-8,000/month for handling 1TB daily.

Q: Does ClickHouse Cloud cost more than self-hosted?
ClickHouse Cloud costs roughly 2x self-hosted. You pay for managed infrastructure, automatic scaling, and support.

Q: What's the biggest mistake teams make with BigQuery costs?
Running unoptimized queries that scan entire tables. Always select only needed columns, filter on partitioned columns.

Q: How long does it take to migrate from BigQuery to ClickHouse?
Typically 4-8 weeks depending on query complexity. Expect to rewrite 30-50% of your queries.

Q: Does ClickHouse handle real-time data better than BigQuery?
Yes. ClickHouse supports sub-second inserts and queries. BigQuery is optimized for batch analytics, not real-time.

The clickhouse vs bigquery cost comparison isn't about per-byte pricing. It's about total system cost for your specific workload. BigQuery wins for low-volume, ad-hoc analytics. ClickHouse dominates high-volume, real-time, high-concurrency workloads.

Your next move:
Run a cost audit. Extract your last 3 months of BigQuery usage data. Calculate total cost per query, per user, per dashboard. If it exceeds $5,000/month, run the same workload on ClickHouse.

I help companies make this decision at SIVARO. We build data infrastructure that scales without bleeding your budget.

Nishaant Dixit

Founder, SIVARO

Building data infrastructure and production AI systems since 2018

Built systems processing 200K events/sec

Connect on LinkedIn

Originally published at https://sivaro.in/articles/clickhouse-vs-bigquery-cost-the-real-numbers.

DEV Community

ClickHouse vs BigQuery Cost: The Real Numbers

Top comments (0)