Big news for speed lovers: ClickHouse is now a full, first-class data store on Meltano, right alongside Postgres, Snowflake, BigQuery and MSSQL. Loader, state backend, and dbt transforms, all pointed at the same warehouse. Our new Meltano ClickHouse integration enables you to build full-load and incremental pipelines into it, track pipeline state in it, and run dbt transforms on top of it, all without stitching together a custom integration.
This has been one of our most requested integrations and it’s live today. Here’s everything that shipped, and why we built it the way we did.
The Need for Speed: Why ClickHouse, Why Now
ClickHouse has become the default home for analytics at a lot of the teams we talk to. It’s fast, it’s cheap at scale, and it’s built for the kind of aggregate-heavy queries that BI runs all day: real-time dashboards, high-cardinality event data, massive rollups, the kind of workloads where every millisecond of query latency counts.
Until now, though, there was no way to run a Meltano data store on ClickHouse at all. No loader to write data in, no state backend to track pipeline progress, no dbt support to transform in place. Enterprise teams asking about ClickHouse had no timeline to point to.
Not anymore. We built the whole thing.
Feature Parity, Unlocked
Three things have to work together for a store to be first-class in Meltano and Meltano ClickHouse integration now ships with all three:
target-clickhouse, the loader Registered in the Meltano connector catalog and fully configurable from the workspace UI, including host, port, database, credentials, TLS, table engine, and load method. It supports both full-load (truncate-and-reload) and incremental/append pipelines, and it’s built to move data fast, more on that below.
meltano-state-backend-clickhouse, the state backend Remembers where each pipeline left off, the incremental bookmarks and full-table markers that let a sync resume instead of starting over. Open source, MIT licensed, and built to work with ClickHouse’s architecture instead of against it.
dbt-clickhouse, for transforms A dbt plugin available in the Meltano catalog, using the same proven dbt-ext pattern already established for MSSQL. Settings mirror your loader configuration, so the workspace UI pre-fills values instead of making you enter everything twice.
Point all three at the same instance and you have an end-to-end extract, load, state, and transform stack on a single warehouse. One system to run, secure, and pay for.
Built for Speed, Not Just Compatibility
ClickHouse exists to move data fast, billions of rows without blinking, so throughput wasn’t an afterthought in how we built the loader. It shaped the design from the start.
The standard way to write a Singer target inserts rows one at a time through SQLAlchemy. That’s the right call for a row-oriented database like Postgres. It’s the wrong call for ClickHouse, a column store built to swallow large columnar blocks in one shot. Row-by-row inserts fight the engine the whole way: per-row overhead, tiny parts, and a merge system stuck cleaning up far more little pieces than it should.
So instead, target-clickhouse uses a native, column-oriented bulk-insert path built on clickhouse-connect, ClickHouse’s own client. Rather than streaming rows one by one, the loader assembles columnar blocks and inserts them the way ClickHouse actually wants them. In a 1M-row benchmark, that difference was dramatic:
| Path | Throughput |
| Row-oriented SQLAlchemy (SDK default) | ≈ 80k rows/s |
| Native columnar bulk insert | ≈ 540k rows/s |
That’s roughly 5 to 7 times the throughput, same data, same server, just inserted in the shape the engine is designed for. And it’s not all-or-nothing: the native path is the default, with the SDK’s SQLAlchemy insert kept as a safe fallback, so anything the fast path can’t handle degrades gracefully instead of failing.
The same pass also hardened the loader for production: insert retries with backoff for transient errors, a byte-size batch cap so wide rows can’t produce oversized inserts, native JSON column support for nested data, and optional server-side async inserts for high-frequency, small-batch streams.
Solving the Update Problem, ClickHouse-Style
Pipeline state is mutable by nature. Every run, Meltano reads the latest bookmark for a pipeline, does its work, and writes a new one. On Postgres or Snowflake, that’s a boring UPDATE.
ClickHouse doesn’t do boring UPDATEs. It’s an OLAP column store with no row-level updates and no unique constraints, the exact things a state store seems to need. Bolting a transactional key-value pattern onto an analytics engine is a recipe for something slow and fragile, so we didn’t do that. Instead, the state backend leans into how ClickHouse actually wants to work:
- The state table is a ReplacingMergeTree, keyed by pipeline and versioned by an updated_at timestamp. Writing new state is just an append, the cheapest thing ClickHouse does.
- Reads use FINAL, so the newest version of each pipeline’s state always wins, even before ClickHouse has merged older rows away in the background.
- Locking is best-effort: an insert-then-verify check with stale-lock cleanup. Meltano already namespaces state per pipeline and serialises runs per state ID, so real contention is rare, and you don’t pay for heavyweight coordination the workload doesn’t need.
The result is a state backend that behaves correctly but reads and writes the way ClickHouse is designed to. No impedance mismatch, no side-car database to babysit.
Append-First by Design
One theme runs through everything above: don’t make people pay for work they didn’t ask for.
ClickHouse has no native row update, so an “upsert” is emulated with a ReplacingMergeTree engine plus an OPTIMIZE call after each load, and that OPTIMIZE rewrites whole partitions. On a large table, it can cost more than the load itself. So both the loader and the state backend default to append-only, ClickHouse’s happy path, and the right choice for the append-heavy event and time-series data ClickHouse is built for. Upserts are fully supported when you genuinely need to re-load overlapping keys, but they’re opt-in, not a tax on every load.
Up and Running in Minutes
If you already run Meltano pipelines, adding ClickHouse as a data store follows the exact same flow you already know:
- Add a ClickHouse workspace and configure connection settings (host, port, database, credentials, TLS).
- Choose your load method: full-load or incremental/append.
- Point pipeline state at the ClickHouse state backend, if you want everything living in one place.
- Optionally add the dbt-clickhouse plugin to run transforms directly against ClickHouse.
- Run your pipeline.
The Road Ahead
This release brings ClickHouse to full parity for full-load, incremental, state and dbt workflows, but we’re not stopping here. We’re continuing to validate the connector against more real-world source combinations and will keep expanding documentation and examples as more teams put it into production.
Check out the ClikHouse Store Guide to get set up or get in touch and we’ll help you wire it into your stack.