Spotify Data Engineer Interview Preparation Guide - Entry Level

Direct answer. A real trade-off is documented not with a vague 'optimized for performance' comment but with the SPECIFIC evidence that justified it: the measured gain, the alternative that was rejected and why, and a pointer future maintainers can use to re-evaluate whether the trade-off still holds.

A representative situation

A hot-path function serializing millions of small messages per second was rewritten from a clear, idiomatic object-based approach to a manually packed byte-buffer format, because profiling showed serialization was consuming 30% of total CPU under peak load and the byte-buffer version cut that to 9%.

How I'd document the decision

def pack_message(msg: Message) -> bytes:
    """
    Manually packs fields into a byte buffer instead of using the standard
    (slower) serialization library.

    WHY: profiling on 2026-06-01 (see PERF-118) showed the library-based
    version consumed 30% of CPU at peak load (180k msgs/sec); this version
    cuts that to 9%, confirmed via the benchmark in bench/serialize_bench.py.

    IF YOU ARE ABOUT TO 'CLEAN THIS UP': re-run that benchmark first. If
    peak load characteristics have changed enough that this optimization
    no longer matters, prefer reverting to the clearer library-based version
    below (kept in git history at commit abc123) over maintaining this.
    """

• The comment names the SPECIFIC evidence (the ticket, the measured numbers, the benchmark script) rather than asserting 'this is faster' unfalsifiably.
• It explicitly invites future re-evaluation rather than treating the optimization as permanent, which matters because the conditions that justified it (load characteristics, hardware, library performance) can change.
• A runnable benchmark in the repo means the claim is checkable by anyone, not just trusted on the original author's word.

Why this matters more than the code itself

The optimized code, by construction, is HARDER to read than the alternative -- the entire justification for accepting that cost lives in the documentation. Without it, a future engineer has no way to distinguish 'this is deliberately optimized, don't simplify it' from 'this is just badly written,' and the natural instinct (correctly, in the absence of context) is to clean up code that looks unnecessarily complex.

Trade-offs and pitfalls

• A comment claiming a performance benefit that's never re-verified becomes exactly the kind of unfalsifiable folklore that accumulates in old codebases ('don't touch this, it's for performance' with no evidence anyone can check) -- a runnable benchmark, not just prose, is what keeps the justification honest over time.
• Don't over-document routine, low-stakes optimizations with this much ceremony; reserve the full treatment for cases where the readability cost is real and the code will likely tempt a future 'cleanup.'

Bucketing with CASE and then counting per bucket is a two-step composition: first classify each row, then treat the classification as an ordinary GROUP BY column.

Structured elaboration

SELECT bucket, COUNT(*) AS product_count FROM (
  SELECT CASE
    WHEN net_revenue < 100 THEN 'low'
    WHEN net_revenue < 2000 THEN 'medium'
    ELSE 'high'
  END AS bucket
  FROM sales
) t
GROUP BY bucket;

Wrapping the CASE expression in a subquery (or a CTE, a Common Table Expression) first, then grouping on its output column, avoids having to repeat the entire CASE expression in the GROUP BY clause; some engines do allow grouping directly on a repeated CASE expression inline, but the subquery form reads more clearly and sidesteps any alias-in-GROUP-BY portability question entirely.

Worked example

Given products with net_revenue 50, 500, 5000, 20: the low bucket (< 100) catches two products (50 and 20), medium (100 to <2000) catches one (500), and high (>= 2000) catches one (5000), giving bucket counts low: 2, medium: 1, high: 1.

Trade-offs and pitfalls

This composition pattern, classify-then-group, generalizes well beyond revenue buckets: cohort labeling, risk tiers, and age brackets all follow the identical shape. Keep the threshold logic in exactly one place (the subquery/CTE) so a later change to the bucket boundaries doesn't need to be hunted down in multiple copies of the same CASE expression.

Direct answer. Compare a small set of stable, cheap-to-compute signals (estimated-vs-actual row-count ratio at key nodes, overall plan cost, and total execution time) between successive runs or plan versions of the same query, and alert on a meaningful, sustained shift in any of them rather than on absolute latency alone, since latency alone conflates plan regressions with unrelated load spikes.

