Interview guides · Shopify

Shopify Software Engineer Interview Guide

Shopify Software Engineer interview prep: recruiter screen, online assessment, Life Story, pair programming, technical deep dive, plus a 7-day plan.

The 60-second brief

Shopify's platform serves merchants, and the engineering problems in this guide come from that setting: checkout pipelines, inventory under heavy concurrent demand, developer-facing APIs and webhook delivery. Shopify runs a large Ruby on Rails monolith alongside services and interfaces built with Go, React and TypeScript. Candidates report questions built on practical, domain-shaped problems rather than algorithm puzzles: extending a grid robot simulator, building an LRU cache or a URL shortener, and designing webhook delivery or oversell-proof inventory.

This guide covers the five stages candidates report for the Software Engineer loop: a recruiter screen, an online assessment, the Life Story interview, one or two pair programming sessions, and a technical deep dive that spans presenting a past project and system design. Each stage lists the reported question types and how to prepare for them. The guide also includes original SQL, coding and design drills with worked solutions, plus a debugging drill, a 7-day plan and common mistakes to avoid in each round. Rounds vary by seniority, so confirm your own loop with your recruiter.

Focus areas

Make checkout idempotent from cart to capture
Model variant attributes without wide tables or EAV
Keep flash-sale writes off one contended row

Overview

Software Engineers at Shopify build and run systems for the company's merchants. The problem areas include high-throughput checkout, real-time inventory, developer ecosystem APIs and automated webhook delivery, and those systems must survive traffic spikes during large sales events such as Black Friday / Cyber Monday.

The reported interview questions follow the same practical line. In pair programming, candidates describe building something small and then extending it: a robot or vacuum simulation on a grid that later has to handle obstacles, an LRU cache, a URL shortener with collision handling, a per-merchant sliding-window rate limiter, and character movement on an ASCII grid. The reported system design questions cover webhook delivery with at-least-once guarantees, preventing overselling during flash sales, scaling a relational database, multi-channel notification dispatch, and splitting a monolithic component without downtime.

Two stages differ from a typical loop and deserve their own preparation. The Life Story interview goes through your career in order and asks why you made each move. The technical deep dive asks you to present a system you built and defend its internals. Candidates also report that documentation and AI assistants are allowed during pair programming. If you use them, review generated code line by line so you can explain every function if asked.

The Shopify interview process

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

Recruiter Screen

The recruiter screen is a call about your background and whether you fit the role. Use it to learn which version of the loop you will get. Leave the call knowing which of those apply to you.

What to demonstrate

Whether you can give a clear, honest summary of your background and why you want this role
Whether your experience lines up with the level and team being hired for, stated without inflating it

How to prepare

Prepare a short career summary that doubles as the opening of your Life Story answer: each role, what you owned, and why you moved on
Ask which rounds your loop includes and in what order, whether the Life Story round comes early or late, and whether system design is a separate session
Ask what pair programming allows: your own IDE or CoderPad, which language, and whether documentation and AI assistants may be used

Online Assessment

Candidates describe this stage as an online assessment or a cognitive/personality test. If the assessment is automated, the problem statement and its examples are the whole specification. Coding questions in the bank for this role include simulating Tic-Tac-Toe and detecting the result, and finding the top three companies by seven-day average stock price with a size-three min-heap. None of these is tied to a specific round.

What to demonstrate

Whether your code handles cases the examples do not show, such as empty input, ties and a board with no winner yet
Whether you finish a correct, readable solution rather than an ambitious one that does not run
On the aptitude part, whether you reason through logic and computational-thinking problems accurately

How to prepare

Before writing the solution, write a small harness that runs the prompt's examples plus an empty and a single-element case and prints expected against actual
Practise small simulation and top-k problems (a game-state checker, a heap-based ranking) in a plain editor without autocomplete
Do a few timed logic and pattern-reasoning sets so the format of an aptitude test is familiar before the day, and answer the values questionnaire honestly rather than guessing at a preferred answer

Life Story Interview

The Life Story interview goes through your career in order: the decisions behind each role change, promotion or company move, what you learned from failures, and what motivates you. Reported questions include walking through your whole career with the reasoning at each decision point, handling ambiguous or flawed leadership direction, a critical initiative you failed to deliver, staying technical while taking on leadership, and what draws you to Shopify specifically. Candidates report it can happen early or late in the process.

What to demonstrate

Whether each career move comes with a reason you actually held at the time, not a tidy story made up afterwards
Whether you describe failures with specific consequences and a concrete change in how you work
Whether you can explain why Shopify in particular, tied to its merchant-focused work, rather than giving a generic answer

How to prepare

