Interview guides · Apogee Engineering

Apogee Engineering Software Engineer Interview Questions & Guide 2026

Apogee Engineering software engineer prep on databases: indexes, query plans, and the anomalies an isolation level still permits.

The 60-second brief

As a Software Engineer at Apogee Engineering, you are at the intersection of complex systems engineering and cutting-edge software development. This role is critical to the company’s mission of delivering advanced technical solutions, particularly in defense, aerospace, and specialized research environments. You will be responsible for building, maintaining, and integrating software that powers sophisticated testbeds, models, and emerging technologies.

State every complexity claim with the assumption sitting under it. Hash lookup is O(1) on average and only for a hash that spreads your actual keys; comparison-based sorting cannot beat n log n, though counting or radix sort can when the keys are bounded integers.

Focus areas

Trace a symptom to a mechanism under load
Detect concurrent edits instead of losing writes
Paginate large result sets with keyset cursors

Overview

This is not a traditional, generic software role. You will often work on high-stakes projects, such as Model-Based Systems Engineering (MBSE), SysML architecture, or the integration of emerging technologies into existing systems. Your contributions directly influence how Apogee Engineering supports its clients in solving complex, large-scale problems. Expect to work in environments that value technical rigor and a deep understanding of the underlying system architecture as much as clean, functional code.

The Apogee Engineering interview process

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

Initial Screening

The title covers product work, platform work, infrastructure, mobile and frontend, and those are different jobs with different loops behind them. A screening call is the cheapest place to find out which one the seat is, and asking reads as experienced rather than fussy. The questions that separate them: what the team is on call for, what the last three projects were, and whether any round happens inside an existing repository instead of a blank file. Then say which of that you have done and which you have not. Claiming the whole posting is the fastest way to be found out one round later.

What to demonstrate

Whether you can locate your experience inside one flavour of the role honestly instead of claiming the entire requirements list
Whether you name what you have not done, which an experienced screener reads as a level signal and can plan the loop around
Whether what you want next matches what the seat is: someone who wants greenfield work landing on a team that mostly operates an existing system is a hire that leaves within the year

How to prepare

Mark every line of the posting as done, adjacent or new, and write one sentence for each adjacent line naming the closest thing you actually built
Split your last two years into rough percentages across feature work, operating and debugging live systems, and design or review, so a question about scope gets numbers rather than adjectives
Bring three questions that discriminate between seats: what the team is paged for, how much of the work is changing existing code versus standing up something new, and what shipped in the last quarter

Technical Assessment

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

Salary Discussion

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

Where candidates lose points

Letting a slow dependency consume unbounded concurrency

The failure that takes a service down is usually not an error but a delay. A dependency answering in thirty seconds instead of fifty milliseconds holds each request's worker or connection six hundred times longer, and since required concurrency is arrival rate times latency, a fleet sized for sixty in-flight requests now needs thirty-six thousand to sustain the same rate - so it queues, and requests whose clients have already abandoned them still occupy resources. Retries make it precisely worse: a policy of three attempts triples the load on a dependency at the exact moment it is least able to serve, which is how one slow dependency becomes an outage of everything sharing that pool. Containment is four specific things - a timeout on every outbound call shorter than the caller's remaining budget, a bounded pool per dependency so one cannot starve the others, backoff with full jitter rather than a fixed delay so retries do not resynchronise, and a circuit that stops sending once the failure rate makes an attempt pointless.

Paginating with LIMIT/OFFSET over a set that changes while the client is reading it

OFFSET n makes the database produce and discard n rows before returning anything, so the cost of a page grows with its depth rather than with its size and page 500 costs five hundred pages of work. The correctness problem is worse than the cost: if a row is inserted or reordered between two page fetches, rows shift across the offset boundary and are either skipped entirely or returned twice, and neither outcome leaves any trace in the response for the client to detect. Keyset pagination - WHERE (sort_key, id) < ($last_sort_key, $last_id) ORDER BY sort_key DESC, id DESC LIMIT n, backed by an index in exactly that order - reads only the rows it returns and is stable against concurrent inserts. It requires the tie-break column: a timestamp is not unique, and duplicate sort keys straddling a page boundary reintroduce the skip it was adopted to remove.

Sorting when the problem never required a total order

Match the algorithm to the guarantee actually needed: the top k comes from a size-k heap in O(n log k) time and O(k) space, distinctness needs a set rather than an ordering, and a small bounded integer key range admits a linear counting pass. A full O(n log n) sort is the right default only when you genuinely need everything in order.

