Aberdeen Software Engineer Interview Questions & Guide 2026

The 60-second brief

A Software Engineer at Aberdeen plays a critical role in bridging the gap between complex financial requirements and scalable technical solutions. You are not just writing code; you are building the digital infrastructure that supports global investment management. This role requires a balance of high-level architectural thinking and the ability to execute on precise, detail-oriented implementation tasks.

Find out what you will be typing into. A shared plain-text editor with no autocomplete, compiler or test runner changes what you have to hold in your head, and practising inside your own configured environment hides exactly that gap.

Focus areas

• Trace a symptom to a mechanism under load
• Bound every outbound call with a timeout
• Paginate large result sets with keyset cursors

Overview

The work environment at Aberdeen is fast-paced and collaborative, requiring engineers to work closely with cross-functional teams to solve real-world problems. Whether you are designing robust system architectures or optimizing existing features, your contributions directly impact how the firm delivers value to its clients. You will be expected to demonstrate both technical depth and a strong aptitude for team-based problem solving.

The Aberdeen interview process

Aberdeen candidates report 5 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

Recruiter Screen

The person on this call usually cannot evaluate your code and does not need to. They write a short paragraph, and that paragraph is what a hiring manager skims when deciding who to put on your loop. So the test is not whether your work was hard, it is whether a non-engineer can repeat it correctly. Name systems by what they did rather than by their internal codename, give each project a shape (what was breaking, what you changed, what happened after), and keep the whole walkthrough near ninety seconds. Depth that cannot survive a paraphrase reads as vagueness.

What to demonstrate

• Whether a non-engineer can restate your projects without distorting them, since their paraphrase is what travels to the hiring manager, not your sentences
• Whether each project has a shape rather than a stack list: the failure or constraint, the change you made, the result and how it was measured
• Whether you can say what was yours inside a team project without either inflating it or disappearing into the plural

How to prepare

• Rewrite each headline project as two sentences with no internal system names and no acronyms outside your company, then say them to someone outside engineering and have them repeat them back. Fix whatever came back wrong
• Attach one measured number to each project: the baseline, the change, and the window it was measured over. Where nothing was ever measured, say that plainly rather than reaching for a plausible percentage
• Time the background walkthrough against a clock. If it runs past two minutes, compress the earliest role to a single clause and spend the recovered time on the most recent one

Technical Assessments

Input bounds are the part of the prompt most often skimmed, and they usually contain the answer. They tell you which complexity class is admissible, which narrows the search before you have thought about the problem itself. As a rough planning figure, a compiled language does on the order of 10^8 simple operations per second and an interpreted one roughly an order of magnitude less. So n up to about twenty admits enumerating subsets, a few thousand admits a quadratic pass, and a million admits neither: you need near-linear, or linear with a log factor. If the bounds are missing, ask for them.

What to demonstrate

• Whether the approach is justified by the stated input size rather than by whichever pattern you recognised first
• Whether you ask about the properties that change the algorithm: whether the input arrives sorted, whether duplicates occur, whether values are bounded integers, whether it all fits in memory
• Whether you can name the bottleneck in your own solution and what would remove it, even when you deliberately leave it in place
• Whether a claimed speedup is real, since memoising a recursion only helps when subproblems genuinely overlap and the state can be keyed cheaply

How to prepare

• For each algorithm you rely on, write down the largest n it handles in roughly a second, then check two of those figures by timing them in the language you will actually type in
• For two weeks, write one line naming your target complexity and the bound that justifies it before you write any code, then compare that line with what you ended up submitting
• Practise the conversion backwards: given a required O(n log n), list the mechanisms that get you there (sorting, a heap, an ordered map, divide and conquer) and choose by what the problem needs to query, not by what you used last

Team-Based Interviews

You cannot drill a format you do not know, so put the preparation into material that travels. Three pieces of your own work, each rehearsed until you can take a follow-up you did not anticipate, will carry a conversation or a code walkthrough equally well. Specificity is what separates that from filler. A number needs its definition before it means anything: a p99 is over some window and measured at some hop, and a server-side figure excludes the queueing and network time a client would see. The number you cannot qualify is the one to leave out.

What to demonstrate

