DoorDash Data Scientist Interview Preparation Guide - Mid Level

Goal: produce an offline protocol that yields unbiased (or corrected) estimates of ranking metrics from click logs affected by position bias.

1. Setup and basic idea

• Clicks c_i on item at position i are influenced by relevance r and exposure e_i (position bias). Naïve CTR/ranking metrics are biased toward top positions.
• Use Inverse Propensity Scoring (IPS) to reweight observed clicks by 1/propensity to correct for exposure differences.

2. IPS estimator

• For a binary click signal, estimate expected utility of a policy π (re-ranking) from logged policy π0:
  IPS(π) = (1/N) Σ_{t, i} w_{t,i} · c_{t,i}, where w_{t,i} = I[item_i under π would be shown at pos j] · (π(pos=j | context,item)/π0(pos=j | context,item)) often simplified to 1 / propensity_{t,j} if evaluating deterministic re-rankings.
• For ranking metrics like DCG (Discounted Cumulative Gain: a ranking-quality score that gives more credit to relevant items placed higher in the list, discounted the further down the list they appear), compute weighted gain: weighted_DCG = Σ_j (gain(item_j) * discount(j) * I[click observed]) / propensity(j).

Worked example. Take a ranked list of 3 items at positions 1, 2, and 3 with estimated exposure propensities 0.9, 0.5, and 0.2 respectively. Suppose only the item at position 2 got a click (gain=1). Using the standard DCG discount(j) = 1/log2(j+1), discount(2) = 1/log2(3) ≈ 0.631. The naive (unweighted) contribution of this click to DCG would be gaindiscount(2) = 10.631 = 0.631. The IPS-weighted contribution divides that by the item's exposure propensity: 0.631 / 0.5 ≈ 1.262, roughly double the naive value, because a click at a position with only a 50% chance of being seen is scaled up to compensate for the other similar impressions that never got the chance to generate a click at all. A click at position 3 (propensity 0.2) would be inflated even more, by a factor of 1/0.2 = 5, showing concretely how reweighting corrects for lower-exposure positions being systematically under-counted by a naive click-based ranking metric.

3. Propensity estimation

• Best: instrument randomized exposure (shuffle or position-randomization) to directly measure propensity p(j | context). If not possible, estimate via click models:
  • Position-Based Model (PBM): P(click|item, pos) = α_item * β_pos. Fit β_pos by likelihood / EM.
  • Logistic regression / IPS with features: model propensity as function of pos and context.
• Validate propensities (e.g., holdout randomization checks).

4. Variance and bias trade-offs: practical techniques

• IPS is unbiased if propensities correct, but high variance when propensities are small (top-heavy). Mitigations:
  • Clipping: replace weights w = min(w, w_max) reduces variance at cost of bias.
  • Self-normalized IPS (SNIPS): SNIPS = Σ w·y / Σ w reduces variance and stabilizes.
  • Doubly Robust (DR) estimators: combine a direct model μ(x) for reward with IPS: DR = μ + (w)(y - μ): lower variance and remains unbiased if either model or propensities correct.
  • Stratify by propensity or use control variates.
• Practical choices: collect some randomized impressions to get reliable propensities; use clipping/winsorizing and DR for production evaluation.

5. Interleaving (complementary technique)

• Interleaving compares two rankers by combining their lists into a single impression and attributing clicks to rankers. In online/offline logged randomized settings, interleaving yields low-variance pairwise comparisons without full propensity modeling. Offline interleaving requires logs with per-item exposure randomness or logged pairwise assignments.
• Use interleaving for quick A/B-like comparisons; use IPS/DR for absolute metric estimation.

6. Protocol checklist

• Instrument small amount of randomization to estimate propensities.
• Fit PBM or context-aware propensity model; validate.
• Use SNIPS/DR and clipping to control variance.
• Report confidence intervals (bootstrap or analytic variance for IPS).
• For pairwise decisions, use interleaving when feasible.

