Interview guides · Commonwealth Scientific and Industrial Research Organisation

Commonwealth Scientific and Industrial Research Organisation Software Engineer Interview Questions & Guide 2026

Debugging practice for Commonwealth Scientific and Industrial Research Organisation software engineer prep built on a reproduction you trust, narrowing to the smallest failing input, and changing one variable per run so the result stays attributable.

The 60-second brief

A Software Engineer at the Commonwealth Scientific and Industrial Research Organisation (CSIRO) is more than just a developer; you are a critical contributor to Australia’s national science agency. Your work directly enables high-stakes innovation, ranging from managing the complex data pipelines of the SKA-Low Telescope to developing systems for the National Energy Analysis Centre. This role is pivotal in bridging the gap between cutting-edge scientific research and robust, scalable engineering solutions.

If the loop includes an asynchronous take-home, treat it as a code review of you rather than as a puzzle: structure, what you chose to test, and what you wrote down about the constraint you were working under. Hold the stated time box and say what you would have done with more of it.

Focus areas

Name the isolation level each invariant requires
Consume webhooks duplicated, delayed and out of order
Store money as integer minor units

Overview

You will operate at the intersection of discovery and application. Whether you are building platforms for astronomical data collection or designing systems for energy research, your code will support projects that have a tangible impact on the nation’s scientific trajectory. Expect a professional environment that values technical rigor, long-term reliability, and a collaborative spirit, as you will frequently work alongside research scientists who rely on your technical expertise to achieve their objectives.

The Commonwealth Scientific and Industrial Research Organisation interview process

Commonwealth Scientific and Industrial Research Organisation candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Application Review

Before anything technical happens, someone has to decide which rung of the ladder your loop is calibrated to, and that decision sets the bar for every round after it. It comes from how you describe scope, not from your title, because titles do not convert cleanly between companies. The weak version of the answer is team size and years. The strong version names the largest change you shipped where nobody reviewed the design, what would have broken if you had been wrong, and what you were paged for. Get the level said out loud on this call, because the range and the loop both follow from it.

What to demonstrate

Whether the scope in your own account maps onto a level the team actually has an opening at, so a mismatch ends the process cheaply rather than after four interviewers have spent a day
Whether your title needs re-mapping: the same word describes very different amounts of independent decision-making at a twenty-person company and a ten-thousand-person one
Whether your compensation expectation can be filled at that level in the structure the role pays in, which is why the number gets asked for before any engineer is scheduled

How to prepare

Write down two changes from the last two years: the largest one you designed with nobody reviewing the design, and the largest one where someone more senior did. Lead with the first when scope comes up, and be ready to say which parts of the second were yours
Ask which level the loop is calibrated to and what changes at the level above it, then plan your weeks from that answer rather than from the posting
Settle a total-compensation range beforehand with the split named, base against bonus against equity and its vesting period, so a question about numbers gets a number instead of the word market

Technical Screening

Most of the time lost in this format is not lost to thinking. It goes to a standard-library call you half-remember, an off-by-one in a loop bound, and a debugging loop that mutates code at random until something passes. When output is wrong, stop re-reading the whole function: take the smallest input that reproduces it and walk the state through by hand, printing intermediates if the environment allows. Guessing at a fix without a failing case you understand is how a five-minute bug becomes twenty, and the clock does not pause while you do it.

What to demonstrate

Whether you reach the right structure without a detour, and can write it from memory rather than only recall that one exists
Whether overflow is considered where the language has fixed-width integers, since a signed 32-bit value stops at 2,147,483,647 and then wraps in Java, is undefined behaviour in C++, and does not arise in Python, whose integers grow instead
Whether recursion depth is treated as a constraint on large inputs, given that CPython's default limit is 1000 frames and a deep recursion can exhaust the stack in any language where an iterative version would not
Whether a failing case is isolated and explained before any edit is made to the code

How to prepare

From an empty file and with no references open, implement the pieces you lean on most: a heap push and pop, an iterative DFS with an explicit stack, and a binary search whose midpoint is written lo + (hi - lo) / 2, which avoids the overflow that (lo + hi) / 2 can hit in a fixed-width integer type
Time yourself on the ten library calls you look up most, such as sorting with a custom comparator, splitting and joining strings, and finding the next key at or above a value in an ordered map, until the lookup is gone
Take a solution you know is broken and, before touching it, write one sentence naming the input, the expected value and the actual value. Repeat until you do it without deciding to.

Architectural Discussion

Because the format is not fixed, the first job in the room is classification. Listen to the opening question and decide what it is: a probe into work you have already described, a fresh problem to solve now, or a conversation about how you operate. Each wants a different register, and the common failure is forcing a rehearsed structure onto a question that did not ask for it. Running a full design ritual on a ten-minute debugging question reads as not listening. When you cannot tell which it is, ask how long they want to spend and answer at that depth.