• Whether your examples carry detail only someone who did the work would hold, such as what the binding constraint actually was, which alternative you rejected and why it was worse, and what you measured on each side of the change
• Whether a number survives one follow-up, meaning you can say what it was measured over and whether it moved because of your change or merely alongside it
• Whether a failure is described with the specific change that followed it, rather than a lesson stated in general terms
• Whether your part in a team effort is stated accurately, including what other people did

How to prepare

• Write a page on each of three projects covering the constraint, the option you rejected, the measurement before and after, and what went wrong. Cut any line you cannot take a follow-up on, since you are writing the parts you will be pressed on rather than a summary.
• Recover the real figures while you still have access: request volume, data size, latency with its percentile and window, team size, timeline. Note where each came from, whether a dashboard, a design document or memory, and mark the estimates so you can say which they are out loud.
• Take your weakest project story to someone who works in a different area and have them ask why four times in succession. The point where you run out of answer is the part to go and re-read before the round.

Architectural Discussions

When a round has no standard shape, it is often there because something is still open: an area no earlier conversation reached, a round where the signal came out mixed, or a decision someone is not ready to make alone. Work out which by going back over what each earlier round actually covered rather than how it felt, and arrive able to give evidence on that point without being asked twice. Weak answers replay the loop's earlier material at the same depth. Strong ones go a level deeper and stay consistent with what you already said.

What to demonstrate

• Whether your account of a project matches the one you gave earlier in the loop, since what you said before may be available to whoever runs this round
• Whether you can go a level deeper on something already covered, reaching the decision and its alternatives rather than repeating the summary
• Whether you state your own uncertainty accurately, including parts of a system you did not build and decisions you inherited, instead of claiming even ownership across all of it
• Whether you can answer a question you handled poorly earlier by naming what you missed, rather than delivering a polished second version as if the first had not happened

How to prepare

• Reconstruct the loop on one page: for each round, the questions you were asked and the answer you actually gave, not the better one you thought of afterwards. The gaps on that page are your best available guess at why this round exists.
• Take the two claims you made earlier that carry the most weight and assemble the backing for each: the measurement, the date, what broke, the decision you would make differently now.
• Write down the three facts about your work that must not drift between tellings, such as team size, timeline and your own role, and check your stories against that list rather than trusting recall under pressure

Technical Deep Dives

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.

Where candidates lose points

Shipping a migration and the code that depends on it as a single change

During any rolling deploy, and for as long as a rollback remains possible, old and new code execute against the same schema at the same time. A migration that drops or renames a column breaks every instance that has not restarted yet, and code that requires a column the migration has not applied breaks every instance that restarted early. The discipline is expand then contract: add the new column nullable, write both shapes, backfill in batches, move reads across once the backfill is verified, and only then stop writing the old shape and drop it - four deploys, usually spread over days. It feels disproportionate until the first rollback, at which point it is the only reason the previous version still runs.

Assuming an isolation level prevents the anomaly you actually have

Isolation levels are named by the SQL standard but implemented differently, so any claim about one is only true of a named engine. PostgreSQL defaults to READ COMMITTED, where every statement takes a fresh snapshot, so two statements inside one transaction can legitimately disagree about the same row. Its REPEATABLE READ is snapshot isolation: it removes non-repeatable and phantom reads but permits write skew, where two transactions each read a set, each conclude their own write is safe, both commit, and the combined result violates a constraint that no single row expresses. Only SERIALIZABLE closes that, and it closes it by aborting a transaction with a serialization failure (SQLSTATE 40001), which means the guarantee is theoretical unless the application has a retry loop. InnoDB's REPEATABLE READ is a different mechanism again - plain SELECTs read a consistent snapshot while locking reads and writes see the latest committed row - so a read-modify-write inside one transaction can act on a value that the transaction's own earlier SELECT never returned.

Never running a concrete value through the code

Trace one small input and one edge input by hand, index by index, out loud. Re-reading your own code catches design mistakes; walking a real value through it catches the off-by-one, the uninitialised accumulator and the loop that never advances.

Optimising an axis nobody named

Ask which resource is actually scarce here: wall-clock latency, throughput, memory footprint, cost per request, or engineering time. Shaving a constant factor off an in-memory step is wasted effort when the same function makes a blocking remote call inside the loop.

Questions & practice

Identify the heaviest tenants in a five-minute window under memory pressure

Medium · top-k · heavy hitters · streaming

