Migrating from Snowflake to ClickHouse: a pragmatic migration guide with Terraform templates

Hook: Why your Snowflake-to-ClickHouse migration should be pragmatic — not rushed

If you’re reading this, your team is under pressure to reduce cloud spend, simplify an increasingly fragile analytics stack, and speed up ad-hoc analytics queries without regressing on correctness or compliance. Migrating from Snowflake to ClickHouse can deliver major cost and performance gains in 2026 — ClickHouse’s momentum (including a $400M raise led by Dragoneer in late 2025) shows the traction behind low-latency OLAP platforms — but the migration needs a stepwise, testable path and repeatable IaC to avoid creating new outages or tool sprawl.

Executive summary: the migration in one paragraph

Do a staged migration: assess and map schemas, run a pilot for high-impact datasets, use Terraform for infra and migration jobs, validate every table with automated checks (counts, stats, row-hashes), tune ClickHouse for MergeTree/partitioning, and cut over once parity and performance targets are met. This guide provides decision checkpoints, validation scripts, and Terraform templates to provision ClickHouse clusters (self-hosted on Kubernetes) and migration jobs (Kubernetes CronJob), plus example Python validation tooling you can drop into CI.

The landscape in 2026 — why ClickHouse now?

Two trends matter in 2026:

• ClickHouse adoption has surged for high-concurrency, sub-second analytics and large-scale event ingestion. Industry momentum — including a large late-2025 financing round — means managed offerings and cloud integrations matured rapidly.
• Teams are consolidating tools to reduce cost and complexity. Replacing Snowflake for workloads that don't need its unique managed features can reduce compute and storage spend while improving query latency on common analytical patterns.

High-level migration strategy (stepwise)

1. Inventory & prioritize — catalog tables, query patterns, SLAs, and cost drivers. Prioritize tables by business impact and query frequency.
2. Schema & semantic mapping — map Snowflake data types and features (VARIANT, semi-structured JSON, time-travel) to ClickHouse equivalents or design workarounds.
3. Proof of concept — migrate a single high-value dataset; validate performance and cost.
4. Pilot — migrate 10–20% of datasets that cover different shapes: wide tables, time-series, high-cardinality joins.
5. Full migration & cutover — once validations and performance gates pass, switch ETL/pipeline sinks and decommission Snowflake in phases.
6. Post-migration tuning — optimize partitions, ORDER BY keys, compression codecs, and TTLs for cost/performance.

Decision checkpoint: when to migrate schema-as-is vs. redesign

• Migrate schema-as-is when the table is read-mostly, has simple types, and queries are simple aggregates or filters.
• Redesign when the table uses Snowflake-specific features (time-travel, streams, external functions), or if ClickHouse columnar structures (ORDER BY for MergeTree) would significantly change query patterns. Prefer redesign for very wide, sparse tables where nested structures can be exploded into narrow event tables.

Practical schema mapping rules

• Numeric types: map NUMERIC/NUMBER to ClickHouse Decimal or Float depending on precision needs. For monetary values, use Decimal(18,4).
• Strings & VARIANT: use String or Nested/JSON functions; consider materializing frequently accessed JSON fields as explicit columns.
• Timestamps & Timezones: ClickHouse stores Date/DateTime and DateTime64; store in UTC and use DateTime64(3) for milliseconds.
• Primary/cluster keys: ClickHouse doesn’t use primary keys like OLTP DBs — design ORDER BY and partitioning for query patterns (e.g., ORDER BY (date, user_id)).

Terraform templates — what you’ll get in this guide

This guide includes two Terraform approaches you can adapt:

• Provision ClickHouse on Kubernetes (recommended for controlled infra and GitOps). We use Helm + ClickHouse Operator via Terraform.
• Provision migration jobs as Kubernetes CronJobs (Python container) to extract from Snowflake and insert into ClickHouse. Secrets are stored as Kubernetes Secrets following security best practices.

