Spotify Data Analyst Interview Preparation Guide - Entry Level

Conditional probability updates a belief about an event given new evidence; Bayes' theorem does this formally by combining the detector's known accuracy with how rare fraud actually is. Even with a 98% true positive rate and only a 1% false positive rate, a 0.1% base rate of fraud means most flagged transactions are still false alarms: the posterior probability a flagged transaction is truly fraudulent comes out to about 8.9%.

Structured elaboration

The formula

$P(\text{Fraud}\mid \text{Flag}) = \frac{P(\text{Flag}\mid \text{Fraud})\,P(\text{Fraud})}{P(\text{Flag}\mid \text{Fraud})\,P(\text{Fraud}) + P(\text{Flag}\mid \text{Not Fraud})\,P(\text{Not Fraud})}$

Each term has a name:

• P(Fraud): the prior, the base rate before seeing the flag.
• P(Flag given Fraud): the likelihood, here the true positive rate (sensitivity).
• P(Flag given Not Fraud): the false positive rate.
• The denominator, P(Flag), is the total probability of a flag from either a truly fraudulent or a truly legitimate transaction. This is what "explains away" most of the apparent accuracy.

Why intuition misleads here (base-rate neglect)

It is tempting to read "98% true positive rate" as "a flag is 98% likely to be fraud." That conflates P(Flag given Fraud) with P(Fraud given Flag), which are different conditional probabilities. When the event being detected is rare, even a small false positive rate produces far more false alarms than true ones in absolute terms, because there are so many more legitimate transactions to draw false positives from.

Worked example

$P(\text{Fraud}\mid \text{Flag}) = \frac{0.98 \times 0.001}{0.98 \times 0.001 + 0.01 \times 0.999} = \frac{0.00098}{0.01097} \approx 0.0893$

So about 8.9% of flagged transactions are actually fraudulent; roughly 91% are false alarms (computed directly from the formula above, no simulation needed).

Implications for the decision threshold: the false positive rate drives the posterior almost as much as the base rate does, because it applies to the much larger pool of legitimate transactions. Holding the true positive rate fixed at 98% and varying only the false positive rate:

(each row computed from the same formula with only the false positive rate changed; verified with python3)

Trade-offs & pitfalls

• A model can have excellent sensitivity and still produce a low-precision alert stream when the target event is rare; sensitivity alone is not enough to judge a detector meant for a rare class.
• Tightening the threshold to shrink the false positive rate raises the posterior (fewer false alarms per true one), but it comes at the cost of missing more true fraud (lower true positive rate); the two move in opposite directions.
• Operationally, an 8.9% posterior does not mean "ignore the flag." It means route flagged transactions to a cheaper secondary check (rules, a human reviewer, step-up authentication) rather than an automatic block, so the cost of a false positive stays low.
• Base rates drift over time as fraud patterns and volumes change; a posterior computed once goes stale if the prior is not periodically re-estimated.

Direct answer

Don't start pulling data. Spend the first few minutes clarifying exactly what "freshest usage data" means to the requester, quickly check what's actually feasible in the time available, then offer a concrete option with an honest trade-off (what you can deliver, by when, and what limitations it has) instead of either refusing or silently guessing.

Structured elaboration

