Data Pipeline Architecture Design

The reasoning

Take the hops in order. The SDK retries, so the same event reaches Kafka more than once. Structured Streaming commits offsets after writing, so a restart replays the micro-batch. The hourly load into the fact reads with a deliberate overlap so nothing falls through the boundary. Every one of those is at-least-once, and none is a fault — it is simply what these components offer.

So duplicates arrive at the fact table routinely, and the design does not try to prevent them upstream because it cannot. It absorbs them at the write: MERGE INTO fct_pageview USING ... ON t.event_id = s.event_id. The run below delivers the same batch twice and the table is left identical, and equal to a full recompute over everything delivered.

The missing event_id guarantee is the part to raise unprompted, because it is the assumption the MERGE depends on. If the client cannot mint a stable id, derive one deterministically from the fields that identify the event — session id, event type, client timestamp — and hash them. A random UUID minted at send time changes on every retry and defeats the entire design, which is the specific mistake worth naming.

The reasoning

The defect is a pairing, not a component. An at-least-once hop is fine. An appending write is fine. Together they mean the pipeline duplicates data under conditions guaranteed to occur — a deploy, a node loss, an OOM, any of which restarts the stream from its last checkpoint.

A diagram cannot show it, because every arrow looks the same. The habit that catches it is to annotate each edge with its delivery guarantee and each stage with whether it is idempotent, then read the pairs: an at-least-once edge into a non-idempotent stage is a defect, every time. Here it is exactly one edge, and it is flagged above.

The run below is the same batch and the same redelivery as the previous question with only the write changed: three page views become six, and the table no longer matches a recompute. As for when anyone finds out — there is no error to alert on, so the answer is whenever someone compares the number against another source, which in most organisations is weeks.

The reasoning

Bronze is an append-only log of change events: op, binlog position, before and after images, one row per change. It is deliberately not idempotent — the connector redelivers after a restart and bronze keeps both copies, which is exactly what makes replay possible. It is also the only place a delete's before image survives, which matters for audit and for rebuilding history later.

Silver is current state: MERGE on the primary key, taking the row with the highest binlog position per key, and applying op='D' as a delete rather than filtering it out. That last part is the most-skipped detail in CDC design — dropping delete rows because they arrive with nulls in the after image leaves cancelled records alive downstream forever.

The single replication slot is the constraint that shapes everything else. One connector serves all twelve tables, so a stall on any one topic backs up the slot and the binlog grows on a production database — which is exactly why the DBA is cautious. Monitor replication lag and slot size as first-class metrics, size Kafka retention so a consumer outage does not force a re-snapshot, and key topics by primary key so per-key ordering survives parallel consumption.

The reasoning

Three problems, three defences, and they are not interchangeable. A repeated *file* is caught by the ledger — one row per filename and checksum, written before the load and consulted before processing. A repeated *row* inside a differently-named file is not caught by the ledger at all and needs the MERGE on record_id. Lateness is caught by neither and needs an expectation.

That last one is what people miss. If the vendor simply does not send, nothing fails: no error, no red task, no bad data — an hour with no file looks identical to a quiet hour. Detecting absence requires an explicit check, which is why that first edge is annotated at-most-once and the design carries a 90-minute freshness alert. Absence is the one failure mode here that your own code cannot see.

Two things to add unprompted. Keep the raw object forever and never edit it, so a parsing bug is fixable by reprocessing rather than by asking the vendor to resend. And validate the shape before the MERGE — column count, types, a row count inside a plausible band — because a vendor changing their export without telling you is the other thing this feed does.

The reasoning

Do the arithmetic first: ten million rows at a thousand per page is ten thousand requests, at five a second that is 2,000 seconds — a little over half an hour of uninterrupted calling. Anything running that long will be interrupted, so resumability is a requirement rather than a refinement.

That means checkpointing. Persist the pagination cursor after each successful page and write that page's rows straight to the raw layer, so a failure at minute twenty resumes at page 6,000 rather than page one. Cursor-based pagination is strongly preferable to offset-based here: with offsets, rows inserted during your half-hour shift every subsequent page and you silently skip records.