Structured elaboration. Track, per monitored query (or query fingerprint, grouping structurally-identical queries that differ only in literal parameter values), a small rolling history of: the plan's shape (which operators and join algorithms it used, which you can fingerprint cheaply as a hash of the plan structure without storing the full plan every time), the ratio of actual to estimated rows at whichever node historically diverges most for that query, and total execution time. A genuine regression usually shows up as either the plan SHAPE changing (a different join algorithm or access method than the historical norm) or the estimate-actual ratio drifting badly, and either of those is a much more specific, actionable signal than "latency went up," which could equally be caused by concurrent load, a maintenance job, or a genuinely larger input this run.

Thresholds should be based on a meaningful, sustained deviation from that query's own historical baseline, not a single global number across every query, since a normally-slow query getting noticeably slower still is a very different signal than a normally-fast query jittering within its usual noisy range; alert on relative deviation from each query's own baseline, and require the deviation to persist across more than one execution before paging anyone, to avoid alerting on a single noisy outlier run.

Worked example. A regression-detection job that periodically diffs a query's current plan shape against its own last-known-good shape, and separately tracks a rolling p95 of its execution time, would catch both of the two most common regression patterns: a plan shape change (immediately visible in the diff) even before latency visibly degrades, and a pure statistics-drift regression (same plan shape, but the estimate-actual ratio at a specific node quietly getting worse over several days) that a single latency threshold alone might miss until it's already painful.

Trade-offs and pitfalls. Storing and diffing full plan text for every execution of every query is expensive and noisy; a cheaper structural fingerprint (a hash of the sequence of operator types and join algorithms, ignoring cost estimates and literal constants) captures the signal that actually matters, "did the STRATEGY change," without the storage and comparison overhead of full plan text, and is what keeps this kind of monitoring affordable to run continuously rather than as an occasional manual check.

Linearizability and serializability sound similar but answer different questions. Linearizability is about a single object: every operation on it must appear to happen instantaneously at some point between when it was invoked and when it returned, and that ordering must match real time. Serializability is about multiple objects touched by a transaction: the outcome of running several transactions concurrently must be equivalent to running them in some serial order, but that order does not have to match real time or even the order the transactions actually started in. A system can have one property without the other, and assuming the wrong one silently breaks a different class of guarantee.

Two mechanisms that actually enforce serializability

• Two-phase locking (2PL): a transaction acquires every lock it needs before releasing any of them, and once it starts releasing locks it may acquire no more. This physically prevents conflicting concurrent access, at the cost of blocking and potential deadlock.
• Optimistic concurrency control (OCC): transactions proceed without locking, then get validated at commit time; if another transaction's concurrent writes conflict with what this one read, it aborts and retries. This avoids blocking under low contention but wastes work under high contention.

When you need one but not the other

Consider a key-value store advertising single-copy semantics: every replica must behave as if there is exactly one physical copy of the data, so any client reading a key right after a write, from any client, on any replica, sees that write or a later one, never a stale value. The same requirement shows up as a highly available configuration service needing linearizable reads: if a client reads a feature flag or a routing rule right after it changed, it must get the new value, since acting on a stale one applies the wrong policy. Neither of these needs serializability: there is no multi-key transaction to isolate, just one key's recency.

The mirror case: a reporting system running multi-row aggregate queries across many tables needs those queries to see an internally consistent snapshot (serializability, or at least snapshot isolation), but does not need that snapshot to be the absolute latest possible instant in real time. A report built from data a few hundred milliseconds behind the live system is fine, as long as every row it reads is mutually consistent with every other row it reads.

Worked example: what breaks if you assume the wrong one

Linearizable but not serializable, no cross-key transaction: a key-value store gives linearizable single-key reads and writes but has no multi-key transactions. A funds transfer moves 30 units from account A (currently 100) to account B (currently 50) as two separate linearizable writes: write A=70, then write B=80. A concurrent reader can land exactly between the two writes and read A=70 and B=50. Both individual reads are linearizable, each reflects the latest write to that specific key at the moment it was read, but the reader just observed a total of 70+50=120, when the true, fully-settled total is 70+80=150: 30 units appear to have vanished mid-transfer. That is the anomaly linearizability alone does not prevent, because it says nothing about atomicity across two different keys.

