Estuary Flow vs Streamkap vs Kafka Connect: Real-Time ELT in 2026

estuary flow is the pick-one architectural decision that finally lets a data platform run sub-second ELT without owning a Kafka cluster — and it is the single evaluation senior data engineers get wrong most often because "just use Fivetran" is a batch answer to a streaming question. Every operational database mutation your business writes — an order state change, a customer sign-up, a soft-deleted product row — has to reach the warehouse fast enough that dashboards reflect reality, the reverse-ETL layer disappears, and the operational-analytics workloads that used to live in a bespoke event bus can now live in Snowflake, BigQuery, or Databricks. The engineering trade-off does not live in "should we do real-time ELT" — every 2026 stack with more than one downstream consumer needs it — but in which real-time ELT tool you pick and what it costs to run over three years.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through estuary flow vs streamkap vs Kafka Connect and where each wins", or "your warehouse team wants sub-second freshness but has zero Kafka operators — what do you pick?", or "explain the real-time elt decision matrix and why managed streaming beats DIY at small scale but loses at very large scale." It walks through the three canonical stacks — Estuary Flow (managed streaming ELT on the open-source gazette broker with SQLite-friendly derivations), Streamkap (managed Debezium-based CDC → Snowflake / BigQuery / Databricks with autoscaled connectors), and Kafka Connect (DIY on Debezium sources + JDBC / Snowflake / BigQuery sinks with SMTs, DLQs, and a Distributed-mode worker fleet you own) — the four axes interviewers actually probe (latency, exactly-once, schema evolution, TCO), and the hybrid patterns where teams run Streamkap for the boring Postgres feed and Kafka Connect for the custom SaaS connector. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the streaming practice library →, and sharpen the system-design axis with the design practice library →.

---

On this page

• Why real-time ELT matters in 2026
• Estuary Flow deep dive
• Streamkap deep dive
• Kafka Connect (DIY)
• Decision matrix + when to pick each + interview signals
• Cheat sheet — Real-time ELT recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why real-time ELT matters in 2026

Batch ELT is the 2020 answer to a streaming question — the 2026 platform lands data in the warehouse sub-second

The one-sentence invariant: real-time ELT is the pattern where source-database mutations reach the warehouse (or lakehouse) at sub-second cadence via CDC → optional in-flight transform → warehouse-native MERGE, and the tool choice — managed streaming SaaS like Estuary Flow, managed CDC-to-warehouse like Streamkap, or DIY Kafka Connect on Debezium — trades operational burden against connector breadth, exactly-once guarantees, schema-evolution safety, and three-year total cost of ownership in a way that binds every downstream dashboard, feature store, and reverse-ETL job for years. The tool you pick in month one becomes the tool you fight to migrate away from in year three because every warehouse table, every dbt model, every BI dashboard, and every alerting rule silently hard-codes latency assumptions against your CDC cadence.

The four axes interviewers actually probe.