The edge service handles about 3,000 requests per second across roughly 50,000 tenants, peaking near 9,000. Expose the 50 heaviest tenants by request count over the trailing five minutes so limits can be tightened before one tenant's backfill starves the fleet. You may not retain five minutes of raw records. Give the exact solution and its memory, then the bounded-memory approximation with its error stated as a formula, and say which you would ship and at what tenant cardinality that choice changes.

Approach

1. Do the exact version first, because it is affordable at this cardinality: a ring of 300 one-second counters per tenant, advanced lazily, is 1,200 bytes of counters per tenant and roughly 60 to 90 MB for 50,000 tenants with overhead. Carry a running total and subtract the bucket you overwrite so a window read is O(1) rather than 300 adds.
2. Extract the top 50 with a size-k min-heap over the tenant sums: O(d log k) for d tenants, against O(d log d) to sort them all. Maintaining the heap continuously instead of on query requires a tenant-to-heap-index map, because incrementing a count already inside the heap means sifting from a known position, and without that map you rebuild the heap on every request.
3. State the approximation precisely rather than gesturing at sketches. Misra-Gries with m counters retains every item whose true count exceeds N/(m+1), and each retained count underestimates the truth by at most N/(m+1). With m = 1,000 and N = 900,000 requests in the window the error is roughly 900 requests, which is fine for spotting a tenant sending 50,000 and useless for ranking two tenants 200 apart.
4. Say what breaks when the window slides: Misra-Gries and Space-Saving are insert-only and cannot be decremented as records age out. The workable construction is one summary per sub-window, say ten seconds, with 30 summaries merged at query time, and the merged error is the sum of the per-summary errors, so the bound degrades linearly in the number of sub-windows.
5. Choose and defend it: at 50,000 tenants the exact rings cost under 100 MB in a process that already holds more, so ship exact. Keep the sketch for the case that actually motivates it, a per-principal or per-IP key where cardinality runs to millions and is not bounded by anything you control.
6. Raise the fleet problem before it is asked: each of 20 to 40 instances sees only its share, and the top 50 of one shard is not the top 50 of the fleet. Either aggregate counts centrally or accept that a per-instance threshold multiplied by instance count is the limit you are really enforcing.

Follow-up

• The heaviest tenant is heavy because of one export job rather than user traffic. Should the limiter treat those as the same tenant?
• Two tenants sit tied at the boundary of the top 50. Does your answer flap, and does the flapping matter?
• You switch to per-principal keys and cardinality goes to 10 million. Walk through what changes.

Archive a resource graph without breaking live references or recursing

Medium · graph traversal · topological order · tenant isolation

Resources reference other resources within a tenant; for the largest tenant the reference table holds up to 2,000,000 nodes and 8,000,000 edges. Archiving a resource must archive everything reachable from it that nothing outside the set still references, refuse when a live external referrer exists, and terminate when references form cycles, which they legitimately do. Produce the archive order and the refusal list, targeting O(V+E). Say what stops the traversal crossing a tenant boundary, and why recursion is the wrong control structure at this size.

Approach

1. Load the subgraph with the tenant predicate on both endpoints of the edge, not only on the side you started from. Scoping the left table alone is the classic cross-tenant leak: one mis-entered edge then pulls another tenant's resources into the traversal and, worse, into the archive.
2. Traverse iteratively with an explicit stack. A 2,000,000-node graph can hold a chain deep enough to exhaust a native stack in the low tens of thousands of frames, and that failure is a process crash rather than an error you can return.
3. Treat cycles as data rather than corruption: compute strongly connected components with Tarjan in O(V+E) using its own explicit stack, then condense. The condensation is a DAG, so a topological order over it gives the archive order, and every member of a component archives in one transaction because no order within a cycle is valid.
4. Decide refusals with reverse edges. A candidate is archivable only if every in-edge originates inside the candidate set, so build the transpose or count in-degrees restricted to the visited set, and emit each blocked resource with the id of the external referrer, which is the only part of the answer an operator can act on.
5. Store the graph as CSR rather than a map of lists: an offsets array of V+1 8-byte entries plus E 8-byte targets is about 80 MB at this size, where boxed adjacency lists cost several times that and lose cache locality on every hop.
6. Run Kahn over the condensation for the order in O(V+E). If the emitted count is short of the component count the condensation step itself is wrong, since a condensation cannot contain a cycle, which makes the check free.

