Interview guides · LPL Financial Global Capability Center

LPL Financial Global Capability Center Software Engineer Interview Questions & Guide 2026

Take-home style practice for LPL Financial Global Capability Center software engineer candidates: a small service, a fixed budget, a written tradeoff.

The 60-second brief

The Software Engineer role at the LPL Financial Global Capability Center is a pivotal position that sits at the intersection of complex financial infrastructure and modern technological innovation. As part of a global team, you will be responsible for designing, developing, and maintaining scalable software solutions that empower financial advisors and their clients. Your work directly influences the stability and efficiency of the LPL Financial ecosystem, ensuring that mission-critical platforms operate with high availability and precision.

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

Make every money-moving endpoint idempotent by key
Model money movement as balanced double-entry postings
Name the isolation level each invariant requires

Overview

You will find yourself working on diverse initiatives, ranging from Integrated Command Center (ICC) operations and Camunda BPM workflow automation to full-stack development using.NET/C# and Angular. This environment is ideal for engineers who thrive on solving architectural challenges at scale. Whether you are building user-centric interfaces or optimizing core network services, your contributions directly impact the firm’s ability to deliver seamless financial services in a highly regulated, fast-paced industry.

The LPL Financial Global Capability Center in Hyderabad acts as a strategic hub for global technology operations. Expect to collaborate across time zones, emphasizing the need for clear communication and documentation skills.

The LPL Financial Global Capability Center interview process

LPL Financial Global Capability Center candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Technical Screening

Most of the time lost in this format is not lost to thinking. It goes to a standard-library call you half-remember, an off-by-one in a loop bound, and a debugging loop that mutates code at random until something passes. When output is wrong, stop re-reading the whole function: take the smallest input that reproduces it and walk the state through by hand, printing intermediates if the environment allows. Guessing at a fix without a failing case you understand is how a five-minute bug becomes twenty, and the clock does not pause while you do it.

What to demonstrate

Whether you reach the right structure without a detour, and can write it from memory rather than only recall that one exists
Whether overflow is considered where the language has fixed-width integers, since a signed 32-bit value stops at 2,147,483,647 and then wraps in Java, is undefined behaviour in C++, and does not arise in Python, whose integers grow instead
Whether recursion depth is treated as a constraint on large inputs, given that CPython's default limit is 1000 frames and a deep recursion can exhaust the stack in any language where an iterative version would not
Whether a failing case is isolated and explained before any edit is made to the code

How to prepare

From an empty file and with no references open, implement the pieces you lean on most: a heap push and pop, an iterative DFS with an explicit stack, and a binary search whose midpoint is written lo + (hi - lo) / 2, which avoids the overflow that (lo + hi) / 2 can hit in a fixed-width integer type
Time yourself on the ten library calls you look up most, such as sorting with a custom comparator, splitting and joining strings, and finding the next key at or above a value in an ordered map, until the lookup is gone
Take a solution you know is broken and, before touching it, write one sentence naming the input, the expected value and the actual value. Repeat until you do it without deciding to.

Deep-Dive Interviews

An unlabelled round is first an information problem, and the cheapest information is free. Whoever schedules it can usually tell you how long it runs, who will be in the room and what they work on, whether you will be writing code and in what environment, and whether anything is being sent beforehand. Ask in writing so the answer is on record, then prepare for the two or three formats those answers still leave open instead of betting on one. What separates a strong candidate is not guessing right; it is having an opening that works whichever one it turns out to be.

What to demonstrate

Whether you can start work from an ambiguous brief, since tolerating a vague scope without stalling is the same thing the job asks for
Whether the questions you asked beforehand were ones that change your preparation, such as duration, medium and who is joining, rather than ones whose answers you could not have acted on
Whether you adapt when the round turns out to be something other than what you were told, instead of spending the first ten minutes visibly recalibrating

How to prepare

Send one short scheduling message asking four things: how long, who is joining and what they work on, whether you will be writing code and where, and whether to prepare anything in advance. Treat a vague reply as real information, since it means the round is loosely structured and you will be shaping it yourself.
Write one opening that works in any of the formats still open: restate in your own words what you have been asked to do, then ask which of two directions is more useful to them. Say it aloud until it stops sounding recited.
Set up for the two most likely formats before the call starts, with a blank editor in the language you would choose and a shared document you can type into, so a format surprise costs you nothing in the first minutes

