Python Approach & Data Structures

The reasoning

All the dict-based options are the same algorithm — one pass, hash per row, amortised O(1) insert — and differ only in how they handle the first time a key appears. `dict.get(k, [])` rebuilds and reassigns; `setdefault` is one call but always constructs the empty list even when the key exists; `defaultdict(list)` moves the branch into C. At a million rows the difference is small but real, and `defaultdict` is also the clearest to read.

`itertools.groupby` is the odd one out and the one worth being careful about. It never builds a dict: it walks the iterable and starts a new group whenever the key changes from the previous row. On unsorted input that means `eu, us, eu, us` produces four groups, and if you materialise them into a dict the later ones overwrite the earlier ones — you get the last run of each key and lose everything before it, with no error. The snippet below shows exactly that.

What earns groupby its place is memory. It is streaming: it never holds more than one group, so it is the right tool for a file that is already sorted by the grouping key — which files landing from a warehouse export frequently are — or for data you can afford to sort first. Sorting is O(n log n) and requires materialising, so it is a real trade rather than a free win.

The senior answer names the deciding question: does the whole result fit in memory? If yes, `defaultdict` and stop thinking about it. If no, sort (or arrange for sorted input) and stream with `groupby`, processing each group as it completes — and at that point also consider whether the grouping belongs in Python at all rather than in the warehouse that already has the data.

The reasoning

All three produce identical dicts. `d[k] = d.get(k, 0) + 1` does a lookup and a store per row in Python; `defaultdict(int)` moves the missing-key branch into C; `Counter(iterable)` does the entire loop in C, which is why it is meaningfully faster on large inputs and shorter to read.

`Counter` also brings the operations you usually want next. `most_common(n)` uses `heapq.nlargest` under the hood, so getting the top 10 of a million distinct keys is O(n log 10) rather than the O(n log n) of sorting everything. `Counter` objects add and subtract (`a + b`, `a - b`), which makes combining per-partition counts trivial, and `total()` gives the sum.

The behaviour to know is that indexing a missing key returns 0 *without* inserting it — unlike `defaultdict`, which inserts. That makes `Counter` safe to probe (`counts["signup"]` is 0 and the key stays absent), which matters when you go on to iterate the keys.

Where `defaultdict(int)` genuinely wins is when the value is not a count: summing amounts, tracking a maximum, accumulating into a set or list. And where all of them lose is when the cardinality is huge — a hundred million distinct keys is a hundred million Python objects, at which point the honest answer is that the aggregation belongs in the warehouse, or in a sketch like HyperLogLog if approximate is acceptable.

The reasoning

A list stores items in order and knows nothing about their values, so `in` walks it until it finds a match — averaging half the list per hit, all of it per miss. A set stores items in a hash table, so `in` computes one hash and looks at one bucket. Going from 10^11 comparisons to 10^6 hashes is not an optimisation, it is the difference between an overnight job and a two-second one.

The cost is building the set: one pass and roughly 32–60 bytes per entry, which is real but almost always the right trade the moment you do more than a handful of lookups. The rule of thumb worth saying out loud is that a lookup structure pays for itself at about the point where the number of lookups exceeds a small constant, so the only case where the list is fine is a genuinely one-off check.

The constraint is hashability: members must implement `__hash__`, which means immutable in practice. Lists and dicts raise `TypeError: unhashable type` — so if your key is a composite, use a tuple `(dataset, day)` or a frozen dataclass, not a list. This is the same requirement dict keys have, for the same reason.

Two more things worth knowing: use a `dict` rather than a `set` if you need the matched row and not just a yes/no, because the lookup cost is identical and you avoid a second pass; and a set silently deduplicates, which is usually what you want but will change your counts if you were relying on the reference collection having repeats.

The reasoning

`defaultdict` implements `__missing__`, which the subscript operator calls when the key is absent. It runs the factory, *stores* the result, and returns it. That is exactly what makes `d[k].append(v)` work in one line — the list has to be stored for the append to persist — and it means there is no way to distinguish reading from writing through the subscript.

So a perfectly reasonable-looking probe mutates the structure. `if seen["us"]:` creates `"us": []`, and now `"us" in seen` is True, `len(seen)` is one higher, and iterating the keys yields a region that never appeared in the data. Nothing raises, and the error surfaces later as a count that is slightly wrong.

`.get(k)` goes through `__getitem__`'s sibling path and never inserts, so it is the correct way to probe. `k in d` is also safe. The habit worth forming is to use subscript only where you intend to write, and `.get` everywhere you intend to read — which is good practice with a plain dict too, and mandatory here.

This is also why passing a `defaultdict` out of a function is a mild code smell: the caller has no idea that reading it changes it. Converting with `dict(grouped)` at the boundary costs one pass and hands back something that behaves the way every reader expects.

The reasoning

Iterating a file object already yields one line at a time, so the memory question is entirely about what you do with them. `f.readlines()` or `list(reader)` materialises everything and is the only real mistake available here. Everything else follows from keeping the data moving.

Structuring it as separate generator functions — `read → parse → filter` — costs nothing at run time, because a generator holds a resume point rather than a buffer, and buys two things. The whole pipeline occupies a couple of hundred bytes regardless of the input size, and each stage can be tested by handing it a three-element list, with no file and no fixtures.

The part that does grow is the aggregate. Summing by region is bounded by the number of distinct regions, which is fine. Summing by `customer_id` across 200 million customers is not, and that is the point at which the honest answer changes: sort by key and aggregate in a streaming pass, or spill to disk, or push the aggregation into DuckDB or the warehouse. Knowing which of your dicts is bounded is the actual skill.

Two practical notes that come up as follow-ups. Use `csv.reader` rather than `line.split(",")` unless you can guarantee no quoted commas, because the naive version is wrong for real CSV. And open with an explicit `encoding` and `newline=""`, because the default encoding is platform-dependent and will decode differently on someone else's machine.

The reasoning

The three things a generator cannot do are the three reasons to materialise. It has no `len()` — the object does not know how many items it will produce. It has no indexing, so `rows[0]` is a `TypeError`. And it is single-pass, so a second consumer sees nothing at all, which is the dangerous one because it returns an empty result rather than raising.

Sorting also forces it, and there is no way around that: `sorted()` must see every element before it can emit the first, so it materialises internally regardless of what you pass in. Same for `max`, `min` and `sum` — those consume the whole thing but only hold one value, so they are fine on a generator; it is specifically the operations that need the *collection* that require the memory.

The API-design case is the one worth raising unprompted. A function that returns a generator has handed the caller something they can consume exactly once, and if they store it on an object and read it twice, the second read is empty. Returning a list is the safer public contract; returning a generator is right when the caller is a pipeline stage that you know consumes it once. Say which one you mean in the type hint — `Iterator[Row]` versus `list[Row]` is a real difference.