Serializable but write-skew possible, snapshot isolation only: a hospital scheduling system enforces one invariant, that at least one doctor remains on call.

$\text{doctors on call} \ge 1$

Two doctors, Alice and Bob, are both currently on call, so the on-call count is 2. Both, concurrently, read a snapshot showing 2 doctors on call and each independently decide it is safe to go off-call, and both commit that decision under snapshot isolation, since neither transaction's write conflicts with what the other actually wrote (each only writes their own on-call flag). The result: 0 doctors on call, violating the invariant, even though each transaction, viewed alone against its own snapshot, looks perfectly valid. Full serializability, not just snapshot isolation, would detect that these two transactions' reads and writes interfere and force one to abort; snapshot isolation's weaker check does not.

Trade-offs & pitfalls

• Common wrong turn: treating serializable as automatically meaning fresh or linearizable. It is not: transactions can be serialized in an order that does not match when they actually ran.
• Common wrong turn: treating a single-key linearizable store as if it gives transactional safety across several keys. It does not, by itself, unless the store also offers multi-key transactions on top.
• Snapshot isolation is cheaper than full serializability, since it does not need to detect every possible interleaving, only genuine write-write conflicts, and is what most production databases default to, which is exactly why the write-skew anomaly above shows up in practice more often than people expect.

Direct answer

Raise it early and privately, focused on the pattern rather than a single incident, and ask for one specific, small change rather than a general request to "communicate better." The framing matters: you're protecting the team's ability to deliver, not criticizing your manager's judgment.

The move: name the pattern, own the cost, ask for one thing

1. Choose a private one-on-one, never raise this in a team setting where it can read as public pushback.
2. Open with intent, not accusation. State plainly that you want the team to hit its commitments reliably, before you name the problem. This reframes the conversation as shared interest, not complaint.
3. Name the pattern with two or three concrete instances, not "you always" or "this keeps happening." Specifics are harder to dismiss and easier to discuss without defensiveness.
4. State the cost in terms your manager owns: rework, missed commitments, the team's confidence in future sprint plans, not your own frustration.
5. Make one small, specific ask. A heads-up in a shared channel when priorities shift mid-sprint is concrete and adoptable; a whole new change-management process is not a conversation, it's a project, and it will read as overreach.
6. Listen for their constraint. Late changes are often absorbed pressure from above them. Asking what's driving it turns the conversation from confrontation into problem-solving, and may surface a fix you couldn't propose yourself.

Worked example

Three sprints in a row, priorities shifted mid-week without warning, and the team reworked already-reviewed code each time. In a one-on-one, you say you want the team's commitments to hold, then walk through the three instances and what each cost in rework. You ask for one thing: a short heads-up before a change lands mid-sprint, even informally, so the team can flag what's already in flight. Your manager explains they're absorbing last-minute asks from their own leadership and hadn't realized the downstream cost; they agree to flag changes before committing to them upward.

Trade-offs and pitfalls

Escalating past your manager before raising it directly with them first skips the relationship and reads as going over their head, even when you're right about the pattern. Turning the ask into a formal governance process oversteps what a single conversation can carry, and it isn't yours to impose. If the pattern continues after a clear, direct ask with a stated cost, that's the actual trigger to escalate, framed as protecting the team's delivery, not as a personal complaint about your manager.

Direct answer. Starting from 1 million daily active users generating 30 events/user/day, daily event volume is 30 million events; with a stated average event size and a stated Parquet compression ratio, that translates to a small, easily-provisioned daily processing volume (single-digit GB/day compressed) -- the calculation matters less for its final number here than for demonstrating the Fermi-estimation METHOD (building a quick order-of-magnitude estimate from a chain of explicit, stated assumptions when exact data is not available, rather than declining to estimate at all), since production teams almost never start from a bare event count without a real average size measured from actual data.

Structured elaboration and calculation.

dau = 1_000_000
events_per_user = 30
daily_events = dau * events_per_user
# = 30,000,000 events/day

avg_event_bytes = 500   # stated assumption: a compact structured event, not a large payload
daily_bytes_raw = daily_events * avg_event_bytes
# = 15,000,000,000 bytes

