DoorDash Senior Data Engineer Interview Preparation Guide

Direct answer

Anchor the whole program on baselining first, not target-setting first: measure where the org actually is on a small number of leading and lagging signals, then set targets as a fraction of that measured baseline rather than an invented number, and phase in enforcement gradually so the program earns credibility before it asks for hard compliance. What keeps it from becoming a compliance exercise is that the scoreboard is outcomes, fewer incidents, faster recovery, faster and safer delivery, not activity, percentage of teams that attended training, percentage of services with a badge.

Structured elaboration

What to measure, leading and lagging, and why both matter:

• Lagging (outcomes): incident frequency, mean time to detect and mean time to recover, production bug rate per release. These are what you are actually trying to improve, but they move slowly and are easy to game by redefinition.
• Leading (practice signals that predict the lagging ones): percentage of critical services with basic tracing, metrics, and logging in place; pull-request cycle time; whether critical services have an on-call runbook that has actually been used, not just written. These move faster and tell you whether the program is working before the lagging metrics catch up.

Governance and phasing, quarterly and staged, not a big-bang mandate:

1. Baseline quarter: measure current state honestly on a couple of pilot teams before committing to any target. A program that skips baselining ends up defending invented numbers.
2. Pilot quarter: apply the practice changes, tracing, basic service-level objectives (SLOs, the specific reliability targets a team commits to), continuous-integration (CI) checks, to the pilot teams, and treat the pilot's own before-and-after change, not an assumed industry figure, as the evidence for the rest of the rollout.
3. Expand quarter: roll out to the rest of the org with the templates and tooling the pilot proved out, and introduce lightweight enforcement, CI gates for the checkable items, not a review board for everything.
4. Sustain quarter: fold the surviving metrics into normal quarterly review and retire the special program status. A program that never ends is not a program, it is a permanent tax, and teams notice the difference.

Avoiding the compliance-exercise trap specifically: tie the metrics to things a team would want anyway, fewer 2am pages, less time firefighting, and use the pilot team's own real improvement as the pitch, not a mandate from a council. Never let "percentage compliant" become the headline metric, a team can be fully compliant on checklist items and still have bad reliability if the checklist does not map to real behavior, so keep the lagging outcome metrics as the actual scoreboard and treat leading and practice metrics as diagnostic. Build in an explicit, lightweight exception path so teams with a real reason to deviate do so openly; a program with no legitimate way to say "not yet, and here's why" produces gaming instead of honest exceptions.

Governance structure: a small standing group, a rotating set of senior engineers plus one reliability-focused owner, that reviews the metrics and playbook quarterly and has authority to revise the standard, not just enforce it. A body that can only enforce and never revise loses credibility the first time a rule turns out to be wrong for some team's real context.

Worked example

Illustrative baseline-and-target methodology, shown so a reviewer can reproduce the reasoning, not a claimed historical outcome. Suppose the pilot's baseline quarter measures a current mean time to recover of 90 minutes across the pilot's critical services. Rather than asserting an arbitrary target such as "cut it 40 percent," the program sets the target from what the specific interventions plausibly buy: adding basic distributed tracing and a documented, tested runbook. If the pilot's own after-state for the services that got both changes comes in at, say, 55 minutes, that pilot number, not a projected industry average, becomes the evidence used to set the expand-quarter target for the rest of the org. The program's credibility rests entirely on that number being the pilot's real, reproducible before-and-after, not an assumed percentage stated up front.

Trade-offs and pitfalls

• Setting a target before baselining, mandating "cut mean time to recover 40 percent" org-wide on day one, is the fastest way to make this read as a compliance exercise, because nobody can tell if the number is real.
• Over-indexing on leading or practice metrics, badges, checklist completion, without ever checking whether they moved the lagging outcome metrics lets a team look compliant while reliability does not improve.
• A governance body with only enforcement power and no ability to revise the standard loses legitimacy the first time a rule is wrong for a real team's context, and teams stop engaging honestly once that happens.
• Treating this as a 12-month sprint that ends on schedule regardless of outcome, instead of sustaining the surviving pieces past the program, is how these programs regress within a year of "completion".