Template A: Helm + ClickHouse Operator (Terraform)

Use this when you control a Kubernetes cluster (EKS/GKE/AKS). It gives predictable operational control for MergeTree tuning.

provider "helm" {
  kubernetes { }
}

resource "helm_release" "clickhouse_operator" {
  name       = "clickhouse-operator"
  repository = "https://helm.altinity.com"
  chart      = "clickhouse-operator"
  version    = "0.22.0"
  values = [file("./values.clickhouse-operator.yaml")]
}

resource "kubernetes_manifest" "chi" {
  manifest = yamldecode(file("./clickhouse-installation.yaml"))
}

Example clickhouse-installation.yaml (abbreviated):

apiVersion: "clickhouse.altinity.com/v1"
kind: "ClickHouseInstallation"
metadata:
  name: "analytics-chi"
spec:
  configuration:
    clusters:
      - name: "cluster1"
        layout:
          shardsCount: 1
          replicasCount: 2
        templates:
          podTemplate: default

Template B: Kubernetes CronJob for migration (Terraform)

This CronJob runs a container that streams data from Snowflake to ClickHouse in incremental batches.

resource "kubernetes_secret" "db_secrets" {
  metadata { name = "migration-secrets" }
  data = {
    SNOWFLAKE_USER     = var.snow_user
    SNOWFLAKE_PASSWORD = var.snow_password
    SNOWFLAKE_ACCOUNT  = var.snow_account
    CLICKHOUSE_HOST    = var.clickhouse_host
  }
}

resource "kubernetes_cron_job" "sf_to_ch" {
  metadata { name = "sf-to-ch" }
  spec {
    schedule = "0 * * * *"
    job_template {
      spec {
        template {
          spec {
            container {
              name  = "migrate"
              image = "myorg/sf-to-ch:latest"
              env_from {
                secret_ref { name = kubernetes_secret.db_secrets.metadata[0].name }
              }
              args = ["--table", "events", "--chunk-size", "10000"]
            }
            restart_policy = "OnFailure"
          }
        }
      }
    }
  }
}

Build the container around a Python script (example below) that supports resumable, idempotent ingestion.

Example Python ETL: Snowflake → ClickHouse (key parts)

This snippet shows batching, upserts (if needed via dedupe), and row-hash generation for validation.

from snowflake.connector import connect
from clickhouse_driver import Client
import hashlib

SNOW_SQL = "SELECT id, col1, col2, last_updated FROM analytics.events WHERE last_updated >=%s AND last_updated <%s"

def row_hash(row):
    s = "|".join(str(x) for x in row)
    return hashlib.md5(s.encode()).hexdigest()

# Fetch rows from Snowflake in chunks and insert into ClickHouse
sf = connect(user=..., password=..., account=...)
ch = Client(host='clickhouse-host')

cursor = sf.cursor()
cursor.execute(SNOW_SQL, (start_ts, end_ts))
while True:
    rows = cursor.fetchmany(10000)
    if not rows:
        break
    # compute per-row hashes
    enriched = [(*r, row_hash(r)) for r in rows]
    # ClickHouse insert (assuming table has a 'row_hash' column)
    ch.execute("INSERT INTO analytics.events (id, col1, col2, last_updated, row_hash) VALUES", enriched)

Data validation checks — automation is mandatory

Validation gates should be automated and run in CI/CD for every migrated table. Implement these checks in this order (fastest to slowest):

1. Row counts — total row counts by partition/date.
2. Nullability & schema checks — ensure required columns are present and types match mapped expectations.
3. Column stats — min/max/sum/avg for numeric columns; cardinality/approx_distinct for high-cardinality fields.
4. Row-hash parity — compute a deterministic row hash on both sides and compare mismatches via a join or by exporting mismatched keys.
5. Sample queries & result parity — run representative dashboards/queries; compare result sets or acceptance thresholds.
6. Performance & SLA tests — query latency and concurrency tests to match or improve SLAs.

