Entry-Level Data Engineer Interview Preparation Guide (FAANG Standards)

Direct answer

Don't try to average everyone's position into a compromise nobody's happy with. Ground the negotiation in the shared outcome, make the trade-offs between options explicit with evidence, and force a real decision (with an owner and a documented rationale) within a fixed timeframe. A compromise sticks when people can see why it was chosen, not just that it split the difference.

Structured elaboration

1. Reframe around outcome, not position. Ask each stakeholder what success looks like for them, not what they want built. Two stakeholders who seem opposed on the "what" often agree on the "why," which is where the real compromise lives.
2. Bring evidence, not opinions. Gather whatever is available and relevant: usage data, cost/effort estimates, prior incidents, qualitative feedback. A room full of opinions negotiates forever; a room with a shared set of facts converges faster.
3. Make trade-offs visible. Lay out 2-3 real options with their costs and benefits side by side, instead of a single proposal to accept or reject. People compromise more easily when they're choosing between concrete alternatives than when they're being asked to give up a specific ask.
4. Use a structured negotiation move. Propose a balanced default option first, then invite each side to request a bounded concession from it, rather than starting from each side's maximal ask and negotiating down. Time-box the discussion so it doesn't drift into re-litigating the same points.
5. Document the decision and name an owner. Write down what was decided, why, who owns it, and when it will be revisited. If the group truly can't converge, escalate with a specific recommendation rather than an open question, so the escalation itself doesn't become another unresolved debate.
6. Build in a review point. Treat the agreement as provisional and testable, not permanent. A short follow-up (after the next milestone, or a fixed number of weeks) to check whether the compromise is actually working keeps people bought in because they know it isn't final and unappealable.

Worked example

Three stakeholders disagree on scope for a feature: one wants the full version shipped now, one wants it deferred a quarter, one wants a stripped-down version shipped immediately. Instead of negotiating "how much scope," the facilitator asks each what outcome they're protecting: the first is protecting a customer commitment, the second is protecting engineering capacity for other work, the third is protecting the team's ability to learn before over-investing. That reframing surfaces a real option none of them had proposed: ship a narrow version that satisfies the customer commitment, explicitly scoped as a first iteration, with the deferred work logged and re-prioritized at the next planning cycle. The decision, the scope boundary, and the re-prioritization date are written down and shared with all three stakeholders.

Trade-offs & pitfalls

• Pitfall: false compromise, where everyone gets a token piece of what they asked for and the result satisfies no one's actual underlying need.
• Pitfall: skipping documentation. An undocumented "agreement" gets re-argued the moment someone's memory of it differs.
• Pitfall: treating consensus as required. Some decisions need a single accountable owner to make the call after input, not unanimous agreement, especially under a deadline.
• Senior differentiator: designing the forcing function (a default option, a timebox, a named decision owner) instead of facilitating an open-ended discussion indefinitely. That's what turns "several people who each want something different" into an actual decision.

Direct answer

Because every row and every column is already sorted, a query needs no extra structure at all to run faster than a plain scan: starting the search from the top-right (or bottom-left) corner and stepping left or down as comparisons dictate finds any value in $O(m + n)$ time with zero preprocessing and $O(1)$ extra space. If memory allows more, the same $mn$ values can be flattened and sorted once, trading $O(mn \log(mn))$ preprocessing and $O(mn)$ space for $O(\log(mn))$ binary-search queries, or hashed into a set once for $O(mn)$ preprocessing time and space with $O(1)$ average-case query time. Given a memory budget, the choice is really about how much of that one-time cost you can afford to pay before the first query ever arrives.

Structured elaboration

The full preprocessing/space/query spectrum

Why the corner search works with no preprocessing

Starting at the top-right corner, every step down increases the value (columns are sorted top to bottom) and every step left decreases it (rows are sorted left to right). Comparing the target against the current cell tells you unambiguously which single direction to move: if the cell is too large, the entire column below it is even larger, so move left; if too small, the entire row to its left is even smaller, so move down. Each comparison eliminates exactly one row or one column, giving at most $m + n$ steps.

Why more preprocessing buys a faster query, and what it costs

Sorting or hashing all $mn$ values loses the matrix's 2-D shape entirely, but that shape was only useful for the zero-preprocessing corner search; once every value is available as one flat sorted list or one flat hash set, the query becomes a standard 1-D lookup. The trade is a one-time $O(mn)$ (hash) or $O(mn \log(mn))$ (sort) cost and $O(mn)$ memory paid once, in exchange for every subsequent query dropping from linear-in-(m+n) to logarithmic or constant.

Picking one under a memory budget

• Budget is effectively $O(1)$ (matrix itself is already large, or queries are rare): corner search, no preprocessing needed at all.
• Budget allows $O(mn)$ but query-time determinism matters more than raw speed (e.g. needing a guaranteed worst-case bound rather than an average case): sort once, binary search each query, $O(\log(mn))$ worst case, guaranteed.
• Budget allows $O(mn)$ and queries dominate the workload (many queries, want the fastest possible average lookup, worst-case hash collisions are an acceptable risk): hash all values once, $O(1)$ average query.