On the rate limit, read the response headers rather than sleeping a fixed interval. A 429 with Retry-After tells you exactly how long to wait, and honouring it is both faster and politer than a guess. Add jitter so retries do not synchronise, cap total attempts so a genuinely broken endpoint fails loudly instead of retrying all night, and treat 4xx other than 429 as fatal — retrying a 400 burns the budget and delays the alert.

The reasoning

Add them from the topology: about five seconds of replication lag, a second of connector flush, two minutes into bronze, up to fifteen minutes waiting for the silver MERGE, and up to sixty seconds of dashboard cache. Typical sits under ten minutes and the worst case brushes the requirement. Say a number out loud — an interviewer is checking you can produce one at all.

Then say why typical is the less useful figure. What breaches an SLA is the worst case, and these terms do not vary independently: replication lag grows under write load, the MERGE takes longer when there is more to merge, and both peak at the same moment. Correlated stretching is why the sum of typical cases understates the risk.

Attack the largest term. Dropping the silver MERGE from fifteen minutes to five removes ten minutes of budget; optimising a one-second connector flush cannot remove more than a second however hard you work. And measure end to end rather than per hop: carry the source commit timestamp through to the serving table and report the distribution, because every component can sit inside its own budget while the total does not.

The reasoning

Walk it hop by hop. MySQL's binlog is totally ordered, so Debezium reads the two changes in sequence. Kafka preserves order within a partition, and because the topic is keyed by primary key both changes for that customer land in the same partition — so per-key order survives that hop. Across partitions there is no ordering at all, and there does not need to be.

After that it stops. A Spark job reading the topic processes partitions in parallel and writes a micro-batch with no ordering guarantee between rows; a restart replays a batch alongside newer data. By the time rows reach the MERGE, arrival order means nothing, and any apply taking the last row per key by arrival will sometimes write the older value.

The fix is to stop depending on it. Every change record carries its binlog position, so the MERGE selects the row with the maximum position per key. The result is then a function of the set of records received rather than of the order they arrived in, and reordering becomes harmless. Keying the topic is still worth doing — it makes the common case cheap — but correctness should not rest on it, because the day someone repartitions the topic is not the day to discover the dependency.

The reasoning

The first requirement is that the input still exists. If bronze holds every record as received, the fix is to correct the transform and recompute — no source is involved, no vendor retention policy matters, and it does not matter that the operational database purged six months of history. That is what an append-only raw layer is for and why it is worth its storage.

The second is that the transform accepts a range. A job that processes yesterday because yesterday is hard-coded cannot be pointed at last March without an edit, and editing production code during an incident is how a second incident starts. The window belongs in the interface; the nightly schedule is then that same function applied to yesterday.

The run below demonstrates the third property, which is the subtle one. Raw is append-only and deliberately not idempotent — it collects overlapping deliveries and keeps every copy. The aggregate above it counts distinct events and rebuilds whole partitions, so replaying a window converges on the same answer and matches a full recompute. Raw is allowed to duplicate precisely because the layer above it is written not to care — and had the aggregate used COUNT(*) instead of COUNT(DISTINCT), the replay would have inflated it.

The reasoning

The naive design looks the dimension up per event, which means a network call per record at the point in the system with the tightest latency budget. It works in testing and falls over at production rates. The standard alternative is to broadcast the dimension into the job's state and refresh it on an interval — fast, no per-event lookup, and it raises a question nobody asks: which version does an event see?

With a periodic refresh, the answer is whichever version happened to be loaded when the event was processed. That is fine for a display attribute and wrong for anything audited, because reprocessing the same events tomorrow produces different scores. A temporal join — matching the event against the dimension version valid at the event's own timestamp — makes the result reproducible, and it requires the dimension to carry validity ranges, which is a Type 2 modelling decision made much earlier.

