Netflix Data Engineer - Entry Level Interview Preparation Guide (2026)

Direct answer. Nested loop is cheapest when the outer side is small and the inner side has a cheap way to be probed (typically an index); hash join is the workhorse for large, unsorted inputs where one side comfortably fits in memory to build a hash table from; sort-merge join wins when both inputs are already sorted (or cheaply sortable) on the join key, since it can then walk both in a single linear pass with no random lookups and no hash table at all.

Structured elaboration. Nested loop's cost is roughly (outer rows) times (cost per inner probe); it degrades badly the moment the outer side is larger than expected, since every extra outer row multiplies the total work. Hash join's cost is roughly (build side rows, to construct the hash table) plus (probe side rows, each a cheap in-memory lookup); its main risk is memory: if the build side is bigger than the memory budget allotted, the hash table spills to disk in partitions, which is far slower than an in-memory hash join but still usually better than a nested loop over the same volumes. Sort-merge needs both inputs sorted; if an index already provides that order, the "sort" part is free and the join itself is a fast linear merge, but if neither input is sorted and both are large, the up-front sort cost can make this the worst of the three options.

Worked example. Joining a 50-million-row fact table to a 200-row lookup table: a nested loop with an index on the fact table's join key (probing the fact table once per lookup row) does roughly 200 cheap index probes, which is far cheaper than building a hash table from 50 million rows. Flip the sizes, joining two roughly-equal, unsorted multi-million-row tables, and a hash join (build from the smaller of the two, probe with the larger) beats a nested loop by orders of magnitude, since nested loop would need tens of millions of probes instead of one hash table build plus one linear probe pass.

Trade-offs and pitfalls. None of the three algorithms is universally best; the right one is a function of the relative sizes of the two inputs, whether either side is already sorted or indexed on the join key, and how much memory is available for a hash table. A senior red flag is seeing a nested loop join over two large, unindexed inputs (that's the shape most likely to be a genuine planner mistake, usually traced back to a cardinality misestimate) versus seeing it over one genuinely small input joined to a large indexed one (that's usually correct and fast).

Direct answer. A FULL OUTER JOIN keeps every row from both sides, matched where possible and NULL-padded where not, which makes it the natural tool for reconciliation: it surfaces rows that exist on only one side just as readily as ones that mismatch on both.

Structured elaboration. Build the reconciliation with COALESCE to pick a display key regardless of which side it came from, then classify each row with a CASE expression that checks, in order: is the key missing from one side entirely (only_a / only_b), and if present on both, do the values actually match (match / mismatch).

Worked example. sales_a(date, revenue): ('2025-01-01', 100), ('2025-01-02', 80). sales_b(date, revenue): ('2025-01-01', 100), ('2025-01-03', 60).

SELECT COALESCE(a.date, b.date) AS date,
       a.revenue AS revenue_a, b.revenue AS revenue_b,
       COALESCE(a.revenue,0) - COALESCE(b.revenue,0) AS delta,
       CASE
         WHEN a.date IS NULL THEN 'only_b'
         WHEN b.date IS NULL THEN 'only_a'
         WHEN a.revenue = b.revenue THEN 'match'
         ELSE 'mismatch'
       END AS reconciliation_status
FROM sales_a a
FULL OUTER JOIN sales_b b ON a.date = b.date
ORDER BY date;

Result: ('2025-01-01', 100, 100, 0, 'match'), ('2025-01-02', 80, NULL, 80, 'only_a'), ('2025-01-03', NULL, 60, -60, 'only_b'). Every discrepancy type is visible in one pass: the shared date matches exactly, and each system's unique date is flagged distinctly rather than silently dropped.