What to demonstrate

Whether the shape of your answer matches the question, so a yes-or-no gets answered before it is justified and an open prompt gets a direction before a detour
Whether you check how much depth is wanted instead of deciding for them, and whether you stop when the answer is complete rather than continuing until someone interrupts
Whether you can be redirected in the middle of an answer without restarting it from the beginning
Whether a question outside your experience gets an honest boundary followed by reasoning from what you do know, instead of a confident answer with nothing behind it

How to prepare

Rehearse one project at three lengths, roughly thirty seconds, three minutes, and a full walkthrough at the depth of a design review, and practise switching between them when someone interrupts mid-telling
Have someone ask you five questions of deliberately mixed type in one sitting without telling you the types, and score only whether you identified each one correctly before you started answering
Draft the sentence you will use to check depth, along the lines of asking whether the short version is useful here or they want the detail, and use it in a real conversation this week so the day of the round is not its first outing

Where candidates lose points

Deriving the business date from the UTC timestamp

Posting date, value date and the processor's settlement date are three different dates, determined by cutoff times, business-day calendars and holidays rather than by midnight UTC. A movement recorded at 23:50 on one side of a cutoff belongs to the next business date, so computing business_date as created_at::date makes daily totals disagree with every statement and every settlement file. The signature is a reconciliation break that resolves itself the following day and then reopens, which reads like a flaky job and is actually a data model that is missing a column: business_date has to be stored explicitly and set from the cutoff rule, with the timestamptz kept separately for ordering.

Assuming the default isolation level enforces the invariant you wrote down

PostgreSQL defaults to READ COMMITTED, where every statement takes a fresh snapshot, so a read-modify-write on a balance loses updates under concurrency. Its REPEATABLE READ is snapshot isolation, which blocks that particular anomaly by aborting the loser with SQLSTATE 40001 but still permits write skew across two different rows; only SERIALIZABLE closes that, and both levels therefore require a bounded retry loop on 40001 that many implementations simply never write. MySQL's InnoDB REPEATABLE READ behaves differently again — it does not abort on a conflicting write, so the identical application code silently changes behaviour when the engine changes. Two-sided transfers add a second failure mode on top: without a deterministic lock ordering, such as always locking account ids in ascending order, concurrent opposing transfers deadlock (SQLSTATE 40P01).

Starting work without saying what you are about to spend time on

State the plan before executing it: the approach, roughly how long it will take, and what you intend to leave hand-waved. That gives the interviewer a chance to redirect you in ten seconds rather than watching you spend fifteen minutes on the wrong sub-problem.

Treating a network call as though it were a local function call

A remote call can be slow, fail, or return after you stopped waiting, so name the timeout, the retry policy, and what the caller sees while the dependency is down. A call with no timeout turns one slow dependency into an exhausted thread or connection pool in every service upstream of it.

Questions & practice

Rank merchants by unmatched break value in one streamed pass

Medium · top-k · heap · streaming · space-bounded

A reconciliation run streams up to 50 million break rows of (merchant_id, currency, delta_minor signed, break_type). Return the 50 merchants with the largest total absolute delta_minor in a single currency, from one pass, with memory that does not grow with merchant count — there are up to 8 million distinct merchants and room for roughly 200,000 counters. Give both an exact and an approximate design, state the error bound the approximate one actually guarantees, and say which you would put in the nightly job.

Approach

Start by testing whether the constraint is real. Exact needs one hash map merchant_id -> int64 plus a size-50 min-heap: O(n) to aggregate, O(m log 50) to rank, O(m) space. At 8 million merchants and 16 bytes of payload that is a few hundred megabytes — quote the number before reaching for a sketch.
If it genuinely does not fit, exact still costs only two passes or an external group-by: partition by hash(merchant_id) % P to disk, aggregate each partition independently, then merge with a K-way heap. This is the answer whenever exactness is non-negotiable, which for money it usually is.
One-pass approximate: weighted Space-Saving with C counters. An item either hits an existing counter or evicts the minimum one and inherits its value as an over-estimate. With total weight W and C counters, every reported total over-estimates by at most W/C, and any merchant whose true total exceeds W/C is guaranteed to be in the table. Quote W/200,000 as a fraction of the day's break value.
State what the bound does not give you: the ordering within the top 50 is not guaranteed, and a merchant just below the threshold can be missed entirely. The honest claim to an operations reader is 'contains everyone above 0.0005% of today's break value', not 'the top 50'.
Decide the metric before either design, because sum(abs(delta)) and abs(sum(delta)) are different questions: a merchant with offsetting +1,000,000 and -1,000,000 breaks is top-ranked under the first and invisible under the second. Break work wants the first.
Recommend exact (two-pass or external group-by) for the nightly job — it runs once, has hours of budget, and an analyst acts on its output. Keep the sketch for a live dashboard, where a cheap bounded approximation beats an exact number that is four hours stale.