So name the trade rather than picking blindly. Broadcast with a refresh interval when the attribute is stable and the score is not audited; temporal join against a versioned dimension when the result must be reproducible or explainable to a regulator. And watch the restart in both cases: the job rebuilds its broadcast state from the current dimension, so a replay of yesterday's events scores them against today's tiers unless the join is temporal.

The reasoning

Building everything to the fraud requirement means running Flink with managed state to produce a daily finance table — always-on operation and reprocessing-as-a-project for something a scheduled job serves. Building everything to the finance requirement gives the fraud team hourly data, which is useless for blocking an authorisation. Both mistakes are real and both get made.

Capturing once and forking at the serving layer resolves it. Kafka is the single capture point; Flink reads it for sub-second scoring; a streaming job reads the same topic into an append-only Delta table, from which an hourly MERGE builds the fact. The property that matters most is not cost — it is that both paths derive from the same captured events, so they cannot disagree about which transactions occurred. Two separate captures of one source is the arrangement that eventually produces two different answers to one question.

Watch the boundary, because this is where Lambda's problem creeps back in. If the fraud path starts computing business aggregates that also exist in the warehouse — daily totals, per-merchant volumes — there are now two implementations of one definition and they will drift. Keep the streaming path to what genuinely needs the latency and let everything analytical come from one place.

The reasoning

Bronze's property is fidelity: every record as received, never edited, so a transformation bug is fixed by recomputing rather than by re-extracting. Gold's property is a consumption contract: a declared grain and conformed keys that do not move when a source changes shape. Those two are what a platform cannot do without, and a two-layer platform is a legitimate design.

Silver earns its place through reuse. With twelve sources feeding several marts, the cleaning, typing, deduplication and key conforming is shared work, and doing it once is obviously right — the logic lives in one place and each mart stays readable. With one source and one mart, silver is a rename of bronze: a table nobody reads, a build nobody needs, and an extra hop in every lineage diagram.

Be specific about the cost, because that is what makes this a judgement rather than a convention. Each layer is another materialisation, another scheduled build, more storage, more latency between source and consumer, and more for a newcomer to hold in their head. **What would change my answer**: a second consumer needing the same cleaning is usually the moment silver stops being premature.

The reasoning

Concede what no longer differs: both give ACID transactions, time travel, schema evolution and good SQL, and both scale past what most organisations need. Arguments built on those distinctions are out of date and interviewers notice.

What decides it here is the second consumer. Iceberg on object storage means Spark, Trino, Flink and a warehouse read the same files, so the ML feature pipeline and the dashboards run against one copy. Warehouse-native means the ML team either pulls through a connector — slow, and it burns warehouse compute on a workload that is not SQL — or you export a second copy to object storage, which is the thing everyone regrets: two copies, two freshnesses, eventually two answers.

The cost of the open format is operational and real: compaction, file sizing, snapshot expiry, orphan file cleanup, and a catalogue to run. A warehouse does all of that invisibly, which is worth a great deal to a small team. **What would change my answer**: if the ML team disappeared and every consumer were SQL, Snowflake-native is the right call and the maintenance you took on buys nothing.

The reasoning

The requirement is that three consumers resolve the same number, so the definition has to sit at a point all three pass through — downstream of the conformed facts, because it needs modelled data to be defined against, and upstream of every serving surface, because that is what makes it shared.

Each alternative forks along a predictable line. In the BI tool it is invisible to the reverse-ETL sync, so whoever builds that sync reimplements it — and a salesperson looking at Salesforce sees a different revenue figure from the dashboard, which is precisely the incident that destroys trust in a platform. Repeated per consumer it drifts as each is maintained separately. In the source system it is consistent and needs another team's release cycle to change.

The honest caveat is that this only pays for itself with more than one consumer. With a single BI tool, defining metrics in it is entirely reasonable and a semantic layer is ceremony. **What would change my answer**: one consumer and no near-term second — then the BI tool is the right place, and you revisit when the second arrives.

The reasoning

Three of the four are deliberate and each has a property attached. Kafka's short retention exists so several consumers read the same events independently and so a consumer can rewind. Bronze's full history exists so a transform bug is fixable by recomputing. Silver exists because consumers need a modelled shape that performs and does not move. Three copies with three reasons is a healthy design, not waste.