Trade-offs and pitfalls. Not every engine supports FULL OUTER JOIN natively (older MySQL versions don't); you can emulate it as a LEFT JOIN UNION a RIGHT JOIN (or, more simply, a LEFT JOIN UNION an anti-joined right-side-only query), which produces the identical result at the cost of scanning each side twice. The pattern also extends past two sources: reconciling three or four systems at once is usually cleaner as a chain of FULL OUTER JOINs (A FULL OUTER JOIN B FULL OUTER JOIN C, using COALESCE to unify the key at each step) rather than trying to write one giant CASE expression across all combinations by hand, since the number of "only in X" cases grows combinatorially with the number of sources.

Direct answer. Live re-sharding without downtime requires the new and old partition schemes to coexist for a transition window: write to both (or migrate incrementally with a cutover marker), keep consumers reading correctly throughout, and only retire the old scheme once every consumer has confirmed it has migrated -- treating this as an online migration with a rollback path, not a one-shot cutover.

Structured elaboration.

1. Initial partition assignment. Do not size the initial scheme for today's average load; size it with headroom for the known skew ceiling (up to 1000x per the scenario). Start with a hash-based assignment across a partition count high enough that even the largest anticipated single tenant's share stays well under any one partition's practical throughput ceiling, and reserve a small fixed set of partitions from day one as an escape valve (a shared low-traffic pool plus a handful of unassigned partitions) so a newly-hot tenant can be moved onto dedicated capacity without an emergency topic-level change. If tenant size tiers are known upfront (e.g., enterprise vs. self-serve), route by tier at assignment time rather than treating all tenants as uniform, since that avoids discovering the skew only after it becomes a hot partition.
2. Detect the need before it's an emergency. Track per-tenant traffic share against total partition count; when a small number of tenants approach the point where even distribution is no longer possible at the current partition count (traffic variance up to 1000x per the scenario), that is the trigger to plan re-sharding proactively rather than reactively.
3. Choose a migration mechanism. The two realistic options: (a) topic split -- create a new topic with more partitions (or a partition scheme better suited to the skew, e.g., dedicated partitions for known heavy tenants) and dual-write to old and new while consumers migrate at their own pace; (b) online migration with a coordinator that reassigns partition ownership incrementally, backed by a durable log so no writes are lost mid-transition. Topic split is operationally simpler and safer for most teams; a true online repartition is more elegant but higher-risk to implement correctly.
4. Consumer group rebalance behavior. Any partition-count or assignment change triggers a consumer-group rebalance, which pauses consumption for all consumers in the group (not just the ones affected) for the rebalance duration. For availability-sensitive pipelines, use a rebalance-minimizing assignment strategy (e.g., cooperative/incremental rebalancing rather than stop-the-world) and schedule the change for a lower-traffic window even when a true zero-downtime path exists, as a safety margin.
5. Cutover and rollback. Maintain the ability to route back to the old scheme until the new one has run cleanly for a defined validation period (record-count parity checks, no consumer lag growth); only then decommission the old topic/partitions.

Worked example. A tenant's traffic grows from a typical 1x share to 1000x within a promotional event. The initial partition assignment (hash-based, uniform) puts that tenant on a single partition among 200. Detection (per-partition throughput ratio) fires well before the event peaks, giving time to create a topic variant with dedicated partitions for the top 5 tenants by volume plus a shared pool of hashed partitions for everyone else. Dual-write both topics for a 24-hour window, migrate consumers to read from the new topic (which they can do incrementally, since both carry equivalent data during the overlap), then retire the old topic once consumer lag on the new topic is stable and a sample-based record-count reconciliation between old and new confirms no data loss.

Trade-offs & pitfalls. A soft cluster limit on total partition count (e.g., 10k, as often exists for coordination/metadata overhead reasons) means dedicated per-tenant partitions cannot scale indefinitely with tenant count; the dedicated-plus-shared-pool design above is a common way to bound partition growth while still isolating the few tenants that actually need it. Dual-writing doubles write cost and storage temporarily; keep the overlap window as short as validation allows, not open-ended.

Direct answer

Recompute the corrected 90 days into a separate staging area using the fixed transform logic, verify the staged output against the current live data with automated checks, then swap the corrected partitions into place atomically, one bounded time window at a time, so dashboards keep reading a consistent, even if briefly stale, version throughout and never see a half-recomputed state.

Structured elaboration

Why staging plus atomic swap, not in-place correction

Correcting rows in place while dashboards are live means any query that runs mid-correction sees a mix of old and new values for the same metric, which is worse than being uniformly wrong. Writing the corrected 90 days to new, parallel storage and only swapping the read pointer once a whole window is verified keeps every read consistent: either the old, known-wrong-but-internally-consistent version, or the new, corrected-and-verified version, never a blend of both.

Windowing the backfill

Recompute in bounded time chunks rather than all 90 days at once. Each chunk gets verified and swapped independently, so a defect found partway through does not block chunks already verified, and a bug in the fix itself is caught on one small chunk before it has been applied to all 90 days.

Determinism is a precondition, not a nice-to-have

Before touching real data, confirm the corrected transform is deterministic: recomputing the same input window twice, same code, same raw input, must produce identical output. This matters just as much when the corrected logic is a code-driven feature transform feeding a model as when it is a simple metric aggregation: either way, running the backfill twice or out of order must not silently recompute the same window into two different results. If the fix relies on anything time-dependent or order-sensitive, such as a default that reads the current time, or an aggregation whose result depends on processing order, running the backfill twice or out of order produces different corrected values, which defeats the audit trail this whole design is trying to establish. Confirm reproducibility on one chunk before running the rest.

Verification before swap

Compare staged output to current live output for the same window on row counts, key aggregate totals (with the specific corrected metric expected to differ and every other metric expected to match exactly), and referential completeness, checking that the fix introduced no orphaned keys. Only swap a chunk once its verification passes; a failed chunk stays on the old, wrong data until investigated, without blocking chunks that already passed.

Communication as part of the design

Since dashboards stay live throughout, tell stakeholders which historical dates are still on the old, known-wrong numbers and which have already swapped to corrected values, so nobody draws a conclusion from a number mid-flight.

flowchart LR
    Raw[Raw immutable source] --> Fix[Corrected transform logic]
    Fix --> Staging[(Staging: recomputed partitions)]
    Staging --> Verify{Verification checks pass?}
    Verify -- Yes --> Swap[Atomic partition swap]
    Verify -- No --> Retry[Investigate and re-run]
    Swap --> Live[(Live tables: dashboards keep reading)]

Worked example

Assume the corrupted metric is computed from roughly 50 million source rows per day:

$50{,}000{,}000 \text{ rows/day} \times 90 \text{ days} = 4.5\text{ billion rows to recompute}$

Chunking the backfill into 7-day windows:

$90 \text{ days} \div 7 \text{ days/batch} = 12.86 \Rightarrow 13 \text{ batches (last one partial, 6 days)}$

which puts roughly

$\frac{4.5\times10^{9} \text{ rows}}{13 \text{ batches}} \approx 346\text{ million rows per batch}$

a size the verification step needs to run against once per batch, thirteen times total, rather than once against the full 4.5 billion rows, which is what makes an early bug in the fix cheap to catch instead of expensive.

Trade-offs & pitfalls

• Correcting live tables in place, even for "just one column," is not atomic from a concurrent reader's point of view unless the store guarantees it, and most analytical stores do not at the row level during a bulk update.
• Running all 90 days as one giant job means a bug discovered on day 60 requires redoing all 90, instead of redoing one already-isolated chunk.
• Smaller chunks catch problems earlier and reduce blast radius but add coordination overhead (more swap events, more verification runs); chunk size should track how much the fix is trusted, not a fixed default.
• Skipping the determinism check because the fix "looks simple" is a common wrong turn; a fix that is not reproducible turns the audit trail, what changed, why, and by how much, into something nobody can reconstruct later.

Direct answer

Use a relational online transaction processing (OLTP) database, meaning a system optimized for many small, fast, individually correct read and write operations, for the sub-second transactional reads. Replicate its data via change data capture (CDC) into a columnar online analytical processing (OLAP) warehouse, a system optimized for scanning and aggregating large volumes of historical data, for complex ad hoc analytics. Build near-real-time dashboards as a third, narrower consumer of that same CDC stream, rather than querying either the OLTP primary or waiting on the warehouse's normal load cadence.

Structured elaboration

OLTP for sub-second reads. The transactional database stays the fast path for the product's own operational reads, the ones a user is actively waiting on, and should never be the target of heavy ad hoc analytical scans. A large aggregate query competing for the same memory cache and disk input/output as production traffic is a classic self-inflicted incident, exactly why the analytics workload needs to live somewhere else entirely.

OLAP for complex ad hoc analytics. A columnar warehouse stores data column by column, reading only the few columns a query needs across billions of rows rather than whole rows, and is built for the group-by, aggregate, and join-heavy queries analysts run, at a batch or near-batch freshness, commonly minutes, depending on the pipeline's cadence, that is entirely acceptable for ad hoc analytics as distinct from the product's own live reads.

Near-real-time dashboards as a third path. Dashboards needing freshness measured in seconds, not minutes, an operations dashboard or a live metrics view, should not wait on the warehouse's normal load cycle, nor hit the OLTP primary directly. The standard pattern is a streaming layer, a CDC stream consumed by a stream processor, that maintains continuously updated, pre-aggregated views in a fast serving store purpose-built for "show me the last few minutes, refreshed continuously," a genuinely different requirement from either the OLTP path or the OLAP warehouse's own load cadence.

Data flow end to end. The OLTP primary's write-ahead log feeds a CDC connector, which publishes into a durable stream, consumed in parallel by two independent paths: a micro-batch loader into the OLAP warehouse for ad hoc analytics, and a stream processor maintaining pre-aggregated dashboard views in a low-latency serving store.

Minimizing operational overhead. Prefer managed versions of each piece, managed OLTP, a managed CDC connector, a managed warehouse, managed stream processing, over self-hosting the pipeline, since the team's differentiated work is the data model and the dashboards, not operating streaming infrastructure. Feeding both the warehouse and the dashboard path from one CDC stream, rather than two separate extraction jobs hitting the OLTP primary, also halves the load the analytics side places on production.

Worked example

flowchart LR
    App[Application] -->|writes| OLTP[(OLTP: relational primary)]
    OLTP -->|write-ahead log, CDC| Stream[[Durable stream]]
    Stream -->|micro-batch load| OLAP[(OLAP warehouse)]
    Stream -->|stream processing| Agg[Stream processor: pre-aggregates]
    Agg -->|serve| Dash[(Serving store: dashboards, seconds-fresh)]
    Analyst -->|ad hoc SQL| OLAP
    OpsViewer[Ops viewer] -->|live view| Dash

Trade-offs and pitfalls

A pitfall is pointing the dashboard directly at the OLAP warehouse and calling it near-real-time. A warehouse's normal load cadence, commonly minutes and sometimes more depending on pipeline design, is a genuinely different freshness tier from what near-real-time implies; if the actual requirement is seconds-level, the dashboard needs the streaming path, not a faster warehouse refresh schedule.

A second pitfall is skipping CDC and instead running scheduled full-table extracts from the OLTP primary. That both taxes production directly and gets progressively slower as data volume grows, since extract time itself grows, whereas CDC only ever ships the delta.

What would flip the recommendation: if "near-real-time" in this specific product actually means acceptable at 5 to 15 minute freshness once clarified with stakeholders, the third streaming path can be dropped entirely, and the OLAP warehouse's own micro-batch cadence covers both analytics and dashboards, meaningfully reducing operational overhead. Always confirm the actual freshness bar before building the more complex streaming path.

Direct answer
Read for the conclusion the source material is actually building toward, state that conclusion as the first sentence, then compress the two or three things a reader needs to trust it: the biggest risk or driver, and what you want them to do next.

Structured elaboration

1. Find the real headline first. Before writing a single summary sentence, identify what decision or fact the full document is ultimately arguing for. If you cannot state it in one sentence, you have not finished reading it.
2. Pick the two or three supporting points that matter most, not the ones that are easiest to quote. A common failure is summarizing the document's structure (section 1 covers X, section 2 covers Y) instead of its substance.
3. State the risk or the catch. A summary that hides the caveat the full report surfaces on page 8 is not a summary, it is spin. Include the single biggest risk or open question in the same paragraph as the good news.
4. End with the ask. What do you want the reader to approve, decide, or do. If there is no ask, say so explicitly ("for awareness only, no action needed") so the reader doesn't hunt for one.
5. Cut ruthlessly for length last. Once the four pieces above are on the page, tighten wording, not content: remove hedging phrases ("we believe that," "it seems"), redundant qualifiers, and any sentence that restates something already said.

Worked example
Source material (excerpt of a longer report): a 1,400-word analysis of a marketing campaign covering channel-by-channel spend, a methodology section, a table of conversion rates by week, a note that attribution data for the last two weeks is incomplete, and a recommendation to shift 20% of budget from display to search.

Executive summary (under 80 words): "Recommendation: shift 20% of display budget to search next quarter. Search converts at roughly double the rate of display in this campaign, and the gap has held for six of the eight weeks measured. Caveat: the final two weeks of data are incomplete due to a tracking gap, so we're treating the 2x figure as directional rather than final. Full channel breakdown and methodology in the appendix."

This keeps the conclusion, the one supporting number, the caveat, and the ask, and drops the methodology walkthrough and the week-by-week table, which belong in an appendix a reader can choose to open.

Trade-offs and pitfalls

• The most common failure mode is summarizing evenly across sections instead of unevenly toward the conclusion; a good summary is lopsided on purpose.
• Compressing away a real caveat to make the summary look cleaner is a trust problem, not an editing win. It's the fastest way to have your future summaries doubted.
• For a highly technical audience, "top risk" might be a methodology limitation; for an executive audience it might be a cost or timeline risk. The compression target changes with audience even when the underlying facts don't.

Direct answer

Maintain per-user running aggregates with groupBy(window(...), "user_id") under a watermark, write output via writeStream with checkpointLocation set, and let the watermark itself drive state cleanup: once event time (as tracked by the watermark) passes a window's end plus the watermark threshold, Structured Streaming's state store drops that window's accumulated state automatically, which is what keeps the job's memory bounded over an unbounded stream. Checkpointing protects the query's OWN progress (which offsets were processed, what the state store contained) across a restart; it does not protect against, or retroactively fix, data that arrives after its watermark has already passed.

Structured elaboration

Where checkpointLocation goes and what it stores. Set once per streaming query, writeStream.option("checkpointLocation", "/checkpoints/<query-name>"); each query needs its OWN checkpoint directory (never shared between two different queries). The directory accumulates, per micro-batch: the source offsets read (offsets/), which batches were fully committed (commits/), and, for a stateful query specifically, the actual contents of the state store (state/), one versioned snapshot plus incremental delta files per shuffle partition.

Watermarking for late data. withWatermark("event_time", "10 minutes") tells Structured Streaming to track the maximum event time seen so far and treat any row with event_time more than the threshold behind that maximum as too late to affect any window that has already been allowed to close. This bounds how long the engine keeps a given window's state open, which is the mechanism that makes "periodic state cleanup" automatic rather than something the application code manages by hand.

Triggering periodic state cleanup, concretely. State cleanup is not a separate scheduled job; it happens as a side effect of the watermark advancing on each micro-batch (controlled by trigger(processingTime=...), the interval at which the engine checks for new data and, in doing so, re-evaluates the watermark and finalizes any windows now behind it). A shorter processing-time trigger means the watermark advances (and stale state gets cleaned up) more promptly, at the cost of more frequent, smaller micro-batches.

What checkpointing protects and what it does not.

• Protects: exactly which offsets have been durably processed (so a restart resumes from the right place, not from the beginning or with data skipped), and the state store's contents at the last successfully committed batch (so a restart does not lose in-flight aggregation state for open windows).
• Does not protect: data that arrives with an event time already older than the current watermark. That data is dropped by design (it cannot correctly update a window that has already been finalized and had its state discarded), and this is a data-completeness trade-off the watermark threshold controls, not something checkpointing recovery changes. It also does not protect against a CODE CHANGE to the query's aggregation logic between restarts; changing the grouping columns or aggregation function is generally incompatible with resuming from an old checkpoint's state schema.

Worked example

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("stateful-agg").getOrCreate()

raw = spark.readStream.format("kafka") \
    .option("kafka.bootstrap.servers", "broker:9092") \
    .option("subscribe", "events") \
    .load()

events = (raw.select(F.from_json(F.col("value").cast("string"), event_schema).alias("e"))
             .select("e.*"))

agg = (events
       .withWatermark("event_time", "10 minutes")
       .groupBy(F.window("event_time", "5 minutes"), "user_id")
       .count())

query = (agg.writeStream
    .outputMode("append")  # append only emits a window once its state is finalized
    .format("console")
    .option("checkpointLocation", "/checkpoints/per-user-window-counts")
    .trigger(processingTime="30 seconds")
    .start())

query.awaitTermination()

To confirm this shape actually behaves as described, I ran the equivalent logic against Spark's built-in rate source (generating synthetic timestamped rows locally, standing in for Kafka) with a 5-second window and a 3-second watermark, a memory sink instead of console, and a 1-second trigger, for a bounded 30-second run:

numInputRows per batch (last 5 of 29 batches actually executed):
 batchId 24 numInputRows 20  watermark 2026-07-30T19:34:04.044Z
 batchId 25 numInputRows 20  watermark 2026-07-30T19:34:05.044Z
 batchId 26 numInputRows 20  watermark 2026-07-30T19:34:06.044Z
 batchId 27 numInputRows 20  watermark 2026-07-30T19:34:07.044Z
 batchId 28 numInputRows 20  watermark 2026-07-30T19:34:08.044Z

finalized (state-cleaned-up) window results, count: 25
 window[12:33:40-12:33:45] user 0 -> 4
 window[12:33:40-12:33:45] user 1 -> 4
 window[12:33:40-12:33:45] user 2 -> 4
 window[12:33:40-12:33:45] user 3 -> 4
 window[12:33:40-12:33:45] user 4 -> 3
 window[12:33:45-12:33:50] user 0 -> 20
 ... (25 rows total across 5 users x windows finalized in the run)

checkpoint directory (proves real checkpointing occurred):
 commits/: 29 committed batch entries (0..28)
 offsets/: 30 offset entries (0..29)
 state/0/: 4 state-store partitions (spark.sql.shuffle.partitions set to 4 for this run),
           each containing versioned .delta state files

(actually executed with python3.12 + pyspark 3.5.1 + Java 17, local[2], rate source at 20 rows/sec, spark.sql.shuffle.partitions=4; full state-partition and per-window counts captured in the run log). This confirms three things concretely: the checkpoint directory genuinely accumulates offset/commit/state artifacts (checkpointing is really happening, not a no-op), outputMode("append") genuinely withholds a window's result until its state is finalized (25 rows only appear after the watermark passes each window, not immediately), and the query keeps running and committing new batches continuously, which is the state-cleanup mechanism in action (old windows' state is not re-emitted or re-touched once finalized).