Worked solution (25 min)

Fix the metric in writing as sum(abs(delta_minor)) per (merchant_id, currency), because that is what an analyst works from.
Implement exact: hash-map aggregate, then a size-50 min-heap — push while the heap is under 50, then push-pop only when an item beats the root.
Implement weighted Space-Saving with 200,000 counters over the same stream.
Generate 50 million rows with a Zipfian merchant distribution near s = 1.1 so a few merchants dominate, then generate a uniform variant for contrast.
Compare: how many of the exact top 50 the sketch recovers, and the largest observed over-estimate, against the W/C bound.

Expected result: On the Zipfian stream the sketch recovers all 50 and every reported total over-estimates by at most W/200,000. On the uniform stream it recovers materially fewer — which is the result that tells you the bound, not the benchmark, is what you may promise a reader.

Verify it yourself

Exact top-50 via the heap equals exact top-50 via a full sort on a 1-million-row fixture.
Every sketch total is greater than or equal to the true total and never less; an under-estimate means the counter-inheritance step is wrong.
The largest observed over-estimate is at or below W/C on every run.
Heap memory stays at 50 entries regardless of input size.

Follow-up

Totals are now needed per currency as well as overall. What does that do to the key and to the counter budget?
Prove the Space-Saving over-estimate bound to me in two sentences.
The nightly job restarts halfway. Is the aggregate idempotent, and what does the heap do with a partially consumed stream?

Match a settlement file to ledger postings under duplicate keys

Medium · hash join · reconciliation · external memory

You have one business date of ledger postings (about 12 million rows: transaction_id, source_id, amount_minor, currency, business_date) and the processor's settlement file (about 12 million lines: settlement_line_id, external_reference, amount_minor, currency, business_date), where source_id carries the external reference. Match one-to-one on (external_reference, amount_minor, currency, business_date). Duplicate keys occur legitimately — the same amount can appear twice. Emit every unmatched item classified ledger_only, file_only or duplicate_match, in linear expected time. Then say what you do when neither side fits in memory.

Approach

Build the smaller side into key -> deque of row ids, never key -> row id. A duplicate key is data, not corruption; a single-row map drops one of a legitimate pair and the break report then shows a file_only that does not exist.
Probe the larger side once, carrying one extra bit per bucket: whether that bucket was ever hit. Pop from the bucket on a match and set the bit. An absent key is a probe-side-only row. A present-but-empty bucket means the probe side holds more copies than the build side — surplus, so duplicate_match. After the pass, a leftover non-empty bucket that was never hit is build-side-only; one that was hit is build-side surplus, so also duplicate_match.
That bit is what makes the classification a function of the per-key counts rather than of which side you happened to build. For a key with L ledger and F file copies: min(L, F) match, and the |L - F| surplus rows are duplicate_match tagged with the side that is over, degenerating to ledger_only or file_only exactly when min(L, F) is 0. Without the bit, surplus is only observable as a present-but-empty bucket, which can only ever happen on the probe side — so the same input reports different break classes depending on build order, and the smaller-side heuristic in bullet one silently decides which.
A genuine amount difference does not surface as amount_mismatch here, because the amount is inside the key — it surfaces as a ledger_only and a file_only sharing a reference. Promote those in a second, separate pass keyed on reference alone, recording signed delta_minor as ledger minus file. Keep that promotion out of the exact pass.
Cost: O(N+M) expected time and O(min(N,M)) memory; the hit bit packs into the bucket header and changes neither bound. The constant is the hash map, roughly 60 to 100 bytes per entry in most runtimes, so 12 million rows is order 1 GB — measure it rather than assert it.
When neither side fits, use a grace hash join: partition both sides with the same hash function into P spill files so a key lands in the same partition on both sides, then join partition by partition in memory. Cost is two extra sequential passes; skew inside one partition is the failure mode, handled by re-partitioning that partition under a second hash.
Sort-merge is the alternative at O(N log N + M log M) with external sort, and it wins when the file already arrives sorted by reference or the output must be ordered. It also gets the surplus classification for free, since a merge sees L and F side by side. Whichever you pick, do not widen the amount comparison to make breaks disappear: a tolerance wide enough to absorb rounding is wide enough to absorb a real loss.

Follow-up

The file nets three fee lines into one batch total. Which pass catches that, and what is its stopping rule?
The processor's business date sits one cutoff behind yours for forty minutes of traffic. What does that do to the exact join, and what does it do to break ages?
The same break recurs on the next run. Why must it link to the existing reconciliation_break row rather than open a second one?