Requirements & constraints:

• Must respect country/region data-residency and consent laws (e.g., EU GDPR, Brazil LGPD, China PIPL).
• Minimize cross-border transfer of raw PII.
• Enable global product availability with staged rollout.
• Provide central analytics without moving raw data.

Phased rollout / region sequencing:

1. Pilot (single low-risk region with flexible regs) to validate infra and workflows.
2. GDPR-region (EU/EEA) next — high compliance bar forces robust controls early.
3. Other regulated regions (UK, Switzerland, Brazil) in parallel.
4. High-restriction regions (China, Russia) after local legal & infra readiness.
5. Remaining markets.

Architecture: federated with isolated region data stores

• Per-region isolated data stores (cloud-region-specific accounts/AZs) to guarantee residency and ease audits.
• Federated control plane: central metadata/catalog, policy engine, and orchestration; no raw-data movement.
• Use immutable event streams locally (e.g., region Kafka/MSK) and local data lake/warehouse (S3 + Athena / ADLS + Synapse / GCS+BigQuery in regional projects).

Consent capture & enforcement:

• Capture consent at point-of-collection with standardized consent schema (versioned).
• Store consent tokens and provenance locally tied to user ID and region.
• Central policy engine maintains consent rules; enforcement happens in-region (via SDKs or API gateways) that check local consent store before processing or sharing.
• Expose tokenized consent proofs to central plane (hashes/consent-IDs) — no PII leaked.

Handling schema divergence:

• Enforce a core canonical schema (stable, minimal required fields) across regions.
• Allow region extensions via namespaced fields and schema registry (Avro/Protobuf) per region.
• Use schema registry supporting compatibility checks; conversion adapters in ETL to map regional variants into canonical view for analytics derivatives.

Central analytics via aggregated derivatives:

• Define privacy-preserving aggregates computed locally (e.g., counts, histograms, differentially-private summaries).
• Local jobs produce normalized, aggregated derivatives and publish to central analytics bucket (only aggregates and metadata, no raw records).
• Use techniques: differential privacy, k-anonymity thresholds, cryptographic MPC or secure aggregation for sensitive metrics.
• Central analytics layer ingests aggregates for global dashboards, ML model training on aggregated features, and federated learning approaches where models are trained locally then gradients/weights are aggregated centrally (secure aggregation).

Operational & governance:

• Automated compliance checks, region-specific monitoring, and audit logs stored locally with exportable signed proofs.
• CI/CD for schema, consent policies, and pipelines with staged gates per region.
• Trade-offs: isolated stores increase operational overhead; federated control plane adds complexity but offers compliance and centralized governance benefits.

Example tech stack:

• Regional: Kafka/MSK, S3/GCS/Azure Blob in-region, Spark/Flink for local ETL, Postgres/BigQuery regional.
• Central: Metadata store (Data Catalog), policy engine (OPA), orchestration (Airflow/Kubernetes), analytics (Looker/BigQuery on aggregates), federated learning frameworks.

This design balances legal requirements with ability to run global analytics by keeping raw data local, enforcing consent locally, and moving only privacy-preserving derivatives to the center.

Direct answer
A boundary check catches a specific class of bug (accessing an index, offset, or value outside the range the code actually handles correctly) at the moment it happens, instead of letting it silently produce wrong output or crash somewhere unrelated later; three concrete examples: array/list indexing, pagination offsets, and numeric limits.

Structured elaboration and worked examples