• Latency. Fivetran defaults to 15-minute sync intervals; Airbyte defaults to hourly; both are batch-first. real-time elt means sub-second p95 from source commit to warehouse row visibility. Estuary Flow ships sub-second by design; Streamkap ships single-digit-second by design; Kafka Connect ships whatever your worker fleet and sink batching allow — typically 1-5 seconds with tuning. Interviewers open here because it separates people who know the streaming-vs-batch inflection point from people who conflate them.
• Exactly-once semantics. Estuary Flow is exactly-once by design (gazette's two-phase commit contract combined with idempotent materializations). Streamkap is at-least-once by default with idempotent MERGE on the sink side. Kafka Connect is at-least-once out of the box — exactly-once requires wiring transactional producers, careful consumer offset semantics, and idempotent sinks. This is the interview question that separates architects from implementers.
• Schema evolution. A column add on the source should propagate to the warehouse without a pipeline outage. Estuary Flow versions collection schemas and evolves the materialization automatically. Streamkap handles column add / column drop / type widening natively via managed schema evolution. Kafka Connect requires a schema registry + a compatibility mode + sink-connector configuration to survive non-additive changes.
• Total cost of ownership. Managed tools charge on data volume or connector-hours; DIY Kafka Connect trades platform cost for engineer-hours. The crossover point at scale (>10 TB/day CDC throughput, >200 tables) generally favours DIY; below that, managed usually wins on true TCO once you count on-call, upgrades, and one senior engineer's fraction of time.

The 2026 reality — three stacks, distinct sweet spots.

• Estuary Flow is the default for teams that want managed streaming without giving up in-flight transforms and want exactly-once end-to-end. The open-source gazette broker underneath is public; the managed control plane sells you deploy + observability + connector library. Sweet spot: 5-500 tables, mixed CDC + SaaS sources, transforms non-trivial (aggregations, joins, filters), sub-second freshness required.
• Streamkap is the default for teams whose only real requirement is Postgres/MySQL → Snowflake / BigQuery / Databricks with the least possible operational surface. UI-driven config, autoscaled Debezium under the hood, managed schema evolution, MERGE-based sink. Sweet spot: 5-100 CDC tables, warehouse-only downstream, no in-flight transforms, single-digit-second freshness acceptable.
• Kafka Connect is the default for teams with a dedicated streaming platform team, a pre-existing Kafka footprint, and either bespoke connectors that no managed tool has or an enormous CDC volume where managed pricing becomes prohibitive. You own everything: worker fleet, offsets, DLQs, upgrades, security patches. Sweet spot: >10 TB/day CDC, custom source connectors, integrated with an existing event bus.

What interviewers listen for.

• Do you distinguish "streaming ELT" from "streaming ETL" — the T is inside the warehouse for ELT, in-flight for ETL — without prompting? — senior signal.
• Do you name exactly-once as a system-level property, not "at-least-once with dedupe"? — required answer.
• Do you push back on "just use Kafka Connect because it's free" with a TCO calculation that includes engineer-hours? — senior signal.
• Do you name schema evolution as the load-bearing operational property, not "it just works"? — required answer.
• Do you describe real-time elt as "CDC → warehouse-native MERGE at sub-second cadence" rather than as vague "streaming pipelines"? — required answer.

Worked example — the four-axis comparison table

Detailed explanation. The single most useful artifact for a real-time ELT interview is a memorised 3×4 comparison table (three tools × four axes). Every senior real-time-ELT discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical retail company that needs to land orders, customers, and inventory from Postgres into Snowflake with sub-second freshness.

• Source. Postgres 16, three tables (orders, customers, inventory), combined DML rate ~300/sec.
• Downstream. Snowflake, single warehouse, standard MERGE semantics.
• Target latency. p95 under 5 seconds from source commit to Snowflake row visibility.
• Constraints. Two data engineers, no dedicated streaming platform team, three-year TCO budget of ~$150k/year all-in.

Question. Build the three-tool comparison for the retail workload and pick the tool that satisfies every constraint.

Input.

Axis	Estuary Flow	Streamkap	Kafka Connect (DIY)
Latency p95	sub-second	1-5 seconds	1-10 seconds (tuning-dependent)
Exactly-once	yes (native)	at-least-once + idempotent MERGE	at-least-once by default; EOS requires wiring
Schema evolution	automatic (collection versioning)	automatic (managed)	requires schema registry + config
TCO at 300 DML/s	~$40k/year (managed)	~$30k/year (managed)	~$50-80k/year (mostly engineer time)
Ops burden	low (managed control plane)	very low (UI-driven)	high (own the worker fleet)
Connector breadth	~200 (SaaS + DB)	~50 (DB + warehouse-focused)	~200+ (open ecosystem)

Code.

# One YAML per tool showing the same Postgres → Snowflake pipeline

# 1. Estuary Flow catalog snippet
captures:
  acme/postgres-orders:
    endpoint:
      connector:
        image: ghcr.io/estuary/source-postgres:v1
        config: { host: db.internal, database: production }
    bindings:
      - resource: { name: public.orders }
        target: acme/orders
materializations:
  acme/snowflake-warehouse:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-snowflake:v1
        config: { account: acme, warehouse: LOAD_WH, database: RAW, schema: PUBLIC }
    bindings:
      - source: acme/orders
        resource: { table: orders }

# 2. Streamkap pipeline (equivalent UI-driven config exported as JSON)
{ "source": "postgres-prod",
  "destination": "snowflake-raw",
  "tables": ["public.orders", "public.customers", "public.inventory"],
  "snapshot_mode": "initial",
  "sync_mode": "streaming" }

# 3. Kafka Connect — one Debezium source + one Snowflake sink
# (Debezium source config — see H2-4 for the full form)
name: pg-orders-source
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  slot.name: dbz_orders
  publication.name: orders_pub
  table.include.list: public.orders,public.customers,public.inventory
---
name: snowflake-sink
config:
  connector.class: com.snowflake.kafka.connector.SnowflakeSinkConnector
  topics: prod.public.orders,prod.public.customers,prod.public.inventory
  snowflake.url.name: acme.snowflakecomputing.com
  buffer.count.records: 10000
  buffer.flush.time: 5

Step-by-step explanation.

1. The Estuary Flow snippet defines a capture (source), a collection (durable log inside gazette), and a materialization (sink). One YAML file drives the entire pipeline; flowctl publish deploys it. Sub-second latency and exactly-once are the default, not a config knob.
2. The Streamkap config is a JSON export of a UI-driven pipeline; the actual click-through in the Streamkap console produces this. Snapshot then streaming is the default; the managed control plane handles the Debezium slot, the internal Kafka buffer, and the MERGE-based Snowflake sink transparently.
3. The Kafka Connect config is two connectors: a Debezium Postgres source that emits change events to Kafka topics, and a Snowflake sink connector that consumes them and lands rows in Snowflake. You own the Connect worker fleet, the Kafka cluster, and the operational integration between them.
4. All three achieve the same functional outcome — Postgres CDC lands in Snowflake sub-second — but the operational surface differs by an order of magnitude. The right pick for the retail team above is Streamkap (least surface, meets latency, cheapest at this scale) or Estuary Flow (if in-flight transforms are on the roadmap). Kafka Connect at this scale is over-engineered.
5. At 30 TB/day CDC throughput the answer flips: Kafka Connect wins on unit economics, Estuary Flow's managed pricing becomes uncompetitive, and Streamkap's per-table pricing model doesn't fit the shape. The decision is genuinely scale-dependent.

Output.

Requirement	Winning tool	Why
Sub-second freshness, minimal ops	Streamkap	UI-driven, managed, meets 5s target easily
Sub-second + in-flight transforms	Estuary Flow	derivations run in-flight; exactly-once end-to-end
Custom source connector + huge scale	Kafka Connect	own the ecosystem; unit economics favour DIY at >10 TB/day
Warehouse-only + small team	Streamkap	least operational surface
Mixed CDC + SaaS + transforms	Estuary Flow	connector library + derivations

Rule of thumb. For real-time ELT tool selection, walk the (latency × exactly-once × schema evolution × TCO) matrix on a whiteboard before naming a tool. Managed tools win at small-to-mid scale; DIY wins at very large scale or when custom connectors force your hand. Never pick a tool because it's trendy or because a vendor's rep suggested it.

Worked example — what senior interviewers actually probe

Detailed explanation. The senior real-time-ELT interview has a predictable structure: the interviewer opens with an ambiguous question ("how would you build a real-time pipeline from our OLTP into the warehouse?"), then progressively narrows to test whether you know the axes and the trade-offs. Candidates who name a specific tool with justification in sentence one score highest; candidates who describe "a Kafka pipeline" without specifying source/sink connectors score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you build a real-time CDC pipeline from Postgres to Snowflake?" — invites you to name a tool with justification.
• Follow-up 1. "Why not Fivetran or Airbyte?" — probes streaming-vs-batch understanding.
• Follow-up 2. "What's your exactly-once story?" — probes semantic guarantees.
• Follow-up 3. "How does schema evolution work?" — probes operational maturity.
• Follow-up 4. "How does this scale to 10 TB/day?" — probes when the tool choice flips.

Question. Draft a 5-minute senior real-time-ELT answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Tool named	"we'd use Fivetran"	"Streamkap for the CDC path, Estuary Flow if we need in-flight transforms"
Batch vs streaming	"Fivetran syncs frequently"	"Fivetran defaults to 15-minute batches; real-time means sub-second; different tool category"
Exactly-once	"we could dedupe"	"Estuary Flow is EOS by design; Streamkap is at-least-once + idempotent MERGE"
Schema evolution	"we'd rebuild the pipeline"	"managed tools handle column add / type widening natively; Kafka Connect needs schema registry"
Scale flip	"Kafka is fastest"	"managed wins TCO at <5 TB/day, DIY Kafka Connect wins above that or when custom connectors are needed"

Code.

Senior real-time-ELT answer template (5 minutes)
=================================================

Minute 1 — name the tool with a one-line justification
  "Default: Streamkap for warehouse-only CDC; Estuary Flow if we need
   in-flight transforms; Kafka Connect if we're >10 TB/day CDC or need
   a bespoke source connector."

Minute 2 — batch vs streaming
  "Fivetran/Airbyte default to 15-min to hourly sync intervals. That's
   batch ELT. Real-time means sub-second p95 from source commit to
   warehouse row visibility — a fundamentally different tool category
   built on CDC + streaming rather than periodic snapshots."

Minute 3 — exactly-once
  "Estuary Flow ships exactly-once end-to-end via gazette's two-phase
   commit + idempotent materializations. Streamkap is at-least-once at
   the buffer + idempotent MERGE by PK at the sink — effectively
   exactly-once for warehouse purposes. Kafka Connect out of the box is
   at-least-once; EOS requires transactional producers, careful offset
   semantics, and idempotent sinks — real work."

Minute 4 — schema evolution
  "Estuary Flow versions collection schemas; column add propagates
   through the materialization automatically. Streamkap handles column
   add / drop / type widening via managed schema evolution. Kafka
   Connect needs a schema registry (Confluent / Apicurio) + BACKWARD
   compatibility mode + sink-connector config to survive non-additive
   changes without downtime."

Minute 5 — TCO scale flip
  "At <5 TB/day CDC, managed tools (Estuary Flow or Streamkap) beat
   DIY on true TCO once you count on-call, upgrades, and the fraction
   of a senior engineer. Between 5-30 TB/day, it depends on whether
   you already have a Kafka platform team. Above 30 TB/day or with
   bespoke source connectors, Kafka Connect wins on unit economics."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming a tool with a one-line justification — "Streamkap for warehouse-only CDC; Estuary Flow for in-flight transforms; Kafka Connect for scale or custom connectors" — signals you're a decision-maker, not a task-runner. Weak candidates say "we'd evaluate several tools" and never commit.
2. Minute 2 addresses the batch-vs-streaming axis before the interviewer asks. This preempts the common trap where you name a real-time tool, then admit you don't know why Fivetran doesn't count. Fivetran is excellent — for batch ELT.
3. Minute 3 is the exactly-once probe. Naming EOS as a system-level property (not "at-least-once with dedupe") and knowing which tool ships which guarantee is senior signal. The Kafka Connect nuance — EOS is possible but requires real work — is the highest signal.
4. Minute 4 is the schema evolution probe. Managed tools handle it transparently; Kafka Connect needs explicit setup. Knowing that a column add can cause a downstream outage if the sink connector isn't configured for compatibility is the difference between "we tested it once" and "we run this in production."
5. Minute 5 is the scale-flip argument. TCO comparison at different scales is the senior architect's answer. Never claim one tool is universally best; the crossover point is real and depends on volume + custom-connector needs.

Output.

Grading criterion	Weak score	Senior score
Names tool in minute 1	rare	mandatory
Distinguishes batch vs streaming	occasional	required
Names exactly-once system-level	rare	mandatory
Names schema evolution mechanism	rare	senior signal
Names TCO scale flip	very rare	senior signal

Rule of thumb. The senior real-time-ELT answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once; deploy it every time. Managed tools are the default; DIY Kafka Connect is the specialised choice.

Worked example — the "pick the tool" decision tree

Detailed explanation. Given a new real-time ELT requirement, the senior architect runs a 5-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios: a mid-market SaaS with 20 CDC tables, a fintech with regulatory in-flight transforms, and a hyperscaler with 50 TB/day.

• Q1. Do you need in-flight transforms (aggregations / joins / masks) before the warehouse? → yes = Estuary Flow; no = go to Q2.
• Q2. Is your downstream only Snowflake / BigQuery / Databricks? → yes = go to Q3; no = go to Q4.
• Q3. Is CDC throughput under ~5 TB/day and connector-count under ~100? → yes = Streamkap; no = go to Q4.
• Q4. Do you have or can hire a dedicated streaming platform team? → yes = Kafka Connect; no = Estuary Flow (fallback).
• Q5 (parallel branch). Do you have a bespoke source connector no vendor supports? → yes = Kafka Connect (regardless of everything else).

Question. Walk the decision tree for the three scenarios and record the tool each ends up with.

Input.

Scenario	Q1 (transforms?)	Q2 (warehouse-only?)	Q3 (small scale?)	Q4 (platform team?)	Q5 (custom conn?)
Mid-market SaaS	no	yes	yes	no	no
Fintech + PII masks	yes	yes	yes	no	no
Hyperscaler 50TB/day	no	yes	no	yes	no

Code.

# Decision-tree helper (illustrative)
def pick_realtime_elt(needs_transforms: bool,
                       warehouse_only: bool,
                       small_scale: bool,
                       has_platform_team: bool,
                       needs_custom_connector: bool) -> str:
    """Return the primary real-time ELT tool for a workload."""
    if needs_custom_connector:
        return "kafka-connect (Q5 override)"

    if needs_transforms:
        return "estuary-flow"

    if warehouse_only and small_scale:
        return "streamkap"

    if has_platform_team:
        return "kafka-connect"

    return "estuary-flow (fallback)"


# Walk the three scenarios
print(pick_realtime_elt(False, True,  True,  False, False))
# → 'streamkap'

print(pick_realtime_elt(True,  True,  True,  False, False))
# → 'estuary-flow'

print(pick_realtime_elt(False, True,  False, True,  False))
# → 'kafka-connect'

Step-by-step explanation.

1. Scenario 1 — Mid-market SaaS with 20 CDC tables, no in-flight transforms, Snowflake-only downstream, small ops team. The tree short-circuits at Q3 → Streamkap. This is the modern default for the "just get Postgres into Snowflake" workload.
2. Scenario 2 — Fintech with PII masking / tokenisation required before warehouse landing (regulatory). Q1 = yes → Estuary Flow. The derivation layer runs the masks in-flight; the warehouse never sees the raw PII. This is the pattern that convinces regulated industries to adopt managed streaming.
3. Scenario 3 — Hyperscaler with 50 TB/day CDC, warehouse-only, dedicated streaming platform team. Q3 = no (too large), Q4 = yes → Kafka Connect. At this scale, managed pricing loses to DIY unit economics; the platform team can absorb the ops overhead.
4. The parallel Q5 branch (custom connector) is orthogonal to the primary decision. If you need a bespoke source that no managed vendor supports, Kafka Connect wins regardless of Q1-Q4. This is the "we integrate with a proprietary mainframe" answer.
5. If none of the branches produce a clean answer, the fallback is Estuary Flow — it has the broadest applicability (transforms + managed + reasonable pricing at most scales). Never say "it depends" without walking the tree; the tree is the answer.

Output.

Scenario	Tool	Primary rationale
Mid-market SaaS	Streamkap	warehouse-only + small scale = least surface
Fintech + PII masks	Estuary Flow	in-flight transforms required
Hyperscaler 50TB/day	Kafka Connect	scale flip + platform team
No clean fit	Estuary Flow (fallback)	broadest sweet spot

Rule of thumb. The five-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get a tool name in under 60 seconds. The decision is scale + transforms + warehouse-only + platform-team + custom-connector — five inputs, one answer.

Senior interview question on real-time ELT tool selection

A senior interviewer often opens with: "You inherit a batch Fivetran pipeline that syncs 40 Postgres tables into Snowflake every 15 minutes. The exec team wants sub-second freshness because a new operational dashboard depends on it. Walk me through the tool you'd evaluate to replace Fivetran, the axes you'd score against, and the migration risk you'd manage."

Solution Using an axis-scored evaluation with Streamkap as the primary and Estuary Flow as the fallback

# eval_realtime_elt.py — axis-scored tool evaluation for the 40-table workload
from dataclasses import dataclass

@dataclass
class ToolScore:
    tool: str
    latency: int         # 1-5, higher is better
    exactly_once: int
    schema_evolution: int
    tco_at_scale: int    # scale = 40 tables, ~300 DML/s
    ops_burden: int      # higher = less burden
    connector_breadth: int

CANDIDATES = [
    ToolScore("streamkap",     5, 4, 5, 5, 5, 3),
    ToolScore("estuary-flow",  5, 5, 5, 4, 4, 5),
    ToolScore("kafka-connect", 4, 3, 3, 3, 2, 5),
]

def total(t: ToolScore) -> int:
    return (t.latency + t.exactly_once + t.schema_evolution
            + t.tco_at_scale + t.ops_burden + t.connector_breadth)

def score():
    return sorted(CANDIDATES, key=total, reverse=True)

if __name__ == "__main__":
    for t in score():
        print(f"{t.tool:15s} total={total(t)}")
    # → streamkap       total=27
    # → estuary-flow    total=28   ← winner by 1
    # → kafka-connect   total=20

# 12-week migration plan (config-first, per-table cutover)
week_01_02:
  - Provision Streamkap trial + Snowflake target schema `RAW_STREAMKAP`
  - Run parity harness (Fivetran-loaded table vs Streamkap-loaded table)
week_03_04:
  - Cut over 5 non-critical tables to Streamkap; keep Fivetran running in parallel
week_05_08:
  - Cut over 25 more tables in batches of 5; measure end-to-end latency
week_09_10:
  - Cut over final 10 critical tables; run parity for 7 days
week_11_12:
  - Disable Fivetran pipeline; monitor for 30 days before decommission

-- Snowflake side — one MERGE pattern per table, idempotent by PK
MERGE INTO analytics.orders AS tgt
USING (SELECT * FROM raw_streamkap.orders) src
ON tgt.id = src.id
WHEN MATCHED AND src.__streamkap_op = 'd' THEN DELETE
WHEN MATCHED AND src.__updated_at > tgt.__updated_at THEN UPDATE SET
    customer_id = src.customer_id,
    status      = src.status,
    total_cents = src.total_cents,
    __updated_at = src.__updated_at
WHEN NOT MATCHED THEN INSERT (id, customer_id, status, total_cents, __updated_at)
                       VALUES (src.id, src.customer_id, src.status, src.total_cents, src.__updated_at);

Step-by-step trace.

Step	Before (Fivetran)	After (Streamkap primary)
Latency p95	15 min	1-3 sec
Freshness gap	15-30 min	sub-5-sec
Delete visibility	yes (managed)	yes (op='d' in stream)
Snowflake sync	full-table refresh per table	MERGE by PK
Migration risk	(baseline)	per-table cutover, parallel run, parity harness
Rollback	disable Streamkap pipeline	revert Fivetran cadence
TCO year 1	~$25k Fivetran	~$28k Streamkap + $8k one-time migration

After the migration, the 40 tables land in Snowflake within ~2 seconds of source commit, the operational dashboard hits its freshness SLA, and Fivetran can be decommissioned after 30 days of parallel run. Estuary Flow is documented as the fallback if Streamkap fails a critical requirement (e.g. a needed connector or transform requirement surfaces).

Output:

Metric	Before (Fivetran)	After (Streamkap)
Warehouse freshness p95	15 min	~2 sec
Sync mechanism	periodic full/incremental	continuous CDC + MERGE
Schema evolution	requires connector rebuild	managed (add/drop/widen)
TCO year 1	$25k	$36k (transition year)
TCO year 2+	$25k	$28k
Migration duration	(baseline)	12 weeks

Why this works — concept by concept:

• Axis-scored evaluation — comparing tools on (latency, exactly-once, schema evolution, TCO, ops burden, connector breadth) turns vague preferences into a scoreable rubric. Every senior architect ships one of these before signing off on the tool choice; the write-up is the artefact that survives the meeting.
• Per-table cutover with parallel run — never cut over 40 tables at once. Batches of 5 with a 7-day parity window catches "the sink connector encodes decimals differently" bugs before they hit production. Parallel Fivetran run is the rollback safety net.
• Idempotent MERGE by PK — the sink-side MERGE by PK plus the __streamkap_op column plus the __updated_at guard makes replay safe. Re-applying the same CDC batch produces the same result — the correctness contract for at-least-once → effectively-exactly-once at the warehouse.
• Parity harness — before every cutover, an automated job compares the Fivetran-loaded table to the Streamkap-loaded table (row count + PK diff + column-value hash). Discrepancies block the cutover. This is the operational-maturity contract that separates a real migration from a "we tested it once" hope.
• Cost — one Streamkap tenant, one Snowflake target schema, one parity harness (reusable), 12 weeks of one engineer's time for the migration itself. Compared to Fivetran, the transition-year cost is ~$11k higher; steady-state cost is ~$3k higher; the freshness gain is 500× (15 min → 2 sec). Net O(1) per source commit versus O(N) per sync interval. Estuary Flow is one config swap away if Streamkap ever hits a wall.

SQL
Topic — sql
SQL CDC-MERGE and idempotent-sink problems

Practice →

Streaming Topic — streaming Streaming real-time ELT and CDC problems

Practice →

---

2. Estuary Flow deep dive

Estuary Flow is a managed streaming SaaS on the open-source gazette broker — captures, collections, materializations, and SQLite-friendly derivations

The mental model in one line: estuary flow is the managed streaming ELT platform where a capture connector reads mutations from a source (Postgres, MySQL, Mongo, Kafka, SaaS APIs), writes them into a durable collection backed by the open-source gazette broker, an optional derivation transforms rows in-flight using SQLite-friendly SQL, and a materialization connector sinks the result into a warehouse or lakehouse — the whole path is exactly-once by design, sub-second by default, and configured through a single YAML catalog with the flowctl CLI. Every senior data engineer evaluating managed streaming ELT in 2026 has to know the collection/derivation/materialization triad because it is what distinguishes Estuary Flow from every other CDC-to-warehouse tool.

The four building blocks — captures, collections, derivations, materializations.

• Captures. Source connectors that read mutations and write them into collections. Postgres capture uses logical replication (pgoutput), MySQL capture uses binlog, Mongo capture uses change streams. SaaS captures (Salesforce, HubSpot, Stripe) poll or webhook. Every capture is a durable process managed by the Flow control plane.
• Collections. Immutable, durable, ordered logs of JSON documents backed by the gazette broker (open-source, gRPC-based, similar in shape to Kafka topics but with stronger transactional semantics). Each collection has a JSON schema; every document is schema-validated on write. Retention is configurable per collection.
• Derivations. Optional in-flight transforms written in either SQL (SQLite dialect, run inside Flow's runtime) or TypeScript. A derivation reads from one or more source collections and writes to a derived collection. Derivations run stateful (grouped aggregations, joins) with exactly-once semantics via gazette's two-phase commit.
• Materializations. Sink connectors that read from a collection and write to a target system (Snowflake, BigQuery, Databricks, Postgres, Elasticsearch, DynamoDB, S3, and dozens more). The materialization maintains a per-target checkpoint so restarts resume exactly where they left off — no dedupe required downstream.

The gazette broker underneath — why exactly-once actually works.

• What it is. An open-source, gRPC-based, transactional streaming broker written in Go. It's Kafka-shaped (topic-like journals, offsets, consumers) but with a stronger commit contract: writes are two-phase, so a producer's transaction either commits all documents or none, and consumers see them atomically.
• Why it matters. Kafka's exactly-once semantics require transactional producers and idempotent consumers, and the interplay is famously tricky. Gazette's two-phase commit makes the same guarantee at the broker level; the client contract is simpler.
• What it costs. Gazette is open-source and can be self-hosted; but running it in production requires the same operator investment as Kafka. Estuary Flow's managed service is what most teams buy — the broker under the hood is public, the operational burden is not yours.

The flowctl CLI + YAML catalog — the developer experience.

• The catalog. A YAML file (or set of files) describing captures, collections, derivations, and materializations. Version-controllable, reviewable, deployable via CI.
• The CLI. flowctl publish catalog.flow.yaml deploys the catalog. flowctl catalog list shows deployed resources. flowctl collections read streams a collection to stdout for debugging.
• The dev loop. Edit YAML, run flowctl test against a local snapshot, publish. The whole thing is git-friendly — the operational contract is code, not clicks.

Pricing model — connector-hours + data volume.

• Task-hours. Each running connector (capture / derivation / materialization) accrues task-hours per second while running. Multiple bindings per connector = one task.
• Data volume. Bytes processed through collections, priced per GB. In-flight transforms don't double-count (the input volume is what's billed).
• Sweet spot. Small-to-mid workloads (5-500 tables, 100 GB - 5 TB/day) get predictable pricing. Very large workloads (>10 TB/day) tip toward DIY on unit economics.

Common interview probes on Estuary Flow.

• "What does Estuary Flow give you that Fivetran doesn't?" — required answer: sub-second latency + in-flight transforms + exactly-once by default.
• "How does Estuary Flow do exactly-once?" — gazette's two-phase commit + idempotent materialization checkpoints.
• "What's a derivation?" — SQL (or TypeScript) transform between collections; stateful, exactly-once, runs in Flow's runtime.
• "When would you not pick Estuary Flow?" — warehouse-only with no transforms (Streamkap wins on TCO) or >30 TB/day (Kafka Connect wins on unit economics).

Worked example — a Postgres capture + Snowflake materialization catalog

Detailed explanation. The canonical Estuary Flow pipeline: capture from Postgres via logical replication, land in one or more collections, materialize into Snowflake via the native materialization connector. Walk through the YAML catalog end-to-end and the flowctl publish flow.

• Source. Postgres 16 with wal_level=logical and a REPLICATION role.
• Collection. acme/orders with a JSON schema derived from the source table.
• Materialization. Snowflake with an idempotent MERGE-based load pattern.
• Deploy. flowctl publish catalog.flow.yaml.

Question. Write the complete Flow catalog YAML for the Postgres → Snowflake pipeline and describe what happens at publish time.

Input.

Component	Value
Source	Postgres 16 (production)
Tables	public.orders, public.customers
Broker	managed gazette (Estuary control plane)
Sink	Snowflake account acme, database RAW_FLOW, schema PUBLIC
CLI	flowctl publish

Code.

# catalog.flow.yaml — one file, whole pipeline
captures:
  acme/postgres-cdc:
    endpoint:
      connector:
        image: ghcr.io/estuary/source-postgres:v1
        config:
          address: db-primary.internal:5432
          database: production
          user: flow_reader
          password_sops: sops://kv/prod/flow-postgres
          slot_name: estuary_flow
          publication_name: flow_pub
    bindings:
      - resource:
          namespace: public
          stream: orders
        target: acme/orders
      - resource:
          namespace: public
          stream: customers
        target: acme/customers
    interval: 0s          # 0s = as fast as possible (sub-second)

collections:
  acme/orders:
    schema:
      type: object
      properties:
        id:            { type: integer }
        customer_id:   { type: integer }
        total_cents:   { type: integer }
        status:        { type: string  }
        created_at:    { type: string, format: date-time }
        updated_at:    { type: string, format: date-time }
      required: [id, customer_id, total_cents, status]
    key: [/id]

  acme/customers:
    schema:
      type: object
      properties:
        id:      { type: integer }
        email:   { type: string  }
        name:    { type: string  }
      required: [id, email]
    key: [/id]

materializations:
  acme/snowflake-raw:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-snowflake:v1
        config:
          host: acme.snowflakecomputing.com
          account: acme
          user: flow_writer
          password_sops: sops://kv/prod/flow-snowflake
          database: RAW_FLOW
          schema: PUBLIC
          warehouse: LOAD_WH
          role: FLOW_WRITER
    bindings:
      - source: acme/orders
        resource: { table: orders }
        fields:
          recommended: true
      - source: acme/customers
        resource: { table: customers }
        fields:
          recommended: true

# Deploy
flowctl catalog publish --source catalog.flow.yaml

# Verify
flowctl catalog list --prefix acme/

# Stream the orders collection to stdout for a quick smoke test
flowctl collections read --collection acme/orders --limit 10

Step-by-step explanation.

1. The captures/acme/postgres-cdc block declares a Postgres capture using logical replication. slot_name and publication_name map 1:1 to the Postgres replication slot and publication objects — Flow creates them on first publish. interval: 0s runs as-fast-as-possible; a non-zero interval throttles capture polling for bursty low-volume sources.
2. The collections blocks declare the shape and key of each collection. key: [/id] tells gazette which JSON pointer(s) form the primary key; documents with the same key overwrite (last-write-wins with LSN ordering). The JSON schema is validated on write; a source row that violates it is rejected to a per-collection error stream, not silently dropped.
3. The materializations/acme/snowflake-raw block declares the Snowflake sink. fields: recommended: true tells the connector to sync every column present in the collection schema. Behind the scenes, the connector runs a MERGE INTO ... USING <staging> ON id = id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... pattern with a Snowflake stream + task for micro-batched loads.
4. flowctl catalog publish sends the catalog to the Flow control plane, which validates it, provisions the connectors, and starts them. On first start the Postgres capture runs a bootstrap snapshot (all existing rows are emitted as documents), then switches to WAL tailing. The materialization is idle until the first bootstrap batch arrives.
5. Steady-state latency: Postgres commit → gazette collection write → Snowflake materialization write ≈ 200 ms - 2 s depending on the Snowflake warehouse size and the materialization's micro-batch settings. Exactly-once is preserved because the gazette write and the Snowflake MERGE both participate in the connector's two-phase commit; a mid-flight crash restarts from the last acknowledged offset with no duplicates.

Output.

Stage	Event	Latency (typical)
Postgres commit	INSERT / UPDATE / DELETE	0 ms (baseline)
WAL decode	pgoutput → capture	20-50 ms
Collection write	gazette 2PC	30-100 ms
Materialization batch	Snowflake MERGE	500-1500 ms
Total p95	source → Snowflake	~1-2 sec

Rule of thumb. For any Estuary Flow catalog, keep captures, collections, materializations in separate top-level blocks in the same file (or split by concern across files under one prefix like acme/). Use fields: recommended: true for full-column sync; use explicit include: / exclude: lists when column-level control is required. Always version-control the catalog and deploy via CI, not from a laptop.

Worked example — a SQLite derivation for in-flight aggregation

Detailed explanation. Estuary Flow's derivations let you run in-flight transforms between collections without leaving the platform. A derivation reads from one or more source collections and writes to a derived collection using SQL (SQLite dialect) or TypeScript. Because derivations run inside Flow's runtime with gazette's two-phase commit, they're exactly-once and stateful — you can compute rolling aggregations, streaming joins, filters, and enrichments without deploying a separate Flink or Spark cluster.

• The idea. Source collection acme/orders has raw order documents. Derived collection acme/customer_order_stats has per-customer aggregates (total order count + total spend) updated in real time.
• The tool. SQLite dialect SQL, run in Flow's runtime. State is persisted per derivation shard.
• The output. Every incoming order updates the corresponding customer's aggregate; the derived collection is materializable like any other.

Question. Write the derivation that maintains acme/customer_order_stats from acme/orders, and describe how the state is shard-partitioned.

Input.

Component	Value
Source collection	acme/orders
Derived collection	acme/customer_order_stats
Key	customer_id
Aggregations	count(*), sum(total_cents)

Code.

# catalog.flow.yaml — add the derivation + derived collection
collections:
  acme/customer_order_stats:
    schema:
      type: object
      properties:
        customer_id:      { type: integer }
        order_count:      { type: integer }
        total_spend_cents:{ type: integer }
        last_order_at:    { type: string, format: date-time }
      required: [customer_id, order_count, total_spend_cents]
    key: [/customer_id]

    derive:
      using:
        sqlite: {}
      transforms:
        - name: fromOrders
          source: acme/orders
          shuffle:
            key: [/customer_id]           # co-locate per customer
          lambda: |
            SELECT
              $customer_id                          AS customer_id,
              1                                     AS order_count,
              $total_cents                          AS total_spend_cents,
              $updated_at                           AS last_order_at
            ;
      shards:
        min_txn_duration: 500ms            # micro-batch commits
        hot_standbys: 1                    # warm standby for fast failover

-- Materialization side — Snowflake sees customer_order_stats as any other table
-- Downstream dashboards read from analytics.customer_order_stats,
-- computed continuously by the derivation.
SELECT customer_id, order_count, total_spend_cents, last_order_at
FROM   analytics.customer_order_stats
ORDER  BY total_spend_cents DESC
LIMIT  10;

Step-by-step explanation.

1. The derived collection acme/customer_order_stats is declared like any collection — schema + key. What makes it a derivation is the derive: sub-block: using: sqlite: {} selects the SQLite runtime; transforms: list the input transforms.
2. Each transform binds one source collection with a shuffle: clause and a lambda: SQL block. shuffle: key: [/customer_id] tells gazette to route all orders for the same customer to the same derivation shard — critical for stateful aggregation correctness.
3. The lambda: SQL is the per-document projection: for each incoming order it emits a partial aggregate (customer_id, 1, total_cents, updated_at). The Flow runtime combines partials with the existing state per key using the collection's reduction annotations (elided here for brevity; typical reductions are sum, max, merge).
4. shards.min_txn_duration: 500ms micro-batches commits — the derivation coalesces documents received within 500 ms into one atomic commit for downstream materializations. Lower values reduce latency at the cost of more commit overhead.
5. shards.hot_standbys: 1 runs a warm standby of every shard for fast failover — if the primary shard crashes, the standby resumes without a cold-start snapshot rebuild. This is the pattern for production-grade streaming aggregations.

Output.

Incoming order	Derivation state (customer_id=7)	Derived collection write
customer_id=7, total=1500, at 12:00:00	{count: 1, spend: 1500, last: 12:00:00}	version 1
customer_id=7, total=800, at 12:05:00	{count: 2, spend: 2300, last: 12:05:00}	version 2
customer_id=7, total=1200, at 12:10:00	{count: 3, spend: 3500, last: 12:10:00}	version 3
customer_id=9, total=500, at 12:11:00	{count: 1, spend: 500, last: 12:11:00}	version 1 (new key)

Rule of thumb. For any Estuary Flow derivation with stateful aggregation, always set shuffle: key: [...] to the same fields as the derived collection's key — this co-locates per-key state on one shard, which is the correctness contract for aggregation. Set min_txn_duration between 100 ms and 1 s to balance latency and commit overhead; enable hot_standbys: 1 for production.

Worked example — schema evolution on a products capture

Detailed explanation. The single most operationally important property of a real-time ELT tool is what happens when the source schema changes. In Estuary Flow, each collection has a versioned schema; adding a nullable column to the source table triggers an automatic schema-evolution flow that propagates the new field through captures, collections, and materializations. Walk through an ALTER TABLE products ADD COLUMN sku_family TEXT event and its downstream effect.

• Trigger. DBA runs ALTER TABLE public.products ADD COLUMN sku_family TEXT on the Postgres primary.
• Detection. The Postgres capture detects the DDL via the WAL and emits a schema_updated control event.
• Propagation. Flow's control plane bumps the collection schema version, updates the materialization, and rolls out the change with zero pipeline downtime.

Question. Describe the end-to-end schema-evolution sequence for adding a nullable column and quantify the downtime.

Input.

Component	Before	After
products.sku_family	(does not exist)	TEXT NULLABLE
collection schema	version 1, 6 fields	version 2, 7 fields
Snowflake target	6 columns	7 columns (auto-added by materialization)
Downtime	(baseline)	0 s (rolling roll-out)

Code.

# 1. Source-side DDL (DBA action)
psql -h db-primary -U dba -d production <<'SQL'
  ALTER TABLE public.products ADD COLUMN sku_family TEXT;
SQL

# 2. Flow autodetects the schema change on the next capture batch.
# The control plane logs a schema-evolution event; the collection
# schema version bumps from v1 → v2.

# 3. Verify the new collection schema
flowctl catalog get --collection acme/products
# → schema.version: 2, properties.sku_family present

# 4. Materialization auto-evolves — Snowflake table gets a new column
# via an ALTER TABLE ... ADD COLUMN issued by the materialize-snowflake
# connector. No user action required.

# 5. Verify downstream
snow sql -c 'DESCRIBE TABLE RAW_FLOW.PUBLIC.products;'
# → sku_family VARCHAR(16777216) NULLABLE — added at Flow evolution time.

# Optional: if you want to override auto-evolution behaviour, add a
# per-materialization schema-evolution policy:
materializations:
  acme/snowflake-raw:
    endpoint: { ... }
    bindings:
      - source: acme/products
        resource: { table: products }
        fields:
          recommended: true
        schemaEvolution:
          onSchemaChange: alter-table         # alter-table | rebuild-collection | pause

Step-by-step explanation.

1. The DBA runs ALTER TABLE public.products ADD COLUMN sku_family TEXT — a standard, non-blocking, nullable column add. Postgres records the DDL in the WAL alongside the row events for the same transaction.
2. The Postgres capture, on its next WAL decode cycle, sees the DDL event and emits an internal schema_updated control message. The Flow control plane picks it up and increments the collection schema version.
3. The materialization connector detects the new schema version. Because the change is additive (new nullable column), the connector issues an ALTER TABLE analytics.products ADD COLUMN sku_family VARCHAR(16777216) against Snowflake and updates its internal binding. No pipeline pause, no restart.
4. All subsequent capture documents include the sku_family field, and all subsequent materialization writes include the column. Historical rows in Snowflake have NULL for sku_family — matching Postgres semantics.
5. For non-additive changes (column drop, type narrowing), the schemaEvolution.onSchemaChange policy determines behaviour: alter-table attempts the change and fails loudly if unsafe; rebuild-collection re-snapshots the source; pause freezes the pipeline for manual intervention. Additive changes are always auto-handled.

Output.

Timeline	Event	Impact
T+0s	DBA ALTER TABLE	(no effect on capture yet)
T+50ms	WAL DDL event	capture emits schema_updated
T+300ms	Control plane update	collection schema → v2
T+500ms	Materialization ALTER	Snowflake column added
T+1s	First row with new column	end-to-end propagated
Downtime	0 s	rolling roll-out

Rule of thumb. For additive schema changes in Estuary Flow, do nothing — the platform handles it. For non-additive changes, set the per-materialization schemaEvolution policy explicitly and rehearse the flow on staging first. Never assume "schema evolution just works" without knowing which class of change your pipeline supports without user action.

Senior interview question on Estuary Flow

A senior interviewer might ask: "Design an Estuary Flow pipeline that captures 30 tables from Postgres, computes a real-time customer 360 aggregation via a derivation, and materializes both raw tables and the aggregation into Snowflake — with exactly-once end-to-end and sub-2-second p95 latency. Cover the catalog structure, the derivation logic, the pricing model, and the failure semantics when Snowflake is unreachable for 30 minutes."

Solution Using a multi-file catalog + SQLite derivation + materialize-snowflake with hot standbys

# 1. captures.flow.yaml — Postgres capture for 30 tables
captures:
  acme/postgres-warehouse:
    endpoint:
      connector:
        image: ghcr.io/estuary/source-postgres:v1
        config:
          address: db-primary.internal:5432
          database: production
          user: flow_reader
          password_sops: sops://kv/prod/flow-postgres
          slot_name: estuary_warehouse
          publication_name: warehouse_pub
    bindings:
      - resource: { namespace: public, stream: orders }
        target: acme/orders
      - resource: { namespace: public, stream: customers }
        target: acme/customers
      - resource: { namespace: public, stream: line_items }
        target: acme/line_items
      # ... 27 more bindings, one per source table ...
    interval: 0s

# 2. derivations.flow.yaml — customer 360 aggregation
collections:
  acme/customer_360:
    schema:
      type: object
      properties:
        customer_id:       { type: integer }
        lifetime_orders:   { type: integer }
        lifetime_spend:    { type: integer }
        last_order_at:     { type: string, format: date-time }
        top_category:      { type: string  }
      required: [customer_id, lifetime_orders, lifetime_spend]
    key: [/customer_id]

    derive:
      using:
        sqlite: {}
      transforms:
        - name: fromOrders
          source: acme/orders
          shuffle: { key: [/customer_id] }
          lambda: |
            SELECT
              $customer_id       AS customer_id,
              1                  AS lifetime_orders,
              $total_cents       AS lifetime_spend,
              $updated_at        AS last_order_at
            ;
        - name: fromLineItems
          source: acme/line_items
          shuffle: { key: [/customer_id] }
          lambda: |
            SELECT
              $customer_id       AS customer_id,
              0                  AS lifetime_orders,
              0                  AS lifetime_spend,
              NULL               AS last_order_at,
              $category          AS top_category
            ;
      shards:
        min_txn_duration: 500ms
        hot_standbys: 1

# 3. materializations.flow.yaml — Snowflake sink for raw + derived
materializations:
  acme/snowflake-raw:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-snowflake:v1
        config:
          host: acme.snowflakecomputing.com
          account: acme
          user: flow_writer
          password_sops: sops://kv/prod/flow-snowflake
          database: RAW_FLOW
          schema: PUBLIC
          warehouse: LOAD_WH
          role: FLOW_WRITER
    bindings:
      - source: acme/orders
        resource: { table: orders }
        fields: { recommended: true }
      # ... 29 more raw bindings ...
      - source: acme/customer_360
        resource: { table: customer_360 }
        fields: { recommended: true }

# 4. Deploy
flowctl catalog publish \
  --source captures.flow.yaml \
  --source derivations.flow.yaml \
  --source materializations.flow.yaml

Step-by-step trace.

Layer	Component	Purpose
Postgres primary	logical replication slot estuary_warehouse	source WAL feed
Capture connector	source-postgres bindings × 30	one connector, N bindings
Collections (raw)	acme/orders, acme/customers, … × 30	durable ordered logs
Derivation	acme/customer_360 (SQLite)	in-flight aggregation, shuffled by customer_id
Materialization	materialize-snowflake with hot standby	Snowflake MERGE per table + derived
Pricing	task-hours × 3 connectors + data volume	predictable at 30 tables

After deployment, all 30 raw tables land in RAW_FLOW.PUBLIC.* in Snowflake within ~1.5 s of source commit; the customer_360 derived aggregation lands in the same schema within ~2 s (extra derivation hop). A 30-minute Snowflake outage causes the materialization to buffer in gazette; gazette retains up to the configured collection retention (default 30 days) so no data is lost; when Snowflake recovers, the materialization drains at maximum throughput.

Output:

Metric	Value
End-to-end latency p95 (raw)	~1.5 s
End-to-end latency p95 (derived)	~2.0 s
Exactly-once	yes (gazette 2PC + idempotent MERGE)
Snowflake outage tolerance	up to 30 days (default collection retention)
Pricing	~$2.5k/month at this scale
Ops burden	~0.05 FTE (managed)

Why this works — concept by concept:

• Captures + collections + derivations + materializations — the four building blocks map 1:1 to source read, durable log, in-flight transform, and sink write. The catalog is code; the operational contract is a YAML file in git, not a UI click.
• Gazette two-phase commit — the broker under the hood commits producer writes and consumer offsets atomically, which is what makes exactly-once actually work end-to-end. Kafka needs transactional producers + idempotent consumers + careful config; gazette gives you the same guarantee with less client-side work.
• SQLite derivation with shuffled key — the derivation is stateful (per-customer aggregate) and correct only because shuffle: key: [/customer_id] co-locates all events for the same customer on one shard. Without shuffle, two shards would each maintain a partial view and produce wrong aggregates.
• Hot standby shards — hot_standbys: 1 runs a warm replica of every derivation shard so failover is a checkpoint-hop rather than a cold snapshot rebuild. This is the pattern for production-grade stateful derivations.
• Cost — three connectors (one capture, one derivation, one materialization) with 30 raw bindings, ~$2.5k/month at ~5 GB/day CDC volume, ~0.05 FTE for operations. The eliminated cost is a Kafka cluster + a Flink cluster + the engineer time to keep both running. Compared to Streamkap, the derivation capability is the buy-decision; compared to Kafka Connect, the managed control plane is the buy-decision.

Streaming
Topic — streaming
Streaming derivation and gazette / Kafka-shaped problems

Practice →

SQL Topic — sql SQL aggregation problems on real-time customer 360

Practice →

---

3. Streamkap deep dive

Streamkap is managed Debezium-based CDC into Snowflake / BigQuery / Databricks — the least-surface answer for "just get Postgres into the warehouse in real time"

The mental model in one line: streamkap is the point-and-click, autoscaled, managed CDC pipeline that reads mutations from Postgres / MySQL / SQL Server / MongoDB via Debezium under the hood, buffers them in an internal (invisible-to-you) Kafka spine, and lands them in Snowflake / BigQuery / Databricks / Redshift / Delta via a per-target sink connector that performs idempotent MERGE by primary key — the whole path is UI-configured, autoscaled, schema-evolution-safe by default, and requires zero Kafka operators on your side. Every senior data engineer evaluating managed CDC-to-warehouse in 2026 has to know Streamkap's positioning because it is the tool that most cleanly beats Fivetran on freshness for the warehouse-only workload.

The four building blocks — sources, connectors, streams, destinations.

• Sources. Managed CDC connectors: Postgres (logical replication), MySQL (binlog), SQL Server (CDC tables), MongoDB (change streams), Oracle (LogMiner). Also supports SaaS APIs (Stripe, Salesforce, Zendesk) for polling ingestion.
• Streams. The internal representation of one source table's CDC feed. Streams are backed by Streamkap's managed Kafka spine, but you never see or touch it — the Kafka layer is a hidden implementation detail.
• Destinations. Managed sink connectors: Snowflake, BigQuery, Databricks, Redshift, Delta on ADLS/S3, ClickHouse. Sink connectors default to idempotent MERGE by primary key with a __streamkap_op operation column indicating insert/update/delete.
• Transformations. Lightweight in-flight transforms (column drops, PII masks, filters) via a UI-driven or SQL-lite expression. Anything heavier (aggregations, joins) should live in the warehouse as a dbt model.

Snapshot + streaming mode — how initial data lands.

• Snapshot. On first activation, Streamkap runs a consistent snapshot of the source table (SET TRANSACTION SNAPSHOT on Postgres, FLUSH TABLES WITH READ LOCK briefly on MySQL) and emits every existing row as a snapshot=true insert event. Records the WAL/binlog position at snapshot start.
• Streaming. After snapshot, the connector switches to tailing the WAL/binlog from the recorded position. This is the same bootstrap pattern Debezium implements natively (Streamkap wraps and manages it).
• Resume. On connector restart, Streamkap resumes from the last acknowledged WAL/binlog position — no data loss, no duplication (with the sink's idempotent MERGE contract).

Managed schema evolution — the buy-decision.

• Column add. Nullable column on the source → automatic ALTER TABLE ADD COLUMN on the warehouse target. Zero pipeline downtime.
• Column drop. Requires user acknowledgement in the UI (destructive change); Streamkap can either drop the column downstream or ignore it going forward.
• Type widening. INT → BIGINT, VARCHAR(50) → VARCHAR(200) — automatic ALTER on the warehouse.
• Type narrowing / rename. Requires manual intervention; pipeline flags for user attention.

Pricing model — per-row + per-table + connector-hour.

• Per-row. Billed on rows-transferred, typically at rates that undercut Fivetran on high-throughput CDC workloads.
• Per-table. A small per-source-table monthly fee (varies by tier).
• Connector-hour. Autoscaled compute time; you don't pay for idle connectors.
• Sweet spot. 5-100 CDC tables, moderate row volume (up to ~1B rows/day per connector). Above that, custom quotes.

Competitive positioning — where Streamkap wins and loses.

• Vs Fivetran HVR / Airbyte CDC. Streamkap ships sub-5-second latency; Fivetran defaults to 15-minute cadence; Airbyte defaults to hourly. On freshness, Streamkap wins outright.
• Vs Estuary Flow. Streamkap has fewer connectors (~50 vs ~200) and no in-flight transforms. On warehouse-only + simple pipelines it wins on TCO; on complex pipelines Estuary Flow wins.
• Vs Kafka Connect DIY. Streamkap costs more per row at very large scale (>10 TB/day) but eliminates the entire ops burden. On mid-scale workloads it wins on TCO; at hyperscale DIY wins.

Common interview probes on Streamkap.

• "How does Streamkap differ from Fivetran?" — required answer: sub-5-second latency vs 15-minute batches; managed Debezium vs Fivetran's proprietary poller.
• "What does the sink connector do?" — required answer: idempotent MERGE by PK with the __streamkap_op column.
• "How does schema evolution work?" — automatic for additive changes; user-acknowledged for destructive ones.
• "When would you not pick Streamkap?" — need in-flight transforms (pick Estuary Flow), need bespoke source connectors (pick Kafka Connect), or extreme scale (>10 TB/day CDC).

Worked example — configuring a Postgres → Snowflake pipeline in Streamkap

Detailed explanation. The canonical Streamkap pipeline: connect a Postgres source, connect a Snowflake destination, pick tables, click activate. Behind the UI is a JSON pipeline definition (also API-driven) that describes source + destination + table mappings + schema-evolution policy. Walk through the JSON and the Snowflake landing table.

• Source. Postgres 16 with wal_level=logical and a streamkap_reader REPLICATION role.
• Destination. Snowflake account acme, database RAW_STREAMKAP, schema PUBLIC.
• Tables. public.orders, public.customers, public.line_items.
• Snapshot + streaming. Snapshot then tail; managed schema evolution.

Question. Write the Streamkap pipeline JSON export and show the Snowflake target table shape after activation.

Input.

Component	Value
Source	Postgres 16 (production)
Destination	Snowflake acme / RAW_STREAMKAP / PUBLIC
Tables	public.orders, public.customers, public.line_items
Sync mode	snapshot + streaming
Schema evolution	automatic (add/widen); user-acknowledged (drop/narrow)

Code.

{
  "pipeline_name": "postgres-prod-to-snowflake-raw",
  "source": {
    "type": "postgres",
    "host": "db-primary.internal",
    "port": 5432,
    "database": "production",
    "user": "streamkap_reader",
    "password_ref": "secret://streamkap/postgres-prod",
    "publication_name": "streamkap_pub",
    "slot_name": "streamkap_prod",
    "plugin": "pgoutput"
  },
  "destination": {
    "type": "snowflake",
    "account": "acme",
    "user": "STREAMKAP_WRITER",
    "password_ref": "secret://streamkap/snowflake-prod",
    "warehouse": "LOAD_WH",
    "database": "RAW_STREAMKAP",
    "schema": "PUBLIC",
    "role": "STREAMKAP_WRITER"
  },
  "tables": [
    { "source_table": "public.orders",     "sync_mode": "snapshot_and_streaming" },
    { "source_table": "public.customers",  "sync_mode": "snapshot_and_streaming" },
    { "source_table": "public.line_items", "sync_mode": "snapshot_and_streaming" }
  ],
  "schema_evolution": {
    "column_add": "auto",
    "column_drop": "manual",
    "type_widening": "auto",
    "type_narrowing": "manual"
  },
  "transformations": [
    {
      "table": "public.customers",
      "type": "mask",
      "columns": ["email"],
      "mask_function": "sha256"
    }
  ]
}

-- Snowflake target after Streamkap activation (auto-created by the sink)
CREATE TABLE RAW_STREAMKAP.PUBLIC.orders (
    id                BIGINT       NOT NULL,          -- source PK
    customer_id       BIGINT,
    total_cents       BIGINT,
    status            VARCHAR,
    created_at        TIMESTAMP_TZ,
    updated_at        TIMESTAMP_TZ,
    -- Streamkap metadata columns (present on every landed row)
    __streamkap_op    VARCHAR,                        -- 'r' snapshot, 'c' create, 'u' update, 'd' delete
    __streamkap_ts    TIMESTAMP_TZ,                   -- source commit time
    __streamkap_lsn   VARCHAR,                        -- source LSN for ordering
    PRIMARY KEY (id)
);

-- Sink connector runs a MERGE per micro-batch, ordered by __streamkap_lsn
MERGE INTO RAW_STREAMKAP.PUBLIC.orders AS tgt
USING (SELECT * FROM staging_batch_delta) src
ON tgt.id = src.id
WHEN MATCHED AND src.__streamkap_op = 'd' THEN DELETE
WHEN MATCHED AND src.__streamkap_lsn > tgt.__streamkap_lsn THEN UPDATE SET
    customer_id     = src.customer_id,
    total_cents     = src.total_cents,
    status          = src.status,
    updated_at      = src.updated_at,
    __streamkap_op  = src.__streamkap_op,
    __streamkap_ts  = src.__streamkap_ts,
    __streamkap_lsn = src.__streamkap_lsn
WHEN NOT MATCHED THEN INSERT (id, customer_id, total_cents, status, created_at, updated_at,
                              __streamkap_op, __streamkap_ts, __streamkap_lsn)
                       VALUES (src.id, src.customer_id, src.total_cents, src.status,
                               src.created_at, src.updated_at,
                               src.__streamkap_op, src.__streamkap_ts, src.__streamkap_lsn);

Step-by-step explanation.

1. The pipeline JSON declares source + destination + table list + schema-evolution policy in one object. In the UI this is a wizard; via the Streamkap API this is a POST to /pipelines. The Postgres side needs streamkap_pub PUBLICATION + streamkap_prod replication slot; Streamkap creates them on first activation via the credentials you supply.
2. On activation the connector runs a snapshot: for each table, SET TRANSACTION SNAPSHOT (Postgres) freezes a point-in-time view, SELECT * every row, emit as __streamkap_op = 'r' (read/snapshot) events, record the LSN at snapshot start. This is the standard Debezium bootstrap flow, managed by Streamkap.
3. After snapshot, the connector switches to WAL tailing from the recorded LSN. Every INSERT lands as 'c', UPDATE as 'u', DELETE as 'd' — with the pre-image on updates and deletes so downstream can compute row diffs.
4. The Snowflake sink runs an idempotent MERGE per micro-batch (default ~5-second batches). Ordering by __streamkap_lsn ensures replay produces the same warehouse state — the AND src.__streamkap_lsn > tgt.__streamkap_lsn guard makes the MERGE last-write-wins in LSN order.
5. The transformations block runs the email SHA-256 mask on the customers stream before it lands in Snowflake — this is Streamkap's answer to PII regulation. The mask happens in the internal transform layer; the warehouse never sees the raw email.

Output.

Source event	__streamkap_op	Warehouse action
Snapshot bootstrap row	'r'	INSERT (or upsert on re-snapshot)
INSERT orders VALUES (1,...)	'c'	INSERT
UPDATE orders SET status='shipped' WHERE id=1	'u'	UPDATE (LSN guard)
DELETE FROM orders WHERE id=1	'd'	DELETE (soft or hard per config)
Late-arriving event (LSN < target)	'u' or 'c'	ignored (LSN guard filters)

Rule of thumb. For any Streamkap → warehouse pipeline, keep the target schema in a dedicated RAW_* database so dbt models can transform out of raw with predictable naming. Never write dashboards against the raw Streamkap tables directly — always layer a dbt or view abstraction on top so the __streamkap_* metadata columns don't leak.

Worked example — schema evolution end-to-end

Detailed explanation. The single most operational win of managed CDC-to-warehouse tools is schema evolution: a source-side ALTER TABLE ADD COLUMN should propagate to the warehouse without a pipeline outage. Walk through the sequence for adding a nullable column, and contrast it with the destructive column-drop case that requires user acknowledgement.

• Nullable column add. ALTER TABLE public.orders ADD COLUMN priority INT on Postgres → warehouse gets ADD COLUMN priority NUMBER automatically.
• Type widening. ALTER COLUMN status TYPE VARCHAR(200) on Postgres (was VARCHAR(50)) → warehouse issues equivalent widening. Automatic.
• Column drop. ALTER TABLE public.orders DROP COLUMN legacy_field on Postgres → Streamkap pauses the stream and flags a schema-alert requiring UI acknowledgement.

Question. Show the timeline for the three schema-evolution scenarios and quantify the pipeline impact.

Input.

Change	Automatic?	Downtime	User action
Add nullable column	yes	0 s	none
Widen type	yes	0 s	none
Drop column	no	pipeline pause	UI acknowledgement
Narrow type	no	pipeline pause	UI acknowledgement
Rename column	no	pipeline pause	UI acknowledgement (may require re-snapshot)

Code.

# 1. Additive change — column add
psql -h db-primary -U dba -d production <<'SQL'
  ALTER TABLE public.orders ADD COLUMN priority INT DEFAULT 0;
SQL

# ~1s later, Streamkap sink issues on Snowflake:
# ALTER TABLE RAW_STREAMKAP.PUBLIC.orders ADD COLUMN priority NUMBER(38,0);
# Pipeline never pauses. New rows include `priority`; historical rows show NULL.

# 2. Type widening
psql -h db-primary -U dba -d production <<'SQL'
  ALTER TABLE public.orders ALTER COLUMN status TYPE VARCHAR(200);
SQL

# ~1s later, Streamkap sink issues on Snowflake:
# ALTER TABLE RAW_STREAMKAP.PUBLIC.orders ALTER COLUMN status VARCHAR(200);
# Pipeline never pauses.

# 3. Destructive change — column drop
psql -h db-primary -U dba -d production <<'SQL'
  ALTER TABLE public.orders DROP COLUMN legacy_field;
SQL

# Streamkap detects the drop and pauses the pipeline with a schema-alert:
#   > Column `legacy_field` was dropped from source table `public.orders`.
#   > This is a destructive change. Choose an action:
#     [Drop column in destination]  [Keep column in destination]  [Pause and investigate]
# Operator resolves via the UI or API; pipeline resumes.

# schema_evolution policy in the pipeline JSON
schema_evolution:
  column_add:      auto        # apply on warehouse without pause
  type_widening:   auto        # apply on warehouse without pause
  column_drop:     manual      # pause; operator acknowledgement
  type_narrowing:  manual      # pause; operator acknowledgement
  column_rename:   manual      # may require re-snapshot; operator decides

Step-by-step explanation.

1. Additive changes (column add + type widening) are always safe on the warehouse side — the new column is either new (no historical data conflict) or wider (all existing values fit). Streamkap detects the change via the source-side DDL event (Postgres WAL DDL event, MySQL binlog DDL) and issues the equivalent ALTER on the warehouse within ~1 second.
2. Historical rows in the warehouse get NULL for newly added columns — matching source semantics. New rows include the new column value from the source. Downstream dbt models can coalesce NULL or add default logic without a pipeline change.
3. Type widening (e.g. INT → BIGINT, VARCHAR(50) → VARCHAR(200)) is applied as an ALTER COLUMN TYPE on the warehouse. Snowflake supports most widenings in-place; BigQuery may require a column rebuild internally but is transparent to the user.
4. Destructive changes — column drop, type narrowing, column rename — require user acknowledgement because they can silently corrupt downstream dashboards (a dbt model that SELECTs the dropped column would break). Streamkap pauses the pipeline, emits an alert, and waits for a UI (or API) decision.
5. The schema_evolution policy in the pipeline JSON lets you override defaults per change type. Aggressive teams set everything to auto; conservative teams set even additive changes to manual to require a change-management approval. The default (add and widening auto, others manual) matches production expectations for most workloads.

Output.

Scenario	Streamkap action	Warehouse effect	Downtime
Add nullable column	auto-ALTER	new NULLABLE column	0 s
Widen VARCHAR / INT	auto-ALTER TYPE	wider column	0 s
Add NOT NULL with default	auto-ALTER + backfill	new column with default	0 s
Drop column	pause + alert	(unchanged until user acks)	until ack
Narrow type	pause + alert	(unchanged until user acks)	until ack
Rename column	pause + alert	(may require re-snapshot)	minutes to hours

Rule of thumb. For every Streamkap deployment, configure the schema_evolution policy explicitly (don't rely on defaults) and rehearse a destructive change on staging before it lands in production. The 5 minutes of runbook practice saves the 4 AM incident when a source engineer drops a "legacy" column that turns out to power the executive dashboard.

Worked example — competitive positioning vs Fivetran HVR

Detailed explanation. The most common competitive question senior architects face is "why not Fivetran?" Fivetran HVR (their high-volume, log-based tier) is the closest peer to Streamkap and Estuary Flow; the differences matter for latency-sensitive workloads. Walk through the head-to-head for a 40-table Postgres → Snowflake replication.

• Latency. Streamkap sub-5-sec; Fivetran HVR ~1-min cadence; Fivetran default ~15-min.
• Pricing model. Streamkap per-row + per-table + connector-hour; Fivetran per-MAR (monthly active row).
• Ops surface. Both fully managed; both UI-driven.
• Schema evolution. Both automatic for additive; both flag destructive.

Question. Build the head-to-head comparison for a 40-table Postgres → Snowflake pipeline and pick the tool.

Input.

Axis	Streamkap	Fivetran HVR
Latency p95	~2-3 sec	~60 sec (HVR tier)
Sync model	continuous streaming	log-based near-real-time
Pricing	per-row + per-table + connector-hour	per-MAR (monthly active row)
Snowflake sink	idempotent MERGE with __streamkap_* metadata	proprietary sink table shape
Schema evolution	automatic + configurable	automatic + configurable
Connector breadth	~50 DB / warehouse sources	~500 (SaaS + DB)
Free trial	yes	yes

Code.

# TCO comparison for the 40-table workload
def tco(rows_per_month: int, tool: str) -> int:
    """Rough monthly TCO — illustrative only."""
    if tool == "streamkap":
        # $0.10 per 1M rows + $20 per table + $500 base per connector
        per_row = (rows_per_month / 1_000_000) * 0.10
        per_table = 40 * 20
        base = 500
        return int(per_row + per_table + base)
    elif tool == "fivetran_hvr":
        # Fivetran MAR pricing tiers (illustrative);
        # ~$500 per M MAR up to 5M, then $200 per M
        mar = rows_per_month
        if mar <= 5_000_000:
            return int(mar / 1_000_000 * 500)
        else:
            return int(2500 + (mar - 5_000_000) / 1_000_000 * 200)

# 40 tables × 300 DML/s × 86400 s/day × 30 days ≈ 31B row-events/month
# But MAR counts distinct rows changed per month, not events. Assume 40M MAR.
print("streamkap 40M rows/mo:", tco(40_000_000, "streamkap"))
print("fivetran   40M MAR/mo:", tco(40_000_000, "fivetran_hvr"))

Step-by-step explanation.

1. Latency is the strongest differentiator: Streamkap's continuous streaming beats Fivetran HVR's minute-cadence for any dashboard or operational use-case that needs sub-10-second freshness. If your target latency is >1 minute, either tool works and price becomes the tiebreaker.
2. Pricing models are structurally different. Streamkap charges on row events + tables + connector-hours; Fivetran charges on monthly-active-rows (MAR) — the count of distinct rows that changed at least once in the month. For high-cardinality, low-modification workloads Fivetran can be cheaper; for high-modification workloads (frequent UPDATEs on the same PKs) Streamkap can be cheaper.
3. The Snowflake sink shape is different: Streamkap uses explicit __streamkap_op / __streamkap_ts / __streamkap_lsn metadata columns; Fivetran uses _fivetran_deleted / _fivetran_synced columns. Downstream dbt models must reference the correct metadata columns; migrating from one to the other requires model changes.
4. Connector breadth favours Fivetran heavily (~500 vs ~50). If your requirement is 20 SaaS APIs + 5 Postgres tables, Fivetran is likely the answer. If it's 40 Postgres tables, Streamkap wins on latency + often on cost.
5. Ops burden is a tie — both are fully managed. The differentiators are latency (Streamkap wins for streaming), connector breadth (Fivetran wins for SaaS), and pricing shape (workload-dependent).

Output.

Requirement	Winning tool	Rationale
40 Postgres tables, sub-5-sec latency	Streamkap	Fivetran HVR's ~60s doesn't meet SLA
40 Postgres tables, sub-1-min latency	tie (price-dependent)	run both trials; pick lower TCO
20 SaaS + 5 Postgres	Fivetran	connector breadth
Extreme UPDATE churn	Streamkap	per-event pricing beats MAR for high-write
Zero downstream migration friction	tool already used	staying costs nothing

Rule of thumb. For sub-10-second freshness on Postgres/MySQL → warehouse, Streamkap and Estuary Flow are the two managed-streaming candidates. Fivetran (and Airbyte) are the batch-ELT default; they're excellent tools in that category but the wrong category for real-time workloads. Never pick a tool from a different category to solve a category problem.

Senior interview question on Streamkap

A senior interviewer might ask: "You're the tech lead of a 20-person data team at a mid-market SaaS. The exec team wants to replace Fivetran (15-min freshness) with a real-time CDC pipeline for 60 Postgres tables into Snowflake — sub-5-second latency, zero data loss, and zero Kafka operators. Walk me through the Streamkap adoption: the source-side setup, the pipeline config, the schema-evolution policy, the migration plan, and the runbook when the Postgres replication slot lag exceeds 1 GB."

Solution Using Streamkap with a per-schema slot, staged migration, and slot-lag monitoring

-- 1. Postgres-side prep (one time, DBA action)
-- postgresql.conf: wal_level = logical, max_replication_slots = 10,
-- max_wal_senders = 10, max_slot_wal_keep_size = 20GB

CREATE ROLE streamkap_reader WITH LOGIN REPLICATION PASSWORD 'strong-secret';
GRANT USAGE ON SCHEMA public TO streamkap_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO streamkap_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO streamkap_reader;

CREATE PUBLICATION streamkap_pub FOR ALL TABLES;
-- Slot is auto-created by Streamkap on first activation as `streamkap_prod`.

// 2. Streamkap pipeline JSON (via API or UI export)
{
  "pipeline_name": "postgres-prod-to-snowflake",
  "source": {
    "type": "postgres",
    "host": "db-primary.internal",
    "database": "production",
    "user": "streamkap_reader",
    "password_ref": "secret://streamkap/pg-prod",
    "publication_name": "streamkap_pub",
    "slot_name": "streamkap_prod",
    "plugin": "pgoutput",
    "heartbeat_interval_ms": 10000
  },
  "destination": {
    "type": "snowflake",
    "account": "acme",
    "database": "RAW_STREAMKAP",
    "schema": "PUBLIC",
    "warehouse": "LOAD_WH",
    "role": "STREAMKAP_WRITER"
  },
  "tables": [
    // ... 60 entries, one per source table, sync_mode = snapshot_and_streaming ...
  ],
  "schema_evolution": {
    "column_add":     "auto",
    "type_widening":  "auto",
    "column_drop":    "manual",
    "type_narrowing": "manual"
  },
  "alerting": {
    "slot_lag_bytes_warn":  1073741824,   // 1 GB
    "slot_lag_bytes_alert": 5368709120,   // 5 GB
    "slot_inactive_min":    15
  }
}

# 3. Migration plan — 6 weeks, batched cutover, parallel run
week_01:
  - Provision Streamkap trial + Snowflake `RAW_STREAMKAP` schema
  - DBA creates replication user + publication
  - Enable Streamkap for 5 non-critical tables
week_02_03:
  - Cut over 20 more tables in batches of 5; parity harness after each batch
week_04:
  - Cut over 20 more tables
week_05:
  - Cut over final 15 critical tables; run parity for 7 days
week_06:
  - Disable Fivetran; monitor 30 days before decommission

-- 4. Postgres-side slot lag monitoring (Prometheus scrape query)
SELECT
    slot_name,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM   pg_replication_slots
WHERE  slot_name = 'streamkap_prod';

-- Runbook when lag > 1 GB:
--   1. Check Streamkap dashboard for connector health
--   2. Check Snowflake load warehouse suspension state
--   3. Contact Streamkap support if lag continues to grow
--   4. If slot lag approaches 20 GB (max_slot_wal_keep_size), primary is at risk
--      → escalate to Streamkap on-call + prepare re-snapshot as last resort

Step-by-step trace.

Layer	Config	Reasoning
Postgres	wal_level=logical + max_slot_wal_keep_size=20GB	disk-full defense
Publication	FOR ALL TABLES	60-table workload; avoid per-table maintenance
Streamkap source	pgoutput + heartbeat 10s	quiet-table slot advance
Streamkap sink	Snowflake idempotent MERGE by PK	at-least-once → effectively exactly-once
Schema evolution	additive auto, destructive manual	production-safe default
Migration	batched, parity-harnessed	rollback-ready per batch
Slot monitoring	Prometheus + Streamkap alerting	catch stalled reader early

After the migration, all 60 tables land in Snowflake within ~2-3 seconds of source commit; the Fivetran pipeline is decommissioned after 30 days of parallel run; Postgres slot lag stays under 100 MB in steady state with heartbeat and healthy sink throughput. The runbook covers slot-lag spikes with clear escalation paths.

Output:

Metric	Before (Fivetran)	After (Streamkap)
Warehouse freshness p95	15 min	~2 sec
Delete visibility	yes	yes (__streamkap_op = 'd')
Schema evolution	manual per connector	auto for additive; manual for destructive
Ops burden	0.1 FTE (Fivetran config)	0.05 FTE (Streamkap)
TCO year 1	$25k Fivetran	$22k Streamkap + $10k migration
TCO year 2+	$25k	$22k

Why this works — concept by concept:

• Managed Debezium under the hood — Streamkap wraps Debezium as an implementation detail. You get Debezium's mature CDC quality without owning the Kafka Connect cluster, the schema registry, or the Debezium version upgrade cycle. The trade-off is less config surface — you can't tune every Debezium knob, but you also can't misconfigure them.
• Idempotent MERGE by PK — the sink-side MERGE with __streamkap_lsn guard makes replay safe. Re-applying the same CDC batch produces the same warehouse state. This is the at-least-once → effectively-exactly-once contract for the warehouse layer.
• Additive-auto + destructive-manual schema evolution — the production-safe default: additive changes (column add, type widen) apply automatically because they can't break downstream; destructive changes (drop, narrow) require user acknowledgement because they can. This matches what mature Fivetran users configure manually.
• Batched migration with parity harness — never cut over 60 tables at once. Batches of 5-20 with a parity check catches sink-encoding bugs before they hit production. The 30-day parallel run is the rollback safety net.
• Cost — one managed pipeline, one replication slot, one Snowflake sink schema, ~0.05 FTE ops, ~$22k/year steady state. The eliminated cost is Fivetran's higher steady-state price plus the 15-minute freshness gap that killed the operational dashboard. Compared to Estuary Flow, this is warehouse-only + no transforms → cheaper. Compared to Kafka Connect DIY at this scale, this is dramatically less operational surface at a moderate premium.

Streaming
Topic — streaming
Streaming Debezium and managed CDC problems

Practice →

SQL Topic — sql SQL MERGE by PK and idempotent-sink problems

Practice →

---

4. Kafka Connect (DIY)

Kafka Connect is the DIY streaming ELT chassis — hundreds of connectors, SMTs for in-flight transforms, DLQs for bad rows, and a Distributed-mode worker fleet you own end-to-end

The mental model in one line: kafka connect is the open-source (Apache-licensed) integration framework that runs source connectors (Debezium for CDC, JDBC source for polling, hundreds of SaaS + queue + file-system connectors) and sink connectors (Snowflake sink, BigQuery sink, S3 sink, Elasticsearch sink) inside a worker fleet that you deploy either standalone (single JVM, no fault tolerance) or distributed (multi-worker cluster with automatic task rebalancing), with SMTs (Single Message Transforms) for lightweight in-flight transformations and a per-connector dead-letter queue (DLQ) for records the sink cannot process — and every piece of the operational surface (upgrades, offsets, security patches, memory tuning) is yours to run. Every senior data engineer needs to know Kafka Connect because it is the escape hatch when managed tools don't have the connector you need or the price at scale is prohibitive.

The four building blocks — workers, connectors, tasks, SMTs.

• Workers. JVMs running the Connect runtime. Standalone mode is one worker, one config file, no fault tolerance. Distributed mode is N workers forming a group; connectors and tasks are distributed and rebalanced automatically. Production always uses Distributed.
• Connectors. Java classes implementing the SourceConnector or SinkConnector interface. Debezium is a family of source connectors (Postgres, MySQL, MongoDB, SQL Server, Oracle). Confluent's Snowflake / BigQuery / Elasticsearch sinks are the common sink connectors. Hundreds more in the open ecosystem.
• Tasks. Each connector spawns one or more tasks — the parallelism unit. A JDBC source connector polling 10 tables might spawn 10 tasks; a Debezium Postgres connector always runs exactly one task (WAL is sequential).
• SMTs (Single Message Transforms). In-flight per-message transforms applied inside the connector pipeline. Built-in SMTs include Cast, Filter, ReplaceField, MaskField, ExtractField, TimestampConverter. kafka connect vs debezium interviewers love this distinction — Debezium is a source connector that runs inside Kafka Connect; SMTs are how you shape its output.

Distributed vs Standalone — always Distributed in production.

• Standalone. Single JVM, in-process offset storage (local file). Zero fault tolerance; any crash means data loss until manual restart. Fine for local dev, terrible for prod.
• Distributed. Multi-worker cluster. Offsets, configs, and statuses live in three internal Kafka topics (connect-offsets, connect-configs, connect-status). Workers form a group via the Kafka group coordinator; connector tasks are rebalanced on worker join/leave.
• Sizing. Rule of thumb: 3-node worker fleet for HA baseline; scale horizontally with throughput. Each worker JVM: 4-8 vCPU, 8-16 GB heap, 500 MB - 2 GB overhead for connector plugins.

The DLQ + error-tolerance contract — how bad records are handled.

• The problem. A record whose schema doesn't match the sink's expected schema (e.g. Snowflake sink expects INT, record has "foo") crashes the sink task by default. One bad record blocks the entire pipeline.
• The fix. Configure errors.tolerance=all + errors.deadletterqueue.topic.name=<dlq_topic>. Bad records go to the DLQ; the sink continues. Alert on DLQ backlog; investigate offline.
• The DLQ format. Original message + headers indicating the failure cause (__connect.errors.exception.class, __connect.errors.exception.message, etc.). Downstream tooling can inspect and replay.

Offset drift and other failure modes — what actually breaks.

• Offset drift. In Distributed mode, offsets live in connect-offsets topic. If that topic is misconfigured (wrong replication factor, no compaction), workers can lose offsets on rebalance. Always set offset.storage.replication.factor=3 and cleanup.policy=compact.
• Deserialisation errors. A source that changes serialization mid-stream (JSON → Avro, or a broken schema evolution) causes converter errors. Handled via errors.tolerance if configured.
• Slot / binlog retention. Debezium sources hold source-side retention (Postgres slot, MySQL binlog); the same disk-full defense as any log-based CDC applies (max_slot_wal_keep_size on Postgres, binlog_expire_logs_seconds on MySQL).
• Task-partition-count mismatch. If you scale a source connector's tasks.max up or down, existing offsets may not map cleanly; some connectors handle it, some don't. Test on staging.

When DIY wins on cost + control — the scale flip and the custom-connector case.

• Scale. At >10 TB/day CDC throughput, managed pricing (per-row, per-GB) becomes prohibitive; DIY unit economics on a Kafka cluster you already own win. You trade platform cost for engineer-hours.
• Custom connectors. No managed tool implements a proprietary mainframe API, a bespoke internal REST feed, or an obscure legacy queue. Kafka Connect's open connector interface lets you write one in Java (or Kotlin) and deploy it in the same fleet.
• Existing Kafka footprint. If Kafka is already the operational backbone for your event bus, adding Connect on top is marginal ops overhead — no new stateful service.
• Regulatory / air-gap. Some deployments cannot send data to a SaaS control plane; DIY is the only option.

Common interview probes on Kafka Connect.

• "What's the difference between Kafka Connect and Debezium?" — required answer: Kafka Connect is the runtime; Debezium is a family of source connectors that run inside it.
• "What's an SMT?" — Single Message Transform: per-message in-flight transformation.
• "How do you handle bad records?" — DLQ via errors.tolerance=all + errors.deadletterqueue.topic.name.
• "When would you pick Kafka Connect over Streamkap?" — >10 TB/day scale, custom connectors, existing Kafka platform team, or air-gapped requirement.

Worked example — Debezium Postgres source + Snowflake sink in Distributed mode

Detailed explanation. The canonical DIY real-time ELT stack: a Distributed-mode Kafka Connect cluster running one Debezium Postgres source connector and one Snowflake sink connector, with SMTs for a light PII mask and a DLQ topic for bad records. Walk through the worker config, the two connector configs, and the operational surface.

• Cluster. 3 Connect workers on Kubernetes, Kafka broker cluster already running.
• Source. Debezium Postgres via pgoutput, single task.
• Sink. Snowflake sink connector, tasks.max=4 for parallelism.
• SMTs. MaskField on customers.email.
• DLQ. snowflake.dlq topic with errors.tolerance=all.

Question. Write the worker properties, the two connector configs, and the operational monitoring.

Input.

Component	Value
Workers	3, Distributed mode
Source	Debezium Postgres, orders + customers + line_items
Sink	Snowflake sink, MERGE mode
SMT	MaskField on customers.email (SHA-256)
DLQ	snowflake.dlq

Code.

# 1. worker.properties (identical on every worker; deployed via K8s ConfigMap)
bootstrap.servers=kafka-broker.internal:9092
group.id=connect-prod
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=false
value.converter.schemas.enable=false

# Offset / config / status storage (Kafka-backed, replicated + compacted)
offset.storage.topic=connect-offsets
offset.storage.replication.factor=3
offset.storage.partitions=25
offset.storage.cleanup.policy=compact

config.storage.topic=connect-configs
config.storage.replication.factor=3
config.storage.partitions=1
config.storage.cleanup.policy=compact

status.storage.topic=connect-status
status.storage.replication.factor=3
status.storage.partitions=5
status.storage.cleanup.policy=compact

# REST API for connector management
rest.host.name=0.0.0.0
rest.port=8083

# Plugin path — mounted from a K8s image with all connector JARs
plugin.path=/opt/connect/plugins

// 2. Debezium Postgres source connector — POST /connectors
{
  "name": "pg-prod-source",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "plugin.name": "pgoutput",
    "database.hostname": "db-primary.internal",
    "database.port": "5432",
    "database.user": "cdc_reader",
    "database.password": "${env:CDC_PASSWORD}",
    "database.dbname": "production",
    "slot.name": "dbz_prod",
    "publication.name": "dbz_pub",
    "snapshot.mode": "initial",
    "table.include.list": "public.orders,public.customers,public.line_items",
    "topic.prefix": "prod",
    "heartbeat.interval.ms": "10000",
    "tombstones.on.delete": "true",

    // SMT — mask the customers.email column
    "transforms": "mask_email",
    "transforms.mask_email.type": "org.apache.kafka.connect.transforms.MaskField$Value",
    "transforms.mask_email.fields": "email",
    "transforms.mask_email.replacement": "[MASKED]"
  }
}

// 3. Snowflake sink connector — POST /connectors
{
  "name": "snowflake-sink",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "tasks.max": "4",
    "topics": "prod.public.orders,prod.public.customers,prod.public.line_items",

    // Snowflake target
    "snowflake.url.name": "acme.snowflakecomputing.com:443",
    "snowflake.user.name": "CONNECT_WRITER",
    "snowflake.private.key": "${env:SNOWFLAKE_PRIVATE_KEY}",
    "snowflake.database.name": "RAW_CONNECT",
    "snowflake.schema.name": "PUBLIC",
    "snowflake.role.name": "CONNECT_WRITER",

    // Buffering — trade latency for throughput
    "buffer.count.records": "10000",
    "buffer.size.bytes": "5000000",
    "buffer.flush.time": "5",           // seconds

    // Converters
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",

    // DLQ — bad records go here instead of crashing the task
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "snowflake.dlq",
    "errors.deadletterqueue.context.headers.enable": "true",
    "errors.deadletterqueue.topic.replication.factor": "3",
    "errors.log.enable": "true",
    "errors.log.include.messages": "true"
  }
}

Step-by-step explanation.

1. The worker properties define the Distributed-mode cluster's storage topics (connect-offsets, connect-configs, connect-status) with replication.factor=3 and cleanup.policy=compact — the two settings that keep the fleet safe against a broker failure. Skimping on these is the most common "we lost our offsets" incident.
2. The Debezium Postgres source connector is submitted via POST /connectors (the Connect REST API). plugin.name=pgoutput uses Postgres's built-in logical-decoding plugin; slot.name=dbz_prod creates a replication slot; snapshot.mode=initial runs a consistent snapshot on first start then tails the WAL.
3. The transforms: mask_email block is an SMT applied per source-connector record. MaskField$Value replaces the email field's value with [MASKED] before the record enters the Kafka topic. All downstream sinks see the masked value; the raw email never leaves the connector's memory.
4. The Snowflake sink is submitted the same way. tasks.max=4 parallelises the sink across 4 tasks (one per topic-partition subset). buffer.count.records=10000 + buffer.flush.time=5 micro-batches Snowflake loads — smaller values reduce latency at higher Snowflake ingest cost.
5. errors.tolerance=all + errors.deadletterqueue.topic.name=snowflake.dlq sends any record the sink cannot process (schema mismatch, Snowflake DDL error) to the DLQ instead of crashing the task. errors.log.enable=true also logs each error for observability. Without these settings, one bad record halts the entire pipeline.

Output.

Stage	Behaviour	Metric
Postgres commit → Kafka topic	WAL decode via pgoutput, SMT applied	~100 ms
Kafka topic → Snowflake sink buffer	consumer fetch + convert	~500 ms
Sink buffer → Snowflake table	MERGE per micro-batch	1-5 sec
End-to-end p95	source commit → Snowflake row visibility	~2-5 sec
Bad record	routed to snowflake.dlq	pipeline continues

Rule of thumb. For any Distributed-mode Kafka Connect deployment, set replication.factor=3 and cleanup.policy=compact on all three internal storage topics, configure the DLQ + errors.tolerance=all on every sink, and monitor DLQ backlog as a first-class SLI. Never run production Connect in Standalone mode.

Worked example — SMT chain for column masking, filtering, and reshape

Detailed explanation. SMTs are the primary in-flight transform mechanism in Kafka Connect. They run per-message inside the source or sink pipeline and can be chained. Walk through a realistic SMT chain that masks PII, filters test records, and reshapes the message envelope to match downstream schema expectations.

• SMT 1 — MaskField. Replace email and phone fields with [MASKED].
• SMT 2 — Filter (Debezium's). Drop test records where customer_id < 100.
• SMT 3 — ExtractNewRecordState. Unwrap the Debezium envelope ({before, after, source, op}) to just the after row, plus a __op column.

Question. Configure the three-SMT chain on the Debezium Postgres source and show the before/after Kafka message shape.

Input.

SMT	Purpose	Order
MaskField	mask email + phone	1
Debezium Filter	drop customer_id < 100	2
ExtractNewRecordState	unwrap envelope	3

Code.

{
  "name": "pg-prod-source-chained",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "plugin.name": "pgoutput",
    "database.hostname": "db-primary.internal",
    "database.dbname": "production",
    "database.user": "cdc_reader",
    "database.password": "${env:CDC_PASSWORD}",
    "slot.name": "dbz_prod_chained",
    "publication.name": "dbz_pub",
    "table.include.list": "public.customers",
    "topic.prefix": "prod",

    // SMT chain — applied in order
    "transforms": "mask,filter,unwrap",

    "transforms.mask.type":      "org.apache.kafka.connect.transforms.MaskField$Value",
    "transforms.mask.fields":    "after.email,after.phone",
    "transforms.mask.replacement": "[MASKED]",

    "transforms.filter.type":      "io.debezium.transforms.Filter",
    "transforms.filter.language":  "jsr223.groovy",
    "transforms.filter.condition": "value.after.customer_id >= 100",

    "transforms.unwrap.type":                              "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.drop.tombstones":                   "false",
    "transforms.unwrap.delete.handling.mode":              "rewrite",
    "transforms.unwrap.add.fields":                        "op,source.ts_ms,source.lsn"
  }
}

// Before SMT chain — Debezium envelope
{
  "before": null,
  "after":  { "id": 42, "customer_id": 7, "email": "[e-mail]", "phone": "555-1234" },
  "source": { "lsn": 24591040, "ts_ms": 1720051260123, ... },
  "op": "c",
  "ts_ms": 1720051260200
}

// After SMT 1 (mask)
{
  "before": null,
  "after":  { "id": 42, "customer_id": 7, "email": "[MASKED]", "phone": "[MASKED]" },
  "source": { ... },
  "op": "c"
}

// After SMT 2 (filter) — only if customer_id >= 100; otherwise dropped

// After SMT 3 (unwrap) — flat shape ready for a warehouse sink
{
  "id": 42,
  "customer_id": 7,
  "email": "[MASKED]",
  "phone": "[MASKED]",
  "__op":            "c",
  "__source_ts_ms":  1720051260123,
  "__source_lsn":    24591040,
  "__deleted":       "false"
}

Step-by-step explanation.

1. The transforms: mask,filter,unwrap declaration is the pipeline in order — mask first (before the filter sees the sensitive values), filter second (drop test data), unwrap third (flatten the envelope for the sink). Order matters; wrong order = wrong results (e.g. filtering after unwrap breaks the filter's access to value.after.customer_id).
2. MaskField$Value operates on the record's value (as opposed to MaskField$Key for the message key). fields: after.email,after.phone targets nested fields using JSON-pointer-ish syntax. The replacement value is written back into those field positions.
3. Debezium's Filter SMT evaluates a Groovy (or JavaScript) expression against the record; if false, the record is dropped. Here we drop test records where customer_id < 100. Language choice affects filter performance — Groovy JSR-223 is the common default.
4. ExtractNewRecordState flattens the Debezium envelope into just the after row plus metadata columns. delete.handling.mode: rewrite converts DELETE events into a normal row with __deleted: true; add.fields: op,source.ts_ms,source.lsn copies those envelope fields into __op, __source_ts_ms, __source_lsn columns.
5. The result is a flat, per-row JSON message that most warehouse sinks (Snowflake, BigQuery, JDBC) can ingest directly without further transformation. The pipeline is production-ready: PII is masked before Kafka, test data is filtered before storage, and the envelope is warehouse-friendly.

Output.

Row	Before SMT	After SMT chain
customer_id=7, email=[e-mail]	full envelope, PII visible	flat row, PII masked, __op=c
customer_id=5 (test)	full envelope	(dropped by filter)
DELETE customer_id=42	envelope with op=d, before=...	flat row with __deleted=true

Rule of thumb. For any Kafka Connect deployment handling PII, place the MaskField SMT first in the transforms chain so downstream stages (including logs and DLQ) never see the raw value. Test SMT chains on a staging topic before deploying to production — a wrong order or wrong field path is silent, not loud.

Worked example — DLQ setup + replay handling

Detailed explanation. The DLQ is the answer to the "one bad record shouldn't halt the pipeline" problem. Configure it once per sink connector; monitor the DLQ topic as a first-class SLI; build a replay tool that inspects DLQ records and either fixes-and-republishes or discards. Walk through the full setup.

• The trigger. A record whose schema mismatches the sink's expected schema, or a Snowflake DDL error, or a network timeout that exceeds retry budget.
• The route. Record + failure metadata headers → DLQ topic.
• The response. Alert on DLQ backlog; investigation tool reads DLQ; operator decides to fix + republish, discard, or block.

Question. Configure the DLQ on the Snowflake sink and write a small replay tool that reads the DLQ, categorises failures, and prints an action recommendation.

Input.

Component	Value
DLQ topic	snowflake.dlq
Error tolerance	all
Headers	enabled (failure class + message)
Replication factor	3
Retention	30 days

Code.

// Sink connector — DLQ config
{
  "name": "snowflake-sink-dlq-ready",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "topics": "prod.public.orders,prod.public.customers",
    "snowflake.url.name": "acme.snowflakecomputing.com:443",
    "snowflake.database.name": "RAW_CONNECT",
    "snowflake.schema.name": "PUBLIC",

    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "snowflake.dlq",
    "errors.deadletterqueue.context.headers.enable": "true",
    "errors.deadletterqueue.topic.replication.factor": "3",
    "errors.log.enable": "true",
    "errors.log.include.messages": "true",
    "errors.retry.timeout": "300000",          // 5 min retry budget before DLQ
    "errors.retry.delay.max.ms": "60000"
  }
}

# dlq_triage.py — a tiny operator tool that reads the DLQ and categorises
from confluent_kafka import Consumer
from collections import Counter

consumer = Consumer({
    "bootstrap.servers": "kafka-broker.internal:9092",
    "group.id": "dlq-triage",
    "auto.offset.reset": "earliest",
    "enable.auto.commit": False,
})
consumer.subscribe(["snowflake.dlq"])

reasons = Counter()
sample = {}
processed = 0

while True:
    msg = consumer.poll(timeout=5.0)
    if msg is None:
        break
    if msg.error():
        continue

    headers = dict(msg.headers() or [])
    exc_class = headers.get(b"__connect.errors.exception.class.name", b"unknown").decode()
    exc_msg   = headers.get(b"__connect.errors.exception.message", b"").decode()

    reasons[exc_class] += 1
    if exc_class not in sample:
        sample[exc_class] = (msg.topic(), msg.partition(), msg.offset(), exc_msg)
    processed += 1
    if processed >= 10_000:
        break

print(f"Processed {processed} DLQ records")
for reason, count in reasons.most_common():
    print(f"  {reason:60s} {count:>6d}")
    tp, part, off, ex = sample[reason]
    print(f"    example @ {tp}/{part}/{off}: {ex[:120]}")

# Example output — 10,000 DLQ records over 24 hours
Processed 10000 DLQ records
  org.apache.kafka.connect.errors.SchemaProjectorException      6820
    example @ prod.public.orders/2/34521: Schema mismatch on field 'total_cents': expected INT64, got STRING
  net.snowflake.client.jdbc.SnowflakeSQLException                2410
    example @ prod.public.customers/1/12034: Column 'legacy_col' not found in target table
  org.apache.kafka.connect.errors.DataException                   770
    example @ prod.public.line_items/0/8811: Invalid JSON in message value

# Action recommendations (human-driven, based on the report)
# 1. SchemaProjectorException — source schema evolution not propagated to sink;
#    reconfigure sink with updated schema; replay from earliest DLQ offset.
# 2. SnowflakeSQLException on legacy_col — sink target missing column;
#    run `ALTER TABLE ... ADD COLUMN legacy_col`; replay.
# 3. DataException invalid JSON — upstream producer bug; fix producer;
#    drop bad records (small volume, historical data acceptable loss).

Step-by-step explanation.

1. The sink connector config wires errors.tolerance=all (never crash the task on record errors) + errors.deadletterqueue.topic.name=snowflake.dlq (route bad records here) + errors.deadletterqueue.context.headers.enable=true (include failure metadata as headers). Without headers, the DLQ has records but no way to categorise them — investigation becomes archaeology.
2. errors.retry.timeout=300000 gives the sink 5 minutes of retries before writing to the DLQ. Transient errors (network blips, Snowflake credential rotation) recover; permanent errors (schema mismatch, missing target column) hit the DLQ. Tune this to your acceptable retry budget.
3. The Python triage tool consumes the DLQ as a normal Kafka consumer, reads the failure headers, and categorises by exception class. This is the right level for a first-pass triage — categorisation drives action decisions.
4. The example output shows a realistic breakdown: ~70% schema mismatches (usually source schema evolution not applied to sink), ~25% Snowflake-side DDL errors (missing target columns), ~5% actual bad data (producer bugs). Each category has a distinct remediation path.
5. The replay contract: fix the root cause (sink config, target DDL, producer bug), then replay by resetting the DLQ consumer offset to earliest and re-processing. Idempotency at the sink is required — the Snowflake sink's MERGE handles it naturally; other sinks (JDBC upsert, Elasticsearch by _id) need care.

Output.

Failure class	Volume	Root cause	Remediation
SchemaProjectorException	68%	source added a field, sink schema stale	update sink; replay
SnowflakeSQLException	24%	target missing column	ALTER TABLE ADD COLUMN; replay
DataException	8%	producer bug	fix producer; drop or replay
ConnectException (transient)	(retried)	network / credential blip	recovered pre-DLQ

Rule of thumb. For every production Kafka Connect sink, ship the DLQ config + a triage script + a DLQ backlog alert before the connector goes live. The DLQ is not an "error log" — it's a durable, replayable buffer of records the sink couldn't handle. Treating it as a first-class part of the pipeline is the difference between a resilient stack and a fragile one.

Senior interview question on Kafka Connect

A senior interviewer might ask: "You're the platform engineer at a hyperscaler with 40 TB/day CDC volume from Postgres/MySQL into Snowflake + Elasticsearch + a downstream microservice bus. Managed tools priced you at $500k/month and you already run a Kafka platform team. Design the Kafka Connect deployment — worker fleet sizing, source + sink connector strategy, DLQ + monitoring, upgrade cadence, and the runbook when the offsets topic loses its leader broker."

Solution Using Distributed Connect + Debezium sources + Snowflake / Elasticsearch sinks + DLQ + full observability

# 1. Kubernetes deployment — 12 Connect workers, Distributed mode
apiVersion: apps/v1
kind: StatefulSet
metadata: { name: kafka-connect-prod }
spec:
  replicas: 12
  template:
    spec:
      containers:
      - name: connect
        image: acme/kafka-connect:3.7.0-debezium-2.5-snowflake-2.3-es-14
        resources:
          requests: { cpu: "4", memory: "12Gi" }
          limits:   { cpu: "8", memory: "16Gi" }
        env:
        - name: CONNECT_GROUP_ID
          value: "connect-prod-fleet"
        - name: CONNECT_OFFSET_STORAGE_TOPIC
          value: "connect-offsets"
        - name: CONNECT_OFFSET_STORAGE_REPLICATION_FACTOR
          value: "3"
        - name: CONNECT_OFFSET_STORAGE_PARTITIONS
          value: "50"
        - name: CONNECT_CONFIG_STORAGE_REPLICATION_FACTOR
          value: "3"
        - name: CONNECT_STATUS_STORAGE_REPLICATION_FACTOR
          value: "3"

// 2. Debezium Postgres source (per source DB — 6 instances at this scale)
{
  "name": "pg-shard-01-source",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "plugin.name": "pgoutput",
    "database.hostname": "pg-shard-01.internal",
    "slot.name": "dbz_shard_01",
    "publication.name": "dbz_pub",
    "snapshot.mode": "initial",
    "snapshot.max.threads": "8",
    "heartbeat.interval.ms": "10000",
    "schema.include.list": "public",
    "tombstones.on.delete": "true",

    "transforms": "unwrap",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.drop.tombstones": "false",
    "transforms.unwrap.delete.handling.mode": "rewrite",
    "transforms.unwrap.add.fields": "op,source.ts_ms,source.lsn"
  }
}