Worked solution (30 min)

1. Write the edge-loading query with the tenant predicate on both endpoints and state what it does with a cross-tenant edge.
2. Implement iterative Tarjan with an explicit stack and confirm on a three-node cycle that it emits one component of size three.
3. Build the transpose restricted to the visited set and mark every node with an in-edge from outside it as refused, carrying the referrer id.
4. Run Kahn over the condensation and verify the emitted order against the referrer-before-referenced rule.
5. Size the CSR arrays for 2,000,000 nodes and 8,000,000 edges and compare against a boxed adjacency map.

Expected result: An iterative O(V+E) traversal over a tenant-scoped CSR subgraph, SCC condensation so cycles archive atomically as one component, a transpose-based refusal list naming the external referrer for each blocked resource, and a Kahn topological order over the condensation, with recursion replaced by an explicit stack because of graph depth rather than style.

Verify it yourself

• A two-node cycle with no external referrers archives as one unit, and adding a single external referrer moves both nodes to the refusal list.
• No resource appears in the archive order before something that references it, except within its own component.
• A counter on edge visits matches the loaded edge count exactly, confirming each edge is traversed once.

Follow-up

• The graph is read in one query and the archive writes a minute later. What can change in between, and how do you make the write safe?
• The candidate set is 400,000 resources. Is that one transaction, and if not, what does a half-finished archive look like to a reader?
• An edge points at a resource in another tenant. Is that a refusal, an error, or an alert?

Diff a projection against the primary without per-row point reads

Hard · reconciliation · range hashing · throttling

The listing projection has drifted and some rows show a stale version. The primary holds 40,000,000 resource rows across 12,000 tenants while serving 1,200 writes and 14,000 reads per second. The obvious repair, reading each resource row and comparing its version against the projection, is correct and would eventually finish. Explain precisely why it is unacceptable here, then give a diff that finds the differing rows, state its complexity, and make it safe to run against a live primary. Replication lag is usually under 100 ms and is not bounded.

Approach

1. Quantify the naive cost rather than calling it slow: 40,000,000 point reads at even 0.5 ms each is over five hours serialised, and the only lever is concurrency, which is exactly what you cannot spend. The primary's pool is sized for the write path, and 40,000,000 random reads evict the buffer cache that sustains the 85 percent cache hit rate, so the audit degrades the system it is auditing.
2. Replace random access with one ordered pass per side. Both sides can be read in (tenant_id, resource_id) order, which is a sequential scan on each and a merge join in O(n) time and O(1) memory. For a dense diff that is the whole answer, and it reads the primary once instead of 40,000,000 times.
3. For the expected sparse case, compare range hashes instead of rows: partition the key space, compute per range an order-independent aggregate over hash(resource_id, version), compare aggregates, and descend only into ranges that differ. With d differing rows and branching factor B, at most d ranges mismatch per level, so the drill-down examines O(d log_B(n/d)) ranges and reads full rows only in mismatching leaves.
4. Aggregate with a sum modulo 2^64 or a multiset hash, never XOR. XOR is order-independent but self-cancelling, so two rows wrong in the same way, or a row duplicated on one side, leave the range aggregate matching and the range is declared clean.
5. Pin the comparison to a point in time or it reports lag as drift: consider only rows whose updated_at is older than now minus a lag margin, and re-check each candidate mismatch individually before repairing. At 1,200 writes per second a diff without this reports thousands of false positives, and an unattended repairer would then overwrite live rows with stale values.
6. Make the run resumable and throttled: batch by range key, persist the last completed range, and watch a signal such as replica lag or primary CPU, pausing rather than pressing on. A reconciliation that cannot be stopped and resumed gets killed halfway and restarted from zero, which is how a repair becomes an incident.

Follow-up

• The diff reports 900 stale rows. How do you decide between patching those rows and rebuilding the projection from resource_revision?
• Same job, but the projection lives in a search index that cannot be scanned in key order. What changes?
• How would you run this continuously at low cost instead of only as incident response?

Explain why the owner filter ignores the listing index

Medium · composite index · query plans · sort elimination