• Array indexing: the bug is an off-by-one or attacker-controlled index reading past the end of a buffer or list. The defensive check: validate 0 <= index < len(array) before accessing, raising a clear IndexError/custom exception instead of either crashing with a cryptic native error or, in an unsafe language, reading adjacent memory. A unit test: assert_raises(IndexError, get_item, [1,2,3], 5).
• Pagination offsets: the bug is a negative or absurdly large offset/limit from a client, which can either error confusingly deep in a SQL driver or, worse, silently return zero rows and look like 'no data' rather than 'bad request'. The defensive check: clamp or reject offset < 0 and cap limit to a sane maximum (say 1000) before it reaches the query layer. A unit test: assert paginate(items, offset=-5, limit=10) raises ValueError.
• Numeric limits: the bug is an integer overflow or an out-of-domain value (a negative quantity in an order, a percentage over 100) silently producing a nonsensical result instead of an error. The defensive check: validate the value's range explicitly before using it in a calculation. A unit test: assert_raises(ValueError, apply_discount, price=100, percent=150).

Trade-offs and pitfalls
Each of these checks is cheap individually, but the value comes from applying them CONSISTENTLY at every place the boundary is actually crossed (every array access from external input, not just the ones you happen to remember); a single unguarded pagination endpoint added six months later by someone who didn't see this pattern reintroduces the exact bug class. Treat these as patterns to lint for or wrap in a shared utility function, not as one-off checks to remember individually.

Direct answer

Model retention and revenue at the PLAN level, not the customer level: a customer who downgrades or pauses is still "retained" as a customer even though their plan-level revenue changed, so the cohort curve needs separate logic for whether the CUSTOMER stayed versus whether the ORIGINAL PLAN'S revenue was retained, expanded, or contracted, with proration and refunds explicitly reconciled rather than assumed away.

Structured elaboration

• Why customer-level and plan-level definitions diverge: a customer who downgrades from a $100/mo plan to a $60/mo plan has NOT churned as a customer (they're still paying, still active), but has produced $40/mo of REVENUE churn on their original cohort's plan-level curve. Conflating the two produces a retention curve that's technically not wrong for either definition alone, but answers neither question cleanly if the two are mixed.
• Renewals, expansions, downgrades, and churn each need their own bucket: renewal (same price continues), expansion (price increases, e.g. an upsell or add-on), downgrade (price decreases but customer remains), and full churn (customer stops paying entirely). A revenue-retention curve typically reports NET revenue retention, which nets expansions against downgrades and churn within the same cohort.
• Proration: a plan change partway through a billing period produces a partial-period charge at each of the old and new price; decide whether the cohort curve attributes that month's revenue at the blended prorated amount or simplifies to whichever plan was active longer that month, and state the choice.
• Multiple overlapping subscriptions per customer (a customer with two products, or a trial running alongside an existing paid plan) require deciding whether the cohort curve is computed per subscription or aggregated per customer; these produce different, both legitimate, answers.
• Validating the accuracy of the resulting curves: reconcile the SUM of a cohort's monthly plan-level revenue at each offset against an independent GAAP (Generally Accepted Accounting Principles)-style recognized-revenue figure -- i.e., the revenue number finance already reports for accounting purposes -- for the same customers and period, since a validated cohort curve should tie out to the finance team's own numbers, not just internally to itself.

Worked example

A cohort of 100 customers who signed up in the same month, each starting on a $50/mo plan. Suppose by month 3: 70 are still on the original $50/mo plan (pure renewal), 10 upgraded to $80/mo (expansion, +$30/mo each), 10 downgraded to $30/mo (downgrade, -$20/mo each), and 10 churned entirely (-$50/mo each). The cohort's month-3 plan-level revenue is $70(50) + 10(80) + 10(30) + 10(0) = 3500+800+300+0 = 4600$, against an original month-0 revenue of $100 \times 50 = 5000$. Net revenue retention at month 3 is $4600/5000 = 92\%$: expansions ($300 extra from upgrades) partially offset downgrades and churn ($700 lost combined), landing at 92% even though 10 of the original 100 customers stopped paying entirely. A customer-level retention curve for the same cohort at month 3 would instead report 90 customers still active (excluding only the 10 who fully churned), a DIFFERENT number answering a different question; presenting only one of these two curves without naming which one it is invites a stakeholder to draw the wrong conclusion from it.

A representative query producing the plan-level cohort curve directly from the schema in the question:

WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('month', start_date) AS cohort_month
  FROM subscriptions
),
monthly_revenue AS (
  SELECT customer_id, DATE_TRUNC('month', event_date) AS revenue_month, SUM(amount) AS revenue
  FROM events
  WHERE event_type = 'payment'
  GROUP BY customer_id, DATE_TRUNC('month', event_date)
)
SELECT c.cohort_month,
  DATEDIFF('month', c.cohort_month, mr.revenue_month) AS month_offset,
  SUM(mr.revenue) AS cohort_revenue,
  COUNT(DISTINCT mr.customer_id) AS paying_customers