Answer as-of balance queries over an append-only entry log

Hard · prefix sums · offline queries · append-only

Given 400 million ledger_entry rows (entry_id, account_id, direction, amount_minor, currency, business_date) and 2 million queries of (account_id, currency, as_of_date) asking for the balance at the end of that business date, produce every answer. The obvious solution — per query, sum that account's entries with business_date <= as_of_date — is correct. Say precisely why it will not finish, then give one that will, with time and space complexity. Corrections are posted as new entries carrying their own business_date.

Approach

Cost the naive version in numbers before rejecting it. Spread uniformly over 20 million accounts, each query touches about 20 rows behind a per-account index and 2 million queries is 4e7 row touches — perfectly fine. The problem is skew: one pooled clearing or merchant settlement account holding 3e7 entries, taking 10% of the queries, is 6e12 row touches. Name the skew; 'n is large' is not the reason.
The structural fact that buys a cheap answer: entries are append-only and never updated, so a prefix sum over an account's entries ordered by (business_date, entry_id) is stable — nothing behind position i can change. No mutable-balance design offers that, and it is why the storage is worth paying for.
Offline sweep, when all queries are known up front: externally sort entries by (account_id, currency, business_date, entry_id) and queries by (account_id, currency, as_of_date), then merge-walk both with a running sum, emitting each query's answer as the sweep passes its date. O((n + q) log(n + q)) dominated by the sort, O(1) beyond sort buffers, one sequential pass over each input instead of 2 million random seeks.
Online alternative: materialise end-of-day snapshots — one row per (account_id, currency, business_date) that had activity, holding the cumulative total. A query becomes one index seek for the latest snapshot at or before as_of_date, O(log n) per query, over far fewer rows than n. Use snapshots when queries arrive singly and the sweep when they arrive as a batch.
Corrections are the subtlety: an entry posted today but dated back changes historical answers, so every snapshot for that account from that date forward is stale. Either keep a Fenwick tree over dates per account (O(log D) update and prefix query) or recompute that account's snapshots from the corrected date onward. Then be precise about what reproducibility means — yesterday's statement is reproducible as of a stated snapshot time, not identical forever.
Bound the resources: int64 sums throughout, no float; 400 million rows at roughly 48 bytes of the columns you actually need is about 19 GB, so the sort is external and its fan-out is chosen from the sort buffer, not from the row count.

Follow-up

One account holds 30% of all entries. What does the external sort do with it, and what would you do for that one key instead?
Queries now arrive online at 500 per second. Which design survives, and what does keeping the other one warm cost?
A correction lands with a business_date 90 days back. Which snapshots are now wrong, and how does a reader find out?

Explain why the outbox relay stopped using its partial index

Medium · indexing · vacuum · queue tables

outbox_event holds event_id, aggregate_type, aggregate_id, aggregate_version, event_type, payload jsonb, published_at, attempts, last_error, created_at, with index ix_unpub ON outbox_event (created_at) WHERE published_at IS NULL. The relay runs SELECT ... WHERE published_at IS NULL ORDER BY created_at LIMIT 500 FOR UPDATE SKIP LOCKED, then marks each row by setting published_at. Unpublished rows hold steady near 400, but the query has gone from 3 ms to 900 ms. Explain what EXPLAIN (ANALYZE, BUFFERS) will show, why it happens, and the fix.

Approach

Read the plan for the gap between rows returned and work done: an index scan on ix_unpub returning 500 rows while touching tens of thousands of buffers is the signature. Rows Removed by Filter and the buffer counts name it; wall-clock alone does not, because a warm cache hides it.
Explain the mechanism: marking a row published is an UPDATE, which writes a new tuple version. The new version fails the index predicate and leaves ix_unpub, but the dead old version's index entry stays until vacuum removes it, so the scan walks dead entries and discards them. PostgreSQL can hint an entry LP_DEAD once a scan has proved it dead, which cheapens repeat visits, but the index pages themselves still have to be read and are not reclaimed.
Ask why vacuum is not reclaiming. Anything holding the xmin horizon back prevents removal: a long-running query, an idle-in-transaction session, an abandoned prepared transaction, or an inactive replication slot. Check the oldest xact_start in pg_stat_activity, pg_replication_slots, pg_prepared_xacts, and n_dead_tup with last_autovacuum in pg_stat_all_tables.
Fix in order of leverage: delete or archive published rows instead of leaving them in place, so a queue table stays a queue; keep the transaction horizon short and alert on it; then tune autovacuum on this one table with an aggressive scale factor rather than changing the global setting.
Rule out the other failure with the same symptom: a partial index is usable only when the planner can prove the query predicate implies the index predicate, so rewriting the filter as coalesce(published_at, 'epoch') = 'epoch' or wrapping the column in a function disqualifies the index entirely and produces a sequential scan instead of a bloated index scan.
Verify by re-running EXPLAIN (ANALYZE, BUFFERS) after the horizon is released and a VACUUM completes, comparing shared buffer reads rather than elapsed time, and confirm the relay keeps per-destination ordering after the change.