Where candidates lose points

Calling a compensating action a rollback

A saga's compensation is a new, externally visible business event, not an undo. A refund after a capture leaves both movements on the customer's statement, may not return the scheme fee, and lands days later rather than immediately. Designing a multi-service flow as though the compensation restores the prior state produces flows that turn out to be unimplementable at the final step, when the thing that needs undoing has already left the building. The sequence has to be ordered so the irreversible step is last and the reversible ones precede it, with an explicit pending state shown to the customer while a compensation is in flight.

Deriving the business date from the UTC timestamp

Posting date, value date and the processor's settlement date are three different dates, determined by cutoff times, business-day calendars and holidays rather than by midnight UTC. A movement recorded at 23:50 on one side of a cutoff belongs to the next business date, so computing business_date as created_at::date makes daily totals disagree with every statement and every settlement file. The signature is a reconciliation break that resolves itself the following day and then reopens, which reads like a flaky job and is actually a data model that is missing a column: business_date has to be stored explicitly and set from the cutoff rule, with the timestamptz kept separately for ordering.

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

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

A queue or buffer with no bound

Every producer-consumer boundary needs a capacity and a policy for reaching it: block the producer, shed load, or drop the oldest entry. Unbounded buffering converts a temporary slowdown into memory exhaustion and hides the backpressure signal that would have revealed the consumer was falling behind.

Questions & practice

Derive per-account balances and catch unbalanced transactions

Easy · aggregation · double-entry · streaming

You are given ledger_entry rows streamed in entry_id order: transaction_id, account_id, direction (debit or credit), amount_minor (a positive int64), currency, business_date. Up to 500 million rows, at most 20 million distinct (account_id, currency) pairs, and the entries of one transaction are contiguous in the stream. In a single pass with no re-reads, return the closing balance per (account_id, currency) and the transaction_id of every transaction whose entries do not sum to zero within each currency. State your time and space bounds.

Approach

Normalise the sign at read time from direction, not from the amount: signed = +amount_minor for debit, -amount_minor for credit (state which convention you picked). The schema constrains amount_minor > 0 precisely so the sign lives in exactly one place.
Hold one hash map keyed (account_id, currency) to an int64 running total. Twenty million keys at 16 bytes of payload plus map overhead is order 1 GB in most runtimes — quote the number, and offer the fallback: partition the stream by hash(account_id) % P and run P passes for 1/P of the memory.
Ride the zero-sum check on the same pass. Because a transaction's entries are contiguous, keep a tiny currency -> int64 map for the current transaction_id only, test it against zero on the boundary and at EOF, then clear it. That is O(currencies in one transaction), typically one or two.
Bound the arithmetic explicitly. Int64 holds about 9.22e18, so overflowing one account across 500 million entries needs an average of 1.8e10 minor units per entry — safe here, but use a checked add so an adversarial file fails loudly rather than wrapping.
Complexity: O(n) time, O(distinct account-currency pairs) space, one sequential pass, no sort. The zero-sum check adds no asymptotic cost, which is the argument for doing it here rather than in a second job.

Follow-up

Entries of a transaction are no longer contiguous. What does the zero-sum check cost now, and which is cheaper: buffering open transactions or an external sort on transaction_id?
How would you produce the same balances as of an arbitrary business_date without a second full scan?
The job is restarted after a crash halfway through the file. What makes the second run produce identical output?

Answer as-of balance queries over an append-only entry log

Hard · prefix sums · offline queries · append-only

Given 400 million ledger_entry rows (entry_id, account_id, direction, amount_minor, currency, business_date) and 2 million queries of (account_id, currency, as_of_date) asking for the balance at the end of that business date, produce every answer. The obvious solution — per query, sum that account's entries with business_date <= as_of_date — is correct. Say precisely why it will not finish, then give one that will, with time and space complexity. Corrections are posted as new entries carrying their own business_date.

Approach

