Interview guides · AXA XL Insurance

AXA XL Insurance Software Engineer Interview Guide

Software engineer interview prep for AXA XL Insurance: original coding, system design and debugging practice, each with a worked solution.

The 60-second brief

A Software Engineer at AXA XL Insurance plays a critical role in transforming how one of the world’s largest commercial insurance and reinsurance companies manages risk. Unlike traditional tech firms, the engineering team at AXA XL Insurance operates at the intersection of complex financial modeling, massive data pipelines, and enterprise-grade cloud architecture. The software you build and maintain directly impacts the company’s ability to underwrite complex risks, analyze global market trends, and deliver sophisticated insurance products to clients worldwide.

Ask whether any round happens inside an existing repository instead of a blank file. Reading unfamiliar code, isolating a fault and making the smallest correct change is a different skill from writing a function from scratch, and it needs its own practice.

Focus areas

Name the isolation level each invariant requires
Store money as integer minor units
Reconcile the ledger against processor settlement files

Overview

In this role, you will work closely with actuaries, underwriters, and data scientists to translate intricate mathematical risk models into highly scalable, performant software solutions. Whether you are optimizing data ingestion pipelines, building APIs for underwriting platforms, or implementing machine learning models, your work directly influences the strategic capabilities of the business. Engineers here must balance rigorous software engineering practices with a deep appreciation for data accuracy and computational efficiency.

The engineering organization at AXA XL Insurance values collaborative problem solvers who are comfortable navigating both legacy systems and modern cloud-native architectures. Succeeding in this role requires not only strong programming capabilities but also the analytical curiosity to understand the underlying business logic and mathematical principles that drive the insurance industry.

The AXA XL Insurance interview process

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

Application Review

Half of this call is the part candidates treat as small talk: start date, notice period, work authorisation and its timing, location and time zone, on-call, and the number. Those are what kill offers late, after several engineers have each spent a day. Surfacing a hard constraint now costs you nothing and occasionally buys you something, since a loop compressed to fit a competing deadline can usually only be arranged if it is asked for early. The common failure is deflecting the compensation question twice, then discovering at offer stage that the band never reached your number.

What to demonstrate

Whether your hard constraints are compatible with the role before a loop gets booked: earliest start, notice period, what authorisation you hold and when it needs action, days on site, willingness to carry a pager
Whether you give a compensation range with something behind it, such as current total compensation or a competing timeline, rather than leaving the band untested
Whether your stated timeline is real, since a competing deadline raised now is something scheduling can sometimes work around and the same deadline raised at offer stage usually is not

How to prepare

Write each constraint down in one line before the call and state them as facts rather than negotiating them live under a question you were not expecting
Set your range from two or three current data points for that level and location, and name the structure you are quoting in, so the number is comparable to the one they are holding
If another process is running, say where it stands and by when, and ask directly whether this loop can be scheduled inside that window

Screening Call

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

Take-Home Assessment

A test suite is read as a statement about what you believe can break, which is why the selection matters far more than the count. Assertions that repeat the happy path with different numbers tell a reviewer nothing. Cases at the boundaries tell them where you looked: empty input, one element, duplicates, a malformed row, a value at the edge of the accepted range. The sharpest question to ask of a test you have just written is whether it would fail if the logic were wrong. If deliberately breaking the function leaves the suite green, that test is documentation rather than a check.

What to demonstrate

Which cases you chose, and whether the suite reaches empty, single, duplicate and malformed inputs instead of the prompt's example with the numbers changed
Whether the suite is deterministic and runs offline, so it produces the same result on a reviewer's machine as on yours
Whether assertions are written against behaviour rather than internals, so a reasonable refactor does not force the tests to be rewritten
Whether a failure is readable on its own, naming the input and the expected value rather than reporting a bare assertion

How to prepare

Mutate your own code by hand before submitting: change a < to <=, drop the last iteration of a loop, return the input unchanged. Anything that stays green is a missing test, and this takes minutes
Remove nondeterminism before writing assertions. Pass the clock in rather than calling now() inside the logic, seed any random generator explicitly, and do not assert on the iteration order of a Go map or a Python set, since Go randomises that order deliberately and Python salts string hashing per process unless PYTHONHASHSEED is pinned
Time a full run of the suite and trace anything slow to its cause, usually a sleep or a real network call, then replace it with a fake so the reviewer is not waiting