The fourth is the problem. BI tools cache their own extracts, and that copy has its own refresh schedule, its own failure mode and its own freshness — and it is the one on screen when somebody says the dashboard is wrong. A great deal of debugging time goes into pipelines that were entirely healthy while a tool-side extract sat two days stale.

So the discipline is that every copy gets a reason, a retention and an owner. Copies without those three accumulate quietly: a team exports to a spreadsheet, someone materialises a convenience table, a notebook writes results back to the lake. Each is reasonable alone, and collectively they produce a platform where nobody can say which number is authoritative.

The reasoning

The tension is real. Bronze is append-only because that is what makes reprocessing possible, and erasure requires removal. Doing it literally means finding and rewriting every affected Parquet file across seven years — which DELETE plus VACUUM will do, but the rewrite is enormous, it runs again for every request, and it invalidates time travel over the range it touches.

The design that avoids it makes sure the raw identifier never lands. Tokenise at ingestion — deterministically, so joins still work — and keep the only mapping in a separate vault. Erasure is then a delete of one vault row: tokens throughout bronze, silver and gold remain, nothing can resolve them to a person, and every downstream copy is covered at once including the ones you have forgotten about.

Two things a reviewer will raise. Deterministic tokenisation is weaker than random — identical values are visibly identical, so a low-cardinality field leaks through frequency analysis, which is acceptable for a customer id and poor for a postcode. And crypto-shredding is a legal argument as much as a technical one: your position is that the data is no longer personal because it can no longer be attributed. That has to be agreed with legal before you rely on it, not after the first request.

The reasoning

Ingestion and transformation have different shapes and should not share a DAG. Each source has its own schedule, failure mode and owner, so one DAG per source keeps failures attributable and lets a broken vendor feed fail without touching the other thirty-nine. One giant DAG makes every failure look like the same failure.

Transformation is one dependency graph and dbt already knows it, so the useful arrangement is for Airflow to trigger dbt and let dbt's graph handle the 240 models rather than reimplementing 240 tasks. What Airflow contributes is the trigger, the retries, and visibility across systems dbt cannot see.

The cross-source dependency is the real question. A model needing three sources should wait on those three having landed, not on 03:00 being late enough — a time offset is a guess about someone else's duration and fails silently the night one runs long. Express it as data availability: dataset-triggered scheduling, an asset-based orchestrator, or sensors with timeouts. Then put a deadline on it, so a source that never arrives becomes an SLA alert rather than a DAG waiting forever.

The reasoning

Two things have to exist. A contract saying which columns are published and supported — so there is an authoritative answer to whether amount is even in scope — and lineage saying which downstream models and dashboards actually read it. Without the first you are negotiating from nothing; without the second the producer has no way to find out who they would break.

The lineage has to be emitted by the runs, not maintained by hand. dbt produces a manifest describing model inputs, outputs and column references on every build; OpenLineage carries the same idea across engines. Anything curated manually is accurate the day it is written and rots from then on — which is worse than nothing, because people trust it.

Then the process makes it usable: the producer queries the catalogue before the change, sees the models and dashboards touching that column, and the rename becomes an announced deprecation — add amount_gross, keep amount populated for a window, remove it once consumers migrate. That is expand-migrate-contract, and it is only possible because the question who reads this had an answer that took a minute rather than a week.

The reasoning

The replication covers object storage and nothing else, which is the usual situation and the usual surprise. The data exists in eu-central-1, at most fifteen minutes stale — that is your effective recovery point and it is fine. What does not exist is the ability to do anything with it: no scheduler, no metadata database holding run history and connections, no secrets, no compute, and no catalogue pointing at the replicated files.

So recovery time is dominated by rebuilding the platform rather than by moving data — and it is unknown, because nobody has done it. An untested failover is a document, and the day you need it is the worst day to discover that the Airflow connections were configured by hand two years ago and exist nowhere in version control.