Build a dated timeline of every role with three notes each: why you joined, the hardest thing you shipped or fixed, and why you left
Prepare one failure story where you owned the outcome, including what you changed afterwards and evidence that the change stuck
Rehearse the full walkthrough aloud, then a compressed version, so you can go deeper wherever the interviewer asks without losing the thread

Pair Programming

Candidates describe one or two practical coding sessions run as a collaboration, in your preferred IDE or CoderPad, with documentation and AI tools reported as permitted. The reported questions (a robot or vacuum grid simulation extended to handle obstacles and path optimization, an LRU cache with a discussion of thread safety and memory, a URL shortener with key generation and collision handling, ASCII-grid character movement driven by key commands, and a per-merchant sliding-window rate limiter) share a pattern: get a simple version working, then extend it without rewriting it. Keeping state separate from command handling is what makes the second step cheap.

What to demonstrate

Whether your class design (clear responsibilities, state separate from execution) lets new requirements slot in without a rewrite
Whether you ask clarifying questions early, talk through your reasoning, and change course when the interviewer adds a constraint
Whether you check correctness with tests and edge cases, and can explain any code an AI assistant produced

How to prepare

Build a grid robot command simulator from scratch, then add obstacles and multiple robots as a second step, and note what you had to change
Implement an LRU cache with O(1) get and put using a hash map plus a doubly linked list, then explain what thread safety would require
Write unit tests for each exercise as you go (boundary moves, eviction order, window edges), since testing after pair programming appears in the bank
Set up your editor, test runner and language beforehand so the session starts with code, not configuration

Technical Deep Dive

The loop listing calls it a system design round, and the process overview describes a technical deep dive where you present and defend a past project, with system design as a separate round in mid-level and senior panels. Prepare for both. Reported deep-dive questions ask you to walk through a system you built (data flow, service boundaries, data structures), a trade-off made under constraints, fault tolerance and monitoring, a production performance bug or memory leak, and API backward compatibility for third-party developers. Reported system design questions include webhook delivery, flash-sale inventory and multi-channel notifications.

What to demonstrate

Whether you can explain the internals of a system you built and justify the trade-offs, including what you would change today
Whether your designs deal with concurrency, retries, failure isolation and consistency instead of only drawing boxes
Whether you back claims about production behaviour with concrete metrics you actually observed

How to prepare

Pick two or three past projects and for each write the data flow, the main trade-off with its alternative, one incident and one number you can defend
Design webhook delivery with at-least-once semantics: idempotency keys, retry with exponential backoff, and isolating failing endpoints so one slow receiver cannot stall the rest
Design flash-sale inventory so reservations cannot oversell, using the conditional-update approach in this guide's inventory drills, and say where the single hot row limits throughput

Where candidates lose points

Pasting AI-generated code into pair programming and being unable to explain a line of it

Be ready to explain every function and the edge cases it handles if asked. If you use an assistant, ask it for small pieces, read each one before accepting it, run it against a case you chose, and say out loud what you checked. If you cannot defend a block, rewrite it yourself.

Writing the grid simulator as one function, so adding obstacles or a second robot forces a rewrite

The reported pair-programming problems grow in steps: obstacles, path optimization, more commands. From the start, separate grid state, command parsing and movement rules into small units, get the base case passing, then extend. When the new requirement arrives, point out which unit changes and which stay the same.

Finishing a pair-programming exercise without a single test

Testing comes up repeatedly in the bank for this role, including a bank-account simulation with self-written tests and a question on testing after pair programming. Write a test for the base case as soon as it works, then add boundary tests (moving off the grid, evicting at capacity, a request exactly at the window edge) before you call the work done.

Telling the Life Story as a list of job titles with no reasons behind the moves

The reported questions ask for the decision points behind each role change and what failures taught you. For every move, give the situation, the options you had, why you chose as you did, and what you would choose now. Include at least one failure with its real cost.

Presenting a past project you cannot defend below the architecture diagram

Deep-dive questions go into data structures, service boundaries, fault tolerance, debugging a production bottleneck and API backward compatibility. Pick a project where you made the decisions yourself, and prepare the trade-off you made, the alternative you rejected, a measured result and an incident, so follow-up questions go into material you know.

Questions & practice

Build an LRU (Least Recently Used) Cache using data structures native to your chosen language, and discuss thread safety and memory complexity.

Medium · data structures and algorithms

Approach

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

Follow-up

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

Design a rate limiter class that restricts API requests per merchant within defined sliding time windows.

Medium · data structures and algorithms

Approach

Walk one small example through your approach before writing the whole thing.
Name the brute-force solution and its complexity before improving on it.
Choose the data structure from the access pattern, not from familiarity.

Follow-up

How does this change if the input no longer fits in memory?
Which test case would catch an off-by-one here?

Design and implement a simulation for a vacuum cleaner or robot navigating a grid, expanding functionality to handle obstacles and path optimization.