Technical Interviews

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.

HR and Behavioral Interview

Your first answer is not really what is scored. It buys the follow-up questions, and those decide the round. An interviewer with fifteen minutes takes one thread and pushes on it four or five times, so a story you can only tell at a single level of detail collapses under the third why. That is an argument for fewer stories known deeply rather than one prepared per prompt. Four or five pieces of work you can still explain down to the code you changed and the argument you had about it will cover nearly anything asked in this round.

What to demonstrate

Whether a story holds as the questioning moves from what you did to why that instead of the alternative, and then to what you would change knowing what you know now
Whether you can re-cut a project to answer the question actually asked rather than delivering a rehearsed block that answers an adjacent one
Whether your level of detail is chosen rather than habitual: going down to the schema when the question is about the data model, staying out of it when the question is about the person who disagreed with you

How to prepare

Pick four projects and write the chain out four levels deep for each: what you did, why that, why not the alternative, and what would have to be true for the alternative to have won. Where you cannot reach the fourth level, you have a placeholder rather than a story
Have someone ask why three times in a row on a single thread with nothing else added, and mark the point where you start repeating a sentence you already said. That point is where the interviewer stops learning anything
Build a one-page index instead of an answer bank: the common prompts in this round (disagreement, a failure that was yours, thin requirements, a deadline you missed, work you inherited) mapped to which of your four projects you would use for each, so the choosing is done now rather than while an interviewer waits

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.

Writing the state change to the database and publishing the event in the same code path

No transaction spans a relational database and a message broker, so a crash between the two leaves one done and the other not, and the failure is asymmetric in both orderings: publish-then-commit invents events for state that never existed, while commit-then-publish silently loses events for state that does. Retrying the publish after the commit is not a fix, because the process can die before the retry runs. The working shape is an outbox row written inside the same transaction plus a relay that publishes it at least once, which makes consumer-side idempotency mandatory rather than optional. Note also that 'exactly-once' in a stream processor means exactly-once processing within that system's own read-process-write transaction, and says nothing at all about an external side effect such as charging a card.

Tests that assert on the implementation rather than the behaviour

Assert on what a caller can observe, not on the number of internal calls or the shape of a private field. A test that breaks on every refactor but still passes when the answer is wrong costs more than it protects.

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.

Questions & practice

Write a recursive function to solve a specific data traversal problem and explain its space and time complexity.

Medium · data structures and algorithms

Approach

State the target complexity and say which constraint rules the naive version out.
Choose the data structure from the access pattern, not from familiarity.
Name the brute-force solution and its complexity before improving on it.

Follow-up

What is the worst case, and how likely is it on real data?
Which test case would catch an off-by-one here?

Write a script to parse and clean a raw dataset, handling missing or corrupted values.

Medium · data structures and algorithms

Approach

Name the brute-force solution and its complexity before improving on it.
State the target complexity and say which constraint rules the naive version out.
Restate the input: its shape, its size, and what is guaranteed about it.

Follow-up

How does this change if the input no longer fits in memory?
What is the worst case, and how likely is it on real data?

Implement a solution to determine if a string of brackets is valid (the Valid Parenthesis problem).

Medium · data structures and algorithms

Approach

Name the brute-force solution and its complexity before improving on it.
Walk one small example through your approach before writing the whole thing.
State the target complexity and say which constraint rules the naive version out.

Follow-up

What is the worst case, and how likely is it on real data?
Which test case would catch an off-by-one here?

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.

Worked solution (40 min)

Compute both costs explicitly: the uniform case at about 4e7 row touches, and the skewed case at about 6e12. Showing that arithmetic is the answer to 'why'.
Implement the offline sweep on a 10-million-row, 50,000-query fixture, merging on (account_id, currency, business_date, entry_id).
Implement the naive version as the reference answer and assert both agree on every fixture query.
Add a correction entry dated 30 days back, re-run, and assert that exactly the queries with as_of_date on or after that date move, all by the same signed amount.
Measure rows touched and wall time for each at 10 million rows, then extrapolate to 400 million and state the assumption that makes the extrapolation valid — sequential I/O, no random seeks.

Expected result: Both implementations agree on all 50,000 fixture queries. After the back-dated correction, exactly the queries at or after its business_date change, each by the identical signed amount. The sweep touches every entry row once; the naive version touches the hot account's rows once per query against it.