Trade-offs and pitfalls

• The watermark threshold is a data-completeness knob, not just a memory knob. A 3-second watermark (as in the executed example) or a 10-minute one (as in the Kafka-based sketch) both bound state size, but they also determine how much genuinely-late data gets silently dropped; sizing this threshold is a business decision (how late can events realistically arrive) as much as an engineering one.
• Common mistake: assuming checkpointing alone makes the pipeline "safe" against any failure mode, including a code change to the aggregation logic; restarting a stateful query from an old checkpoint after changing the groupBy columns or window definition is a common source of confusing runtime errors or silently wrong results, and generally requires starting a fresh checkpoint (accepting the loss of in-flight state) rather than resuming.
• Common mistake: setting the processing-time trigger far longer than the watermark threshold, which does not break correctness but does delay when stale state actually gets cleaned up, since cleanup only happens as a side effect of a micro-batch running and re-evaluating the watermark.
• outputMode("append") (used above) only emits a window's result once it is finalized, which is what makes late-arriving-but-still-within-watermark corrections possible without emitting a value that then needs to be corrected; outputMode("update") instead emits partial results as they change, useful for lower-latency visibility into in-progress windows but requiring the downstream consumer to handle values that may still change before finalization.

The pattern is delete-then-insert scoped to exactly one partition, wrapped in a single transaction, so a reader never sees a half-loaded state and a retry never duplicates or corrupts other partitions.