The only index on resource is (tenant_id, status, updated_at DESC, resource_id DESC). A new endpoint returns one user's resources across all statuses, newest created first: WHERE tenant_id = $1 AND owner_user_id = $2 ORDER BY created_at DESC LIMIT 20. On a tenant with 2M rows it takes 900 ms and EXPLAIN shows a sort above a large scan. Explain precisely why the existing index cannot serve it, give the index that can, and state which of these the new index still will not help: owner_user_id alone across tenants; the same query ordered by updated_at. PostgreSQL 16.

Approach

1. Separate the two jobs an index does. For filtering, a composite btree is seekable only on a left prefix, so with no predicate on status the scan can at best range over tenant_id and test owner_user_id per row; PostgreSQL 16 has no btree skip scan to jump the unconstrained column.
2. For ordering, the index is sorted by (status, updated_at) within a tenant and not by created_at, so the LIMIT cannot stop early: every matching row is read and then sorted. That is the 'Sort Method: top-N heapsort' line, and it is why the plan reads 2M rows to answer with 20.
3. Derive the replacement from the access path — equality, equality, then the ordering column: CREATE INDEX CONCURRENTLY ON resource (tenant_id, owner_user_id, created_at DESC). The scan seeks to the (tenant, owner) range and walks 20 entries in order, so the Sort node disappears along with the row-read.
4. Treat INCLUDE (title, status) as conditional, not free. An index-only scan still visits the heap for any row whose page is not marked all-visible, so on a table taking 1.2k writes/second the win depends on autovacuum keeping the visibility map current, and the wider index costs more on every insert.
5. Answer the two negatives explicitly. owner_user_id alone is not a left prefix of the new index, so it degrades to a full scan of the index at best. Ordered by updated_at, the query still seeks on the (tenant, owner) pair but must sort, because only created_at is ordered within that pair.
6. Measure both sides with EXPLAIN (ANALYZE, BUFFERS) and compare estimated against actual rows at the lowest node — a 2M-versus-200 misestimate there is usually what chose the plan, and adding an index will not fix a statistics problem.

Worked solution (25 min)

1. Load 2M resource rows across 5k owners in one tenant, run the query under EXPLAIN (ANALYZE, BUFFERS), and record the node reading the most rows plus the Sort Method line.
2. Create (tenant_id, owner_user_id, created_at DESC) concurrently and re-run, confirming the Sort node is gone and actual rows fall to about 20.
3. Run the two negative cases and capture the plan for each.
4. Re-run the original tenant listing query to confirm the new index has not displaced the index that query depends on.

Expected result: With (tenant_id, owner_user_id, created_at DESC) the plan is an Index Scan with no Sort, roughly 20 rows and a handful of buffers, sub-millisecond. owner_user_id alone still scans; ORDER BY updated_at seeks on the pair and then sorts that owner's rows.

Verify it yourself

• Actual rows at the leaf node is on the order of the LIMIT, not the tenant's row count.
• No Sort node appears above the index scan.
• Buffers read stays constant as one owner's row count grows; if it grows with it, the ORDER BY is not being satisfied by the index.
• pg_stat_user_indexes shows idx_scan climbing on the new index and not on the listing index for this endpoint.

Follow-up

• 90% of rows are status='active'. Would a partial index WHERE status = 'active' change your answer, and for which of the three queries?
• A dashboard runs this for 40 owners in one page load. What changes about the design?
• How do you roll this index out on a table taking 1.2k writes/second, and what does it cost on every insert from then on?

Write the update path that detects a concurrent edit

Medium · optimistic concurrency · transactional outbox · audit history

resource carries version INT NOT NULL DEFAULT 1. resource_revision holds revision_id, resource_id, version, actor_user_id, change_kind, patch JSONB, request_id, created_at with UNIQUE (resource_id, version). outbox_event holds aggregate_type, aggregate_id, aggregate_version, event_type, payload, status. A PUT carries the version the client read. Write the exact statements for the single transaction that applies the edit, records the revision and enqueues 'resource.updated', and give the handler's branch on zero affected rows. Then say what PostgreSQL 16 does under READ COMMITTED when two of these updates hit one row at once.

Approach

