From Nightly Fires to Boring Reliability: Rebuilding a Kafka Ingest Pipeline

Problem Statement

The pipeline was a single Python script with a while True loop, running on one EC2 instance, writing to Snowflake with plain INSERTs. Retry after a crash? Duplicates. Schema change upstream? Silent failure until Monday. The team measured reliability in “nights since last 2 a.m. page.”

System Architecture & Tradeoffs

The central decision: exactly-once semantics vs. at-least-once delivery with idempotent sinks.

Exactly-once (Kafka transactions + Snowpipe Streaming offsets) is elegant but operationally heavy — transactional producers complicate every future consumer. Idempotent upserts keyed on event_id give the same user-visible correctness with a far simpler failure model: any stage can be retried at any time, by anyone, without fear.

New topology: Kafka → containerized consumers (Kubernetes, autoscaling on lag) → Snowpipe Streaming with event_id merge → DLQ topic for poison messages → one-command replay tool.

Implementation Details

Idempotent sink — the merge key does the work:

merge into analytics.events t
using (select * from {{ ref('stg_events_stream') }}) s
on t.event_id = s.event_id
when not matched then insert values (...);

Consumer with bounded batches and explicit checkpointing:

async for batch in consumer.take(max_records=500, within_seconds=1.0):
    try:
        await snowpipe.ingest(batch, dedupe_key="event_id")
    except RetryableError:
        await dlq.send(batch, reason=traceback.format_exc())
        raise  # offset not committed; batch replays
    await consumer.commit()

Replay tooling — one command, used more often than anyone expected (by design, safely):

./replay --topic orders-dlq --from 2025-06-01 --dry-run

SLO-based alerting: freshness checks in dbt (warn_after: 10 min) plus Kafka lag burn-rate alerts. We deleted 14 noisy threshold alerts; the two that remained page only when the SLO is actually at risk.

Business ROI

The best review came from the on-call rotation: three consecutive months without a single data-platform page.