FROM cohort c
JOIN monthly_revenue mr ON mr.customer_id = c.customer_id
GROUP BY c.cohort_month, month_offset
ORDER BY c.cohort_month, month_offset;

Run against the 100-customer cohort above, this returns month_offset = 0 with cohort_revenue = 5000 and paying_customers = 100, and month_offset = 3 with cohort_revenue = 4600 and paying_customers = 90, reproducing both the 92% net revenue retention and the 90% customer-level retention computed above directly from the query rather than by hand.

Trade-offs & pitfalls

• A single "retention rate" without specifying customer-level vs. plan-level revenue is ambiguous and easy to misinterpret: always label which definition a chart is using, since the two can diverge substantially (as in the worked example, 90% customer retention vs. 92% net revenue retention are both true simultaneously and mean different things).
• Proration decisions materially change the numbers in a plan-change-heavy business and should be documented as an explicit modeling choice, not left as an implicit side effect of however the query happened to be written.
• Multiple subscriptions per customer is a common real-world wrinkle that a simplified one-subscription-per-customer model will get wrong; decide and document the unit of analysis (subscription vs. customer) up front.

Direct answer
My mentoring approach starts from diagnosing where someone actually is, not applying one fixed style, and it balances technical guidance with career development by treating them as two separate but connected tracks: technical guidance closes the gap between where they are and what the work in front of them needs right now, while career conversations look further out at where they're trying to go. The mix between the two shifts substantially depending on how experienced the person already is.

Structured elaboration

Diagnosing before applying a style

The first move with any new mentee is figuring out their actual starting point and goals, not assuming based on title or tenure. Two people at the same level can need very different things: one might need technical unblocking, another might already be technically strong but stuck on visibility or scope.

Balancing technical guidance and career development

• Technical guidance tends to dominate early in a relationship or when someone's working in genuinely new territory; it's concrete, has fast feedback loops, and builds the trust that makes career conversations land later.
• Career development becomes a larger share of the time as technical competence stabilizes; someone who's already reliable on the day-to-day work benefits more from conversations about scope, visibility, and where they're headed than from more line-by-line guidance.
• The two aren't fully separable in practice: a well-run technical conversation often surfaces the real career question underneath it (they're not struggling with the code, they're struggling with whether this kind of work is even what they want to be doing).

How the approach changes: newer teammate vs. experienced one

• A newer teammate typically needs a tighter structure: explicit expectations, closer review, and a higher ratio of technical to career conversation, because there usually isn't yet a track record to have a grounded career conversation about.
• A more experienced teammate usually needs the opposite ratio: less hands-on technical guidance (often none at all on execution, more on judgment calls and trade-offs), and more time spent on career and scope, sometimes including the expectation that they take on some mentoring of their own, since that's often the actual next step in their growth.

Worked example

Applying the philosophy

With a newer teammate, most of an early 1:1 might genuinely be spent walking through a specific technical decision they made, only pivoting to career topics once they'd built enough of a track record to have something concrete to talk about. With a more experienced teammate on the same team, the same 1:1 slot might be spent almost entirely on a scope or visibility question, with technical guidance limited to a quick sanity check on a hard trade-off they'd already mostly worked out themselves.

Signal of it working