Worked solution (25 min)

Open a second session with BEGIN; SELECT 1; and leave it idle to hold the xmin horizon.
Churn 500,000 events through insert and publish, then run EXPLAIN (ANALYZE, BUFFERS) on the relay query inside a transaction you roll back, so FOR UPDATE does not hold locks.
Record the plan node, rows, Rows Removed by Filter and shared buffer counts.
Close the idle transaction, VACUUM outbox_event, and re-run the identical EXPLAIN, then add the archive step and re-measure.

Expected result: Before: Index Scan using ix_unpub, 500 rows returned, tens of thousands of shared buffers read and a large Rows Removed by Filter, in the hundreds of milliseconds. After releasing the horizon and vacuuming: the same plan node, buffer reads down by orders of magnitude, back to low single-digit milliseconds.

Verify it yourself

pg_stat_all_tables.n_dead_tup for outbox_event falls to near zero after the VACUUM.
The plan node is unchanged across both runs, which proves the regression was cost of work rather than plan choice.
Rewriting the predicate with coalesce produces a Seq Scan, confirming the separate predicate-matching failure.
pg_relation_size of ix_unpub shrinks only after the vacuum, not after the rows are published.

Follow-up

SKIP LOCKED means two relay workers never block each other. What else does it change about ordering guarantees for a single destination?
You archive published rows to a second table. What does that do to the relay's crash recovery and to duplicate delivery?
The relay batches 500 rows and publishes them, then marks them. Where exactly can it crash, and what does the consumer see?

Stop the capture-and-refund join from double-counting merchant money

Medium · join fan-out · conditional aggregation · grain

Report captured_minor, refunded_minor and net_minor per merchant per business_date. payment_intent holds intent_id, merchant_id, amount_minor, captured_minor, refunded_minor. ledger_entry holds transaction_id, account_id, direction, amount_minor, source_type in (capture, refund, fee, chargeback), source_id = the intent_id as text, and business_date; every capture posts one debit and one credit. A draft joins payment_intent to ledger_entry twice, once filtered to captures and once to refunds, and sums both. On intents with two partial captures and one refund the totals are wrong. Name both multiplications precisely and write the correct query.

Approach

Name the first multiplication by cardinality: intent to capture entries is one-to-N and intent to refund entries is one-to-M, so joining both yields N times M rows per intent. Every capture row is then counted M times and every refund row N times, and the two columns are inflated by different factors, which is why the totals look almost right rather than obviously broken.
Name the second, which is easier to miss: double-entry means each capture posts two legs, so summing every entry of a capture counts the amount twice regardless of joins. Select the single leg by ACCOUNT - the merchant's settlement payable, which carries exactly one leg of every capture and of every refund - and not by direction. Direction is not a substitute for that filter: a capture credits the payable and a refund debits it, so adding direction = 'credit' keeps the captures and silently drops every refund leg, leaving refunded_minor at exactly zero on a query that otherwise reads as correct.
Reject the reflexive fixes: SELECT DISTINCT and SUM(DISTINCT ...) de-duplicate values, not rows, so two genuine captures of equal amount collapse into one and the total is wrong in the other direction while looking tidier.
Prefer a single pass with conditional aggregation over ledger_entry, restricted to that one account: SUM(amount_minor) FILTER (WHERE source_type = 'capture') and the same for refunds, grouped by merchant and business_date, joining payment_intent only to reach merchant_id. One scan, no intent-level fan-out, and fees or chargebacks are added as another FILTER rather than another join. amount_minor is a positive magnitude, so net_minor is captured minus refunded; the signed form over the same rows, SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END), returns the same net in one column and is the check on the sign convention.
Keep aggregate-then-join in reserve for when per-intent detail is required: aggregate each side to intent grain in its own CTE, then join the two aggregates one-to-one. LATERAL works too and reads better when the right side needs the left's key.
Get the account predicate, the join column and the date right. Each merchant has its own settlement payable, so the predicate is a join to the account dimension (a.account_type = 'merchant_payable' AND a.merchant_id = pi.merchant_id); a single $settlement_account literal is only correct for a one-merchant report. source_id is text and intent_id is uuid, and PostgreSQL has no implicit cast between them, so one side must be cast and the side decides which index stays usable - pi.intent_id = e.source_id::uuid probes payment_intent's primary key per entry row, e.source_id = pi.intent_id::text probes an index on ledger_entry(source_id), and if neither fits the driving table the planner hashes both sides, which is fine for a day's report and not for a single-intent lookup. Group on ledger_entry.business_date rather than posted_at::date, because they differ across the cutoff, and decide explicitly whether a later-dated refund reduces its own day or the original capture's.

