ClickHouse real-time event ingestion with TTL-driven cleanup

Land high volumes of real-time events in ClickHouse without hand-rolling the retention and aggregation logic yourself. This blueprint bulk-inserts a batch of events using ClickHouse's asynchronous insert mode, relies on a materialized view to keep an hourly rollup current the moment rows land, and forces the raw table's TTL to expire old rows on every run instead of trusting ClickHouse's own background merge timing. It is the pattern for teams who chose ClickHouse specifically for its throughput and built-in expiry, not a generic JDBC warehouse load.

How it works

1. ensure_schema (io.kestra.plugin.jdbc.clickhouse.Queries, transaction: false) idempotently creates raw_events as a MergeTree table partitioned by day with TTL event_time + INTERVAL 7 DAY DELETE, creates events_rollup as an AggregatingMergeTree table, and creates the events_rollup_mv materialized view that feeds events_rollup from raw_events on every insert.
2. events_to_ion (io.kestra.plugin.serdes.json.JsonToIon) converts the incoming NDJSON event batch into Kestra's internal ION format.
3. ingest_events (io.kestra.plugin.jdbc.clickhouse.BulkInsert) bulk-inserts the batch into raw_events in JDBC batches of 5,000 rows, using SETTINGS async_insert = 1, wait_for_async_insert = 1 so ClickHouse queues the rows for an asynchronous background insert rather than committing a synchronous write per batch. Because events_rollup_mv is attached to raw_events, every inserted row is also aggregated into events_rollup automatically, so no separate aggregation step runs after ingestion.
4. enforce_ttl (io.kestra.plugin.jdbc.clickhouse.Queries) runs OPTIMIZE TABLE raw_events FINAL, forcing ClickHouse to apply the table's TTL immediately rather than waiting for its own background merge schedule to expire rows past the 7 day window.
5. preview_rollup (io.kestra.plugin.jdbc.clickhouse.Query) reads events_rollup back with the countMerge and sumMerge combinators required by AggregatingMergeTree, and stores the last 24 hourly buckets with fetchType: STORE.

Connection settings for every ClickHouse task are centralized once in pluginDefaults keyed by the io.kestra.plugin.jdbc.clickhouse group. A flow-level errors block posts a Slack alert if any step fails.

What you get

• A self-provisioning event pipeline: the raw table, rollup table, and materialized view are created on the first run and left alone on every run after.
• Automatic, always-current hourly aggregates with no separate scheduled rollup query, since the materialized view aggregates on insert.
• Bounded storage for raw events: TTL deletes rows older than 7 days, and this flow forces that cleanup to run immediately instead of trusting ClickHouse's background merge timing.
• High-throughput ingestion through ClickHouse's asynchronous insert mode plus chunked JDBC batches.
• A Slack alert on failure, so a bad batch or a broken connection does not fail silently.

Who it's for

• Data engineers building real-time analytics or IoT and event pipelines on ClickHouse.
• Platform teams who need bounded, self-cleaning event storage without a separate retention job.
• Analytics engineers who want always-fresh hourly rollups without a nightly batch aggregation query.

Why orchestrate this with Kestra

ClickHouse's TTL and materialized views handle expiry and aggregation once the DDL is in place, but ClickHouse has no scheduler, no retry policy, and no way to notify a person when an ingestion batch fails to insert. Kestra supplies all of that: retries and timeouts around the JDBC calls, run history so a partial batch is easy to spot, a pluginDefaults block so every ClickHouse task shares one connection definition, and a Slack alert the moment a step fails, none of which ClickHouse provides on its own.

Prerequisites

• A running ClickHouse server reachable at the configured JDBC URL, with permission to create tables and materialized views.
• An upstream process (Kafka Connect, a file-landing job, or a fan-out parent flow) that produces NDJSON batches of events and passes them to this flow as the events_file input.
• A Slack incoming webhook for failure alerts.

Secrets

• CLICKHOUSE_URL: JDBC URL, for example jdbc:clickhouse://host:8123/.
• CLICKHOUSE_USERNAME and CLICKHOUSE_PASSWORD: credentials for the JDBC connection.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL used for failure alerts.

Quick start

1. Add the secrets above to your Kestra namespace.
2. Start ClickHouse locally if needed: docker run -d -p 8123:8123 -p 9000:9000 --name clickhouse-events clickhouse/clickhouse-server.
3. Add this flow and run it once with a small NDJSON sample file as events_file to confirm the schema, rollup, and TTL enforcement all succeed.
4. Query events_rollup (with countMerge and sumMerge) to confirm the materialized view populated it from the batch you just loaded.

How to extend

• Replace the manual events_file input with an object storage trigger or a Schedule trigger that pulls new batches from a landing prefix on a fixed interval.
• Shorten or lengthen the TTL clause in ensure_schema to match your own retention requirements.
• Add more AggregateFunction columns to events_rollup (for example p90 or uniq) to extend the rollup without touching the ingestion path.
• Move OPTIMIZE TABLE ... FINAL into a separate scheduled flow if forcing it on every ingestion run is too heavy for your data volume.

Links

• Kestra ClickHouse plugin: https://kestra.io/plugins/plugin-jdbc-clickhouse