Worked example

Approach

Implement all three: the zero-preprocessing corner search, a sort-once-then-binary-search index, and a hash-once index, then confirm all three agree on the same matrix.

import bisect

def staircase_search(matrix, target):
    # No preprocessing. O(m + n) time, O(1) extra space, per query.
    if not matrix or not matrix[0]:
        return False
    m, n = len(matrix), len(matrix[0])
    row, col = 0, n - 1
    while row < m and col >= 0:
        val = matrix[row][col]
        if val == target:
            return True
        elif val > target:
            col -= 1
        else:
            row += 1
    return False

def build_sorted_index(matrix):
    # Preprocessing: flatten and sort once. O(mn log(mn)) time, O(mn) space.
    flat = [v for row in matrix for v in row]
    flat.sort()
    return flat

def query_sorted_index(sorted_flat, target):
    # O(log(mn)) query time.
    i = bisect.bisect_left(sorted_flat, target)
    return i < len(sorted_flat) and sorted_flat[i] == target

def build_hash_index(matrix):
    # Preprocessing: hash all values once. O(mn) time and space.
    return {v for row in matrix for v in row}

def query_hash_index(value_set, target):
    # O(1) average query time.
    return target in value_set

matrix = [
    [1, 4, 7, 11, 15],
    [2, 5, 8, 12, 19],
    [3, 6, 9, 16, 22],
    [10, 13, 14, 17, 24],
    [18, 21, 23, 26, 30],
]

sorted_flat = build_sorted_index(matrix)
hash_index = build_hash_index(matrix)

for target in (5, 20, 30):
    a = staircase_search(matrix, target)
    b = query_sorted_index(sorted_flat, target)
    c = query_hash_index(hash_index, target)
    print(target, "-> staircase:", a, "sorted-index:", b, "hash-index:", c)

This prints:

5 -> staircase: True sorted-index: True hash-index: True
20 -> staircase: False sorted-index: False hash-index: False
30 -> staircase: True sorted-index: True hash-index: True

All three approaches agree on every target, confirming the corner-search logic and both preprocessed indexes reach the same answer; they only differ in when the work happens (per query, versus once up front) and how much memory they hold onto between queries.

Key points

• The corner search's step direction is decided in $O(1)$ per cell, no lookahead or backtracking is needed, since the sorted-row/sorted-column structure guarantees the eliminated row or column can never contain the target.
• Sorting once amortizes its $O(mn \log(mn))$ cost over every future query; it only pays off once the number of queries is large enough that repeated $O(m+n)$ corner searches would cost more in total.
• The hash-index's $O(1)$ is an average-case guarantee (dependent on hash distribution), not a worst-case one; the sorted-index's $O(\log(mn))$ is worst-case guaranteed.

Complexity

Staircase search: $O(m + n)$ time per query, $O(1)$ extra space, no preprocessing.
Sorted index: $O(mn \log(mn))$ one-time preprocessing, $O(mn)$ space, $O(\log(mn))$ time per query.
Hash index: $O(mn)$ one-time preprocessing, $O(mn)$ space, $O(1)$ average time per query.

Edge cases

• Empty matrix, or a matrix with an empty first row: all three approaches should return False immediately without indexing errors, handled here by the not matrix or not matrix[0] guard in the staircase search (the two indexed approaches naturally return False on an empty structure).
• Target smaller than every value, or larger than every value: the staircase search terminates in at most $m + n$ steps by walking straight to a matrix edge without finding a match.
• Duplicate values scattered across the matrix: none of the three approaches are affected, since all only need existence, not counting or locating every occurrence.

Trade-offs & pitfalls

Building the sorted or hashed index and then never issuing enough queries to amortize its cost is a common miscalculation, if only a handful of queries will ever run against a given matrix, the corner search's zero preprocessing wins outright even though its per-query cost looks worse on paper. A second pitfall is assuming the hash-index's $O(1)$ is a hard guarantee; adversarial inputs or unlucky hash collisions can degrade it, so a system with strict worst-case latency requirements should prefer the sorted-index's guaranteed $O(\log(mn))$ instead. Finally, the corner search is not a general binary-search substitute, it exploits the fact that both rows and columns are sorted; if only one axis were sorted, a slower approach (binary search per row, $O(m \log n)$) would be needed instead.

Direct answer

A proper chaos experiment is a falsifiable test, not a demo: state a hypothesis about what SHOULD happen, define the blast radius that limits who is exposed if the hypothesis is wrong, inject the fault, watch specific metrics against pre-declared rollback criteria, and be ready to abort automatically. Below, this exact structure is simulated with real numbers (a synthetic +800ms feature-store latency injected into a small canary slice of traffic) and the first version of the experiment's own guardrail metric produced a genuinely surprising result once actually run: a 1% blast radius that looked obviously safe by intuition turned out to sit right at the edge of the guardrail's blind spot, and the experiment design had to be corrected in response, which is itself the kind of finding a real chaos experiment is supposed to surface.