Follow-up

A refund lands on a later business_date than its capture. Which day does net_minor move, and what does that do to a daily merchant payout?
Add scheme fees, which arrive netted into a batch total rather than per transaction. How does the query change, and what can it no longer claim?
How would you reconcile this output against payment_intent.captured_minor, and which one is authoritative when they disagree?

How would you architect a platform to ingest and process massive volumes of sensor data?

Medium · system design and architecture

Approach

Name the read and write paths separately; they rarely have the same bottleneck.
State the consistency you need, and where you are willing to be stale.
Name the failure you are designing for, then the recovery path.

Follow-up

What would you drop to keep the system up under load?
What breaks first when traffic grows ten times?

How do you ensure data integrity in a distributed environment?

Medium · system design and architecture

Approach

Fix the scope first: who calls this, how often, and what they do when it fails.
Name the read and write paths separately; they rarely have the same bottleneck.
Choose a partition key and say what query it makes expensive.

Follow-up

How does this behave when that dependency is down for an hour?
What breaks first when traffic grows ten times?

How do you design for high availability in distributed systems?

Medium

Approach

Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.
Clarify what is being asked and what a complete answer contains.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

What is your strategy for maintaining codebases that support long-term research projects?

Medium

Approach

State your assumptions explicitly before working the problem.
Clarify what is being asked and what a complete answer contains.
Say what you would check first and why it is the highest-information step.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Raise the write ceiling on one hot clearing account

Hard · lock contention · isolation levels · sharding · write-heavy

The ledger posts balanced entry sets at 10,000 entries/s into append-only ledger_entry(entry_id, transaction_id, account_id, direction, amount_minor>0, currency, source_type, source_id, business_date, posted_at), roughly three entries per transaction. A materialised row per account carries the floor check available_minor >= floor_minor and is updated in the posting transaction. One pooled clearing account appears in 40 percent of transactions. Compute the throughput ceiling on that one row, then raise it. Name the isolation level each version requires and state precisely what sharding the balance costs the floor check.

Approach

Compute the ceiling before designing anything. Throughput against a single row is one divided by the lock hold time, where the hold runs from first acquiring the row to commit, including the WAL flush. At a 2 ms hold that is about 500 commits/s on that row, and adding cores does not move it. Demand is 10,000 entries/s at three entries per transaction, so roughly 3,300 transactions/s, of which 40 percent touch this account: about 1,300/s against a 500/s ceiling. You are 2.6x over before any code exists.
Shorten the hold before sharding, because it is free. Acquire the hot row last in the transaction and hold it for exactly one statement: UPDATE balance SET available_minor = available_minor - $1 WHERE account_id = $2 AND available_minor - $1 >= floor_minor. This is correct at READ COMMITTED, because an UPDATE that blocks on a concurrent writer re-evaluates its WHERE clause against the newly committed row version rather than its original snapshot.
Name what the broken version needs. SELECT the balance, compute in application code, then UPDATE loses updates at READ COMMITTED, since the second statement takes a fresh snapshot. Making that shape safe requires REPEATABLE READ or SERIALIZABLE plus a bounded retry loop on SQLSTATE 40001, which is the part most implementations omit; SELECT ... FOR UPDATE also works but is simply the same lock held for longer. Note that the identical application code changes behaviour on a different engine: InnoDB's REPEATABLE READ does not abort the loser.
Order locks deterministically on any two-account movement, always ascending by account_id. Concurrent opposing transfers without that ordering deadlock with SQLSTATE 40P01, and the deadlock rate rises with exactly the concurrency you added to gain throughput.
Shard the hot balance into N sub-rows keyed (account_id, shard_no), writers choosing a shard by hash of transaction_id. Per-shard hold time is unchanged, so capacity rises roughly N-fold, and at N=16 you clear 1,300/s with headroom. The cost is precise: the floor is now a predicate over the sum of N rows and is no longer expressible as a single-row CHECK, so it must be paid for deliberately. Either give each shard its own floor, which rejects postings while other shards hold headroom, or add a slow path that locks all shards in ascending shard_no under SERIALIZABLE, rebalances and retries on 40001. For a clearing account whose floor is effectively unreachable, dropping the per-shard floor and enforcing it as a monitored invariant is a defensible stated choice; for a customer deposit account it is not.
Keep the entries immutable throughout: no UPDATE or DELETE grant on ledger_entry plus a trigger, corrections as reversing transactions, and partitioning by business_date so index writes stay in the current partition and retention is a DETACH.

