Interview guides · Allegheny Health Network

Allegheny Health Network Software Engineer Interview Questions & Guide 2026

Testing practice for Allegheny Health Network software engineer candidates: the boundaries worth covering, and why flakiness is a real defect.

The 60-second brief

As a Software Engineer at Allegheny Health Network, you play a vital role in bridging the gap between complex healthcare data and actionable clinical outcomes. You are not just writing code; you are building the digital infrastructure that supports patient care, administrative efficiency, and the flow of health information across Allegheny Health Network. Your work directly impacts how providers access and utilize data, making your technical contributions essential to the mission of providing high-quality, patient-centered care.

Treat capacity estimation as a conversion skill rather than a table to memorise: turn a user count and an action rate into requests per second and bytes per day, then name the component that number breaks first. The figure only matters if it changes the design.

Focus areas

Model bitemporal coverage and retroactive eligibility changes
Hold accumulator limits under concurrent claim adjudication
Authorise every identified read against current consent

Overview

This role requires a unique balance of technical precision and a deep understanding of healthcare workflows. You will often find yourself tackling challenges related to data integrity, system interoperability, and the optimization of outpatient coding and documentation processes. It is an environment where your ability to solve problems with accuracy and attention to detail affects the operational success of the network's clinical teams.

Your work at Allegheny Health Network directly supports the frontline staff. Keep the patient at the center of your technical decisions, as even backend optimizations can influence the speed and quality of care delivery.

The Allegheny Health Network interview process

Allegheny Health Network candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Technical Assessments

What this round decides is narrow: whether you can produce code that runs and is correct on inputs nobody showed you. An elegant solution that does not compile scores below a plain one that does, so write a correct brute force first, say out loud that you know its cost, and improve it with the working version still on screen. What separates strong answers is who finds the broken case. Trace your own code against an empty input, a single element, and duplicate keys before you say you are finished, because being told is far more expensive than noticing.

What to demonstrate

Whether degenerate inputs get checked without being asked for: an empty collection, one element, every element equal, and the extreme value the input type allows
Whether the complexity you state matches the code you actually wrote, including a sort or a copy sitting inside a loop
Whether the finished answer is verified against the worked examples before you call it done, rather than assumed correct because the code reads correctly

How to prepare

Take five problems you have already solved and, without running anything, write down what each returns for empty input, a single element, and all-duplicates. Then run them and count how many you predicted wrong.
Drill the brute force as its own skill: on ten problems, write only the obviously-correct slow version and time how long it takes to get it passing. If that is more than a few minutes, that is what to practise, not the optimal version.
Add a fixed last step before you submit anything, reading only the loop bounds and the initial value of each accumulator, which is where most off-by-one errors live

Behavioral Evaluations

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

Writing the access audit record inside the read transaction, or firing it off after the response with no durability.

Inside the transaction, an audit-store outage blocks clinical reads and turns a logging dependency into a care outage. Fire-and-forget afterwards means the audit trail is incomplete during precisely the incidents it exists to reconstruct, and the gaps are invisible until someone asks for the log. The usual resolution is committing the access decision and its audit row together to a local outbox and shipping asynchronously, which keeps the read path available while preserving durability.

Storing monetary amounts as floating-point numbers.

Binary floating point cannot represent values like 0.10 exactly, so summing allowed and paid amounts across millions of claim lines accumulates representation and rounding error. Reconciliation against a remittance file is exact to the cent, so the drift surfaces as a balance that is off by a few cents with no identifiable cause, and engineers lose days looking for a logic bug in code that is correct apart from its numeric type. Integer minor units or an exact decimal type removes the class entirely.

Reading the constraints as preamble rather than as part of the problem

The bounds are usually there to eliminate the obvious approach: n up to 10^5 makes an O(n^2) scan roughly 10^10 operations, far outside any per-test time budget, and an input larger than memory rules out loading it at all. When a bound is not given, ask for it, then say out loud which approach it kills.

Not asking what the system looks like if it dies halfway through

For any multi-step write, say what state remains if the process stops between step two and step three, and what brings it back: a single transaction, a saga with compensating actions, an outbox, or a reconciliation job. Partial failure is routine at any real call volume, so 'that shouldn't happen' is an answer with nothing behind it.

Questions & practice

Merge twelve resource streams into one patient summary page

Medium · k-way merge · heap · pagination

A patient summary fans out to between 8 and 12 resource types. Each returns a network-backed, paged iterator of resource versions sorted by issued_ts descending, up to 200,000 versions per type for one person. Return the 50 most recent current versions across all types, where current means no later version supersedes it within the same logical resource, and a logical resource whose current version is entered_in_error is omitted entirely. You may not materialise the iterators. Give time and space in terms of k types, the result size and the page size.