parquet_compression = 5  # stated assumption for columnar storage of semi-structured events
daily_bytes_parquet = daily_bytes_raw / parquet_compression

Run (verified): daily event volume = 30,000,000 events, daily raw volume = ~13.97 GiB, daily Parquet-compressed volume = ~2.79 GiB.

Cluster resource implications (approximate). At this daily volume, a nightly ETL producing aggregated tables from 30M events is a modest job by data-pipeline standards. Putting an actual approximate number on it: at a conservative single-node combined read+transform+aggregate throughput of ~100 MB/sec (deliberately conservative, well below what a modern single instance can sustain, to keep this an upper-bound estimate), the ~14,305 MB of raw daily volume alone processes in roughly 14,305 / 100 ~= 143 seconds (~2-3 minutes) of wall-clock compute.

raw_mb = daily_bytes_raw / 1024**2
per_node_mb_s = 100   # stated assumption: conservative single-node combined throughput
seconds = raw_mb / per_node_mb_s
# ~= 143 seconds (~2.4 minutes) on a single modest node

Run (verified): ~143 seconds (~2.4 minutes) on a single modest node (e.g., a 4-8 vCPU instance) covers the raw read+transform pass; a single small instance, or at most a 2-3 node cluster for headroom and fault tolerance, is more than sufficient here. The interesting sizing question quickly shifts from raw throughput to whether the AGGREGATION logic itself (join cardinality against a user or product dimension, window-function cost) is the actual bottleneck rather than ingest volume, since 14 GB/day is not large enough to stress most modern processing frameworks on ingest alone -- if aggregation cost dominates, that is a separate, workload-specific estimate this rough pass does not cover.

Stated assumptions, and why they matter more than the arithmetic. The 500-byte average event size, 5x Parquet compression ratio, and 100 MB/sec single-node throughput are placeholders standing in for real numbers that should come from an actual sample of production events (event schema width, field cardinality, how much text/JSON payload each event carries) and a real benchmark of the processing framework in use -- a real product event with a nested JSON payload could easily be 2-10x the assumed 500 bytes, which would proportionally scale every downstream number. The value of walking through this calculation explicitly is that it makes every assumption visible and swappable, rather than presenting a single number with unstated inputs baked in.

Trade-offs & pitfalls. DAU x events/user/day is itself an average-based estimate that hides variance the same way any average-only capacity estimate does -- a product with highly variable per-user engagement (a small set of power users generating disproportionate event volume) needs the same skew-awareness (recognizing and mitigating a small number of keys or users that dominate traffic) once this pipeline is actually built, even though the AGGREGATE daily volume computed here is accurate regardless of that internal distribution.

Direct answer

The first-pass architecture has three jobs: shape and throttle traffic at the edge so the backend never sees the raw 10 million attempts at once, smooth the accepted traffic through a durable queue before it touches checkout logic, and keep an authoritative reconciliation step to catch and repair any inventory oversell that slips through. Each piece exists because the naive path (let every request hit the database directly) would collapse the datastore in the first seconds of the sale.

Structured elaboration

Edge shaping and throttling. A content delivery network (CDN) and web application firewall (WAF) sit in front, followed by an admission controller that applies token-bucket rate limits per IP/user (each client accrues "tokens" over time up to a fixed cap, and each request spends one token, so short bursts are allowed while the sustained rate stays capped) and can present a "waiting room" queue-position page instead of a raw error. This exists because most of the 10 million attempts are read-heavy page loads and refresh-spam, not real purchase intent; rejecting or queuing cheaply at the edge protects everything downstream.

Checkout queueing. Accepted checkout attempts become durable messages on a partitioned queue (partitioned per stock-keeping unit, so one hot item's traffic doesn't starve others), consumed by checkout workers at a controlled concurrency. This exists because the queue absorbs the burst and lets the backend process at a rate it can sustain, instead of every accepted request triggering an immediate synchronous database write.