Automate these checks in your pipeline and store artifacts in a remote backend that meets your security posture; for design patterns around secure storage and signed artifacts see vendor and vault workflow discussions like TitanVault & SeedVault workflows.

Example SQL & Python checks

Row counts (Snowflake):

SELECT DATE_TRUNC('day', last_updated) AS dt, COUNT(*)
FROM analytics.events
GROUP BY dt
ORDER BY dt;

Row counts (ClickHouse):

SELECT toDate(last_updated) AS dt, count()
FROM analytics.events
GROUP BY dt
ORDER BY dt;

Row-hash parity (Python approach): stream row hashes from both engines and diff:

# pseudocode
sf_hashes = set(get_hashes_from_snowflake('events', date))
ch_hashes = set(get_hashes_from_clickhouse('events', date))
missing_in_ch = sf_hashes - ch_hashes
missing_in_sf = ch_hashes - sf_hashes
if missing_in_ch or missing_in_sf:
    raise ValidationError(...)

When full row-hash parity is expensive, do partitioned checks (per-day/per-shard) and use targeted reconciliation for mismatches. For guidance on validating and offering data and ensuring provenance during exchanges see a developer guide to offering content as compliant training data.

Performance and schema tuning in ClickHouse

• MergeTree engine family: use MergeTree variants (ReplacingMergeTree, CollapsingMergeTree) when you need dedup/upsert semantics. Design ORDER BY to match common filter/aggregation patterns.
• Partitioning: partition by date to limit reading during historical queries; avoid tiny partitions.
• Compression codecs: use LZ4 or ZSTD depending on CPU vs. storage tradeoffs. ZSTD gives better compression at increased CPU cost.
• Bulk-loading: prefer ClickHouse native inserts in large batches or S3→ClickHouse ingestion (using the S3 table function) for very large initial imports.
• Materialized views: pre-aggregate heavy queries into smaller tables to speed dashboards and cut compute costs.

Handling Snowflake features not supported in ClickHouse

• Time Travel & Streams: preserve change history in a changelog table with event timestamps; implement retention with TTLs in ClickHouse.
• Semi-structured VARIANT: store raw JSON in a String column and create extracted columns or use the JSONExtract* functions for frequent queries.
• UDFs/Stored Procedures: translate critical logic to dbt transformations or to materialized views in ClickHouse for performance.

Operational playbook: CI, monitoring, and rollback

• GitOps for infra and migrations: store Terraform and CronJob manifests in a repo. Use pipelines to apply to staging and production clusters; pairing GitOps with query telemetry can unlock automation and tuning recommendations described in edge & personalization analytics.
• Automated validation gates: fail the pipeline if row counts or parity checks exceed thresholds.
• Observability: export ClickHouse metrics (Prometheus) and set alerts for replication lag, merge queue size, disk pressure, and query latencies; think about edge signals and real-time detection for live production incidents.
• Rollback strategy: keep Snowflake as the authoritative source until final cutover. Implement dual writes for a short period only if you can control idempotency; more commonly, use read-from-Snowflake and validate before switch.

Cost & security considerations

ClickHouse can reduce compute costs for many workloads, but your savings depend on query patterns and storage needs. Self-hosted clusters shift operational cost to infra and personnel. Managed ClickHouse services remove operational burden at a higher service cost — see vendor playbooks on cloud vendor changes and SMB impacts like cloud vendor merger guidance.

Security: use network layering, TLS, and role-based access. Encrypt backups and control S3 bucket policies; use vaults and secure key workflows such as those discussed in TitanVault & SeedVault. Ensure Terraform state is stored securely (remote backend with encryption) and secrets are managed by a secret store (e.g., HashiCorp Vault, AWS Secrets Manager).

Common migration pitfalls and how to avoid them