Medium · data structures and algorithms

Approach

Restate the input: its shape, its size, and what is guaranteed about it.
Choose the data structure from the access pattern, not from familiarity.
Walk one small example through your approach before writing the whole thing.

Follow-up

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

Implement a URL shortener service API from scratch, focusing on data structures for lookup, key generation, and hash collision handling.

Medium · data structures and algorithms

Approach

Walk one small example through your approach before writing the whole thing.
Name the brute-force solution and its complexity before improving on it.
Restate the input: its shape, its size, and what is guaranteed about it.

Follow-up

How does this change if the input no longer fits in memory?
Which test case would catch an off-by-one here?

Roll up facet counts over a category graph without double counting

Medium · dag · topological sort · distinct counting · sketches

Categories form a directed acyclic graph of up to 100,000 nodes and 300,000 child-to-parent edges, and a category may have several parents. Two million active variants each sit in exactly one category. For every category, return the number of distinct active variants in it or any descendant, and refuse to produce counts at all if a supplier feed has introduced a cycle. The memory budget is one gigabyte. State your complexity and where exactness is lost, if it is.

Approach

Run Kahn's algorithm first: repeatedly remove zero-in-degree nodes, and if any node remains the graph contains a cycle. It costs O(V+E) and hands you the topological order the rollup needs anyway. Report the residual node set so the feed owner sees which edges close the cycle, rather than an assertion that the feed is bad.
Show why addition is wrong here. On a tree, counts accumulate exactly in reverse topological order. On a DAG, a category reachable from an ancestor by two paths contributes twice, so a straight sum overstates every node above a diamond — and the overstatement is largest at the high-traffic parent categories, which is where a wrong number is most visible.
Exact distinct counting needs set union. A bitset per node is 2,000,000 bits, or 250 KB, and 100,000 nodes is 25 GB — twenty-five times the budget. Compute that number and abandon the approach explicitly instead of hand-waving past it.
Use HyperLogLog and merge in reverse topological order. Union is lossless because it is the register-wise maximum, which is exactly the property that makes it safe on a DAG where one variant arrives by two paths. At m = 4,096 registers the relative standard error is 1.04/sqrt(m), about 1.6%, at roughly 4 KB per sketch — about 400 MB for 100,000 nodes, inside budget. At m = 1,024 it is 3.25% error and about 100 MB.
Complexity: O(V + E) traversal with one sketch merge per edge, O(V*m) space. Then state the exactness policy rather than leaving it implicit: keep exact sets below a cardinality threshold and switch to sketches above it, because the pages where an off-by-a-few count is noticeable are the small ones.

Worked solution (35 min)

Build a 12-node DAG containing one diamond, assign variants to leaves by hand, and write down the exact per-node distinct counts.
Implement the additive rollup and confirm it overstates at and above the diamond by exactly the shared subtree's variant count.
Implement the HLL rollup at m = 4,096 and compare against exact counts on the toy graph and on a generated 100,000-node graph.
Add an edge that closes a cycle and confirm the job refuses with the residual node list rather than looping or emitting partial counts.

Expected result: Additive and sketch rollups agree on every node below the diamond. Above it, the additive result is high by exactly the shared subtree's variant count while the sketch stays within roughly 1.6% relative standard error. Cyclic input produces a refusal naming the nodes involved.

Verify it yourself

On the toy graph, every node's sketch count is within one unit of a brute-force distinct union computed with real sets.
No parent's count is below the count of any of its children.
Removing the diamond's second edge makes the additive result equal the exact result everywhere, confirming the diamond was the only source of double counting.

Follow-up

A facet count and the filtered result count differ by 1.4%. What do you show the customer, and which of the two numbers do you fix?
One variant moves between categories. What must be recomputed, and can it be done incrementally?
Counts must now exclude variants with zero ATP at every node. Where does that predicate live, and what does it do to your refresh cadence?

Retire a variant without breaking historical order lines

Easy · soft deletes · audit history · referential integrity

product_variant has status in draft, active, discontinued, blocked, and sku_code UNIQUE. order_line references variant_id and already stores title_snapshot and unit_price_minor. A team proposes adding deleted_at to product_variant, hard-deleting anything discontinued for a year, and reusing the freed sku_code values. Say what each of those three does to order_line and to the unique constraint, give the DDL you would actually ship including any audit table, and write the query listing unshipped lines on confirmed orders whose variant is no longer active.

Approach