Inventory reservation. Workers decrement inventory in a fast in-memory store guarded by atomic operations. Two named mechanisms exist for this, and the choice is a real trade-off: a pessimistic approach acquires a short-lived reservation lock before confirming, which guarantees no oversell but adds contention and reduces throughput under massive concurrent demand; an optimistic approach atomically decrements a counter and confirms after the fact, which sustains far higher throughput but can let the counter go transiently negative, requiring reconciliation. A common middle ground is optimistic decrement for most items with a pessimistic fallback check for low-stock or high-value items, where an oversell would be most costly.

Reconciliation. An event log of every reservation and decrement feeds a reconciliation job that compares committed orders against authoritative inventory after the sale, applying compensating actions (cancel lowest-priority orders, offer backorder or credit) for anything oversold.

flowchart LR
  A[Client] --> B[CDN and WAF edge]
  B --> C[Admission controller: token-bucket rate limit]
  C --> D[Checkout API]
  D --> E[Durable queue, per-SKU partitions]
  E --> F[Checkout workers]
  F --> G[Inventory cache: atomic decrement]
  G --> H[(Authoritative inventory DB)]
  F --> I[Event log]
  I --> J[Reconciliation job]
  J --> H
  J --> K[Compensation: cancel, refund, notify]

Worked example

The stated load is 10 million checkout attempts in a 10-minute window:
$\text{avg rate} = \frac{10{,}000{,}000 \text{ attempts}}{600 \text{ s}} \approx 16{,}667 \text{ req/s}$
That average masks the real shape of a flash sale: demand is heavily front-loaded in the opening seconds. Using an illustrative 8x opening-minute burst factor over the average (a labeled assumption, not a measured figure):
$\text{illustrative peak} = 16{,}667 \times 8 \approx 133{,}333 \text{ req/s}$
This is the number the edge admission controller and queue have to be sized for, not the smoothed 16,667 req/s average; sizing to the average alone would let the opening burst overwhelm the checkout tier before the throttle has a chance to shed load.

Trade-offs & pitfalls

• Sizing capacity to the average rate instead of the front-loaded peak is the most common mistake; a flash sale's demand curve is not uniform across the window.
• Pessimistic locking is safer but throttles throughput exactly when throughput matters most; optimistic decrement is faster but pushes correctness work into reconciliation, which only works if compensating actions (refunds, notifications) are also designed up front, not bolted on after an incident.
• A queue partitioned too coarsely (one partition for all items) recreates the same hot-item contention the partitioning was meant to avoid; partition by item or shard further for known hot stock-keeping units.
• Treating reconciliation as an afterthought is a red flag; for a hard system-design question the reconciliation and compensation path is as much a first-class component as the checkout path itself.

Direct answer

With limited engineering capacity and a backlog of data-quality issues of varying severity, prioritize using a framework that weighs business impact, likelihood of recurrence, and ease of fix together, rather than working purely by reported severity or purely by whichever issue is loudest, and build cross-team alignment around a shared solution by making the cost of NOT solving it collectively visible to everyone who would otherwise keep patching their own symptom independently.

Structured elaboration

• Prioritization framework: score each issue on business impact (revenue, trust, or compliance risk if unaddressed), likelihood of recurrence (a one-time fluke versus a systemic pattern likely to keep causing incidents), and ease of fix (quick win versus multi-quarter investment), and use the combination, not any single dimension alone, since a high-impact-but-nearly-impossible-to-fix issue and a low-impact-but-trivial-to-fix issue both deserve very different treatment than a mid-impact, systemic, moderately-hard-to-fix issue that might actually be the highest-leverage item on the list.
• Building cross-team alignment: when multiple teams each want their own fix prioritized, the highest-leverage move is often identifying that several teams' individually-requested fixes are actually symptoms of the same underlying, systemic root cause (a shared schema-registry gap, a missing common validation framework); making that shared root cause visible reframes the conversation from "whose fix goes first" to "let's jointly fund the one fix that helps everyone," which is a fundamentally easier negotiation.
• Getting commitment without direct authority: bring data, not opinions, to the prioritization conversation (quantified incident counts, quantified business impact per team) and let the shared, objective picture do the persuading, rather than relying on personal influence alone.

Worked example