Cost the naive version in numbers before rejecting it. Spread uniformly over 20 million accounts, each query touches about 20 rows behind a per-account index and 2 million queries is 4e7 row touches — perfectly fine. The problem is skew: one pooled clearing or merchant settlement account holding 3e7 entries, taking 10% of the queries, is 6e12 row touches. Name the skew; 'n is large' is not the reason.
The structural fact that buys a cheap answer: entries are append-only and never updated, so a prefix sum over an account's entries ordered by (business_date, entry_id) is stable — nothing behind position i can change. No mutable-balance design offers that, and it is why the storage is worth paying for.
Offline sweep, when all queries are known up front: externally sort entries by (account_id, currency, business_date, entry_id) and queries by (account_id, currency, as_of_date), then merge-walk both with a running sum, emitting each query's answer as the sweep passes its date. O((n + q) log(n + q)) dominated by the sort, O(1) beyond sort buffers, one sequential pass over each input instead of 2 million random seeks.
Online alternative: materialise end-of-day snapshots — one row per (account_id, currency, business_date) that had activity, holding the cumulative total. A query becomes one index seek for the latest snapshot at or before as_of_date, O(log n) per query, over far fewer rows than n. Use snapshots when queries arrive singly and the sweep when they arrive as a batch.
Corrections are the subtlety: an entry posted today but dated back changes historical answers, so every snapshot for that account from that date forward is stale. Either keep a Fenwick tree over dates per account (O(log D) update and prefix query) or recompute that account's snapshots from the corrected date onward. Then be precise about what reproducibility means — yesterday's statement is reproducible as of a stated snapshot time, not identical forever.
Bound the resources: int64 sums throughout, no float; 400 million rows at roughly 48 bytes of the columns you actually need is about 19 GB, so the sort is external and its fan-out is chosen from the sort buffer, not from the row count.

Follow-up

One account holds 30% of all entries. What does the external sort do with it, and what would you do for that one key instead?
Queries now arrive online at 500 per second. Which design survives, and what does keeping the other one warm cost?
A correction lands with a business_date 90 days back. Which snapshots are now wrong, and how does a reader find out?

Parse settlement amounts into minor units without floating point

Easy · parsing · money representation · iso-4217

A settlement file carries amounts as text: 1234.56, -0.07, 1,234.5, 1234, 12 345,67, and for three-exponent currencies 1.234. Each line also carries an ISO 4217 alphabetic code and the file's sign convention. Write the function converting one amount string plus its currency into a signed amount_minor int64, scaling by the currency's ISO 4217 exponent — 0 for JPY and KRW, 2 for USD and EUR, 3 for KWD and BHD. Reject anything not convertible exactly, with a typed reason. Never raise, never use a float.

Approach

Fix the contract first: return a result union of Ok(int64) or Err(reason) with reason drawn from unknown_currency, malformed, too_many_fraction_digits, overflow. A parser on this path that throws turns one bad line into a failed fifty-million-line run.
Decide which character is the decimal mark from the file's format spec, not from a heuristic on the last separator, then strip grouping separators. 1,234 is genuinely ambiguous between 1234 and 1.234 exactly when the exponent is 3, so an unstated spec is a reject, not a guess.
Split on the decimal mark. Right-pad the fraction with zeros to the currency's exponent e; if it is longer than e and any dropped digit is non-zero, return too_many_fraction_digits. Silently truncating is how half a minor unit becomes a reconciliation break nobody can source.
Build the integer by digit accumulation on int64 with checked multiply and add, or equivalently concatenate the integer and padded-fraction substrings and parse once. Either way the value never passes through a float.
Apply the sign last, from the convention the file declares — trailing CR/DR, a trailing minus as in 1234.56-, or a leading minus. Assuming a leading minus silently flips every credit in a file that uses the trailing form.
Complexity is O(len) per amount with no allocation beyond a digit buffer, so the whole file is one O(total bytes) pass.

Worked solution (20 min)

Write the exponent table {JPY:0, KRW:0, USD:2, EUR:2, KWD:3, BHD:3} and the result type before any parsing code.
Implement integer-only scaling: split on the decimal mark, pad or reject the fraction, concatenate, parse once with checked arithmetic.
Table-test: (1234.56, USD) to 123456; (1234, JPY) to 1234; (1.234, KWD) to 1234; (1234.56, JPY) to Err(too_many_fraction_digits); (0.005, USD) to Err; (-0.07, USD) to -7.
Write the float implementation beside it and run both over ten thousand random amounts across all six currencies, printing every disagreement.
Round-trip: format amount_minor back to text at the currency's exponent and assert equality with the normalised input.