Kill the hard delete on referential grounds rather than on principle. order_line.variant_id is a foreign key, so a DELETE either fails under the default NO ACTION or, with ON DELETE CASCADE, silently removes paid order history. Neither of those is a retention policy.
Point at title_snapshot and unit_price_minor: that denormalisation is exactly what makes retirement survivable, because rendering an old order never joins product_variant. The row then only has to persist for the foreign key and for analytics, not for display, which is a much weaker requirement than the team assumed.
Prefer the status enum that already exists over a parallel deleted_at. Two independent ways for a row to be dead means every query needs both predicates, and the one somebody forgets is the one that resurfaces a blocked variant in a facet or a sitemap.
If soft delete ships anyway, the unique constraint must become a partial unique index — UNIQUE (sku_code) WHERE deleted_at IS NULL — or a retired row holds the code forever. Then argue against reuse on its merits: sku_code is the warehouse- and supplier-facing identifier, so reusing it makes every historical pick, return and supplier invoice ambiguous about which product it refers to.
Put history in product_variant_audit at one row per change, keyed (variant_id, version), carrying the changed columns and changed_at_utc, written by the single writer or a trigger. For as-of questions either keep valid_from_utc and valid_to_utc with an EXCLUDE constraint on (variant_id WITH =, validity WITH &&) using btree_gist to forbid overlaps, or reconstruct by taking the newest audit row at or before the timestamp — the first costs a write-side constraint, the second costs a per-query sort.

Worked solution (25 min)

Attempt a DELETE of a variant referenced by order_line and record the exact error and the constraint name it reports.
Add deleted_at, mark one variant deleted, and try to insert a new variant reusing its sku_code; record the unique violation.
Replace UNIQUE (sku_code) with a partial unique index WHERE deleted_at IS NULL and repeat the insert.
Write and run the unshipped-lines query joining order_line to customer_order and product_variant, filtering order_line.state in ('reserved','released_to_node') and product_variant.status <> 'active'.
Mark a variant discontinued rather than deleted and confirm the query still returns its lines.

Expected result: The DELETE fails with a foreign-key violation naming order_line's constraint; it never cascades. Under plain UNIQUE, SKU reuse fails even though the old row is soft-deleted; under the partial unique index it succeeds, which is precisely the ambiguity you then argue against. The query returns one row per affected order_line with its order state and the variant's current status.

Verify it yourself

Two soft-deleted rows can now share a sku_code under the partial index — insert a second and confirm it is permitted, then decide whether that is acceptable.
EXPLAIN the order-history read path and confirm product_variant does not appear in the plan at all, which is the snapshot columns doing their job.
The query keys on order_line.state and product_variant.status, not on deleted_at, so it keeps working whichever retirement mechanism ships.

Follow-up

A returns agent needs the variant's hazmat flag as it stood on the shipment date. Which of the two history shapes answers that in one index seek, and what does the other cost?
An erasure request arrives for a customer, not a variant. Which columns on customer_order and order_line can be erased while the financial record stays reconcilable?
Merchandising wants discontinued variants gone from search within a minute. What event do you publish, and what does the consumer do with it?

Write the reserve statement that cannot oversell a variant

Medium · isolation levels · compare-and-set · lock contention

inventory_position, keyed (variant_id, node_id), holds on_hand_units, reserved_units, damaged_units, safety_stock_units, oversell_allowance_units and CHECK (reserved_units <= on_hand_units - damaged_units + oversell_allowance_units). inventory_reservation holds reservation_id, variant_id, node_id, owner_type, owner_id, units, state, idempotency_key UNIQUE. Write the exact statements one reserve call runs, name the isolation level, and say what a retry of the same call does. Then explain why SELECT the position, subtract in application code, UPDATE to the result loses units, and give the throughput ceiling for one contended row.

Approach

Write available-to-promise as an expression, never a column: on_hand_units - damaged_units - reserved_units - safety_stock_units + oversell_allowance_units, with in_transit_units included only when the promise date is beyond expected_receipt_at_utc. Note that the CHECK constraint is deliberately looser than that policy because it ignores safety stock: the constraint is the hard oversell floor, the WHERE clause is the commercial one, and they are allowed to differ.
Claim idempotency first. INSERT INTO inventory_reservation (...) VALUES (...) ON CONFLICT (idempotency_key) DO NOTHING RETURNING reservation_id. Zero rows returned means this call is a replay, so read the existing row, return it, and touch no counter. Doing this before the counter update is what makes a retried reserve a no-op instead of a second hold.
Bind the units in one statement: UPDATE inventory_position SET reserved_units = reserved_units + $n, updated_at_utc = now() WHERE variant_id = $v AND node_id = $k AND on_hand_units - damaged_units - reserved_units - safety_stock_units + oversell_allowance_units >= $n. Judge it by rows affected; zero is a refusal, and rolling the transaction back drops the reservation row so the caller may genuinely retry later — a refusal spends nothing.
Say why that is safe at READ COMMITTED in PostgreSQL: the UPDATE blocks on the row lock and, when the blocking transaction commits, re-evaluates its WHERE clause against the updated row version rather than against the snapshot it began with. Split into SELECT then UPDATE and the two statements take separate snapshots, both read the same available count, and both write — a lost update that READ COMMITTED permits by design. REPEATABLE READ does not silently fix it; it converts it into a serialization failure (SQLSTATE 40001) that somebody has to retry, and InnoDB's REPEATABLE READ does not abort at all, so identical code behaves differently on a different engine.
Give the ceiling as arithmetic. The row lock is held from the UPDATE until COMMIT, so a 2 ms hold caps that one key near 500 commits per second and a 5 ms hold near 200, regardless of how many application instances exist — extra instances only lengthen the waiter queue and pin connections. Put a 300 ms payment authorization inside the same transaction and the ceiling falls to roughly three per second, which is the single most common way this design is destroyed.
Price each mitigation rather than listing them: sharding one row into N sub-rows makes remaining stock a SUM over N and strands units in shards nobody selects; a single-writer partition per variant removes contention but bounds latency by queue depth; admission control protects the database only if the customer is shown a real waiting state instead of an error.