Worked solution (45 min)

Run two concurrent sessions doing SELECT then UPDATE to a computed value against the same account under READ COMMITTED and observe one update lost.
Replace it with the single atomic conditional UPDATE and re-run; then run the original read-modify-write under REPEATABLE READ with a bounded retry on 40001.
Measure committed transactions per second against the single pooled row at 1, 8, 32 and 128 concurrent writers, recording mean lock hold time.
Shard into 16 sub-rows hashed on transaction_id, re-measure, and write the query that answers the true available balance.
Run concurrent opposing two-account transfers with and without ascending account_id lock ordering.

Expected result: The read-modify-write loses an update at READ COMMITTED and aborts with 40001 at REPEATABLE READ; the single atomic statement is correct at READ COMMITTED with no retry loop. Single-row throughput flattens near one divided by the lock hold time, about 500 commits/s at a 2 ms hold, against the roughly 1,300 transactions/s this account needs. Sixteen shards clear that, and the floor check becomes a predicate over sixteen rows. Unordered transfers produce 40P01 deadlocks; ascending order produces none.

Verify it yourself

Past the plateau, adding writers raises latency without raising throughput; that is the ceiling, not a tuning problem.
After sharding, a floor breach is reproducible when each shard enforces the floor independently; construct it deliberately rather than assuming it cannot happen.
ledger_entry has no UPDATE or DELETE path throughout, and every correction in the test appears as a new reversing transaction.

Follow-up

While the balance is sharded, what query answers the account's true available balance, and what does it cost?
A transaction has legs in two currencies. What does the balanced-to-zero check mean now, and where does the rounding residual go?
What does the nightly reconciliation compare the sharded balance against?

One merchant stops receiving webhooks while the rest deliver

Easy · poison message · head-of-line blocking · outbox

The outbound relay delivers outbox_event rows to merchant endpoints preserving per-destination ordering. One merchant has received nothing since 09:14; every other destination is current. The relay process is healthy and CPU is flat. For that merchant, unpublished rows are accumulating, the oldest has attempts = 412 with backoff capped at 30 seconds, and last_error is the same string on every attempt. Diagnose in order, and state what you change so one undeliverable row can never stall a destination again.

Approach

Separate destination down from one row undeliverable before anything else, because they are identical from the merchant's side. The discriminator is the error class: a transport error (connection reset, 503, TLS failure) varies and implicates the endpoint, while an identical deterministic error on every one of 412 attempts implicates the row.
Read the blocked set in version order: SELECT event_id, aggregate_id, aggregate_version, attempts, last_error FROM outbox_event WHERE published_at IS NULL AND aggregate_id = $1 ORDER BY aggregate_version LIMIT 5. If everything is queued contiguously behind one row, the ordering guarantee is working exactly as designed and the defect is that it has no exit.
Confirm outside the relay: replay that single payload against the destination by hand. Reproducing the identical error proves the row, not the transport, and stops the escalation to the merchant before it is sent.
Decide what skipping means under a per-destination ordering contract. You cannot simply publish the next row; you either park the poison row in a dead-letter table and accept that the consumer sees an aggregate_version gap, or you deliver a degraded payload. Name which, and say what the consumer does with the gap.
Fix the real defect, which is unbounded retry, not the malformed payload. Bound retries by attempt count or row age, park on exhaustion, alert on the age of the oldest unpublished row per destination, and validate the payload at INSERT time so it cannot be written inside the state-change transaction at all.

Follow-up

The consumer orders on aggregate_version. What does it do with the gap your dead-letter created, and how does it learn the gap is intentional?
How do you distinguish this from a destination returning 4xx on every request during its own deploy?
Does the alert go on attempts, on the age of the oldest unpublished row, or both, and what are the thresholds?

Suggested practice checklist

For someone fluent in a dynamic language who has shipped real work but has never had to say what the runtime is doing underneath. The week is built on measuring and deliberately breaking things, because the questions that expose this background are the ones where the interviewer asks why a second time.

Day 1 — Measure before reasoning

Take a slow piece of your own code, write down in advance where you believe the time goes, then profile it and record how wrong the guess was. The cost is usually an allocation you did not notice or an accidental quadratic membership test.
Replace one list membership test inside a loop with a set and measure at a thousand, ten thousand and a hundred thousand elements, confirming the shape of the curve rather than only that it got faster.
Write down the three quantities you can now measure instead of assert: wall time, peak memory, and call count for the function you suspected.

Deliverable: A before-and-after profile of real code plus a written note on the size of the gap between the guess and the measurement.