Expected result: All six table cases pass. The float version agrees with the integer version on ordinary two-decimal USD amounts — which is exactly why it survives review — and disagrees on every JPY amount by 100x, every KWD amount by 10x, every input with more fraction digits than the exponent (which it rounds instead of rejecting), and under truncation, where int(1.15 * 100) yields 114.

Verify it yourself

For every input that parses, formatting the result back at the currency's exponent reproduces the normalised input string.
No code path returns a value when the fraction is longer than the exponent and a dropped digit is non-zero.
The function body contains no float, no double and no %f — grep is a legitimate test here.
The sum of parsed minor units over a whole file equals the file's own control total exactly, with no tolerance applied.

Follow-up

Exponent 4 exists (CLF). Does the exponent come from a compiled-in table or from the file header, and what happens when the two disagree?
A line has more fraction digits than the exponent allows. Is that a reject, a round, or a break — and who makes that call?
How do you fuzz this so that every failure is a typed rejection rather than a wrong number?

Stop the capture-and-refund join from double-counting merchant money

Medium · join fan-out · conditional aggregation · grain

Report captured_minor, refunded_minor and net_minor per merchant per business_date. payment_intent holds intent_id, merchant_id, amount_minor, captured_minor, refunded_minor. ledger_entry holds transaction_id, account_id, direction, amount_minor, source_type in (capture, refund, fee, chargeback), source_id = the intent_id as text, and business_date; every capture posts one debit and one credit. A draft joins payment_intent to ledger_entry twice, once filtered to captures and once to refunds, and sums both. On intents with two partial captures and one refund the totals are wrong. Name both multiplications precisely and write the correct query.

Approach

Name the first multiplication by cardinality: intent to capture entries is one-to-N and intent to refund entries is one-to-M, so joining both yields N times M rows per intent. Every capture row is then counted M times and every refund row N times, and the two columns are inflated by different factors, which is why the totals look almost right rather than obviously broken.
Name the second, which is easier to miss: double-entry means each capture posts two legs, so summing every entry of a capture counts the amount twice regardless of joins. Select the single leg by ACCOUNT - the merchant's settlement payable, which carries exactly one leg of every capture and of every refund - and not by direction. Direction is not a substitute for that filter: a capture credits the payable and a refund debits it, so adding direction = 'credit' keeps the captures and silently drops every refund leg, leaving refunded_minor at exactly zero on a query that otherwise reads as correct.
Reject the reflexive fixes: SELECT DISTINCT and SUM(DISTINCT ...) de-duplicate values, not rows, so two genuine captures of equal amount collapse into one and the total is wrong in the other direction while looking tidier.
Prefer a single pass with conditional aggregation over ledger_entry, restricted to that one account: SUM(amount_minor) FILTER (WHERE source_type = 'capture') and the same for refunds, grouped by merchant and business_date, joining payment_intent only to reach merchant_id. One scan, no intent-level fan-out, and fees or chargebacks are added as another FILTER rather than another join. amount_minor is a positive magnitude, so net_minor is captured minus refunded; the signed form over the same rows, SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END), returns the same net in one column and is the check on the sign convention.
Keep aggregate-then-join in reserve for when per-intent detail is required: aggregate each side to intent grain in its own CTE, then join the two aggregates one-to-one. LATERAL works too and reads better when the right side needs the left's key.
Get the account predicate, the join column and the date right. Each merchant has its own settlement payable, so the predicate is a join to the account dimension (a.account_type = 'merchant_payable' AND a.merchant_id = pi.merchant_id); a single $settlement_account literal is only correct for a one-merchant report. source_id is text and intent_id is uuid, and PostgreSQL has no implicit cast between them, so one side must be cast and the side decides which index stays usable - pi.intent_id = e.source_id::uuid probes payment_intent's primary key per entry row, e.source_id = pi.intent_id::text probes an index on ledger_entry(source_id), and if neither fits the driving table the planner hashes both sides, which is fine for a day's report and not for a single-intent lookup. Group on ledger_entry.business_date rather than posted_at::date, because they differ across the cutoff, and decide explicitly whether a later-dated refund reduces its own day or the original capture's.