Follow-up

The expiry sweeper releases a hold at the same instant the order service commits it. Write both statements so a committed reservation can never be expired.
reserved_units has drifted 40 units above the sum of active reservations on one row. Write the reconciliation query, and say what it should do when it finds that at 3am.
Two nodes can both satisfy the line. Where does node selection happen, and what does it do to your single-row contention argument?

Explain how you would architect an inventory tracking system to prevent overselling during high-concurrency flash sale events.

Medium · system design and architecture

Approach

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

Follow-up

What breaks first when traffic grows ten times?
How does this behave when that dependency is down for an hour?

Design a notification dispatch service handling push, email, and SMS triggers with distinct rate limits per channel.

Medium · system design and architecture

Approach

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

Follow-up

What breaks first when traffic grows ten times?
How does this behave when that dependency is down for an hour?

Serve available-to-promise reads without letting the cache decide

Easy · read-heavy · caching · invalidation · cache stampede

Search, product pages and cart all ask the inventory service whether a variant can be promised: 10,000 reads/second at peak over two million variants across forty nodes, against roughly two orders of magnitude fewer writes. The source is inventory_position(variant_id, node_id, on_hand_units, reserved_units, damaged_units, in_transit_units, safety_stock_units, oversell_allowance_units, expected_receipt_at_utc). Write the available-to-promise expression with its exclusions named, then design the read path: what is cached, keyed how, invalidated by what, and precisely what a cached answer is allowed to assert. State what a caller may do with a cached 'available'.

Approach

Write the expression per (variant_id, node_id) and commit to its exclusions in one place: max(0, on_hand - damaged - reserved - safety_stock + oversell_allowance). In-transit units are added only when the promise date falls at or after expected_receipt_at_utc, which is a policy decision that must be written down, because two services deriving it independently from the same columns will disagree and the disagreement surfaces as an oversell nobody logged.
Note that network availability is a SUM over the nodes eligible to ship to that destination, so it is destination-dependent and must not be cached as one number per variant. Cache the per-node integers and compute the sum per request over the small eligible set.
Give the cache a one-way contract: a per-(variant, node) integer with a short TTL in seconds plus jitter, allowed to answer 'definitely zero' and short-circuit, never allowed to grant a claim. Redis replication is asynchronous, so a failover can drop writes that were already acknowledged; if the cache could authorise, those lost writes would be reservations customers are mid-payment on. Every binding decision goes to the conditional UPDATE inside the reservation transaction, judged by affected-row count.
Invalidate by event, not by hope: publish position-change events from the same transaction through an outbox, partitioned by variant_id so per-variant ordering survives, and have consumers delete the key rather than write through. A dropped invalidation then degrades to a TTL-bounded stale read instead of a permanently wrong value, and a stale read is safe precisely because the cache cannot authorise.
Protect the origin against the shape this traffic actually has. Demand is heavily skewed, so a hot key expiring at peak sends the full read rate to the database in one instant; single-flight coalescing collapses that to one origin query, jittered TTLs stop keys expiring together, and negative caching absorbs the misses a sold-out drop generates.
Define the degraded mode: if the cache is unavailable, cap origin concurrency with a bounded queue and serve 'availability checked at checkout' rather than an error, and never invert to 'in stock' on failure, since the only safe default is the one that cannot oversell.

Worked solution (20 min)

Write the ATP expression as one line of SQL and list every excluded quantity with the reason for excluding it.
Specify the cache key, value, TTL range and the eviction trigger, and write one sentence stating what the cache may not be used for.
Sketch the outbox event: variant_id partition key, a monotonic version, and the consumer rule that discards any event whose version is not above what it holds.
Compute the origin load when the hottest key expires at peak with and without single-flight, using the peak read rate as the input.