The way to phrase the decision in an interview: materialising is not a failure, it is a choice with a stated cost. "I will call `list()` here because I need two passes and the reference data is 50,000 rows" is a good answer. Materialising by reflex, and only finding out the size in production, is not.

The reasoning

Start with the number. A set of a million ints reports about 33 MB for the hash table itself, and each `int` object is another 28 bytes; strings are worse, at roughly 50 bytes plus the characters. So 50 million integer ids is on the order of 3–4 GB before any of your other data — fine on a 32 GB box, fatal in a 2 GB container, and the whole point is that you should be able to work that out rather than discover it.

When it fits, a set is unambiguously right: one hash per id, O(1) membership, and it streams — you never hold the input, only the distinct keys. If you need to preserve first-seen order, `dict.fromkeys(ids)` does the same job and keeps insertion order, for slightly more memory.

When it does not fit, the options are ordered by how much you are willing to give up. Sort the ids externally and drop adjacent duplicates in one streaming pass — exact, O(n log n), and bounded memory because the sort spills to disk. Or partition by `hash(id) % k` into k files and dedupe each independently, which is exact and parallel and is precisely what a distributed engine does for you. Or, if a small false-positive rate is acceptable, a Bloom filter answers membership in a couple of bits per element — but it can only say "definitely not seen" or "probably seen", so it is suitable for skipping work, not for correctness.

The framing that lands well: dedup at this size is a question about where the state lives. In memory it is a set; on disk it is a sort; across machines it is a partition key; and if you are already writing to a warehouse or an ACID table format, the deduplication is a MERGE and none of this is your problem.

The reasoning

The instinct is `rows[i:i+1000]` in a loop, and it cannot work on a generator — there is no indexing and no length. What you need is something that pulls n items and stops, which is `islice`. Wrapping the source in `iter()` first is the essential detail: `islice` must resume from where the last batch stopped, and it can only do that if it is slicing an *iterator* rather than restarting on the iterable each time.

`while chunk := list(islice(stream, n)):` is the idiom, and it terminates naturally because the final partial batch is truthy and the one after it is empty. Python 3.12 added `itertools.batched`, which does the same thing in C and yields tuples — shorter and slightly faster, with the same laziness.

The thing to say about batch size: it is a trade between request overhead and blast radius. Bigger batches amortise the round trip, and they also mean a single failure invalidates more work and a retry re-sends more rows. If the sink is not idempotent, batch boundaries are also your retry boundaries, so the size is a correctness parameter and not only a performance one.

Two related habits: attach a batch identifier so a retry can be recognised as a duplicate downstream, and make sure the final partial batch is flushed — a loop that only writes when the buffer reaches exactly n silently drops the tail, which is a genuinely common bug and one that only shows up when the row count is not a multiple of the batch size.

The reasoning

The axis that matters is how a mistake fails. With a tuple, `row[3]` is silently wrong when someone inserts a column — no error, just the wrong field, forever. With a dict, `row["custmer_id"]` is a `KeyError` at run time, which is late but at least loud. With a dataclass or NamedTuple, a bad field name is caught by the type checker and by your editor before the code runs.

Dicts earn their place at the edges. JSON arrives as a dict, the schema may vary between vendors, and forcing it into a typed object before you have validated it just moves the failure. The pattern that works is: parse to a dict, validate, construct a dataclass, and let everything downstream of that boundary be typed — so the untyped region is small and named.

Between dataclass and NamedTuple: a NamedTuple *is* a tuple, so it unpacks, compares and indexes like one, and it is immutable and hashable without asking. That makes it excellent for keys and for interop with code that expects positional rows. A dataclass is mutable by default, supports `field(default_factory=...)`, methods, inheritance and `slots=True`, and reads better when the record has behaviour rather than just fields.

The one to be wary of is the dict-as-record habit in the middle of a long pipeline. It is fast to write and it defers every schema question to run time, so five stages later nobody can say what keys a row has without reading all five. That is exactly the cost a dataclass removes, and the reason it is worth the extra ten lines.

The reasoning

A normal instance stores its attributes in a per-object dictionary, which is flexible and expensive: the dict has its own header, hash table and growth slack, and there is one per object. `__slots__` — which `@dataclass(slots=True)` generates for you — declares the attribute names up front so the interpreter can lay them out in a fixed array on the object itself and skip the dict entirely.

The measured difference on the two-field class below is 296 bytes against 32. Multiply by 50 million and it is the difference between roughly 15 GB and 1.6 GB, which is exactly the kind of number that decides whether a job needs a bigger box. Attribute access also gets marginally faster, because it is an index rather than a hash lookup.

What you give up is dynamic attributes: assigning anything not declared raises `AttributeError`. That is usually a feature — it catches typo'd attribute names at the point of assignment rather than leaving a silently-ignored field — but it breaks code that monkey-patches instances or attaches ad-hoc metadata, and it interacts awkwardly with multiple inheritance if two parents both define slots. Note also that `weakref` support goes away unless you add `__weakref__` explicitly.

The honest framing: slots are a memory optimisation, so apply them when memory is the constraint you have measured, not by default. And if you are holding 50 million rows to do columnar arithmetic on them, the better answer may be not to hold objects at all — arrays, pyarrow tables or a dataframe store the same data in a fraction of the space because they do not pay per-row object overhead at all.

The reasoning

At run time, `rows: int` puts an entry in `__annotations__` and nothing else. The interpreter never compares the value to the annotation. A type checker will flag the call if it can see it, but data arriving from a file, an API or a queue is invisible to static analysis by definition — which is exactly where wrong types come from.

The consequence is a wrong value travelling a long way. `Partition(dataset=123, rows="not a number")` constructs happily, prints plausibly, and fails later at something like `rows + 1` — three stages away, with a traceback pointing at innocent code. Or it never fails at all and gets written to a warehouse column that quietly accepts it.

Validation belongs at the boundary, once, where the data crosses from untrusted to trusted. In a dataclass that is `__post_init__`, which runs after the generated `__init__` and is the natural place for both type checks and business rules ("amount must not be negative"). At a bigger boundary, pydantic or attrs earn their dependency: they generate the checking from the same annotations, coerce where you ask them to, and produce error messages that name the field and the row.

The pattern to describe is a narrow validated edge and a typed interior. Parse to dicts, validate and construct typed records at the entry point, and let every function downstream declare and trust its types. That way there is exactly one place that deals with malformed input, and it is the place with the context to report which file and which line it came from.

The reasoning

A tuple of the components is hashable, compares element by element, and cannot collide — `("a-b", "c")` and `("a", "b-c")` are distinct, where `"a-b-c"` cannot tell them apart. It is also faster, because it skips the formatting, and it sorts sensibly for free.

The string version is worth understanding rather than just rejecting, because the failure is subtle. `f"{customer_id}-{day}"` is fine until a customer id contains your delimiter — and ids from an upstream vendor eventually do. Two distinct keys map to one string, the dedup silently merges two customers' events, and the resulting number is wrong in a way no test with clean fixtures will find.