Worked solution (30 min)

Build a fixture with one intent that has two partial captures and one refund, each posting a debit and a credit, and compute the correct totals by hand first.
Run the double-join draft against it and express each column's error as a product of the two mechanisms, rather than guessing: four capture rows and two refund rows, so the capture sum is doubled by its legs and multiplied by the two refund rows, and the refund sum is doubled by its legs and multiplied by the four capture rows.
Write the single-pass version: SELECT pi.merchant_id, e.business_date, COALESCE(SUM(e.amount_minor) FILTER (WHERE e.source_type = 'capture'), 0) AS captured_minor, COALESCE(SUM(e.amount_minor) FILTER (WHERE e.source_type = 'refund'), 0) AS refunded_minor FROM ledger_entry e JOIN payment_intent pi ON pi.intent_id = e.source_id::uuid JOIN account a ON a.account_id = e.account_id AND a.account_type = 'merchant_payable' AND a.merchant_id = pi.merchant_id GROUP BY 1, 2, with net_minor as the difference. No direction predicate: the account join already picks one leg out of each pair, and a direction filter would drop the refunds.
Compare both queries against the hand-computed fixture and against SUM(payment_intent.captured_minor) for the same merchant and day.

Expected result: On that fixture the draft reports captured_minor at four times the true figure and refunded_minor at eight times it, the two factors being 2 legs times 2 refund rows and 2 legs times 4 capture rows. The single-pass query matches the hand-computed totals exactly, refunded_minor is the refund's own magnitude rather than zero, and net_minor equals captured_minor minus refunded_minor to the minor unit.

Verify it yourself

Add a second refund and confirm the correct query's captured_minor does not move at all, while the draft's rises again.
Drop the merchant-payable predicate and watch every total double exactly, which isolates the double-entry legs from the join fan-out. That test only works because there is no direction filter left to mask one leg.
Assert refunded_minor is non-zero on the fixture, then re-add direction = 'credit' and watch it fall to exactly zero while captured_minor does not move - the silent failure this account predicate avoids.
Cross-check the day's captured_minor against SUM over payment_intent.captured_minor and account for any difference by business_date rather than by tolerance.

Follow-up

A refund lands on a later business_date than its capture. Which day does net_minor move, and what does that do to a daily merchant payout?
Add scheme fees, which arrive netted into a batch total rather than per transaction. How does the query change, and what can it no longer claim?
How would you reconcile this output against payment_intent.captured_minor, and which one is authoritative when they disagree?

Make a charge endpoint safe under concurrent duplicate retries

Easy · idempotency · unique constraints · read committed

idempotency_key holds id, scope, key, request_fingerprint (SHA-256 over the canonicalised body), status (in_progress, completed, failed), response_status, response_body, locked_at, completed_at, expires_at, created_at. Fifty identical create-payment requests carrying the same scope and key reach four application instances inside the same 20 ms. Give the DDL constraint and the exact statements the handler runs so that exactly one payment_intent is created and all fifty callers receive the same response body. State what you return when that key arrives with a different fingerprint, and what an arrival after expires_at means.

Approach

Put the concurrency control in the schema: UNIQUE (scope, key). A SELECT-then-INSERT cannot work because both transactions can read nothing before either commits, so the check passes twice and the constraint then surfaces as an error on a payment that succeeded.
Claim the key with INSERT ... ON CONFLICT (scope, key) DO NOTHING RETURNING id. A conflict returns zero rows rather than the existing row, so branch on rowcount: the winner proceeds, the loser reads the stored row.
Keep that path on READ COMMITTED deliberately. The loser's follow-up SELECT takes a fresh statement snapshot and therefore sees the winner's committed row; under REPEATABLE READ the transaction snapshot predates that commit, the row stays invisible and the loser concludes the key does not exist.
Split the work across two transactions because the processor call cannot sit inside one: commit the in_progress row with locked_at first so losers can see a claim, perform the effect, then write payment_intent plus status=completed with response_status and response_body in a single second transaction.
Handle the crash window explicitly: a row stuck in_progress past its lease is an unknown outcome, not a failure, so the reaper queries the processor for that key before deciding. A loser that sees in_progress returns 409 and retries rather than repeating the effect.
Compare request_fingerprint before replaying anything. Same key with a different body is 409, never the cached response, because replaying confirms a payment the caller did not request; and set expires_at beyond the client's and the processor's maximum retry horizon, since a replay after it is a genuinely new request.