Verify it yourself

The balance at the maximum as_of_date equals the plain full-file aggregate for that account and currency.
Summed across all accounts at any single date, the result is 0 per currency — provided every entry in a transaction shares one business_date, which is worth asserting separately rather than assuming.
The sweep's answers do not change when entries are fed in a different physical order, because the sort key is total.
No answer depends on posted_at: swapping two entries' posted_at within one business date changes nothing.

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.

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?

Store multi-currency amounts and prove each transaction balances

Easy · minor units · normalisation · constraints

ledger_entry holds entry_id, transaction_id, account_id, direction (debit or credit), amount_minor (bigint, CHECK > 0), currency char(3), source_type, source_id, business_date and posted_at. The service posts in JPY, USD and KWD. Specify the column types for money and the reference table that carries each currency's ISO 4217 minor-unit exponent, and say why that exponent must not be a constant in application code. Then write the query that returns every transaction_id whose entries fail to sum to zero within each currency, ordered by the largest absolute imbalance.

Approach

State the representation: amount_minor is an integer count of minor units and the scale lives with the currency, not in the column. int4 tops out at 2,147,483,647 minor units, which is about 21.5 million units of a 2-exponent currency, so bigint, and the currency code travels on every row that carries an amount.
Normalise the scale into currency(code char(3) primary key, exponent smallint, name) and join it only at display and parse boundaries. The exponent is 0 for JPY and KRW, 2 for USD and EUR, 3 for KWD, BHD, JOD, OMR and TND, so a hard-coded divide by 100 is wrong by 100x for JPY and by 10x for KWD, in opposite directions.
Rule out binary floating point outright: IEEE 754 binary64 cannot represent 0.1, so repeated accrual drifts by a few minor units that later appear as reconciliation breaks. If a fixed-scale decimal is used instead, pin the scale per currency and name the single place rounding happens.
Write the balance check as a grouped aggregate: sign the amount with a CASE on direction, GROUP BY transaction_id, currency, and HAVING the signed sum <> 0. The sign belongs in direction, and the CASE is the one place it becomes arithmetic.
Then order in a second layer, because the ordering the question asks for cannot be written on the grouped query itself. PostgreSQL lets an output alias stand alone in ORDER BY but resolves anything inside an expression against the input columns, so ORDER BY abs(imbalance_minor) DESC raises SQLSTATE 42703, column "imbalance_minor" does not exist, while the bare ORDER BY imbalance_minor DESC is accepted and sorts signed rather than absolute. Wrap the grouped query and order by abs() outside it, or repeat the aggregate as ORDER BY abs(SUM(CASE ...)) DESC, which is legal because aggregates are allowed there.
Group per (transaction_id, currency) rather than per transaction, because a cross-currency movement balances only within each currency, bridged by an explicit FX position account whose rate, source and timestamp are stored on the transaction.
Note that no CHECK constraint can express this, since it spans rows: enforce it at write time with a DEFERRABLE INITIALLY DEFERRED constraint trigger that fires at commit, or by routing every posting through one function, and keep this query as the independent audit.

Worked solution (15 min)

Define currency(code, exponent, name) and seed JPY 0, USD 2, KWD 3; declare amount_minor bigint NOT NULL CHECK (amount_minor > 0) and currency char(3) REFERENCES currency(code).
Write it in two layers so it runs: SELECT transaction_id, currency, imbalance_minor FROM (SELECT transaction_id, currency, SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END) AS imbalance_minor FROM ledger_entry GROUP BY transaction_id, currency HAVING SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END) <> 0) b ORDER BY abs(imbalance_minor) DESC.
Post one clean capture, one deliberately unbalanced transaction, and one cross-currency transfer with an FX position account, then run the query.
Convert 1000 JPY and 1000 KWD through the exponent table in both directions and confirm the round trip is exact.

Expected result: Exactly one row: the deliberately unbalanced transaction, with a signed imbalance_minor equal to the amount omitted. The clean capture and both single-currency sides of the FX transfer return nothing.

Verify it yourself

The cross-currency transaction produces two groups, one per currency, and neither appears in the output.
Flipping one entry's direction makes imbalance_minor exactly twice that entry's amount, which confirms the sign convention.
Inserting an entry with amount_minor = 0 or a negative value is rejected by the CHECK rather than by application code.
Execute the statement rather than reading it: ordering the grouped query directly by abs(imbalance_minor) fails with 42703, which is why the abs() sits outside the subquery.