This yields principled, practical offline evaluation that balances unbiasedness and variance for ranking with position bias.

Direct answer: Filter, wrapper, and embedded methods are the three families of feature selection, and they differ in whether they look at the model at all: filters score features independently of any model, wrappers repeatedly train a model to search for a good subset, and embedded methods get selection "for free" as a side effect of fitting a model that has built-in sparsity or importance.

Structured elaboration:

• Filter methods score each feature against the target using a statistic (chi-squared for categorical-vs-categorical association, mutual information for general dependence, a simple univariate test) and rank or threshold on that score, entirely independent of any downstream model. They're cheap and model-agnostic, but they ignore feature interactions and redundancy between features.
• Wrapper methods (recursive feature elimination, forward or backward sequential selection) repeatedly fit a real model on candidate subsets and use its performance to guide the search. They can capture interactions filters miss, but cost grows with the number of model fits, which becomes expensive fast as the candidate pool grows.
• Embedded methods (L1/Lasso-induced sparsity, tree-based split importance) build selection into the model-fitting process itself, so you pay for one training run and get a ranked or sparsified feature set as a byproduct. They're a good middle ground on cost, but the selection is tied to that specific model family's inductive bias.

Worked example: With 5,000 candidate features and a model that takes a minute to fit, running a naive wrapper search over all subsets is computationally infeasible. A practical pipeline runs a cheap filter first (drop anything with near-zero variance or negligible mutual information with the target) to cut the pool to a few hundred, then applies an embedded method (L1-regularized logistic regression, or a tree ensemble's importance) to get down to a production-sized set, reserving a full wrapper search (if used at all) for a final, much smaller candidate list.

Trade-offs and pitfalls: A common naive-application failure mode is applying any of these blindly without accounting for correlated features: a filter method can rank two highly-correlated features as equally important and keep both redundantly, while a wrapper or embedded method fit on a single split can be unstable (a different train/validation split selects a different subset). Cross-validating the selection step, not just the model, protects against overstating how stable and reproducible the chosen features actually are.

Direct answer

A data-quality rule documentation template needs, at minimum: a plain-language description, the detection logic (the actual query or code implementing the check), severity, an owner, remediation steps, and worked examples of both a passing and a failing record, stored somewhere discoverable and versioned so it stays accurate as the underlying pipeline evolves.

Structured elaboration

• Description: a plain-language statement of what the rule checks and why it matters, written for someone who is not the rule's author.
• Detection logic: the literal, runnable check (SQL, a framework expectation, a script), not a paraphrase of it, so anyone can verify exactly what is being tested rather than relying on the description alone, which can drift out of sync with the actual implementation.
• Severity and owner: an explicit escalation path, who gets paged, and how urgently, when this rule fails.
• Remediation steps: what to actually do when the rule fires, not just what the rule detects; a rule with no documented remediation path is only half-useful.
• Worked examples: a concrete passing record and a concrete failing record, since an abstract description of "what counts as a violation" is often ambiguous at the edges in a way a real example resolves immediately.
• Discoverability and staying current: store the template alongside the pipeline code itself (versioned in the same repository) rather than in a separate wiki that drifts out of sync, and treat a change to the detection logic as requiring an update to the documentation in the same code review, not as a separate, easily-forgotten step.

Worked example

A rule for "duplicate order detection": description = "flags orders with the same (customer_id, order_total, order_date) appearing more than once, which usually indicates a retry-induced duplicate insert rather than a genuine repeat purchase"; detection logic, inlined rather than just referenced, so the documentation and the runnable check are the same artifact:

WITH ranked AS (
  SELECT order_id, customer_id, order_total, order_date, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id, order_total, order_date
           ORDER BY created_at
         ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn > 1;

Every row this returns has rn > 1, meaning it is not the first-created row within its (customer_id, order_total, order_date) group, so it is flagged as a probable duplicate rather than the original; severity = medium, does not block the pipeline, routes to a daily digest rather than paging; owner = the orders-pipeline team; remediation = "confirm via the source system's request ID whether this is a genuine retry duplicate, and if so, mark the later record for exclusion in the next reconciliation pass"; worked examples = one clearly-duplicate pair and one legitimate same-day repeat purchase that should NOT be flagged, illustrating exactly where the boundary of the rule lies.

Trade-offs and pitfalls

The template's biggest practical risk is documentation drift: a rule's underlying SQL gets updated during an incident fix, but the corresponding documentation is not, and six months later the documented detection logic no longer matches what actually runs, misleading whoever reads it next. Requiring the documentation update as part of the same pull request that changes the detection logic, enforced by convention or by a lightweight CI check, is what actually keeps the two in sync rather than relying on someone remembering to update a separate wiki page.

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

"MRR as of a date, prorating partial months" has two legitimate but different conventions, and the definition needs pinning before writing SQL. This answer uses month-to-date proration: an active subscription contributes monthly_price x (days elapsed so far in the calendar month containing as_of_date) / (days in that month), capped at as_of_date and floored at the later of the month's start or the subscription's own start date. The alternative convention, full run-rate (count the full monthly price the moment a subscription exists, prorating only for accounting revenue recognition, not the headline MRR figure) is common at many SaaS companies and gives a different number for any subscription that started mid-month; state clearly which one you're using.

Structured elaboration

Approach:

1. Filter to subscriptions active on as_of_date: start_date <= as_of_date AND (end_date IS NULL OR end_date >= as_of_date).
2. For each, compute the overlap window within the calendar month containing as_of_date: overlap_start = GREATEST(start_date, month_start), overlap_cap = LEAST(month_end, as_of_date).
3. Prorate: monthly_price_cents * (overlap_cap - overlap_start + 1) / days_in_month, summed across active subscriptions.

Key points:

• days_in_month must come from the actual calendar month (DATE_TRUNC plus one month minus a day), not a hardcoded 28-31; getting this wrong systematically skews every partial-month subscription in the same direction.
• Keep the whole calculation in integer cents through the arithmetic, converting to dollars only at final display, to avoid compounding floating-point rounding across many subscriptions.

Worked example

Seed data, as_of_date = 2026-03-15 (March has 31 days):

Point-in-time prorated MRR:

WITH params AS (
  SELECT
    DATE '2026-03-15' AS as_of_date,
    DATE_TRUNC('month', DATE '2026-03-15')::DATE AS month_start,
    (DATE_TRUNC('month', DATE '2026-03-15')::DATE + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end
),
active_subs AS (
  SELECT s.subscription_id, s.customer_id, s.start_date, s.monthly_price_cents, p.*
  FROM subscriptions s
  CROSS JOIN params p
  WHERE s.start_date <= p.as_of_date
    AND (s.end_date IS NULL OR s.end_date >= p.as_of_date)
),
calc AS (
  SELECT
    subscription_id, customer_id, monthly_price_cents,
    GREATEST(start_date, month_start) AS overlap_start,
    LEAST(month_end, as_of_date) AS overlap_cap,
    (month_end - month_start + 1) AS days_in_month
  FROM active_subs
)
SELECT
  subscription_id, customer_id, monthly_price_cents, overlap_start, overlap_cap,
  (overlap_cap - overlap_start + 1) AS active_days_in_month,
  days_in_month,
  ROUND(monthly_price_cents * (overlap_cap - overlap_start + 1)::DOUBLE / days_in_month, 2) AS prorated_mrr_cents
FROM calc
ORDER BY subscription_id;

Result (run in DuckDB; T-SQL/Postgres users would use DATEADD/EOMONTH or date_trunc equivalents, the arithmetic is identical):

subscription_id | customer_id | monthly_price_cents | overlap_start | overlap_cap | active_days_in_month | days_in_month | prorated_mrr_cents
----------------+-------------+----------------------+---------------+-------------+-----------------------+----------------+---------------------
              1 |         101 |                 5000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |             2419.35
              2 |         102 |                 3000 |    2026-03-10 |  2026-03-15 |                     6 |             31 |              580.65
              5 |         104 |                 6000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |             2903.23
              6 |         105 |                 1000 |    2026-03-01 |  2026-03-15 |                    15 |             31 |              483.87

Subscriptions 3 and 4 correctly don't appear, they aren't active on the as_of_date (subscription 3 churned March 5; subscription 4 was superseded February 28). Summed and converted to dollars: $63.87 total prorated MRR as of March 15.

Extension: the new / expansion / churned MRR bridge

A point-in-time figure alone doesn't say WHY MRR is moving. The standard SaaS decomposition classifies subscription-level events within the month using each subscription's FULL monthly price (a different, non-prorated convention from the point-in-time figure above; the two must never be added together or expected to reconcile directly).

Detecting continuity (an upgrade, not a churn-then-new pair) uses LAG() per customer to compare each subscription to that same customer's immediately prior one, treating it as a continuation only when the previous subscription's end_date is exactly one day before the new one's start_date:

WITH ordered AS (
  SELECT
    subscription_id, customer_id, start_date, end_date, monthly_price_cents,
    LAG(monthly_price_cents) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_price_cents,
    LAG(end_date) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_end_date,
    LEAD(start_date) OVER (PARTITION BY customer_id ORDER BY start_date) AS next_start_date
  FROM subscriptions
),
classified AS (
  SELECT
    subscription_id, customer_id, start_date, end_date, monthly_price_cents, prev_price_cents,
    CASE
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31' AND prev_price_cents IS NULL THEN 'new'
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND prev_end_date = start_date - INTERVAL 1 DAY AND monthly_price_cents > prev_price_cents THEN 'expansion'
      WHEN start_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND prev_end_date = start_date - INTERVAL 1 DAY AND monthly_price_cents < prev_price_cents THEN 'contraction'
      ELSE NULL
    END AS start_event,
    CASE
      WHEN end_date BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
           AND (next_start_date IS NULL OR next_start_date <> end_date + INTERVAL 1 DAY) THEN 'churned'
      ELSE NULL
    END AS end_event
  FROM ordered
)
SELECT 'new' AS event_type, subscription_id, customer_id, monthly_price_cents AS mrr_delta_cents
FROM classified WHERE start_event = 'new'
UNION ALL
SELECT 'expansion', subscription_id, customer_id, (monthly_price_cents - prev_price_cents)
FROM classified WHERE start_event = 'expansion'
UNION ALL
SELECT 'contraction', subscription_id, customer_id, (monthly_price_cents - prev_price_cents)
FROM classified WHERE start_event = 'contraction'
UNION ALL
SELECT 'churned', subscription_id, customer_id, -monthly_price_cents
FROM classified WHERE end_event = 'churned'
ORDER BY event_type, subscription_id;

Result:

event_type | subscription_id | customer_id | mrr_delta_cents
-----------+------------------+-------------+-----------------
churned    |                3 |         103 |           -2000
expansion  |                5 |         104 |            2000
new        |                2 |         102 |            3000

Customer 104's plan swap is correctly classified as expansion (+$20), not as a churn-then-new pair, because subscription 4's end_date (Feb 28) is exactly one day before subscription 5's start_date (Mar 1), the continuity condition. Customer 103's subscription (subscription 3) is churned (-$20) because nothing replaces it. Customer 102 is genuinely new (+$30), a first subscription with no prior one for that customer.

Net MRR movement for March: SUM(mrr_delta_cents) = 3000 cents, +$30.00 net new MRR for the month.

Complexity

The point-in-time query is a single filter and scalar computation per active subscription, O(n). The bridge adds two window-function passes (LAG/LEAD partitioned by customer), O(n log n) for the per-customer sort, then a constant number of UNION ALL branches over the already-classified rows. Both are cheap at any realistic subscription-table size; the only real cost driver is whether customer_id and start_date are indexed for the partition/sort.

Edge cases

• A subscription active exactly on as_of_date with start_date = as_of_date: overlap_start = overlap_cap = as_of_date, one active day, correctly prorated to a single day's worth of the monthly price, not zero.
• Leap years and variable month lengths: days_in_month is computed from the actual calendar month, not hardcoded, so February in a leap year prorates correctly against 29 days.
• A customer with two subscriptions active at once (e.g., a base plan plus a separately-billed add-on) breaks the "one active subscription per customer" assumption this bridge logic relies on; each subscription line needs to be classified independently rather than assuming a single prior subscription per customer.
• The one-day-gap continuity rule is a specific assumption about this business's billing semantics (old plan ends the day before the new one starts). A business whose old and new subscriptions overlap by a day, or cut over same-day, needs that condition adjusted to match its actual data model; state the assumption explicitly whenever this query ships.

Trade-offs & pitfalls

• The point-in-time prorated MRR ($63.87) and the bridge's net movement (+$30.00) are two different, non-reconcilable numbers built on different conventions (prorated vs full monthly price). Presenting them on the same dashboard without labeling which is which invites a stakeholder to try to reconcile them and conclude the numbers are broken.
• The continuity/gap detection is a judgment call encoded in SQL, not a SQL universal. Every business's billing system handles plan swaps slightly differently; verify the actual gap convention against real data before trusting the classification, rather than assuming the one-day rule used here transfers unchanged.
• This model assumes one active subscription per customer at a time. Multi-subscription accounts need every subscription LINE classified on its own, or a genuine new add-on gets misclassified as an expansion of an unrelated existing plan.
• Keep monetary values in integer cents through every intermediate step; converting to floating-point dollars early and summing many rows is a classic source of cents-level drift that shows up as "why doesn't this tie out to the penny" in a finance review.

Direct answer

Map the OKR (objective and key results) to one KPI (key performance indicator) per level of the organization so each team owns a number it can actually move: a company-level outcome metric, a product-level conversion metric that is the biggest lever on that outcome, and a feature-level metric tied to a specific launch or experiment. Each KPI needs its own cadence, owner, and an explicit pass or fail threshold, an acceptance criterion in the same spirit as an OKR's key results, so a status of "on track" or "off track" is unambiguous rather than a matter of interpretation.

Structured elaboration

Framing each KPI with an explicit acceptance threshold, rather than only a direction such as "up is good," is what turns a KPI tree into something a status report can actually score, the same acceptance-criteria discipline a support team would use to phrase a key result like "resolve 90% of tickets within SLA" rather than just "improve resolution time."

Worked example

Baseline paid subscribers: 100,000. The OKR asks for 30% growth:
$100{,}000 \times 1.30 = 130{,}000$
Net subscribers needed:
$130{,}000 - 100{,}000 = 30{,}000$

Assume paid-base churn runs at roughly 2% per month; over 12 months, as a simplified, non-compounding planning estimate rather than a forecast:
$2\% \times 100{,}000 \times 12 = 24{,}000 \text{ gross losses}$

Gross new paid subscribers needed for the year:
$30{,}000 + 24{,}000 = 54{,}000$
Average monthly pace needed:
$54{,}000 / 12 = 4{,}500 \text{ per month}$

If the current free-tier MAU (monthly active users) is 150,000 and the current free-to-paid conversion rate is 2% per month:
$150{,}000 \times 0.02 = 3{,}000 \text{ new paid per month}$
That is short of the 4,500 needed. Closing the gap at the current free-tier size requires the conversion rate to rise to:
$4{,}500 / 150{,}000 = 3\%$
a one-percentage-point lift, which is exactly the number the product-level KPI needs to hit, and which a feature-level trial-conversion improvement, the third KPI, is one lever for achieving.

Trade-offs & pitfalls

• Setting the product-level KPI target without running this kind of top-down arithmetic risks a target that's either trivially easy or quietly impossible relative to what the company number requires.
• A feature-level KPI that improves in isolation, trial conversion up, does not guarantee the company number moves if the feature only reaches a small fraction of the free base; check the feature's reach, not just its own conversion rate.
• Acceptance criteria that are too lenient at every level can all show "on track" while the company-level number still misses, since rounding and timing lags compound going up the tree; sanity-check the tree bottom-up periodically, not just top-down at target-setting time.
• Multiple teams each optimizing their own KPI in isolation can create conflicting incentives, for example a feature team pushing hard trial pressure that hurts the product-level conversion quality; a shared guardrail metric, such as support ticket volume or churn, helps catch this.

What I would look at
Spill to disk usually means the engine ran out of memory for a large sort or window step. Since the query has several window functions, I would inspect whether they share the same PARTITION BY and ORDER BY clauses. If not, the engine may sort the same billion-row fact table multiple times.

Tuning steps

• Keep only the columns needed before the window step.
• Filter as early as possible, especially on date ranges.
• Align window definitions so the engine can reuse one sort.
• Pre-aggregate to the lowest grain that still preserves the result.
• Check table clustering or partitioning on the hottest filters, such as day.

Example
If one window is over (customer_id, day) and another is over (customer_id, country, day), I would ask whether the business result really needs both raw row-level windows. If a daily summary is enough, I would stage one row per customer, country, and day first, then run the windows on that smaller set.

Why this helps
You are not changing the answer, just reducing the amount of data that must be sorted and carried through each window calculation. That is usually the fastest way to stop spills.

Direct answer

A naive rolling z-score (mean and standard deviation over the trailing N days, current day included or not) breaks down whenever the metric has weekly seasonality, because it compares a day to a mixed population of weekdays and weekends rather than to its own kind of day. The fix is to compute the baseline per day-of-week: compare a Monday only to prior Mondays.

Structured elaboration

• Naive approach: AVG/STDDEV_SAMP over ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, ordered by date, then z = (value - roll_mean) / roll_std.
• Why it breaks: a 7-day window spanning both weekday and weekend values estimates a standard deviation that reflects the SIZE of the seasonal swing, not genuine day-to-day noise. That inflated standard deviation does two things at once: it makes structurally-low weekend days score a moderate, harmless-looking negative z (a false signal of concern where none exists), and it makes a real anomaly on a weekday look small relative to that inflated spread (a missed true anomaly).
• Fix: partition the window by day-of-week, ordered by date, so each day is compared only to its own recent history of the same weekday: PARTITION BY EXTRACT(dow FROM day) ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING.
• Remaining pitfalls even after the fix: a day-of-week baseline still needs several prior instances of that weekday before its own standard deviation is meaningful (same minimum-history guard as any rolling statistic), and it will not by itself catch a genuine change that happens to be a full week long, since day-of-week baselines are blind to same-weekday drift over a multi-week span.

Worked example

Five weeks of a metric with clean weekly seasonality (weekdays around 100-104, weekends around 40-42) and ONE planted real anomaly: a Friday reading of 130 where every other Friday in the series was 100-104. Concretely:

Naive rolling 7-day z-score on that Friday:

SELECT day, metric_value,
  AVG(metric_value) OVER w AS roll_mean,
  STDDEV_SAMP(metric_value) OVER w AS roll_std,
  (metric_value - AVG(metric_value) OVER w) / STDDEV_SAMP(metric_value) OVER w AS z
FROM daily_metric
WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

gives roll_mean = 89.00, roll_std = 33.98, z = 1.21 -- computed directly from the trailing 7 calendar days ending on that Friday (week 4's Sat, Sun: 41, 42, then week 5's Mon-Fri: 104, 102, 103, 101, 130): mean = (41+42+104+102+103+101+130)/7 = 623/7 = 89.00, and the sample standard deviation of those same 7 values is 33.98, comfortably under any reasonable alert threshold. The genuine 25-30% spike is missed, because the naive window's standard deviation is inflated to 33.98 by mixing weekday and weekend values.

Day-of-week-adjusted baseline on the identical Friday:

SELECT day, metric_value,
  AVG(metric_value) OVER w AS dow_mean,
  STDDEV_SAMP(metric_value) OVER w AS dow_std,
  (metric_value - AVG(metric_value) OVER w) / STDDEV_SAMP(metric_value) OVER w AS z_dow
FROM daily_metric
WINDOW w AS (PARTITION BY dayofweek(day) ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)

gives dow_mean = 102.00, dow_std = 1.826, z_dow = 15.34 -- from the prior 4 Fridays in the table (weeks 1-4: 101, 103, 100, 104): mean = 408/4 = 102.00, sample standard deviation 1.826, and z_dow = (130 - 102.00) / 1.826 = 15.34. Comparing only to prior Fridays gives a tight, honest standard deviation and correctly flags this as a dramatic outlier. On week 5's Saturday (41), an ordinary non-anomalous weekend day in the same table: the naive method's trailing 7-day window shifted one day later ([42, 104, 102, 103, 101, 130, 41], the same mean 89.00 and std 33.98 as above) reports z = (41 - 89.00) / 33.98, which works out to roughly -1.41 (looks like a mild, spurious negative signal), while the day-of-week method, using the prior 4 Saturdays (41, 40, 42, 41, mean 41.00, std 0.816), reports z_dow = (41 - 41.00) / 0.816 = 0.00 exactly (correctly recognizing that value as completely normal for that day type).

Trade-offs & pitfalls

• The naive method is not just "less accurate", it fails in both directions at once: false positives on structurally low days, false negatives on real anomalies, because both failures share the same root cause (an inflated, conflated standard deviation).
• Day-of-week baselining costs sample size: with only 5 weeks of history, the day-of-week window has just 4 prior points, which is a thin standard deviation estimate. In practice you want at least 6-8 weeks of history before trusting this baseline, and you should still apply a minimum-history guard exactly as in a simpler per-entity outlier check.
• Other seasonality shapes (monthly billing cycles, holiday effects) need the equivalent treatment: partition by whatever unit of seasonality actually governs the metric, not just day-of-week by default.

Direct answer

Keep the refactor from becoming a blocker by freezing the current feature-engineering interface as a stable contract dependents can build against, giving them an explicit SLA for how long that contract holds, and defining upfront the conditions that would make you pause the refactor to fix a break versus accelerate it into production.

Structured elaboration

• Short-term mitigation: don't ask dependent teams to wait on the refactor's completion. Stand up a thin adapter or shim in front of the in-progress refactor so the old interface keeps working unchanged while the internals change underneath it. Dependent teams keep shipping against the interface they already know.
• Contractual SLA: put the interface freeze in writing with a specific duration and a change-notice window, for example "the current feature-engineering interface will not change behavior for the length of the refactor; any unavoidable breaking change gets at least two weeks' notice and a migration guide." This turns an implicit assumption into something dependents can plan launches around.
• Gating criteria to pause the refactor: define a concrete trigger before you start, not in the moment of pressure, for example "if a refactor-introduced defect blocks a dependent team's launch for more than 3 days, refactor work pauses and the team fixes that defect first." Without a pre-agreed trigger, every dispute becomes a negotiation.
• Gating criteria to accelerate: define what "ready early" looks like, for example running the new and old feature pipelines in parallel, in shadow mode, on real traffic, and requiring a set number of consecutive clean days with matching output before promoting a piece of the refactor ahead of schedule, so acceleration is evidence-based rather than optimism-based.

Worked example

The refactor is scoped at 8 weeks. Three product launches are scheduled during that window, at week 2, week 5, and week 7. All three build against the frozen interface and the adapter layer, so none of them wait on the refactor itself. The written SLA promises the interface is stable for the full 8 weeks, with 2 weeks' notice before any change. In week 4, the week-2 launch's team reports a subtle numeric difference in one feature; the fix takes 2 days, under the 3-day pause threshold, so work continues without a full pause, and the fix ships inside the existing schedule. In week 6, the new pipeline has run in shadow mode against production traffic for 5 consecutive days with output matching the old pipeline on every checked feature, meeting the pre-agreed acceleration bar, so that piece is promoted to production a full 2 weeks ahead of the original 8-week completion estimate.

Trade-offs and pitfalls

The biggest trap is treating the frozen interface as free, when maintaining an adapter under an evolving internal design has a real, ongoing cost that competes with the refactor's own timeline; budget for it explicitly rather than absorbing it as invisible overhead. Another common failure is setting the pause and accelerate thresholds after a real dispute has already started, when whichever side is more anxious in the moment wins the argument instead of the criteria; agree on the numbers before anyone is under pressure. Finally, watch for a refactor that quietly extends its SLA window without a formal re-notice; that's how dependents get burned by a "temporary" interface that was never actually re-confirmed as stable.

Direct answer

I treat a paper as a claim to be tested against my own situation, not a text to summarize. I triage fast to see whether it's even worth deeper investment, then build the smallest thing that could prove or disprove the specific claim against my own data or context, and I judge the result against my own baseline rather than the paper's reported numbers.

Structured elaboration

• Triage before investing real time. I read the summary, the method, and the results first, and ask directly whether this actually applies to my problem, my scale, and my constraints, before going any deeper. Most things that look relevant from the headline don't survive this first pass.
• Decide the reproduction scope on purpose. I'm not obligated to rebuild the whole thing; I pick the smallest slice that actually tests the specific claim I care about, and I'm explicit with myself about what fidelity I'm giving up to get there, such as simplified data or a toy version of the setup, so I don't end up trusting a shortcut more than it deserves.
• Build something that runs, not just a mental summary. A claim only becomes genuinely checkable once it's instantiated against real inputs I control, not just reasoned about on paper.
• Compare against my own baseline, not the source's. The source's own reported baseline was almost certainly measured under different conditions than mine, so the only comparison that actually tells me something is against what I'm currently doing, or would do without this.
• Decide adopt, adapt, or discard from that comparison, and write the verdict down so the next person doesn't have to redo the same triage from zero.

Worked example

I came across a paper proposing a locality-sensitive hashing (LSH) scheme for near-duplicate detection in a large text corpus, claiming it could find duplicates within a fixed similarity threshold at a fraction of the compute cost of the pairwise cosine-similarity comparison our own pipeline already used. The triage pass took maybe twenty minutes: our corpus was a similar order of magnitude to theirs, but their reported numbers came from a dataset of well-formed articles, while a meaningful share of what we processed was short, noisy user-generated text, so I knew going in that a direct comparison to their published numbers wouldn't mean much. I decided the smallest slice worth reproducing was just the hashing-and-banding step the approach relied on, not their full indexing and clustering pipeline, and built a small runnable version of just that against a sample of our own real documents, explicitly accepting that I was skipping their canonicalization preprocessing to keep it fast. I then ran it head to head against our existing pairwise comparison on the same sample, measuring both duplicate pairs found and wall-clock time, rather than comparing to their published numbers, and it matched our existing method's results about ten times faster, but only once I'd widened their suggested hash-band parameters, since their published default missed several near-duplicates that were common in our noisier text. I wrote a short note with the parameter change and the before-and-after timing, and we adopted it as the pipeline's first-pass filter, keeping the slower pairwise comparison as a confirming check on anything it flagged as a near-miss.

Trade-offs and pitfalls

The clearest trap is trusting a paper's reported numbers as if they'd transfer directly to your own situation, when they were almost always measured under different conditions. The same is true of a method's tuned parameters, not just its headline numbers: the published defaults are calibrated for the paper's own data and may need to be re-derived for yours before the comparison is fair. The opposite trap is full-fidelity reproduction of something a day-long scoped test would have been enough to evaluate, which burns real time on a claim that didn't need that much rigor to check. A published venue or well-known authors can also create false authority that skips the validation step entirely, which is exactly the habit this whole approach is meant to guard against.