Follow-up

The handler dies after the processor call and before the local commit. What does the next retry with that key observe, and how does the system converge on exactly one charge?
Does the downstream processor honour an idempotency key of its own? Who mints it, and what breaks if a fresh one is generated per attempt?
How do you purge rows past expires_at without the delete contending with the insert path?

Design a scalable monitoring solution for an Integrated Command Center (ICC).

Medium · system design and architecture

Approach

Name the read and write paths separately; they rarely have the same bottleneck.
Name the failure you are designing for, then the recovery path.
Fix the scope first: who calls this, how often, and what they do when it fails.

Follow-up

What would you drop to keep the system up under load?
How does this behave when that dependency is down for an hour?

How would you approach refactoring a legacy monolithic application into microservices?

Medium · system design and architecture

Approach

State the consistency you need, and where you are willing to be stale.
Name the failure you are designing for, then the recovery path.
Choose a partition key and say what query it makes expensive.

Follow-up

What would you drop to keep the system up under load?
How does this behave when that dependency is down for an hour?

How do you ensure high availability and reliability in distributed systems?

Medium

Approach

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

Follow-up

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

Describe a challenging bug you encountered in a Camunda BPM or middleware workflow and how you resolved it.

Medium

Approach

Work from the requirement backwards to the design.
Clarify what is being asked and what a complete answer contains.
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?

Ingest daily settlement files idempotently and restartably

Easy · idempotency · batch ingestion · partitioning · restartability

A processor delivers one settlement file per business date containing 10 to 50 million lines, each with an external reference, a signed amount in minor units, a currency, a fee and the processor's settlement date. Files arrive late, are sometimes re-sent byte-identical, and are sometimes re-sent corrected under the same filename. Design ingestion: how a line becomes a settlement_line row, how a re-send avoids duplicating 50 million rows, how a correction supersedes the earlier version without deleting it, and how a run that dies at line 30 million resumes. State your throughput target.

Approach

Treat the file as a versioned object rather than a stream of events. Key a file record on (processor_id, business_date, content_sha256): an identical re-send matches the digest and becomes a no-op in O(1), while a corrected re-send has a different digest and becomes file_version 2, which supersedes version 1 by stamping superseded_at on its lines rather than deleting them. Deleting them destroys the evidence for any break already raised against version 1.
Give lines two levels of identity. UNIQUE (file_version_id, line_no) makes a resumed chunk unable to double-insert. Deduplication on business content must not collapse (external_reference, amount_minor, currency) into one row, because two identical genuine charges in one day are ordinary; if you need a business-level key, it has to include the line ordinal.
Make restart cheap by committing in chunks of about 10,000 lines and recording a high-water line_no per file_version, so a crash costs at most one chunk. Smaller chunks mean more commit and WAL overhead; larger chunks mean more re-work after a failure. Pick from measured commit cost, not from a round number.
Set the throughput target from the loading mechanism: a bulk COPY of narrow rows runs at order 10^5 rows/s per connection where row-by-row INSERT runs at order 10^3 to 10^4, so 50 million lines is minutes rather than hours. Parallelise by splitting the file into byte ranges aligned to line boundaries, one worker and one checkpoint per range.
Partition settlement_line by business_date. A full re-ingest then rebuilds one partition instead of issuing a 50-million-row DELETE, and retention becomes a partition DETACH rather than a long-running vacuum problem.
Validate before publishing the version: compare the file trailer's control totals against SUM(amount_minor) over the loaded rows, and fail the run on mismatch. A truncated file that loads cleanly otherwise looks exactly like a day when the processor sent less money.

Worked solution (30 min)

Generate a 10,000,000 line file, compute its SHA-256, and load it with COPY into a staging table partitioned by business_date, committing every 10,000 lines and recording the high-water line number.
Ingest the identical file again and assert zero new rows, with no work done beyond the digest comparison.
Alter 100 lines, re-ingest under the same filename, and assert a new file_version supersedes the first while version 1 rows remain fully readable.
Kill the worker at line 4,000,000, restart it, and count the final rows.

