Interview guides · Alberta Health Services

Alberta Health Services Software Engineer Interview Questions & Guide 2026

Alberta Health Services software engineer prep on databases: indexes, query plans, and the anomalies an isolation level still permits.

The 60-second brief

A Software Engineer at Alberta Health Services (AHS) plays a pivotal role in maintaining and evolving the digital infrastructure that supports one of Canada’s largest integrated health systems. You are not just writing code; you are building and supporting tools that directly impact the efficiency of healthcare delivery, data management, and operational workflows across the province. Your work ensures that clinical and administrative systems remain robust, scalable, and secure.

Getting the code to run is the floor. What usually separates answers is the case checked without prompting: empty input, a single element, duplicate keys, or a value that overflows the integer type you chose.

Focus areas

Make HL7 and X12 ingestion idempotent under replay
Model bitemporal coverage and retroactive eligibility changes
Hold accumulator limits under concurrent claim adjudication

Overview

This role is unique due to the intersection of complex, large-scale enterprise environments and the critical nature of the healthcare domain. You will work within diverse, multidisciplinary teams to solve problems that span from server virtualization and data visualization to the integration of legacy and modern software stacks. Whether you are optimizing internal processes or supporting patient-facing data initiatives, your technical contributions are essential to the mission of providing high-quality, accessible health services to Albertans.

The Alberta Health Services interview process

Alberta Health Services candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

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

What to demonstrate

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

How to prepare

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

Formal Interviews

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

What to demonstrate

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

How to prepare

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

Panel Interviews

Coding rounds mostly set a floor. They decide whether you clear the bar, not where you land on the ladder. Level tends to come out of the design discussion and the ownership stories, so the question worth auditing beforehand is whether the scope you describe matches the scope of the job. Work that stops at your own service, or a story whose hard part was writing the code rather than getting several people to agree on an interface, reads a level below where you think you are interviewing, and that gap is usually resolved downwards.

What to demonstrate

Whether the largest thing you describe owning ran end to end — the decision, the migration path, the rollout, and what you did when it went wrong — or stopped at the change you merged
Whether design answers include what you would not build, what you would defer, and what you would measure before committing, rather than only what the boxes are
Whether a disagreement in a story was settled with something checkable — a benchmark, a prototype, a written proposal — instead of by seniority or by waiting it out
Whether you can say which calls you made alone and which you escalated, and why the line sat where it did

How to prepare

Write your largest piece of owned work as a timeline of decisions — who decided what, when, and what you did when the plan broke — then delete every sentence whose subject is "we" and see how much survives
Take one system you know well and drill the migration answer: how old and new paths run side by side under live traffic, how you compare their outputs, what the rollback is once writes are going to both, and which step you would not automate
Map each line of the ladder in the job posting to a specific thing you have done, find the line you cannot support, and prepare the closest evidence you have plus an honest account of the gap

Where candidates lose points

Assuming admission, discharge and transfer messages arrive in the order the events happened.

Interface engines route by message type across separate queues and retry independently, so a discharge can land before the admission it closes and an update can land before the registration it modifies. Ordering has to come from the sender's event timestamp plus a per-encounter sequence, and the consumer has to apply out-of-order and late-arriving events correctly rather than rejecting them, because rejection turns a recoverable ordering issue into permanent data loss that nobody notices until a report is short.

Caching an eligibility answer with a long time-to-live and without an as-of date.

Coverage terminates retroactively as a matter of routine: an enrolment file received on the fifth of the month can terminate coverage effective the first. A day-long cache means services are delivered against a 'covered' answer that was already false when it was served, and the denial arrives weeks later. The answer needs to be keyed on person, plan and service date, carry the date it was computed as of, and expire fast enough that the exposure window is a decision rather than an accident.

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.

Comparing floating-point values for equality, or holding money in them

Binary floating point cannot represent 0.1 exactly, so repeated addition drifts and an equality check fails on values that are mathematically equal. Store currency as integer minor units or a decimal type, and compare floats against a tolerance you chose for a stated reason.

Questions & practice

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?

Reconstruct what the chart displayed at five million past instants

Hard · external sort · as-of join · io bound