Approach

k-way merge with a max-heap holding one head per iterator, keyed on issued_ts. Seeding is O(k), each pop is O(log k), so reaching R emitted rows costs O(k + P log k) for P pops, with O(k) heap space plus one page buffered per iterator. Fetching everything and sorting is O(V log V) over V up to 2.4 million versions and drags every page across the network to produce 50 rows.
Suppress with a hash set of logical resource ids already seen, recorded on first sight whether or not that version is emitted. A logical resource has exactly one resource type, so all of its versions arrive on one iterator, and that iterator is descending in issued_ts: the first version you see for a logical resource is its newest. Deciding on first sight and suppressing every later pop for that id is therefore correct in one pass with no lookahead.
Count emits, not pops. A correction-heavy chart can burn many pops per emitted row, so a loop that stops at 50 pops returns a short page. Put a bound on total pops as well, and when it trips, return what you have with a continuation token rather than spinning.
Break issued_ts ties deterministically on (resource type, resource id). Without it two identical requests return two different orderings and the next page silently skips or repeats rows.
Handle entered_in_error at first sight: the erroneous version still supersedes its predecessor, so record the id in the seen set and emit nothing, dropping the whole logical resource instead of falling back to the value it replaced. Recording it is the load-bearing half. Skip it and the next pop re-displays the value a clinician already retracted.
Summarise the budget honestly: the composite p99 is what the user feels, and it is bounded below by the slowest of the k iterators, so the merge fixes the ordering cost but not the fan-out tail.

Worked solution (25 min)

Seed the heap with the head of each iterator and an empty emitted-id set.
Pop the maximum by issued_ts, push that iterator's next head, and check the logical resource id against the set.
On first sight record the id, then emit unless that version is entered_in_error; on a repeat sight suppress. Stop when emits reach the target.
Return the rows plus a continuation token carrying the last (issued_ts, type, id) tie-break tuple.

Expected result: Three streams: labs hold A2 at 10:05 which supersedes A1 at 09:00, plus B1 at 08:30; medications hold C1 at 09:30; encounters hold D1 at 10:01. Asking for three rows emits A2, D1, C1 in three pops. Asking for four takes five pops: the fourth pop is A1, suppressed because logical resource A was already seen, and the fifth is B1, emitted. Now mark A2 entered_in_error: asking for three rows takes those same five pops but emits D1, C1 and B1, because the A2 pop records logical resource A and emits nothing, and the A1 pop is then suppressed rather than resurrecting the 09:00 value.

Verify it yourself

Emitted rows are non-increasing in issued_ts.
No logical resource id appears more than once in the page.
Every pop falls into exactly one of three buckets, so pops equal emits plus later versions suppressed plus logical resources dropped as entered_in_error. Pops equal emits only when the popped prefix holds no second version of any logical resource and no first-sight version that is entered_in_error.
Running with k set to 1 returns that stream's first 50 surviving current versions in exactly 50 pops plus one per later version suppressed plus one per logical resource dropped as entered_in_error.

Follow-up

One of the twelve iterators has a p99 of 400ms while the rest return in 20ms. What is your composite p99 and what would you change first?
The user pages to rows 51 through 100. How do you resume without re-reading from the top, and what breaks if issued_ts is not unique?
One resource type is accidentally returning ascending order. How would your code detect that rather than quietly emitting the oldest rows?

Resolve enterprise identity from a link log that supports unmerge

Hard · union-find · rollback · identity resolution

person_identity_link holds link_id, enterprise_person_id, assigning_authority, source_person_id, match_score, link_status (auto_linked, manual_linked, potential_duplicate, unlinked, rejected), version, superseded_by_link_id, decided_by, decided_at. You have up to 40 million source identities and 60 million decisions applied in decided_at order. Build a structure answering which enterprise identity a given (assigning_authority, source_person_id) resolves to after any prefix of the log, and supporting a revert of the k most recent merges at O(1) each. State the query complexity, and say why path compression is unavailable to you.

Approach