Three different teams each separately request their own data-quality fix be prioritized this quarter. Digging into the actual root cause of all three reveals they all stem from the same underlying gap: no shared schema-registry enforcement across producer teams. Reframing the ask from three competing, team-specific requests into one shared infrastructure investment, with each team's incident history used as concrete evidence of the collective cost of not fixing it, turns three competing priorities into one aligned, jointly-funded initiative that serves all three teams' actual underlying need. Scoring this alongside two other backlog items on a simple 1 (low) to 3 (high) scale per dimension, and summing the three scores, makes the trade-off concrete rather than just narrated:

The schema-registry gap wins the priority order (7, then 6, then 5) precisely because it combines high impact with high recurrence, even though it is the hardest of the three to fix, which is the case the framework is built to catch: scoring on ease of fix alone would have wrongly ranked the two quick patches above the systemic, harder, but far higher-leverage fix.

Trade-offs and pitfalls

The temptation under pressure is to simply work the backlog in the order requests arrived, or to prioritize whichever team is loudest, both of which systematically under-invest in quieter-but-higher-leverage systemic fixes in favor of whoever advocates hardest for their specific, narrower issue. A framework that explicitly scores business impact and recurrence likelihood, and actively looks for shared root causes across seemingly-separate requests, protects against exactly that bias.

Direct answer

For each numeric column, decide whether it is genuinely integer-valued (either already an integer dtype, or a float column whose values are all whole numbers within tolerance) and downcast those to the smallest integer type that fits; for the remaining floats, downcast to float32 only if the resulting rounding error stays under an explicit absolute or relative tolerance you choose up front. Non-numeric columns must be left completely untouched, and every step should be validated by comparing before/after memory usage and by checking that the precision loss actually stayed within the declared tolerance rather than assuming it did.

Structured elaboration

• Step 1, classify each column: integer dtype already, integer-valued float (all values equal their own rounded value within a small tolerance), genuine float requiring real fractional precision, or non-numeric.
• Step 2, downcast integers: pd.to_numeric(series, downcast='integer') picks the smallest integer type (int8/int16/int32) that still holds every value in the column. If the column has missing values, a plain integer dtype can't represent NaN (not-a-number, pandas' missing-value marker) at all, so use pandas' nullable Int64 (capital I, the nullable extension dtype, not the numpy int64 dtype) instead, which is NaN-safe.
• Step 3, downcast floats conditionally: cast to float32 and measure both the absolute and relative error against the original float64 values; only keep the downcast if the error is within the tolerance you set, otherwise keep float64.
• Step 4, validate: report the byte usage per column before and after, and confirm the whole-DataFrame memory total actually decreased by the expected amount rather than assuming it did from the per-column deltas alone.

Worked example

import pandas as pd
import numpy as np

def is_integer_like(s, tol=0.0):
    if not pd.api.types.is_float_dtype(s.dtype):
        return False
    s_nonnull = s.dropna()
    if s_nonnull.empty:
        return True
    return np.all(np.isclose(s_nonnull, s_nonnull.round(), atol=tol))

def safe_downcast_df(df, float_tol=1e-6, float_rel_tol=1e-6):
    before_mem = df.memory_usage(deep=True).sum()
    result = df.copy()
    numeric_cols = df.select_dtypes(include='number').columns
    report = []

    for col in df.columns:
        s = df[col]
        if col not in numeric_cols:
            result[col] = s                              # non-numeric: leave untouched
        elif pd.api.types.is_integer_dtype(s.dtype) or is_integer_like(s, tol=float_tol):
            result[col] = s.round().astype('Int64') if s.isna().any() else pd.to_numeric(s, downcast='integer')
        elif pd.api.types.is_float_dtype(s.dtype):
            cast = s.astype('float32')
            mask = s.notna()
            abs_err = (s[mask] - cast[mask].astype(s.dtype)).abs()
            rel_err = abs_err / s[mask].abs().replace(0, np.nan)
            result[col] = cast if ((abs_err.fillna(0) <= float_tol).all() or (rel_err.fillna(0) <= float_rel_tol).all()) else s
        else:
            result[col] = s
        report.append((col, s.dtype, result[col].dtype, s.memory_usage(deep=True), result[col].memory_usage(deep=True)))

    after_mem = result.memory_usage(deep=True).sum()
    return result, pd.DataFrame(report, columns=['col', 'orig_dtype', 'new_dtype', 'orig_bytes', 'new_bytes']), before_mem, after_mem