Follow-up

A transaction has a USD leg and a JPY leg. Which accounts appear, and where does the rounding residual land?
How would you enforce the zero-sum rule at write time without serialising all postings behind one lock?
Someone proposes storing a display amount alongside amount_minor. What goes wrong at the first rate change or rounding rule change?

What happens mathematically if you press the square root button on a calculator repeatedly for a number greater than 1? What if the number is between 0 and 1?

Medium

Approach

Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.
Work from the requirement backwards to the design.

Follow-up

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

How many trailing zeros are in 100 factorial (100!)? Explain the mathematical logic behind your calculation.

Medium

Approach

Work from the requirement backwards to the design.
Say what you would check first and why it is the highest-information step.
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?

Design the error taxonomy for a money-moving API

Easy · error taxonomy · retries · api contract

The caller is a merchant's server-side integration behind a 2 s timeout; on any non-2xx it consults a generic retry policy. Design the error contract for POST /payments: the response media type, the fields every error carries, and the classification a caller switches on. Cover input validation, authentication, state conflicts, rate limiting, indeterminate upstream outcomes, and issuer declines. For each class give the status code, whether a retry is safe, and whether that retry reuses the idempotency key. Deliver the schema and the classification table.

Approach

Fix the envelope first: application/problem+json (RFC 9457, which obsoletes RFC 7807) with type, title, status, detail and instance, plus two extension members, a stable machine code and a retry class. Callers switch on the code; the prose is for humans and must be free to change.
Separate transport failure from business outcome. An issuer decline is a completed call with a definite answer, so it belongs in the payment representation as 200 with status failed and a decline code. Putting it behind a 4xx or 5xx invites every generic retry layer in the caller's stack to re-present a card the issuer already refused.
Classify the rest on two axes: is the resulting state known, and is the caller at fault. Known and caller's fault gives 400/422 for validation and 401/403 for credentials, never retryable. Known and ours gives 409 for a fingerprint or version conflict, retryable only after the caller changes something. Unknown gives 502/504 or an explicit status of processing, retryable with the original key. Overload gives 429 with Retry-After.
Write the rule for the unknown class explicitly: the retry must carry the same idempotency key and the server must replay the stored response, so repeated attempts converge on one effect rather than several.
Pin the taxonomy with a test that asserts every error path emits a code from the enumerated set. Without it the catch-all handler produces an uncoded 500 and the caller's switch silently falls through to its default branch, which is usually retry.

Worked solution (20 min)

Write the problem+json schema: type, title, status, detail, instance, code (stable enum), retry_class (never | after_backoff | same_key).
Enumerate ten concrete failures from the real path: missing currency, unknown merchant, expired instrument token, key reused with a different body, concurrent duplicate key, quota exceeded, processor timeout, processor unreachable, issuer decline, insufficient funds.
Place each one in the table with its status code, code, retry_class and the caller's action in one line.
Re-read the two decline rows and confirm they are 200 responses carried in the payment representation, not entries in the error envelope.
Write the caller-side switch in pseudocode and check that every branch is reachable from the table and that nothing falls through to a default that retries.

Expected result: A table in which declines are 200 responses, exactly three classes are retryable (429, indeterminate 5xx, and explicit processing), every retryable-with-key row states that the original Idempotency-Key must be resent, and no 4xx other than 429 is marked retryable.

Verify it yourself

Every error carries a stable code distinct from its HTTP status, so two different 409s are distinguishable by a caller.
A caller that implements only the retry_class column never re-presents a declined card.
Replaying a completed idempotency key returns the original status code, including when that status was itself a 4xx.

Follow-up

Two requests arrive with the same idempotency key while the first is still in flight. What status do you return, and what is the caller supposed to do with it?
How do you add a new error code later without breaking a client that switches exhaustively over the current set?
The caller reports that a 504 from you was followed by a successful payment they never saw. What in this contract lets them discover that without charging twice?

Authorisation p99 tripled overnight with no deploy

Medium · index bloat · autovacuum · long-running transaction · latency