Intern the node key as the pair (assigning_authority, source_person_id) into a dense integer index. Never key on source_person_id alone: two facilities in one network routinely issue the same medical record number to different people, so a bare-value key merges two patients before any matching logic has run, and a single-source test fixture will never show it.
Classify the log rows before touching the structure. auto_linked and manual_linked are unions; potential_duplicate and rejected are recorded decisions that must not union anything; unlinked is a revert of the link it supersedes, not a new edge.
Use union by size with an explicit undo stack, pushing (attached_root, its previous parent, the previous size of the absorbing root) on every union. Find walks parent pointers to the root: union by size bounds tree height at log2(n), so a query is O(log n) worst case, a union is O(log n), and a revert pops two words and is O(1).
Path compression is the thing you have to give up, and the reason is specific: it rewrites parent pointers of nodes that were never named in the union being recorded, so the undo entry no longer describes the mutation that happened and a revert restores a forest that is quietly wrong. Near-constant find is only available to an append-only structure.
Hold enterprise_person_id as a property of the root slot rather than a value copied onto every member. A merge then relabels one slot instead of n rows, and an unmerge restores two labels instead of reconstructing n.
State the price plainly: every read path gains a resolution hop and no downstream table can carry a plain patient_id column. That cost is what buys reversibility, and it belongs in the design discussion rather than being discovered later by whoever writes the first join.

Follow-up

A merge is found wrong three weeks and 200,000 unions later, and your stack only reverts a suffix. What do you do instead, and what does that cost?
A claim was posted under the losing identity before the merge. After the unmerge, which identity owns it, and what in your design let you answer that?
Two matcher instances submit unions concurrently. What is the smallest change that keeps both the structure and the undo stack consistent?

Group transfer-chained encounters into episodes with out-of-order arrival

Medium · graph traversal · cycle detection · memoisation

You have up to 5 million encounter rows for a reporting month, in arbitrary order: encounter_id, enterprise_person_id, facility_id, encounter_class, admit_ts, discharge_ts (nullable), status, prior_encounter_id (nullable, set on transfer), source_event_ts. prior_encounter_id may reference a row that arrives later or never arrives. Group rows into episodes, one per maximal transfer chain, returning episode root, person, earliest admit_ts, latest discharge_ts (null if any member is still open) and member count. Report cycles instead of following them. Target O(N) expected time.

Approach

Recognise the shape: every row has at most one prior_encounter_id, so this is a forest of parent pointers, not a general graph. A hash map from encounter_id to row is the whole index you need, and the work is O(N) expected rather than anything sort-shaped.
Resolve roots by iterative pointer-following with write-back memoisation: from each node walk prior_encounter_id until you reach a node with no parent, a parent absent from the input, or a node whose root is already known, then stamp the discovered root onto every node on that path. Each node is finalised once, so total work stays O(N) even when one chain is very long.
Do not recurse. A malformed feed can produce a chain tens of thousands deep, and a recursive resolver dies on stack depth taking the whole batch with it. An explicit loop with write-back is both faster and bounded.
Detect cycles with three-state marking: unvisited, on the current path, finalised. On reaching a node that is on the current path, emit a data-quality record naming every encounter_id in the cycle and exclude that component from the episode output. Choosing an arbitrary root instead would produce an episode that looks plausible and will be believed.
Keep a dangling prior_encounter_id distinct from a genuine root. It means the chain starts outside the window or has not arrived, so flag the episode left-truncated and carry that flag into the output: an episode with an unknown start cannot be used for length of stay or readmission counting without biasing both downward.
Compute aggregates during the same pass that assigns roots: minimum admit_ts, maximum discharge_ts with null propagation so any open member leaves the episode open, and exclude cancelled and entered_in_error members from the aggregates while keeping them retrievable.

Follow-up

Two encounters name each other as prior_encounter_id. What does your detector emit, and who needs to see it?
One episode spans two facilities whose clocks differ by four seconds. Do you order on source_event_ts or admit_ts, and what does the choice cost?
A chain is cut in half by the month boundary. What does this month's report say, and how do you make next month's agree with it?

Add a backfilled NOT NULL column to a live encounter table

Hard · online migration · ddl locks · backfill

encounter holds 900 million rows and is written continuously by the admit/discharge/transfer consumer. You must add admission_source_code text, backfill it per row from a lookup on the source message archive, constrain it to a fixed code set, make it NOT NULL, and add an index on (facility_id, admit_ts) — with no write outage and no statement blocked longer than one second. Give the ordered steps, the lock level each takes, why the obvious single ALTER is unavailable here, and the session setting that stops a DDL statement from stalling everything behind it.

Approach