The clearest sign the ratio was right in either case wasn't a specific number, it was whether the conversation actually used the full time productively: a newer teammate's 1:1 running long on technical questions because they had real ones was a good sign; the same happening with an experienced teammate, repeatedly, usually meant something else was being avoided, often a harder career conversation neither of us had opened yet.

Trade-offs & pitfalls

• Applying the same ratio to everyone regardless of experience. A fixed philosophy that doesn't flex by seniority isn't really a philosophy, it's a script, and it under-serves experienced mentees while potentially overwhelming newer ones.
• Letting technical conversations become a permanent default because they're easier. Technical questions have clear right answers and fast feedback; career conversations are ambiguous and can feel uncomfortable. A senior mentor notices when technical talk has become an avoidance pattern rather than what's actually needed.
• Treating career conversations as an occasional add-on rather than a real track. If career development only comes up during formal review cycles, it usually means the day-to-day mentoring relationship isn't actually addressing it.

Direct answer

A declarative data-testing framework lets you state the expected properties of a dataset (a column is never null, a value falls in a range, a duplicate rate stays under a threshold) as data rather than as imperative test code, and have the framework validate any batch of data against that specification and produce a structured, human-readable report.

Structured elaboration

Using pandera (a lightweight, pandas-native validation library, representative of this class of tool alongside Great Expectations and dbt tests), a schema for the described transactions table:

import pandas as pd
import pandera.pandas as pa
from pandera.pandas import Column, DataFrameSchema, Check

schema = DataFrameSchema({
    "transaction_id": Column(str, unique=True),
    "user_id": Column(str, nullable=False),
    "amount": Column(float, Check.gt(0)),
    "occurred_at": Column(
        "datetime64[ns]",
        Check(lambda s: s >= pd.Timestamp.now() - pd.DateOffset(years=2)),
    ),
})

The fourth named assertion, that occurred_at falls within the last two years, is the Check(lambda s: ...) on that column: it compares every value in the column against "now minus two years" and fails any row whose transaction is stale, catching a case (an old, re-surfaced, or backdated record slipping into a supposedly-recent batch) that none of the other three checks would catch.

Running this against a two-row valid frame passes cleanly (verified by execution). Running it against a deliberately broken frame, a duplicated transaction_id, a NULL user_id, a negative amount, and an occurred_at more than two years in the past, with lazy=True to collect every failure rather than stopping at the first one, correctly surfaces all four problems in one structured report: a uniqueness failure on transaction_id, a not-nullable failure on user_id, a greater_than(0) failure on amount, and the lambda date-range failure on occurred_at (verified by execution: validating a frame with occurred_at=2022-01-01, more than two years before today, alongside the other three broken values, raises a SchemaErrors collection containing exactly these four failure rows, one per check, confirming the range check fires independently of the other three). The duplicate-rate-under-1% check is not a single-column constraint pandera expresses natively; it is implemented as a dataset-level custom check computing df['transaction_id'].duplicated().mean() < 0.01 and asserting it separately, or, in a more full-featured tool like Great Expectations, as a table-level "expect_column_values_to_be_unique" style expectation with a configurable mostly-threshold.

Trade-offs and pitfalls

What the declarative approach buys you over hand-written SQL/dbt tests: the schema doubles as living documentation of what "valid" means for this table (a new engineer can read the schema and understand the contract without reading test code), failures across many checks are collected and reported together rather than failing fast on the first one, and adding a new check is a one-line declaration rather than a new SQL query to write and maintain. Where it adds friction: there is a real learning curve to the framework's API and its own failure-mode nuances (lazy versus eager validation, how it handles nullable columns by default), and for a team already deeply invested in SQL/dbt tooling, introducing a second, Python-based validation layer adds a second CI dependency and a second place engineers need to look when a check fails, which is a genuine cost, not just a one-time learning tax.