// 3. Snowflake + Elasticsearch sinks (per topic-family)
// Snowflake sink
{
  "name": "snowflake-warehouse-sink",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "tasks.max": "12",
    "topics.regex": "prod\\.public\\..*",
    "snowflake.database.name": "RAW_CONNECT",
    "snowflake.schema.name": "PUBLIC",
    "buffer.count.records": "50000",
    "buffer.flush.time": "10",
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "snowflake.dlq"
  }
}

// Elasticsearch sink
{
  "name": "es-search-sink",
  "config": {
    "connector.class": "io.confluent.connect.elasticsearch.ElasticsearchSinkConnector",
    "tasks.max": "8",
    "topics.regex": "prod\\.public\\.(products|customers)",
    "connection.url": "https://es-cluster.internal:9200",
    "type.name": "_doc",
    "key.ignore": "false",
    "schema.ignore": "true",
    "behavior.on.null.values": "delete",   // tombstones become deletes
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "es.dlq"
  }
}

# 4. Prometheus scrape config for JMX metrics
scrape_configs:
- job_name: kafka-connect
  static_configs:
  - targets: ["connect-0:9095", ..., "connect-11:9095"]
# Key metrics: connect_source_task_records_polled_total,
#              connect_sink_task_records_consumed_total,
#              connect_worker_task_count,
#              connect_worker_rebalance_completed_total