1. Clarify fast, in a few targeted questions. Which metric or entity, exactly? What time range and freshness actually matters for the demo (last five minutes, last hour, end of day)? What format and how many rows? Who's the audience? Two minutes here avoids delivering the wrong thing under time pressure.
2. Check feasibility before promising anything. Is there already a near-real-time source (a stream, a recent materialized view, meaning a pre-computed, saved query result that refreshes on a schedule so it's faster to read than running the full query live), or does this require querying the warehouse directly? A quick look at pipeline health tells you what's realistically possible in two hours.
3. Offer real alternatives with honest trade-offs, rather than a flat yes or no: a slightly-stale snapshot delivered fast, a live dashboard with a visible "data as of" timestamp, or a small representative sample if a full extract isn't feasible in time.
4. Set expectations explicitly and in writing. State exactly what you'll deliver, by when, and what its limitations are, and get an explicit "yes, that works" before you start, so the requester isn't surprised mid-demo.
5. Capture the request in a lightweight ticket or thread (what was asked, what was delivered, why) so a recurring need doesn't turn into repeated fire drills, and so the next urgent ask has a template to follow.

Worked example

A message comes in: "I need the freshest usage data for a demo in two hours." Instead of guessing, the reply is: "Which metric, exactly, active sessions or feature-level usage? And is a snapshot from an hour ago fresh enough, or does it need to be closer to real time?" The answer comes back: feature-level usage, an hour old is fine. A quick check shows the closest fast option is a query against yesterday's partition (a time-sliced chunk of the table, one day's worth of rows) plus this morning's incremental load (just the new rows added since the last full update), deliverable as a CSV in about 30 minutes, versus a true near-real-time pull that would need infrastructure support not available in two hours. The reply to the requester: "I can get you feature usage as of this morning in about 30 minutes. Sub-hour freshness isn't feasible in the time we have. Does the morning snapshot work for the demo?" Only after confirmation does the work start.

Trade-offs & pitfalls

• Pitfall: silently delivering whatever's easiest without confirming it matches what "fresh" means to the requester, then having it fail live in the demo.
• Pitfall: over-promising real-time freshness under pressure and missing the deadline entirely.
• Pitfall: treating the ask as one-off. If this is the third urgent demo-data request this month, that's a signal to build a lightweight self-serve or scheduled export, not just to keep responding faster each time.
• Senior differentiator: naming the trade-off explicitly and getting a quick confirmation before starting, instead of either refusing the ask outright or quietly doing extra unscoped work to make an unrealistic version happen.

Direct answer

A strong answer names two or three concrete responsibilities from the actual job description, not the company's reputation or brand, and shows you've already done close versions of them. State the overlaps directly, then commit to a plausible first-quarter deliverable so the interviewer hears intent, not just interest.

The framework

1. Read the posting for verbs, not adjectives. "Own", "build", "triage", "partner with" tell you what the job actually is. "Fast-paced", "passionate", "innovative" tell you nothing you can map to.
2. Pick two or three responsibilities and pair each with one piece of your own evidence: a project, a specific outcome, a tool you used under real conditions, not just a course you took.
3. Say what you'd do first. A candidate who names a plausible 30-90 day deliverable signals they read the job as work, not as a title.
4. If a responsibility exposes a real gap, name it and pair it with evidence of how fast you close gaps (a similar tool you picked up quickly, a domain you ramped into before). Skipping the gap and hoping it goes unnoticed rarely works; it's usually visible on your resume already.

This same structure compresses into a 60-second "pitch" version of the answer: one sentence per step, evidence first, no preamble about how excited you are.

Worked example

The posting listed [responsibility A, e.g. "own the intake-to-resolution pipeline for X"] and [responsibility B, e.g. "partner with three cross-functional teams on Y"]. In my current role I [did a close variant of A], and separately I [did a close variant of B]. Neither was identical to what this job asks for, which is part of why I want it: I'd start by [a concrete first deliverable, e.g. "auditing the current handoff points between the two teams most affected by the gap"] in the first month.