Say why the shortcut does not apply. Since PostgreSQL 11, ADD COLUMN with a constant default is metadata-only and cheap, but this value is derived per row from an archive lookup, so there is no constant to store and the table must be written row by row regardless.
Add the column nullable first: catalog-only, ACCESS EXCLUSIVE for microseconds. The danger is not the statement's duration but the lock queue — a pending ACCESS EXCLUSIVE blocks every reader that arrives behind it, so a long-running report turns a microsecond DDL into a multi-minute outage. SET lock_timeout = '1s' before the ALTER and retry on failure; that converts a potential outage into a retried statement.
Deploy dual-write before backfilling. The consumer must populate the column on every insert and transfer from that moment, or the backfill chases a moving tail forever.
Backfill in bounded batches over primary-key ranges, a few thousand rows per transaction with a pause between, restricted to WHERE admission_source_code IS NULL so it is resumable and idempotent. One giant UPDATE holds a transaction open for hours, pins the xmin horizon so autovacuum cannot clean anything, and bloats the table by a full row version per updated row.
Add the value constraint as CHECK (...) NOT VALID first — ACCESS EXCLUSIVE, no scan — then ALTER TABLE ... VALIDATE CONSTRAINT, which takes only SHARE UPDATE EXCLUSIVE and scans without blocking reads or writes.
Reach NOT NULL without a blocking scan: add CHECK (admission_source_code IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL, which on PostgreSQL 12 and later uses the validated constraint as proof and skips the full scan; drop the now-redundant CHECK afterwards. Build the index with CREATE INDEX CONCURRENTLY, which takes SHARE UPDATE EXCLUSIVE, makes two passes plus a wait for concurrent transactions, cannot run inside a transaction block, and leaves an INVALID index behind on failure that must be dropped and rebuilt.

Worked solution (45 min)

Write the runbook as numbered statements with the lock level annotated next to each, and mark which ones may be retried safely.
Set lock_timeout = '1s' and statement_timeout deliberately high for the VALIDATE steps, since they are long but non-blocking.
Write the batch loop with an explicit key range and a resume predicate, plus a query that reports remaining NULLs so progress is observable.
Sequence NOT NULL as CHECK NOT VALID, VALIDATE, SET NOT NULL, DROP CONSTRAINT, and state which of those four takes ACCESS EXCLUSIVE.
Run CREATE INDEX CONCURRENTLY outside any transaction block and monitor pg_stat_progress_create_index through its phases.
Write the rollback for each step, including the DROP INDEX CONCURRENTLY for a failed build.

Expected result: 1) SET lock_timeout='1s'; ALTER TABLE encounter ADD COLUMN admission_source_code text; (ACCESS EXCLUSIVE, catalog-only, retry on lock timeout). 2) deploy dual-write. 3) batched UPDATE ... WHERE encounter_id >= :lo AND encounter_id < :hi AND admission_source_code IS NULL, a few thousand rows per transaction. 4) ALTER TABLE encounter ADD CONSTRAINT encounter_admission_source_chk CHECK (admission_source_code IN (...)) NOT VALID; (ACCESS EXCLUSIVE, no scan) then ALTER TABLE encounter VALIDATE CONSTRAINT encounter_admission_source_chk; (SHARE UPDATE EXCLUSIVE, scans concurrently). 5) ADD CONSTRAINT ... CHECK (admission_source_code IS NOT NULL) NOT VALID; VALIDATE; ALTER COLUMN ... SET NOT NULL (PG 12+ skips the scan using the validated constraint); DROP the helper constraint. 6) CREATE INDEX CONCURRENTLY idx_encounter_facility_admit ON encounter (facility_id, admit_ts); outside any transaction block.

Verify it yourself

SELECT count(*) FROM encounter WHERE admission_source_code IS NULL returns 0 before SET NOT NULL is attempted.
SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'encounter'::regclass shows convalidated = true for both constraints.
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'encounter'::regclass shows indisvalid = true for the new index; false means the concurrent build failed and left a partial index.
Throughout the migration, pg_stat_activity shows no session with wait_event_type = 'Lock' held for more than one second, and the ADT consumer's write latency is unchanged.

Follow-up

CREATE INDEX CONCURRENTLY has been running for four hours and you need to cancel it. What state is the index left in, how do you detect it, and what do you run next?
Your backfill is at 40% and the replica is 90 seconds behind. What do you change, and what do you measure to decide the new batch size?
The code set gains a value two weeks later. Does your CHECK constraint or a lookup table with a foreign key make that change cheaper, and what does each cost on the write path?

Namespace source identifiers so a cross-facility join cannot collide

Easy · identity · composite keys · constraints

person_identity_link holds one row per (assigning_authority, source_person_id) per link version: link_id, enterprise_person_id, assigning_authority, source_person_id, match_score, link_status in ('auto_linked','manual_linked','potential_duplicate','unlinked','rejected'), version, superseded_by_link_id, decided_by, decided_at. Two facilities in one network both issued medical record number 004821, to different people. An existing extract joins on source_person_id alone and has been merging their charts. Give the constraint set that makes the bare join impossible to write by accident, then write the query that resolves one facility's (authority, MRN) to its current enterprise_person_id.