BEGIN TRANSACTION;

DELETE FROM fact_events WHERE event_date = DATE '2026-01-01';
INSERT INTO fact_events
SELECT * FROM fact_events_stg_20260101;

COMMIT;

Verified: starting with fact_events holding rows for both 2026-01-01 and 2026-01-02, and a staging table fact_events_stg_20260101 with a corrected value for an existing row, an unchanged row, and one row that was previously missing:

before: 2026-01-01 has rows (1, amount=10), (2, amount=20)
        2026-01-02 has row  (3, amount=30)

staging: (1, amount=11 [corrected]), (2, amount=20 [unchanged]), (4, amount=40 [new])

after the transactional swap:
  event_id | event_date | amount
  1        | 2026-01-01 | 11.0
  2        | 2026-01-01 | 20.0
  4        | 2026-01-01 | 40.0
  3        | 2026-01-02 | 30.0   <-- untouched

re-running the SAME load again: identical result (confirmed idempotent)
2026-01-02's row count confirmed unchanged at every step: 1

Why this stays scoped to one partition: both the DELETE and the INSERT filter by event_date, and staging is itself built per-partition (its own table or a filtered subset), so nothing outside 2026-01-01 is ever touched, which is what lets you reprocess one day without a full-table operation.

Why the transaction matters, not just the DELETE+INSERT logic: without wrapping both statements in one transaction, a reader querying between the DELETE and the INSERT would see zero rows for that partition, a real (if brief) data-availability regression for anyone querying at the wrong moment, and a crash between the two statements would leave the partition empty rather than in either its old or new correct state. A single transaction makes the swap atomic from any reader's point of view: they see either the old partition contents or the new ones, never neither.

