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.

The reasoning

**Start with the two consumers, because they want opposite things.** The fraud service asks one question per transaction: "the current features for card 123", in milliseconds. Training asks for months of history: "the features as they were at each past transaction". So there are two stores. The **online store** is a key-value store (Redis, DynamoDB, Bigtable or Cassandra) holding only the latest value per entity. The **offline store** is a table in the lake or warehouse (Parquet, Iceberg, Delta, or warehouse tables) holding every value with the time it became true.

**Write each feature once.** A feature such as "number of transactions on this card in the last hour" is defined once and computed by the stream processor (Flink or Spark Structured Streaming) from Kafka. The same job writes the new value to the online store and appends it, with its timestamp, to the offline store. Nightly features are computed in the warehouse and loaded into both stores the same way. Computing the same feature in two codebases is how training-serving skew starts: the model learns one definition and is served another.

**Make training point-in-time correct.** For every labelled transaction, take the latest feature value whose timestamp is at or before the transaction time, never after. This is an as-of join, and feature-store tools such as Feast do it for you: you pass a list of entities with an event timestamp, and each row gets the latest values known at that time. A plain join on card_id against the current table leaks the future into training, and the model looks far better offline than it ever performs online.

**Plan the operations.** A new feature needs a backfill over history, run in batch with the same definition. Each feature has a freshness expectation and a time-to-live, so a stale online value is noticed instead of served. Monitor three things: how old the newest online value is, whether online and offline values agree for a sample of keys, and whether the distribution of a feature drifts from what the model was trained on.

The reasoning

**Ask for the rule and the targets first.** "EU data stays in the EU" is a company or contract rule here; the GDPR itself allows transfers with safeguards, so find out exactly what is restricted (all data, or personal data only). Then ask for the **RPO** (how much recent data you may lose) and the **RTO** (how long you may be down) per dataset. A finance close and an internal dashboard rarely need the same answer, and the answer drives the cost.

**One data plane per region.** EU sources land in EU storage and are processed and modelled by EU compute; the same for the US. Each table has exactly one region that writes it. The global layer receives only data that may cross the border: aggregates, or rows where personal fields are removed or replaced by tokens that can only be resolved in the EU. This keeps residency simple to prove and avoids write conflicts, because no table is written in two places.

**Recovery inside the jurisdiction.** Replicate each region's storage to a second region in the same jurisdiction (for example one EU region to another): S3 Cross-Region Replication, a dual- or multi-region Cloud Storage bucket, or geo-redundant Azure storage. Warehouses have their own tools, each with conditions: Snowflake database replication works on every edition, but failover groups need Business Critical or higher; BigQuery cross-region replication copies datasets, but promoting the replica during an outage needs managed disaster recovery on Enterprise Plus; Redshift can copy snapshots to another region. Keep the infrastructure in code so compute in the standby region can be created when needed.

**Why not active-active.** Two regions both accepting writes to the same tables need conflict resolution, double the always-on compute, and make residency harder to prove. Analytics can usually accept an RTO of hours — here, 4 — which a standby that is promoted on failure meets. Active-active is worth its cost for a serving path with a strict uptime target, not for the warehouse as a whole. Say the cost out loud: replication doubles storage and adds transfer charges between regions.

The reasoning

**Clarify what "the dashboard" needs.** Which numbers (counts and sums are easy; distinct customers is not additive), how far back (the last 24 hours, or two years), and how often each screen refreshes. 200 open screens refreshing every 10 seconds is about 20 queries a second all day. That concurrency, not the event rate, is what breaks the naive design.

**The naive design and why it fails.** Load events into the warehouse every minute and point the BI tool at the raw table. Every refresh scans a table that grows all day, 20 times a second, so the bill rises with the data and with the number of viewers, and queries queue up at peak.

**Baseline: pre-aggregate in the stream.** A stream processor (Flink or Spark Structured Streaming) reads the order events, groups them by city and minute of event time, and upserts the totals into a small serving table keyed by (city, minute) — in Postgres, the warehouse, or a key-value store. The dashboard reads a few thousand rows instead of millions. For late events, keep each minute open for a short window (a watermark of, say, two minutes) and let a late order update its minute; a nightly batch job recomputes yesterday from the raw events so the dashboard agrees with the finance numbers. For distinct counts, use an approximate sketch such as HyperLogLog and say that it is approximate.

**When to add a real-time OLAP store.** If people must filter and group by many fields the pre-aggregate does not have, a store built for fresh, fast analytical queries — Apache Pinot, Apache Druid or ClickHouse — can read straight from Kafka and make new rows queryable within seconds. It is another system to run and pay for, so choose it for that need, not by default. Whatever serves it, show the age of the newest event on the dashboard and alert when it passes one minute.