Approach

Name the root cause precisely: an MRN is unique only inside the authority that issued it, and a member ID only inside payer plus plan. The identifier is half a key; the namespace is the other half.
Make the composite the only addressable key. UNIQUE (assigning_authority, source_person_id, version) gives version history; a partial unique index on (assigning_authority, source_person_id) WHERE superseded_by_link_id IS NULL enforces one live decision per source record.
Remove the bare column as a join target from every consuming view: expose a view or function that takes both arguments, so a one-argument lookup is a compile-time error rather than a silent cross-patient merge.
Write the resolution query against the live version only, and filter link_status separately — 'unlinked' and 'rejected' are live rows that mean 'no enterprise identity', so they must not be confused with 'no row'.
State the detection query you would run before trusting any existing extract: group source_person_id across authorities and count distinct authorities, which surfaces every collision already in the data.

Follow-up

The spine stores enterprise_person_id directly on encounter and claim_line. What does a wrong merge cost you with that design, and what would you store instead to keep unmerge a supported operation?
A link is downgraded from auto_linked to potential_duplicate. Should the resolution query return the old enterprise identity, nothing, or an error, and who decides?
Your partial unique index blocks a second live row. How does the merge path insert the new version and supersede the old one without violating it mid-transaction?

How do you ensure accuracy and data integrity when handling large datasets?

Medium

Approach

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

Follow-up

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

What steps do you take to validate that your code meets all functional requirements?

Medium

Approach

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

Follow-up

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

Enrolment loading, bitemporal writes and retroactive fan-out

Hard · bitemporal · backpressure · invalidation · retroactive-correction

A weekly X12 enrolment file carries between tens of thousands and two million member rows and routinely terminates coverage retroactively — received on the fifth, effective the first. It writes coverage_span, whose effective_date and termination_date are business time and whose valid_from and valid_to are system time, alongside status and source_file_id. Two files covering overlapping populations can be in flight at once. Design the loader's backpressure, the bitemporal write, and the invalidation fan-out to the eligibility cache and to claims already adjudicated against the now-terminated coverage.

Approach

Never update a coverage_span row. A retroactive termination closes the current row by setting valid_to to now and inserts a new row with the same effective_date, the new termination_date, and valid_from set to now. Both questions then stay answerable: current truth for a past service date filters valid_to = 'infinity' and ranges on business time, while what the system believed on a past date filters valid_from <= that instant < valid_to.
Index for the two axes separately rather than hoping one structure serves both. A btree on (enterprise_person_id, effective_date DESC) partial on valid_to = 'infinity' serves the hot current-truth path; the as-of-system-time path is rare and analytical and can afford a wider scan. A single range index covering both usually makes the common query worse to make the rare one tidier.
Bound the loader instead of running it flat out: chunk into batches of a few thousand rows with a committed checkpoint offset per file, and pace on a downstream signal — replication lag or invalidation queue depth — rather than a fixed sleep. Two million rows written as fast as the database accepts them is how a weekly file becomes a replica-lag incident at 03:00, and the checkpoint is what makes a mid-file crash resumable instead of a full re-run.
Serialise per person rather than per file. Two overlapping files must not interleave versions for one member, so key the work queue on enterprise_person_id and let files proceed concurrently across different members; order within a person's partition by the file's assertion time, and keep source_file_id so a bad file is attributable and reversible as a set.
Fan the consequences out as work, not as a cache flush. Publish the changed enterprise_person_ids so the eligibility cache drops exactly those keys, and enqueue the claim identifiers whose service dates now fall outside coverage. Those claims need reversal and re-adjudication, which touches accumulators, so this is a throttled backlog with its own ordering rather than a synchronous cascade at load time.
Measure the blast radius before it lands: a dry-run pass counting rows whose termination precedes the file's receipt date, and the claims each would affect, turns 'we loaded the file' into a reviewed number. A file retroactively terminating forty thousand members is either a real event or a sender bug, and you want to know which before the reversals run.

Worked solution (45 min)

Write the close-and-insert pair for a retroactive termination, then write the two queries — current truth for a past service date, and belief as of a past instant — and run both against the result.
Write the chunked loader loop with its checkpoint and the downstream signal it paces on, and state the batch size and what it is derived from.
Define the per-person work queue key and show what happens when two overlapping files touch the same member.
Write the fan-out payload and the two consumers, and state the throttle on the claims reprocessing backlog.
Write the dry-run query that counts retroactively terminated members and the claims they affect.

