Most companies with any operating history are sitting on years of transactional data that's functionally unqueryable — locked in an OLTP database that chokes on analytical queries, or worse, sitting cold in backups nobody wants to restore. Turning that history into something a data team can actually use is a design problem first, and a migration problem second.
Why ClickHouse specifically
Columnar storage and aggressive compression make ClickHouse a strong fit for exactly this kind of workload: large volumes of historical, mostly-append-only data queried with aggregations rather than single-row lookups. Queries that would take minutes against billions of rows in a row-oriented OLTP database routinely run in a few seconds once the data is reshaped for a columnar engine — assuming the schema is actually designed around how the data gets queried, not just a copy of the source schema.
The approach that actually works
- Start with what's actually queried. Warehousing everything indiscriminately is slower to build and harder to validate than warehousing what the business actually needs to ask questions about. Talk to the people who'll run the queries before designing the schema.
- Design around query patterns, not the source schema. The OLTP schema was normalized for transactional integrity, not for analytical speed. A ClickHouse warehouse usually denormalizes deliberately, and the choice of sort key (ClickHouse's
ORDER BY) has as much impact on query performance as any index choice in a traditional database. - Partition by time for data with a natural time dimension — it keeps queries that only touch recent data fast, and makes lifecycle management (archiving or dropping old partitions) trivial instead of a full-table operation.
- Backfill in batches, and make it resumable. A decade of history doesn't move in one transaction. Batch it, checkpoint progress, and validate each batch against the source before moving to the next — not just row counts, but reconciled sums and sampled row-level comparisons.
- Decide how fresh the warehouse needs to be going forward. Batch ETL on a schedule is simpler to build and operate; change-data-capture streaming is more complex but keeps the warehouse close to real-time. Pick based on what the analytics actually require, not by default.
On one recent engagement, this approach turned over a decade of a logistics company's historical records — previously spread across systems that made anything beyond basic lookups impractical — into a single warehouse that analysts could query directly, with response times fast enough for interactive dashboards rather than overnight batch reports.
The mistakes that show up later, not immediately
- Copying the normalized schema over directly — it'll load fine and then perform badly the moment anyone runs a real aggregation across it.
- Ignoring the cardinality of
GROUP BYkeys when choosing the sort key — a mismatch here is one of the most common causes of a ClickHouse warehouse that's technically working but disappointingly slow. - No lifecycle plan. A decade of data needs a policy for what happens to the oldest partitions — TTL rules, tiered storage, or an explicit decision to keep everything forever — decided upfront rather than as an emergency once storage costs show up on someone's radar.
Key takeaways
- Design the warehouse schema around how it will be queried, not around the source OLTP schema.
- Partition by time and choose the sort key deliberately — it's the single biggest performance lever.
- Backfill in resumable batches with real reconciliation, not just row counts.
- Decide on a data lifecycle policy before storage costs force the decision later.