• Pitfall: Migrating everything at once and discovering parity issues late. Fix: staged pilot and automated validation checks.
• Pitfall: Ignoring query patterns and choosing poor ORDER BY/pk. Fix: analyze top queries and model MergeTree ordering accordingly.
• Pitfall: Underestimating cardinality and memory requirements. Fix: test high-cardinality joins with representative data and tune max_memory_usage and join algorithms; consider energy and edge-efficiency tradeoffs referenced in discussions of local infrastructure labs.

Real-world example — a concise case study

One SaaS metrics team in late 2025 moved their event analytics workload (1.2B rows/day) from Snowflake to a Kubernetes-backed ClickHouse cluster using the operator pattern. They:

• Ran a 2-week pilot on 10% of traffic and validated query latencies dropped by 4x for common dashboards.
• Used the CronJob approach to seed historical data via S3 bulk imports and then switched to incremental hourly jobs.
• Implemented automated row-hash checks and saw a 0.01% mismatch rate early that revealed an ETL bug — fixed before cutover.
• Saved ~40% on analytical compute costs in the first quarter after tuning merge and compression settings.

"Staged migration + automated validation turned a risky migration into a repeatable pattern across multiple teams." — Lead Data Engineer, SaaS company

Advanced strategies & future-proofing (2026+)

• Hybrid patterns: keep Snowflake for complex ELT logic or sensitive workloads and ClickHouse for high-throughput, low-latency analytics.
• Data mesh considerations: use standardized Terraform modules and validation libraries so product teams can safely spin up ClickHouse datasets with guardrails.
• Automation & AI-assisted tuning: in 2026, expect third-party tools and managed providers to suggest MergeTree keys and compression settings from query telemetry — similar automation themes discussed in edge analytics playbooks.

Actionable checklist you can run this week

1. Inventory top 20 tables by query volume and cost.
2. Choose one table for a POC and create a Terraform plan to deploy a ClickHouse test cluster (use the Helm template above).
3. Build a small migration container that copies one partition from Snowflake to ClickHouse and computes row-hashes.
4. Automate count + sample hash parity checks in your CI pipeline; fail on mismatches above a threshold (e.g., 0.1%).
5. Run performance benchmarks comparing your slowest queries between Snowflake and ClickHouse.

Resources & next steps

• Terraform Helm + k8s examples (above) — adapt the chart values to control resources and replica counts.
• Python connector references: snowflake-connector-python, clickhouse-driver, and bulk-loading patterns (S3).
• Monitoring: ClickHouse exporter for Prometheus and Grafana dashboards for MergeTree metrics; for real-time incident detection and edge-aware observability see edge signals & live events.

Closing: implementable migration, not theory

Migrating from Snowflake to ClickHouse in 2026 is a pragmatic choice for teams that need faster analytics and predictable costs — but it must be done with staged plans, automated validation, and repeatable Terraform modules. Use the templates and checks here as a starting point, iterate with pilots, and treat parity tests as your safety net.

Call to action

If you want the full Terraform repo and CI pipeline used in our pilots, download the reference implementation from our migration starter (visit deployed.cloud/migration-starters) or contact our team for a migration review workshop. Start with a one-table pilot — you’ll discover the important decisions early and minimize hidden costs.

Related Reading

• Cost Impact Analysis: Quantifying Business Loss from Social Platform and CDN Outages
• Security Best Practices with Mongoose.Cloud
• Hands‑On Review: TitanVault Pro and SeedVault Workflows for Secure Creative Teams (2026)
• Edge Signals & Personalization: An Advanced Analytics Playbook for Product Growth in 2026
• Sell Out Your Next Collection: Print Marketing Ideas for Indie Beauty Brands with VistaPrint
• Hybrid Town Halls, Micro‑Retreats, and Sober‑Curious Design: Community Strategies for Quitters in 2026
• Top Portable Chargers, Solar Panels and Power Tech from CES 2026 for Multi-Day Trips
• The Satellite Gap: What NASA Budget Changes Mean for Commercial Shipping Trackers
• How Universities Built Inclusive Assessment Workflows in 2026: Practical Lessons for Students