Expected result: Retroactive changes written as close-and-insert across system time with no in-place update, separate indexes for the current-truth and as-of paths, a chunked and checkpointed loader paced on a downstream signal, per-person serialisation that lets overlapping files proceed concurrently, and a published-identifier fan-out feeding cache invalidation and a throttled claims reprocessing backlog.

Verify it yourself

After loading the correction, a query as of the day before it arrived still returns the original coverage.
Killing the loader mid-file and restarting resumes from the checkpoint without duplicating or skipping rows.
Two files touching one member produce a single linear version chain with no interleaving.
The eligibility cache stops returning covered for the affected service dates within one invalidation cycle, and the affected claims appear in the reprocessing queue.

Follow-up

The sender resends last week's file unchanged. What does the loader write, and how do you prove nothing changed?
One file terminates a coverage that a later file reinstates with the same effective date. What does the current-truth query return while both are mid-load?

Nightly adjudication throughput collapses with deadlocks on accumulators

Medium · deadlock · lock ordering · isolation levels

During the nightly claims batch, throughput collapses from 4,000 to about 300 claim lines a second for minutes at a time. pg_stat_database.deadlocks climbs, the PostgreSQL log carries deadlock detected with SQLSTATE 40P01, and the application retries the aborted transactions. Benefit application and the reversal handler both take SELECT ... FOR UPDATE on the member's deductible accumulator row and on the out-of-pocket accumulator row. Diagnose in order, then give a fix that removes the cycle by construction rather than by retrying faster.

Approach

Read the deadlock report before theorising. PostgreSQL logs both process IDs, the statement each was running and the relation and tuple each was waiting on, which names the two lock-acquisition orders directly. There is no need to guess which code paths collide or to reproduce it first.
Separate deadlock from ordinary contention. pg_stat_database.deadlocks counts cycles, while log_lock_waits reports waits longer than deadlock_timeout that never form a cycle. Collapsing throughput with few recorded deadlocks means a hot row — one family plan's shared accumulator — is serialising the batch, which has a different remedy from a cycle.
Account for the detector's cost. A cycle is only detected after deadlock_timeout, one second by default, so every deadlock burns at least that on both sides plus the retried work. Raising the timeout lengthens each stall; lowering it raises the frequency of the detector's checks. Neither is a fix, and both are common first answers.
Reject the two reflex answers explicitly. SERIALIZABLE does not prevent deadlocks in PostgreSQL — row-level lock cycles still occur and serialization failures with SQLSTATE 40001 are added on top — and a larger connection pool simply puts more transactions in contention for the same rows.
Remove the cycle by construction: make each member's benefit application take exactly one lock. Either fold both accumulators into a single row keyed (enterprise_person_id, plan_id, plan_year) and update it in one statement, or take pg_advisory_xact_lock over a hash of that key as the first statement of both paths. One lock cannot form a cycle with itself. Deterministic ordering via ORDER BY ... FOR UPDATE also works but depends on the chosen plan preserving that order, so it is a weaker guarantee; hash collisions in the advisory-lock variant only serialise two unrelated members, which is safe but costs throughput.
Then bound the transaction. No external call may sit between acquiring the lock and committing, or the row is held for a network round trip and the hot member serialises the batch regardless of which deadlock fix you chose.

Follow-up

A reversal arrives for a claim adjudicated under a plan design that has since changed. What amount does it subtract, where is that amount recorded, and is that still atomic under your single-lock design?
Remove the FOR UPDATE entirely and show the exact two-session interleaving that produces a lost update under READ COMMITTED, then say why a single UPDATE ... SET remaining = remaining - $1 does not have the same problem.

Suggested practice checklist

For a candidate senior enough that the loop turns on design and judgement rather than on whether the coding round gets finished. Five days build one system properly and then stress it; coding gets a single maintenance day, on the assumption that the risk at this level is an unexamined tradeoff rather than a missed algorithm.

Day 1 — Numbers before diagrams

Build your own reference card of the figures you will re-derive all week: bytes for a realistic record, requests per second implied by a given daily active count, and the storage that a year at a given write rate produces. Derive each one rather than copying it, because the derivation is what survives a follow-up.
Turn one product statement into capacity requirements. From ten million daily users at four writes and forty reads each, state the peak-to-average factor you are assuming and why, then produce peak write QPS, peak read QPS and a year of storage.
Write the two numbers whose order of magnitude changes the design, the read-to-write ratio and the working-set size against memory per node, and state the threshold at which each one flips your answer.