Answering a debugging question with a guess instead of a bisection

Give a procedure that halves the search space at each step: confirm the symptom reproduces, establish the last known-good version, input or timestamp, then bisect over commits, over the data, or over the layers of the request path. A plausible cause with no way to confirm it is the same move whether it happens to be right or wrong, which is why it scores nothing.

Questions & practice

Canonicalise a request body into a stable idempotency fingerprint

Medium · parsing · canonicalisation · hashing

idempotency_key.request_fingerprint is a SHA-256 over the method, path and canonicalised body, and a retry whose fingerprint differs must be rejected with 422 rather than served the stored response. Write the canonicaliser. Bodies are JSON up to 256 KB nested at most 32 levels; clients vary key order, whitespace and unicode escaping, and some send 64-bit ids as JSON numbers. Produce a deterministic byte string such that semantically identical bodies match and any semantic difference does not. State your complexity and name two normalisations you refuse to perform.

Approach

Parse once into a tree, then re-serialise under fixed rules: object keys sorted, array order preserved, one escaping convention, no insignificant whitespace. Parsing is O(n) and sorting keys is O(k log k) per object, so O(n log n) overall with O(depth) stack, and the 32-level cap is enforced during parsing because hostile nesting is how a canonicaliser becomes a stack overflow.
Sort keys by their UTF-8 bytes and say why the obvious implementation is wrong in some runtimes: a default string comparison that orders by UTF-16 code units places surrogate pairs, meaning code points from U+10000 up, below U+E000 to U+FFFF, which is not UTF-8 byte order, so two services written in different languages disagree on the same document.
Do not re-encode numbers through a double. IEEE-754 binary64 represents integers exactly only up to 2^53, so normalising a 19-digit id through a float changes it, and 1 against 1.0 cannot be reconciled without deciding whether they are the same value. Preserve the literal token, and require ids as strings at the API boundary if you want them comparable.
Reject duplicate keys rather than picking one. JSON permits them and parsers disagree, most keeping the last, so any choice you make ties the fingerprint to a parser detail that the code handling the request does not necessarily share.
Frame the hash preimage so concatenation cannot collide: delimit or length-prefix the method, path and body, otherwise one request's fields can be rearranged into another request with the same byte stream and the same fingerprint.
Name the refusals and their consequence: no case folding, no dropping of null-valued keys, no Unicode normalisation. Each makes two different requests fingerprint alike, and the resulting failure is the worst one this table has, since the second request is answered with the first one's stored response and its effect never happens.

Worked solution (25 min)

Write the serialiser: recursive emit with a depth counter, objects sorted by UTF-8 key bytes, arrays in order, strings escaped by one fixed rule, numbers emitted as their original token.
Run it over three bodies: the same object with keys reordered, the same object with \u0041 written as A, and one with a nested array reversed. The first two must produce identical bytes and the third must not.
Take the id 9007199254740993, round-trip it through a double, show it returns as 9007199254740992, then state the rule that prevents this.
Define the hash preimage explicitly with its delimiters, and construct a pair of (path, body) inputs that would collide without them.

Expected result: A canonicaliser that is O(n log n) in body size with an enforced depth cap, sorts keys by UTF-8 byte order, preserves array order, keeps number literals verbatim, rejects duplicate keys, and feeds a delimited preimage to SHA-256, together with a stated list of normalisations deliberately not performed and the failure each would cause.

Verify it yourself

Key reordering and whitespace changes produce identical fingerprints while reordering an array does not.
A body containing an integer above 2^53 fingerprints identically after a parse and re-serialise round trip.
Two requests differing only in a field the endpoint ignores produce different fingerprints, and you can say why that is the safe direction to err.

Follow-up

A client sends the same logical request with an extra field your API ignores. Same key, different fingerprint, so you return 422. Is that the right answer?
Where does the fingerprint get computed relative to request decompression and the body-size limit?
The endpoint takes 1,000 requests per second with 256 KB bodies. What does hashing cost, and does it belong at the edge or in the core service?

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

Hard · reconciliation · range hashing · throttling

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

Approach

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

Follow-up

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

Find overlapping job attempts and peak concurrency from lease records

Medium · sweep line · intervals · leases