The reasoning

**Incremental, not nightly full rebuilds.** Find what changed since the last run: an updated_at watermark, the source system's change feed or webhooks. For each changed document, extract the text, remove boilerplate, and split it into chunks (by heading, a few hundred tokens each, with a little overlap so a sentence is not cut from its context). Give each chunk a stable id, such as document id plus position, and store a hash of its text, so a rerun or an unchanged chunk does not create a new vector or a new embedding bill.

**Embed and upsert with metadata.** Send changed chunks to the embedding model in batches, then upsert each vector by chunk id into the vector store (pgvector, OpenSearch or a managed vector database) with its document id, version, source and access groups. The upsert makes the load idempotent: running the same day twice leaves the same index.

**Deletes and permissions are the correctness rules.** A deleted or unpublished document must remove all of its chunks, so delete by document id, and run a daily reconciliation that compares the document ids in the source with those in the index and removes the extras. Access must be enforced inside the retrieval query — filter on the reader's groups before ranking — not by dropping results afterwards, because by then the restricted text has already been retrieved for the model. Permission changes in the source must flow to the chunk metadata as fast as content changes do.

**Plan for reprocessing and quality.** A new embedding model or chunking rule changes every vector: build a new index in parallel, check it, then switch the chatbot to it (blue/green) instead of mixing old and new vectors. Track freshness (the age of the newest indexed change), the number of deleted documents still in the index (it should be zero), and retrieval quality on a fixed set of test questions.

The reasoning

**Ask before you draw.** How many rows a day, how fresh must the data be, who reads it, and how correct must it be? For a report read each morning, one batch run each night is enough. Write the answers down, because every later choice points back to them.

**Ingest and store.** Copy only new and changed rows from Postgres, with an updated_at watermark or with CDC. Land them raw and unchanged, one partition per day, so you can always rebuild. Never clean data on the way in: the raw copy is your safety net.

**Transform and serve.** Clean the rows, remove duplicates by order_id, and join them into tables people can trust. Publish the final tables to the warehouse that analysts query. Keep the business rules in one place, so two reports cannot disagree.

**Observe and make it safe to run twice.** Check freshness, row counts and nulls, and alert a person when a check fails. Write each day with a partition overwrite or a MERGE on the key, never a plain append. Then a retry, a rerun or a backfill gives the same result.

The reasoning

**Start from the freshness each team needs.** Finance reads once a day, so a nightly batch is cheaper and simpler. Fraud must act within seconds, so it needs a stream. A stream costs more: it runs all day and needs state and monitoring.

**Capture once.** Payments land in a log such as Kafka. The stream job reads each payment, scores it, and writes alerts. The same events are also saved raw to the lake, one partition per day.

**Batch for finance.** Each night a job reads yesterday’s raw partition, removes duplicates by payment_id, and overwrites that day in the sales table. Because it rewrites the whole day, a rerun is safe. Late events still land in the raw layer, so a backfill picks them up.

**Say the trade-off.** Running everything as one stream means one codebase, but it costs more and makes corrections harder. Two paths are common: a fast one for alerts and a batch one for the numbers finance signs off.

The reasoning

**Assume every step can run twice.** A task retries after a timeout, and a stream replays after a restart. Someone reruns last Tuesday by hand. So each run must give the same result.

**Pick an idempotent write.** Overwrite the partition: replace the whole day in one transaction. Or MERGE on a key: insert new payment_ids and update the ones already there. A plain append is not idempotent, because a retry adds every row again.

**Choose a key that never changes.** The key must be the same on every retry, like the payment_id from the source. A random id made at send time changes on each retry and defeats the MERGE. If the source has no id, hash the fields that identify the payment.

**Write first, then save progress.** Write the data, then save the watermark or the offset. If the job dies between the two, the next run repeats a few rows, and the idempotent write absorbs them. Saving progress first can skip rows for ever.

The reasoning

**A full copy every hour is too slow and too costly.** It reads 50 million rows to find a few thousand changes, and it loads the source database. So do one full load, then load only what changed.

**Option 1: a watermark.** Each hour, read the rows whose updated_at is later than the last run, and MERGE them on product_id. It is simple to build. But it misses deleted rows, and rows where nobody set updated_at.

**Option 2: CDC from the database log.** A tool such as Debezium reads the binlog and sends every insert, update and delete as an event. You apply them in log order with a MERGE, so deletes become deletes. It is more work to run, so choose it when deletes or freshness matter.

**Either way, plan the edges.** Start the change stream from the point where the full copy was taken, so nothing is lost or doubled. Watch how far behind the copy is. Agree with the source team what happens when they add a column.