(Swap the bracketed specifics for your own domain: a Data Engineer cites a pipeline they owned, a Product Designer cites a research-to-ship handoff, a Penetration Tester cites an engagement type they've run before.)

Trade-offs and pitfalls

Reciting the job description back almost verbatim reads as flattery, not evidence, because it proves you can read, not that you've done comparable work.

Direct answer

Averaging independent estimates cancels out uncorrelated errors, so an ensemble's variance shrinks roughly in proportion to how independent its base learners' mistakes are. If the base learners are highly correlated (they tend to make the same mistakes on the same examples), averaging barely helps, because there's nothing uncorrelated left to cancel. Bagging and random forests work by deliberately reducing that correlation, through resampled training data and, for random forests, randomized feature subsets at each split.

Structured elaboration

The variance-reduction mechanism. For $M$ estimators each with variance $\sigma^2$ and pairwise correlation $\rho$ between any two of them, the variance of their average is:

As $M \to \infty$, the first term vanishes but the second converges to $\rho\sigma^2$, a floor set entirely by the correlation between base learners. This is the core intuition: adding more trees keeps shrinking the $\sigma^2/M$ term, but if $\rho$ is large, the ensemble variance plateaus well above zero no matter how many learners you add. Diversity (low $\rho$) is what lets averaging actually pay off.

Why bagging and random forests reduce correlation.

• Bagging trains each base learner on a different bootstrap resample of the training data, so each tree sees a slightly different, overlapping-but-not-identical dataset; their errors become less correlated because they're fit to different noise realizations of the same underlying signal.
• Random forests add a second layer: at each split, only a random subset of features is considered as candidates. This deliberately prevents every tree from all discovering and splitting on the single strongest feature first, which would otherwise make the trees structurally similar (and their errors correlated) even with different bootstrap samples.

Other ways to increase diversity. Varying the base learner's hyperparameters (depth, minimum leaf size) across the ensemble; using different model families entirely (stacking a tree, a linear model, and a neural net); injecting randomness into training (different random seeds, dropout-like row/column sampling); or training on different feature subspaces (the random subspace method). All of these push $\rho$ down, which is where most of the practical ensemble gain comes from once you already have reasonably accurate individual learners.

Worked example

Fix $\sigma^2 = 1$ and $M = 100$ base learners, and compare two correlation regimes:

Going from $\rho=0.30$ to $\rho=0.05$, holding $M$ and $\sigma^2$ fixed, drops the ensemble variance from $0.307$ to $0.0595$, a reduction of about $81\%$, even though the number of trees and each individual tree's own variance didn't change at all. That's the diversity mechanism in isolation: the same $M$, the same per-tree quality, but a five-fold better ensemble outcome purely from decorrelating the errors, which is exactly what random forests' extra feature-subsampling step is designed to do relative to plain bagging.

Trade-offs & pitfalls

• Diversity is not free: features or hyperparameter choices that increase diversity often do so by making individual base learners slightly weaker (e.g., a very small feature subset per split can starve a tree of its best signal). The formula above assumes $\sigma^2$ is held fixed, but in practice pushing $\rho$ down can push $\sigma^2$ up if taken too far, and the net effect on ensemble variance can reverse.
• The floor $\rho\sigma^2$ means that beyond a certain point, adding more trees has rapidly diminishing returns; the marginal value of tree number 500 versus tree number 100 is small once $M$ is already large enough that $\sigma^2/M$ is small relative to $\rho\sigma^2$.
• This formula addresses variance only; it says nothing about bias, so an ensemble of biased base learners stays biased no matter how diverse they are.
• The independence/correlation framing is intuitive but approximate for classification (0/1 loss) rather than squared-error regression; the qualitative conclusion, correlated errors limit ensemble gains, carries over, but the exact algebraic form above is derived for a variance-of-the-mean setting.

Direct answer

A RangeIndex represents a regular integer sequence (start, stop, step) as three numbers, without ever allocating an actual array of n integers; pandas gives a freshly constructed DataFrame one by default because it is virtually free to create and any position-based lookup can be computed arithmetically instead of looked up in memory. The pitfall: as soon as you filter, sort by a value, or merge, the row labels typically stop lining up with row position, so any code that treats .loc[i] and .iloc[i] as interchangeable, or assumes label i still means "the i-th row," can silently return the wrong row or raise a KeyError.

Structured elaboration: what a RangeIndex actually is

pd.RangeIndex(start=0, stop=n, step=1) needs to store only three integers, start, stop, step, regardless of n. Verified: pd.RangeIndex(0, 1_000_000).memory_usage() reports 132 bytes, versus 8,000,000 bytes (8 MB) for pd.Index(np.arange(1_000_000)), a materialized int64 array that allocates 8 bytes per entry. That gap scales linearly, so at 100 million rows a materialized integer index costs roughly 800 MB while a RangeIndex still costs a few hundred bytes.

Because a RangeIndex is defined arithmetically, pandas never has to scan it to answer "what label is at position k," it just computes start + k * step. That is why any DataFrame built without an explicit index gets one by default: it is the cheapest possible index, and for the common case (no natural key, rows are just "the data in order") it is also the most useful one.

The pitfall: once the index is no longer a clean RangeIndex, position and label diverge

import pandas as pd

orders = pd.DataFrame({'order_id': [101, 102, 103, 104, 105], 'amount': [10, 20, 30, 5, 15]})
big_orders = orders[orders['amount'] > 12]
print(big_orders)

Output (verified by running this exact code):

   order_id  amount
1       102      20
2       103      30
4       105      15

The surviving rows keep their original labels, 1, 2, 4, not a fresh 0, 1, 2. big_orders.index is now a plain Index, not a RangeIndex. Code that then does big_orders.loc[0], expecting "the first row of the filtered result," raises KeyError: 0, because label 0 was filtered out; big_orders.iloc[0] (position-based) correctly returns the order_id=102 row instead. Sorting by a value column produces the same effect: orders.sort_values('amount').index comes back as [3, 0, 4, 1, 2], a permutation, not a range, so .loc and .iloc again disagree with each other at every position except by coincidence.

Merging is a related but distinct trap. A default merge always hands back a fresh, clean RangeIndex on the result, so the "is this a RangeIndex" check alone can pass while the deeper assumption still breaks: a one-to-many merge changes the row count, and an inner join can drop non-matching rows, so row 0 of the merged frame is not necessarily row 0 of either input anymore, even though the merged frame's own index looks pristine. A fresh RangeIndex after a merge means "these labels are 0 through n-1," not "these rows are still in the same order as before the merge."

Worked example, the general pattern

import pandas as pd

df = pd.DataFrame({'a': [30, 10, 20, 50, 40]})
sorted_df = df.sort_values('a')
print(type(sorted_df.index), sorted_df.index.tolist())

Verified output: <class 'pandas.Index'> with values [1, 2, 0, 4, 3], a plain Index, not a RangeIndex, and not 0..4 in order. sorted_df.iloc[0] gives the smallest a value (correct, position-based); sorted_df.loc[0] gives the row that was originally at position 0 before sorting (a=30), not the smallest value, because label 0 refers to the original row, not the current position.

Trade-offs and pitfalls

• .loc is always label-based and .iloc is always position-based; they agree only when the index happens to be 0..n-1 in order, which is exactly the situation a fresh RangeIndex guarantees and almost nothing else does. Treat that agreement as a special case, not the default.
• reset_index(drop=True) restores a clean RangeIndex after a filter or sort, which is the right move whenever the original labels carried no meaning you need to keep; if the original labels DO mean something (a natural key, a stable row id you want to keep referring to), resetting them away loses that information.
• Do not assume df.index.values or .to_numpy() is free just because the index started as a RangeIndex, calling either one forces pandas to materialize the full array of positions on the spot, spending the exact memory a RangeIndex exists to avoid.
• The safest habit for code that must not care whether the index is currently a clean range: use .iloc when you mean "the k-th row regardless of label," use .loc when you mean "the row with this specific label," and never assume the two are interchangeable just because they used to agree earlier in the pipeline.

SQL uses three-valued logic: every comparison against NULL (including `NULL = NULL`) evaluates to UNKNOWN, not TRUE or FALSE, so a row is only kept when a condition evaluates to TRUE. That is why `col = NULL` never matches anything, no matter what `col` holds, and why you must use `IS NULL` / `IS NOT NULL`, which are defined to test for NULL directly rather than compare against it.

How NULL propagates

• `col != 'x'` silently drops every row where `col` is NULL, because `NULL != 'x'` is UNKNOWN, and `WHERE` only keeps TRUE rows. A validation query that assumes this filter caught "everything except x" will silently under-count.
• `COALESCE(col, default)` returns the first non-NULL argument, so `COALESCE(country, 'unknown')` turns a NULL into a labeled default without disturbing real values.
• `NULLIF(a, b)` returns NULL when `a = b` and `a` otherwise, useful for turning a sentinel value (like `-1` or `'N/A'`) back into a proper NULL before further processing.
• Aggregates treat NULL differently by design: `COUNT(*)` counts every row regardless of NULLs, `COUNT(column)` counts only non-NULL values in that column, and `SUM`/`AVG` simply ignore NULLs (an `AVG` over one NULL and one 10 is 10, not 5).

Worked example

`COUNT(*)` = 3, `COUNT(amount)` = 2, `SUM(amount)` = 30, `AVG(amount)` = 15 (30 divided by the 2 non-NULL rows, not 3).

Trade-offs and pitfalls

Whether to store missing text as NULL or as an empty string is a warehouse-wide policy decision, not a one-off choice: mixing the two conventions across tables means a null-rate check on one table silently misses the "missingness" that another table represents as `''`. Pick one convention, document it, and have every validation check test for both until legacy data is migrated. A related, often-overlooked pattern is grouping by a nullable column directly: `GROUP BY event_type` will produce a genuine NULL group in most engines, but downstream tools (dashboards, BI exports) frequently render that group as blank or drop it, so it's often safer to wrap it as `COALESCE(event_type, 'UNKNOWN')` before grouping, so the missing-category count stays visible.

Joining through a one-to-many relationship (customer to orders to order_items) means a plain COUNT after the join counts item-rows, not distinct products; COUNT(DISTINCT product_id) is what actually answers "how many different products".

Structured elaboration

SELECT c.customer_id, COUNT(DISTINCT oi.product_id) AS unique_products_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id;

Once you join customers to orders to order_items, each customer can appear once per order-item row, not once per customer. A plain COUNT(*) or COUNT(oi.product_id) after this join counts rows in the joined result, which over-counts if the customer bought the same product more than once, or simply reflects total items purchased rather than distinct products. COUNT(DISTINCT product_id) deduplicates within each customer's group, correctly answering the "how many different products" question. Separately, an outer join can introduce NULL product_ids for customers with no purchases at all; COUNT(DISTINCT ...) already ignores NULLs, so this is handled for free.

Worked example

Given one customer with two orders, one order having two order_items (products 5 and 6) and the other having one order_item (product 5 again): COUNT(oi.product_id) returns 3 (three item-rows total), while COUNT(DISTINCT oi.product_id) correctly returns 2 (products 5 and 6, with the repeat of 5 not double-counted).

Now add a second customer with zero orders at all, reached only via the outer join. For that customer, oi.product_id comes back NULL. Side by side: COUNT(*) counts that NULL row anyway, wrongly showing 1 for a customer who has made no real purchases; COUNT(oi.product_id) correctly returns 0, since COUNT of a column never counts NULLs; and COUNT(DISTINCT oi.product_id) also correctly returns 0, for the same reason. So COUNT(*) is the one form that needs the outer join's NULLs actively guarded against (or avoided by counting a non-nullable column instead), while both COUNT(oi.product_id) and COUNT(DISTINCT oi.product_id) handle them safely without any extra work.

Trade-offs and pitfalls

This is a specific instance of a broader pattern: any COUNT taken after a one-to-many join needs to be checked against what grain the join actually produced, not the grain you're mentally picturing (customers, in this case) has become. COUNT(DISTINCT) is the fix here; the more general fix for other aggregate types (SUM, AVG) is pre-aggregating before joining, covered in the double-counting question.

Direct answer

Treat this as an evidence-gathering exercise for each flagged point, not a blanket rule: check whether the value is physically plausible, whether it correlates with a known legitimate cause (a VIP account, a bulk order, a specific promotion), and whether removing or capping it would change a downstream decision. Only remove a point once you have a specific reason to distrust it, not just because it's far from the rest.

A decision process

1. Check plausibility first. Is the value even possible given the business (a negative quantity, an age of 300)? If so, it's very likely an error, not a legitimate extreme value.
2. Look for corroborating signal. Does the account have a history of similarly large orders? Is there a promotion or bulk-purchase flag active that explains it? A genuine VIP purchase usually has supporting evidence elsewhere in the row or the customer's history; a data-entry error usually doesn't.
3. Consider the downstream use. If you're reporting a sum (total revenue), an error needs fixing but a legitimate large value should stay in. If you're reporting a "typical" value (median order size), a genuine extreme value can often stay in the data untouched, since the median won't be swayed by it anyway.
4. Choose the least destructive fix that solves the actual problem. Removing a row throws away information permanently; winsorizing (capping at a threshold) keeps the row but limits its influence; a log transform reduces the influence of all large values proportionally rather than singling one out. Reach for the most surgical option that addresses what's actually wrong.

Worked example

Say a $50,000 order shows up in a dataset where the 99th percentile is $1,200. Checking the account: it belongs to a corporate customer with three other orders over $30,000 in the past year, and the order line items list 500 units of the same product, consistent with a bulk purchase. That's a legitimate outlier, kept as-is for a sum, and probably fine to leave untouched for a median too since it won't move it much. Contrast that with a second $50,000 order from a first-time individual customer buying a single unit of a $12 product: no supporting evidence, and a quick check shows the quantity field was likely fat-fingered as 4,000 instead of 4. That one gets corrected (or excluded and flagged), not because it's large, but because there's a specific, checkable reason to distrust it.

Trade-offs and pitfalls

The temptation to "just remove outliers because they skew the numbers" is a real trap: skewing the numbers is sometimes the entire point, if the numbers are genuinely skewed. Document whatever decision you make and why, since a silent removal is invisible to anyone auditing the analysis later, and a wrong call here (removing real revenue, or keeping a fat-fingered order) directly changes a business number.

Direct answer

When customer impact is accumulating, I split effort in a specific order: first look for a mitigation that does not require understanding the unfamiliar tool at all, rolling back, failing over, disabling the feature, because that buys time without betting the fix on knowledge I do not have yet. Only after impact is controlled do I spend real time learning the tool, narrowly focused on confirming the mitigation is safe and understanding what actually happened, and once the site is healthy I close the knowledge gap properly rather than letting the next incident start from the same zero.

Structured elaboration

• Default to reversible, understanding-independent mitigations first: roll back the last change, fail over to a known-good path, disable the feature flag, before attempting a fix that requires trusting a mental model built in the last thirty minutes.
• If no clean mitigation exists, learn the smallest possible slice of the tool needed to act safely, what this specific alert or error means, and what the safest reversible action available is, not the whole system.
• Pull in whoever actually knows the tool immediately, in parallel with your own triage, rather than as a last resort; the goal is not stalling on your own unfamiliarity while someone who could shortcut it is reachable.
• Communicate honestly while still uncertain: state what is known, what is being tried, and what is still unknown, rather than implying more confidence than actually exists.
• Once the site is healthy, close the gap deliberately: understand what actually happened well enough to explain it, and write down what would help the next person, including a future version of yourself, not start from zero.

Worked example

On call, an alert fires for a service built on a message broker configuration I had never operated, and error rates are climbing on a customer-facing path. First move: check whether the last deploy touching that service can be rolled back, since that requires no understanding of the broker at all, just the deploy pipeline I already know well. It can, and error rates start dropping within minutes, before I have understood the broker's internals at all. While that mitigation lands, I pull in a teammate who has used this broker before, in parallel rather than after struggling alone, and ask specifically what the alerting metric means. It turns out a consumer group had fallen behind and the broker started dropping messages under a backpressure policy I did not know existed. I post an honest update to the incident channel: mitigation applied, error rate recovering, root cause still being confirmed, not yet certain it is fully resolved. Once healthy, I spend time properly understanding that backpressure policy, since it is exactly the kind of thing that will bite someone again, and I write a short note pointing at where to look first next time.

Trade-offs and pitfalls

• Trying to diagnose and fix the unfamiliar tool directly, before attempting an understanding-independent mitigation, risks extending customer impact while a mental model is still being built under pressure.
• Pulling in an expert too late, after struggling alone to look self-sufficient, wastes exactly the time that is most valuable during active impact.
• Overstating confidence in an incident update to sound more in control than you are erodes trust worse than admitting uncertainty; stakeholders can tolerate "still investigating," not being told it is fixed when it is not.

Direct answer

Maintain the cumulative metric as an incrementally-updated table keyed on the entity (for example, per user), where each run only touches the partitions affected by NEW or CORRECTED data since the last run, using a merge/upsert rather than recomputing the running total from the full history every time.

Structured elaboration

• Table design: partition (or cluster) the maintained cumulative table by the entity key and, if the source data itself is naturally date-partitioned, by date as well, so a given run only needs to read the partitions that actually changed.
• Merge/upsert pattern: for each entity affected by new data since the last run, recompute that entity's cumulative value by adding the new increment to its PREVIOUSLY STORED cumulative value, rather than re-summing the entity's entire history. This turns an $O(\text{full history})$ recompute into an $O(\text{new data})$ update.
• Handling corrections to historical partitions: if a correction lands against data from months ago (a late refund, a corrected transaction amount), the incremental approach above breaks, because it assumed only NEW increments need to be added, not that OLD increments might change. The fix: when a correction is detected, recompute the cumulative value starting from the corrected partition forward, not just apply the delta at the current date, since every day's running total downstream of the correction is now stale. This is more expensive than a routine incremental update but still far cheaper than a full history recompute, since it only replays forward from the earliest affected date rather than from the beginning of time.
• Detecting that a correction happened at all: usually requires either an explicit "corrected records" feed from the source system, or a reconciliation check comparing a freshly-computed total for an old period against the currently stored one and flagging a mismatch.

Worked example

Consider a per-user cumulative revenue table maintained incrementally. On a normal day, a new $50 order for user 42 arrives; the maintained pipeline reads user 42's current stored cumulative value (say $1,200), adds $50, and writes back $1,250, touching only that one row. Contrast this with a correction: a $30 refund is discovered against an order from 60 days ago for the same user. A pure "add the new increment" pipeline would incorrectly just subtract $30 from TODAY's cumulative value, leaving every day BETWEEN the refunded order and today reporting a cumulative total that is $30 too high (since the running total at each of those intermediate dates was itself computed by summing everything up to that point, and none of those historical days ever saw the correction). The correct handling replays the cumulative sum forward starting from the date of the original order 60 days ago, correcting that day's cumulative total and every day after it through today, using the corrected order-level data as the source of truth.

Trade-offs & pitfalls

• The single biggest defect in a naive incremental design is exactly the correction case above: an interviewer probing this question is very likely testing whether the candidate's design silently mishandles a correction to old data, not just whether they know how to add up numbers.
• Detecting corrections is often the harder engineering problem than applying them, especially against a source system that doesn't clearly flag which historical records changed; a reconciliation job comparing recomputed vs. stored totals on a sample of old partitions is a common pragmatic mitigation.
• Replaying forward from a correction point is still bounded work (from the correction date to today), not unbounded, which is the entire point of this design relative to a full nightly recompute; but it does mean a correction to very old data is proportionally more expensive to apply than one to recent data.