Airbnb Staff Data Engineer Interview Preparation Guide

Direct answer: For a fixed, small array (say n <= 20) on a latency-sensitive hot path, insertion sort - despite its O(n^2) worst-case complexity - typically beats an asymptotically-optimal O(n log n) algorithm like quicksort, because at small n the constant-factor overhead of quicksort's recursion, partitioning logic, and function-call machinery dominates, while insertion sort's simple, branch-predictable inner loop with minimal overhead wins outright, and its cost is highly predictable (important when "predictable" matters as much as "fast").

Structured elaboration

Asymptotic notation describes behavior as $n \to \infty$; it says nothing about which algorithm is faster for a SPECIFIC small, bounded n. At n=20: insertion sort does at most $\binom{20}{2}=190$ comparisons/shifts in the absolute worst case (already-reverse-sorted input) - a tiny, fixed number of simple operations with excellent cache locality (sequential array access, no recursion, no extra memory allocation). Quicksort at the same n pays real overhead: recursive call setup, partition-index bookkeeping, and (if implemented generically) potential heap allocation for the call stack - fixed costs that don't shrink just because n is small.

This is precisely why production-quality sort implementations (Timsort in Python, introsort variants in C++'s std::sort) switch to insertion sort for small subarrays below some threshold (commonly 16-32 elements) even inside an otherwise O(n log n) algorithm - it's a well-established, empirically-validated engineering pattern, not a theoretical curiosity.

Worked example

At n=20, insertion sort's worst case is $O(n^2) = 400$ "units" of work in the crude operation-count sense, while quicksort's average case is $O(n \log n) \approx 20 \times 4.3 \approx 86$ units - fewer raw operations by count, but each of quicksort's operations (recursive calls, partition scans, pivot selection) carries far more overhead per unit than insertion sort's simple compare-and-shift. The actual wall-clock comparison depends on implementation-specific constants that must be benchmarked on the target platform, not derived from operation counts alone - but the qualitative result (insertion sort wins at this small, fixed scale on latency-sensitive paths) is a well-established, reproducible finding across language implementations, which is exactly why hybrid sort algorithms bake in this exact threshold-switch.

Trade-offs & pitfalls

• "Low-latency and predictable" is doing real work in the question - insertion sort's worst case (400 operations) has almost no VARIANCE across inputs, while quicksort's worst case (O(n^2), extremely rare but real) introduces tail-latency risk that a hot path may not tolerate, even though its average case is better.
• This reasoning only holds for genuinely small, BOUNDED n - if the "20" could occasionally spike to 20,000, the calculus flips entirely and you'd want the asymptotically-better algorithm as a safety net.
• Don't over-generalize to "small-n sorts should always use insertion sort" without checking the actual threshold empirically on your platform - the crossover point is a real number (commonly cited as roughly 16-32 elements) worth validating, not assuming.

Direct answer

Computing a metric like daily active users per user's LOCAL day, correctly across a multi-region user base, requires storing timestamps in UTC alongside each user's timezone (captured at event time, not assumed from their current profile setting), and using the IANA timezone database, not a fixed offset, to convert to local dates, since only the IANA database correctly encodes historical daylight-saving and political timezone-rule changes.

Structured elaboration

• Users who travel: a user's "local day" should be computed using the timezone in effect at the moment of each individual event, not their profile's currently-set timezone, since a user who traveled between the event and when the aggregation runs would otherwise have their events misattributed to the wrong local day.
• Users with no stored timezone: define an explicit, documented fallback policy (a default timezone based on IP geolocation at event time, or a global default like UTC with an explicit "unknown timezone" flag) rather than silently guessing or silently excluding these users, since either of those silent choices distorts the aggregate without anyone knowing.
• Ambiguous and nonexistent local times around DST transitions: the "fall back" hour occurs twice in local time and needs a rule for which occurrence an event belongs to (usually resolved by the event's actual UTC timestamp, which is unambiguous, converted through the correct offset for that specific UTC instant); the "spring forward" hour never occurs in local time at all, so any local timestamp claiming to be in that gap is itself invalid and should be flagged.
• Testing strategy: unit tests for the core UTC-to-local-date conversion function using known DST transition dates in several different timezones; integration tests validating a full day's aggregation produces the correct DAU count across a synthetic dataset specifically constructed to include events crossing a DST boundary; and explicit boundary test cases (an event at exactly 1 second before and after a transition) to catch off-by-one-second errors at the edge.

Worked example

An event logged at 2026-11-01 06:30:00 UTC for a user in America/New_York: correctly converting this requires knowing that daylight saving ended that day, so events shortly before and after the transition, despite being close together in UTC, land on different sides of the DST boundary and must use the correct offset (EDT versus EST) in effect at each event's specific UTC instant, not a single fixed offset applied to the whole day. The America/New_York transition instant that day is exactly 2026-11-01 06:00:00 UTC, at which local clocks fall back from 2:00 AM EDT to 1:00 AM EST; converted with the correct IANA rule for each event's exact UTC instant, an event at 2026-11-01 05:59:59 UTC (one second before the transition) lands at 2026-11-01 01:59:59 EDT, while an event at 2026-11-01 06:00:00 UTC (one second later) lands at 2026-11-01 01:00:00 EST, an hour EARLIER in local time despite being only one second later in UTC. A naive converter using a single fixed offset for the whole day would get one of these two events wrong, either mislabeling the pre-transition event as EST or the post-transition event as EDT, and in either case would place it in the wrong local hour. The original anchor event at 06:30:00 UTC falls fully after the transition and converts unambiguously to 01:30:00 EST, which is why it alone does not illustrate the boundary crossing the way the 05:59:59/06:00:00 pair does.

Trade-offs and pitfalls

The recurring implementation mistake is using a fixed, cached UTC offset per user (computed once and reused) rather than looking up the correct offset fresh for each event's specific timestamp using the full IANA timezone database; a fixed offset is wrong immediately after any DST transition and stays wrong until someone notices the DAU numbers look subtly off around every clock change, which is a hard bug to trace back to its root cause without dedicated test coverage for exactly this scenario.

Computing both flags side by side on the same data reveals a real limitation of z-score that's easy to miss when you only ever use one method: an extreme outlier can inflate the very standard deviation used to judge it, silently masking itself.

Approach (verified by execution)

```sql
WITH stats AS (
SELECT AVG(amount) AS mean_amt, STDDEV_SAMP(amount) AS std_amt,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
FROM tx
)
SELECT t.transaction_id, t.amount,
(t.amount > s.mean_amt + 3s.std_amt) AS zscore_flag,
(t.amount < s.q1 - 1.5(s.q3-s.q1) OR t.amount > s.q3 + 1.5*(s.q3-s.q1)) AS iqr_flag
FROM tx t CROSS JOIN stats s;
```

Worked example (verified by execution)

Five values around 9-13 plus one value of 500: the mean is 92.5 and the standard deviation balloons to roughly 200 because that single outlier inflates it, so `mean + 3*std` is nearly 700, and the z-score flag comes back FALSE for the 500 row, a genuine miss. The IQR flag, computed from quartiles anchored on the bulk of the data, correctly comes back TRUE for the same row, because a single extreme point barely shifts Q1 or Q3.

Trade-offs and pitfalls

This isn't a contrived edge case; it's the expected behavior any time your dataset contains few points and one is genuinely extreme, which describes most real anomaly-hunting scenarios (you don't know the anomaly is there until you look). The practical implication is not "always use IQR instead of z-score" but rather: if you must use a mean/standard-deviation approach on data you suspect already contains outliers, compute the baseline from a TRIMMED or historical (pre-anomaly) window rather than the same window you're testing, exactly as the per-user running-baseline approach does by excluding the current row from its own statistics.

Direct answer

I'd start by pulling the itemized billing export (AWS's Cost and Usage Report, or the equivalent from Azure or Google Cloud) rather than the headline invoice total, break the delta down by service, region, and tag, and look for whether the jump is broad (many services up a little) or concentrated (one or two line items driving most of it), because that single split determines whether I'm hunting for a config change or a runaway process. In parallel, I'd put an emergency budget alert and a hard cap or throttle in front of the likeliest culprit so I'm not still bleeding money while I investigate, since stopping the bleeding and finding the root cause are two different, simultaneously-runnable tasks, not a sequence.

Structured elaboration

Step 1: Scope the delta before hypothesizing about the cause
Pull the granular billing export for both months and diff them by service, region, account or project, and tag. A 40% jump could be one resource type up 400% or every service up a proportional amount, and those point to completely different investigations, so this diff is the first thing to run, before forming any hypothesis about root cause.

Step 2: Narrow to the resources actually driving the delta
Match the billing line items with the largest deltas back to specific resource IDs and tags. Look specifically for: new or unusually large resource counts, a spike in a service that's normally near-zero for this account, unattached storage volumes that shouldn't exist, or a jump in data-transfer line items, since egress spikes are a common and easy-to-miss cause of a sudden bill jump.

Step 3: Correlate against what changed operationally, in the same window
Cross-reference the spike window against deployment history and CI/CD logs (did a release ship right before the jump started), autoscaling events (did a scaling policy misfire and hold capacity high), and application/infrastructure logs for anything that looks like a runaway job or a misconfigured batch process. This is where "a new deploy shipped a debug flag that left instances scaled up" or "a scheduled job got triggered twice" typically surfaces.

Step 4: Form and test a specific hypothesis
By this point there's usually a short list of candidates (a specific deploy, a specific job, a specific resource type). Validate each by reproducing the cost against the actual usage: does the resource's usage-hours or data-scanned metric, for the spike window, actually account for the dollar delta observed. A hypothesis that doesn't reproduce the dollar amount isn't the (whole) root cause, and the investigation isn't done until the numbers actually add up.

Step 5: Stop further unexpected spend while still investigating
This runs in parallel with steps 1 through 4, not after them: set an emergency budget alert on the affected account or service immediately, and for the most likely culprit, apply a targeted control (scale the autoscaling group's max down to a safe ceiling, pause the suspect job, revoke a deployment credential if a bad deploy is implicated) rather than a blanket freeze that would stop legitimate traffic too. The goal is to bound the damage on the specific thing under suspicion without causing a second incident by shutting down something that turns out to be unrelated.

Worked example

A team's monthly bill jumps from a baseline of $42,000 to $58,800, a 40% increase, or $16,800 in absolute terms. The billing-export diff shows the increase isn't broad: EC2 (Amazon Elastic Compute Cloud) compute is up only 4%, but a single service, a managed data-warehouse query engine, is up $11,200, and S3 (Amazon Simple Storage Service) data-transfer/egress charges are up $4,100, together accounting for $15,300 of the $16,800 delta, with the remaining $1,500 spread thinly across normal month-over-month growth.

Matching the query-engine spike to usage shows a single ad-hoc analytics query scanned roughly 2 TB of data three separate times during the window, run by an analyst testing a new dashboard against a table that wasn't partitioned the way they assumed, at a rate where scanning that much unpartitioned data repeatedly accounts for the bulk of the $11,200. The egress increase correlates with a deploy that changed a batch export job's destination from an in-region bucket to a bucket in a different region, adding a per-GB cross-region transfer charge that hadn't existed before that release.

Root cause: two independent, unrelated changes landing in the same billing period, an expensive unpartitioned ad-hoc query pattern and a misconfigured cross-region export destination, together explaining $15,300 of the $16,800 (91%) increase, with the remainder attributable to normal growth.

Trade-offs and pitfalls

The most common mistake is jumping straight to a hypothesis (usually "it's autoscaling" or "it's a new feature") before actually running the billing diff, which wastes time chasing a plausible-sounding cause that the data doesn't support. A second is applying a blanket spend freeze while investigating, which stops the bleeding but can also take down legitimate production traffic, turning a cost incident into an availability incident. A third is declaring victory once you've found a cause that "sounds right" without checking that the dollar amount it explains actually adds up to the observed delta. As this example shows, a real spike is often more than one thing happening at once, and stopping the investigation after finding the first plausible cause can leave a second, still-active cost driver running unnoticed.

Direct answer. Placing a filter on the right-hand table's column in WHERE, rather than in the join's ON clause, forces that column to be non-NULL to pass the filter, which throws away every left row that had no match (and therefore had NULL there), functionally converting your LEFT JOIN back into an INNER JOIN.

Structured elaboration. A LEFT JOIN's whole purpose is to preserve unmatched left rows by filling the right side with NULLs. WHERE runs AFTER the join has already produced its rows (including the NULL-padded ones), so a condition like WHERE o.status = 'SHIPPED' evaluates to unknown for any row where o.status is NULL, and WHERE discards anything that isn't TRUE. The fix is to move the condition into the ON clause instead, so it only constrains WHICH right-side rows are allowed to match in the first place, rather than filtering the joined result afterward; unmatched left rows still make it through with NULLs, exactly as a LEFT JOIN should behave.

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1, status='SHIPPED'), (order_id=101, customer_id=2, status='PENDING'). Customer 3 has no orders at all.

-- buggy: WHERE demotes this to an INNER JOIN
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'SHIPPED';
-- returns only (1, 100). Customers 2 and 3 vanish, even though the LEFT JOIN
-- was written specifically to keep customers with no matching order.

-- fixed: move the condition into ON
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED';
-- returns (1, 100), (2, NULL), (3, NULL). Every customer still appears.

Trade-offs and pitfalls. A quick check for this class of bug: run the query with and without the suspect WHERE clause and compare row counts against COUNT(*) on the left table; if the "LEFT JOIN" version returns fewer rows than the left table has, something downstream demoted it. The same bug hides in more places than a literal comparison: o.status IS NOT NULL, o.amount > 0, even a seemingly harmless AND o.region = 'US' in WHERE all have the identical effect. As a review habit, any filter that mentions a LEFT-JOINed table's column should be treated as suspicious until you've confirmed it's meant to belong in ON rather than WHERE.

Direct answer

Constructive skepticism means treating a colleague's analysis as something to verify before it reaches people who will make a decision on it, not something to trust blindly or attack. What keeps it collaborative rather than adversarial is that the questions are aimed at the work, in service of the same goal the analyst has (a correct, defensible result), not aimed at their competence.

Structured elaboration

What to actually check

• Data provenance and cleaning: were there filters, joins, or exclusions applied that could bias the result?
• Assumptions and their sensitivity: does the conclusion hold under a slightly different time window, cohort definition, or parameter choice?
• Confounders and alternative explanations: could something else, like seasonality or a cohort mix shift, explain the pattern as well as the stated cause?
• Reproducibility: can someone else rerun the analysis and get the same numbers, and are the metric definitions written down anywhere?

How to raise it without it turning into a credibility fight

The framing matters more than the content. Raise it privately and early, before it's in front of stakeholders, not during the stakeholder meeting itself. Ask it as a question about the data or method ('what date range did you use for this cohort?'), not as a verdict about the person or their competence. Where possible, offer to help verify rather than only pointing out a gap; that keeps the interaction collaborative instead of adversarial. The deeper mechanics of de-escalating a tense disagreement are their own skill; the key move here is simply getting the framing and the timing right before it escalates into one.

Worked example

A colleague's dashboard shows a conversion metric trending in a direction that conflicts with what other data would suggest. Before it goes in front of stakeholders, a private message asks what date range and cohort definition were used, and whether a known seasonal effect was accounted for. It turns out the shift came from a change in how the cohort was defined that week, not a real change in behavior. The colleague fixes the definition before the meeting, and the stakeholder presentation goes out correct, with no public correction needed.

Trade-offs and pitfalls

• Raising a concern only after it's already in front of stakeholders turns a technical question into a public correction, which is exactly where it tends to become a credibility fight.
• Flagging every minor doubt in a public forum regardless of the stakes wears down trust and slows the team; reserve escalation for cases where a private check didn't resolve it and the decision at stake actually matters.
• Being right about a caught issue is not the same as handling it well; how the concern was raised often matters more to the relationship than the fact that it was correct.

Direct answer

Without global ordering, deduplication has to rely on identity plus BOUNDED correctness windows rather than a single global sequence, using whichever of three mechanisms fits the topology: partition-local monotonic sequence numbers (cheap, but only meaningful within one partition, need explicit handling for wrap-around), vector clocks or logical (Lamport) timestamps (capture cross-partition causal ordering, at real, growing metadata cost), or a centralized dedup store keyed by a globally-unique event ID (simplest correctness model, at the cost of a shared-state bottleneck). Probabilistic filters (a Bloom-filter-style hybrid, checked before falling back to the exact mechanism) can front any of these to reduce load on the exact check. The right choice depends on how much metadata overhead and correctness-window latency the system can afford, not on which is "most correct" in the abstract.

Structured elaboration

Partition-local monotonic sequence numbers. Cheapest option: each partition/topic assigns its own increasing sequence number, and a consumer tracks, per partition, the highest sequence seen. A duplicate WITHIN the same partition is trivially detected (sequence already seen or below the watermark). This does NOT solve cross-partition/cross-topic deduplication at all, a duplicate of the same logical event appearing on a DIFFERENT partition (a common real case: the same event published to a backup topic, or replicated across a partition rebalance) is invisible to a purely partition-local scheme. Wrap-around: a fixed-width sequence number (a 32-bit counter, common in some systems) eventually wraps back to zero; a consumer's "seen" logic must explicitly handle this (treating a sequence far LOWER than the last seen as either a genuine wrap or a very-late/very-early out-of-order arrival, distinguishable only with an additional epoch/generation counter incremented on each wrap).

Vector clocks or logical timestamps. A vector clock (one counter per participating node/partition, all carried together) captures genuine CAUSAL ordering across the whole distributed system without requiring synchronized physical clocks. Metadata overhead: a vector clock's size grows linearly with the number of distinct nodes/partitions that have EVER causally influenced an event, which for a system with many producers or long-lived partitions can grow to a genuinely significant per-event overhead (this is a real, specific limitation: vector-clock growth is not bounded by default and needs an explicit pruning/garbage-collection strategy for entries from nodes no longer relevant). Clock-synchronization limits: logical (Lamport) timestamps avoid needing synchronized WALL clocks (they are pure counters, incremented on each event and on each message received), but they only give a PARTIAL order (two logically-concurrent events from different nodes get incomparable timestamps), which is sufficient for detecting genuine causal duplication but not for imposing a total, deterministic order the way a single global sequence would.

Centralized dedup store. A shared, external store (e.g. a keyed conditional-write against DynamoDB or Redis) checked by every producer/consumer regardless of which partition/topic an event arrived on. Simplest correctness model (no metadata carried per-event, no vector-clock growth to manage), but introduces a real, shared bottleneck and a single point that must scale to the SYSTEM's aggregate event rate, not any one partition's share of it, this is the direct trade against the partition-local approaches above.

Probabilistic filters as a front-end. Regardless of which exact mechanism (partition-local, vector-clock, or centralized store) provides ground truth, a Bloom-filter-style probabilistic pre-check can reduce the load on the exact mechanism by cheaply filtering out the common case (genuinely new IDs) before paying the cost of a cross-partition/vector-clock comparison or a centralized-store lookup.

Correctness windows. None of these mechanisms (except an unbounded centralized store) can guarantee correctness FOREVER; each needs an explicit, bounded window (how far apart, in time or sequence distance, two events can be and still be correctly recognized as duplicates) beyond which correctness is not guaranteed, a deliberate, sized trade-off, not an oversight.

Cleanup strategies. Partition-local sequence watermarks: drop tracking state for sequences far below the current watermark. Vector clocks: prune entries for nodes that have not contributed for longer than the correctness window (directly addressing the unbounded-growth risk named above). Centralized store: TTL-based expiry relative to the same correctness window.

Worked example

A cross-service system uses per-aggregate (per-entity) sequence numbers: each entity has its OWN monotonic sequence, not a global one, and a bounded reordering buffer holds up to 50 out-of-sequence events per entity before flagging a probable gap. For entity order-789, sequence numbers 1 through 10 have been correctly applied in order; sequence 12 arrives (11 is missing, likely delayed in transit). The reordering buffer holds sequence 12, waiting for 11, up to a bounded timeout or buffer-depth limit:

If sequence 11 arrives within that bound, order is correctly restored and both 11 and 12 apply in the right sequence. If it never arrives (a genuine loss, not just delay), the bounded buffer prevents unbounded memory growth waiting indefinitely for a sequence that may never come, and the gap is surfaced as a detected anomaly rather than silently held forever.

Trade-offs and pitfalls

• Common mistake: assuming partition-local sequence numbers alone solve cross-topic deduplication. As stated above, this is a real, common gap: a scheme that works perfectly within one partition can completely miss a duplicate that crosses partition or topic boundaries, exactly why partition-local sequences plus wrap-around alone needed the additional mechanisms this answer covers.
• Common mistake: an unpruned vector clock. Left unbounded, vector-clock metadata can eventually exceed the size of the event payload itself for a long-lived, many-node system; this limitation is real and requires an explicit, deliberate pruning policy, not an assumption that vector clocks are "just a bit of extra metadata."
• The correctness-window trade-off applies uniformly across all these mechanisms, and should be sized against the system's OWN measured maximum realistic delay/reordering distance, not a default; too narrow, and legitimate late arrivals are treated as new (a real duplicate risk); too wide, and metadata/state overhead grows for diminishing benefit.
• A centralized dedup store trades metadata complexity for a scaling bottleneck, the opposite trade from vector clocks; for a system with enough throughput, this bottleneck can become the binding constraint the partition-local or vector-clock approaches were specifically chosen to avoid.

Direct answer. A merge join needs both inputs to already be sorted on the join key (either because an index provides that order for free, or because the optimizer decided an explicit sort was worth paying for), and it wins over a hash join specifically when that sortedness is cheap to obtain and the two inputs are both large, since it then needs no in-memory hash table at all, just a single linear walk through both sorted streams.

Structured elaboration. Given two inputs sorted on the join key, a merge join advances through both in lockstep, comparing the current key from each side and advancing whichever is behind, producing matches as it goes; this is a single linear pass with essentially no extra memory needed beyond tracking the current position in each input, which makes it very cheap once the sortedness is already available. A hash join instead builds an in-memory hash table from one side (a real memory cost, and a potential spill-to-disk cost if that side is large) and probes it with the other; it doesn't need either input sorted, which is exactly why it's the more common default for two large, unsorted inputs.

Merge join suffers when the required sortedness ISN'T already available and has to be produced with an explicit sort step, since sorting a large input from scratch can be as expensive as (or more expensive than) simply building a hash table would have been, at which point the merge join's own advantage evaporates and a hash join becomes the better choice. To prepare data to make a merge join a live, attractive option, ensure both sides have an index on the join key (so the sort comes "free" from index order) or that the join key is already the table's natural clustering order, rather than relying on an explicit, expensive sort step to manufacture that order on the fly.

Worked example. Joining two large fact tables that are BOTH naturally maintained in sorted order on the join key (say, both clustered or indexed on the same surrogate key by construction) is close to the ideal case for a merge join: the sortedness is free, and the join itself becomes a cheap linear pass with no hash table memory pressure at all, which can beat an equivalent hash join, especially under tight memory constraints where a large hash table would otherwise risk spilling.

Trade-offs and pitfalls. Don't assume a merge join is "the sophisticated choice" and therefore always preferable; it only wins when sortedness is genuinely cheap, and forcing one via a hint on two unsorted large inputs, paying for an explicit sort neither input naturally had, is very often worse than just letting the optimizer's default hash join choice stand.

Direct answer

Build small, deterministic pytest fixtures for a transformation function, then assert four
things separately: schema (columns and dtypes), value ranges and null handling, invariants
(uniqueness, row counts), and full content equality via pandas.testing.assert_frame_equal
rather than manual loops or ==.

Structured elaboration

• Isolate the transform as a pure function (DataFrame in, DataFrame out) so it can be
  tested without touching the real pipeline's I/O.
• Fixtures are small and synthetic: a pytest.fixture returning a literal pd.DataFrame
  with just enough rows to exercise the interesting cases (a normal row, a row with a null).
  Do not sample production data into a fixture; you want the values pinned and readable.
• One behavior per test: a schema/dtype test, a values-and-nulls test, an invariants test
  (uniqueness, expected row count), each independently informative when it fails.
• Use assert_frame_equal, not ==: it correctly handles float tolerance, dtype
  comparison, and treats not-a-number (NaN) as equal to itself, and it prints a readable diff
  on failure instead of a confusing elementwise boolean frame.
• Sort and reset the index before comparing: pandas does not guarantee row order carries
  meaning unless you sorted it yourself, so compare on sort_values(key).reset_index(drop=True)
  to avoid flaky failures caused by incidental ordering.

Worked example

# tests/test_transformations.py
import pandas as pd
import pytest
from pandas.testing import assert_frame_equal

# Example transformation to test
def transform(df):
    df = df.copy()
    df['amount'] = df['amount'].astype(float).fillna(0.0)
    df['status'] = df['status'].fillna('unknown').astype('category')
    df['amount_usd'] = (df['amount'] * 1.1).round(2)
    return df[['id', 'status', 'amount', 'amount_usd']]

@pytest.fixture
def input_df():
    return pd.DataFrame([
        {'id': 1, 'status': 'complete', 'amount': 10},
        {'id': 2, 'status': None, 'amount': None},
    ])

@pytest.fixture
def expected_df():
    return pd.DataFrame([
        {'id': 1, 'status': 'complete', 'amount': 10.0, 'amount_usd': 11.0},
        {'id': 2, 'status': 'unknown', 'amount': 0.0, 'amount_usd': 0.0},
    ]).astype({'id': 'int64', 'status': 'category', 'amount': 'float64', 'amount_usd': 'float64'})

def test_schema_and_dtypes(input_df, expected_df):
    out = transform(input_df)
    assert set(out.columns) == set(expected_df.columns)
    for col in expected_df.columns:
        assert out[col].dtype == expected_df[col].dtype

def test_values_and_nulls(input_df, expected_df):
    out = transform(input_df)
    assert out['id'].isna().sum() == 0
    assert out['status'].isna().sum() == 0
    assert (out['amount'] >= 0).all()
    assert (out['amount_usd'] >= 0).all()
    out_sorted = out.sort_values('id').reset_index(drop=True)
    exp_sorted = expected_df.sort_values('id').reset_index(drop=True)
    assert_frame_equal(out_sorted, exp_sorted, check_dtype=True, atol=1e-6)

def test_invariants_no_duplicate_ids(input_df):
    out = transform(input_df)
    assert out['id'].is_unique

Running pytest tests/test_transformations.py -v executes all three tests green: the
transform fills the missing amount with 0.0, the missing status with 'unknown', and
computes amount_usd from the filled amount, matching expected_df exactly.

Key points

• Fixtures keep tests small, deterministic, and hand-verifiable.
• pandas.testing.assert_frame_equal is the correct equality tool: it handles floats, dtypes
  and NaN semantics that == gets wrong.
• Sort and reset the index before comparing to avoid ordering flakiness.
• One responsibility per test: schema, nulls/ranges, invariants, content equality.

Complexity

Fixture construction and assertions are O(n) in the number of test rows, which should stay
in the tens, not thousands (this is a correctness test, not a benchmark). assert_frame_equal
is O(rows x columns).

Edge cases

Empty input DataFrame, a column that is entirely null, duplicate keys in the index, an
unexpected extra or missing column, a numeric column that silently arrived as object dtype
because one row held a string, and timezone-aware datetime columns (assert_frame_equal
treats naive and timezone-aware datetimes as different types, so a mismatch there fails loudly,
which is the point).

Trade-offs and pitfalls

• Fixtures that grow large stop being unit tests: keep them small enough to verify by eye.
• Asserting exact column order when order carries no business meaning makes tests brittle to
  harmless refactors; compare set(out.columns) (as above) or sort columns explicitly when
  order genuinely does not matter.
• The dtype check is the one most often skipped, and it is exactly the class of bug this suite
  exists to catch: two DataFrames can print identically and still fail assert_frame_equal on
  dtype (int64 vs pandas' nullable Int64), which is the kind of silent schema drift that
  breaks a downstream join or aggregation.
• Fail loud, not quiet: raise or assert rather than logging a warning and continuing, since the
  whole point of schema testing is to stop bad data before it reaches production.

Direct answer

Add a schema-validation step at the ingestion boundary, using something like JSON Schema or Pydantic, that runs on every real response as part of the pipeline, not just at test time, so drift fails fast with a clear error instead of silently flowing downstream. Pair that with a scheduled contract test against the live upstream, not just mocked fixtures, so the team learns about a breaking change before a batch job does.

Structured elaboration

• Define the contract explicitly: required fields, types, and value constraints, not just "the response was 200 OK."
• Validate at the boundary: check every real response against the schema as it enters your system, and quarantine records that fail rather than letting a type mismatch propagate through several transformations before it's hard to trace back.
• Contract-test against the real upstream, scheduled independently of your own deploys: mocked fixtures will happily keep passing forever after the real API changes, because the mock never changes.
• Version and alert on schema changes, even for fields you don't currently use, since today's ignored field can become tomorrow's dependency.

Worked example

A pipeline ingests a partner's product feed where "price" was always a decimal like 19.99. The partner changes their API to a nested object with an amount in cents and a currency code. A schema check asserting price is a number fails immediately with "price: expected number, got object" and quarantines the batch, instead of the pipeline coercing the object to a missing value and silently loading zero-priced products.

Trade-offs and pitfalls

An overly strict schema that rejects any unknown extra field creates noisy failures every time upstream adds something unrelated; a good contract distinguishes fields you depend on from fields you ignore. Mock-only contract tests give false confidence because the mock and the real API can drift apart silently for months.

What the interviewer probes next

Whether the candidate distinguishes a test that checks your own parsing code from a test that checks the upstream contract itself.