Production Python for Data Engineers

Final Interview Preparation — Model Answers

About this document

Complete answers to all three questions in interview-questions.md. Don't read this until you've genuinely attempted every question yourself, out loud, the way you would in a real interview — a model answer read before your own attempt teaches you almost nothing; read after, it tells you exactly where your own reasoning was thin.

Each answer follows the same shape: the hardest constraint named explicitly, an architecture diagram, and the full explanation a strong candidate would give out loud.

Question 1: Multi-Bank Payment Reconciliation

The hardest constraint

This isn't a validation problem — it's a matching problem between two independent sources of truth, with a hard audit requirement on top. Get the matching logic and the audit trail right, and the rest of the design mostly follows.

Architecture

Rendering diagram...

The model answer

Start with requirements, out loud. Before anything else, I'd want to know: how many banks, how many transactions per bank per day, and what's the actual regulatory retention requirement — years, not just "a while"? For this answer, I'll assume moderate volume (tens of thousands of transactions per bank per day) and a multi-year retention requirement, since that's realistic for financial reconciliation.

Per-bank parsing, normalized to one common schema. Each bank's file gets its own parser — because the formats genuinely differ — but every parser's job is the same: produce records in one common, validated shape before anything downstream ever sees them. This is exactly the Module 2 discipline, applied at the boundary of each input source individually, not just once for the whole system.

The matching engine is where the real design decision lives. For every transaction, there are exactly three possible outcomes, and the system needs to determine which one applies, using a shared reference ID both sides agree on:

  • Matched — present on both sides, with agreeing amounts
  • Bank-only — the bank reports it, internal records don't have it
  • Internal-only — internal records have it, the bank's file doesn't

A mismatched amount on an otherwise-matched reference ID is its own fourth case, worth calling out separately rather than folding into "bank-only" or "internal-only" — it's a different kind of problem, usually a data-entry or timing issue, not a missing-record issue.

The audit requirement changes the data model, not just the logging. "We'll log it" isn't a real answer here. What's actually needed is an append-only reconciliation ledger — every decision the matching engine makes gets recorded as an immutable event: which transaction, which outcome, when, and as part of which run. This is different from a simple mutable "matched" flag on a row, because a regulator asking "why was this flagged as a mismatch three months ago" needs the original decision preserved, not today's possibly-corrected state.

Idempotency here means something slightly different than in the capstone. Re-running the same day's reconciliation shouldn't create duplicate ledger entries — but it also shouldn't silently overwrite a decision's history. The practical mechanism: key each reconciliation event on (bank_id, reference_id, run_id), where run_id is deterministic per (bank, date) rather than a fresh UUID every time. Re-running the same day's job against the same input naturally produces the same run_id, and an atomic insert with ON CONFLICT DO NOTHING on that key prevents duplicate events — while a genuinely new decision, from a later correction run, gets its own run_id and its own event, preserving history rather than overwriting it.

Failure handling: if one bank's file is malformed or missing entirely for a day, that should never block reconciliation for the other banks — the same "one bad record doesn't crash the whole run" discipline from the capstone, applied at the level of one whole bank's file instead of one record.

What I'd explicitly trade off: true real-time reconciliation isn't worth the complexity here — daily batch is the right call, since settlement files themselves only arrive once a day. Building real-time infrastructure for a fundamentally daily-batch input would be solving a problem this system doesn't actually have.

Question 2: Real-Time Clickstream Analytics

The hardest constraint

Correctness here isn't a fixed, one-time judgment the way it was for every record in the capstone. A rolling metric can be revised by data that arrives after you've already reported a number. The entire design has to be built around that reality, not around it.

Architecture

Rendering diagram...

The model answer

Start with requirements, out loud. I'd want to know real event volume (this scenario implies genuinely high throughput — millions of devices), how "under five minutes" is actually measured, and how late "significantly delayed" really means in practice — seconds, minutes, or occasionally hours. I'll assume most events arrive within a couple of minutes, with a long tail of stragglers up to perhaps 30 minutes late.

Durable, replayable ingestion is the foundation everything else depends on. Every event lands in a durable event stream first, before any aggregation happens — and every validated event also gets written to a long-term raw store. This single decision is what makes the reprocessing requirement possible at all: if raw events are kept, durably, separately from the aggregates computed from them, then changing the aggregation logic later just means running a new job against history — not re-ingesting anything from the original millions of client devices again, which likely isn't even possible after the fact.