A frozen dataclass is the tuple with names. It costs a little more memory and buys self-documenting call sites: `Key(customer_id=..., event_day=...)` cannot be built with the fields in the wrong order, which a two-string tuple absolutely can. Use it when the key is constructed in several places or passed across module boundaries; use a plain tuple when it is local to one function.

Two details worth stating. Normalise before keying — trim whitespace, case-fold, and parse dates to a `date` rather than keeping whatever string format arrived — because `"2026-03-01"` and `"2026-3-1"` are different keys and the same day. And keep `None` out of keys, since a NULL customer id groups every unknown customer together, which is almost never what you want.

The reasoning

The work is waiting, not computing, so the GIL is irrelevant — a thread blocked on a socket has released it. A pool of 20 turns 100 seconds into roughly 5, and that number is set by concurrency, not by CPU.

The sizing question is the interesting half. More threads stop helping the moment you saturate the remote service, and past that you are generating 429s and getting throttled — which is slower than the smaller pool would have been. So the real limit is the API's documented rate limit, and the polite implementation respects `Retry-After` and backs off exponentially with jitter rather than hammering. Bounding the pool also bounds memory, since each in-flight response is held.

Asyncio with `httpx` or `aiohttp` is the other good answer and scales further — thousands of concurrent requests cost coroutines rather than thread stacks. It is the right choice for a service already built on an event loop or for genuinely high fan-out. For a batch job doing 500 calls with a blocking client, it is a rewrite that buys nothing measurable, and one synchronous call left in the path silently removes the benefit.

A process pool is the wrong tool: you would pay process start-up and pickling for workers that spend all their time asleep. And whichever model you pick, the pagination detail matters more than the concurrency — if pages must be fetched in sequence because each response carries the next cursor, you cannot parallelise the walk at all. You parallelise across partitions, date ranges or keys instead, which is a design decision made before the executor.

The reasoning

A process pool has three costs the serial version does not: starting the workers, pickling each argument and result, and copying those bytes through a pipe. Parallel speedup only appears when the per-task compute is large relative to that overhead. A task that parses one 200-byte row spends more time in transit than in work, and the pool loses.

The fix is granularity. Give each worker a unit of work that is meaningful on its own — one file, one partition, one byte range — so the argument is a path (tiny to pickle) and the compute is seconds rather than microseconds. If you must map over rows, pass a large `chunksize` so the pool batches them; the default of 1 for `ProcessPoolExecutor.map` is exactly the pathological case.

Return values matter as much as arguments. A worker that parses a file and returns a million dicts pickles all of them back to the parent, and you have simply moved the memory problem while adding a serialisation step. The pattern that works is for each worker to write its own output — one file per partition — and return a small summary: path, row count, checksum. The parent then does no heavy lifting at all.

The other answer worth giving is to leave Python. JSON parsing is the archetypal case where a C library wins outright: `orjson` is several times faster single-threaded, and pyarrow or DuckDB will read the files in parallel with the GIL released and no pickling at all. Multiprocessing is what you use when the hot work genuinely must be Python.

The reasoning

Both run the same tasks with the same parallelism — the difference is purely in how results are handed back. `map` preserves correspondence with the input, which is what you want when you are zipping results against the inputs that produced them or writing an ordered output file. The cost is head-of-line blocking: if the first task takes 30 seconds and the rest take one, you get nothing for 30 seconds and then everything at once.

`as_completed` takes a collection of futures and yields each as it finishes. That lets downstream work overlap with the remaining fetches, which matters when you are writing results to a sink or streaming them onward. You lose the input correspondence, so if you need to know which input a result came from, keep a `{future: input}` dict and look it up — that is the standard idiom and it is worth being able to write from memory.

Error handling differs too, and it is the part that catches people. With `map`, an exception in a task is re-raised when you reach that result in the iteration, so tasks after it never surface. With `as_completed`, calling `future.result()` raises for that one future and the loop continues — which makes partial success much easier to handle. For a batch of 500 fetches where a handful will fail, that is a real advantage.

The snippet makes the ordering concrete: three tasks whose durations are the reverse of their submission order come back reversed under `as_completed` and in submission order under `map`. Note that `map` returns a lazy iterator, so nothing is yielded until you consume it — but every task starts as soon as it is submitted either way.

The reasoning

Threads share memory, so passing a closure to a thread is free — it is the same object. A process does not: the argument is serialised, sent through a pipe, and rebuilt on the other side. Pickle handles functions by writing down "module X, name Y" and importing that at the far end, which works for anything defined at module level and fails for lambdas, nested functions and anything else without a stable import path.

The error message names it directly — `PicklingError`, "attribute lookup <lambda> on __main__ failed" — because pickle went looking for the name and there was nothing there. The same restriction hits open file handles, database connections, sockets and locks: they are not meaningful in another process, so either they refuse to pickle or, worse, they arrive as something that looks usable and is not.

The workarounds in order of preference: define the worker at module level and pass data as arguments; use `functools.partial(fn, config)` to bind extra parameters, since a partial of a module-level function pickles fine; or make the worker a small class with `__call__` and picklable state. If the worker genuinely needs a connection, open it *inside* the worker — a lazily-initialised per-process client is the standard pattern, and it is also what you want for connection pooling anyway.

This constraint is the reason worker functions end up looking like pure functions of plain data, which is a good outcome. It also explains why `if __name__ == "__main__":` is mandatory under the spawn start method: the child re-imports your module, and without the guard it would re-execute the pool creation and fork bomb itself.

The reasoning

The nested loop compares every order against every customer, so the work is the product of the sizes. At 10 million and 100,000 that is a trillion comparisons — hours to days. Indexing the small side turns each order into a single hash lookup, so the total is one pass to build the index plus one pass over the orders.

Which side to index is decided by memory: build on whichever fits, stream the other. That is exactly what a database calls a hash join, and the "build side" and "probe side" language transfers directly — worth using in an interview, because it shows you know you are hand-implementing something the engine does for you.

The join semantics still have to be chosen deliberately, and this is where Python code silently differs from SQL. `index[key]` raises `KeyError` on a miss, which is an inner join that crashes; `index.get(key)` gives you a left join with `None`; and if the build side has duplicate keys, `{c["id"]: c for c in customers}` keeps only the last one and silently drops the rest. If duplicates are legitimate you need `defaultdict(list)` and a nested loop over the matches — which is the fan-out that inflates row counts, exactly as it does in SQL.

The senior point to land: if both sides are large enough that neither indexes comfortably, stop. Sort-merge both sides on the key and walk them in lockstep, or partition by `hash(key) % k` and join partition-wise, or accept that you are re-implementing a database and push the join into DuckDB, Spark or the warehouse. Joining 10 million against 10 million in a Python dict is possible and is rarely the right call.

The reasoning