The questions that come before any of this are the recovery objectives. RPO 24 hours and RTO a week is a nightly copy and almost nothing else. RPO minutes and RTO an hour means warm compute, infrastructure as code deployed to both regions, replicated secrets, a catalogue that points at both, and regular failover drills — a different order of cost. Ask for those two numbers before designing, because they decide everything else.

The reasoning

A usable SLA has four parts: a named table, because a platform-wide promise means nothing; a complete-by deadline rather than a frequency, because hourly does not say when the hour ends; a percentile, because 100% is never true and promising it means the promise gets ignored; and a definition of complete, which everyone omits and which decides every later argument.

Complete is genuinely ambiguous with late data. All events up to midnight, or all events received by the deadline? The second is achievable and the first is not, and the gap between them is the late-arrival tail. Stating which you mean — typically that recent partitions are provisional and restated on a rolling window — is what stops the SLA becoming a dispute the first time a number moves.

Then the constraint. You cannot offer 06:30 when the vendor publishes some time before 06:00, because you would be committing to their behaviour. Measure their actual publication times for a few weeks, offer a deadline their 99th percentile supports, and separately raise the vendor's own SLA as a commercial conversation. Committing first and negotiating afterwards is how a data team acquires a reputation for missing deadlines it never controlled.

The reasoning

The vendor edge is the hard one, because its failure mode is silence. If no file is sent, no task fails, no error is logged and no bad data appears — the hour simply has no file, which looks exactly like a quiet hour. Only an explicit expectation catches it, which is why that edge is annotated at-most-once and the design carries a 90-minute freshness alert. Absence is the failure your own code cannot see.

At landing, measure what the producer promised: did the columns and types arrive as agreed, is the row count inside a rolling band, how old is the newest record. At transform, measure your own logic: is the grain still unique, do totals reconcile against the layer below, are null rates on important columns stable. At serving, measure the commitment — the complete-by time, and whether anyone reads the table at all.

Task status comes last because it measures the wrong thing: whether code raised an exception. A duplicated file loaded twice, a MERGE keyed above the grain, a dropped delete and a watermark skipping a row all complete successfully. Data-level checks are the only ones positioned to see any of them, and reconciliation against an independent recomputation is the only one positioned to see all of them.

The reasoning

One choice decides four things at once, which is why it gets so much airtime. **Ordering scope** — records with the same key land on the same partition and are read in sequence, so ordering is guaranteed *per key*, never globally. **Distribution** — the key is hashed, so its cardinality determines how evenly load spreads. **Hotspot risk** — one heavy key means one saturated partition. **Scalability** — you cannot have more useful parallelism than you have distinct keys.

Derive it from the ordering requirement and the rest follows. An order pipeline uses `order_id`, so created → packed → shipped → delivered arrive in order for each order. A user-activity stream uses `user_id`. A payment lifecycle uses `payment_id`. In each case the entity whose state machine must not be observed out of sequence is the key.

The bad keys fail for the same underlying reason. A **constant** (`"all_events"`) sends everything to one partition — a stream with the throughput of a single consumer. A **low-cardinality attribute** — country, status, event type — gives a handful of buckets, and real distributions are not uniform, so one of them takes most of the traffic. A **timestamp** distributes acceptably but orders nothing useful, because two events for the same order land wherever their timestamps hash to.

When one legitimate key runs hot — a merchant generating a hundred times the traffic of any other — you can bucket it as `order_id#0..9`. Be explicit that this is a **trade, not a fix**: you have bought throughput by giving up ordering across the buckets, and that is only acceptable if the business can tolerate it. If strict per-entity ordering is a requirement, the answer is a stable key and a conversation about capacity, not a silent salt.

The reasoning

**`event_id`** — a unique id assigned by the producer. This is what makes deduplication possible at all, and it has to come from the producer because a retry must reuse the same id. A consumer-generated id deduplicates nothing.

**`event_time`** — when the thing happened, distinct from when it arrived. Every windowing and lateness decision downstream depends on having both, and a pipeline that only records ingestion time can never be correct about late data.