Supporting backfills and reprocessing without duplicates: because the DELETE always removes the full current contents of the target partition before the INSERT, this pattern is naturally idempotent, a backfill or a manual reprocess of a partition is exactly the same operation as its normal load, not a special code path. That's the core advantage over an append-only or a keyed-MERGE approach for this specific case: you don't need a unique constraint or a dedup step, because you're replacing a bounded, well-defined chunk wholesale rather than trying to reconcile individual rows.

Trade-off worth naming: this pattern requires staging to hold the FULL correct set of rows for the partition being reloaded, not just a delta; if your staging batch is itself incomplete (missing rows that should still be in that partition), the DELETE will correctly remove them but the INSERT won't bring them back, silently shrinking the partition. That's the failure mode to design guardrails against (a row-count sanity check on staging before running the swap), not a corner case you can ignore.

Direct answer: For the slow SUM(...) OVER (PARTITION BY user_id ORDER BY ts) query, diagnose in this order: first confirm whether the partition key is skewed (a handful of users with far more rows than everyone else), since no indexing fixes that; then check whether an index on (user_id, ts) lets the engine skip the explicit sort the plan is paying for; only after those are ruled out or exhausted does restructuring the query (pre-aggregating, or bounding the frame) become the next lever. Separately, a per-user running total written as a self-join or a correlated subquery per row is quadratic work in disguise: rewriting it as a single window function turns an O(n^2)-per-partition comparison into one O(n log n) sort plus a single O(n) pass, because the window function reuses the previous row's accumulated state instead of re-scanning all prior rows from scratch for every single row.