The space difference is the one that decides it. `sorted(rows)[:10]` must hold every row to sort it, so 50 million rows have to fit in memory before you discard 49,999,990 of them. `nlargest` holds ten: it pushes each incoming row onto a bounded heap and pops the smallest, so it consumes an iterator and never grows. That is the difference between a job that needs a large instance and one that runs anywhere.

Time follows the same shape. Comparing against the heap root is O(log k) with k = 10, so the total is O(n log 10) rather than O(n log n). CPython's `sorted` is extremely fast, so for small n the sort can win outright — the crossover is roughly when k is small relative to n, which is exactly the top-N case.

Both take a `key`, which is how you do this over records rather than scalars: `heapq.nlargest(10, rows, key=lambda r: r["amount"])`. Ties are broken by input order in both, and neither is stable in a way you should rely on — if ties matter, put the tiebreaker in the key tuple explicitly.

Two edges worth knowing. When k approaches n, `nlargest` degrades and the docs themselves suggest sorting instead. And if you need the top N *per group* — top 10 customers per region, the classic — the answer is a dict of heaps, one bounded heap per key, which keeps the memory proportional to the number of groups rather than to the data. That is the version that actually comes up in pipeline work.

The reasoning

A key tuple sorts lexicographically, so `(region, -amount)` gives region ascending and amount descending in one pass. It is the cleanest form, and it only works when the descending field is numeric — you cannot negate a string or a date, and `reverse=True` applies to the whole comparison rather than to one component.

Stability is what covers the rest. Python's sort guarantees that records comparing equal keep their previous relative order, which means you can decompose a multi-key sort into a sequence of single-key sorts applied from least significant to most significant. Sort by amount descending, then sort by region ascending, and the second sort preserves the amount ordering inside each region. The snippet shows both routes producing the identical result.

Two performance notes. `operator.itemgetter("region")` is meaningfully faster than the equivalent lambda because it avoids a Python-level call per comparison, and `itemgetter` takes multiple fields — `itemgetter("region", "amount")` builds the tuple in C. And each pass is a full O(n log n) sort, so the two-pass version costs roughly twice the single-key one; use the key tuple when you can and the decomposition when you cannot.

The related trap worth mentioning is sorting heterogeneous data. `sorted([1, "a"])` raises `TypeError` in Python 3, which is a feature — but it means a column with mixed types from a messy source blows up at sort time rather than at parse time. Normalise the type in the key function (`str(r["id"])` or an explicit parse) if the source cannot be trusted, and be aware that changes the ordering.

The reasoning

Dataframes win when the operation is expressible over whole columns. A `groupby().sum()` runs in C over contiguous arrays and will beat a Python loop by one to two orders of magnitude. They also give you a huge amount of correct, tested behaviour for free — joins, pivots, resampling, null handling — that is genuinely tedious to write by hand.

They lose in three situations. Memory: pandas typically wants several times the file size in RAM, and object-dtype columns (which is what strings become) are pointers to individual Python objects, so a "small" 2 GB CSV of text can become 10 GB in memory. Shape: deeply nested JSON has to be flattened before a dataframe helps, and if the per-row logic is genuinely irregular you end up in `.apply`, which is a Python loop with dataframe overhead on top — the worst of both. Streaming: pandas is fundamentally in-memory, so a source larger than RAM needs chunking, which is exactly the plain-Python generator structure with extra steps.

Polars changes some of those numbers rather than the principle. It is columnar and multithreaded with the GIL released, uses Arrow memory so strings are not Python objects, and its lazy API can push filters and projections down and stream larger-than-memory data. Where pandas would need chunking, polars often just runs. DuckDB fills the same niche from the SQL side and reads Parquet and CSV out-of-core.

The framing to offer: choose by the shape of the operation, not by the size of the dependency. Columnar work over a table — dataframe. Row-at-a-time work over a stream, or arbitrary nested structures — plain Python generators. And if the whole job is a `SELECT ... GROUP BY` over files, the honest answer is often that neither Python nor pandas should be doing it.

The reasoning

**The quadratic.** `df = pd.concat([df, chunk])` inside a loop allocates a brand new frame each time and copies everything accumulated so far. By file 500 you have copied the first file's rows 500 times. Total work is proportional to the square of the data. The same trap applies to the older `df.append`, which is why it was deprecated and removed.

**The fix is to defer.** Collect the frames in a list and concatenate once — a single allocation sized correctly, and one pass. Add `ignore_index=True` unless the source indices mean something, because otherwise you get 500 rows numbered 0 and any later `.loc` lookup is ambiguous.

**Add provenance while you are reading.** Stamping the source filename onto each chunk costs nothing and is the difference between "row 84,102 is wrong" and "row 84,102 came from `orders_2026_03_14.csv`". On a directory load it is the single most useful column you can add.

**The failure modes that actually bite in production.** Columns in a different order across files — `concat` aligns by name, which is what you want, but a typo'd header becomes a new mostly-null column rather than an error. Inconsistent dtypes — one file where an id column happens to contain a non-numeric value makes that column `object` and silently poisons the join downstream. And an empty file produces an empty frame with no columns, which then contributes nothing and is easy to miss. Passing an explicit `dtype` and checking the final column set against the expected one catches all three.

**When 500 files becomes 50,000**, pandas stops being the right tool — the whole result has to fit in memory. That is the point to move to Parquet with a columnar reader, or to a query engine that reads the directory as one table.

The reasoning

**Why float fails.** A `float` is binary, and 0.1 has no exact binary representation any more than 1/3 has an exact decimal one. `0.1 + 0.2 == 0.30000000000000004`. One such error is invisible; summed over ten million transactions it is a reconciliation break that nobody can trace, because every individual row looks right.

**Option one: integer minor units.** Store 1234 for £12.34 and do all arithmetic in integers, formatting only at the boundary. Addition and subtraction are exact, it is fast, and it maps directly to how payment systems and ledgers actually represent money. The cost is that you must remember the scale — and the scale is not always 2, since JPY has no minor unit and some currencies use 3 decimal places, so a hardcoded ÷100 is a real bug.

**Option two: `Decimal`.** Exact decimal arithmetic with a configurable precision and rounding mode. It expresses money naturally, it handles division and percentages without you tracking scale by hand, and it is slower and heavier than integers — which matters for tens of millions of rows in Python and not at all for thousands.

**Division and conversion are where both need care.** Currency conversion introduces a fraction: 100 GBP at 1.2734 is 127.34 exactly, and at 1.27345 it is not. Decide the rounding — `ROUND_HALF_EVEN` is the usual financial default because it does not bias totals upward the way `ROUND_HALF_UP` does — and quantise explicitly at the point of conversion rather than letting the precision drift. Allocating a total across lines has the same problem: split 100 three ways and you must decide who gets the extra penny, and *not* deciding means the parts do not sum to the whole.