Deliverable: A one-page numbers card and one worked capacity estimate with every assumption written down.

Day 2 — One system, from requirements to schema

Spend the first ten minutes producing only functional requirements, non-functional targets with numbers attached, a p99 latency, a durability expectation, a consistency requirement, and an explicit out-of-scope list.
Define the interface before the boxes: the three or four endpoints, their parameters, what each returns, and which of them are idempotent.
Write the data model, then write the single access pattern that justifies it, and state what the schema would have to become if the dominant access pattern were the other one.

Deliverable: One design carried to endpoint-and-schema depth, with non-functional targets expressed as numbers and a written out-of-scope list.

Day 3 — The consistency you are actually buying

Write out what a client sees under asynchronous replication when its write commits on the leader and its next read is served by a lagging follower, then write the two fixes, pinning that session's reads to the leader for a bounded window or carrying a version token the replica must reach, and the cost of each.
Work the quorum arithmetic on paper for N of three with W and R of two, and separate what R + W > N does guarantee, that any read set intersects any write set, from what it does not: on its own it is not linearizability, and a sloppy quorum that accepts writes on nodes outside the preference list breaks even the intersection.
Take two storage choices with different defaults, a single-leader relational store committing synchronously and a quorum-replicated store that converges eventually, and write the specific product behaviour that would be wrong under each, rather than a general statement about which is stronger.

Deliverable: A page separating what quorum overlap guarantees from what it does not, with one concrete product misbehaviour attached to each gap.

Day 4 — Failure is the design

For one write path, work through the case where the client times out after the server has already committed, then design the idempotency key: who generates it, how long it is retained, and what the duplicate request returns.
Express the retry policy as parameters rather than as a word: maximum attempts, base delay, backoff factor, jitter, and which error classes are retried at all. Then state why retrying a non-idempotent write without a key is a correctness bug and not merely waste.
Compute the fan-out effect on tail latency. If a request waits on ten backends and each independently exceeds its p99 one percent of the time, the chance at least one is slow is 1 - 0.99^10, about ten percent. Then write why independence is the optimistic assumption and what correlates them in practice.
Name the backpressure mechanism for one queue or one dependency in the design, a bounded queue with shedding or a concurrency limit, and write what the caller is told when it engages.

Deliverable: One write path with an idempotency design, a parameterised retry policy, and a written tail-latency calculation with its assumption named.

Day 5 — Scaling the hot path

Choose cache-aside or write-through for one read path and write the staleness window each produces, then name the invalidation event and what the system does when that event is lost.
Design against the stampede: either coalesce requests so only one recomputes a missing key, or refresh early with jittered expiry, and write why identical TTLs on keys populated in the same moment produce a synchronised expiry and a thundering herd.
Shard one table by a key you choose, then answer the two questions that break the choice: which queries now require a scatter-gather, and what happens to the distribution when one tenant is a hundred times larger than the median.
Write the cost of adding a node under plain modulo placement, where nearly every key moves, against consistent hashing, where roughly one key in n+1 moves, and state what virtual nodes are for.

Deliverable: A caching and sharding decision for one path, each with its failure mode and its rebalancing cost written beside it.

Day 6 — Keep the coding hand in, at the bar that applies to you

Solve one medium problem in thirty minutes, then spend twenty more making it production-shaped: named invariants, validation at the boundary, and errors that distinguish a caller mistake from an internal fault.
Write the tests you would require of a colleague's version of that function: one for empty input, one for the boundary, and one for the case the implementation is most likely to get wrong.
Read a piece of your own code from six months ago and write the change you would ask for, phrased as you would actually phrase it in review.

Deliverable: One problem hardened to review standard, with its test list and one written review comment.

Day 7 — Defend it while being interrupted

Run a forty-five-minute design mock with an interviewer briefed to change a requirement halfway, a tenfold traffic increase or a new strict consistency requirement, and to push on one number you estimated.
Rehearse the two sentences a senior loop is listening for: naming the tradeoff you are choosing against and why, and saying what you would measure to learn that the choice was wrong.
Prepare the design you regret: a real decision, the constraint that produced it, what it cost, and what you changed afterwards.

Deliverable: Mock notes recording how the design changed under the new requirement, plus a written account of one regretted decision.

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.

Describe a time you had to troubleshoot a technical issue in an existing system.

Medium · behavioural and engineering judgement

Approach

State the situation in two sentences and spend the rest on the reasoning.
Give the blast radius: what could have broken, and what you measured.
Name the disagreement and how you resolved it with evidence.

Follow-up

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

Own an incident where the matcher linked two different people