A day of job_run history yields about 50,000,000 attempt records: (job_run_id, job_type, attempt, started_at, finished_at which is NULL when the worker died, lease_expires_at). Leases expire on a clock, so a job that outran its lease ran twice. Produce (a) every job_run_id whose attempts overlapped in wall-clock time and (b) the peak number of simultaneously running attempts per job_type with the minute it occurred. Target O(n log n). State how you treat a NULL finished_at and what clock skew does to your answer.

Approach

Define the interval before sorting anything: an attempt occupies [started_at, COALESCE(finished_at, lease_expires_at)). finished_at is observed and lease_expires_at is only a promise, so every attempt without a finish contributes an estimate and the whole result is a lower bound on overlap rather than an exact count.
For peak concurrency, sweep: emit 2n endpoints, sort by (timestamp, kind) with ends ordered before starts at equal timestamps, then walk the sequence maintaining a counter per job_type and record each type's maximum with its timestamp. O(n log n) dominated by the sort, O(n) space, or O(1) extra if the sort is external and the walk streams.
For overlap detection, do not compare attempts pairwise. A single global sort by (job_run_id, started_at) gives both the grouping and the order; within a group, keep the maximum end seen so far and report an overlap exactly when the next start is less than that running maximum, which is one linear pass after the sort.
Half-open intervals matter and are easy to get wrong: with closed intervals an attempt ending at the same millisecond another begins reads as concurrency two, and across 50,000,000 records that artefact swamps the real signal.
State the clock caveat: started_at and finished_at are written by different workers, so under skew of a few hundred milliseconds an apparent overlap shorter than that bound is not evidence. Filter reported overlaps by a minimum duration, or prefer timestamps written by whichever component heartbeats the lease.
Scale the sort rather than assuming it fits: the sweep emits two endpoints per attempt, so 50,000,000 records become 100,000,000 endpoints, and at roughly 24 bytes each, an 8-byte timestamp plus a 4-byte job_type plus a kind flag padded to alignment, that is about 2.4 GB of sort keys before any scratch space. Either push the ordering into the database behind an index on (job_type, started_at) or run an external merge sort in chunks; the overlap pass sorts n records rather than 2n, so it is the cheaper of the two.

Follow-up

A handler is not idempotent and you have found 400 overlapping jobs. Which of them actually caused damage, and what would you query to find out?
Peak concurrency for one job_type is 4 against a configured cap of 4. Is the cap working, or is the data hiding attempts that never started?
How would you compute both answers incrementally as records arrive rather than in a daily batch?

Write the update path that detects a concurrent edit

Medium · optimistic concurrency · transactional outbox · audit history

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

Approach

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

Worked solution (25 min)

Write the three statements plus the rowcount branch and confirm they sit in one BEGIN/COMMIT with no outbound call between them.
Run two clients that both read version 7 and apply their updates 5 ms apart; assert one 200 and one 409.
Assert resource.version = 8, exactly one resource_revision row at version 8, and one outbox_event row at aggregate_version 8.
Repeat at REPEATABLE READ and record the different failure shape (SQLSTATE 40001) the handler must also map to 409.
Delete the version predicate and re-run: both writes commit and the first edit disappears with no error raised anywhere.

Expected result: Exactly one of the two commits. version = 8 with one revision row and one outbox row at version 8, the loser gets 409 carrying version 8, and no state exists in which the resource moved but the revision or the outbox row is missing.

Verify it yourself

COUNT(*) of resource_revision for that resource equals resource.version, with no gap in the version sequence.
Kill the process between the UPDATE and the outbox INSERT — the transaction rolls back and no event describes a change that never happened.
Under a 50-way race, resource.version equals the number of 2xx responses rather than the number of requests.

Follow-up

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

Stop tag and share joins from fanning out a page

Medium · join fan-out · semi-joins · many-to-many

resource_tag is (resource_id, tag_id) with PK (resource_id, tag_id); resource_share is (resource_id, shared_with_user_id, permission). The tagged-and-shared listing inner-joins resource to both, filters tenant_id, tag_id = ANY($2) and shared_with_user_id = $3, orders by updated_at DESC and takes 50. Pages come back with fewer than 50 distinct resources and the total in the header is far too high. Explain the row multiplication, rewrite both the page query and the count query so each is correct, and name the index each one needs. PostgreSQL 16.

Approach

