I had workers repeatedly asking ClickHouse the same question: has anything changed? The aggregate was expensive and the polling interval was short, so most queries repeated work only to produce the same answer.

The worse problem was timing. When an aggregate depended on two inputs, a poll could land after the first insert but before the second and publish an intermediate result. Making the query faster did not remove that race. The system needed a change signal.

Polling was the wrong control plane

POLLING
timer
  → run an expensive aggregate
  → discover that nothing changed
  → repeat

or, at the wrong moment:

first input arrives
  → poll computes partial state
  → second input arrives
  → stale result remains until another poll

An application cannot subscribe to a general row-level changefeed from an arbitrary MergeTree table. Polling fills that gap, but query frequency becomes disconnected from actual change frequency.

Emit dirty keys, not derived rows

The useful event is small: an entity identifier and the time of the change. It means “this entity may now be stale,” not “this is the new value.”

EVENT DRIVEN
insert
  → materialized view emits a dirty key
  → consumer batches and deduplicates keys
  → query current ClickHouse state once
  → idempotent bulk upsert

Recomputing from current state makes duplicate events harmless. A consumer can keep the newest event time per key, collapse a burst into one query, and bulk-write the result. The event stream becomes a scheduling mechanism while ClickHouse remains the source of truth.

Webhook after a refresh completes

The simpler case was a refreshable materialized view that rebuilt a complete snapshot on a schedule. A worker only needed to run after that refresh finished. A best-effort signal was sufficient because periodic reconciliation repaired missed notifications.

In this deployment, the view lived in a Replicated database. After the target-table exchange, cleanup of the old table ran through the replicated DDL path and produced a Drop row in system.query_log. A materialized view selected that row and wrote it to a URL engine, turning it into an HTTP request:

CREATE MATERIALIZED VIEW refresh_webhook
ENGINE = URL(
    'https://internal.example/recompute',
    'JSONEachRow'
)
AS
SELECT 1 AS refresh_complete
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Drop'
  AND log_comment = 'refresh of analytics_snapshot_mv';

The handler then read the completed snapshot and bulk-upserted the serving store. No cron job had to guess whether the refresh was done.

This is deployment-specific and best-effort. It depends on the Replicated-database DDL path and the observed ClickHouse version;system.query_log is asynchronous, cleanup or logging failure can omit the signal, and multiple replicas can emit duplicates. Verify the matched fields after upgrades, keep the endpoint private or authenticated, make the handler idempotent, and retain reconciliation.

Kafka for frequent per-key changes

HTTP becomes awkward when inserts are frequent and many keys change at once. For that path, a normal insert-triggered materialized view wrote dirty keys into a Kafka-engine table. ClickHouse acted as the Kafka producer:

CREATE TABLE stat_refresh_events
(
    entity_id String,
    event_time Int64
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'broker:9092',
    kafka_topic_list = 'stat_refresh_events',
    kafka_group_name = 'stat_resolver',
    kafka_format = 'JSONEachRow';

CREATE MATERIALIZED VIEW emit_stat_refresh
TO stat_refresh_events
AS
SELECT
    entity_id,
    toUnixTimestamp64Milli(now64()) AS event_time
FROM changed_state
GROUP BY entity_id;

The consumer deduplicated each fetched batch by key, retained the newest event time, ran one ClickHouse query for the remaining keys, and bulk-upserted the results. Offsets advanced only after successful, idempotent processing.

Kafka added buffering, replay, backpressure, and natural batching. A burst of hundreds of inserts could become one recomputation per affected key instead of hundreds of immediate queries.

Materialized views are not CDC

An incremental materialized view sees inserted blocks from its source table. It does not automatically react when another table referenced by a join changes, and it does not describe later merges or mutations.

Where the derived value depended on two changing inputs, I used one trigger view per input. A small periodic reconciliation job remained as a backstop for cross-table timing gaps and missed events. The event path removed constant polling; reconciliation protected correctness.

Choosing between them

  • Use an HTTP trigger for one coarse, infrequent refresh with a clear completion point.
  • Use Kafka for frequent per-key changes that need buffering, retries, replay, and batch deduplication.
  • Keep consumers idempotent and retain a reconciliation path in either design.

Rule of thumb

Do not ask ClickHouse whether something changed every second. Emit a cheap invalidation when it changes, batch those invalidations, then query the current truth once.