Medium · incident response · identity resolution · blast radius · rollback

You shipped the change that moved the identity matcher's auto-link threshold from 0.94 to 0.88, to cut the manual review queue. Six hours later a clinician reports another person's results on a chart. person_identity_link has roughly 4,000 auto_linked rows since the deploy, and the longitudinal record service resolves patient reads through that table, so every bad link is already widening chart reads. Describe an incident of this shape that you owned: detection, what you stopped first, how you reversed links that must stay reversible, and the durable change afterwards.

Approach

The probe is whether you measure blast radius in the units the domain cares about. Open with the number of auto_linked rows written since the deploy, how many of those join two source records that disagree on a demographic the matcher did not weigh, and how many patient reads resolved through them — not with the root cause, which hides whether you could see the problem at all.
Be honest that the false-link rate is not a count you can query. There is no ground-truth column saying two source records are the same human, so the first defensible number is precision on an adjudicated sample with a stated sample size, and everything downstream of it is an estimate.
Separate stopping from fixing, and name both stop actions. Reverting the threshold halts new bad links within a deploy cycle and does nothing to the ones already written; any cached patient summary or materialised projection keyed on enterprise_person_id keeps serving the merged chart until it is invalidated.
Say what reversal costs on this schema: new person_identity_link rows at version+1 with link_status 'unlinked', and superseded_by_link_id set on the bad rows. No DELETE, because the question an incident review asks later is what the index believed at a specific minute, not what it believes now.
Escalate clinical exposure rather than data exposure. Enumerate the encounters and orders placed during the window against the affected enterprise ids, because someone may have acted on another person's result; that list, not the row count, is what goes to safety review.
Close on what made the threshold reviewable afterwards: a shadow run scoring the candidate threshold against live traffic without writing links, a precision target measured on a labelled set, and an alert on auto-link rate per hour so the next one is caught by a metric rather than by a clinician.

Follow-up

Two of the bad links were adjudicated and confirmed wrong, but a claim was already submitted under the merged identity. What do you do with that claim, and who decides?
Your shadow run shows the old threshold also produces false links, at a lower rate. Does that change whether this was an incident?
How would you have detected this in fifteen minutes instead of six hours, and what would that alert cost you in false positives during a normal registration peak?

Take a review disagreement about upserting corrected lab results

Medium · code review · versioning · unique constraints · idempotency

A colleague's result consumer writes observation_result with INSERT ... ON CONFLICT (filler_order_id) DO UPDATE SET value_numeric, result_status, issued_ts. Corrections arrive carrying the same filler order identifier as the original, and their fixture suite — one final result per order — passes. They argue the table stays bounded and reads need no version predicate, and you are the only objector. Reconstruct the review: the comment you wrote, how you demonstrated the defect without a full reproduction, the uniqueness key you proposed instead, and what you would have accepted.

Approach

The probe is whether you can make a correctness objection land on someone who has passing tests. Name what the upsert destroys in specifics — the prior value_numeric, its issued_ts and its result_status — so that no query can answer what was displayed before the correction arrived, which is the exact question an incident review or a legal hold asks.
Demonstrate inside their own suite rather than arguing. Add one message: same filler order and LOINC code, result_status 'corrected', a different value. Point at the row count staying constant and the original value becoming unselectable. A failing case in their fixture is more persuasive than a paragraph.
Lead with the sharper defect, which is the key itself. One accession fans out to many analytes, so filler_order_id is not unique even across originals; the unique index their ON CONFLICT requires cannot exist without collapsing a panel to a single row. Propose UNIQUE (filler_order_id, loinc_code, version), with the insert carrying supersedes_observation_id.
Deal with their read-simplicity objection honestly instead of dismissing it. With a backwards supersedes pointer, 'current' means 'no row supersedes this one', which is an anti-join; a real design therefore adds a current-version marker maintained in the same transaction as the insert, or a per-(order, analyte) pointer table. Versioning is not free and saying so is what makes the rest credible.
Name the second consequence, for consumers rather than for storage: an in-place update emits no state transition, so anything that already acted on the preliminary value never learns it changed. Versioned inserts produce that correction event as a by-product.
Close on conduct: what you wrote, whether you blocked the merge, who decided, and the smaller thing you would have accepted — shipping the versioned insert without the current-version marker and eating the anti-join until it shows up in p99.

Follow-up

The same corrected message is replayed a week later during an interface restart. Walk both designs through it.
How do you keep the current-version marker correct when two corrections for the same analyte are processed concurrently?
A result arrives with status 'entered_in_error'. Is that a new version, a deletion, or something else, and what does a chart show afterwards?