Approach: diagnosing the slow EXPLAIN ANALYZE

A plan showing a large Sort and a WindowAgg with high disk usage on a huge table, timing out, is telling you the sort and the per-partition running-sum state both outgrew available memory. Work through these in order:

1. Check for partition-key skew first. SELECT user_id, COUNT(*) FROM tx GROUP BY user_id ORDER BY 2 DESC LIMIT 10; If a small number of users dominate row counts, that alone can make one partition's window state balloon regardless of any indexing, and the fix is closer to "cap or pre-aggregate that user's history" than "tune the query."
2. Add or confirm an index matching the partition and order columns: CREATE INDEX ON tx(user_id, ts);. If the engine can scan in that order already, it can skip the explicit Sort node in the plan entirely, which is usually the single biggest win, since sorting a huge table is often the dominant cost, not the window computation itself.
3. Reduce partition cardinality or bound the frame if the running sum only actually needs to look back a fixed distance (e.g. ROWS BETWEEN 29 PRECEDING AND CURRENT ROW for a 30-row trailing sum) rather than the full-partition UNBOUNDED PRECEDING default; a bounded frame needs a small sliding buffer instead of the whole partition's history.
4. Restructure only if the above aren't enough: pre-aggregate to a coarser grain before windowing (e.g., roll transactions up to one row per user per day first, if daily granularity is acceptable), or maintain an incrementally-updated running-total table so the expensive full recompute never has to happen live.

