Meta Solutions Architect Interview Preparation Guide - Entry Level

Direct answer

Do not referee the argument on either side's terms. Get the product manager and the sales leader to agree on the criteria a good decision should satisfy, such as impact, risk, cost, and strategic fit, before evaluating the two initiatives against them, make the actual trade-off explicit and visible to both, and if they still cannot converge, fall back to a pre-agreed neutral path, such as a specific owner or forum, rather than letting whoever argues harder win by default.

Structured elaboration

Separate positions from underlying interests

"We should build my initiative" is a position. The interest underneath it, such as protecting a committed relationship, capturing a strategic capability, or hitting a revenue target, is what actually needs to be satisfied, and there is sometimes more than one way to satisfy it.

Agree on decision criteria before ranking anything

If the criteria are picked after looking at the options, whoever's initiative fits best gets to claim the criteria were obviously the right ones. Agreeing on what matters, and roughly how much, before scoring anything removes that bias.

Make the trade-off explicit, not implied

State plainly what saying yes to one costs the other: displaced timeline, deferred capability, real opportunity cost. Vague trade-offs, such as trying to do both eventually, just relocate the disagreement to a later date.

Have a pre-agreed fallback for genuine ties

Sometimes both initiatives are legitimately close in value. Decide in advance who breaks the tie, such as a shared manager or a specific forum, and under what timeline, so a genuine tie does not become an open-ended standoff.

Worked example

A product manager wants to build a feature that improves activation for the broad user base. A sales leader wants a different feature that would help close several deals already in the pipeline. Both have real reasoning behind their case, but the reasoning is not answering the same question, so comparing the two head-on produces a false comparison, not a decision.

The facilitator, an engineering manager in this scenario, brings both together and gets agreement on criteria first: how many users or how much revenue does each option affect, how reversible is each choice if it turns out to be wrong, and how well does each align with the stated strategy for the quarter. Evaluated against the same criteria, it becomes clear the two initiatives are not actually mutually exclusive at full scope: a smaller version of the sales-driven feature can ship inside the existing capacity without displacing the activation work, while the full-scope version is deferred to the next quarter with an explicit commitment. Both leaders leave with a decision they can each explain to their own stakeholders, because they agreed on the criteria before either one saw how the evaluation would land.

Trade-offs and pitfalls

Forcing an artificial "both, just smaller" compromise when the initiatives are genuinely incompatible produces two half-built things instead of one well-built thing. The compromise only works when the criteria step reveals real headroom, not as a default peacekeeping move.

Over-structuring a call that both sides would have agreed on anyway wastes time and can read as bureaucratic theater. And skipping the criteria-first step under time pressure, going straight to picking one, reintroduces exactly the dynamic, whoever argues harder wins, that a structured process exists to avoid.

Choosing between a small hotfix and reverting to the previous stable release comes down to which action more reliably and quickly restores correctness with the least additional risk.

Decision criteria

Prefer a hotfix when the root cause is well-understood, the fix is small and isolated (touches little beyond the specific broken behavior), and reverting would also roll back unrelated, wanted changes that shipped in the same release. Prefer a revert when the cause isn't yet fully understood (a revert restores a known-good state without needing to be right about the cause), the "small" fix would actually need to touch several places, or there's any doubt the hotfix itself is fully safe under production conditions.

Two concrete examples

• Hotfix preferable: a null-check is missing on one specific field that a new client type started sending; the fix is one line, well-understood, and reverting would also undo unrelated bug fixes shipped in the same release.
• Revert safer: a new feature interacts with three other systems in ways not yet fully mapped, and a stakeholder needs the feature live today despite the fix genuinely needing more than a day; here, reverting removes both the feature and the risk cleanly, buying time to fix it properly without the pressure of an active production issue.

Risk assessment and customer impact

Weigh: confidence in root cause (low confidence favors revert), blast radius of the broken behavior (wide impact favors whichever restores service fastest), and what else would be lost by reverting (favors hotfix if the release bundled other now-live fixes worth keeping).

Trade-offs and pitfalls

A partial hotfix (fixes some cases but not all) is worse than either a clean revert or a complete fix, since it can create a false sense the issue is resolved while a subset of users remain affected; if a hotfix can't be verified to be complete, defaulting to revert is usually the safer choice.

Direct answer

With no historical traffic, you do not guess a single number: you build a workload estimate from comparable analogs and top-down business inputs, wrap it in an explicit safety margin, put it behind elastic capacity so the estimate does not have to be exact, and then replace the estimate with real data as fast as possible after launch through staged rollout and monitored experiments.