Payment orchestration serves about 3,000 authorisations per second against a 150 ms p99 budget. Since 02:00, p99 is 460 ms and rising about 8 ms per hour, while p50 is unchanged at 11 ms. There was no deploy, no traffic change and no processor degradation. The hot path does one INSERT into idempotency_key, which has UNIQUE (scope, key), then two UPDATEs on that row: locked_at, then status and response_body. A nightly reconciliation job started at 01:50 and is still running. Give the ordered diagnostic checklist and the cause.

Approach

Read the shape first. p50 flat with p99 rising and no deploy is a resource or data-volume effect, not a code path, because a code change moves the median too. A tail that climbs monotonically at fixed workload means something monotonically grows.
Ask what started at 01:50. In PostgreSQL an open transaction holds back the xmin horizon cluster-wide, so autovacuum can reclaim no dead tuple newer than that snapshot. Confirm with pg_stat_activity (state, now() - xact_start, backend_xmin) and with pg_stat_all_tables (n_dead_tup, last_autovacuum) for idempotency_key.
Connect it to the write pattern. Three writes per key produce up to two dead tuples each, so at 3,000 rps the table sheds roughly 6,000 dead tuples per second. Heap-only tuple updates would keep those out of the index, but only when no indexed column changes and the page has room, and appending response_body grows the tuple enough to force a new page. So the unique index on (scope, key) grows too.
Explain why only the tail suffers. A larger index means more pages per lookup and a rising fraction of them missing shared_buffers; the median request still hits cache while the tail pays physical I/O. This is exactly the p50-flat, p99-rising signature, and it is worth stating before acting.
Verify before fixing rather than after. n_dead_tup in the millions and rising, last_autovacuum stale since about 01:50, and pg_relation_size on the unique index measured twice fifteen minutes apart showing growth at constant workload. Index bloat cannot be inferred from row count alone; use the size series or pgstattuple.
Fix in two moves and prevent separately. End or chunk the long transaction so the reconciliation job commits per batch instead of holding one snapshot over 50M lines, then let autovacuum catch up or run REINDEX CONCURRENTLY. Add a transaction-age alert and a statement timeout on the reporting role. Collapsing the two UPDATEs into one and lowering fillfactor halves dead-tuple production, but that is an optimisation, not the cause.

Follow-up

The reconciliation job legitimately needs a consistent view of 50M lines. How do you give it one without pinning the xmin horizon?
Why did p50 not move at all?
You also run an expires_at cleanup job that DELETEs old idempotency keys. During this incident, does running it help or hurt, and what design avoids the question entirely?

Suggested practice checklist

Four days sample coding, design, fundamentals and the practical rounds at deliberately shallow depth, which is enough to surface the topics you did not know were in scope. That map, rather than a guess made on day one, decides where the last three days go.

Day 1 — Coding, one pass at shallow depth

Solve one problem from each of six families, an array with two pointers, hash counting, binary search, a tree traversal, a graph traversal and one dynamic program, under a hard twenty-minute cap with no extensions, marking each finished, late, or stalled.
For every stall, write the exact move you could not make rather than the subject, so the note reads could not turn the recurrence into a loop rather than bad at dynamic programming.
Fix nothing today. The value of the pass is the unfixed record.

Deliverable: Six timed attempts marked finished, late or stalled, each stall carrying a named blocking move.

Day 2 — Design, one pass at shallow depth

Spend twenty minutes each on three different shapes, a read-heavy feed, a write-heavy ingest path, and something needing a transaction across two entities, stopping each at requirements, interface and data model.
After each, write the first question you could not answer, which is usually a number you could not estimate or a failure mode you had no vocabulary for.
Mark which of the three you would be most relieved not to be asked, and treat that as data rather than as a preference.

Deliverable: Three shallow designs, each with the first unanswerable question written at the bottom.

Day 3 — Fundamentals and the practical rounds

Answer eight short questions in writing at four minutes each, covering the material that fills the gaps between the big rounds: what happens between a URL and a rendered page, what an index costs on write, when a process is preferable to a thread, and what conditions a deadlock requires.
Do one thirty-minute practical task of the kind a take-home compresses: read an unfamiliar two-hundred-line file and write what it does, what you would change, and the one thing you remain unsure of.
Score every answer fluent, correct but slow, or absent, and keep the absent ones visible.

Deliverable: Eight scored short answers and one written reading of unfamiliar code.

Day 4 — The rounds that are about you, and the map