Expected result: An ATP formula whose exclusions are explicit and identical in every consumer; a cache contract that permits a negative short-circuit and forbids a positive commitment; an event-driven invalidation path that fails safe to staleness; and a stated origin-load figure showing single-flight turning a hot-key expiry from thousands of queries into one.

Verify it yourself

Two services given the same inventory_position row compute the same ATP, including the in-transit case.
Deleting every cache entry changes latency and origin load but changes no answer's correctness.
The design contains no path in which a cache read alone decrements or promises a unit.

Follow-up

A node goes offline for a day. What happens to its positions in the ATP sum, and who decides?
last_counted_at_utc is six weeks old for a variant. Should that change what the API returns, and how would you express the doubt?
How do you measure whether the cache is actually wrong, as opposed to merely stale within its TTL?

Order history page slows as its line count grows

Easy · n+1 · batching · latency

The account order-history endpoint returns a customer's last 20 customer_order rows with their order_line rows, and for each line renders a display title read from product_variant plus tracking read from shipment. p95 is 380 ms; the single-order view is 40 ms. The database logs 157 statements for one request, none slower than 1 ms, with flat CPU and no bad plans. Diagnose the latency, name the second defect the same code path carries, give the fix, and state the statement count you expect afterwards.

Approach

Read the shape out of the count before forming a theory. Twenty orders averaging 3.4 lines gives 68 lines, and 1 + 20 + 68 + 68 = 157: one driver query, one per order, two per line. Every statement being sub-millisecond rules out plans, locks and data volume, so the time is round trips.
Check the arithmetic accounts for the whole regression before fixing anything. 156 extra round trips at roughly 2 ms each is about 310 ms on top of a 40 ms baseline, which covers the observed 380 ms. Had it covered only half, there would be a second latency defect and the batching fix alone would disappoint.
Identify the per-row statements by their normalised text: a lines-by-order_id lookup, a single-row product_variant lookup keyed by variant_id, and a single-row shipment lookup keyed by shipment_id. Confirm they are lazy relationship loads by dropping those two fields from the response and watching the count fall to 21.
Rewrite the round trips away first, one batched statement per level. Select the orders, then SELECT ... FROM order_line WHERE order_id = ANY($1), then SELECT ... FROM product_variant WHERE variant_id = ANY($1), then SELECT ... FROM shipment WHERE shipment_id = ANY($1), and join them into maps in application memory. That is four statements, down from 157, and the count is now independent of both orders and lines per order.
Name the correctness defect the batching accidentally exposes: the display title is being joined back to product_variant, which is mutable and carries no validity interval, so a renamed or discontinued variant silently rewrites a historical order. order_line.title_snapshot and unit_price_minor exist precisely so the order does not read the live catalogue. Read the snapshot and the product_variant batch disappears entirely, leaving three statements: the orders, their lines, and the shipments.

Follow-up

The endpoint filters on customer_id. What happens to guest orders, which carry customer_id NULL and are identified by contact_email?
A wholesale account has 4,000 orders. What breaks about ANY($1) and keyset pagination here, and in which order do you fix them?
No statement was slow, so no slow-query alert fired. What signal would have caught this before a customer did?

Suggested practice checklist

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

Day 1 — Recruiter screen and online assessment

Write your career summary and the questions for your recruiter: which rounds your loop includes, whether the Life Story comes early or late, and the rules for pair programming (IDE or CoderPad, language, AI tools)
Solve two practical coding problems from the bank categories, such as a Tic-Tac-Toe winner check and top-three-by-average with a min-heap, in a plain editor with a test harness written first
Do one timed set of logic and pattern-reasoning questions to get used to the aptitude format

Deliverable: A career summary, a list of recruiter questions, and two solved problems each with a harness covering empty and edge inputs.

Day 2 — Pair programming: grid simulator built in steps

Build a grid robot command simulator with state, command parsing and movement rules in separate units, and write tests for the base moves
Add obstacles as a second requirement, then a second robot, and record exactly which units changed
Explain the design aloud as you would to a pairing partner, including one moment where you ask a clarifying question before coding

Deliverable: A working, tested simulator in its base and extended versions, plus a note on which units changed at each step.

Day 3 — Pair programming: classic practical builds

Implement an LRU cache with O(1) get and put, test the eviction order, and write one paragraph on what thread safety would require
Implement a per-merchant sliding-window rate limiter and a URL shortener with collision handling, testing the window-edge and collision cases
Ask an AI assistant for one of these implementations, then review it line by line and fix or explain every issue you find
Work the reviewed coding exercise 'Roll up facet counts over a category graph without double counting' and check your result against its checks

Deliverable: Three tested implementations, a written review of one AI-generated solution, and the completed facet-rollup exercise.

Day 4 — System design for the deep-dive stage