Step-by-step trace.

Layer	Config	Reasoning
Worker fleet	12 pods, 4-8 vCPU, 12-16 GB RAM	40 TB/day throughput headroom
Source connectors	6 Debezium Postgres, 1 task each	one connector per source shard
Sink connectors	1 Snowflake (12 tasks) + 1 ES (8 tasks)	parallelism matches throughput
DLQ	per-sink DLQ topic, 3× RF, headers on	replayable failure buffer
Offsets topic	50 partitions, RF=3, compact	fault-tolerant offset storage
Monitoring	JMX → Prometheus + DLQ backlog alerts	task health + failure SLI
Upgrades	rolling, one worker at a time	tasks rebalance to healthy workers

After deployment, 40 TB/day flows through the fleet with p95 end-to-end latency ~3-5 seconds; DLQ backlogs stay under 100 records/day steady state; the 6 Postgres replication slots stay under 1 GB lag with heartbeats + healthy sink throughput. Rolling upgrades happen weekly (one worker at a time) with zero pipeline downtime.

Output:

Metric	Value
Throughput	40 TB/day sustained
End-to-end latency p95	3-5 sec
Worker fleet cost	~$18k/month K8s + ~$8k Kafka amortised
Ops FTE	1.5 (platform team splits time)
DLQ backlog steady state	< 100 records/day
Comparison — managed alternative	~$500k/month (10× more)