Structured elaboration

Hypothesis. State, before running anything, exactly what should remain true if the system is as resilient as believed: here, "limiting the injected +800ms feature-store latency to a small canary slice of traffic will keep the OVERALL (system-wide, blended) p99 request latency within its 200ms SLO," a specific, falsifiable claim, not a vague "the system should handle this."

Blast radius. Only a defined fraction of live traffic is routed through the fault-injected path (a canary), with the remainder (control) running untouched; this is what keeps a failed hypothesis from becoming a full outage. The worked example below shows the blast-radius fraction is not just "pick something small": it has a precise, mechanical relationship to whatever percentile metric is guarding the rollback decision.

Experiment steps (fault injection). Route a small, defined percentage of feature-store lookup calls through a proxy or feature flag that adds a fixed extra delay before the call completes, simulating a slow downstream dependency (a plausible real fault: a feature store under load, a network path with added latency); everything else in the request path is untouched, isolating the effect to exactly the dependency being tested.

Metrics to monitor. Two DIFFERENT metrics, each catching something the other can miss: (1) the CANARY group's own latency and timeout rate, which directly measures how badly the fault affects requests that hit it, and (2) the BLENDED, system-wide latency percentile, which measures whether the fault is contained enough to be invisible at the aggregate SLO level. The worked example demonstrates concretely why relying on only one of these is a mistake.

Rollback criteria. Defined and automated BEFORE the experiment starts, not decided in the moment: abort immediately if the canary group's own timeout rate exceeds a threshold (here, 1%), OR if the blended system-wide p99 breaches its SLO (here, 200ms). Two independent triggers, because either one crossing is sufficient reason to stop.

Running this safely in production or staging. Start in staging with the same relative traffic proportions if realistic synthetic load is available; if the experiment must run in production to be meaningful (staging often cannot reproduce real traffic patterns and real feature-store load), start at the SMALLEST blast radius that can still produce a measurable signal, hold a kill switch that can instantly stop the fault injection (not just a manual rollback plan, an automated one wired to the rollback criteria above), and run during a low-traffic, well-staffed window with the on-call team aware in advance, not as a surprise.

Worked example

"""
Simulates the chaos experiment: baseline feature-store latency ~
Exponential(mean), +800ms fault injected into a canary slice, capped at an
850ms hard client-side timeout. Pinned RNG seed for reproducibility.
"""
import math
import random


def simulate_experiment(n_requests=10_000, canary_fraction=0.05,
                          baseline_mean_ms=20.0, injected_latency_ms=800.0,
                          hard_timeout_ms=850.0, seed=20260730):
    rng = random.Random(seed)
    control_latencies, canary_latencies = [], []
    canary_timeouts = 0
    for _ in range(n_requests):
        is_canary = rng.random() < canary_fraction
        base_latency = rng.expovariate(1.0 / baseline_mean_ms)  # healthy feature-store call
        if is_canary:
            total_latency = base_latency + injected_latency_ms  # fault injection applied
            if total_latency > hard_timeout_ms:
                canary_timeouts += 1
                total_latency = hard_timeout_ms  # a real client cancels at the timeout
            canary_latencies.append(total_latency)
        else:
            control_latencies.append(base_latency)
    return {"control": control_latencies, "canary": canary_latencies,
            "blended": control_latencies + canary_latencies, "canary_timeouts": canary_timeouts}


def percentile(values, p):
    s = sorted(values)
    k = max(0, min(len(s) - 1, int(round(p / 100.0 * (len(s) - 1)))))
    return s[k]


def main():
    SLO_P99_MS, ROLLBACK_TIMEOUT_RATE = 200.0, 0.01

    # First parameter choice, disclosed rather than discarded: baseline_mean_ms=50.0
    # gives an UNFAULTED control-group p99 that already exceeds the 200ms SLO,
    # which would make ANY canary fraction look like a breach for a reason that
    # has nothing to do with blast radius. Caught here, then corrected below.
    probe_wrong = simulate_experiment(canary_fraction=0.0, baseline_mean_ms=50.0)
    p99_wrong = percentile(probe_wrong["control"], 99)
    print(f"Sanity check, FIRST baseline (mean=50ms): unfaulted control p99 = {p99_wrong:.1f}ms "
          f"vs analytic {50*math.log(100):.1f}ms (SLO {SLO_P99_MS:.0f}ms) -- already breaches unfaulted, discarded.\n")

    probe_ok = simulate_experiment(canary_fraction=0.0, baseline_mean_ms=20.0)
    p99_ok = percentile(probe_ok["control"], 99)
    print(f"Corrected baseline (mean=20ms): unfaulted control p99 = {p99_ok:.1f}ms "
          f"vs analytic {20*math.log(100):.1f}ms (SLO {SLO_P99_MS:.0f}ms)")

    primary = simulate_experiment(canary_fraction=0.01)
    blended_p99 = percentile(primary["blended"], 99)
    timeout_rate = primary["canary_timeouts"] / len(primary["canary"])
    print(f"\nPrimary experiment: 1% canary, +800ms injected, n_canary={len(primary['canary'])}")
    print(f"  Blended (system-wide) p99: {blended_p99:.1f}ms (SLO: {SLO_P99_MS:.0f}ms) -- "
          f"{'BREACH' if blended_p99 > SLO_P99_MS else 'within SLO'}")
    print(f"  Canary timeout rate:       {timeout_rate:.2%} (rollback trigger: >{ROLLBACK_TIMEOUT_RATE:.0%}) -- "
          f"{'FIRES' if timeout_rate > ROLLBACK_TIMEOUT_RATE else 'does not fire'}")

    wide = simulate_experiment(canary_fraction=0.02)
    wide_p99 = percentile(wide["blended"], 99)
    print(f"\nNegative control: 2% canary (double the primary), same fault, n_canary={len(wide['canary'])}")
    print(f"  Blended (system-wide) p99: {wide_p99:.1f}ms (SLO: {SLO_P99_MS:.0f}ms) -- "
          f"{'BREACH' if wide_p99 > SLO_P99_MS else 'within SLO'}")