observation_result holds 200 million versions: observation_id, enterprise_person_id, filler_order_id, loinc_code, value_numeric, result_status, collected_ts, issued_ts, version, supersedes_observation_id. An incident review hands you 5 million queries of (enterprise_person_id, filler_order_id, loinc_code, as_of_instant). For each, return the version that was visible at that instant, meaning the one with the greatest issued_ts at or before it. Neither side fits in memory. The per-query scan is correct. Explain precisely why it is too slow, then give a plan with its complexity.

Approach

Name the clock before naming an algorithm. Visibility is issued_ts, the release time. collected_ts is when the specimen was drawn and can precede release by hours, so ordering on it reports a correction as visible long before anyone could have seen it. No amount of index work rescues the wrong column.
Be exact about why the naive plan fails, because the interviewer is testing whether you can tell arithmetic cost from I/O cost. Per query the chain scan is O(V_k) and the arithmetic is trivial, but 5 million independent lookups into a 200-million-row structure that does not fit in RAM is 5 million random reads. The job is bounded by seeks per query, not by comparisons, and buying a faster comparison changes nothing.
Convert random access into sequential access. Hash-partition both sides on the chain key (enterprise_person_id, filler_order_id, loinc_code) into P shards sized to fit memory, sort each shard's versions by (chain key, issued_ts) and its queries by (chain key, as_of_instant), and sweep the pair in lockstep. Total O((V + Q) log(V + Q)) with external sort, replacing Q seeks with two sequential passes.
Inside a chain the sweep is linear, not logarithmic, because queries are visited in ascending as_of order and the version pointer only moves forward: O(V_k + Q_k) per chain. Binary search per query is the better shape only when Q is small relative to V and the versions are already indexed and resident.
Return the empty answer as a distinct outcome. A query whose as_of precedes the first issued_ts means nothing was displayed, which is not the same as the earliest value, and is frequently the exact fact the review is chasing.
Return a version later marked entered_in_error if it was live at the instant asked about. Reconstructing the past means reporting what was on the screen, including what was wrong, and quietly substituting today's truth defeats the purpose of the exercise.

Follow-up

Read replicas lagged 40 seconds at the time. Does your answer describe what the clinician actually saw, and how would you bound the difference?
Serve the same question online for a single chart at a p99 under 50ms. What changes?
One partner changed its filler_order_id format mid-year, so the chain key is not stable. How does that appear in your output, and how do you detect it rather than returning empty answers?

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?

Bitemporal coverage table with retroactive termination queries

Easy · bitemporal · schema design · retroactive correction

You maintain coverage_span: coverage_id, enterprise_person_id, payer_id, plan_id, effective_date, termination_date (nullable, meaning open-ended, not unknown), status, valid_from timestamptz, valid_to timestamptz DEFAULT 'infinity'. An enrolment file received on 2026-03-05 terminates a member effective 2026-03-01, after a claim for service date 2026-03-03 was already adjudicated against the answer then in force. State your termination_date convention, write the two SELECTs — (a) is the person covered on 2026-03-03 as known now, (b) what the system believed on 2026-03-04 — and say precisely what the loader writes when the file lands.

Approach