Structured elaboration

1. Build an initial estimate from two independent angles and reconcile them.

• Top-down: start from a business number you do have (invited users, marketing reach, sales pipeline) and multiply down to requests. This is the only lever available with zero history.
• Analog: find the closest comparable system you or the industry already operates (a similar feature, a similar-sized customer base, a similar product category) and scale its known request-per-user rate to your expected user count.
• Reconcile the two. If they disagree by more than roughly 2-3x, that gap itself is useful information: it tells you where your uncertainty is concentrated and what to instrument first.

2. Convert the estimate into a load shape, not just a total.
A daily total hides the number that actually threatens the system: peak requests per second (RPS, requests per second). Apply a peak-to-average ratio to account for daily cycles and, for a launch specifically, a possible synchronized spike (a launch email, a push notification, a press mention) that behaves nothing like organic steady traffic.

3. Set headroom deliberately, and say why.
Headroom on a zero-history estimate covers two different kinds of error: normal variance (traffic is noisier than a smooth average implies) and estimate error (the whole model could be wrong). Treat these as multiplicative: a peak-shape multiplier for the first, then a separate safety-margin multiplier for the second. Document both numbers as assumptions, not facts, so whoever revisits capacity later knows which parts were guessed.

4. Plan for elastic capacity so the estimate does not have to be right.
Because pre-launch numbers are inherently soft, favor a design where compute scales out automatically (for example an Auto Scaling group, ASG, sized with a low minimum and a generous maximum) over one where you provision a fixed fleet sized to the estimate. Stateless request handlers are what make this possible: any instance can pick up any request, so the ASG can add or remove capacity without session-affinity constraints. Identify the one component that will NOT scale elastically as fast as the rest (usually the database or a rate-limited third-party dependency) and size or protect that one deliberately, since it becomes the real ceiling regardless of how large the compute fleet grows.

5. Define what you will measure and how you will validate the assumption after launch.
Before launch, decide: the metrics that reveal reality (RPS, P95/P99 latency [95th-percentile/99th-percentile], error rate, queue depth, database connection saturation), the rollout mechanism that limits blast radius while those metrics come in (percentage-based ramp or canary release to a small traffic slice first), and the trigger for pausing the ramp (an explicit threshold on any of the above, decided in advance rather than improvised under pressure).

Worked example

Assume, as planning inputs rather than measured facts:

