From Teradata to ClickHouse

Teradata carries premium per-TB licensing and hardware (or costly cloud equivalents), a maintenance-heavy perpetual model, and constant modernization pressure off legacy MPP. ClickHouse is a common open destination for the analytical workloads Teradata typically runs: a blazing columnar engine (open-source, with a managed Cloud option) that can cut cost dramatically for the right query patterns. As with any warehouse move, this is a re-platform with real SQL and modeling work, not a lift-and-shift.

Where ClickHouse fits, and where it doesn’t

ClickHouse excels at high-volume analytical scans, aggregations, and real-time analytics, much of classic Teradata reporting maps well. It is weaker at extremely complex multi-way JOIN-heavy enterprise queries, certain Teradata-specific optimizer behaviors, and workloads that lean on Teradata’s mature workload-management. If your estate is JOIN-heavy star-schema with strict concurrency SLAs, also evaluate Trino over a lakehouse. Match the engine to your query shape.

Use open formats as the bridge

The clean path is extract to Parquet on object storage, then load into ClickHouse, avoiding vendor dump formats and giving you a reusable layer. Teradata extraction typically uses TPT (Teradata Parallel Transporter) to unload tables efficiently to flat/Parquet files; ClickHouse ingests directly from object storage (s3() function) into MergeTree tables. Inventory schemas, datasets, BTEQ/ETL scripts, BI connections, and SQL specifics first.

TPT is built for parallel, high-throughput unload, which is exactly what you want when moving a large legacy MPP estate. Script one TPT job per large table, or per date range for the biggest fact tables, and land the output as Parquet so types and compression survive the trip. Bounded jobs are restartable and let you throttle the extract so it does not compete with production reporting during business hours. Since Teradata’s cost is dominated by per-TB licensing and hardware rather than per-query billing, the extract itself is mostly an elapsed-time and coordination problem, not a metered-spend one.

Redesigning the table layout

Teradata’s physical layout is defined by the primary index that distributes rows across AMPs and, optionally, by PPI partitioning. None of that carries over directly. In ClickHouse you make the layout explicit:

• Pick the engine. Plain MergeTree for append-heavy fact and event tables; ReplacingMergeTree when you need last-write-wins upserts; SummingMergeTree or AggregatingMergeTree for pre-aggregated rollups.
• Design the ORDER BY around your reports. The ORDER BY tuple is the primary key and sparse index. Lead with the columns your classic Teradata reports filter and range-scan on, so ClickHouse can skip most of the data.
• Partition coarsely. Use PARTITION BY (for example toYYYYMM(load_date)) for retention and dropping old data, not as a substitute for the sorting key. Avoid many small partitions.
• The distribution key has no single-server analogue. A Teradata primary index chosen for even AMP distribution is irrelevant on one ClickHouse server. If you run a cluster, sharding and replication are a separate, deliberate design step.

Porting BTEQ and the SQL

This is the substance of the project:

• BTEQ scripts. The control-flow and session logic in BTEQ has no ClickHouse interpreter; rewrite it in your orchestration layer. Convert the highest-value reports first.
• SQL dialect. Teradata functions, QUALIFY, period and temporal types, and proprietary syntax must be converted, then tested on a sample before full load.
• Updates and deletes. ClickHouse mutations are asynchronous and costly, design for append-mostly, using ReplacingMergeTree or similar for upserts.
• Workload management. Teradata’s mature WLM has no direct ClickHouse equivalent; plan resource and quota controls explicitly rather than assuming parity.

Re-point ETL/ELT and BI tools (Tableau, Power BI, MicroStrategy) to ClickHouse.

Cutover via reconciliation

1. Extract with TPT to Parquet; load into ClickHouse MergeTree with the right ORDER BY/partitioning.
2. Convert and validate BTEQ/SQL for your top queries and reports.
3. Run both systems in parallel, reconciling row counts and aggregate results, plus query-correctness and performance benchmarks.
4. Re-point ETL and BI to ClickHouse only after sign-off; keep Teradata authoritative until reconciled.

Reconciling reports and cost

Acceptance bar: numbers reconcile and queries are fast enough under realistic concurrency. Benchmark your real reports, confirm ingestion keeps up with load windows, and validate the converted BTEQ logic against Teradata output before retiring the platform.

Run each converted report on both systems over the same data and diff the aggregates; watch for drift in SUM totals and for distinct-count differences where an exact count was replaced with an approximate function. The cost story here is different from a usage-billed cloud warehouse: the Teradata win is retiring premium per-TB licensing, perpetual maintenance, and hardware, against the ClickHouse infrastructure or Cloud cost for the same workload. Because that trade is mostly fixed-cost avoidance rather than per-query metering, model it over the full contract and refresh cycle in the calculator above, and treat the numbers as illustrative until the parallel run proves both correctness and performance.

Workloads that should stay on Teradata

Not every Teradata workload should move. Extremely complex multi-way JOIN enterprise queries, anything that depends on Teradata’s mature workload management to hold concurrency SLAs, and queries tuned around Teradata-specific optimizer behaviour are the risky cases. ClickHouse shines on high-volume analytical scans and aggregations, much of classic reporting, but it is not a full replacement for a heavily transactional or JOIN-dense enterprise warehouse. For those estates, weigh Trino over a lakehouse, and consider a phased move that lands the aggregation-heavy reporting on ClickHouse first while leaving the hardest JOIN workloads for a later, separate decision.

Where this leaves you

Teradata → ClickHouse can sharply cut cost for analytical and high-volume reporting, but it’s a full re-platform: TPT extraction to Parquet, BTEQ/SQL conversion, and ORDER-BY modeling are the work, and JOIN-heavy/high-concurrency estates should also weigh Trino. Bridge via Parquet, convert, and cut over by reconciliation. Model your per-TB savings in the calculator above, and treat them as provisional until your parallel run proves both cost and performance.