n = 1000
df = pd.DataFrame({
    'int_like_float':    np.repeat([1.0, 2.0, 3.0, 4.0, 5.0], n // 5),        # float64 but whole numbers
    'true_float':        np.random.RandomState(0).uniform(0, 1, n),           # needs real fractional precision
    'small_int_float':   np.repeat([10.0, 20.0], n // 2),                     # small-range integer-valued float
    'category_like':     np.repeat(['a', 'b', 'c', 'd'], n // 4),             # non-numeric, must pass through
    'with_nan_int_like': np.where(np.arange(n) % 7 == 0, np.nan,
                                   np.repeat([1.0, 2.0], n // 2)),            # integer-valued float with NaNs
    'flag':               np.repeat([True, False], n // 2),                   # boolean, must pass through
    'ts':                 pd.date_range('2021-01-01', periods=n),             # datetime, must pass through
})

result, report, before, after = safe_downcast_df(df)
print(report.to_string())
print(f"Memory: {before} -> {after} ({(before - after) / before:.1%} saved)")

Verified on the fixture above: the integer-like columns correctly downcast to int8, with_nan_int_like correctly becomes nullable Int64 with every original NaN (not-a-number, pandas' missing-value marker) preserved, and category_like/flag/ts pass through completely unchanged. true_float, despite holding genuinely random fractional values, also ends up downcast to float32 here (max relative error against the float64 original was about 5.7e-8), because the default float_rel_tol=1e-6 is looser than float32's own precision (roughly 1.2e-7 relative) for values in this range. That is worth noticing on its own: at these default tolerances almost any "normal-range" float column will pass, so a genuinely precision-sensitive column is only protected if you tighten the tolerance below what float32 can represent, not by the presence of fractional values alone. Total memory dropped from 50,132 to 33,132 bytes on this fixture, a 33.9% reduction.

Trade-offs and pitfalls

• Complexity: O(n) per column for the scans (classification, error computation); memory is O(n) for the returned copy, since a new frame is built rather than mutated in place, unless memory pressure requires downcasting column-by-column in place instead.
• Edge cases: values large enough to require int64 correctly stay at int64 (downcasting must never silently overflow into a smaller type); a column that is float but has zero non-null values reports is_integer_like == True trivially (nothing to contradict it) and downcasts to an empty-safe integer type; columns already at the smallest reasonable width are left alone rather than needlessly copied.
• A pandas 3.0-specific bug worth naming explicitly: an earlier, unguarded version of is_integer_like called np.issubdtype(s_nonnull.dtype, np.floating) unconditionally on every column, including non-numeric ones. This crashes under pandas 3.0 with TypeError: Cannot interpret '<StringDtype(na_value=nan)>' as a data type the moment a string column is present, because pandas 3.0 changed the default dtype for string columns from generic object to a dedicated StringDtype, which numpy.issubdtype cannot interpret at all. This was reproduced directly: constructing a plain pd.DataFrame({'col': ['a','b','c']}) under pandas 3.0 and passing that column through the unguarded helper throws immediately, even though the exact same code ran fine on any pandas dtype scheme where a string column reported as plain object. The fix used above checks is_float_dtype first and returns False immediately for anything that isn't a float column, so the numpy comparison is never reached for non-numeric dtypes. A recipe copied from older pandas material that skips this guard will now fail on the first DataFrame that has any text, boolean, or category column mixed in with the numeric ones, which is most real DataFrames.
• Rounding an integer-like float before casting to Int64 (s.round().astype('Int64')) is deliberate: values like 2.9999999999 that are "integer-like" only within tolerance need to actually round to 3 before the integer cast, or the cast truncates toward 2 instead.

Direct answer

A lowest common ancestor (LCA) query in a general binary tree can be answered with a single postorder-style depth-first search (DFS, a traversal that explores each branch fully before backtracking) that returns node references bubbling up: if a subtree's search finds both target nodes on different sides, the current node is the LCA; if only one side finds anything, that result is passed further up. When the tree happens to be a binary search tree (BST), searching both subtrees isn't necessary at all: comparing the two target values against the current node's key, and walking down toward whichever side both targets agree on, is enough.

Structured elaboration

Approach: general binary tree

• Recurse into both children. At any node, if the node itself is one of the two targets, or if the node is None, return it directly (a None or a matched target both act as the "nothing more to find below here, here's what was found" signal).
• After the recursive calls return, if both the left and right calls found something non-None, the current node sits between the two targets, so it is the LCA; return it.
• If only one side found something, that result (the target itself, or an LCA found deeper down) is passed up unchanged, since the current node cannot be the answer.

class TreeNode:
    def __init__(self, val, left=None, right=None):
        self.val = val
        self.left = left
        self.right = right


def lca_general(root, p, q):
    """Lowest common ancestor in a general binary tree. p and q are TreeNode
    references known to exist in the tree."""
    if root is None or root is p or root is q:
        return root
    left = lca_general(root.left, p, q)
    right = lca_general(root.right, p, q)
    if left and right:
        return root
    return left if left else right

Approach: binary search tree

• In a BST, every node's key already encodes where its descendants live relative to it, so two arbitrary nodes don't require searching both subtrees.
• Starting at the root, compare both target values to the current node's key: if both are smaller, the LCA must be in the left subtree, so move left; if both are larger, move right; if they split (one on each side, or either target equals the current key), the current node is the LCA, since that's the first point where the two search paths diverge.
• This turns an O(n) full-tree traversal into an O(h) walk that only ever moves in one direction, without exploring both children at any step.

def lca_bst(root, p_val, q_val):
    """Lowest common ancestor in a binary search tree, using key comparisons
    instead of exploring both subtrees."""
    node = root
    while node is not None:
        if p_val < node.val and q_val < node.val:
            node = node.left
        elif p_val > node.val and q_val > node.val:
            node = node.right
        else:
            return node  # values split here (or one equals node.val): this is the LCA
    return None

Key points

• The general-tree version explores every node in the worst case, since it has no way to prune a subtree without checking it.
• The BST version needs no recursion into both sides at all; it reuses the same "which direction do both targets agree on" comparison as an ordinary BST search, walking a single path from the root.

Worked example

Building this tree:

        6
      /   \
     2     8
    / \   / \
   0   4 7   9
      / \
     3   5

This tree also happens to satisfy the BST ordering property (every left descendant is smaller, every right descendant larger), so both functions can be run on it and compared directly. lca_general(root, node(2), node(8)) and lca_bst(root, 2, 8) both print 6 (the two nodes sit in different subtrees of the root). lca_general(root, node(2), node(4)) and lca_bst(root, 2, 4) both print 2 (node 2 is an ancestor of node 4). lca_general(root, node(3), node(5)) and lca_bst(root, 3, 5) both print 4 (they are siblings under node 4).

Trade-offs & pitfalls

Complexity

General binary tree: Time $O(n)$, visiting every node once in the worst case, since there's no way to prune a subtree that hasn't been checked. Space $O(h)$ for the recursion stack, where $h$ is the tree's height ($O(\log n)$ balanced, $O(n)$ degenerate).
Binary search tree: Time $O(h)$, a single downward walk with no backtracking. Space $O(1)$ with the iterative version shown, or $O(h)$ if written recursively.

Edge cases

• One of the two targets is an ancestor of the other: both approaches correctly return the ancestor itself as the LCA.
• p and q are the same node: returns that node.
• p or q is not actually present in the tree: both implementations shown assume presence and will return a plausible-looking but wrong answer rather than erroring; a production version should verify both nodes exist first, a separate O(n) or O(h) check, if that guarantee doesn't already hold elsewhere.
• A deeply skewed tree: the general-tree recursive version risks hitting the language's recursion limit; converting to an explicit iterative stack avoids that.

Applying the BST shortcut to a tree that is not actually a BST silently gives a wrong answer with no error, since the comparison-based walk assumes an ordering invariant that a general binary tree doesn't provide; always confirm which structure is actually in hand before choosing the approach. A second common mistake in the general-tree version is comparing node values instead of node identity when duplicate values are possible, which can match the wrong node entirely.