Do the arithmetic against the predicates that are actually there. An inner join emits one row per matching child row, and both joins are filtered: tag_id = ANY($2) admits only the requested tags, shared_with_user_id = $3 admits one user's share rows. So a resource holding three of the requested tags and shared with $3 once yields three rows, not one — the multiplier is its count of matching tags times its share rows for that single user, and that second factor is 1 unless the table admits duplicate (resource_id, shared_with_user_id) pairs. LIMIT 50 then limits rows rather than resources, and COUNT(*) counts pairs — the header is the product, not the population.
Reject DISTINCT as the fix. It deduplicates after the product has been built, so the planner must materialise and sort the fanned-out set before the LIMIT can apply, and it leaves any SUM or AVG in the same select list wrong.
Rewrite both filters as semi-joins, keeping resource as the only row source: AND EXISTS (SELECT 1 FROM resource_tag rt WHERE rt.resource_id = r.resource_id AND rt.tag_id = ANY($2)) and the same shape against resource_share. A semi-join stops at the first match per resource and preserves the driving index order, so ORDER BY updated_at DESC, resource_id DESC LIMIT 50 still stops after 50 rows.
Count with the same predicates and no join at all: SELECT count(*) FROM resource r WHERE r.tenant_id = $1 AND r.status = 'active' AND EXISTS (...) AND EXISTS (...). Nothing multiplies a resource, so the number is the population.
Attach the tags for display after the page has been cut — LEFT JOIN LATERAL (SELECT array_agg(rt.tag_id) FROM resource_tag rt WHERE rt.resource_id = p.resource_id) ON TRUE over the 50 returned rows. Aggregate over the page, never over the tenant.
Index both directions and say which query each serves: PK (resource_id, tag_id) serves the lateral lookup, (tag_id, resource_id) serves the EXISTS probe by tag, and resource_share needs (shared_with_user_id, resource_id) for the same reason. An index covering one direction only leaves the other as a scan.

Follow-up

The filter changes from 'any of these tags' to 'all of these tags'. Rewrite it and state what it costs relative to the ANY form.
A resource can be shared with the same user twice under different permissions. Does your count change, and should it?
Where does the correct total come from when the tenant holds 4M resources and the header must not cost 200 ms?

How have your previous roles prepared you for systems-heavy software development?

Medium

Approach

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

Follow-up

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

What was your last position?

Medium

Approach

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

Follow-up

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

Can you describe a project where you had to integrate software into a larger engineering architecture?

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?

Design the async export contract a client can resume safely

Hard · async operations · idempotency · leases

A tenant asks for a CSV of every resource. The work runs for minutes on the worker fleet through a job_run row carrying a lease, an attempt count and a dedupe_key, far past the edge's 400 ms budget. Callers are a browser that polls and a script that walks away and checks later. Specify what the submit call returns, the operation resource and its states, how a duplicate submit is handled, how a client learns about completion, what cancellation means given that a lease can expire mid-run, and how the result is fetched and when it expires.

Approach

Split the API in two. Submit returns 202 with an operation id and a location to poll, and never blocks on the work. The operation is a real resource with its own lifecycle - queued, running, succeeded, failed, cancelled - plus attempt, a monotonic progress figure, and a terminal error drawn from the same code taxonomy the synchronous endpoints use, so a client needs one error vocabulary rather than two.
Deduplicate at submit using job_run.dedupe_key, unique over (job_type, dedupe_key) while status is 'queued' or 'running': a repeat submit of the same logical export returns 200 with the existing operation instead of 202 with a new one, and the partial index deliberately permits a legitimate re-run once the first has finished. Pair it with the request's idempotency key so an HTTP-level retry of the submit is exact rather than merely similar.
Tell the poller how to poll: Retry-After on the polling response, a minimum interval enforced at the edge, and a documented maximum lifetime after which an operation is reaped. Polling is the contract of record; the webhook is the fast path, and both must lead to the same terminal state, so a client that receives the completion event and then polls anyway sees no contradiction.
Be exact about cancellation. A cancel request records intent; it cannot stop work already executing. The handler reads the flag at checkpoints, and because a lease expires on a clock that cannot distinguish a dead worker from a slow one, a second copy may start after the cancel was recorded - so the handler re-reads the flag immediately after claiming the lease. 'cancelled' becomes terminal only when no lease is outstanding; reporting it earlier shows a client a stopped job while a worker is still writing output.
Make the handler safe to run twice, because the lease guarantees that it will be. Write output to a deterministic object key derived from the operation id so a second copy overwrites its own work instead of appending a second file, and record completion with a conditional update that only the copy holding the current lease can win.
Treat result fetch as a separate authorised read: a short-lived signed URL, the tenant checked when it is issued rather than only when the file was produced, and a documented retention after which the operation remains terminal but the bytes are gone - a state the client must be able to tell apart from a failure.