Deliver three behavioural answers aloud against a timer, a conflict, a failure you owned, and a decision made without enough information, marking any that ran past three minutes or contained no number.
Assemble the map: every marked item from days one to three on a single page, sorted by how likely it is to appear in your loop rather than by how uncomfortable it felt.
Choose exactly two areas for the remaining three days and write down what you are deliberately abandoning.

Deliverable: A one-page scored map of the whole surface area with two areas chosen and the rest explicitly abandoned.

Day 5 — First chosen area, to the depth you skipped

Work the higher-ranked area in four focused blocks, choosing items one level above where you stalled rather than repeating what already works.
After each block write the rule you extracted in one sentence with its precondition attached, since a rule carrying no precondition is exactly what fails under a variation.
Re-attempt the day-one or day-two item that exposed this area and compare against the original timing.

Deliverable: Four worked blocks, a timed re-attempt against the original, and three one-sentence rules with preconditions.

Day 6 — Second chosen area, where the gap is coverage rather than speed

Treat the second area differently from the first. Day five drilled something you could already half-do; this one is usually a topic you had simply never met, so build one worked reference example end to end and keep it, rather than attempting six problems badly.
Write down the vocabulary you were missing on day two or three, five terms at most, each with the one sentence that makes it usable in an answer rather than the textbook definition.
Redo the shallow attempt that exposed this area and note whether you now fail later in the problem, because moving the failure point is the realistic gain from a single day and is worth more than a score that did not change.

Deliverable: One worked reference example for the newly covered area, a five-term vocabulary list, and a note on where the failure point moved.

Day 7 — Reassemble the loop

Sit two rounds back to back with no gap, ordering them so the area you chose second comes last, because the map was built from rested, isolated attempts and the loop will reach your weaker area when you are already spent.
Write where the second round suffered from the first, which is normally the point at which structure collapses into narration.
Reduce the week to one page holding only the rules you can state without reading them.

Deliverable: Mock notes on cross-round carryover plus a one-page card of rules you can recite from memory.

What the behavioural round is actually measuring

Every story you tell gets read for blast radius and judgement: what could have broken, who else it touched, what you knew at the moment you decided. Nobody can audit your code in an hour, so they audit your reasoning instead. Pick work where the call was genuinely yours and the consequences were real enough to remember.

Force an implicit timeout behaviour into an explicit decision

Medium · fail open · risk decisioning · decision records

The risk decision service has an 80 ms p99 budget inside a roughly 2 s caller timeout. Today, when its feature store is unavailable, the timeout handler returns approve. Nobody chose that; it is what the code does. You need a real decision: fail open, fail closed, or refer, potentially differing by amount band. Describe a time you turned an accidental behaviour into an owned decision. State who had to be in the room, the data you brought, what you did when nobody wanted to own it, and where the decision was recorded so it outlived you.

Approach

The probe is whether you can drive a cross-functional decision rather than escalating and waiting. Lead with the framing that makes it undeniable: this is already a product decision, it is currently being made by an exception handler, and the only question is whether anyone reviews it.
Bring the two losses side by side instead of arguing a principle. Fail open costs expected fraud loss on approved-but-should-have-declined volume during the outage; fail closed costs declined good payments, which is lost revenue plus customer harm and a support queue; refer costs manual review capacity, which is a headcount number and saturates within minutes at 3,000 decisions per second. Give each as a rate per minute of outage using real volume.
Propose the banded answer as the default, because the two losses cross over at an amount: below some threshold the expected fraud loss is smaller than the expected decline loss, above it the reverse, and the crossover is computable from observed fraud rate by band. That converts a values argument into an arithmetic one.
Name the attendees by the decision they own, not by title: whoever carries fraud loss, whoever carries approval rate, and whoever staffs manual review. Three people who can each say yes is a decision; eight people who can each say no is a meeting.
Say what you did when ownership was contested. A strong answer has a forcing function: propose a default in writing with a review date and state that it ships unless someone objects, which converts inaction into consent rather than into another meeting.
Record it where the code can find it: the decision, its date, its owner, the amount thresholds, and a test asserting the fallback behaviour, so the next engineer reading the timeout handler learns it was chosen. A wiki page nobody links from the code is the generic answer.

Follow-up

The feature store is degraded rather than down and the model is scoring on stale features. Is that the same decision?
How do you stop the banded thresholds from silently rotting as fraud patterns shift?
Nobody objects to your written default, and six months later there is an outage and a loss. Who owns it?

