ClickHouse Certified: Ingestion and Integrations

This domain covers getting data in without creating a part explosion: batch sizing, formats, deduplication on retries, streaming engines, file and object-storage ingestion, materialized views as pipelines, and Cloud-specific tooling. Questions usually describe a pipeline and ask what breaks under retry, restart, or high insert frequency.

Batching and formats

• Never insert row-by-row. Target at least ~1,000 rows per INSERT, and commonly 10k–100k+; each INSERT creates a part, and many small parts stall merges.
• Native is the fastest format for ClickHouse-to-ClickHouse transfers; RowBinary is compact; JSONEachRow is the convenient HTTP/streaming default; TSV/CSV parse cheaply; Parquet suits files.
• input_format_skip_unknown_fields = 1 tolerates schema drift; input_format_null_as_default = 1 avoids NULL-type surprises.
• async_insert = 1 with wait_for_async_insert = 1 lets the server coalesce many small client inserts into fewer parts.

Idempotent retries

• insert_deduplicate = 1 (default for Replicated tables) hashes each inserted block; resending the identical block is ignored. It only works if the client retries with byte-identical batch content and size, and it is not available on plain non-replicated MergeTree.

Streaming and external sources

• The Kafka table engine is a consumer: pair it with a materialized view that reads from the Kafka table and writes into a MergeTree table. The MV fires on inserts into its source table and never backfills.
• ClickHouse Cloud does not expose the Kafka engine; use ClickPipes instead. ClickPipes also covers object storage ingestion.
• s3() table function for ad hoc reads, S3Queue engine for continuous file pickup, S3 engine for repeatable queries.
• Refreshable materialized views (REFRESH EVERY ...) recompute a target table on a schedule rather than per insert.
• Dictionaries provide low-latency lookup enrichment via dictGet.

Worked example

Kafka topic of JSON events to a deduplicated table:

1. CREATE TABLE kafka_q ENGINE = Kafka SETTINGS kafka_broker_list='...', kafka_topic_list='events', kafka_group_name='ch', kafka_format='JSONEachRow'
2. CREATE TABLE events (...) ENGINE = ReplacingMergeTree ORDER BY event_id
3. CREATE MATERIALIZED VIEW mv TO events AS SELECT ... FROM kafka_q Offsets commit after the block is written, so a crash in between can replay a batch - ReplacingMergeTree absorbs the duplicates.

Traps that cost marks

• SELECT * FROM kafka_q consumes messages instead of inspecting them.
• Expecting a materialized view to populate historical rows.
• Retrying inserts with re-serialized, non-identical batches and expecting deduplication.