Expected result: COPY sustains order 10^5 narrow rows per second per connection, so 10 million lines land in roughly one to two minutes on a single connection and scale close to linearly with byte-range parallelism. The identical re-send costs one digest comparison. The crash-restart run ends with exactly 10,000,000 rows: no duplicate chunk, no gap at the restart boundary.

Verify it yourself

UNIQUE (file_version_id, line_no) exists and is what makes the resumed chunk safe, rather than the worker's own bookkeeping.
Two lines identical in reference, amount and currency both survive ingestion.
Control totals from the trailer equal SUM(amount_minor) for the version, and a deliberate truncation fails the run instead of opening breaks.

Follow-up

The same file starts ingesting twice concurrently from two workers. What stops the second one?
The corrected file has fewer lines than the original. Which lines disappeared, and how does a break already raised against one of them get resolved?
How do you know the file is complete rather than still being written by the transfer?

Statement endpoint slows linearly with the entry count

Easy · n+1 · latency · query planning

A statement endpoint reads ledger_entry for one account and a date range and returns entry_id, amount_minor, currency, source_type and business_date, plus the display name of the counterparty account found through the other entry on the same transaction_id. p99 is 40 ms for a 20-entry month and 2.1 s for a 900-entry month. The database reports 901 statements per request, each under 1 ms, and no single query is slow. Diagnose the cause and give the fix, stating the statement count and the p99 you expect afterwards.

Approach

Establish the shape before theorising. Divide the per-request statement count from pg_stat_statements by the rows returned: 901 statements for 900 rows is one driver query plus one per row, and because each is sub-millisecond it rules out a bad plan. The latency is round trips, not work.
Identify the per-row statement by its normalised text. It will be a single-row lookup on ledger_entry joined to account, keyed by transaction_id, issued from the serialisation layer rather than the repository. Confirm it is lazy loading by checking that it disappears when the counterparty name is dropped from the response.
Do the arithmetic as a division from the incident rather than a multiplication from assumptions. The 900-entry request issues 880 more statements than the 20-entry one, 901 against 21, for 2.06 s more wall time, so each extra statement costs at most about 2.3 ms end to end, and that is an upper bound because the larger response also carries more payload. Then measure one round trip on this path instead of assuming it: pool checkout, network, parse, bind, execute and per-row hydration in the driver, not network alone. A naive 1 ms network plus the 0.2 ms the database reports would cover only about 1.05 s, half the excess, so if a measured round trip really is that cheap then the N+1 is not the whole story and you owe an explanation for the rest before proposing a fix.
Replace the per-row lookup with one batch statement: collect the transaction_ids from the driver query and fetch all counterparty entries with WHERE transaction_id = ANY($1), then build the map in memory. Two statements per request, independent of the range width.
Check the index before declaring victory. The batch query needs an index on ledger_entry(transaction_id); without it ANY($1) degrades to a sequential scan over the entry table and the fix becomes a worse regression than the bug.

Follow-up

What changes if the statement must paginate at 200 entries per page?
A transaction can carry more than two entries once fees have their own leg. What does your query return then, and what should the counterparty column mean?
What would have caught this before production, given that no individual query is slow?

Suggested practice checklist

For someone fluent in a dynamic language who has shipped real work but has never had to say what the runtime is doing underneath. The week is built on measuring and deliberately breaking things, because the questions that expose this background are the ones where the interviewer asks why a second time.

Day 1 — Measure before reasoning

Take a slow piece of your own code, write down in advance where you believe the time goes, then profile it and record how wrong the guess was. The cost is usually an allocation you did not notice or an accidental quadratic membership test.
Replace one list membership test inside a loop with a set and measure at a thousand, ten thousand and a hundred thousand elements, confirming the shape of the curve rather than only that it got faster.
Write down the three quantities you can now measure instead of assert: wall time, peak memory, and call count for the function you suspected.

Deliverable: A before-and-after profile of real code plus a written note on the size of the gap between the guess and the measurement.

Day 2 — References, copies, and the bugs they produce