if __name__ == "__main__":
    main()

Output (actually executed with python3):

Sanity check, FIRST baseline (mean=50ms): unfaulted control p99 = 233.1ms vs analytic 230.3ms (SLO 200ms) -- already breaches unfaulted, discarded.

Corrected baseline (mean=20ms): unfaulted control p99 = 93.2ms vs analytic 92.1ms (SLO 200ms)

Primary experiment: 1% canary, +800ms injected, n_canary=97
  Blended (system-wide) p99: 154.8ms (SLO: 200ms) -- within SLO
  Canary timeout rate:       5.15% (rollback trigger: >1%) -- FIRES

Negative control: 2% canary (double the primary), same fault, n_canary=193
  Blended (system-wide) p99: 811.8ms (SLO: 200ms) -- BREACH

This is the genuinely non-obvious finding, discovered by actually running the experiment rather than reasoning about it in the abstract: at a 1% blast radius, the BLENDED p99 guardrail alone says the experiment is safe (154.8ms, comfortably under the 200ms SLO), but the CANARY group's own timeout-rate guardrail fires (5.15%, five times the 1% rollback threshold), catching the fault the blended metric completely missed. The reason is mechanical, not incidental: p99 is defined as the value below which 99% of samples fall, i.e. it is set by the top 1% of the sample. A canary fraction sitting right at that same 1% boundary means the fault-affected requests only barely fail to dominate the percentile at 1% (confirmed by the negative control: doubling to a 2% blast radius, with the identical fault, pushes the blended p99 to 811.8ms, a clear breach). If this experiment had used ONLY the blended-SLO guardrail, exactly as the first, wrong-baseline attempt implicitly assumed, it would have shipped a 1%-blast-radius rollout believing it was safe while 5% of the canary's own requests were silently timing out, which is precisely why the rollback criteria above require BOTH metrics, not the aggregate one alone.

Trade-offs and pitfalls

• Common mistake: trusting a single blended, system-wide metric as the sole safety signal. As demonstrated above, a blended percentile metric can stay well within its SLO even while a meaningful fraction of the exposed canary traffic is failing outright, specifically when the canary fraction sits near the percentile's own tail-mass threshold; always pair an aggregate guardrail with a canary-group-specific one.
• Common mistake: assuming "small blast radius" is safe without relating it to the specific metric guarding the experiment. The negative control shows the safety margin here is much tighter than intuition suggests: doubling from 1% to 2% flips the blended-SLO guardrail from safe to breaching, for the identical injected fault.
• A hypothesis that turns out wrong is not a failed experiment, it is the experiment doing its job. The value of running this in a controlled, blast-radius-limited way with an automated kill switch is exactly that a wrong assumption about resilience gets caught safely, instead of being discovered for the first time during an actual, unscoped incident.
• Staging cannot always substitute for production for this class of experiment. A feature store's latency behavior under real concurrent load and cache pressure is often not reproducible synthetically; when production is genuinely necessary, the blast radius, automated rollback, and stakeholder awareness described above are what make that acceptable rather than reckless.

Direct answer

A pipeline stage is idempotent if running it again on the same input leaves the system in the same end state as running it once, with no extra side effects like duplicate rows or double-counted totals. This matters because retries and reprocessing are a normal part of running a pipeline (a job fails partway, a message gets redelivered, a backfill re-runs old data), and without idempotency each of those ordinary events risks silently corrupting downstream data.

Structured elaboration

What makes an operation idempotent

A write is idempotent when it is expressed as "set this to X" (an upsert or merge keyed by a stable identity) rather than "add X to whatever is already there" (a blind append or increment). The second form is only safe to run exactly once; the first is safe to run any number of times. Idempotency is a property of the write itself, not of the retry logic wrapped around it: retry logic decides whether to attempt again, idempotency decides whether attempting again is safe.