**Never mix.** `Decimal('0.1') + 0.1` raises, which is helpful, but `Decimal(0.1)` silently constructs the *float* error into a Decimal — you must build from a string. And a database `NUMERIC` read through a driver that hands you floats has already lost the precision before your code sees it, so the type has to be right end to end.

The reasoning

**Three outputs, not one.** "Do these match" is not a boolean — it is *missing on the left*, *missing on the right*, and *present on both but different*. Returning a single list of differences collapses three distinct operational responses into one, and the person on the other end has to re-derive which is which.

**Index, then use set operations on the keys.** Build `{key: record}` for each side, then `a.keys() - b.keys()`, `b.keys() - a.keys()`, and for the intersection compare the values. Each dict build is O(n), each set operation is O(n), and the whole thing is linear with one dict per side in memory. The nested-loop version — for each row in A scan B — is O(n·m) and is the version people write first.

**Compare the fields you mean, not the records.** Two records that differ only in an ingestion timestamp are not a mismatch. Extract a comparison tuple of the fields that must agree and compare those, so the diff reports business differences rather than metadata noise. And use exact types — comparing a `Decimal` to a `float` will report a difference that is not one.

**The key is the hard part, same as always.** If the two systems identify rows differently, the mapping between their keys is the actual work and the diff is trivial afterwards. And if either side can have duplicate keys, a dict silently keeps the last one — so either assert uniqueness while building and fail loudly, or group into lists and compare multisets. Silently dropping a duplicate makes a reconciliation report that a duplicate-caused imbalance does not exist.

**When it does not fit in memory**, this becomes the sort-merge join: sort both sides by key and walk them together in one pass with constant memory. Same three outputs, and it is the reason external merge sort exists — at which point the honest answer is usually to do it in SQL instead.

The reasoning

**First, decide what state the computation needs.** A filter or a projection needs none — it is a generator pipeline and the file size is irrelevant. A sum or a count per key needs state proportional to the number of keys, not to the rows, which is usually small. Only a sort, a join against something equally large, or a distinct count genuinely needs everything.

**Stream by default.** Iterate the file object rather than calling `read()`, and chain generators so nothing materialises: parse, filter, transform, write. Memory then depends on your batch size and on the state you chose to keep, and both are numbers you can state rather than hope about.

**Bound the state you cannot avoid.** A distinct count over a huge key space is the classic — an exact answer needs every key in memory, and the honest options are a sketch such as HyperLogLog, a two-pass approach, or pushing it to a database. Say which trade you are making rather than pretending the problem is not there.

**Then the shape questions.** Is the file splittable — line-delimited rather than one giant JSON document? Is it compressed with a non-splittable codec, which forces a single reader? Could the source produce Parquet instead, so you read three columns instead of eighty? Those change the answer more than any code you write.

**And know when to stop.** Pure Python streams fine and is roughly an order of magnitude slower than a columnar engine on the same work. If the job runs nightly and takes forty minutes, leave it. If it is the critical path, the answer is DuckDB, Polars or Spark, and saying so is a stronger answer than optimising a loop.

The reasoning

**The first question is the read pattern.** Analytics reads a few columns of very many rows, and that is what columnar formats are for — Parquet reads only the columns you asked for and skips row groups whose statistics rule them out. A row format has to read every byte to find three fields.

**The second is the shape.** Deeply nested payloads survive JSON and NDJSON honestly; flattening them into CSV loses structure, and CSV has no types at all, so every consumer re-parses every value and they disagree about how. NDJSON is the streaming form: one object per line, appendable, and readable one record at a time.

**The third is schema evolution.** Avro carries its schema and has explicit rules for adding and removing fields, which is why it turns up in event pipelines where producers and consumers deploy separately. Parquet also carries a schema; the difference is that Avro is designed for the message and Parquet for the table.

**And the fourth is who else touches it.** CSV is the format everybody can open and nobody can trust: no types, ambiguous quoting, no null-versus-empty distinction. It is the right answer for a hand-off to a person and the wrong one for a pipeline stage, and "we have always used CSV" is a reason to ask what it costs.

**Say the trade, not the favourite.** Parquet is worse than NDJSON for tiny frequent appends, because small files are the thing that kills a lakehouse. Compression matters too: gzip is not splittable, so one 10 GB gzip file is one reader whatever your cluster size.

The reasoning

**Why a dict at the edge.** JSON and CSV hand you a dict, the keys vary, and you do not yet know the shape is right. Forcing it into a typed record before validation gives you a well-typed object full of unchecked values, which reads as safer than it is.

**Why a record inside.** After validation the keys are a contract. `record.amuont` fails immediately with the name in the message; `row.get('amuont')` returns `None` and surfaces three functions later as a total that is quietly low. That difference is worth more than any performance argument between the two.

**What you get for free.** A dataclass gives you a `__repr__` that names its fields — `Order(order_id=1042, sku='A1')` in a log line beats a dict dumped in insertion order — and an `__eq__` that makes tests read as comparisons rather than as dict spelunking. Frozen, it can be a key.

**When a dict stays right.** Genuinely variable keys, a passthrough that never inspects the payload, or a shape that changes per tenant. Wrapping those in a class buys nothing and adds a conversion nobody reads.

**And the practical marker.** Count the defensive `.get()` calls in the code downstream of the boundary. Every one of them is a place the shape was uncertain, and converting once turns all of them into attribute access that fails loudly at the one place the record was built.

The reasoning

**agg: one row per group.** `df.groupby("user")["amt"].sum()` returns one value per user: for the amounts 10, 20, 5, 5, 40 with users a, a, b, b, b it gives `a 30` and `b 50`. Use it when the output is a summary table.

**transform: same shape as the input.** `df.groupby("user")["amt"].transform("sum")` returns `[30, 30, 50, 50, 50]`: the group total repeated on every row, aligned to the original index. That is exactly what you need to add a column or compute a share: `df["amt"] / df.groupby("user")["amt"].transform("sum")` gives `0.333, 0.667, 0.1, 0.1, 0.8`.

**apply: any function, any shape.** `apply` passes each group to a Python function and stitches the results back. It can do things agg and transform cannot, such as returning the top two rows of each group, but it runs your Python code once per group, and the output shape depends on what the function returns. Prefer the built-in aggregations first.

**The mistake to avoid.** Computing the totals with `agg`, then merging them back by key, works but takes three steps and risks a many-to-many merge. `transform` does it in one step and cannot change the number of rows. (Checked on pandas 3.0.)

The reasoning

**What happened.** A merge produces one output row for every matching pair. If `customers` has two rows for `c1`, every order from `c1` is repeated twice. With 2 orders and 2 matching customer rows, the merge of this example returns **4** rows from 2 orders, and every total built on top of it is now double counted.

**How to catch it.** Pass `validate="m:1"` (many orders to one customer). If the right side has duplicate keys, pandas raises `MergeError: Merge keys are not unique in right dataset; not a many-to-one merge`. The other choices are `"1:1"`, `"1:m"` and `"m:m"`. Also assert that `len(result) == len(left)` after a left join.