1. One transaction, three writes, no network call inside it: UPDATE resource SET title = $3, version = version + 1, updated_at = now() WHERE resource_id = $1 AND tenant_id = $4 AND version = $2; then INSERT the resource_revision row at version $2 + 1; then INSERT the outbox_event row at the same aggregate_version. The event goes to a table rather than a broker because no transaction spans both.
2. Branch on the affected-row count before doing anything else. Zero has three causes — stale version, wrong tenant, row gone — so re-read once and map to 409 carrying the current version, or 404 for an id outside the caller's tenant, which also stops the endpoint confirming that another tenant's id exists.
3. State the engine behaviour instead of assuming it. Under READ COMMITTED the second UPDATE blocks on the row lock, and when the first commits PostgreSQL re-evaluates the WHERE clause against the newly committed row, so the version predicate now fails and the statement reports zero rows. Under REPEATABLE READ the identical collision raises SQLSTATE 40001 instead, so the handler must fold both shapes into one conflict response.
4. Keep UNIQUE (resource_id, version) even though the predicate already serialises writers. It is what makes a lost update unwritable if any other path ever reaches the revision table, and it converts a logic bug into 23505 rather than into a silently missing history row.
5. Refuse to auto-retry the whole PUT. A retry re-reads the winner's state and reapplies an intent formed against data that no longer exists — the silent overwrite the version token was added to detect. Return the conflict; merge field-wise only if the patches are provably disjoint.
6. Note that now() is the transaction timestamp in PostgreSQL, so resource.updated_at, the revision's created_at and the outbox row share one instant, which is what later makes reconciliation between the three tables unambiguous.

Follow-up

• A client sends the version it read ten minutes ago and the resource has moved three versions. What is in your 409 so it can resolve the conflict without a full re-fetch?
• Two editors, two disjoint fields, no overlap. Does your answer still refuse the second write, and should it?
• Every write now touches a second hot table. How do you keep the outbox insert and its partial index from becoming the write bottleneck at 1.2k writes/second?

Why do you want to work for Aberdeen?

Medium

Approach

1. Clarify what is being asked and what a complete answer contains.
2. Work from the requirement backwards to the design.
3. Say what you would check first and why it is the highest-information step.

Follow-up

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

These questions help us understand your professional journey and your genuine interest in the firm.

Medium

Approach

1. Work from the requirement backwards to the design.
2. State your assumptions explicitly before working the problem.
3. 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?

Choose what to break when replication lag reaches forty seconds

Hard · replication lag · read-after-write · consistency vs availability · load shedding

Reads are served from two replicas: 14k requests/second, about 85% absorbed by cache, so roughly 2.1k reads/second reach the database. Writes go to the primary at 1.2k/second. A tenant's backfill drives replication lag from under 100 ms to 40 seconds and it is still climbing. Sessions that have just written are pinned to the primary. Decide, endpoint class by endpoint class, whether to serve stale, fail, or route to the primary, and justify each choice with the load it adds to the primary. Then state what you would have built beforehand.

Approach

1. Establish blast radius before cause, because mitigation and diagnosis have different deadlines. The decisive arithmetic is what happens if the database reads move to the primary: 2.1k reads/second on top of 1.2k writes/second roughly triples its operation count, on the node already absorbing the backfill that caused this. Reads and writes are not equal in cost, so treat that as an argument against a blanket move rather than as a capacity model - but it is enough to rule out routing everything to the primary.
2. Classify endpoints by what staleness costs, not by how important they feel. Reads whose staleness is invisible - listings, search, counters - stay on the replica and return the watermark so the client can tell. Reads that immediately follow that same session's write keep their primary pin, which is a small bounded slice of traffic rather than the whole 2.1k/second. Reads that feed a decision with a side effect - authorisation, quota, the read half of a read-modify-write - must not be stale at all, because a 40-second-old permission row is the stale-permission failure wearing a different costume; those go to the primary or fail.
3. Shed instead of queueing. If the must-be-fresh class alone exceeds the primary's headroom, refuse its lowest-value slice with 503 and a retry-after. A request queued behind a saturated primary holds a connection for a client that has already given up, and the retry storm that follows is what turns degradation into an outage. Bound the connection pool per role so the read fallback cannot consume the write path's connections - that bulkhead is the single decision that determines whether writes survive the next ten minutes.
4. Attack the cause in parallel, since it is the one thing that can be stopped. The backfill is the load generator. A backfill that reads replication lag as its throttle signal and pauses above a threshold would have made this a non-event, with batch sizes small enough that each batch's write volume is a fraction of what a replica can apply per second. That is most of the answer to what should have existed beforehand.
5. Name the mechanism you would prefer over session pinning. Capture the write position at commit and require the read path to be at or past it: compare the primary's pg_current_wal_lsn() at commit time against the replica's pg_last_wal_replay_lsn(), and fall back to the primary only for the specific request that is ahead of the replica. Session pinning is the cheap approximation and it over-pins - every read in the window goes to the primary whether or not it needed to, which is a share of the cost being paid right now.