Worked solution (40 min)

Write the submit request and its two possible responses, 202 for new and 200 for a duplicate, and the dedupe_key construction.
Draw the operation state machine, marking which transitions a client may observe and which are terminal.
Write the cancellation sequence across a lease expiry, showing where the second copy reads the flag.
Define the output key, the completion update's predicate, and why both are needed for a double run.
Specify result fetch: URL lifetime, authorisation point, retention, and the distinct response once the bytes are gone.

Expected result: A 202-plus-operation contract with deduplicated submits, a documented poll interval alongside an optional completion webhook, cancellation defined as recorded intent with a terminal state gated on lease ownership, an idempotent handler writing to a deterministic key, and a separately authorised expiring result.

Verify it yourself

Submitting the same export twice while the first is running produces one job_run row and one operation id.
Killing a worker mid-export and letting the lease expire yields one output file, not two, and one terminal transition.
A client that receives the completion webhook and then polls sees the same terminal state and the same result location.

Follow-up

An operation has said 'running' for 40 minutes and the worker is gone. What does the client see, and which columns in job_run decide that?
Two tenants each submit 50 exports at once. What in this contract stops one of them delaying the other?
The customer wants the export emailed instead. What changes, and what becomes harder to make exactly-once?

Every query on one table stalls for forty seconds mid-deploy

Medium · lock contention · ddl · migrations · postgres

During a release on PostgreSQL, every query touching resource times out for about 40 seconds and then recovers with no intervention. The release ran one migration, ALTER TABLE resource ADD COLUMN archived_reason TEXT, and the migration log shows it completing in 6 ms. Unrelated tables showed no change in error rate. Explain how a 6 ms statement caused a 40-second stall, give the ordered checks you would run on a live system to confirm it, and give the migration procedure that prevents a repeat.

Approach

Separate the statement's duration from the lock's duration. ADD COLUMN with no default is a catalogue-only change and genuinely runs in milliseconds, but it requires ACCESS EXCLUSIVE, and it cannot acquire that until every transaction already touching the table has finished.
Account for the queueing, which is the part that surprises people. A lock request that is waiting blocks later requests for conflicting modes behind it rather than letting them overtake, so one long-open transaction holds the DDL and the DDL holds all the traffic. The stall length is set by the longest open transaction, not by the size of the change.
Confirm on a live system in this order: pg_stat_activity for that table ordered by xact_start, looking for the oldest transaction and specifically for state = idle in transaction; then pg_locks where granted = false to find the waiter; then join them on pid to name blocker and blocked. pg_blocking_pids() does that join for you and is the fastest single call.
Prevent rather than merely time it better. Set lock_timeout to a second or two on the migration session so the DDL abandons the queue after a bounded wait and is retried, instead of holding it for as long as the oldest transaction lives. Be exact about what that buys: queries arriving during the wait still queue behind the pending ACCESS EXCLUSIVE request, so each attempt costs them up to one lock_timeout of added latency. The outage goes from 40 seconds to about one second per attempt, not to zero. Also run migrations away from deploy-time peaks, and put a statement timeout and an idle-in-transaction timeout on the analytics role that opens the long transactions.
Know the lock each change takes, since the mitigation differs by change. A column with a non-volatile default is a metadata-only change from PostgreSQL 11 and still needs the brief ACCESS EXCLUSIVE; an index needs CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and leaves an INVALID index to drop if it fails; a check or foreign key is added NOT VALID and then VALIDATE CONSTRAINT as a separate statement under a weaker lock.

Follow-up

The same release also wants NOT NULL on that column. What is the sequence that gets there without a long lock?
Your lock_timeout retry fails ten times in a row because the analytics transaction is always open. What do you change?
How does this differ on MySQL with InnoDB online DDL, and what is the equivalent of the waiting-lock queue there?

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.

Disagreement needs an outcome you did not choose

Conflict answers where you were right and everyone came round are the weakest ones. Stronger: the evidence you went and collected, what would have changed your mind, and what you did in the weeks after the call went against you. Implementing a design you argued against, properly, is a specific and checkable behaviour.

Reverse your own decision and price the reversal

Medium · reversibility · measurement · migrations