The reasoning

**Retries handle short failures.** A network blip or a busy database usually passes. So each task retries a few times, waiting longer each time. A retry is only safe because the write is idempotent.

**Alerts handle the rest.** When retries run out, the orchestrator alerts a person with the task name and the error. A freshness check also alerts when the table is late, even if no task failed.

**Recover in a fixed order.** Find which task failed and why, from its logs. Fix the cause, then rerun from that task, for that day only. Check row counts and totals against the source before you say it is fixed.

**Limit the damage.** Publish to the final table only after the checks pass, so readers never see half a day. If you cannot fix it by 8, tell the report owners early and say which numbers they can trust.

The reasoning

**Sort the change first.** A new optional column is usually safe, because old jobs ignore it. A rename looks like a dropped column to every reader. A changed type or meaning can break numbers without any error.

**Catch it where data lands.** Compare the incoming schema with the expected one before you transform. Let safe changes through and record them. Stop the load on a breaking change and alert, instead of writing wrong data.

**Fix the cause with a contract.** Agree in writing which columns the source promises, and how changes are announced. A rename then goes in three steps: add the new column, move readers to it, and drop the old one later.

The reasoning

**Clarify the scale and the promises.** Ask how many events a second arrive at peak, how fresh the live view must be, and how long reports are kept. Live counts can be a few seconds old and approximate. Daily reports must be complete and agree with each other.

**Ingest through a collector and a log.** The tracking script sends small events to a stateless collector behind a load balancer. The collector adds a server timestamp and writes to Kafka, keyed by site_id. Kafka keeps events for days, so any job can replay them.

**Run two paths from the same events.** A stream job counts active visitors per site in short windows and writes them to a fast store for the live page. Every raw event also lands in the lake, partitioned by date. Each night a batch job builds sessions, then daily tables by page, country and source.

**Handle late and repeated events.** Use event time with a watermark, and let the daily job reprocess the last few days. Remove duplicates by an event id that the script makes once per event. Report how late data may change yesterday’s numbers.

**Name the cost levers.** Exact unique-visitor counts over many days are costly, so approximate counts such as HyperLogLog are common, and you should say so. One very large site can overload one partition, so split its key further.

The reasoning

**Decide who owns each write.** If both regions can sell the same room at once, two guests can get it. So each hotel has one home region, and only that region’s database accepts its bookings. A request that reaches the other region is sent on to the home region.

**Move changes with CDC.** Debezium reads each database’s log and writes every new booking, change and cancellation to a Kafka topic in that region. The topic is copied to the other region by a replication tool, such as MirrorMaker. There, a consumer applies the changes to a read copy and to the analytics tables.

**Apply changes safely.** Key each change by booking_id and apply it with a MERGE that keeps the newest version by log position. A replay after a failure then changes nothing. A cancellation becomes a status change, never a lost row.

**Plan for the link failing.** If the link goes down, each region keeps selling its own hotels. Changes wait in Kafka and catch up when the link returns. Alert when the copy is more than five minutes behind, and show analysts how fresh their data is.

**Mention the law and the bill.** Guests’ personal data may have to stay in its region, so copy only what the law allows, or mask it. Traffic between regions is billed, so send changes, not full copies.

The reasoning

**Each trip is a short story.** Events such as requested, accepted, started and ended arrive from phones, sometimes late or out of order. Key them by trip_id in Kafka, so one trip’s events stay in order on one partition.

**Keep state in the stream job.** Flink or Spark Structured Streaming holds the current status of each open trip. From that state it computes trips in progress and wait times per city, in windows of a few seconds, by event time. A watermark decides when a window is complete, and a trip’s state is cleared after it ends.

**Serve from a fast store.** The dashboard reads numbers already computed per city, from a fast store such as Redis, Druid or Pinot. A warehouse is too slow and costly for many viewers refreshing every few seconds.

**Keep finance on a batch path.** Every raw event also lands in the lake. A nightly job rebuilds each trip from its events and produces the finance tables, so late events and fixes are included. The live view can be a little wrong; finance cannot.

The reasoning

**Know the grain and the changes.** One order has many lines, and each line can be paid, shipped, returned and refunded on different days. So keep the events, not just the latest state. Agree with finance which date a refund belongs to.

**Capture with CDC.** Read changes from the orders, payments and returns tables with log-based CDC into Kafka, then land them raw by day. Apply them to current-state tables with a MERGE by key, in log order.

**Build a money ledger.** Each payment, refund or chargeback is one row with an amount and a date, and it is never updated. Daily revenue is the sum of the ledger for that day. A refund adds a negative row, so it is counted once, on its own day.