Separate the two time axes explicitly before writing anything: effective_date/termination_date are business time (when benefit applied), valid_from/valid_to are system time (when this row was the system's belief). Every query names both, or it is ambiguous.
Pin the inclusivity convention, because the predicate depends on it. Enrolment files conventionally carry termination_date as the last covered day, so the business-time predicate is effective_date <= :svc AND (termination_date IS NULL OR termination_date >= :svc). If your feed uses an exclusive end, it becomes > :svc, and a half-open daterange column removes the ambiguity permanently.
Write the as-known-now query by pinning system time to the open version (valid_to = 'infinity') and letting business time float to the service date.
Write the as-of-belief query by pinning system time to an instant: valid_from <= :asof AND valid_to > :asof. Half-open on both ends is what makes 'exactly one row per key per instant' provable.
Describe the write as one guarded statement rather than two independent ones: a data-modifying CTE whose UPDATE stamps the open row's valid_to to now() (the only UPDATE the design permits — it stamps system time, it never edits business facts) and returns that row's keys, with the INSERT of the new version selecting FROM that CTE. Feeding the INSERT from the UPDATE's RETURNING is what makes a replayed file a no-op; an unconditional INSERT leaves the partial unique index to raise 23505 instead, which is a backstop rather than idempotency. The pre-correction row stays readable either way, which is what makes the already-adjudicated claim defensible.

Follow-up

The claim adjudicated on 2026-03-03 is now retroactively uncovered. What does reprocessing have to read — the belief at adjudication time or the current truth — and what does the remittance reversal reference?
Add the constraint that stops two active coverage versions from overlapping in business time for the same person and plan, without blocking the superseded history rows.
A later file reinstates coverage with the same effective_date. How many rows exist for that period afterwards, and which one does query (a) return?

Denormalise consent into a decision table with a stated staleness bound

Hard · denormalisation · cache invalidation · access control

Access governance answers 'may this requester read this person's data for this purpose' on every identified read — tens of thousands per second at a 5ms p99 — from consent_directive: consent_id, enterprise_person_id, version, scope, purpose_of_use text[], grantee_org_id (NULL meaning all organisations), permit boolean, effective_ts, expires_ts, revoked_ts, status. Evaluating the normalised version history per read misses the budget. Design the denormalised decision store and its key, state the revocation staleness bound in seconds and the mechanism that actually enforces it, and say which normalised rows remain the system of record and why.

Approach

Derive the shape from the read, not from the source table. The read is a point lookup on (person, grantee organisation, purpose), so fan the purpose_of_use array out into one row per purpose and make those three columns the primary key. A GIN index on the array would serve containment queries nobody issues.
Handle the wildcard concretely. grantee_org_id NULL means 'all organisations', and NULL never equals anything, so a nullable column in the key breaks both the primary key and the equality lookup. Store a sentinel org id of 0 for the wildcard and resolve specific-before-wildcard in the lookup, or the wildcard row is silently unreachable.
Make refusal a row, not an absence. An explicit permit = false must outrank a permit for the same key, so carry a precedence rank (specific grantee beats wildcard, refusal beats permit at equal specificity) and resolve with ORDER BY rank LIMIT 1. Default deny when nothing matches, so a rebuild failure fails closed.
Separate the two staleness mechanisms and be precise about which one is the bound. The in-process decision cache has a TTL; a revocation also publishes an invalidation. The publish shortens the typical case to milliseconds, but it can be lost, so the guaranteed bound is the TTL alone — quote that number, for example 30 seconds, and justify it against the cache hit rate you need to hold 5ms p99.
Evaluate expires_ts at read time against the request clock rather than baking the decision. A cached permit whose expires_ts has passed is wrong regardless of invalidation, because no event fires when a timestamp simply goes by.
Keep consent_directive as the system of record: audit must reconstruct the directive in force at any past instant, the decision table holds only current state, and a corrupted decision table must be rebuildable by replaying the directives. Bound the write amplification by keeping grantees at organisation granularity and purposes a closed code set, so one directive expands to a known small number of rows rather than an unbounded cross product.

Worked solution (40 min)

Write the decision table DDL with the three-column primary key, the sentinel wildcard, the precedence rank and a monotonic decision_version.
Write the lookup query: equality on the key with the wildcard row unioned in, ORDER BY rank, LIMIT 1, and an expires_ts predicate evaluated against the request timestamp.
Write the projection that turns one consent_directive version into its decision rows, and compute the expansion factor for a directive covering four purposes and one organisation.
State the TTL, then justify it: at N reads per second and M people, compute the cache hit rate the TTL yields and check it against the 5ms p99 budget.
Write the failure narrative for a lost invalidation and confirm the TTL, not the bus, is what caps exposure.
Write the rebuild procedure from consent_directive and the assertion that proves the rebuild is complete before it is swapped in.

Expected result: consent_decision (enterprise_person_id bigint, grantee_org_id int NOT NULL DEFAULT 0, purpose_of_use text, scope text, permit boolean NOT NULL, precedence smallint NOT NULL, expires_ts timestamptz NULL, decision_version bigint NOT NULL, computed_at timestamptz NOT NULL, PRIMARY KEY (enterprise_person_id, grantee_org_id, purpose_of_use)), where grantee_org_id = 0 is the wildcard. Lookup: SELECT permit FROM consent_decision WHERE enterprise_person_id = :p AND purpose_of_use = :pou AND grantee_org_id IN (:org, 0) AND (expires_ts IS NULL OR expires_ts > :now) ORDER BY precedence LIMIT 1; no row means deny. The guaranteed revocation bound is the local cache TTL — 30 seconds — because the invalidation publish is best-effort and a lost message must not extend exposure. consent_directive stays the system of record: it is what audit replays to reconstruct the directive in force at a past instant, and what the decision table is rebuilt from.

Verify it yourself

A person with an explicit refusal for one organisation and a wildcard permit resolves to deny for that organisation and permit for every other — verified by two lookups against one fixture.
Truncating consent_decision yields deny for every request rather than permit, confirming the store fails closed.
With the invalidation bus stopped, a revoked directive stops being served no later than TTL seconds after the revocation commits — measured, not assumed.
A decision row whose expires_ts has passed is never returned even when the cache entry is still within its TTL.

Follow-up

Break-glass must always succeed and must be unmistakably marked. Where does it sit relative to this lookup, and what stops it from becoming the quiet default path?
The invalidation bus is down for thirty minutes. Walk through what a revoked person's data exposure looks like minute by minute, and what the backlog does when the bus returns.
Where does the access audit record get written so that an audit-store outage degrades neither the log's completeness nor the read's availability?

What technologies are you most comfortable using in your daily work?

Medium

Approach

Work from the requirement backwards to the design.
Clarify what is being asked and what a complete answer contains.
State your assumptions explicitly before working the problem.

Follow-up

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

Why did you choose specific technology stacks over others for your previous projects?

Medium

Approach

State your assumptions explicitly before working the problem.
Clarify what is being asked and what a complete answer contains.
Work from the requirement backwards to the design.

Follow-up

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

Benefit accumulators under concurrent claim adjudication

Medium · isolation · lock-contention · partitioning · retries

Adjudication applies benefit by reading a member's remaining deductible and out-of-pocket accumulators, computing patient responsibility, then writing both the new accumulator values and the claim_line amounts (allowed_amount_cents, paid_amount_cents, patient_responsibility_cents, all bigint minor units). Several lines for one member adjudicate concurrently out of a batch of a few million lines a day, alongside a real-time path. On PostgreSQL at READ COMMITTED, exhibit the interleaving that overshoots the plan limit, then give three mechanisms that prevent it and the throughput each one costs.

Approach

Exhibit the anomaly with numbers. Transactions A and B each SELECT remaining_deductible = 40000 cents, each compute an application of 30000, each UPDATE to 10000, and both commit: 60000 applied against a 40000 limit. The lost update lives across the statement boundary — READ COMMITTED does re-evaluate a blocking UPDATE's own predicate against the newly committed row, but it does nothing about a value the application read in an earlier statement.
Mechanism one, SELECT ... FOR UPDATE on the accumulator row at the top of the transaction: correct, simple, and it serialises that one member's lines. The cost is a held row lock for the transaction's lifetime, so contract and pricing lookups must complete before the lock is taken — a lock held across a call to an external system is how one slow partner stalls a queue.
Mechanism two, SERIALIZABLE with a bounded retry on SQLSTATE 40001: no explicit locking, but the abort rate climbs with contention and each retry redoes the whole computation, so effective throughput falls as contention rises rather than degrading gently. The retry is only safe if the transaction is idempotent or can recognise its own partial work by a claim-version key.
Mechanism three, partition the work queue by enterprise_person_id so one member is handled by exactly one writer: contention disappears instead of being resolved, at the cost of a rebalancing story and a real-time request queueing behind the batch for a hot member.
Handle reversals, where all three are equally exposed: the amount to subtract is what was applied at the time under the plan design then in force, not what recomputation today would produce. Record the applied amount on the claim version and subtract that stored value.
Choose and justify for this shape: batch dominates and members are naturally independent, so partition by member for the batch path while the real-time path takes FOR UPDATE on the same row, since both must respect one counter.

Worked solution (30 min)

Open two psql sessions and reproduce the lost update against a real accumulator row, recording the exact statement order.
Rewrite the path three ways — FOR UPDATE, SERIALIZABLE with retry, per-member partition — and run the same two-session interleaving against each.
Measure or estimate throughput per member for each variant and write one paragraph on where each degrades.
Write the reversal path and state which stored value it subtracts and why recomputation is wrong.
Mark every point in the transaction where an external call would extend a held lock.

Expected result: A concrete lost-update interleaving under READ COMMITTED, three mechanisms with their distinct costs — held-lock duration, retry amplification under contention, per-member throughput ceiling — a reversal that subtracts the recorded applied amount rather than a recomputed one, and a stated choice with its reasoning.

Verify it yourself

The lost update reproduces in two real sessions, not only on paper.
Under each of the three mechanisms, a hundred concurrent applications against a 40000-cent limit leave the accumulator at exactly 40000 and never above.
A retried transaction under SERIALIZABLE applies the benefit once, verified by the claim version rather than by the accumulator alone.
No transaction in any variant holds a lock across a network call.

Follow-up

Eligibility changes retroactively, so a claim adjudicated in March should have denied. What must be recomputed, in what order, and what happens to the accumulators in between?
A family deductible accumulates across several members' claims simultaneously. What is the lock now, and where is the new deadlock?

Duplicate results appear only in the hour after interface restarts

Hard · race condition · idempotency · read committed

About one in 40,000 ingested results produces a duplicate observation_result row. It happens only in the hour after an interface engine restarts and never reproduces in tests. The consumer checks a ledger table for the idempotency key, then writes the observation, then inserts the ledger row in a second transaction. The ledger has a unique index on the key, and the losing insert logs duplicate, ignoring. Adding a debug log between the check and the write made duplicates more frequent. Diagnose and fix.

Approach

Run the query that splits the hypotheses before anything else: for one duplicate pair, compare their idempotency keys. Identical keys mean the key is right and mutual exclusion is broken. Different keys mean the key itself is wrong — a sender reusing or rotating control identifiers — which is a different bug with a different fix. Most time lost on this class of problem is lost by skipping this step.
Measure the gap between the pair. created_at deltas in the tens of milliseconds, attributed to two different worker identifiers, mean two workers processed the same replayed message concurrently. Deltas of minutes or hours mean the ledger row was never durably written — rolled back, or written in a transaction that later aborted — and a later replay legitimately re-processed the message.
Read the transaction boundaries rather than the isolation level. The check, the clinical write and the ledger insert span two transactions, so the window between the check and the ledger insert is unprotected at every isolation level, READ COMMITTED and SERIALIZABLE alike. The unique index protected the ledger; it never protected the observation, which had already committed by the time the conflict was detected.
Explain why instrumentation moved it. The debug log widened the window between the check and the ledger insert, so more concurrent pairs landed inside it. Deleting the log narrows the window and hides the defect without fixing it — the same reason a single-threaded test suite is green, and the reason the incident correlates with restarts, when the broker redelivers a window of messages at once.
Fix by turning check-then-act into one atomic conditional write: in a single transaction, INSERT INTO ingest_ledger (idem_key, ...) VALUES (...) ON CONFLICT (idem_key) DO NOTHING RETURNING idem_key, and if no row comes back, do nothing further. The observation insert belongs in that same transaction. Under READ COMMITTED the second inserter blocks on the unique index until the first commits and then sees the conflict, so the index — not the isolation level — supplies the mutual exclusion.
Prove it with a deliberate concurrency test: two sessions released by a barrier on the same key, several thousand rounds, asserting exactly one observation row each time, then replay a captured restart window and diff row counts against a clean run.

Follow-up

The clinical write must go to a different store than the ledger. How do you keep this atomic without a distributed transaction, and what does the outbox look like?
The sender's control ID counter rolls over and begins reusing values. What breaks in your key, and what production signal would tell you it happened rather than a customer telling you?

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.

The bug you decided not to fix

Engineers over-index on what they repaired. A stronger answer covers something you knowingly left broken: the alert you tuned down, the data inconsistency you documented instead of chasing, the cleanup you deferred past two quarters. Give the reasoning and the condition that would have reopened it, so it reads as a decision and not as neglect.

Describe a situation where you dealt with diverse employees to achieve a goal.

Medium · behavioural and engineering judgement

Approach

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

Follow-up

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

How do you handle conflict within a team setting?

Medium · behavioural and engineering judgement

Approach

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

Follow-up

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

What is your experience with Tableau and data visualization tools?

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
State the situation in two sentences and spend the rest on the reasoning.
Close with what you would do differently, concretely.

Follow-up

What did you decide not to do, and why?
What would you do differently if you ran that again?

What is your experience with server virtualization?

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
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.

Follow-up

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