Design webhook delivery with at-least-once semantics: idempotency keys, retry with exponential backoff, and isolating failing endpoints
Design flash-sale inventory so it cannot oversell, then work the reviewed exercise 'Serve available-to-promise reads without letting the cache decide'
Work the SQL exercise 'Retire a variant without breaking historical order lines' and the reserve-statement drill to practise talking about data integrity

Deliverable: Two design write-ups, each naming its failure modes and recovery paths, plus the completed design and SQL exercises.

Day 5 — Technical deep dive: your own project

Choose two or three past projects and for each write the data flow, service boundaries, key data structures, one trade-off with the alternative you rejected, and one real metric
Prepare one production incident or performance bug from those projects: how it was detected, how it was diagnosed, and the fix
Present one project aloud and have a partner interrupt with 'why not X?' questions until you reach something you cannot answer, then fill that gap

Deliverable: A one-page brief per project and a list of follow-up questions you now have answers for.

Day 6 — Life Story and behavioral answers

Write the full career timeline with the reason behind each move, and rehearse it aloud in a long version and a compressed version
Prepare stories for the reported prompts: unclear leadership direction, a failed critical initiative, staying technical while leading, and why Shopify
Practise one of this guide's behavioral drills, such as arguing against a design you were assigned to build, and give specific numbers in your answer

Deliverable: A rehearsed timeline and four stories, each with a specific decision, a consequence and what you changed afterwards.

Day 7 — Mock loop and review

Run a pair-programming mock on a new grid or cache problem with a partner who adds a constraint partway through
Follow it immediately with a deep-dive mock on one of your projects and a short Life Story walkthrough
Go through this guide's losing points one by one and write the single fix you will apply for each

Deliverable: Mock notes marking where each session slipped, plus a one-page checklist for interview day.

Outage answers are graded on instruments

The Life Story interview goes through your career in order and asks about the reasoning behind each decision, so prepare specifics for each move rather than a summary. Give each move a concrete situation, the options you had, why you chose as you did, and what it taught you. For failures, state the real cost and what you changed afterwards.

Walk me through your career, explaining the key decision point behind every role change, promotion or company move.
Describe a project where leadership direction was ambiguous or flawed, and explain how you moved the team toward a good outcome.
Tell me about a time you failed to deliver a critical initiative, what you learned, and how it changed your approach to engineering.
How do you stay technical and keep expanding your skills while taking on more project leadership?
What draws you to Shopify specifically, and how does your work connect to the merchants its platform serves?
Describe a time a cross-functional stakeholder challenged one of your architectural decisions, and how you resolved it.

Argue against holding reservations in a cache with a TTL

Hard · cache coherence · reservations · design review · disagreement

A lead specifies that checkout should hold reservations in an in-memory cache with a fifteen-minute TTL and write back to inventory_position asynchronously, on the grounds that the database row is the bottleneck. You believe it is wrong and you have been assigned to build it. Describe a time you argued against a design you were told to implement. State the failure you predicted, the evidence you brought, how long the disagreement ran, what you did once the decision went against you, and what production eventually showed. Include the version of your argument that persuaded nobody.

Approach

Establish the failure precisely, and note that there are two independent ones. First, replication in a typical in-memory store is asynchronous, so a failover can promote a replica that is missing writes already acknowledged to the client, and here those lost writes are holds on units customers are mid-payment for. Second, and separately, a TTL that expires while an authorization is in flight releases units to another buyer, producing an oversell that no component logs as an error.
Pre-empt the durability counter-argument, because that is what keeps the design alive: an append-only file flushed once a second bounds loss on a single node to roughly a second of writes and says nothing about what a failover discards. Durability knobs on a single node do not make a replicated cache a transactional store.
Attack the premise rather than the taste, because the premise was a performance claim and is therefore measurable. The claimed bottleneck is one row's lock hold time, which runs from lock acquisition to commit: at a 2-5ms hold that is a few hundred updates per second on that key, and the number collapses only if something slow sits inside the transaction. Measure the current hold time and find out whether the external call is inside it before redesigning around the symptom.
Bring evidence the decision-maker can check in a day, not a quarter: the measured p99 hold time on the hot row, and a count of committed reservation units exceeding the position for any variant over the last week. An argument that costs the other person one query is the one that moves.
State the alternative in one sentence with its cost owned: the cache is a negative filter that may say 'definitely none left' and never 'yes, it is yours', the binding decision is a conditional UPDATE in the same store as the reservation rows judged by affected-row count, and the cost is that you now confront the real single-key ceiling and must pay for sharding, single-writer partitioning or admission control if it binds.
Describe the disagree-and-commit mechanics concretely: what you built, what you instrumented so the prediction was falsifiable, and what threshold would have proved you wrong. Report the outcome including the chance you overstated severity, and keep 'I was right' separate from 'the disagreement was handled well'.