**Plan for scale and checks.** Partition large tables by date, and cluster them by order_id. Each day, reconcile the ledger totals with the payment system, and alert on any gap. A backfill rebuilds a day from the raw layer.

The reasoning

**Separate the fast number from the billed number.** Advertisers want spend within minutes, but the invoice must be exact. So the stream gives an estimate, and the daily batch gives the number you bill. Say this split early.

**Remove repeats by click id.** The ad server makes one id per click and sends the same id on every retry. The stream job keeps the ids it has seen in a time window, so a repeat is dropped. The spend table is written by that id, so a replay changes nothing.

**Filter bad clicks in two passes.** Fast rules in the stream drop clear bots, such as many clicks from one address in a few seconds. The batch job applies slower rules over the whole day, then corrects the spend and the bill. Advertisers see both numbers, and the bill is final.

**Keep it auditable.** Store every raw click for months, with every decision to drop one and the rule that dropped it. Compare daily counts from the stream and the batch, and alert on a big gap.

The reasoning

**Frame it first.** Ask who the users are, what hurts most today, and what the company needs from data in two years. With three warehouses and 400 unowned pipelines, the pain is probably trust and cost, not speed. Say that framing out loud, and check it with the interviewer.

**Draw the capabilities, not the vendors.** Ingest, storage, compute, orchestration, quality, catalog and lineage, access control, and serving. For each one, say what every team gets for free: the paved road. Tools come last, chosen against those needs.

**Pick a small first step.** For example: one warehouse for new work, a catalog with an owner for every table, and freshness checks on the 50 most-read tables. It can be undone, and it shows value in one quarter. Say what would make you change course.

**Plan the move and the owners.** Domain teams own their data; the platform team owns the road. Move readers table by table, run old and new side by side, and retire each old path on a date. Measure adoption, failed runs and cost per team.

The reasoning

**Diagnose before you decide.** Sample the forty definitions and group the differences: what counts as active, which time zone, test accounts, duplicates. Forty copies often come down to a few real disagreements. Bring that list, with the size of each gap, to the people who own the metric.

**Agree one definition, and one owner.** A business owner signs off the meaning; one data team owns the code. Publish it once, in a semantic layer or one governed table, with tests and a description anyone can read.

**Move the readers.** List every dashboard and job that uses a copy, from lineage. Give each team a date, help the biggest ones move first, and run old and new side by side so each difference is explained. Retire a copy when its readers are gone.

**Stop it coming back.** Put a data contract and freshness and volume checks on the shared metric. New dashboards must read the governed one. Report each quarter how many copies remain.

The reasoning

**Lead with the problem in their terms.** For example: fraud is found 24 hours late and costs $2 million a year, and the warehouse bill grows 40% a year. Engineers care about the design; the CFO cares about money and risk. Give both a number.

**Make it a series of bets, not one.** Quarter 1: stream the fraud data only, and show the losses fall. Quarter 2: move the largest tables to an open table format and cut storage cost. Each step pays for itself or teaches you something, and each one can stop.

**Name the costs and the risks.** People, licences, running two systems during the move, and the skills the team must learn. Say the biggest risk, such as a long dual run, and how you limit it. Hiding risk loses the room.

**End with how you will be judged.** Agree three numbers, such as fraud losses, cost per query and failed runs, and when you report them. Say what would make you stop or change course.

The reasoning

**Spend the first weeks learning.** Talk to the main users and owners, and read the incidents of the last six months. Measure the basics: failed runs, freshness, the top 20 costs, and the tables everyone reads.

**Rank, do not rewrite.** Put each problem on two scales: business impact and effort. Three customer tables that disagree is high impact. 600 DAGs may be fine if they run; old is not the same as broken.

**Win one early fix.** For example, cut the bill by fixing the five most expensive jobs, or give the customer table one owner. A visible early win earns the trust you need for bigger changes.

**Write the plan with the teams.** A short document: what we found, what we will do in two quarters, what we will not do, and how we will measure it. Review it with the people it affects, and change it when they are right.

The reasoning

**Sort the workloads.** Some jobs are SQL transforms on tables, some are heavy Spark, and a few are small Python tasks. Each kind has a natural home: the warehouse, a Spark platform, or containers.

**Pick a paved road for each kind.** One default per kind, with templates, monitoring and cost reports built in. A team on the default gets support for free; a team off it must say why.

**Decide with numbers.** Compare cost per run, the time to build a new job, and how many people can support each engine. Run one real workload on each candidate before you decide.

**Move teams by value.** Move a team when it saves them money or time, starting with the most painful. Keep a list of exceptions, and review it each quarter.