Snowflake vs self-hosted ClickHouse for a 5TB/day analytics pipeline. Team of 3 data engineers, $15K/month budget, need sub-second dashboard queries.

Council notes

Evidence boundary

Observed from your filing

• Snowflake vs self-hosted ClickHouse for a 5TB/day analytics pipeline. Team of 3 data engineers, $15K/month budget, need sub-second dashboard queries.

Assumptions used for analysis

• The $15K/month budget is a hard ceiling that cannot be negotiated upward
• 5TB/day ingestion rate is sustained and will not decrease significantly
• The 3-person data engineering team has or can acquire sufficient ClickHouse operational skills within 60 days
• Dashboard query patterns are predictable enough to align with MergeTree ORDER BY keys for sub-second performance
• AWS i3.2xlarge instances remain available and priced at approximately $1,400/month each

Inferred candidate specifics

These details were introduced by the Council during analysis. They were not supplied in your filing.

• Deploy self-hosted ClickHouse on 3x AWS i3.2xlarge instances ($4,200/month compute) with S3 tiered storage for 90+ day data (~$3,000/month at steady state), totaling $7,200-$9,000/month. Use ReplicatedMergeTree engine with ClickHouse Keeper (not ZooKeeper). This stays well within the $15K budget while Snowflake structurally cannot — at 5TB/day, Snowflake storage alone reaches $15K/month within 4-5 months, with compute credits pushing to $20K-$35K/month by month 6. ClickHouse on NVMe delivers sub-100ms on typical dashboard aggregations over billion-row tables with proper MergeTree ORDER BY keys, exceeding the sub-second requirement by 10x. Critical failure mode: operational burnout. A 3-person team will spend 20-30% of time on ops. If one engineer leaves, the remaining two face unsustainable burden. Mitigation: document runbooks aggressively in the first 60 days and keep ClickHouse Cloud as an escape hatch. Provision with Ansible/Terraform, enforce 90-day tiered storage policy to keep NVMe under 80% capacity.
• Write a Terraform module provisioning 3x i3.2xlarge instances in a single AZ with ClickHouse Keeper, ReplicatedMergeTree table schemas matching the top 5 dashboard query patterns, and an S3 lifecycle policy for 90-day data tiering — target a working proof-of-concept cluster within 5 business days.
• b003 had the highest confidence (0.93) among surviving branches, survived 3 rounds of adversarial scrutiny with multiple models strengthening it, named specific instance types and cost numbers, identified concrete failure modes with mitigations, and provided an actionable architecture. b001 (0.70 confidence) proposed Snowflake but could not overcome the structural budget constraint at 5TB/day ingestion. b003 was the clear dominant branch.
• Write Terraform + Ansible provisioning for 3x i3.2xlarge ClickHouse cluster with ClickHouse Keeper, NVMe storage configuration, and S3 tiered storage bucket with lifecycle policy
• Design ReplicatedMergeTree table schemas with ORDER BY keys aligned to top 5 dashboard query patterns; benchmark sub-second performance with representative 1TB sample data
• Create operational runbooks for ClickHouse upgrades, disk capacity alerts (80% NVMe threshold), replication lag monitoring, and Keeper failure recovery within the first 60 days
• Set up Grafana + Prometheus dashboards tracking NVMe usage, replication lag, query P99 latency, and monthly infrastructure cost against $15K ceiling
• Define ClickHouse Cloud escape hatch trigger: if ops time exceeds 40% of team capacity for 2 consecutive sprints OR team drops below 2 engineers, initiate migration evaluation

Unknowns blocking a firmer verdict

• Whether the 3-person team has sufficient ClickHouse operational expertise — if all three are Snowflake/Redshift-experienced with zero ClickHouse exposure, the 60-day ramp-up estimate may be optimistic
• Schema evolution failure mode was cut off in b003's output — the full severity and mitigation for ALTER TABLE blocking on 10B+ row ReplicatedMergeTree tables was not fully articulated
• The $23/TB Snowflake compressed storage pricing is the current list rate; negotiated enterprise pricing could extend Snowflake's budget viability, though likely not enough to overcome the structural gap at 5TB/day
• Query pattern diversity is unknown — sub-100ms assumes dashboard queries align with MergeTree ORDER BY keys. Ad-hoc exploratory queries on non-indexed dimensions may exceed sub-second SLA
• b004's killed argument about Keeper failure during peak ingest causing hours of recovery was not fully rebutted — this is a real operational risk for a 3-person team

Operational signals to watch

Branch battle map