Why this works — concept by concept:

• Distributed-mode worker fleet — 12 workers with tasks rebalanced automatically on join/leave. One worker crash = one rebalance cycle (seconds); a data-centre-level outage = degraded throughput until failed workers replaced. No single-point-of-failure in the runtime.
• One source connector per shard + Snowflake sink with topics.regex — sources are single-task by protocol constraint (WAL is sequential); the sink parallelises across topics via topics.regex + tasks.max. The 12-task Snowflake sink absorbs 40 TB/day without per-topic connector explosion.
• DLQ with headers + replay tool — every sink has an errors-tolerant path to a durable DLQ topic; a lightweight triage script categorises failures; operators fix + replay. This is what makes the pipeline resilient to source schema drift and target-DDL misalignment.
• Kafka-backed offset / config / status storage — the three internal topics with replication.factor=3 + cleanup.policy=compact are the durability contract for the fleet's state. Losing a broker doesn't lose offsets; a rebalance re-assigns tasks; work resumes.
• Cost — ~$26k/month all-in for the fleet + shared Kafka amortisation, ~1.5 FTE for the platform team's Connect share, sustained 40 TB/day CDC throughput. Compared to the managed alternative at ~$500k/month, DIY wins by an order of magnitude on unit economics at this scale — but only because the Kafka platform team is a pre-existing sunk cost. At 400 GB/day the numbers reverse.