Direct answer: Frame exclusion is a clause that removes rows from an already-computed frame, applied after the ROWS/RANGE/GROUPS bounds are evaluated, not instead of them. EXCLUDE CURRENT ROW removes only the current physical row from the frame, even if other rows tied with it (its "peers" under RANGE) remain. That is meaningfully different from ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, which is a purely physical frame that stops one row before the current one and never includes any peer, tied or not, that comes after it in sort order.

Structured elaboration

• Frame exclusion options (SQL:2011): EXCLUDE CURRENT ROW (drop only the current row), EXCLUDE GROUP (drop the current row and all its peers), EXCLUDE TIES (drop all peers except the current row itself), EXCLUDE NO OTHERS (the default: exclude nothing).
• Why it matters with ties: under a RANGE frame, rows with an identical ORDER BY value are peers and RANGE always includes or excludes an entire peer group together. EXCLUDE CURRENT ROW removes only the one physical row, so its peers still count. ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, by contrast, is entirely positional: it simply stops one physical row short of the current one and has no concept of "peer" at all.
• Support varies by engine: EXCLUDE is part of the SQL:2011 standard and available in Postgres and several other engines, but not universally, so check target-engine support before relying on it.

Worked example

Table scores(id, score): (1,10), (2,20), (3,20), (4,30). Ids 2 and 3 tie on score = 20.

CREATE TEMP TABLE scores(id int, score int);
INSERT INTO scores VALUES (1,10), (2,20), (3,20), (4,30);

SELECT id, score,
  SUM(score) OVER (
    ORDER BY score
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE CURRENT ROW
  ) AS sum_excl_current,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS sum_rows_1_preceding
FROM scores
ORDER BY id;

Verified in DuckDB. Results:

