What the report looks like

Describes a real system with concrete scale, stack, and boundaries without prompting.

Diagnosed and fixed a real slow query, but explains the fix by outcome rather than mechanism.

Knows a unique constraint protects against duplicates; unsure how file-level deduplication actually works.

Reaches for set-based processing over row-by-row loops when pushed on 10× volume.

Not reached in this interview.

You open with system context most candidates omit — stack, data volume, and upstream dependencies — which lets an interviewer ask sharper follow-ups immediately.

00:00 · Q1

You located the bottleneck yourself rather than describing the system as uniformly slow, and named the exact step that degrades under load.

06:12 · Q2

Under a 10× volume premise you moved from row-by-row processing to a set-based approach — the right instinct, and you reached it without being led there.

29:18 · Q5

When the inconsistency in your timings was raised, you corrected yourself immediately and precisely instead of defending the earlier number. Interviewers read that as trustworthy.

37:52 · Q6

You justified the index by its result (four hours to one) but not its mechanism — why a B-tree on that column changes the plan, or what the planner was doing before. Expect a follow-up you currently can't answer.

13:40 · Q306:12 · Q2

On idempotency you hedged — "I think we also check the file hash… I'd have to look." For a payments system this is a core correctness property, and uncertainty here is disproportionately costly.

21:05 · Q4

Your scaling answer stayed at the level of intent ("batch it", "maybe a join") without naming the trade-off — memory footprint, transaction size, or lock duration on the ledger table.

29:18 · Q5

The nightly job was described as "just over one" hour at 13:40, then as roughly two and a half hours at 37:52. Your clarification was correct — the first figure covered only the matching step — but the original answer would have left an interviewer with the wrong number.

13:40 · Q337:52 · Q6

Week 1 — Read the EXPLAIN ANALYZE output for your own reconciliation query. Be able to say, out loud, what the planner did before and after the index, and why a B-tree suits an equality lookup on a high-cardinality column.

Week 1 — Open the ingest code and confirm exactly how file-level deduplication works. Write the two-sentence version: what makes a re-run safe, and what would happen if the same file arrived twice.

Week 2 — Rehearse the 5M-per-night redesign with numbers attached: rows per batch, memory per batch, and the lock/transaction implications of a set-based update on the ledger.

Week 2 — This JD lists queue-based processing, which never came up. Prepare one story about retries, ordering, or at-least-once delivery from your Celery work before the real interview.