Streaming
Topic — streaming
Streaming Kafka Connect + Debezium DIY problems

Practice →

Design Topic — design Design problems on real-time ELT platforms

Practice →

---

5. Decision matrix + when to pick each + interview signals

The three-tool decision reduces to five inputs — walk the matrix out loud in the interview and pick the tool that satisfies every constraint

The mental model in one line: the real-time ELT tool choice is a five-input decision — in-flight transforms? warehouse-only downstream? scale under 5 TB/day? dedicated streaming platform team? bespoke source connector? — and the answers pick one of Estuary Flow, Streamkap, or Kafka Connect DIY, with the boundaries between them scale-dependent and workload-dependent in ways that senior architects have to defend from first principles rather than by trend. Every senior data-engineering interviewer in 2026 asks the tool-selection question because it separates architects who understand the crossover points from candidates who parrot a single vendor's marketing.

The five decision inputs — memorise the order.

• In-flight transforms. Do you need to aggregate, join, filter, or mask before the warehouse? Yes → Estuary Flow (derivations are the differentiator). No → continue.
• Warehouse-only downstream. Is your only sink Snowflake / BigQuery / Databricks / Redshift? Yes → continue to scale check. No (also feeding search index, event bus, feature store, microservice bus) → Estuary Flow (broadest sink) or Kafka Connect (own the fan-out).
• Scale — under 5 TB/day. Is your CDC volume comfortably under 5 TB/day and table count under 100? Yes → Streamkap wins on TCO for warehouse-only. No → continue.
• Dedicated streaming platform team. Do you have (or can hire) engineers whose job is running Kafka + Connect? Yes → Kafka Connect DIY is viable. No → Estuary Flow (fallback for scale beyond Streamkap's sweet spot).
• Bespoke source connector. Does your workload require a connector no managed vendor implements? Yes → Kafka Connect DIY regardless of the other inputs.

The three canonical winners — where each is the clean answer.

• Estuary Flow wins. Multi-engine downstream (warehouse + event bus + search), in-flight transforms required (aggregations / joins / PII masks), moderate scale (5-500 tables, 100 GB - 5 TB/day), no dedicated Kafka team. Sweet spot: fintech / regtech / operational-analytics platforms.
• Streamkap wins. Warehouse-only, no in-flight transforms, small-to-mid scale (5-100 tables, sub-5 TB/day CDC), zero Kafka operators, sub-5-sec freshness. Sweet spot: mid-market SaaS / e-commerce / B2B data teams.
• Kafka Connect wins. Hyperscale (>10 TB/day CDC), bespoke source connectors, existing Kafka platform team, or air-gapped / regulatory constraints preventing SaaS control plane. Sweet spot: Big Tech / hyperscalers / regulated industries with in-house Kafka expertise.

The four interview probes senior architects pre-empt.

• "What's your latency target?" — always name a number (sub-second, sub-5-sec, sub-minute); the tool falls out. Sub-second → Estuary Flow. Sub-5-sec → Streamkap or Estuary Flow. Sub-minute → any of the three.
• "How do you handle exactly-once?" — name the mechanism per tool: Estuary Flow = gazette 2PC + idempotent materialization. Streamkap = at-least-once + idempotent MERGE. Kafka Connect = at-least-once + wired EOS if needed.
• "How does schema evolution work?" — managed tools = automatic for additive, user-acknowledged for destructive. Kafka Connect = schema registry + BACKWARD compat mode + sink config.
• "How do you monitor it?" — always name slot-lag as the primary SLI for any CDC source; sink backlog + DLQ backlog for sinks; end-to-end p95 latency for the pipeline.

The senior-signal answers.

• Do you say "the tool depends on scale and transform requirements" and then walk the tree? — senior signal.
• Do you name the exact scale flip (~5-10 TB/day) where managed pricing loses to DIY? — senior signal.
• Do you push back on "just use Kafka Connect because it's open source" with a TCO calculation? — senior signal.
• Do you name hybrid patterns (Streamkap for boring CDC + Kafka Connect for custom SaaS) as valid architectures? — senior signal.
• Do you describe exactly-once as a system-level property, not "at-least-once with dedupe"? — required answer.

Worked example — the three-tool decision matrix (memorise)

Detailed explanation. The single most useful artifact for a real-time ELT interview is a memorised 3×6 matrix (three tools × six axes). Every senior real-time-ELT discussion converges on this within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one.

• Axes. Latency p95, exactly-once, schema evolution, connector breadth, TCO at small scale, TCO at large scale, ops burden.
• Scoring. 1-5 dots per cell, from lowest to highest.
• Usage. Draw it on the whiteboard first; let the pattern fall out of the constraints.

Question. Build the memorable matrix and pick the tool for three canonical scenarios.

Input.

Axis	Estuary Flow	Streamkap	Kafka Connect
Latency p95	sub-second	1-5 sec	1-10 sec (tuning)
Exactly-once	native	at-least-once + idempotent MERGE	wired if needed
Schema evolution	automatic (collection versioning)	automatic + policy	schema registry required
Connector breadth	~200	~50	~200+ (open ecosystem)
TCO at 1 TB/day	best per-value	best per-value	worst (fleet fixed cost)
TCO at 40 TB/day	prohibitive	prohibitive	best (unit economics)
Ops burden	low	very low	high

Code.

# Decision-tree helper mirroring the 5-input model
def pick_tool(scenario: dict) -> str:
    if scenario.get("bespoke_source_connector"):
        return "kafka-connect (bespoke connector override)"

    if scenario.get("needs_transforms"):
        return "estuary-flow"

    if scenario.get("warehouse_only") and scenario.get("cdc_tbday", 0) < 5:
        return "streamkap"

    if scenario.get("cdc_tbday", 0) > 10 and scenario.get("has_kafka_team"):
        return "kafka-connect"

    return "estuary-flow (fallback)"

scenarios = [
    {"name": "Mid-market SaaS", "warehouse_only": True, "cdc_tbday": 0.5},
    {"name": "Fintech + PII", "warehouse_only": True, "cdc_tbday": 1.0, "needs_transforms": True},
    {"name": "Hyperscaler", "warehouse_only": True, "cdc_tbday": 40, "has_kafka_team": True},
    {"name": "Mainframe integration", "bespoke_source_connector": True},
    {"name": "Multi-sink SaaS", "warehouse_only": False, "cdc_tbday": 2},
]
for s in scenarios:
    print(f"{s['name']:30s} → {pick_tool(s)}")

Step-by-step explanation.

1. The matrix rows are the six axes senior interviewers probe. Each cell is a qualitative score, not a benchmark number — the point is to memorise the shape of the differences, not the numeric details.
2. Latency p95 favours Estuary Flow at the low end (sub-second) and Kafka Connect at the high end when tuning matters. Streamkap is comfortably in the 1-5-sec band which covers most warehouse workloads.
3. Exactly-once is native in Estuary Flow (gazette 2PC), effective in Streamkap (idempotent MERGE), and possible-but-manual in Kafka Connect (transactional producers + idempotent sinks + careful config).
4. TCO flips at scale: at 1 TB/day both managed tools beat DIY on true TCO once ops is counted; at 40 TB/day DIY wins on unit economics but only if you already have a Kafka team.
5. The five scenarios walk the decision tree end-to-end and produce a clean tool pick for each. Senior interviewers score highest when you can hand-execute this in under 60 seconds per scenario.

Output.

Scenario	Tool	Primary reason
Mid-market SaaS (0.5 TB/day)	Streamkap	warehouse-only + small scale
Fintech + PII (1 TB/day, transforms)	Estuary Flow	in-flight masks required
Hyperscaler (40 TB/day)	Kafka Connect	scale flip + platform team
Mainframe integration	Kafka Connect	bespoke connector override
Multi-sink SaaS (2 TB/day, warehouse + ES + bus)	Estuary Flow	broadest sink coverage

Rule of thumb. Draw the 3×6 matrix on the whiteboard before naming a tool. Walk the five-input decision tree out loud. Explain the scale flip where DIY beats managed. Never claim one tool is universally best; the crossover points are real and interviewers know it.

Worked example — hybrid patterns (Streamkap + Kafka Connect side by side)

Detailed explanation. Real teams often run two real-time ELT stacks side by side. The boring Postgres → Snowflake path uses Streamkap (least surface, meets SLA); a custom SaaS ingestion path uses Kafka Connect (custom source connector no managed tool supports). Walk through the hybrid design and its cost.

• Path 1. Streamkap for Postgres → Snowflake (40 tables, 1 TB/day).
• Path 2. Kafka Connect for custom mainframe API → Elasticsearch + Snowflake (5 GB/day, bespoke connector).
• Total ops burden. ~0.15 FTE (mostly Kafka Connect operations).
• Total cost. ~$25k/year Streamkap + ~$15k/year Connect infra + engineer time.

Question. Design the hybrid split and justify each path's tool choice.

Input.

Path	Volume	Source	Sink	Tool	Rationale
Postgres → Snowflake	1 TB/day	Postgres CDC	Snowflake	Streamkap	warehouse-only + managed
Mainframe → ES + Snowflake	5 GB/day	proprietary mainframe API	Elasticsearch + Snowflake	Kafka Connect	bespoke source connector required

Code.

# 1. Streamkap pipeline (Path 1)
{ "pipeline_name": "pg-prod-to-snowflake",
  "source": { "type": "postgres", "host": "db-primary.internal", ... },
  "destination": { "type": "snowflake", "database": "RAW_STREAMKAP", ... },
  "tables": [ /* 40 entries */ ] }

// 2. Kafka Connect — custom mainframe source (Path 2)
{
  "name": "mainframe-source",
  "config": {
    "connector.class": "com.acme.connect.MainframeSourceConnector",
    "tasks.max": "1",
    "mainframe.host": "mainframe-01.internal",
    "mainframe.port": "3270",
    "mainframe.session": "PROD01",
    "topic.prefix": "mainframe",
    "poll.interval.ms": "5000"
  }
}

// 3. Kafka Connect — Elasticsearch + Snowflake sinks (Path 2)
{
  "name": "mainframe-es-sink",
  "config": {
    "connector.class": "io.confluent.connect.elasticsearch.ElasticsearchSinkConnector",
    "topics.regex": "mainframe\\..*",
    "connection.url": "https://es-cluster.internal:9200",
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "es.dlq"
  }
}

{
  "name": "mainframe-snowflake-sink",
  "config": {
    "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
    "topics.regex": "mainframe\\..*",
    "snowflake.database.name": "RAW_MAINFRAME",
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "snowflake.dlq"
  }
}

Step-by-step explanation.

1. Path 1 uses Streamkap because the requirements are the poster child for it: warehouse-only, moderate scale, no in-flight transforms, no custom connector needed. Any other tool at this scale would be over-engineered.
2. Path 2 uses Kafka Connect because no managed tool supports the mainframe protocol. The custom source connector is a ~2-week Java project (once); the Kafka Connect fleet operates it as a peer to any built-in connector.
3. The two paths land in the same Snowflake account (separate database schemas: RAW_STREAMKAP and RAW_MAINFRAME). Downstream dbt models can join them for analytics; the paths are independent operationally.
4. The Kafka Connect fleet for Path 2 is a minimal deployment: 3 workers on Kubernetes, ~$1k/month infrastructure, ~0.1 FTE ops. The custom-connector engineering is a fixed one-time cost.
5. The hybrid pattern is common in mature teams: use managed tools for the 80% of workloads they excel at; keep a small Kafka Connect fleet as the escape hatch for the 20% that need custom work. Nobody runs everything in one tool; the interview signal is knowing when to split.

Output.

Component	Cost	Ops FTE
Streamkap (Path 1)	$22k/year	0.05
Kafka Connect infra (Path 2)	$12k/year	0.10
Custom mainframe connector (one-time)	$30k engineering	(amortised)
Total year 1	~$64k	0.15
Total year 2+	~$34k	0.15

Rule of thumb. For real teams, the answer is rarely "one tool for everything." Design the hybrid: managed for the boring 80%, DIY for the custom 20%. The hybrid answer is a senior signal in interviews because it shows you understand tool boundaries rather than tool cults.

Worked example — interview signals recap

Detailed explanation. The senior real-time ELT interview has predictable follow-up questions once you've named a tool. Prepare answers for each so you can respond fluently without stalling. Walk through the top-10 senior signals and the answer that hits each.

• Signal 1. "Why not Fivetran?" — batch vs streaming category distinction.
• Signal 2. "How do you handle exactly-once?" — mechanism per tool.
• Signal 3. "What's the scale flip?" — ~5-10 TB/day, managed → DIY.
• Signal 4. "How do you monitor slot lag?" — Prometheus + alert thresholds.
• Signal 5. "How do you replay 24 hours of events?" — per-tool answer.

Question. Draft a one-sentence answer for each of the ten top interview signals.

Input.

Signal	Weak answer	Senior answer
Why not Fivetran?	"we could use Fivetran"	"Fivetran is batch (15-min cadence); real-time is a different category"
Exactly-once?	"we dedupe downstream"	"Estuary is native EOS; Streamkap = at-least-once + idempotent MERGE; Connect = wire it"
Scale flip?	"Kafka scales"	"~5-10 TB/day CDC is the managed → DIY crossover on unit economics"
Slot lag?	"we look at Postgres"	"Prometheus scrape of pg_replication_slots.lag_bytes; alert > 1 GB for 10 min"
Replay?	"restart the job"	"reset slot LSN + re-emit (log-based); rewind Kafka offset (Connect); Streamkap = trigger re-sync"
Schema drop?	"we rebuild"	"managed = user-acknowledge; Connect = schema registry + BACKWARD compat"
Custom connector?	"we build one"	"Kafka Connect implements SourceConnector interface; ~2 engineer-weeks"
Multi-sink?	"we duplicate"	"Estuary Flow one collection → many materializations; Connect one topic → many sinks"
PII masking?	"we mask in warehouse"	"in-flight: Flow derivation, Streamkap transformation, or Connect SMT"
TCO?	"cost depends"	"walk the model: rows/GB × managed rate vs infra + engineer time"

Code.

Top-10 senior signal cheat card
================================

1. "Why not Fivetran?" — Fivetran is batch (15-min default); real-time
   ELT is sub-5-second. Different tool category.

2. "Exactly-once?" — Estuary Flow: gazette 2PC + idempotent
   materialization. Streamkap: at-least-once + idempotent MERGE by PK
   with LSN guard. Kafka Connect: at-least-once by default; EOS
   requires transactional producers + idempotent sinks + config care.

3. "Scale flip?" — ~5-10 TB/day CDC. Below: managed wins TCO. Above:
   DIY wins unit economics if a Kafka team exists.

4. "Slot lag?" — Prometheus scrape of pg_replication_slots.lag_bytes;
   alert on > 1 GB for 10 min; hard cap via max_slot_wal_keep_size.

5. "Replay 24 hours?" — Log-based: reset slot LSN. Kafka Connect:
   rewind consumer offset. Streamkap: trigger re-sync via UI or API.

6. "Column drop?" — Managed: user-acknowledge in UI/policy. Kafka
   Connect: schema registry + BACKWARD compat + sink retry.

7. "Custom source connector?" — Kafka Connect implements
   SourceConnector interface; ~2 engineer-weeks; deploy in fleet.

8. "Multi-sink fan-out?" — Estuary Flow: one collection → many
   materializations. Kafka Connect: one topic → many sink connectors.
   Streamkap: single-sink today; use hybrid if multi.

9. "PII masking?" — In-flight: Estuary Flow derivation, Streamkap
   transformation, or Kafka Connect MaskField SMT. Never mask in
   warehouse only; the raw value is durable there.

10. "TCO?" — Walk the model. Managed: rows/GB × published rate. DIY:
    infra + engineer time (typically 1-2 FTE at scale). Crossover
    around 5-10 TB/day CDC volume.

Step-by-step explanation.

1. Signals 1-2 are the framing questions — always ask yourself "is the interviewer probing batch vs streaming?" or "is the interviewer probing semantics?" before answering. The senior signal is the mechanism per tool, not "we handle it."
2. Signals 3-4 are the operational questions — scale + monitoring. The senior signal is a specific number (5-10 TB/day flip, 1 GB slot lag alert) rather than qualitative words.
3. Signals 5-6 are the failure-mode questions — replay + schema evolution. The senior signal is the per-tool mechanism, not "we retry."
4. Signals 7-8 are the design questions — custom connectors + fan-out. The senior signal is naming the interface / mechanism (SourceConnector, collection → materialization), not "we build it."
5. Signals 9-10 are the correctness + cost questions — PII + TCO. The senior signal is the why (raw values durable in warehouse if not masked in-flight) and the how (walk the TCO model) rather than a hand-wave.

Output.

Signal	Interviewer expects	Fluent answer time
1. Fivetran?	category distinction	15 sec
2. Exactly-once?	mechanism per tool	30 sec
3. Scale flip?	specific number	15 sec
4. Slot lag?	Prometheus + threshold	15 sec
5. Replay?	per-tool mechanism	30 sec
6. Column drop?	policy per tool	20 sec
7. Custom connector?	interface + effort estimate	20 sec
8. Multi-sink?	fan-out mechanism	20 sec
9. PII?	in-flight mask per tool	20 sec
10. TCO?	walk the model	45 sec

Rule of thumb. Rehearse the top-10 senior signal answers as a spoken script. If any answer takes you more than the target time to deliver fluently, you're not ready for the senior real-time ELT interview. Practice against a whiteboard until the tree + matrix + signal answers are muscle memory.

Senior interview question on tool selection

A senior interviewer might ask: "Your CTO gives you 30 days to recommend a real-time ELT tool for a 3-year commitment. The workload is 80 tables from Postgres and MySQL into Snowflake and Elasticsearch, ~2 TB/day CDC, a 6-engineer data team with no dedicated Kafka expertise, and a regulatory requirement to mask PII before the warehouse. Walk me through your evaluation, the scoring rubric, the pilot design, and the recommendation with justification."

Solution Using an axis-scored evaluation + 4-week pilot + Estuary Flow recommendation

# 1. eval_pipeline.py — axis-scored comparison for the workload
CANDIDATES = ["estuary-flow", "streamkap", "kafka-connect"]
AXES = ["latency_sub_5s", "exactly_once", "schema_evolution",
        "multi_sink", "in_flight_pii_mask", "no_kafka_team",
        "tco_year_1", "tco_year_2_3"]

SCORES = {
    "estuary-flow":  {"latency_sub_5s": 5, "exactly_once": 5, "schema_evolution": 5,
                      "multi_sink": 5, "in_flight_pii_mask": 5, "no_kafka_team": 5,
                      "tco_year_1": 4, "tco_year_2_3": 4},
    "streamkap":     {"latency_sub_5s": 5, "exactly_once": 4, "schema_evolution": 5,
                      "multi_sink": 2, "in_flight_pii_mask": 3, "no_kafka_team": 5,
                      "tco_year_1": 5, "tco_year_2_3": 5},
    "kafka-connect": {"latency_sub_5s": 4, "exactly_once": 3, "schema_evolution": 3,
                      "multi_sink": 5, "in_flight_pii_mask": 4, "no_kafka_team": 1,
                      "tco_year_1": 2, "tco_year_2_3": 3},
}

def total(tool: str) -> int:
    return sum(SCORES[tool][a] for a in AXES)

if __name__ == "__main__":
    ranked = sorted(CANDIDATES, key=total, reverse=True)
    for t in ranked:
        print(f"{t:15s} total={total(t)}")
    # → estuary-flow    total=38   ← winner
    # → streamkap       total=34
    # → kafka-connect   total=25

# 2. Pilot plan — 4 weeks, one non-critical table per candidate
week_01:
  - Provision trials: Estuary Flow, Streamkap, Kafka Connect on K8s
  - Configure Postgres slot + publication for a `customers_test` table
week_02:
  - Run each tool with the same source + Snowflake target + Elasticsearch target
  - Measure: latency, throughput, schema-evolution behaviour, DLQ (Connect only)
week_03:
  - PII masking test: mask `email` column and verify raw value never leaves connector
  - Failure test: kill sink for 30 min; measure resume behaviour + data loss
week_04:
  - Write up per-tool scorecard; recommend to CTO with rationale + 3-year TCO

# 3. Recommendation memo
============================================================
Recommendation: Estuary Flow (managed streaming ELT)

Rationale
---------
- Meets latency SLA (sub-5-sec p95 for 80-table Postgres/MySQL → Snowflake)
- Native multi-sink fan-out (Snowflake + Elasticsearch via two materializations)
- In-flight PII masking via SQLite derivation (regulatory requirement)
- No Kafka operator required (6-engineer team has no such expertise)
- Native exactly-once (gazette 2PC + idempotent materialization checkpoints)
- 3-year TCO ~$120k vs Kafka Connect ~$180k (mostly engineer time)

Runner-up: Streamkap
- Would win on TCO if only Snowflake sink were needed
- Loses on multi-sink and in-flight PII mask flexibility
- Recommended as fallback if a critical Estuary Flow limitation surfaces

Not recommended: Kafka Connect DIY
- No Kafka operator on the team; hiring one adds ~$180k/year cost
- 2 TB/day CDC is below the DIY-favorable scale flip
- Would require ~9 months to reach production maturity vs 6 weeks for Estuary Flow
============================================================

Step-by-step trace.

Concern	Answer	Reasoning
Latency SLA	sub-5-sec	Estuary Flow ships sub-second natively
Multi-sink	Snowflake + ES via 2 materializations	Estuary Flow one collection → many sinks
PII masking	SQLite derivation	in-flight, warehouse never sees raw
Ops burden	managed control plane	matches 6-engineer team capacity
Exactly-once	gazette 2PC + idempotent materialization	regulatory + correctness
3-year TCO	~$120k	best among candidates for this workload

After the 4-week pilot, the scorecard picks Estuary Flow as the primary with Streamkap as the fallback; Kafka Connect DIY is ruled out due to the team's lack of Kafka expertise and the workload being below the DIY-favorable scale flip. Production rollout takes 6 additional weeks (60-table migration + parity harness).

Output:

Metric	Value
Evaluation duration	4 weeks pilot + 2 weeks recommendation
Production rollout	6 weeks (batched migration)
Year-1 TCO	~$45k (transition year)
Year 2-3 TCO	~$38k/year
3-year total	~$120k
Alternatives ruled out	Kafka Connect (no Kafka team); Fivetran (batch, wrong category)

Why this works — concept by concept:

• Axis-scored evaluation — turning "which is better" into "which scores highest across our specific axes" removes vibes-based decision-making. The scorecard is the artefact that survives the CTO meeting.
• 4-week pilot with real workload — never pick a tool based on demos or vendor slides. A pilot with your actual Postgres table + your actual sink + your actual failure test surfaces bugs no vendor mentions.
• Multi-sink materialization + PII derivation — the two Estuary Flow features that lock the recommendation. Streamkap's single-sink model would require a second pipeline for Elasticsearch; Kafka Connect would require SMT + custom sink management.
• 3-year TCO with engineer time counted — comparing tools without counting engineer time systematically undercounts DIY. The ~$60k/year gap between Estuary Flow and Kafka Connect is real once ops FTE is priced.
• Cost — 4 weeks of ~0.5 FTE evaluation, 6 weeks of ~1 FTE migration, ~$38k/year steady-state platform cost. The eliminated cost is the "we picked wrong and now we're rebuilding in year 3" outcome that batch-first tools frequently produce for real-time requirements. Compared to a rushed Kafka Connect adoption without expertise, this saves ~$180k/year in avoided hire + ~9 months of production-readiness lag.

Design
Topic — design
Design problems on real-time ELT tool selection

Practice →

Streaming
Topic — streaming
Streaming interview signal + hybrid-architecture problems

Practice →

---

Cheat sheet — Real-time ELT recipes

• Which tool when. Estuary Flow is the 2026 default for teams that need in-flight transforms (aggregations, joins, PII masks) or multi-sink fan-out (warehouse + search + event bus) without owning a Kafka cluster — managed streaming with native exactly-once via the open-source gazette broker. Streamkap is the default for warehouse-only CDC at small-to-mid scale (5-100 tables, sub-5 TB/day) — the least operational surface for "just get Postgres into Snowflake in real time." Kafka Connect DIY is the answer at hyperscale (>10 TB/day CDC), when a bespoke source connector is required, when an existing Kafka platform team can absorb the ops, or when regulatory / air-gap constraints preclude SaaS control planes.
• Estuary Flow catalog YAML template. Single file with captures: (source connectors reading into collections), collections: (durable JSON-schema-validated logs, key: [/id] for the PK), optional collections/<name>/derive: (SQLite or TypeScript transforms with shuffle: key: [/customer_id] for stateful correctness), and materializations: (sink connectors with fields: recommended: true for full-column sync). Deploy via flowctl catalog publish --source catalog.flow.yaml; version-control in git; deploy via CI. Enable shards.hot_standbys: 1 on derivations for production-grade failover.
• Streamkap Postgres → Snowflake config recipe. Source: type=postgres, plugin=pgoutput, slot_name=streamkap_<env>, publication_name=streamkap_pub, heartbeat_interval_ms=10000. Destination: type=snowflake, database=RAW_STREAMKAP, dedicated schema=PUBLIC, sink runs idempotent MERGE by PK with __streamkap_lsn guard. Schema evolution: column_add=auto, type_widening=auto, column_drop=manual, type_narrowing=manual. Alerting: slot_lag_bytes_warn=1073741824 (1 GB), slot_lag_bytes_alert=5368709120 (5 GB), slot_inactive_min=15.
• Kafka Connect Debezium + JDBC-sink JSON. Worker: 3+ pods in Distributed mode, group.id=connect-prod, offset.storage.replication.factor=3 + cleanup.policy=compact on all three internal topics. Source: Debezium Postgres with plugin.name=pgoutput, snapshot.mode=initial, heartbeat.interval.ms=10000, tombstones.on.delete=true. Sink: Snowflake / JDBC / Elasticsearch with errors.tolerance=all, errors.deadletterqueue.topic.name=<sink>.dlq, errors.deadletterqueue.context.headers.enable=true, errors.retry.timeout=300000. Always ship DLQ + triage script + backlog alert on day one.
• Exactly-once semantics matrix. Estuary Flow = native (gazette 2PC + idempotent materialization checkpoints); no config knob needed. Streamkap = at-least-once at the buffer + idempotent MERGE by PK at the sink with __streamkap_lsn guard — effectively exactly-once for the warehouse. Kafka Connect = at-least-once by default; EOS requires transactional producers (transactional.id), idempotent sinks (MERGE by PK or insertMode=upsert), and exactly.once.support=enabled on supported connectors. Never claim EOS without naming the mechanism.
• Schema evolution recipe per platform. Estuary Flow: automatic via collection schema versioning; additive changes propagate through captures + materializations without pause; destructive changes controlled by per-materialization schemaEvolution.onSchemaChange: alter-table|rebuild-collection|pause. Streamkap: automatic for column_add + type_widening; manual acknowledgement for column_drop + type_narrowing + column_rename (may require re-snapshot). Kafka Connect: requires schema registry (Confluent, Apicurio) + BACKWARD compat mode + sink connector auto.evolve=true (JDBC sink) or Snowflake/BigQuery sink schema-evolution config.
• SMT chain order (Kafka Connect). Always place PII-masking SMTs (MaskField) first in the chain so downstream stages (filters, unwrap, logs, DLQ) never see raw sensitive values. Order matters: mask → filter → unwrap is the canonical sequence. Filters that reference envelope fields (e.g. value.after.customer_id) must run before ExtractNewRecordState (unwrap), which flattens the envelope. Test SMT chains on a staging topic before production; wrong order is silent.
• DLQ + triage contract. Every production sink connector has three DLQ settings: errors.tolerance=all, errors.deadletterqueue.topic.name=<sink>.dlq, errors.deadletterqueue.context.headers.enable=true. Ship a triage script (Python + confluent-kafka) that categorises DLQ records by exception class + prints example failures. Alert on DLQ backlog rate (records/hour), not absolute size — spikes are more important than baselines. Replay by resetting the DLQ consumer offset to earliest after the root cause is fixed; idempotent sinks make replay safe.
• Latency budget worksheet. Postgres commit → CDC decode ~50-100 ms; CDC decode → broker/collection write ~30-100 ms; broker → sink consumer fetch ~100-500 ms; sink batch → warehouse MERGE 500-5000 ms depending on batch size + warehouse size. Total p95: Estuary Flow ~1-2 sec, Streamkap ~2-5 sec, Kafka Connect ~2-10 sec (tuning-dependent). If your SLA is sub-second, only Estuary Flow ships it out of the box; if sub-5-sec, any of the three; if sub-minute, batch tools become viable competitors.
• Slot-lag monitoring query (source-side, all three tools). SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes FROM pg_replication_slots WHERE slot_type='logical'; scrape every 30 s into Prometheus. Alerts: lag_bytes > 1 GB for 10 min (warn), lag_bytes > 5 GB for 5 min (critical), active = false for 15 min (reader down). Postgres-side defense: max_slot_wal_keep_size = <N>GB (Postgres 13+) caps how much WAL a slot can hold before invalidation — prevents disk-full incidents at the cost of losing continuity on the affected slot.
• Migration playbook — Fivetran → real-time ELT. (a) Provision the new tool alongside Fivetran, land data into a new RAW_STREAMKAP / RAW_FLOW / RAW_CONNECT schema. (b) Build parity harness (row count + PK diff + column-hash comparison) between the old and new landings. (c) Batch cutover 5-20 tables at a time; parity-check after each batch. (d) Keep Fivetran running in parallel for 30 days after final cutover. (e) Update dbt models to point at the new raw schema; deploy with feature flag per model. (f) Decommission Fivetran after 30-day parallel-run success.
• TCO calculation template. Managed tool cost = (row events × per-event rate) + (per-table monthly fee × table count) + (connector-hours × hourly rate). DIY Kafka Connect cost = worker fleet infra ($/vCPU-hour × vCPU count × hours) + Kafka cluster amortised share + (engineer FTE × loaded cost). Compare over 3-year horizon; managed usually wins at <5 TB/day, DIY usually wins at >10 TB/day. Never omit engineer FTE from the DIY side — it is the load-bearing cost that separates real TCO from "look at the infra bill" TCO.
• Hybrid pattern — managed + DIY side by side. Real teams often run Streamkap or Estuary Flow for the boring 80% (Postgres → Snowflake) and a small Kafka Connect fleet for the 20% custom work (bespoke source connectors, air-gapped sources, or sinks with no managed option). Both stacks land in the same warehouse under different schemas; dbt joins them for analytics. This is the mature-team answer and a senior signal in interviews — nobody runs everything in one tool.
• Decision-tree cheat card. Q1: In-flight transforms? → yes = Estuary Flow. Q2: Warehouse-only downstream? → no = Estuary Flow or Kafka Connect. Q3: Under 5 TB/day CDC and under 100 tables? → yes = Streamkap. Q4: Dedicated Kafka platform team? → yes = Kafka Connect; no = Estuary Flow (fallback). Q5 (override): Bespoke source connector required? → yes = Kafka Connect regardless. Walk this tree out loud in every interview — a tool falls out in under 60 seconds.
• What senior interviewers score highest. Naming a tool with justification in sentence one; distinguishing batch ELT (Fivetran, Airbyte) from real-time ELT (Estuary Flow, Streamkap, Kafka Connect); naming exactly-once mechanism per tool (gazette 2PC, idempotent MERGE, transactional producers); naming the ~5-10 TB/day scale flip where DIY beats managed; naming hybrid patterns as valid; naming slot lag + DLQ backlog as first-class SLIs; naming the top-10 interview signal answers fluently in under 5 minutes. These are the senior signals that separate architects who have shipped a real-time ELT pipeline from candidates who have only read the docs.

Frequently asked questions

What is real-time ELT vs batch ELT?

Real-time ELT is the pattern where source-database mutations reach the warehouse (or lakehouse) at sub-second-to-single-digit-second cadence via change data capture (CDC) → optional in-flight transform → warehouse-native MERGE, rather than the batch-ELT pattern of periodic full-table or incremental snapshots synced every 15 minutes to several hours. Fivetran and Airbyte are the canonical batch-ELT tools — excellent for their category, wrong for real-time requirements because their default cadence sits between 15 minutes and 1 hour. Estuary Flow, Streamkap, and Kafka Connect (on Debezium) are the canonical real-time elt tools; they ship sub-5-second p95 latency by design, capture deletes natively via CDC log tailing, and land data with idempotent MERGE semantics so downstream dashboards and models see a continuously-consistent view. Every senior data-engineering interview in 2026 asks about the batch-vs-real-time distinction because it is the pick-one architectural decision that binds every downstream freshness SLA for years — a 15-minute pipeline never lands operational-analytics workloads, and a sub-second pipeline over-serves batch reporting dashboards. Getting the category right is the first architectural signal.

Estuary Flow vs Streamkap — when do I pick each?

Default to Streamkap when your requirements are warehouse-only (Snowflake / BigQuery / Databricks / Redshift), your scale is comfortably under 5 TB/day CDC and 100 tables, and you have no need for in-flight transforms beyond lightweight column masks or filters — Streamkap's UI-driven, autoscaled, managed Debezium-under-the-hood model has the smallest operational surface of any tool in the category and ships sub-5-second p95 latency out of the box. Default to estuary flow when you need in-flight transforms (streaming aggregations, joins, PII masks via SQLite derivations), when you need multi-sink fan-out (one collection materialising to Snowflake and Elasticsearch and Postgres and an event bus), when you need native exactly-once end-to-end via the gazette broker's two-phase commit, or when your scale reaches Streamkap's upper edge (multi-TB/day, hundreds of tables) but hasn't hit the Kafka Connect DIY-favorable scale flip. Both are managed streaming tools; the deciding question is almost always "do I need transforms or multi-sink" — yes means Estuary Flow, no means Streamkap wins on TCO. Feature-wise both handle schema evolution, delete propagation, and slot-lag monitoring; the deciding differentiators are derivations + sink breadth on the Estuary Flow side and least-surface + lowest per-value pricing on the Streamkap side.

Kafka Connect vs Debezium — are they the same thing?

No — they are different layers of the same stack and confusing them is a common junior-DE mistake that senior interviewers screen for. Kafka Connect is the open-source (Apache-licensed) integration framework that provides the runtime, the REST API, the worker fleet, the offset / config / status Kafka-backed storage, the SMT (Single Message Transform) pipeline, and the DLQ contract. Debezium is a family of source connectors — Postgres, MySQL, MongoDB, SQL Server, Oracle, DB2 — that implement CDC by reading source-DB write-ahead logs and emitting change events. Debezium connectors run inside the Kafka Connect runtime (or, in newer Debezium Server mode, standalone against Kafka or Pulsar or Kinesis). The kafka connect vs debezium distinction matters because it lets you mix and match: a Debezium Postgres source + a Snowflake sink connector + a Confluent Elasticsearch sink connector all coexist in one Kafka Connect fleet. Managed tools like Streamkap and (parts of) Estuary Flow wrap Debezium as an implementation detail — you get its CDC quality without owning the Connect runtime. Naming this layering correctly in a senior interview signals you understand the ecosystem rather than treating "Kafka Connect" and "Debezium" as synonymous.

Does managed CDC give me exactly-once?

The nuanced answer is "effectively exactly-once at the warehouse layer, via at-least-once transport plus idempotent MERGE by primary key." Estuary Flow ships true end-to-end exactly-once because the gazette streaming broker underneath uses two-phase commit for both producer writes and consumer offsets — the collection write, any derivation transform, and the materialization sink all participate in the same atomic commit boundary, so a mid-flight crash restarts from the last acknowledged offset with no duplicates. Streamkap is at-least-once on the internal transport layer (managed Debezium → internal Kafka → sink connector) and combines that with a sink-side idempotent MERGE INTO ... USING <staging> ON id = id WHEN MATCHED AND src.__streamkap_lsn > tgt.__streamkap_lsn THEN UPDATE ... pattern that makes replay produce the same warehouse state — effectively exactly-once for warehouse purposes because dashboards see a deterministic table regardless of transport-layer retries. Kafka Connect is at-least-once by default; wiring true EOS requires transactional producers (transactional.id), idempotent sinks (upsert-mode JDBC / Snowflake MERGE / Elasticsearch by _id), and connector-specific config (exactly.once.support=enabled on Debezium 2.5+). The senior interview signal is naming the mechanism per tool, not asserting "we get exactly-once" without qualification.

How do I handle schema evolution across all three?

Estuary Flow handles schema evolution via collection schema versioning: each collection has a versioned JSON schema; adding a nullable column on the source triggers an automatic schema-evolution flow that propagates through the capture, bumps the collection schema version, and issues an equivalent ALTER TABLE ADD COLUMN on the materialization target — all within ~1 second, zero pipeline downtime. Non-additive changes (column drop, type narrowing) are controlled by a per-materialization schemaEvolution.onSchemaChange policy: alter-table (attempt and fail loudly if unsafe), rebuild-collection (re-snapshot), or pause (freeze for manual intervention). Streamkap handles it via a schema_evolution policy block: column_add=auto, type_widening=auto for safe additive changes; column_drop=manual, type_narrowing=manual, column_rename=manual for destructive changes that require user acknowledgement in the UI or API before the pipeline resumes. Kafka Connect requires more setup: run a schema registry (Confluent, Apicurio), set the Avro converter with BACKWARD compatibility mode, and configure the sink connector's schema-evolution flags (auto.evolve=true on JDBC sink; Snowflake / BigQuery sink each have their own schema.evolve config). In all three cases the operational contract is the same — additive changes should be transparent, destructive changes should require an explicit human decision — but the mechanism differs and knowing which class of change your tool handles automatically is the difference between a mature ops story and a 3 AM incident.

Is DIY Kafka Connect cheaper than Estuary Flow / Streamkap at scale?

Yes at very large scale, no at small-to-mid scale — the crossover point matters more than the direction. At under 5 TB/day CDC throughput, managed tools (Estuary Flow, Streamkap) almost always win on true total cost of ownership once you count engineer time: managed pricing at this scale is ~$20-50k/year, while a DIY Kafka Connect deployment (3-worker fleet + Kafka cluster amortisation + 0.5-1.0 FTE engineer time) easily reaches $80-150k/year. At 5-10 TB/day, the answer depends on whether you already have a Kafka platform team; if yes, DIY starts winning on unit economics; if no, the cost of hiring one dwarfs any platform savings. At above 10 TB/day, DIY almost always wins: managed pricing scales linearly with volume ($200k-500k/year is not unusual at 20-40 TB/day), while DIY infrastructure cost stays roughly flat with modest fleet growth ($20-40k/year infra + ~1.5 FTE = $250-300k/year all-in even with generous headcount). The kafka connect vs debezium DIY answer requires two additional caveats: (a) bespoke source connectors force DIY regardless of scale, and (b) regulatory / air-gap constraints preclude SaaS control planes and force DIY. Never quote a TCO comparison without naming the scale bucket and including engineer FTE; both omissions systematically undercount DIY.

Practice on PipeCode

• Drill the SQL practice library → for the CDC MERGE, watermark, idempotent-sink, and warehouse-modelling problems senior interviewers use to test real-time ELT designs.
• Rehearse on the streaming practice library → for Debezium, Kafka Connect, gazette / broker-shaped concurrency, and CDC-replay problems that mirror the Estuary Flow / Streamkap / Kafka Connect interview.
• Sharpen the system-design axis with the design practice library → for real-time ELT platform selection, hybrid-architecture, and migration-planning scenarios that show up in senior data-platform interviews.
• Warm up on the aggregation practice library → for the rolling / tumbling / session-window shapes that dominate Estuary Flow derivations and streaming dashboarding workloads.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the three-tool decision matrix, the five-input decision tree, and the top-10 interview signals against real graded inputs.

Lock in estuary flow muscle memory

Docs explain tools. PipeCode drills explain the decision — when Estuary Flow's derivations earn their place, when Streamkap's minimal surface beats every alternative, when Kafka Connect DIY's unit economics flip the answer at scale, and when the hybrid pattern is the right architecture. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the real-time ELT trade-offs senior data engineers actually face.

Practice streaming problems →
Practice design problems →