For id=2: sum_excl_current = 30 (10 from id=1, plus 20 from its peer id=3; only id=2 itself is excluded). sum_rows_1_preceding = 10 (only the single physical row before id=2, which is id=1). For id=3: sum_excl_current = 30 (same peer group logic: id=3 is excluded, its peer id=2's 20 still counts, plus id=1's 10). sum_rows_1_preceding = 30 (physical rows before id=3 are id=1 and id=2: 10+20). The two ids that tie on score end up with different results between the two approaches specifically because RANGE + EXCLUDE keeps peer symmetry (both tied rows see the other's value) while the pure ROWS version does not (only the row physically earlier in sort order sees the other).

Key points

• EXCLUDE is a post-processing step on an already-defined frame; it does not change how RANGE or ROWS determines the frame's boundaries in the first place.
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING includes exactly the physical rows before the current one, full stop.
• RANGE ... CURRENT ROW EXCLUDE CURRENT ROW includes every row up to and including the current row's whole peer group, then drops just the current row, leaving other peers in.

Complexity
Both queries require one O(n log n) sort by score, then a single incremental pass. Frame exclusion doesn't add asymptotic cost: it's implemented as a bookkeeping adjustment (skip the current row, or the whole peer group, when reading from the already-maintained running frame) rather than a second pass over the data.

Edge cases

• The first row in sort order (id=1, score=10): no rows precede it under either approach, so both results are NULL, which is expected, not a bug.
• EXCLUDE GROUP instead of EXCLUDE CURRENT ROW: would drop the entire peer group for a tied row, not just the current physical row, giving id=2 and id=3 both sum = 10 (only id=1) instead of 30.
• All rows tied to a single value: every row would compute against the same peer group; EXCLUDE CURRENT ROW still only drops the one physical row each time, so different rows in that group can still get different results depending on what else is elsewhere in the frame.

Trade-offs & pitfalls

The common wrong turn is assuming EXCLUDE CURRENT ROW behaves like a ROWS-based "skip one row" adjustment; it doesn't, because it operates on the RANGE frame's peer semantics. If you actually want "everything strictly before this physical row" with no peer awareness, reach for ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING directly and skip EXCLUDE entirely. Reserve EXCLUDE for cases where you deliberately want peer-aware behavior, such as computing "the sum of everyone else in my tie group" for a leaderboard or fairness calculation, which ROWS ... 1 PRECEDING cannot express at all since it has no notion of ties.

Approach: Use a classic Grace hash-join (partition-then-probe). First pass: hash-partition both CSVs into N partitions on disk using join_key so matching keys go to same partition. Choose N so each partition pair from left/right fits into memory_limit_mb (accounting for overhead). Second pass: for each partition i, load the smaller side into an in-memory hash table and stream the other side, emitting matches to out_path. Manage temp files and clean up.

Key points:

• Partitioning ensures only partition pairs need memory.
• Handle skew by detecting partitions that still exceed memory_limit and recursively re-partition or switch to sort-merge for that partition.
• Reduce I/O by writing partitions in binary (pickle/avro) or compressed CSV and using buffered I/O.

Code sketch (simplified):

import csv, os, tempfile, hashlib
from pathlib import Path

def _hash_key(val, n):
    return int(hashlib.md5(str(val).encode()).hexdigest(), 16) % n

def external_hash_join(left_path, right_path, join_key, out_path, memory_limit_mb):
    # estimate partition count N: simple heuristic: N = max(4, total_size/(memory_limit_mb*1024*1024/2))
    left_size = Path(left_path).stat().st_size
    right_size = Path(right_path).stat().st_size
    mem = memory_limit_mb * 1024 * 1024
    N = max(4, int((left_size + right_size) / (mem/2)) + 1)

    tempdir = tempfile.mkdtemp(prefix="ehj_")
    left_parts = [open(os.path.join(tempdir,f" L_{i}.csv"), "w", newline='') for i in range(N)]
    right_parts = [open(os.path.join(tempdir,f" R_{i}.csv"), "w", newline='') for i in range(N)]

    # Partition both files
    with open(left_path, newline='') as lf:
        rdr = csv.DictReader(lf)
        writers = [None]*N
        for i in range(N):
            writers[i] = csv.DictWriter(left_parts[i], fieldnames=rdr.fieldnames)
            writers[i].writeheader()
        for row in rdr:
            h = _hash_key(row[join_key], N)
            writers[h].writerow(row)
    for f in left_parts: f.close()

    with open(right_path, newline='') as rf:
        rdr = csv.DictReader(rf)
        writers = [None]*N
        for i in range(N):
            writers[i] = csv.DictWriter(right_parts[i], fieldnames=rdr.fieldnames)
            writers[i].writeheader()
        for row in rdr:
            h = _hash_key(row[join_key], N)
            writers[h].writerow(row)
    for f in right_parts: f.close()

    # Probe phase
    out_file = open(out_path, "w", newline='')
    out_writer = None
    for i in range(N):
        Lp = os.path.join(tempdir, f" L_{i}.csv")
        Rp = os.path.join(tempdir, f" R_{i}.csv")
        lsize = os.path.getsize(Lp); rsize = os.path.getsize(Rp)
        if lsize == 0 or rsize == 0:
            continue
        # choose smaller to load
        build_path, probe_path = (Lp, Rp) if lsize <= rsize else (Rp, Lp)
        with open(build_path, newline='') as b:
            b_rdr = csv.DictReader(b)
            build = {}
            for row in b_rdr:
                key = row[join_key]
                build.setdefault(key, []).append(row)
        # check memory estimate and handle skew (not implemented: recursion)
        with open(probe_path, newline='') as p:
            p_rdr = csv.DictReader(p)
            if out_writer is None:
                out_fields = (b_rdr.fieldnames if lsize<=rsize else p_rdr.fieldnames) + \
                             (p_rdr.fieldnames if lsize<=rsize else b_rdr.fieldnames)
                out_writer = csv.DictWriter(out_file, fieldnames=out_fields)
                out_writer.writeheader()
            for prow in p_rdr:
                matches = build.get(prow[join_key])
                if matches:
                    for m in matches:
                        out_row = {**m, **prow} if lsize <= rsize else {**prow, **m}
                        out_writer.writerow(out_row)
    out_file.close()

    # cleanup
    for fn in os.listdir(tempdir):
        os.remove(os.path.join(tempdir, fn))
    os.rmdir(tempdir)

Complexity and trade-offs:

• I/O: two reads + two writes for partitioning + one read of each partition during probing ≈ 3-4× data size. More passes if recursive repartitioning needed.
• Passes: 2 main passes typically. Additional passes for skewed partitions.
• Choosing N larger reduces partition size but increases metadata and file handles; too large increases overhead.
• Memory: ensure estimated partition fits by choosing N so max(partition_size_left + partition_size_right) < memory_limit_mb * 0.6 (reserve for python overhead).
  Handling skew:
• Detect partition whose size exceeds threshold and either:
  • Recursively partition that partition with a different hash salt (multi-pass), or
  • Use external sort-merge on that partition pair, which avoids building a huge hash table.
    Practical notes:
• Use binary formats (Parquet/Avro) and compression in production for speed and space.
• Use buffered I/O, limit open files, and consider multiprocessing to parallelize partitioning/probing.

Approach
I would make checkpointing a consistent snapshot of everything needed to resume training, not just model weights. That means saving model parameters, optimizer state, learning-rate scheduler state, random number generator seeds, current epoch and batch cursor, and any data sampler state.

Design

• Write checkpoints atomically: first write to temporary storage, validate checksums, then publish a manifest pointer.
• Keep checkpoints incremental when possible, but always make the latest one self-contained for fast restart.
• Trigger an immediate checkpoint on spot interruption notice, then resume from the last good manifest.
• Store code version, config, and data version so the run is reproducible.

Worked example
If I checkpoint every 10 minutes and a preemption happens at minute 37, the restart only redoes at most 7 minutes of work, not the whole job.

Why this works
The manifest guarantees consistency, the RNG and sampler state keep replay deterministic, and the atomic publish prevents half-written checkpoints from being used.

Situation: At my previous company we supported a recommendation feature that consumed user activity events from a Kafka topic, processed them in Spark Streaming, and wrote aggregated signals to BigQuery. Product decided to A/B test a new "listening-intent" event that changed schema and increased throughput by ~3x — rolling it out in two weeks.

Task: I needed to adapt pipelines quickly so downstream models kept receiving correct, timely signals without blocking the product launch.

Action:

• I scoped impact in 24 hours: identified affected topics, schemas, downstream tables, and SLAs. Created a changelog mapping old → new fields and which aggregations would change.
• Implemented schema-tolerant ingestion in Spark (using Avro with schema registry) so new fields are optional and unknown fields are dropped by default. Added a lightweight transformation layer to compute fallbacks for renamed fields.
• Optimized windowing and parallelism to handle 3x throughput (increasing executors, tuning Spark shuffle partitions).
• Wrote an Airflow DAG change with a feature flag to route a percentage of traffic to the new path for canary testing.
• Communicated risks and rollback: in a stakeholder email and JIRA, I listed failure modes (data loss, inflated counts, downstream model drift), mitigation steps, monitoring dashboards (throughput, error rates, key metric diffs), and a rollback plan: flip feature flag to route 100% back to the old pipeline, revert schema registry compatibility setting, and redeploy previous DAG within 15 minutes.
• Ran a 2-hour canary at 10% traffic, monitored dashboards and validated sample outputs with data scientists, then gradually increased to 100% over 6 hours.

Result: The change was deployed on schedule with zero data loss and <1% variance in core metrics compared to expected. Canary caught a minor timestamp parsing bug which we fixed before full rollout. The process reduced uncertainty for product and gave data scientists confidence to use the new signal immediately.

Why this fits Spotify: I move quickly without sacrificing safety—using incremental rollout, schema evolution, observability, and clear rollback plans. In fast-changing environments like Spotify, that combination (technical agility + proactive communication and guardrails) lets teams iterate rapidly while keeping data quality and downstream consumers protected.