Deduplication has to happen before aggregation, not as an afterthought. At real scale, "at least once" delivery is normal, not an edge case — the same event can genuinely arrive twice. Each event needs a stable event_id, and the aggregation layer needs to recognize and skip an already-processed event_id, the same idempotency discipline as the capstone's upsert, just applied to a streaming context instead of a batch upsert.

Watermarking is the actual answer to the hardest constraint. A watermark is a explicit, moving cutoff: "we consider a time window closed, and its aggregate final, once we're confident no more events for it are coming — say, five minutes after the window's end." Events that arrive within that watermark get folded into the still-open window normally. Events that arrive after the watermark — genuinely late stragglers — don't get silently dropped, and they don't get silently added to whatever window is currently open either, which would corrupt an unrelated window's numbers. They trigger an explicit, visible correction: the specific already-closed window's aggregate gets revised, and that revision is itself a distinguishable event, not a silent mutation.

This is genuinely different from the capstone's model, worth naming explicitly. The capstone treated every fetched record as a fixed fact the moment it arrived. Here, "how many people are watching right now" is a number that's allowed to be revised — the system has to be honest about that with whoever's reading the dashboard, rather than pretending every number it ever showed was final.

What I'd explicitly trade off: true exactly-once processing end-to-end is extremely expensive to build correctly at this scale. I'd choose at-least-once delivery with idempotent, event_id-based deduplication instead — it achieves the same practical correctness with far less infrastructure complexity.

Question 3: Multi-Tenant Usage Metering for Billing

The hardest constraint

The unit of correctness isn't "the system was right today" — it's "one specific tenant's bill, for one specific month, is exactly right," provably, and independently correctable without touching anyone else's already-finalized data.

Architecture

Rendering diagram...

The model answer

Start with requirements, out loud. I'd want real numbers — how many tenants, and how skewed is usage across them (is this a handful of huge tenants and a long tail of small ones, or roughly even)? I'll assume real skew, since that's realistic for B2B usage-based billing, and it directly affects the isolation design below.

Every raw usage event gets recorded exactly once, immutably. Each event carries a stable event_id; ingestion inserts it with a real uniqueness constraint on (tenant_id, event_id) — the same atomic upsert discipline as the capstone, just scoped per tenant. Once written, a raw usage event is never modified. This immutability is what makes everything downstream trustworthy.

Tenant isolation happens at ingestion, not as an afterthought. Partition ingestion by tenant_id, and bound concurrency per tenant, not just globally — the same semaphore discipline from Module 7, applied per partition. Without this, one tenant sending a huge burst of usage events could starve every other tenant's ingestion, which is exactly the kind of failure a shared, unbounded pipeline would hide until the worst possible moment.

The actual key design decision: aggregation always recomputes from source, scoped to one (tenant_id, billing_period). This is the answer to the hardest constraint, and it's worth stating precisely. The billing aggregate for a tenant's month is never built by incrementally adding to a running counter — it's always computed fresh, deterministically, from that tenant's full set of raw events for that specific period, then upserted as one row. Two direct consequences follow from this single decision:

  • "Never double-billed" becomes structural, not just a hoped-for outcome. Re-running the aggregation for a tenant's month, for any reason — a retry, a rerun, a scheduled reprocessing — produces the exact same number every time, because it's recomputed from the same immutable source events, not accumulated on top of a possibly-already- applied previous result.
  • The dispute/correction requirement is nearly free. "Reprocess one tenant's one month" is just: run the same aggregation function, scoped to that one (tenant_id, billing_period) partition. Nothing about that touches any other tenant's data, or any other month's already-finalized aggregate, because the computation was always scoped that narrowly to begin with.

Failure handling: if aggregation fails partway through for one tenant, it doesn't affect any other tenant's billing run — each (tenant_id, billing_period) aggregation is its own independent unit of work, the same "one bad thing doesn't take down the whole batch" discipline as everywhere else in this course, just applied at the tenant-period granularity here.

What I'd explicitly trade off: recomputing a full aggregate from source events every time is more expensive than incrementally updating a running counter would be. I'd accept that cost deliberately — for a system where "never wrong" is a hard business requirement, the computational cost of a full, deterministic recompute is worth paying for the correctness guarantee it buys, especially since usage-metering volume per tenant per month is rarely large enough to make this genuinely expensive in practice.