Follow-up

You lost the argument. What do you instrument so the question is settled by data in a month rather than by another meeting?
Suppose the measurement shows the row genuinely tops out below the drop's arrival rate. Which mitigation do you pick, and what does it break?
What evidence would have made you drop the objection entirely?

Ship a limited promotion with named, scheduled debt

Medium · technical debt · promotions · concurrency · risk

A promotion launches in two weeks and the date is fixed. To make it, the usage counter on the price rule would increment outside the transaction that commits the reservation, so a limited-quantity discount can be granted more times than it was funded for. Describe a time you shipped a known defect on purpose: how you bounded the exposure in money before agreeing, what you wrote down, who signed it, how you instrumented it, and whether the debt was ever actually repaid. Give the numbers you used, not the reassurance you gave.

Approach

Convert the defect into a bounded money figure before you agree to anything, because 'we might overspend' is not a decision input. The overspend is roughly the number of in-flight checkouts at the moment the counter reaches its limit, and the exposure is that count multiplied by the per-redemption discount. If you cannot estimate the concurrency, that is the measurement to take first.
Prefer a cheap bound over a cheap fix. A hard cap on total discount at the promotion level turns an unbounded defect into a known maximum liability, and it is usually an afternoon of work, whereas moving the counter into the reservation transaction is the correct fix and is not two weeks of work you have.
Write the debt down in a form that survives the launch: the defect, the bound, the trigger condition that makes it urgent, and a named owner. A ticket with only a date attached is the version that never gets done, because the date passes silently and nothing escalates.
Instrument it so you find out rather than hear about it: reconcile the price rule's counter against the actual count of order lines carrying that discount, on a schedule, and alert on the gap rather than on the counter.
Say who agreed in a way that includes the money. The person accepting the risk should be the person who owns the promotional budget, and the record of that acceptance should be written, because the conversation you remember and the one they remember diverge within a month.
Report what happened to the debt honestly, including the case where it was never repaid and the code is still there. Interviewers are testing whether you track debt or merely narrate it.

Follow-up

The promotion overspends by 4% and finance asks whether it will recur next quarter. What do you tell them, and what do you change first?
Product wants the same shortcut for the next launch because it worked. How do you respond?
What would have made you refuse the date instead of shipping the defect?

Own the postmortem for an oversell during a drop

Medium · incident response · oversell · blast radius · postmortem

A limited drop put 800 units of one variant on sale. Checkout read availability from a cache, then wrote the reservation, and a cache failover mid-sale let 1,140 units be committed against 800 on hand. A warehouse pick exception surfaced it three hours later. Take the on-call role. Describe an incident you owned of comparable blast radius: how it was detected, how you bounded the affected orders, what you stopped first, and what customers were told. Give a wall-clock timeline, the query that sized the damage, and the change that would have prevented it.

Approach

Open with the invariant that broke rather than the symptom: committed units at a node must not exceed on_hand minus damaged plus the node's oversell allowance. Stating it that way tells the listener exactly what to count and makes the next sentence a query instead of an adjective.
Size the population with the query, out loud: SUM(units) over inventory_reservation WHERE state='committed' GROUP BY variant_id, node_id, joined to inventory_position, keeping rows where the sum exceeds on_hand_units - damaged_units + oversell_allowance_units. Then order the affected order_line rows by the reservation's created_at_utc to identify which claims are beyond the physical cutoff. Say whether that ran against a replica while the incident was live.
Separate mitigation from fix and say which came first. Mitigation is blunt and cheap: pull the variant from sale, or set ATP to zero for that node, which stops the bleeding in a minute. The fix is a conditional UPDATE judged by affected-row count with the CHECK constraint behind it, and that is not an incident-window change.
This domain's remediation is physical and therefore asymmetric, so name the branches: lines still in 'reserved' or 'released_to_node' can be cancelled and the authorization voided at no fee; lines already carrying a carrier_tracking_code cannot be cancelled at all and the only compensation left is a return authorization. Who gets cancelled is a policy call you had to make under time pressure — say what rule you used and who approved it.
Close on one prevention change with its cost, not five nobody staffed. A reconciliation job recomputing reserved_units from the reservation rows on a schedule would have caught the drift within its interval; say what interval you chose and what it pages on.
Name an error you made inside the response window, not only in the retrospective — the mitigation that made it worse, or the twenty minutes spent on the wrong hypothesis. That part is what distinguishes a lived incident from a rehearsed one.

Follow-up

Two hundred of the oversold lines already have tracking codes. What do you do with them, and what does that cost compared with a cancellation?
How would you have detected this in five minutes instead of three hours, and how many false pages per week would that detector produce?
Merchandising asks you to guarantee it cannot recur. What can you actually promise, and what can you only bound?