Worked solution (35 min)

1. List the endpoints in three buckets - staleness invisible, staleness visible to the writer only, staleness unsafe - and attach the share of the 2.1k reads/second each bucket carries.
2. Compute the primary's operation count under each routing option and mark which options are arithmetically available.
3. Write the pin rule and its window, then the shed rule: which slice, what status code, what retry-after.
4. Write the backfill's throttle predicate against a measured lag value, including its pause threshold and resume condition.

Expected result: Staleness-invisible reads stay on the replica and return a watermark. Reads following the session's own write keep a bounded primary pin. Reads that gate a side effect go to the primary or fail, with the lowest-value slice shed as 503 plus retry-after if headroom runs out. Connection pools are bounded per role so the read fallback cannot starve writes. The backfill is throttled on measured replication lag. The durable fix is a commit-position token compared against the replica's replay position, replacing blanket session pinning.

Verify it yourself

• The traffic routed to the primary under the plan is stated as a rate, not as a category.
• No element of the plan increases the write path's connection usage.
• The backfill throttle references a measured lag value and has an explicit resume condition.
• Every endpoint left on a replica returns something with which a client can detect staleness.

Follow-up

• Lag returns to normal in nine minutes. Which mitigation do you remove first, and which one stays permanently?
• A user reports their change did not save, and the write committed. Trace the path that produces that report and name the signal that would have shown it before the report arrived.
• The replica is 40 seconds behind but otherwise healthy. Do you take it out of rotation? What does that do to the other replica's lag?

Read latency spikes on a sixty-second sawtooth

Medium · cache stampede · single-flight · ttl · tail latency

The cached listing read path serves about 14k reads/second at an 85% hit rate. p99 sits at 35 ms for 57 seconds, jumps to 900 ms for 3, and repeats. During each spike the primary shows several hundred identical listing queries starting within the same millisecond, all carrying one large tenant's id. Cache entries use a 60-second TTL. Give the mechanism, the ordered checks, the fix, and the correctness hazard your fix must not introduce.

Approach

1. Match the period to a configured number before theorising about load. A spike every 60 seconds against a 60-second TTL is an entry expiring, and you confirm it by correlating spike timestamps with the entry's write time rather than with the traffic curve. If the period had matched a cron or a GC interval instead, this is a different investigation.
2. Establish the concurrency of the miss. Several hundred identical queries in one millisecond means the miss path has no coalescing: every request that arrives between expiry and repopulation recomputes. The herd size is that key's arrival rate times its recompute time, so at 1.2k reads/second for the hot key and a 250 ms recompute you expect about 300 concurrent misses, which matches what is observed.
3. Add single-flight on the miss path so one caller per key recomputes under a short-lived lock while the rest wait for its result. Prefer stale-while-revalidate where the read tolerates it: return the expired value immediately and refresh asynchronously, which removes the latency spike rather than serialising it into a queue of waiters.
4. De-synchronise the keys. Write TTLs with jitter, for example 60 seconds plus or minus 10%, so a deploy or a mass invalidation does not align every key on the same second and turn a per-key herd into a fleet-wide one.
5. Name the hazard the fix must not introduce. Serving a stale listing is acceptable only because the API reports the projection watermark, and a reader that loaded the old value before a write can repopulate the entry after the invalidation, so the bounded TTL is what actually caps staleness rather than the delete. Keep read-after-write pinned to the primary for the writing session regardless.
6. Verify on miss concurrency, not hit rate. The hit rate barely moves, because the herd is one miss multiplied; the number that must change is distinct origin queries per key per minute.

Follow-up

• The same sawtooth appears on a key that is invalidated on write rather than expired. Is that the same bug?
• How does your answer change if the recompute takes 4 seconds instead of 250 ms?
• What exactly does a client see during a stale-while-revalidate window, and how does the watermark let them tell?