**How to see the unmatched rows.** `indicator=True` adds a `_merge` column with `both`, `left_only` or `right_only` for each row, so you can count the orders that found no customer instead of letting them turn into nulls.

**How to stop it.** Fix the dimension: deduplicate it on the key with a clear rule (latest row, active row) **before** the merge, or add the missing second key to the join. Do not fix it by dropping duplicates after the merge, because by then you cannot tell which row was the right one. (Checked on pandas 3.0.)

The reasoning

**loc versus iloc.** With the index `[5, 6, 7, 8]`, `df.loc[6:7]` returns the rows labelled 6 **and** 7, because label slices include the end. `df.iloc[1:3]` returns the rows at positions 1 and 2, because position slices stop before the end. They give the same rows here only by coincidence. If the index is not a simple 0, 1, 2 count, mixing them up returns the wrong rows without an error.

**Series versus DataFrame.** A `DataFrame` is a two-dimensional table with a row index and column labels. A `Series` is one column (or one row) with an index. `df["v"]` is a Series, `df[["v"]]` is a one-column DataFrame, and `df.loc[5]` is a Series holding one row.

**The chained assignment.** `df[df.a > 1]["b"] = 9` first creates a new object with the filtered rows, then sets a value on that new object. The original `df` is untouched. In pandas 3, with copy-on-write, that is the defined behaviour and pandas emits a `ChainedAssignmentError` warning. In older versions the result depended on whether pandas had made a copy, which is why it was called SettingWithCopy.

**The correct form.** `df.loc[df.a > 1, "b"] = 9` selects rows and column in one step and writes to the original. We checked: on `a = [1, 2, 3]` and `b = [0, 0, 0]`, the chained form leaves `b` as `[0, 0, 0]` and the `.loc` form gives `[0, 9, 9]`.

The reasoning

**Why apply is slow.** With `axis=1`, pandas builds each row as a Series and calls your function in the Python interpreter, once per row. A few million rows means a few million Python calls. The work is the same as a `for` loop with extra overhead.

**What vectorised means.** An operation such as `df["price"] * df["qty"]` is handed to compiled code that loops over the arrays in one go. The result is identical to the row-by-row version, which we checked on a small frame: `apply(lambda v: v * 2)` and `df.x * 2` produced the same values.

**The vectorised toolbox.** Arithmetic and comparisons on columns, `np.where(cond, a, b)` or `Series.where` for if-else, `.str` and `.dt` accessors for text and dates, `pd.cut` for bins, `map` with a dict for lookups, and `merge` for joins. Most row-wise logic can be written with these.

**When apply is acceptable.** The logic really is row-wise and cannot be expressed on columns, or the frame is small. Then say it out loud: "I would use apply here, and if it were hot I would rewrite it as column operations, or move the step into SQL or Spark". Measure before you rewrite.

The reasoning

**NaN.** In a float column, `None` is turned into `NaN`: `pd.Series([1, None, 3])` has dtype `float64`, and the 1 and 3 become `1.0` and `3.0`. `NaN == NaN` is `False`, so you test with `isna()`, never with `==`.

**pd.NA and nullable types.** `pd.Series([1, None, 3], dtype="Int64")` keeps integers and shows the gap as `<NA>`. `pd.NA == 1` returns `<NA>`, not `False`, and using it in an `if` raises `TypeError`, because the answer to "is it equal" is unknown. This is the same three-valued logic as SQL NULL.

**Aggregations skip missing values.** `mean()` of `[1, NaN, 3]` is `2.0`, computed over two values, and `count()` does not count the missing one. The sum of a column that is entirely missing is `0.0`, not `NaN`, which can look like a real zero in a report. Pass `min_count=1` to get `NaN` when nothing is present.

**What to do with them.** Decide the rule first: drop the rows (`dropna`), fill with a default (`fillna`), or keep the gap and carry it. Count the missing values before and after a cleaning step, and write the rule down, because "fill with 0" and "ignore" give different totals. (Checked on pandas 3.0, where text columns default to a string dtype whose missing values also test true with `isna()`.)

The reasoning

**The default is strict.** `pd.to_datetime(series)` raises a `ValueError` when it meets a value it cannot parse, such as `"2026-13-45"` or `"not a date"`. That is a good default in a pipeline, because the load stops before bad data lands.

**Coerce, then count.** `errors="coerce"` turns every unparseable value, and every missing value, into `NaT` (not a time). Do not stop there: count the `NaT` values that were **not** missing before, and write those rows to a reject file. Otherwise a format change in the source silently becomes a column of nulls.

**Time zones.** `"2026-01-05 10:00:00+02:00"` and `"2026-01-05 08:00:00+00:00"` are the same moment. With `utc=True`, pandas converts both to `08:00:00 UTC`, which we checked. Without it, mixed offsets cannot be stored in one tz-aware column. Store UTC, and convert for display only.

**Be explicit about the format.** Give a `format=` when you know it. It is faster, and it stops `01/02/2026` from being read as January 2nd in one file and February 1st in another.

The reasoning

**The limit.** One `list_objects_v2` call returns up to 1,000 objects. If there are more, the response has `IsTruncated: True` and a `NextContinuationToken`. A job that calls it once, reads the keys, and moves on processes the first 1,000 files and reports success.

**The paginator.** `paginator = client.get_paginator("list_objects_v2")` and `for page in paginator.paginate(Bucket=bucket, Prefix=prefix):` does the token handling for you. Yield from it instead of building a list if the prefix is huge, so memory stays flat.

**The empty-page trap.** When nothing matches, the page has no `Contents` key at all. `page["Contents"]` then raises `KeyError`, so write `page.get("Contents", [])`.

**Things to say next.** Keys are returned in UTF-8 byte order, so the list is sorted, which lets you resume from a `StartAfter` key. A prefix is not a folder, so `data/2026-01` also matches `data/2026-010`: end the prefix with a slash when you mean a folder. S3 gives strong read-after-write consistency for new objects and lists, so a listing right after a write sees it. For billions of keys, read an S3 Inventory report instead of listing.

The reasoning

**The bug.** `f"... where name = '{name}'"` makes the value part of the SQL text. If the value is `x' OR '1'='1`, the condition becomes true for every row, and the query returns both users in the snippet. With `UPDATE` or `DELETE`, the same trick is far worse.

**The fix.** Put a placeholder in the SQL and pass a tuple of values: `db.execute("... where name = ?", (name,))`. The driver sends the statement and the values separately, so the value can never change the structure of the query. The same input returns no rows in the snippet. The placeholder style depends on the driver: `?` in sqlite3, `%s` in psycopg, `:name` in SQLAlchemy `text()`.

