ClickHouse Data Integration: Batch, Streaming, CDC and API Patterns

Data integration is the work of moving data from where it is created to where it can be queried. In a ClickHouse project, that sounds simple, but the design decision matters: a daily export, an event stream and a change-data-capture pipeline solve different problems. This guide explains the patterns, trade-offs and practical steps behind each one.

A dependable integration includes more than copying rows. It needs a clear ownership boundary, retry behaviour, schema handling and a way to measure freshness.

Start with the required data behaviour

The right integration is chosen by the question the business is asking. If finance needs yesterday’s closed orders every morning, a batch load is usually the clearest option. If an operations team needs a dashboard to reflect the last few minutes, use streaming. If customer, order or inventory records can change after creation, use CDC so inserts, updates and deletes are represented.

ClickHouse Cloud and self-managed ClickHouse

ClickHouse Cloud includes ClickPipes, a managed ingestion service for supported sources such as object storage, Kafka and selected CDC connectors. It is useful when the team wants the platform to operate the connector.

On a company-managed ClickHouse server, the target tables and query patterns are the same, but the ingestion service is your responsibility. Kafka plus a materialized view is common for events; Debezium and Kafka Connect are common for CDC. This gives more control, but monitoring, upgrades and recovery belong in the team’s runbook.

MySQL to ClickHouse: one-time copy, periodic load or CDC?

There are three sensible ways to bring a MySQL table into ClickHouse: a one-time copy for exploration, a scheduled incremental load when a reliable updated_at column exists, or CDC when ClickHouse must reflect inserts, updates and deletes.

CDC has two distinct phases: first copy historical data, then continuously apply database changes.

For MySQL CDC, database prerequisites typically include log_bin = ON, binlog_format = ROW, binlog_row_image = FULL and GTID configuration where the connector requires it. A CDC pipeline normally writes change versions; the data model or query then selects the current version.

CREATE TABLE analytics.customer_current
(
    customer_id UInt64,
    city LowCardinality(String),
    version DateTime64(3, 'UTC'),
    is_deleted UInt8
)
ENGINE = ReplacingMergeTree(version)
ORDER BY customer_id;

Kafka streaming: events into analytical tables

Kafka is a good fit for immutable events such as web activity, payment events, telemetry, logs or application activity. In ClickHouse, a Kafka engine table consumes the topic, and a materialized view places validated rows into a MergeTree-family table. The target table stores the data; the Kafka table is the reader.

CREATE TABLE analytics.events
(
    event_time DateTime64(3, 'UTC'),
    user_id UInt64,
    event_name LowCardinality(String),
    payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time, user_id);

For production, add a dead-letter or error-handling approach, track consumer lag, retain raw payloads where useful, and define replay rules. A dashboard being fast is not enough if an offset reset silently creates duplicate data.

Files in object storage: the portable warehouse pattern

Parquet on S3, GCS or Azure object storage is often the cleanest bridge between platforms. A warehouse can export a curated dataset to Parquet; ClickHouse can load it in bulk and use it for low-latency serving. This is useful for Snowflake and BigQuery migrations because it separates the source platform from the target platform.

INSERT INTO analytics.orders
SELECT *
FROM s3(
  'https://storage.example.com/exports/orders/date=2026-10-05/*.parquet',
  'Parquet'
);

For files arriving continuously, use an ingestion service or queue-style pattern that records which files were processed. Avoid a job that scans every object and reloads everything: it will eventually create duplicate or missed data.

API integration: incremental data needs a cursor

Public and SaaS APIs are commonly paginated, rate-limited and occasionally corrected after the fact. A robust collector requests a defined time or cursor window, writes the response, then stores its new cursor only after the ClickHouse insert succeeds. It also keeps the raw response or an audit reference for debugging.

Snowflake and BigQuery to ClickHouse

For a complete set of warehouse tables, begin with an inventory: owner, refresh interval, primary key or grain, historical volume, incremental key and downstream users. Export selected models or tables to partitioned Parquet in object storage, then load those partitions into ClickHouse. This keeps the process observable and avoids coupling the target to a proprietary source query path.

Check the current ClickHouse integration catalog and documentation for source availability in your region before committing a design.

How to choose the implementation

A production checklist before going live

• Define table grain, unique key and expected data freshness.
• Validate row counts and key totals for the initial load against the source.
• Decide how updates, deletes, replays and late records behave.
• Keep credentials in a secret store, not in SQL files, dashboards or browser URLs.
• Monitor source lag, failed batches, rejected records, insert errors and storage growth.
• Document a backfill procedure and test it on one small partition before full recovery.
• Assign a business owner who can confirm data correctness, not only whether the job is green.

Real-world design examples

E-commerce operations

Orders and customer changes originate in MySQL. A CDC pipeline loads a current-state order table and an immutable change history into ClickHouse. Kafka carries checkout and browsing events; high-volume events remain optimised for time-range scans.

IoT monitoring

Devices publish measurements to Kafka. A materialized view lands clean records in a table ordered by device and timestamp. The team keeps a short raw retention period for troubleshooting and a longer aggregate table for daily and monthly reporting.

Finance reporting

A Snowflake or BigQuery model is exported as daily partitioned Parquet. ClickHouse ingests the newly closed partition and serves embedded reports. The reconciliation process compares period totals before the report is marked ready.

Frequently asked questions

Final takeaway

A successful ClickHouse integration is not defined by the connector alone. It has a clear data contract, a load pattern matching the business need, a target table designed for queries, and evidence that data remains correct after the first successful run. Start with one source and one measurable use case, then standardise the operating pattern before onboarding every table.

Further reading: ClickPipes documentation · MySQL integration · S3 integration · ClickHouse integrations catalog