• 10,000 users are active on day one (from a marketing pre-registration count, discounted for expected activation rate).
• Each active user generates 15 requests over the day (from an analog product's per-user request rate).
• A peak-to-average ratio of 4x, reflecting a synchronized launch announcement rather than smooth organic arrival.
• A safety margin of 2x on top of the peak, to absorb estimate error since there is no history to validate the inputs against.

That "14 RPS" is not a forecast you defend, it is a starting point for the ASG's scaling policy and a number you replace with observed data within the first days of traffic.

Trade-offs & pitfalls

Over-provisioning a fixed fleet to the safety-margin number wastes money for a launch that may undershoot; under-provisioning without elastic headroom risks a visible outage on the day traffic is most scrutinized. The middle path (a small guaranteed baseline plus autoscaling) is usually right, but it only works if the service is stateless and the true bottleneck (often the database, not the request tier) is identified and protected separately, since databases scale far less elastically than compute. The most common senior-vs-junior tell is whether the candidate treats the initial number as a fact to defend or as an assumption to instrument and correct quickly after launch.

Direct answer
State the decision and the immediate next steps out loud before the meeting ends, then send a short written follow-up within the hour that restates the same thing, so there's both an in-the-room confirmation and a durable record that matches it.

Structured elaboration

1. Confirm verbally before people leave the room (or call). In the last minute or two, say "so to confirm, we've decided X, and the next steps are Y owned by Z by Thursday, does that match everyone's understanding?" This catches a misalignment while everyone who can correct it is still present.
2. Watch for silence versus agreement. Nobody objecting isn't the same as everyone actively agreeing; a direct question ("does that match?") is more reliable than just pausing and moving on if no one immediately speaks up.
3. Send the written follow-up promptly, ideally within the hour, restating the same decision and action items. The verbal confirmation and the written one should say the same thing; if they don't, that's usually a sign the verbal confirmation was rushed or unclear.
4. Keep the written version short and scannable, matching the same content as the verbal confirmation rather than adding new information the room didn't actually agree to.
5. Flag anything genuinely still unresolved, in both the verbal check and the written follow-up, rather than letting an unresolved point quietly look settled just because the meeting ended.

Worked example
Verbal, at the end of the meeting: "So to confirm: we're going with the phased rollout, Sam owns the migration plan by next Friday, and we're holding off on the customer announcement until that's done. Does that match what everyone heard?"

Written follow-up sent the same hour: "Recap from today: decided on the phased rollout. Sam: migration plan due next Friday. Customer announcement is on hold until the migration plan is ready. Shout if this doesn't match what you remember."

The two versions state the identical decision and owner, and the written version explicitly invites correction rather than assuming silence means agreement.

Trade-offs and pitfalls

• Skipping the verbal confirmation and only sending a written recap later means any misunderstanding surfaces after people have already left and possibly acted on their own interpretation.
• Skipping the written follow-up and only confirming verbally means anyone who wasn't in the room, or who forgets, has no record to check against.
• A written recap that silently adds detail beyond what was verbally confirmed can create a new source of disagreement; keep the two consistent, and if you realize something needs adding, flag it explicitly as new rather than folding it in unannounced.

Building custom editor tooling inside a commercial engine is about reducing repetitive manual work, enforcing project-specific rules, and surfacing problems early in the pipeline so teams iterate faster and ship higher-quality content. For a content-heavy title I’d deliver a “Batch Validator + Pipeline Exporter” tool tailored to the client’s asset rules and delivery formats.

Tool overview:

• Purpose: run project-specific validation across large sets of assets, auto-fix common problems, and export validated assets into downstream formats (platform bundles, LODs, texture atlases, metadata manifests).

Core components:

1. Validator engine: rule-driven checker (naming conventions, polycount, UV overlap, texture resolution, animation length, missing references, LOD presence). Rules are configurable via JSON/YAML so producers or TDs can update without code.
2. Auto-fix library: deterministic fixes (rename, reassign material, resize textures, generate LODs, bake tangents) with dry-run and preview.
3. Pipeline exporter: mapped export profiles (PC/mobile/console) that package assets, run compression presets, and produce manifests for build systems.
4. UI/UX: dockable editor window with multi-select, paginated results, severity filters, inline diff/previews, per-asset history, batch action queue and progress bar.
5. Integration hooks: CLI for CI, engine import pipeline hooks, and alerting (slack/build failure) when validation fails.

User experience:

• One-click “Validate Project” or scoped validate (folder/tag).
• Clear per-asset findings, suggested fixes, and “Apply Selected Fixes” with undo.
• Export profiles saved per-project; preview package before commit.
• CI integration runs validator on PRs and fails export if high-severity issues exist.

How it improves iteration and QA:

• Shifts detection left: catches errors locally before QA or automation runs.
• Reduces manual grind: batch fixes cut turnaround from hours to minutes.
• Enforces consistency: configurable rules prevent regressions across large teams.
• Automation-ready: CI gating ensures broken assets don’t land in builds.

Success metrics to track:

• Reduction in asset-related build failures, average time to fix asset issues, and time spent per asset in review. These quantify ROI and make the case to stakeholders during sales and architecture discussions.

Direct answer. Assemble the feed with a fan-out query keyed on the follower list plus a keyset cursor over (post timestamp, post id), fetching posts in batches per followed author rather than joining the full followers table against the full posts table in one shot, which is what actually causes the N+1-style blowup at this scale.

Avoiding N+1 and full joins. The naive approach (for each follower, query their followed authors, then for each author query their posts) is literally N+1. The fix is not a single giant JOIN either, which forces the database to materialize and sort a huge intermediate result before it can apply the LIMIT; instead, pre-compute or cache the list of authors a user follows once per request, then run ONE keyset-paginated query against a posts table that is indexed by (author_id, created_at, id), using an IN clause over the (bounded) follower list, so the database can use the index to fetch only the rows it actually needs for this page, in the same round trip.

Keeping the cursor compact and opaque. Encode the cursor as the last returned (post timestamp, post id) pair only, not the full state of which authors have been exhausted; base64-encode it (optionally with an HMAC signature: HMAC, Hash-based Message Authentication Code, is a keyed checksum, meaning the server computes it over the cursor's bytes using a secret only it knows, and if a client changes even one byte, recomputing the checksum on the next request won't match, so tampering is caught rather than silently trusted; this is what keeps a client from tampering with it to page into another user's private feed) so it travels as one short opaque string in the response, not a structured object the client could inspect or manipulate.

Worked example. Suppose the last row on page 1 is (created_at = 2026-07-20T14:02:00Z, id = 9821). The server builds the payload {"id":9821,"ts":"2026-07-20T14:02:00Z","v":1}, base64-encodes it to eyJpZCI6OTgyMSwidHMiOiIyMDI2LTA3LTIwVDE0OjAyOjAwWiIsInYiOjF9 (verified: this is exactly what base64.b64encode() produces for that exact payload string), then computes an HMAC-SHA256 signature over that base64 string using the server's signing key. Illustrated here with a demo key, b"demo-server-signing-key" (in production this is a real secret held only by the server and never shown to the client), the signature truncated to 12 hex characters is b84145a14d15 (verified: hmac.new(key, b64_string.encode(), hashlib.sha256).hexdigest()[:12]). The server ships the two joined by a dot as the opaque cursor: eyJpZCI6OTgyMSwidHMiOiIyMDI2LTA3LTIwVDE0OjAyOjAwWiIsInYiOjF9.b84145a14d15. Page 2's request sends this string back verbatim; the server decodes the payload, recomputes the HMAC over it with the same key, confirms it matches, and only then runs WHERE (created_at, id) > ('2026-07-20T14:02:00Z', 9821) to fetch the next batch. If a client edited the payload's id to 9822 without a valid signature for that new value, recomputing the HMAC over the tampered payload with the same key produces a different signature (edbc982b49ef, not the original b84145a14d15), so the mismatch is caught immediately and the request is rejected rather than silently paging the attacker past their real position.

Correct ordering as new posts arrive. Because the cursor anchors on (timestamp, id) rather than a row count, a new post from a followed author inserted after the client's last-seen cursor position is picked up naturally on the next page request; it does not shift the position of posts the client has already seen, the same correctness property as the general cursor design, just applied across a fan-out of many authors instead of one table.

Signing and versioning the cursor. Sign the cursor payload (HMAC with a server-held key) so a client cannot forge a cursor claiming a later position than they are actually entitled to (which would let them skip content-moderation-relevant posts, or, worse, read into a private list they should not see). Version the cursor's binary format explicitly (a version byte at the front of the payload) so a schema change to what the cursor encodes does not silently misparse cursors a client is still holding from before the change; an unrecognized version returns 400 rather than attempting to decode a token whose fields no longer mean what the server expects.

Trade-offs and pitfalls. The subtlest mistake is signing the cursor but not versioning it: a schema migration that changes what fields the cursor carries will still verify (the HMAC only proves the bytes were not tampered with, not that the server can still interpret them), and a naive decoder can misread a byte offset as a valid but wrong value instead of failing cleanly.

Direct answer

In forty-eight hours I am not trying to understand the technology, I am trying to deliver one narrow, correctly-working slice of it and be honest about everything I did not verify. I spend the first couple of hours scoping exactly what "real" has to mean for the deliverable, deliberately decide what to fake, stub, or hard-code outside that slice, and I protect the work by verifying the riskiest part by hand rather than trusting untested intuition, then naming the residual risk explicitly to whoever receives the work.

Structured elaboration

• Scope ruthlessly from the actual deliverable backward: what is the smallest real thing that satisfies the ask, and what can be stubbed, mocked, hard-coded, or simply omitted for now.
• Name out loud what is being skipped and why: edge cases, error handling for paths not exercised, configuration options, anything the tool offers that this specific window does not need.
• For the part that has to be real, verify by hand what you cannot yet trust your own understanding to catch: manually walk a request through, check a response against documentation line by line, rather than relying on "it looked right" for the piece that matters most.
• Where existing knowledge partly maps from something familiar, be explicit with yourself about which parts of that intuition are actually being verified and which are just being trusted, since a partial map is exactly where false confidence creeps in.
• Flag residual risk explicitly to whoever receives the work: what was not verified, what could break outside the narrow case tested, and what should be checked next if this needs to become durable.

Worked example

With about forty-eight hours' notice, I was asked to integrate a third-party payment provider's webhook into a live service for a stakeholder demo the next day, having never touched that provider's interface before. I scoped the real slice tightly: handle exactly one webhook event type correctly, with real signature verification, since faking that would be dangerous even in a demo, and hard-coded a canned response for every other event type in the provider's catalog rather than trying to handle all of them. I verified the signature-verification code by hand against the provider's documented example payload and hash, byte by byte, rather than trusting that it compiled and ran without error, since that was exactly the part I could not yet trust my own instincts on. I left retry and duplicate-delivery handling explicitly out of scope, wrote that down in the change description, and told the person receiving the work directly that a duplicate webhook delivery would currently be processed twice, so it was not safe to treat as production-ready before that gap closed.

Trade-offs and pitfalls

• The biggest failure mode under this kind of compression is quietly treating "it ran once without an error" as proof of correctness; hand-verifying the riskiest slice is exactly what prevents that.
• Skipping too aggressively can produce a demo that looks complete and creates false confidence that the hard part is done, when the hard part was actually the part left out; naming what was skipped, out loud, is what prevents that.
• Leaning on knowledge that only partly maps from a familiar tool is efficient but dangerous if the transferable parts are not separated from the parts that merely look similar.

Direct answer
Backward compatibility (new code can read data written by old code) and forward compatibility (old code can read data written by new code) both come from a small set of protobuf (Protocol Buffers, Google's binary serialization format) discipline rules: never reuse or renumber a field, never tighten optional to required, always let unrecognized fields pass through untouched instead of erroring, and always give new fields a sensible default so an old writer's absence of that field is a valid state, not a missing-data error. The way to catch a break before deploy is not code review alone, it is an automated schema-diff check in CI plus round-trip serialization tests that actually exercise old-writer/new-reader and new-writer/old-reader in both directions.

Structured elaboration: the six hazards, why each breaks compatibility, and the test that catches it

Worked example: an Order message evolving from v1 to v2
v1: message Order { int32 id = 1; string customer_email = 2; }
v2 adds a field and removes none: message Order { int32 id = 1; string customer_email = 2; OrderStatus status = 3 [default = PENDING]; }

Four cross-version pairings must all be tested explicitly, not just the same-version case:

1. v1 writer, v1 reader: baseline, must still pass (regression guard).
2. v2 writer, v1 reader: the v1 reader receives bytes for field 3 it does not know. Assert the v1 reader's deserialized Order still has correct id and customer_email, and (if the v1 reader re-serializes and forwards the message, e.g. through a proxy) that field 3's bytes are preserved in the output even though the v1 reader never interpreted them.
3. v1 writer, v2 reader: field 3 is absent on the wire. Assert the v2 reader's status reads as PENDING (the declared default), not a crash, and not silently 0 interpreted as some OTHER enum value if the enum's zero value is ever renamed.
4. A schema-diff CI run against exactly this v1-to-v2 change: assert the diff tool reports "field 3 added, backward and forward compatible" and would FAIL the build if a hypothetical v3 instead reused field number 2 for a different type.

Trade-offs & pitfalls
Automated schema-diff tooling only catches STRUCTURAL breaks (field renumbering, type changes, required-ness). It cannot catch SEMANTIC breaks, where the field number and type stay identical but what the field MEANS changes (the default-value reinterpretation hazard above is exactly this shape), so structural compatibility testing must be paired with an explicit code-review checklist item for "does this field's meaning change for the zero/default value." A second pitfall is treating "it deserializes without throwing" as proof of compatibility: proto3's permissive unknown-field and default-value behavior means a genuinely broken semantic change often deserializes cleanly and fails much later, in business logic, which is why the worked example above asserts on the FIELD VALUES after deserialization, not just on "no exception was thrown." Third, teams sometimes test only forward OR backward compatibility because their rollout is always "new server first," but a rollback (new server reverting to old code while new-format data has already been written) exercises the OTHER direction and is exactly when these bugs surface in production, so both directions belong in the test matrix even if only one direction is the "normal" rollout order.

Requirements & constraints:

• Cross-service workflow across Catalog, Inventory, Billing; operations may fail independently; eventual consistency acceptable; need observability, ability to replay, and easy extension when new services join.

High-level options:

1. Choreography (event-driven): each service reacts to domain events (OrderPlaced → InventoryReserveRequested → BillingChargeRequested → etc.). Pros: decentralized, easy to scale, no central coordinator. Cons: harder to reason globally, increases coupling through event schemas, complex compensations spanning many listeners.
2. Orchestration (saga coordinator): a central Saga Orchestrator drives the steps (invoke Inventory.reserve, then Billing.charge, etc.) and records outcomes. Pros: single place for workflow logic, simpler error handling and observable state. Cons: single control-plane component, potential bottleneck (mitigated by scaling).

Recommendation: Use a hybrid — an orchestrator for core long-running customer-facing flows (gives observability, replay, and easier extension), but allow local choreography for internal eventual-consistency reactions.

Compensating transactions:

• Model each forward step with a corresponding compensating action (Billing.refund, Inventory.release, Catalog.mark-available).
• Compensations must be idempotent, safe for partial application, and explicitly modeled in the saga definition.
• Define compensation semantics: immediate synchronous compensation on failure, or asynchronous best-effort with retries and alerting for human intervention for irrecoverable states.

Persisting saga state (for observability & replay):

• Use an append-only event store (Kafka or event-store DB) or a durable saga store (Postgres/Cassandra) that records: saga_id, versioned saga definition, step attempts (request/response), timestamps, state transitions, side-effect events, and compensation logs.
• Expose a read-model (materialized view) for dashboards: current saga status, timeline, next expected actions, retries.
• For replay: keep immutable events and allow the orchestrator to replay by rehydrating saga state from events; support versioned handlers so older saga definitions can be replayed reliably.

Reliability & correctness primitives:

• Idempotency keys on RPC/events, message deduplication, at-least-once delivery handling.
• Timeouts and retries with exponential backoff; poison-queue handling and alerts.
• Versioning of saga definitions and event schemas; schema registry for compatibility.

Extensibility when new services join:

• Expose a well-documented saga DSL (YAML/JSON) or GUI where new steps/compensations can be declared and registered without code changes.
• Orchestrator emits domain events for each step completion — allow new services to subscribe (choreography) to react locally.
• Use contract-first APIs and a schema registry for events; publish integration patterns and test harnesses (local sandbox consumers).
• Support hooks/plugins so new services can add pre/post validators or enrichment steps.

Scalability & trade-offs:

• Scale orchestrator horizontally by sharding sagas by saga_id; store should support high write throughput.
• Use eventual-consistency tolerant UI: surface provisional status and finality once saga completes.
• Trade-offs: orchestration simplifies operations and observability at the cost of a central controller; choreography reduces centralization but complicates global recovery. Hybrid gives balance.

Operational considerations:

• Monitoring: per-saga metrics (duration, failure rate), SLA alerts for stuck sagas, replay audit trails.
• Runbooks for manual compensation and remediation endpoints in orchestrator.
• Security, RBAC for who can trigger replays/compensations.

Example flow (orchestrator):

• Start saga Order-123
• Step1: Catalog.reserveItem → success
• Step2: Inventory.reserve → fail → orchestrator triggers Catalog.release (compensation) and records both events; marks saga failed; exposes timeline for replay.

This design gives predictable failure handling, rich observability, and a clear path to add services via versioned contracts and a registration surface for new saga steps.

Direct answer

Both a LEFT JOIN with a NULL check and a NOT EXISTS subquery correctly find orphaned orders (those referencing a customer_id absent from customers), but NOT EXISTS is generally the more efficient and clearer-intent choice at scale, since it lets the planner stop scanning each candidate customer as soon as one match is found or ruled out, rather than materializing a full outer join first.

Structured elaboration

-- Approach 1: LEFT JOIN, then filter for the non-match
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- Approach 2: NOT EXISTS
SELECT o.*
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);

Both are logically equivalent and, on most modern query planners (Postgres included), often compile to a similar anti-join execution plan; NOT EXISTS tends to be more explicit about intent ("this order has no matching customer" rather than "join, then check for an absent match"), and behaves more predictably than a LEFT JOIN + IS NULL when the referenced table has its own NULLs or when the query is later modified to select additional columns from customers, which can accidentally break the NULL-check filter if not written carefully.

Worked example

Verified in sqlite3: with customers(1,2) and orders referencing customer_ids (1, 2, 99), both queries correctly and identically return only the order referencing customer_id 99, the one with no matching row in customers.

Trade-offs and pitfalls

• Performance at large scale depends heavily on indexing, not just which SQL form you choose: both approaches need an index on customers.customer_id (almost always present, as the primary key) and benefit from an index on orders.customer_id if the orders table is large and this check runs often, since without it, either form has to scan the full orders table regardless of the join style chosen.
• The LEFT JOIN + IS NULL form has a subtle correctness trap if customers.customer_id itself could be NULL in the join condition (not the primary key case here, but worth knowing generally): a LEFT JOIN's NULL-check filter can behave unexpectedly if the join column on the "many" side allows NULL, which NOT EXISTS handles more predictably since it explicitly tests for the existence of a match rather than relying on a NULL appearing in a specific joined column.
• Finding orphans is a detection step, not a fix; deciding what to do with them (backfill a missing customer record, reassign to a placeholder, or determine the FK constraint itself was missing and should be added going forward to prevent recurrence) is a separate decision this query only surfaces the need for.