**`event_type`** and **`schema_version`** — what this is and which version of the contract it follows. The version is what lets a consumer handle old and new shapes during a rollout instead of breaking at the moment the producer deploys.

**`source`** and **`trace_id`** — which service emitted it, and the request it belonged to. These earn their place the first time someone asks "where did this row come from", and a trace id is what lets you follow one business action across four services and a warehouse.

One rule matters more than the field list: **events are immutable facts, not row updates**. The instinct to publish "the order, changed" is the wrong shape; you publish "an order was updated, and here is its new state or the delta". That is what makes the stream replayable, because replaying a log of facts reproduces the current state, and replaying a log of mutations only works if you also know what the state was.

The envelope pays off again with multiple producers. If every service emits the same wrapper, generic consumers, lineage tooling and debugging all work uniformly instead of per-source.

The reasoning

The asymmetry is what decides it. A bad record rejected at the producer costs one caller an error. The same record published reaches every consumer, lands in raw storage, and stays in the retention window — so it now has to be handled in every downstream job, forever, including in replays.

What belongs at the producer is everything cheap and structural: mandatory fields present, timestamps parseable, the partition key derivable, payload within the size limit, `schema_version` set. None of these need context and all of them are decidable in the producing service.

What does not belong there is anything requiring state or lookups — referential integrity against a dimension, business thresholds, cross-event consistency. Those need data the producer does not have, and pushing them upstream couples the producing service to the warehouse.

The framing worth using: **the stream is a contract boundary, not a landfill.** You still keep a raw layer and you still expect bad data to get through, because producers have bugs — but the checks that are free at the edge should be paid there.

The reasoning

The sequence: locate the files containing matching rows, read each one in full, apply the change in memory, write a new file, and commit a metadata change that removes the old file and adds the new one. The commit is atomic, so a reader sees either the old file or the new one and never a half-written state. **Nothing is edited in place** — "update" is a rewrite plus a pointer swap.

Finding the file is the part people cannot answer, and it is not magic. Every file has an entry in the table metadata carrying its partition values and **min/max statistics for each column**. If File A holds `order_id` 1–1000 and File B holds 1001–2000, an update to `order_id = 1500` touches only File B. Add partitioning and the search narrows before statistics are even consulted: `date=2026-01-02/` is the only directory opened.

This is also why the same update can be cheap or catastrophic depending on the data layout. If the rows you are changing are clustered into a few files, you rewrite a few files. If they are scattered across every file in the table — which is what happens when you update by a column the table is neither partitioned nor sorted on — you rewrite the table.

The trade Copy-on-Write makes: writes are expensive, reads are perfect. There is no merge at query time, the files are clean Parquet, and scan performance is exactly what it would be on a static table. That is why it is the right default for silver and gold layers, where reads outnumber writes by orders of magnitude and a finance dashboard cannot afford unpredictable latency.

The reasoning

**Build the streaming path beside the batch one, writing somewhere else.** Same sources, same business logic, a separate target. Nothing depends on it yet, so it can be wrong without consequence — which is the only condition under which you will actually find out that it is.

**Compare, and make the comparison a job.** Reconcile the two targets on the same window: row counts, control totals, and a per-key diff on the metrics that matter. This is where the real defects surface, and they are always semantic rather than infrastructural — the batch job deduplicated on a key the stream does not, or handled a late correction the stream drops. Run it daily until the diff is stably empty, not once.

**Move consumers one at a time**, starting with the ones that tolerate being wrong. Each move is reversible while both paths are still running, which is the entire point of the parallel period.

**Keep the batch path after cutover, at least for a while.** It becomes the reconciliation baseline: a nightly full recompute that the streaming target is checked against. That is not indecision, it is the cheapest possible audit — and in a regulated context it is often what makes the streaming path acceptable at all.

The trade to state out loud: for a period you are paying for two pipelines and maintaining two implementations of the same logic, and every business-rule change has to be made twice. That cost is the price of a reversible migration, and it is much smaller than the cost of discovering the streaming numbers were wrong after the batch job was deleted.