Day 2 — References, copies, and the bugs they produce

Write the function with a mutable default argument, call it three times, and explain the accumulating result: the default is evaluated once when the function is defined, so every call shares one object.
Build a nested structure, take a shallow copy, mutate an inner element, and show that both views changed, because a shallow copy duplicates the container and not the elements. Then fix it with a deep copy and state the cost you just accepted.
Write two functions, one mutating its argument in place and one rebinding the local name, and predict the caller's view of each before running it. That single distinction produces most of the bugs that pass their tests.

Deliverable: Three small programs whose output you predicted correctly before running, each with a one-line statement of the rule underneath.

Day 3 — Types, once, in a language that checks them

Port one module you have already written, roughly a hundred lines, into a statically typed language, and record every place the compiler demanded an answer your original had left implicit: a value that can be absent, a numeric width, a case never handled.
Write the same signature in both languages and state what the static one guarantees before the program runs and what it does not, since it will not save you from a wrong algorithm or an index out of range.
Write the difference between an interface satisfied by declaration and one satisfied structurally, with one case each where the other approach would miss the mistake.

Deliverable: One module in two languages plus a list of the questions the type checker forced you to answer.

Day 4 — Concurrency, starting with what actually runs at the same time

Run the same CPU-bound function across four threads and four processes and measure both. Under the default CPython build the threaded version will not speed up, because only one thread executes bytecode at a time; the process version will. Check which build you are on first, since free-threaded builds remove that lock and change the result.
Then run a blocking I/O workload across four threads and measure it speeding up, because the interpreter releases that lock around blocking calls, which is why treating threads as useless is wrong as a general claim.
Build the lost update: two threads each incrementing a shared counter a hundred thousand times, and show a final value below the expected sum, because an increment is a load, an add and a store and the thread can be suspended between them. Fix it with a lock and then measure what the lock costs.

Deliverable: Three measurements, threads against processes on CPU work, threads on I/O work, and a demonstrated lost update, each with the mechanism written underneath.

Day 5 — Debugging as a procedure rather than an instinct

Work one real failure as a bisection: find a revision or an input size where it is good and one where it is bad, halve repeatedly, and state the two assumptions bisection needs, that the property changes exactly once across the range and that the test is reliable.
Minimise one failing input to the smallest version that still fails, and record how many rounds it took.
Keep a hypothesis log for one bug in three columns, what I believe, what would disprove it, what I observed, and stop yourself the first time you are about to change two things at once.

Deliverable: One bug worked to root cause with a written hypothesis log and a minimised reproducing input.

Day 6 — Tests that catch the bug you are about to write

Implement an LRU cache with a capacity bound, then write the three test cases that would catch an off-by-one in eviction: insert exactly capacity items and assert nothing was evicted, insert one more and assert the least recently used key is the one gone, and read an old key just before that insert so the eviction victim changes.
Add a property test comparing your implementation against a deliberately slow reference, an ordered list scanned linearly, over a few thousand random operation sequences, because a slow reference finds the cases you would not have thought to write.
Write one numeric test that fails under exact equality and passes with a tolerance, and state why the tolerance has to be relative rather than absolute once the magnitudes grow.

Deliverable: An LRU implementation with three boundary tests, one property test against a slow reference, and one tolerance-based numeric test.

Day 7 — Debug something broken, out loud

Have someone plant three defects in a two-hundred-line program, an off-by-one, a shared mutable state bug, and a wrong error-handling path, then find them while narrating, under a fixed rule: state the hypothesis before touching anything.
Time each one and record which tool found it, reading, a printed value, a debugger, or a test, because the question asked in interviews is how you would find it rather than what it was.
Write the sentence you will use when you do not yet know the cause, one that names the next measurement instead of offering a guess.

Deliverable: A recorded debugging session with time-to-find per defect and the method that found each.

Scope is read from what you decided

Team size, service count and tickets closed say very little. Seniority shows in the decision you owned: what you chose not to build, which constraint you traded away, whose objection you had to resolve before anything could move. A large project where you executed someone else's plan is a small story.

Describe your experience working in an interdisciplinary team.

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
Name the disagreement and how you resolved it with evidence.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

How did you know your change caused the improvement?
What would you do differently if you ran that again?

How do you handle shifting priorities in a long-term research or infrastructure project?

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
State the situation in two sentences and spend the rest on the reasoning.
Name the disagreement and how you resolved it with evidence.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?

Can you describe your experience with real-time data processing?

Medium · behavioural and engineering judgement

Approach

Close with what you would do differently, concretely.
Give the blast radius: what could have broken, and what you measured.
Name the disagreement and how you resolved it with evidence.

Follow-up

How did you know your change caused the improvement?
What would you do differently if you ran that again?