Approach: self-join / correlated subquery to single-pass window function

-- BEFORE: self-join running total (quadratic)
SELECT t1.user_id, t1.ts, t1.amount,
  SUM(t2.amount) AS running_total
FROM tx t1
JOIN tx t2 ON t2.user_id = t1.user_id AND t2.ts <= t1.ts
GROUP BY t1.user_id, t1.ts, t1.amount
ORDER BY t1.user_id, t1.ts;

-- AFTER: single-pass window function
SELECT user_id, ts, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM tx
ORDER BY user_id, ts;

Key points

• The self-join re-derives every row's running total independently, from scratch, by joining it against every earlier row in its partition and summing; the window function computes it incrementally, carrying forward one running accumulator per partition as it walks the sorted rows once.
• A correlated subquery version (SELECT SUM(amount) FROM tx t2 WHERE t2.user_id = t1.user_id AND t2.ts <= t1.ts run once per outer row) has the identical cost shape as the self-join: it re-executes a full sub-scan for every outer row.
• Both rewrite targets need a tiebreaker in ORDER BY (an id column alongside ts) if ts can repeat within a user, so the running total's row-by-row order is reproducible.

Worked example

Verified in DuckDB against tx(user_id, ts, amount) with rows (1,'01-01',100), (1,'01-02',50), (1,'01-03',30), (2,'01-01',20), (2,'01-02',40), both queries return the identical running totals for user 1: 100, 150, 180, and for user 2: 20, 60. The rewrite changes performance characteristics only, never the result.

Complexity
The self-join (or correlated subquery) does, for every row in a partition of size k, a scan/join against up to k earlier rows, so total work per partition is O(k^2), and summed across all partitions that's proportional to the sum of squared partition sizes rather than the total row count n; for a table dominated by one huge partition, this degrades badly. The window-function version requires one sort of each partition (O(k log k), or free if an index already provides that order) followed by a single incremental pass (O(k)) that adds each row's value to a running accumulator, giving O(n log n) overall in the worst case, or O(n) if the sort is avoided via an index.

Edge cases