Describe a technical decision you made and later reversed. Pick one that cost something: a service you split and merged back, a cache you added and removed, an index you created that pushed the planner onto a worse plan, a projection you rebuilt from scratch. State what you believed when you decided, the measurement that changed your mind, how long the wrong version ran in production, and what the reversal cost in migrations, dual writes, and a deprecation window for callers you did not own.

Approach

State the original rationale without irony, in the version you would still defend given what was known then. If it is not defensible, the story is about carelessness rather than judgement, and a different example serves you better.
Give the measurement that moved with a before and after: the p99 that did not improve, the cache hit rate that sat at 40%, the plan that flipped to a sequential scan once the table passed a size you can name.
Cost the reversal in steps, not adjectives: expand-and-contract deploys, the dual-write window, the callers who had to be notified, the rows already written in the wrong shape that had to be backfilled or abandoned.
Distinguish reversal from rewrite by naming what you kept. Most good reversals preserve the schema or the interface and undo one decision inside it, which is also why they were affordable.
Finish on the process change: the smallest experiment that would have produced the same measurement in a day, and why you did not run it the first time.

Follow-up

What in that decision was irreversible, and did you know it was irreversible when you made it?
How did you tell the people who had already built on top of the original decision?
What do you now measure before committing to a change of this size?

Turn a code review disagreement into a decision

Easy · code review · optimistic concurrency · communication

A colleague's change updates a row with UPDATE resource SET version = version + 1 WHERE resource_id = $1 AND version = $2 and treats an affected-row count of zero as a successful no-op. You read that as a silently lost update; they think returning 200 is friendlier to clients than returning a conflict. Describe how you have handled a review disagreement of this shape: what goes in the comment, when you leave the thread, and who decides. Then write the comment you would leave here, in under 80 words.

Approach

Sort the disagreement before writing anything. A silently discarded write is a correctness claim about data; the choice between 409 and 412 is taste. Only the first justifies blocking a merge, and saying which one you are doing is most of the value of the comment.
Make the claim reproducible in the comment itself with an interleaving rather than a principle: A reads version 7, B reads version 7, B commits version 8, A's predicate matches zero rows, A is told it succeeded and A's edit is gone.
Offer the alternative with its cost attached: return 409 carrying the current version and the revision that won, so the client can re-read and re-apply. Note that automatic retry is not the fix, because a retry re-reads the winner's state and reapplies an intent formed against data that no longer exists.
Apply an escalation rule you can state: two round trips on the thread, then a call, and the service's owner decides rather than the reviewer. A reviewer who cannot be overruled is a bottleneck with extra steps.
Close in writing wherever the decision lands, so the next reader finds the reasoning in the code or the ticket instead of in a collapsed review thread.

Follow-up

Where would you put the test that fails if someone reintroduces the swallowed zero rowcount?
The author says clients cannot handle a 409. How do you check whether that is true?
How do you handle the same review comment when the author is more senior than you and in a hurry?

Tell callers you do not own that their integration breaks

Medium · deprecation · compatibility · stakeholders

A field in a write endpoint's response must change shape. You own the endpoint; you do not own the four internal callers or the outbound webhook consumers who read it. Describe a deprecation you were responsible for: what you shipped first, how you established who was actually reading the field, the window you gave and what set its length, what you did about the consumer who never moved, and how you decided removal was safe. Name the signal you used, not the announcement you sent.

Approach

Establish the reader set empirically rather than from a wiki of owners: per-field usage counters keyed by principal, or access logs attributed to a consumer. State the blind spot of whichever you pick, since a consumer that reads the field only on a monthly job will not appear in a week of logs.
Ship additive first. Populate the new field alongside the old one so no reader is forced to move, which is also what keeps a rolling deploy safe, because old and new instances answer the same requests at the same time and a rollback must still find the old shape present.
Set the window from the slowest legitimate consumer's release cadence, not from your calendar, and decide separately what to do for a consumer with no release process at all, such as an external webhook endpoint you can only email.
Convert silence into evidence before you rely on it: a short, low-traffic removal window that makes a still-dependent consumer fail visibly and loudly while you are watching, rather than at three in the morning after you have moved on.
State the removal criterion as a measurement with a duration attached, such as observed reads at zero across a full billing cycle, and keep the change reversible for one release after removal.

Follow-up

How would you detect a consumer that reads the field only during a monthly export?
One caller refuses to move and has a commercial relationship behind it. What changes in your plan and what does not?
After removal, what makes the change irreversible, and how long before you cross that line?