Where the stable identity comes from

The common pattern in distributed ingestion is a unique key assigned once, close to the source, whether a generated identifier, a source-provided event identifier, or a deterministic combination of the event's natural business identity and a time bucket, carried through every downstream stage. Every write then keys off that same identity, so a redelivered or replayed event lands on the same row instead of creating a new one.

Why retries and reprocessing make this non-optional

Retries happen because any network call in a distributed system will occasionally redeliver a message the consumer already processed; that is ordinary operation, not a bug, so every retry is a chance to double-count if the write is not idempotent. Reprocessing and backfills are standard practice (fixing a bug, adding a derived column); without idempotency, re-running a job over a period that already has data corrupts it instead of correcting it. Without this property, operators end up avoiding retries and backfills out of fear, which is worse: it blocks the very bug fixes and failure recovery the pipeline needs.

Worked example

A nightly job loads sales records keyed by order identifier and event date. If the job fails after writing half the day's records and is simply re-run, a blind append would create duplicate rows for that half; an upsert keyed by order identifier and event date instead overwrites those same rows with identical values, so running the job twice looks exactly like running it once.

Trade-offs & pitfalls

• Assuming retries are rare enough to skip idempotent design is risky precisely because the failure only shows up once, silently, and bad data is already downstream by the time anyone notices.
• Deduplicating on a payload hash instead of a stable business identity fails when a payload legitimately changes between attempts (a corrected field): the same conceptual event gets treated as new.
• Idempotent writes (upserts, keyed merges) usually cost a little more per write than blind appends, since the store has to check for an existing key first; that overhead is the price of retry-safety and is almost always worth paying compared to the cost of finding and fixing silent duplication later.
• Making the retry policy careful (backoff, limited attempts) while leaving the underlying write non-idempotent is a common wrong turn; the retry policy is not what makes reprocessing safe, the shape of the write is.

Direct answer

Python resolves a name by searching four scopes in order: Local (the current function body), Enclosing (any outer function that defines this one, for nested functions), Global (the module), Built-in (names like len and range). Lookup stops at the first scope that has the name. The catch is assignment, not lookup: if a name is assigned anywhere inside a function body, Python treats it as local to that function for the whole body, even before the assignment line runs, unless you declare it nonlocal (binds to the nearest enclosing function scope) or global (binds to module scope).

Structured elaboration

The four scopes, outside in:

• Local: parameters and names assigned inside the current function.
• Enclosing: names in any outer function that lexically contains this one (relevant only for nested functions); this is what makes closures possible.
• Global: names bound at module level.
• Built-in: the builtins module (len, range, print, and so on), checked last.

Why nonlocal exists: Python decides, at compile time, whether a name inside a function is local by scanning the function body for any assignment to that name. Reading an enclosing variable works fine without any keyword. The moment you assign to a name that lives in an enclosing scope, Python's default is to create a brand-new local variable with that name instead of reaching outward, which shadows the outer one and usually breaks the intended logic. nonlocal tells the compiler: this name is not local, resolve it in the nearest enclosing function scope (skipping straight past to global is a compile error if no enclosing binding exists). This differs from global, which always targets the module scope regardless of nesting depth.

The closure "late binding" gotcha: a nested function does not capture a variable's value at definition time, it captures the variable itself and reads it whenever the nested function actually runs. If several closures share a loop variable, they all read whatever value that variable holds when they are finally called, which is usually the loop's last value.

Worked example

Where nonlocal is required, verified on CPython 3.12:

def make_accumulator():
    total = 0
    def add(x):
        nonlocal total   # without this, `total += x` raises UnboundLocalError
        total += x
        return total
    return add

acc = make_accumulator()
print(acc(10))   # 10
print(acc(5))    # 15
print(acc(1))    # 16

What happens if you drop nonlocal (exact CPython 3.12 message):

def make_broken_accumulator():
    total = 0
    def add(x):
        total += x   # `total` is assigned here, so LEGB makes it local to add()
        return total
    return add

broken = make_broken_accumulator()
try:
    broken(10)
except UnboundLocalError as e:
    # expected: total is assigned inside add(), so LEGB makes it local for the
    # whole function body, and the read on the += happens before any assignment runs
    print(f'raises as expected: UnboundLocalError: {e}')

The late-binding closure gotcha, same mechanism (enclosing-scope lookup happens at call time, not definition time):

funcs = []
for i in range(4):
    funcs.append(lambda: i)
print([f() for f in funcs])   # [3, 3, 3, 3] -- all read the final value of i

# fix: bind the current value as a default argument, evaluated at definition time
funcs2 = []
for i in range(4):
    funcs2.append(lambda i=i: i)
print([f() for f in funcs2])  # [0, 1, 2, 3]

Trade-offs & pitfalls