Write the function with a mutable default argument, call it three times, and explain the accumulating result: the default is evaluated once when the function is defined, so every call shares one object.
Build a nested structure, take a shallow copy, mutate an inner element, and show that both views changed, because a shallow copy duplicates the container and not the elements. Then fix it with a deep copy and state the cost you just accepted.
Write two functions, one mutating its argument in place and one rebinding the local name, and predict the caller's view of each before running it. That single distinction produces most of the bugs that pass their tests.

Deliverable: Three small programs whose output you predicted correctly before running, each with a one-line statement of the rule underneath.

Day 3 — Types, once, in a language that checks them

Port one module you have already written, roughly a hundred lines, into a statically typed language, and record every place the compiler demanded an answer your original had left implicit: a value that can be absent, a numeric width, a case never handled.
Write the same signature in both languages and state what the static one guarantees before the program runs and what it does not, since it will not save you from a wrong algorithm or an index out of range.
Write the difference between an interface satisfied by declaration and one satisfied structurally, with one case each where the other approach would miss the mistake.

Deliverable: One module in two languages plus a list of the questions the type checker forced you to answer.

Day 4 — Concurrency, starting with what actually runs at the same time

Run the same CPU-bound function across four threads and four processes and measure both. Under the default CPython build the threaded version will not speed up, because only one thread executes bytecode at a time; the process version will. Check which build you are on first, since free-threaded builds remove that lock and change the result.
Then run a blocking I/O workload across four threads and measure it speeding up, because the interpreter releases that lock around blocking calls, which is why treating threads as useless is wrong as a general claim.
Build the lost update: two threads each incrementing a shared counter a hundred thousand times, and show a final value below the expected sum, because an increment is a load, an add and a store and the thread can be suspended between them. Fix it with a lock and then measure what the lock costs.

Deliverable: Three measurements, threads against processes on CPU work, threads on I/O work, and a demonstrated lost update, each with the mechanism written underneath.

Day 5 — Debugging as a procedure rather than an instinct

Work one real failure as a bisection: find a revision or an input size where it is good and one where it is bad, halve repeatedly, and state the two assumptions bisection needs, that the property changes exactly once across the range and that the test is reliable.
Minimise one failing input to the smallest version that still fails, and record how many rounds it took.
Keep a hypothesis log for one bug in three columns, what I believe, what would disprove it, what I observed, and stop yourself the first time you are about to change two things at once.

Deliverable: One bug worked to root cause with a written hypothesis log and a minimised reproducing input.

Day 6 — Tests that catch the bug you are about to write

Implement an LRU cache with a capacity bound, then write the three test cases that would catch an off-by-one in eviction: insert exactly capacity items and assert nothing was evicted, insert one more and assert the least recently used key is the one gone, and read an old key just before that insert so the eviction victim changes.
Add a property test comparing your implementation against a deliberately slow reference, an ordered list scanned linearly, over a few thousand random operation sequences, because a slow reference finds the cases you would not have thought to write.
Write one numeric test that fails under exact equality and passes with a tolerance, and state why the tolerance has to be relative rather than absolute once the magnitudes grow.

Deliverable: An LRU implementation with three boundary tests, one property test against a slow reference, and one tolerance-based numeric test.

Day 7 — Debug something broken, out loud

Have someone plant three defects in a two-hundred-line program, an off-by-one, a shared mutable state bug, and a wrong error-handling path, then find them while narrating, under a fixed rule: state the hypothesis before touching anything.
Time each one and record which tool found it, reading, a printed value, a debugger, or a test, because the question asked in interviews is how you would find it rather than what it was.
Write the sentence you will use when you do not yet know the cause, one that names the next measurement instead of offering a guess.

Deliverable: A recorded debugging session with time-to-find per defect and the method that found each.

'We rewrote it in a better framework' is not a story

A migration is a cost you chose to pay, not an achievement. The story is what the old system made expensive, what you measured before committing, what kept serving traffic during the cutover, and what you would have done if the numbers had come back flat. Without those, a rewrite reads as taste.

Describe a situation where you had to influence a team member to adopt a new technical standard or methodology.

Medium · behavioural and engineering judgement

Approach

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

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 Angular lifecycle hooks and state management?

Medium · behavioural and engineering judgement

Approach

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

Follow-up

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

Tell me about a time you had to explain a complex technical issue to a non-technical stakeholder.

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

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