**What a parameter cannot do.** It stands for a **value**. It cannot be a table name, a column name, a sort direction or a keyword. For those, check the input against an allowlist you control, or use the library's identifier quoting helper, and never paste user text.

**Related habits.** Use `executemany` for batches of rows, keep credentials out of the connection string in code, and close the connection (or use a context manager) on every path. With many executions of one statement, a driver can also prepare it once.

The reasoning

**Timeout.** The requests documentation says it does not time out unless you set `timeout`. A hung server then hangs your job. Pass `timeout=(3.05, 30)`, a connect timeout and a read timeout in seconds, on every call.

**Check the status.** A 500 or 404 response still returns a response object, and `.json()` on an error page raises a confusing error or, worse, parses an error body as data. Call `response.raise_for_status()` first.

**Session and retry.** A `requests.Session` reuses connections. Mount an `HTTPAdapter` with a `urllib3` `Retry(total=5, backoff_factor=1, status_forcelist=[429, 500, 502, 503, 504])` to retry only errors that can heal. `Retry` honours the `Retry-After` header by default, and by default retries only idempotent methods, so a POST is not repeated.

**What retries must not do.** Retry a 400 or 404 (it will fail the same way), retry a write that may have committed without an idempotency key, or retry forever. Bound the attempts, add jitter when many workers run, and surface the final failure instead of returning an empty result. Keep the secret out of the URL and out of logged errors.

The reasoning

**Batches, not rows.** One `INSERT` and one commit per row pays the network round trip and the commit cost a million times. Use `executemany` with batches of a few thousand rows, or the database's bulk path such as `COPY` in PostgreSQL. Commit per batch, not per row.

**A transaction makes a batch all or nothing.** In the snippet, the batch contains a duplicate key. The `with db:` block rolls back, so the table has **0** rows after the failure, not 2. Without the transaction, the rows before the bad one would stay, and the table would be half loaded.

**Idempotency makes a re-run safe.** The snippet then loads the same rows with `insert or replace`, and the table ends with exactly the three rows, the later duplicate winning. In other databases the same idea is `INSERT ... ON CONFLICT DO UPDATE`, or a load into a **staging table** followed by a single `MERGE`.

**Make progress visible.** Record which batches were committed, so a restart resumes instead of starting over, and check the final row count against the source. If part of the load cannot be rolled back, say so, and design for a clean re-run.

The reasoning

**One pass, small state.** `for event in stream:` reads one event, updates `per_user[user][type]` and forgets the event. Memory grows with the number of **distinct** users and event types, not with the number of events. The snippet builds no list of events.

**What not to do.** `events = list(stream)` loads every event first, which is the thing the iterator exists to avoid. Also avoid `len(stream)` and `stream[0]`, which iterators do not support, and do not loop twice. A generator can be consumed only once: the second loop in the snippet finds **0** events.

**Edge cases to state.** An empty stream returns an empty dict. An event missing the user or the type should be counted separately or sent to a reject list, not crash the loop. And if the stream is a file or a socket, close it on every path.

**The follow-up.** If the dictionary of counters is itself too large, the answer changes: partition the stream by a hash of the user and process each partition separately, spill to disk or a database, or use an approximate structure such as a count-min sketch when exact counts are not required.

The reasoning

**The generator.** Make an iterator with `iter(iterable)`, then loop: `batch = list(islice(iterator, size))`. If the batch is empty, stop. Otherwise `yield batch`. The snippet uses the walrus operator to do both in the `while` line. Nothing is read until the caller asks for the next batch.

**The built-in.** `itertools.batched(iterable, n)` has been available since Python 3.12 and yields **tuples**. Say that you would use it on a new version and write the generator when you must support older ones, and that you know the difference.

**Edge cases.** Seven rows in batches of three give `[1, 2, 3]`, `[4, 5, 6]` and `[7]`: the short last batch must be kept. An empty input gives no batches, not one empty batch. An exact multiple gives no extra empty batch at the end. And a `size` of 0 makes this generator quietly yield nothing, which hides a mistake, and a negative size makes `islice` raise, so check `size >= 1` first and raise a clear `ValueError`.

**Why it should be lazy.** Because the generator reads from an iterator, it works on a file or an API and holds only one batch in memory. Slicing a list with `rows[i:i+n]` works only on something that can be indexed, and copies each slice. Batch size for a real sink is also limited by its own rules, such as a maximum payload size.

The reasoning

**Use a fixed order.** Purpose (who uses the output and why), source (what system, what format, how many rows or GB per run), steps (extract, validate, transform, load), destination (what table or file, and what the grain is), schedule (how it runs and how often), and failure handling (what you do when a step fails). The order keeps you calm and shows that you see the whole system.

**Prepare the numbers.** Rows or files per run, run time, how often it fails, and one measurable result such as "the report is ready by 7 a.m. instead of 11". If you do not know a number, say how you would find it. Do not invent one: the next question is often "how did you measure that?".

**Leave hooks on purpose.** Mention one decision and one problem, for example "I made the load idempotent because the source sometimes re-sent files". It tells the interviewer where to dig, and it lets you steer to a topic you know well.

**A fill-in template.** "We needed ___ for ___. The source is ___ (___ rows per day). A Python job does ___, ___ and ___, and writes to ___. It runs ___ via ___. When it fails, ___. One thing I owned was ___."

The reasoning

**Say the word first: idempotent.** Running the job twice with the same input must leave the destination in the same state as running it once. Interviewers listen for it, and then for how you achieved it.

**Name the mechanism.** The common ones are: overwrite the whole partition for the date (delete and write in one step), upsert on a business key (`INSERT ... ON CONFLICT` or `MERGE`), or write to a staging table keyed on a deterministic hash of the source record and merge into the final table. Plain `append` is not idempotent.

**Say how you know.** A test that runs the job twice on a fixed input and asserts the same row count and the same sum of the key amount. In production, a reconciliation check that compares source and destination counts for each run date.

**Mention the date.** The job takes the run date as an argument and never calls `now()` for its logic, so a rerun for last Tuesday behaves the same as the original run. A late file is handled by event time, not by arrival time.

The reasoning

**Contain.** Pause the job or route failures to a reject area. A pipeline that keeps writing nulls for a broken field is worse than one that stops. Tell the downstream users that data is delayed.

**Look at the facts.** Compare a failing payload with an old one. Is the change in every record or only some? Did a new version of the API roll out? Check the changelog, and ask the owner of the source system whether the change is intentional and permanent.

**Decide and fix.** Options: coerce the new shape into the old one, parse the object into new columns, or quarantine records that do not match. A sound fallback is to **keep the raw payload** (for example in a JSON column) and project typed columns on top, so nothing is lost while the typed layer is fixed. Then backfill the period that failed.

**Prevent.** Add a schema or contract check at the boundary that fails loudly on an unexpected type, a test with a sample of the new payload, and an alert on rejects above a threshold. Say what you would agree with the producer: a notice before breaking changes.