Suggested practice checklist

Roughly ninety minutes on weeknights with one longer weekend block. The plan cuts scope rather than compressing everything, on the assumption that one thing finished per night beats four half-started.

Day 1 — Fix the scope and take a cold baseline

• Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list and keep it visible all week.
• Take one twenty-five-minute coding problem and one fifteen-minute design prompt cold, and write the single sentence naming what blocked each, because those two sentences decide where the remaining evenings go.
• Set the week's rule: one thing finished every night, including the night you only have forty minutes.

Deliverable: A one-page scope with a not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One pattern, written three times from blank

• Choose the single pattern most likely to appear in your loop and write it three times from an empty file rather than editing the previous attempt.
• On the third pass, write the invariant as a comment before the loop body and the complexity before the first line of code.
• Stop at ninety minutes even if the third version is imperfect, and write the one thing you would fix given another hour.

Deliverable: Three independent implementations of the same pattern plus a note on what changed between them.

Day 3 — One design, only to the depth you can defend

• Take one system shape and go only as far as requirements, interface and data model, refusing to draw a box you could not survive a follow-up about.
• Attach one number to each non-functional requirement, deriving it rather than asserting it, and write the assumption the number rests on.
• Write the one tradeoff you are choosing against and the observation that would make you reverse it.

Deliverable: One design at interface-and-schema depth with derived numbers and one written reversible tradeoff.

Day 4 — Only the fundamentals you will have to defend

• Write, in under two hundred words each, the answers to the two questions that follow almost any implementation: why this structure and not the obvious alternative, and what happens to this code at a hundred times the input.
• Write what an index actually costs: faster lookups on the indexed columns against a write that now maintains a second structure, plus the cases where the planner declines to use it anyway, low selectivity, or a predicate wrapping the column in a function.
• Delete any answer you cannot deliver aloud in under a minute, since an answer that needs reading is not an answer you have.

Deliverable: Three written answers, each under two hundred words and each timed aloud.

Day 5 — Your own work, timed

• Write a ninety-second and a four-minute version of your main project and time both aloud rather than reading them.
• Prepare the two follow-ups that always come: what you would do differently, and how you knew it worked.
• Put one number in the first sentence and be ready to say exactly where it came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in the weekend block

• Run a sixty-minute mock covering a coding round and a design round in one sitting with no break, because sustained attention is the thing evenings have not trained.
• Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
• Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

• Write the twenty-minute warm-up you will actually do on the morning: one problem you can already solve from a blank file, one design you can narrate, and nothing you have never seen.
• Re-read only your own notes from this week and open no new material.
• Write the logistics down: the editor or shared document you will be working in, whether execution and lookups are permitted, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the design structure, the project numbers, and the logistics.

Bounding a vague ticket before writing code

When the requirements were thin, the interesting part is how you fenced the problem off: the assumption you wrote down, who you got to confirm it, the narrow version you shipped first so the rest stayed cheap to change. Guessing and being right is luck. Guessing in writing, where someone could correct you, is method.

Describe a time you went over and above for a customer.

Medium · behavioural and engineering judgement

Approach

1. Give the blast radius: what could have broken, and what you measured.
2. Name the disagreement and how you resolved it with evidence.
3. 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 did you decide not to do, and why?

These questions assess how you handle professional challenges, teamwork, and client-focused scenarios.

Medium · behavioural and engineering judgement

Approach

1. Close with what you would do differently, concretely.
2. Pick a story where you made the decision, not one where you watched it.
3. Give the blast radius: what could have broken, and what you measured.

Follow-up

• What did you decide not to do, and why?
• How did you know your change caused the improvement?

How do you manage working in a fast-moving environment?

Medium · behavioural and engineering judgement

Approach

1. State the situation in two sentences and spend the rest on the reasoning.
2. Give the blast radius: what could have broken, and what you measured.
3. Pick a story where you made the decision, not one where you watched it.

Follow-up

• How did you know your change caused the improvement?
• What did you decide not to do, and why?

How do you approach collaboration within a team?

Medium · behavioural and engineering judgement

Approach

1. Name the disagreement and how you resolved it with evidence.
2. Close with what you would do differently, concretely.
3. Pick a story where you made the decision, not one where you watched it.

Follow-up

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