• Reaching for nonlocal on every enclosing read is a common overcorrection: it is only needed when you assign to the name, never for reading it.
• nonlocal cannot create a new binding; if no enclosing function scope already has that name, Python raises a SyntaxError at compile time, it will not silently fall through to global.
• The late-binding gotcha bites hardest with lambda inside loops (event handlers, callbacks passed to a scheduler, list comprehensions of functions); the default-argument fix works because default values are evaluated once, at function-definition time, not at call time.
• Mutable enclosing state (a list or dict) sidesteps nonlocal entirely, since mutating the object's contents is not the same as rebinding the name, but that trades an explicit rebind for a shared mutable object, which is its own source of bugs in concurrent code.

Direct answer

Safely replaying the missing window means treating the outage window as a specific, bounded time range to re-extract from the source, applying every replayed record through the same idempotent write path normal ingestion uses so already-landed records are harmlessly overwritten rather than duplicated, and cross-checking the result against an independent total (the source's own reported total, if one exists) before declaring the gap closed. Preventing a repeat means the outage revealed a real gap in what the ingestion pipeline durably records about its own progress during a failure, and that gap is what needs fixing, not just the one bad day's numbers.

Structured elaboration

Determining exactly what is missing

• Establish the outage window's precise start and end from your own ingestion checkpoints and monitoring, not from the source's incomplete logs, since those are exactly what is unreliable here.
• Compare your landed record IDs against the source's current state for that window (a re-query, not the old logs) to build an authoritative list of what is actually missing, rather than assuming the whole window needs a full re-pull.

Replaying without double-counting

• Route every replayed record through the same idempotent upsert path (keyed on the record's own stable ID) that normal ingestion uses, so a record that already landed correctly during the chaotic recovery is simply overwritten with itself, not duplicated.
• Never insert replayed records as new rows into the destination without going through that same conflict-safe write path; a "just for this replay" bespoke insert script is exactly how a well-intentioned fix creates the duplicates it was meant to prevent.

Verifying the fix

• Reconcile against an independent total where one exists: if the source has its own authoritative count or sum for the window (a billing system's own reported revenue, for example), compare your landed total against it, not just against your own row count.
• Spot-check a sample of individual records from the window against the source directly, since an aggregate total matching does not guarantee every individual record is correct, only that errors did not happen to cancel out.

Preventing the same class of gap next time

• The actual root cause here is that your OWN ingestion checkpoint/logging was not durable or granular enough to reconstruct exactly what was captured during the outage; fix that specifically, for example by persisting a checkpoint after every small batch rather than only at the end of a run, so a future outage leaves a precise, trustworthy record of exactly where things stopped.
• Add a lightweight, ongoing reconciliation check (comparing your ingested totals against the source's own reported totals on some regular cadence) so a gap like this is caught within hours, not discovered by a finance team noticing the numbers do not match.

Worked example

A payments source has a 6-hour outage; during automated recovery, some transactions are re-sent and land twice under the pipeline's normal ingestion (no duplicate, because ingestion already upserts on transaction ID), but a smaller set of transactions that occurred right at the very start of the outage were never captured at all, since the pipeline's checkpoint had not yet been durably written when the outage began. The precise gap is identified by querying the source for all transaction IDs in the outage window and diffing against what already landed, rather than trusting the source's own (incomplete) outage-window logs. The missing transactions are replayed through the normal idempotent ingestion path, and the day's total is reconciled against the payment processor's own settlement report for that window, confirming an exact match before the incident is closed. Going forward, the checkpoint is changed to persist after every 1,000-record batch instead of only at the end of a full run, so a future outage at the same point in processing would leave a checkpoint precise enough to know exactly what needs replaying without needing this kind of forensic reconciliation at all.

Trade-offs & pitfalls

• Re-pulling the ENTIRE outage window "to be safe," rather than precisely the missing subset, is simpler to reason about but multiplies load on a source that may still be recovering from the outage that caused this in the first place; a precise, diffed gap is worth the extra care.
• Reconciling only against your own row counts, with no independent total from the source, means a systematic error (every record shifted by the same wrong amount, for example) can pass a naive check that only confirms "the right number of rows landed."
• The prevention step is easy to skip once the immediate incident is resolved and the pressure is off; without it, this exact incident recurs at the next outage, just with a different specific set of missing records.
• A replay that bypasses the normal ingestion write path "because it is faster for this one-off fix" is the single most common way a well-intentioned incident response creates a second incident.

The three pillars of observability are logs, metrics, and traces.

Logs — concrete example: ingestion worker logs recording event timestamps, input offsets, record IDs, and error/ retry messages (e.g., "offset 12345 processed at 2025-11-22T10:05:12, partition 2, 0 retries").
How it helps: when downstream reports are stale, logs let you verify whether raw data arrived on time, whether parsing/validation failures dropped records, or whether retries/backoffs occurred. You can search for missing IDs/timestamps to confirm data never entered the pipeline vs. was delayed.

Metrics — concrete example: time-series metrics like Kafka consumer lag, batch processing latency, throughput (records/sec), and end-to-end pipeline freshness gauge (minutes behind real time).
How it helps: metrics surface anomalies quickly (e.g., consumer lag spikes, processing-time increase, or freshness gauge rising). Alerts based on thresholds point you to when and roughly where delay started and quantify its impact.

Traces — concrete example: a distributed trace that spans ingestion → transform (Spark job) → load into warehouse, with per-span durations and tags (job IDs, host, shuffle size).
How it helps: traces reveal which stage introduced latency (e.g., long Spark shuffle step or slow write to warehouse). With span-level timings you can pinpoint bottlenecks, identify slow hosts or dependency calls, and correlate with metrics/logs to trace root cause and fix (reschedule jobs, increase parallelism, or resolve a failing external service).

Direct answer
Before presenting, think through the questions a skeptical, informed listener would actually ask, prioritizing the ones that probe your weakest assumption or your most surprising claim, and prepare a short, direct answer for each rather than hoping you'll improvise well.

Structured elaboration

1. Look for your weakest link first. Every piece of work has at least one assumption, data limitation, or judgment call that's more debatable than the rest; that's almost always where a sharp question comes from.
2. Look for your most surprising or counterintuitive claim. Anything that contradicts what people expected invites a "how do you know that's really true?" question.
3. Prepare a one-to-two sentence answer, not a rehearsed speech. A concise, direct answer reads as confident; a long, defensive one reads as though you're worried about the question.
4. It's fine to prepare an honest "we don't know yet" answer for a genuine gap, rather than inventing a more impressive-sounding answer under pressure; a confident admission of a limitation is usually better received than an unconvincing dodge.
5. Practice saying the answers out loud, not just thinking through them mentally; the gap between a mentally-rehearsed answer and one you can actually say smoothly under pressure is often bigger than expected.

Worked example
Presenting a recommendation to shift budget from one marketing channel to another based on eight weeks of data: anticipated tough questions might be "how confident are you this isn't just seasonal?", "what happens if the trend reverses next month?", and "did you control for the pricing change that happened in week 5?" Prepared answers: "We checked against the same period last year and saw a similar pattern, though eight weeks is admittedly a short window;" "if it reverses, the downside is limited since we're proposing a 20% shift, not the full budget;" "we did exclude the two weeks around the pricing change specifically to avoid conflating the two effects."

Each answer is short, direct, and, where there's a genuine limitation (the short time window), honestly acknowledged rather than glossed over.

Trade-offs and pitfalls

• Over-preparing for every conceivable question can lead to over-rehearsed, stiff-sounding answers; focus on the two or three questions most likely to actually come up, not an exhaustive list.
• Being defensive about a genuinely fair question damages credibility more than the limitation itself would; a calm, honest acknowledgment of a real gap usually lands better than an unconvincing justification.
• If a question comes up that you genuinely didn't anticipate and don't know the answer to, saying so plainly and offering to follow up is stronger than guessing in the moment.

Direct answer

NULL and an empty string are different things in SQL: NULL means "no value is present," while '' is an actual, present value that happens to be zero-length. Most aggregate functions (SUM, AVG, COUNT(column)) silently skip NULLs, but treat an empty string as a real value to be counted, and a NULL in a JOIN key drops the row from an INNER JOIN entirely because NULL never equals anything, not even another NULL.

Structured elaboration

• COUNT(*) counts every row regardless of NULLs; COUNT(column) counts only non-NULL values in that column; these two numbers diverging by exactly the null count is expected, not a bug.
• SUM(column) and AVG(column) ignore NULLs in their calculation. AVG in particular divides by the count of non-NULL values, not the total row count, so a column that is mostly NULL can show a misleadingly "healthy" average computed over a tiny effective sample.
• A LEFT JOIN produces a NULL foreign key when a SUM(CASE WHEN ... ) or a NULL key propagates through arithmetic: NULL + 5 is NULL, not 5, so a single unexpectedly-NULL input can silently NULL out an entire downstream calculated column without raising any error.
• An INNER JOIN on a nullable key drops any row where either side is NULL, because SQL's three-valued logic treats NULL = NULL as unknown, not true.
• GROUP BY does the opposite of what the NULL = NULL rule above would suggest: every NULL value in the grouping column is treated as belonging to a single group, not split apart and not excluded, even though NULL = NULL is otherwise unknown everywhere else in SQL. SELECT status, COUNT(*) FROM orders GROUP BY status puts every row with a NULL status into one "NULL" group and reports a count for it, rather than silently dropping those rows or creating a separate group per NULL row. This is a genuinely surprising exception once you have internalized that NULL never equals anything else: GROUP BY is special-cased to treat NULLs as mutually equal for grouping purposes specifically, even though the same values would never match each other in a WHERE clause or a JOIN.

Worked example

Given orders(order_id, customer_id, amount) and customers(customer_id, email), an INNER JOIN on customer_id silently drops every order whose customer_id is NULL, with no error and no warning: SELECT COUNT(*) FROM orders o JOIN customers c ON o.customer_id = c.customer_id will simply undercount total orders if any customer_id values are NULL. The common ETL (extract, transform, load) mistake that makes this worse is a transformation step that converts an upstream NULL into an empty string (often accidentally, via a string-concatenation or COALESCE-to-empty-string step), after which a WHERE customer_id IS NOT NULL filter no longer catches the "missing" rows at all, because they are no longer NULL, they are an empty string that silently passes every NULL check downstream. A sentinel value like -1 behaves differently again, and more dangerously, in an aggregate: unlike NULL, -1 is a real, ordinary numeric value as far as SUM and AVG are concerned, so it gets silently included in the arithmetic rather than excluded. Concretely, for four amount values 1, 2, 3, -1 where the last row's -1 is actually a "no purchase amount recorded" sentinel rather than a real negative amount, AVG(amount) computes (1 + 2 + 3 + (-1)) / 4 = 5 / 4 = 1.25, not the (1 + 2 + 3) / 3 = 2 a reader would get if that row's true amount were correctly treated as unknown and excluded the way a NULL would have been. The sentinel does not just get miscounted, it actively drags the computed average down, which is a strictly worse failure mode than NULL's silent exclusion because nothing about the query signals that anything was skipped or mishandled.

Trade-offs and pitfalls

The single most damaging pattern is inconsistency within the same pipeline: some upstream systems send NULL for "unknown," others send an empty string, and a few use a sentinel like -1 or 'unknown'. Without an explicit, documented convention and validation that enforces it, the same underlying "no value" concept gets counted, summed, and joined differently depending on which source it came from, and the resulting discrepancy is exactly the kind of silent, hard-to-trace bug that shows up as "two dashboards disagree" weeks later.

Dirty read — one transaction reads uncommitted changes from another.
Example:

-- T1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- not committed

-- T2
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- reads uncommitted value
COMMIT;
-- if T1 ROLLBACK, T2 saw a value that never existed

Prevented by: READ COMMITTED, REPEATABLE READ, SERIALIZABLE (not by READ UNCOMMITTED).

Non-repeatable read — a transaction reads the same row twice and sees different committed values due to another committed update.

-- T1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- sees 1000

-- T2
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- T1
SELECT balance FROM accounts WHERE id = 1; -- now sees 900 (non-repeatable)
COMMIT;

Prevented by: REPEATABLE READ, SERIALIZABLE (not by READ COMMITTED or READ UNCOMMITTED).

Phantom read — repeated range query returns additional/deleted rows because of concurrent insert/delete.

-- T1
BEGIN;
SELECT COUNT(*) FROM orders WHERE customer_id = 42; -- returns 2

-- T2
BEGIN;
INSERT INTO orders(customer_id, id) VALUES (42, 99);
COMMIT;

-- T1
SELECT COUNT(*) FROM orders WHERE customer_id = 42; -- returns 3 (phantom)
COMMIT;

Prevented by: SERIALIZABLE (REPEATABLE READ in some DBs prevents phantoms; standard: only SERIALIZABLE).

Lost update — two transactions read-modify-write same row without seeing each other's write, last write overwrites first.

-- T1
BEGIN;
SELECT qty FROM inventory WHERE id=1; -- sees 10
UPDATE inventory SET qty = qty - 2 WHERE id=1;
-- not yet committed

-- T2
BEGIN;
SELECT qty FROM inventory WHERE id=1; -- also sees 10
UPDATE inventory SET qty = qty - 3 WHERE id=1;
COMMIT; -- qty becomes 7

-- T1
COMMIT; -- overwrites with 8 (lost update from T2) depending on DB semantics

Prevented by: REPEATABLE READ with proper locking or SERIALIZABLE; many DBs avoid via SELECT ... FOR UPDATE or optimistic locking.

Write skew — two concurrent transactions read overlapping data and make writes that together violate a constraint though each individual check passed.

-- Table: on_call(doctor_id, oncall boolean)
-- Constraint: at least one doctor must be oncall

-- T1
BEGIN;
SELECT oncall FROM on_call WHERE doctor_id IN (1,2); -- sees (true,true)
UPDATE on_call SET oncall = false WHERE doctor_id = 1;
-- not committed

-- T2
BEGIN;
SELECT oncall FROM on_call WHERE doctor_id IN (1,2); -- sees (true,true)
UPDATE on_call SET oncall = false WHERE doctor_id = 2;
COMMIT; -- now both false

-- T1
COMMIT; -- results in no doctor oncall (constraint violated)

Prevented by: SERIALIZABLE (or by explicit locking like SELECT ... FOR UPDATE or using constraints/triggers).

Summary mapping:

• READ UNCOMMITTED: prevents nothing (allows dirty reads)
• READ COMMITTED: prevents dirty reads
• REPEATABLE READ: prevents dirty and non-repeatable reads (phantom depends on DB)
• SERIALIZABLE: prevents all above anomalies (including write skew)
  Use explicit locks or application-level optimistic checks where DB isolation or performance trade-offs require it.