Estimate a reconciliation rebuild you have never attempted

Hard · estimation · unknowns · reconciliation

You are asked how long it takes to replace a reconciliation service matching 30 million settlement lines a day against the ledger, including a bounded fuzzy fallback for netted fees and an ageing model for breaks. You have never built one. Produce an estimate, the range around it, and the two or three unknowns that dominate that range. Then describe a time you estimated unfamiliar work: what you did in the first day to shrink the range, what you committed to publicly, how far off you were, and what you would tell the requester differently now.

Approach

The probe is whether you can be useful under uncertainty without either refusing to estimate or inventing false precision. Give a number with an explicit range and the basis for both, then immediately name what would move it, rather than asking for two weeks of discovery first.
Decompose into parts with different uncertainty profiles. The hash join on (external_reference, amount_minor, currency, business_date) over 30 million lines is well understood engineering and estimates tightly; the fuzzy fallback for netted and fee-adjusted lines does not, because its scope is defined by whatever the files actually contain; the ageing and break workflow is mostly operations-facing surface area, which estimates by counting screens and states.
Name the dominating unknowns concretely: how many distinct file formats and cutoff conventions the sources use, what fraction of lines are netted rather than itemised, and whether business_date is derivable from any field in the file or must be reconstructed from the cutoff rule. Each is a factor on the fuzzy path, not a percentage on the whole.
Describe the first-day range-shrinking work, which is the part that separates strong from generic: take one real file, count distinct formats, measure the netted fraction, and attempt the exact join on a single day of postings to see what the residual actually is. One day of that typically converts a 3x range into something near 1.5x.
Commit in a form that survives being wrong: a range plus a checkpoint date at which you will replace it with a narrower one, and an explicit statement of what you will cut first if the range turns out to be optimistic.
In the retrospective half, give the real numbers: the estimate, the actual, and the specific thing that consumed the difference. Answers that were within 10 percent are less informative than answers that were 2x off for a nameable reason.

Follow-up

The requester wants one number, not a range, for a board deadline. What do you give them?
Your one-day probe finds 40 percent netted lines instead of the 5 percent you assumed. What changes in the plan, not just the estimate?
What do you cut first if you are at the deadline and the fuzzy fallback is not done?

Resolve a review disagreement over isolation level

Easy · code review · isolation levels · lost update

A colleague's pull request reads a balance, compares it to a floor in application code, then issues an UPDATE with the computed value, all under PostgreSQL's default READ COMMITTED. You comment; they reply that staging has never produced a negative balance. Describe a code review disagreement where you were confident and the author was not convinced. State how you made the failure concrete rather than theoretical, how many rounds it took, at what point you would have escalated or approved anyway, and what you conceded to the author.

Approach

The probe is whether you can convert a correctness objection into something reproducible instead of a stalemate of opinions. Name the anomaly by its mechanism: under READ COMMITTED each statement takes a fresh snapshot, so two sessions can both read balance 100, both compute 100 minus 80, and both write 20.
Address the staging evidence directly rather than dismissing it. Staging concurrency on one account row is effectively one, so the absence of the anomaly there is expected under both the broken and the correct implementation. That is the sentence that usually ends the argument.
Reproduce it in two psql sessions and paste the interleaving into the review. A twelve-line transcript settles in one round what three paragraphs of theory will not settle in four.
Offer the fix as a choice with its trade-off, not as a verdict: an atomic UPDATE ... SET balance = balance - $1 WHERE account_id = $2 AND balance - $1 >= $3 with a rowcount check keeps it single statement and needs no retry; SELECT ... FOR UPDATE serialises the row and lets you compute in application code; SERIALIZABLE covers the multi-row version of the predicate but requires a bounded retry on SQLSTATE 40001 that someone has to actually write.
Say where your bar is. Correctness on money is a blocking comment, style is not, and a strong answer states that boundary before the disagreement rather than discovering it during one.
Name what you conceded. The author was usually right about something (scope, naming, the follow-up being separable), and saying so is what makes the blocking comment land next time.

Follow-up

The author switches the service to MySQL. Which of the three fixes still behaves the same, and which changes silently?
The same endpoint later transfers between two accounts. What do you now require in the review?
How do you keep this from being relitigated in every future pull request?