• Duplicate ts values within a user: the self-join's t2.ts <= t1.ts condition includes all tied rows for every one of them, which can double- or triple-count depending on how many rows share that timestamp, unless a tiebreaker column is added to strictly order ties; the window-function rewrite has the identical ambiguity if it relies on the default RANGE frame (which groups ties together) instead of an explicit ROWS frame with a tiebreaker in ORDER BY.
• An empty or single-row partition: both versions correctly return that row's own value with nothing summed in from elsewhere.
• NULL amounts: SUM() ignores NULLs in both the self-join's aggregate and the window function, so behavior matches; a NULL ts sorts as its own peer group under the default ordering rules and needs the same NULLS FIRST/NULLS LAST consideration in either version.

Trade-offs & pitfalls

The subtle trap in the diagnosis playbook is reaching for "add an index" before checking for skew: an index fixes a sort-dominated plan, but it does nothing for a single oversized partition whose per-partition window state simply can't shrink no matter how the rows are ordered. Conversely, once the self-join is rewritten to a window function, it's tempting to assume the query is now cheap; if the partition is still enormous and the frame is still the unbounded default, the window function still has to buffer a large amount of per-partition state, just with much better asymptotics than the self-join it replaced, not zero cost.

Direct answer

At two days of silence with a deadline approaching, keep working the problem in parallel on two tracks: escalate progressively (wider audience, shorter response window) instead of waiting indefinitely or jumping straight to someone's manager, and start a temporary workaround so your own deadline isn't hostage to someone else's response time.

Structured elaboration

1. Reconfirm the ask was clear before escalating. Silence sometimes means the original message was ambiguous or buried, not that it's being ignored. A quick, sharper re-send (what's needed, by when, what breaks if it slips) is worth trying before widening the audience.
2. Widen the channel and audience, not just the volume. Loop in a teammate of the owner's, or their tech lead, with a concise summary: what's blocked, since when, and what you need. This isn't going over anyone's head yet, it's making sure the request isn't sitting unseen in one inbox.
3. Escalate to management if there's still no response, framed around unblocking the work, not blaming the person: bring your own manager or a shared point of contact (like a PM) into a short, direct conversation rather than an open-ended thread.
4. Start a workaround in parallel, not sequentially after escalation: a mock, a stub, or a scoped assumption that lets you keep making progress while the real dependency gets resolved, clearly labeled as temporary so it doesn't quietly become permanent.
5. Close the loop afterward. Once unblocked, note what caused the delay (no on-call coverage, unclear ownership, a channel nobody monitors) so the same two-day silence doesn't repeat next time.

Worked example

Say another team owns a data pipeline, and a schema change they need to ship is blocking your dashboard launch, due in three days. You messaged the pipeline owner two days ago and got no reply.

1. Reconfirm: you send a sharper follow-up in the same thread: "Following up: I need the orders table schema change merged by Thursday EOD to hit our dashboard launch Friday. Anything blocking you on it, or should I loop in someone else?"
2. Widen: a few hours pass with no reply, so you message the pipeline team's tech lead directly (not a reply-all): "I've been blocked on the orders schema change since Monday and our Friday launch depends on it. Can you help me find the right person, or unblock it yourself?"
3. Escalate: by end of day, still nothing, so you bring it to your manager or a shared PM in a short conversation, not a long thread: "I've tried the owner directly and through their lead over two days with no response, and Friday's launch depends on this. Can you help get it unblocked?"
4. Workaround, run in parallel from day one: while those messages are going out, you build your dashboard against a stubbed version of the new schema (a local view with the expected new columns backfilled from sample data), clearly commented as temporary, so the launch timeline doesn't wait on the real merge landing.
5. Close the loop: once the schema change lands, you raise in the team retro that the pipeline team had no on-call coverage for urgent schema requests, and propose a shared "blocked on us" channel so a two-day silence doesn't happen again.

(The same five-step shape applies outside engineering: a designer blocked on a brand asset from marketing, or a QA engineer blocked on a test environment from infra, would reconfirm, widen, escalate, work around, and close the loop the same way.)

Trade-offs & pitfalls

• Pitfall: escalating too fast, before trying a second direct attempt, which can read as skipping over someone unnecessarily.
• Pitfall: waiting too long out of politeness, which puts your own deadline at risk and, in review, looks like you didn't flag a risk early enough.
• Pitfall: treating escalation and workaround as either/or. Doing them in parallel protects the deadline regardless of how fast the escalation resolves.
• Senior differentiator: framing every step (the re-send, the widened ask, the escalation) around getting unblocked, not around who's at fault, so the relationship with the owning team survives the deadline pressure.