The reasoning

**Start with what changed.** Input volume, the source, the data shape (a new very large key), the environment, or the code. Compare logs from a good run and a bad run: where do the timestamps diverge?

**Measure, do not guess.** Add timing around each step, or use `cProfile` for CPU time and `tracemalloc` for memory. A job can be slow because of waiting (network, database, disk) or because of computing, and the fix is different.

**The usual suspects.** Reading everything into memory and swapping. `x in some_list` or `pop(0)` in a loop (quadratic). One query or API call per row instead of a batch. A merge that multiplied rows. A filter applied after loading instead of in the query. A table whose index or partition pruning stopped working.

**Then fix one thing and measure again.** Say the result as a number: "the step went from 2 h 40 min to 6 min after I replaced the per-row lookups with one batch query". If you only suspect the cause, say that, and say how you would confirm it.

The reasoning

**Make the logic testable.** Put the transform in functions that take data and return data, with no file or network access inside. Pass the client and the clock in as parameters. That is what makes tests fast and the same on every run.

**What to cover.** The normal case, an empty input, one row, a row with a missing field, a wrong type, a duplicate, and a boundary value such as midnight or the end of the month. Use `pytest.mark.parametrize` so each case is one line.

**The boundary.** Replace the API or database with a fake or a mock, and assert on what was sent (a retry on a 503, no retry on a 400). Use `monkeypatch` or `unittest.mock.patch` only at the boundary, and prefer injection.

**Say what is not tested.** For example, real data volumes, or the scheduler itself. Then say how you cover the gap: data quality checks in production, row-count reconciliation, and an alert. Naming the gap honestly is a stronger answer than claiming full coverage.

The reasoning

**Where they live.** In a secrets manager (the cloud provider's, or Vault), or in environment variables injected at run time. Never in the source code, a notebook or a committed `.env` file. Add the file name to `.gitignore`, and scan the repository for secrets in CI.

**How the code reads them.** Read configuration once at start-up, and fail fast with a clear message if a required value is missing. Do not read the environment at import time, which makes tests and imports surprising.

**How they leak.** A secret in a URL query string appears in logs and in `requests` exceptions. A connection string in a traceback. `print(config)` or logging a whole settings object. Log the host and the job name, not the full URL, and give settings objects a `__repr__` that hides secret fields.

**Least privilege and rotation.** Each pipeline gets its own role that can read only its source and write only its destination. Short-lived credentials and rotation limit the damage of a leak. If a key is ever committed, rotate it at once: removing it from the history is not enough.

The reasoning

**Short outage.** A timeout on every call, then bounded retries with backoff and jitter for errors that can heal (timeouts, 429, 5xx). Respect `Retry-After`. Do not retry errors that cannot succeed, such as a 401 or a 404.

**Long outage.** After the retries are used up, the task must **fail**, not return an empty result. The scheduler then marks the run failed, alerts the owner, and can retry the run later. Downstream tasks do not start on missing data.

**Catching up.** Store progress outside the job (a watermark, a cursor or a list of processed files), so the next run starts where the last one stopped. Because the load is idempotent, the catch-up run can overlap with data that was already loaded.

**Tell people.** Say how the on-call person learns about it, and what the dashboard shows during the delay: "data as of 06:00" is honest, and stale data that looks fresh is not.

The reasoning

**Check the input.** Required fields present, types as expected, values in range, keys unique where they should be. Rows that fail go to a reject file with a reason code. The run reports how many were rejected.

**Reconcile the output.** Rows read equals rows written plus rows rejected. The sum of an important amount in the source equals the sum in the destination. A count of rows with a missing key is zero. These numbers are written to a run log.

**Watch trends.** A sudden drop or jump in daily volume, a rising null rate, a data freshness that goes past its limit. A check that cannot fail, for example one that compares the output with itself, proves nothing.

**Be concrete.** Give one example of a check that caught a real problem, and what you did. If you do not have one, describe one you would add, and say why.

The reasoning

**Choose well.** A real failure with a clear cause and a clear fix: duplicates after a retry, a silent schema change, a late file that was skipped. A story with no consequence is not believable, and a story that you caused and fixed shows ownership.

**Tell it in order.** Impact (what was wrong, for how long, for whom), detection (how you found out, and whether a check should have found it earlier), diagnosis (what you looked at), fix (what you did first, and what the permanent fix was).

**End with the change.** A test, a check, an alert, a runbook, or a design change such as making the load idempotent. This is the part the interviewer remembers.

**Keep it blameless.** Describe the system and the gap in it, not the person who made the mistake, even if it was you: "I did not add a check for X, so now we have one".

The reasoning

**Find the first limit.** Memory (everything is read into a list or a DataFrame), time (the run no longer fits before the deadline), a rate limit of the source API, or the write capacity of the destination. Say which one you would hit first, and why.

**Use the cheap steps first.** Stream or read in chunks, select only the columns you need, use a columnar format such as Parquet, push filters to the source, batch the writes, and run independent partitions in parallel. For I/O-bound work use threads or `asyncio`, and for CPU-bound work use processes.

**Know when to leave single-machine Python.** When the data no longer fits on one machine or the job needs a shuffle across a lot of data, move the step to Spark or a warehouse engine and keep Python for orchestration and small transforms. Say what would trigger the change: a number, not a feeling.

**Say what you would measure.** Run time per step, memory peak, rows per second, and the source or sink limit. A plan that starts with measurement is more convincing than one that starts with a new technology.

The reasoning

**Packaging.** Dependencies pinned in a lock file or a `requirements` file with versions, so that the build is repeatable. A virtual environment or a container image, so the job runs the same on a laptop and in production.

**Scheduling.** An orchestrator (Airflow, Dagster, or a cloud scheduler) runs the job on a schedule, passes the run date, retries failed tasks, and shows the run history and alerts. The job itself does not contain a `while True` loop with `sleep`.

**Configuration.** The same code runs in each environment, and only configuration changes: bucket names, hosts and credentials come from the environment, not from edits to the code.

**Change safely.** Pull request and review, automated tests in CI, a deployment that can be rolled back, and a backfill plan for a change that affects past data. Mention how you test a change on a copy or a small sample before it touches production tables.

The reasoning

**Choose real weaknesses.** Look at your own list of incidents and annoyances. Reruns that needed manual cleaning, a missing alert, a function that mixed I/O and logic so it could not be tested, a schema that was guessed from the first file.

**Link each change to a reason.** "I would write raw data to a landing zone first, because when the source changed I could not replay what had arrived." A reason shows you learned from experience and not from a checklist.

**Do not say "rewrite in a new tool".** A change of tool is rarely the lesson. Prefer design changes: idempotent loads, contracts at the boundary, tests on pure functions, explicit run dates, and observability.

**Be honest about trade-offs.** Say what the change would cost in time or complexity, and why you did not do it the first time. That is realistic, and it shows judgement.