- Home
- Prep by Job Role
- Data Scientist
- Top 100 Q&A
Top 100 Data Scientist Interview Questions and Answers
The questions most likely to come up in your Data Scientist interview, ranked by likelihood, with senior-level answers that cover what the interviewer is listening for.
Curated: · Written: · Reviewed:
Q&A-41A test for a condition affecting 1 in 1,000 people is 99% sensitive and 99% specific. A person tests positive. What is the probability they have it?
I would treat base rates in a conditional probability question as a design question first and a computation question second.
The answer is dominated by the base rate rather than by the accuracy figures, because the far larger healthy population produces more false positives than the small affected population produces true ones.
Concretely, work in expected counts on a concrete population rather than in conditional probabilities, put true positives and false positives side by side, and read the answer as one share of the other.
The reason for that specificity is a failure I have seen: A fraud team set a review threshold from a 99% accuracy figure and buried its analysts, because at a 0.1% fraud rate roughly 91 of every 100 flagged cases were legitimate customers.
One million people, counted rather than reasoned about.
| Group | People | Test positive |
|---|---|---|
| Has the condition | 1,000 | 990 |
| Does not | 999,000 | 9,990 |
| Total positive | 10,980 | |
| Share truly affected | 990 / 10,980 = 9.0% |
I would not consider it settled without evidence: Restate any accuracy claim as expected counts on the actual population before it is used to set a threshold or staff a queue.
Accuracy figures say nothing useful until the base rate is in the room.
Curated: · Written: · Reviewed:
Q&A-48A process has exponentially distributed gaps with a mean of 10 minutes. Nine minutes have passed. What is the expected wait from here?
10 minutes. The 9 minutes already elapsed does not change the expected remaining wait: that is the whole content of memorylessness. So the expected total length of this gap is 9 + 10 = 19 minutes, and the naive answer of 1 minute (mean minus elapsed) is wrong for an exponential.
Why
With rate λ = 1/10 per minute, the survival function is P(X > t) = e^(−t/10). Condition on having already waited 9 minutes:
P(X > 9 + s | X > 9) = P(X > 9 + s) / P(X > 9) = e^(−(9+s)/10) / e^(−9/10) = e^(−s/10)
The 9 cancels. The residual R = X − 9 given X > 9 is itself Exp(1/10), so E[R] = 10 minutes. The mechanism behind the cancellation is a constant hazard rate:
h(t) = f(t) / S(t) = (1/10)e^(−t/10) / e^(−t/10) = 0.1 per minute
A constant hazard means the process has no memory of how long it has been running, so elapsed time carries no information about the remaining wait.
The integral, run directly
E[R] = ∫₀^∞ P(R > s) ds = ∫₀^∞ e^(−s/10) ds = [−10 e^(−s/10)]₀^∞ = 10
Or without invoking memorylessness, from the conditional density:
E[X − 9 | X > 9] = e^(9/10) ∫₉^∞ (x − 9)(1/10)e^(−x/10) dx
Substitute u = x − 9, so the e^(9/10) prefactor cancels the e^(−9/10) inside:
= ∫₀^∞ u (1/10)e^(−u/10) du = 10
Both routes give 10. The 9 minutes is a red herring once the distribution is exponential.
The answer is distribution-specific
Same mean of 10 minutes, different gap distribution, different answer after 9 minutes elapsed:
| Gap distribution (mean 10 min) | Expected remaining after 9 min |
|---|---|
| Exponential (memoryless) | 10 min |
| Deterministic 10 min | 1 min |
| Uniform on [0, 20] | 5.5 min |
For the uniform case, conditioning on X > 9 leaves X uniform on [9, 20], so the residual mean is (20 − 9)/2 = 5.5. For the deterministic case the gap is exactly 10, so 1 minute remains. Only the exponential gives 10.
The follow-up an interviewer will ask
If instead you arrive at a random point in time rather than being told a gap has already run 9 minutes, you are sampling a gap length-biased: the gap containing your arrival has mean 2/λ = 20 minutes for the exponential, not 10. The residual from that arrival point is still 10 by memorylessness, but the total gap you sit inside averages 20. Distinguishing "9 minutes have passed in this gap" from "I arrived at a random time" is the trap in this question.
When this breaks
Memorylessness holds only for the exponential. If the gaps are Weibull with shape k > 1 (increasing hazard) or k < 1 (decreasing hazard), the residual depends on elapsed time, and the constant-hazard cancellation above fails. That is the case to check for in real systems: for example, retry or timeout policies that assume a constant failure rate when the underlying hazard is rising with age.
Curated: · Written: · Reviewed:
Q&A-51You are asked a probability question whose closed form you cannot recall under interview pressure. What do you do?
I'd simulate it, out loud, and quote the answer with an error bar.
The first move is to say the generating process back to the interviewer in one sentence, because that sentence is the model. "Roll three independent uniforms in [0,1] and ask whether the largest exceeds 0.9" is a question I can answer; "what's the probability of a big value" is not. If I can't state the process, the simulation won't save me: it will just encode my confusion.
Then I write the simulation, run it, and report the estimate with a Monte Carlo standard error, so the number of digits I quote is the number of digits I've earned.
import numpy as np
rng = np.random.default_rng(0)
draws = 200_000
hits = (rng.random((draws, 3)).max(axis=1) > 0.9).mean()
se = np.sqrt(hits * (1 - hits) / draws)
print(f"P = {hits:.4f} +/- {1.96 * se:.4f}")
That prints roughly P = 0.2710 +/- 0.0019 (the exact digits depend on the seed). The closed form here is 1 - 0.9^3 = 0.271, so the simulation lands where it should, but I only know that because I checked, and in an interview I'd say so.
The error bar is the whole point. With 200,000 draws the standard error is about 0.001, so two decimals are real and the third is noise. Quoting 0.2710 to four places from 1,000 draws would be wrong in the second decimal: the standard error there is about 1.5 percentage points.
Alternatives, and when each beats simulation.
- Exact enumeration. If the sample space is small and finite (dice, cards, a handful of Bernoulli trials) count outcomes directly. It's exact and fast, and it's the answer the interviewer probably wants if the structure is simple.
- A bound. If I only need "is it more or less than 1%", a union bound or Markov/Chebyshev inequality gives a defensible range without any simulation. Cheap, and honest about being loose.
- A normal or Poisson approximation. Sums of many independent indicators are approximately normal; rare events over many trials are approximately Poisson. Both give a closed form with a stated error, and both are wrong when the independence or rarity assumptions fail.
- Ask the interviewer. "Are the draws independent? What range?" is often faster than any of the above, and it isn't cheating: it's clarifying the model.
Failure modes. The simulation is only as good as the process I coded: correlated draws, a wrong range, or a max taken over the wrong axis all produce a confident wrong number. A fixed seed makes a run reproducible, not correct. And the classic error is quoting more digits than the standard error supports: the estimate and its error bar travel together or not at all.
If the interviewer wants more precision, I raise the draw count until the standard error is below the precision I'm claiming, and say so.
Curated: · Written: · Reviewed:
Q&A-50Two variables have a correlation of 0.02. Can you conclude they are unrelated?
No. A Pearson correlation of 0.02 means the linear component is essentially zero. It does not mean the variables are independent or that you can drop one from a model. Independence implies zero correlation; zero correlation implies independence only under strong assumptions, e.g. jointly Gaussian (or elliptical) variables. Real data rarely satisfy that.
Pearson r = cov(X,Y)/(σ_X σ_Y). It averages signed products of deviations. A symmetric U-shape or inverted U has positive and negative contributions that cancel. Range restriction, outliers, heteroscedasticity, and mixtures can also push r toward zero. So r=0.02 is evidence against a straight-line relationship, not against any relationship.
What to do instead: plot the raw scatter. If the relationship is monotonic but curved, Spearman or Kendall rank correlation will show it. If it may be non-monotonic, use a binned comparison, mutual information, or distance correlation. Those measures are more sample-hungry and less interpretable than r, so use them as a screen, not a final effect size. For feature selection, compare holdout performance with and without a nonlinear term.
Worked example (figures illustrative). A pricing team computes Pearson r=0.02 between discount depth and conversion. They almost drop discount depth. Binning by decile shows an inverted U:
| Decile of discount depth | Conversion rate |
|---|---|
| 1 (smallest) | 2.1% |
| 4 | 5.6% |
| 6 | 6.0% |
| 8 | 4.2% |
| 10 (largest) | 2.3% |
The linear fit averages the rise and fall to nearly zero. Adding discount depth as a quadratic term improves holdout accuracy by four points in this example. The failure mode is dropping a real nonlinear feature because a linear summary said "no".
Sample size matters. With n=100, the 95% CI for r=0.02 is roughly [-0.18, 0.22], so the estimate is compatible with a weak positive or negative linear association. With n=10,000, r=0.02 can be statistically significant (t≈2.0, p≈0.045) while still explaining about 0.04% of variance (r²=0.0004). "Significant" and "related" are different claims.
Failure modes to name:
- Concluding independence from r≈0. Detection: scatter plot, decile means, residual plots, distance correlation.
- Using Pearson on a monotonic curve. Detection: compare Pearson vs Spearman; a large gap means nonlinearity or outliers.
- Dropping a feature before checking nonlinear terms. Detection: cross-validated holdout with and without splines/quadratics/tree splits.
- Trusting a p-value without effect size. Detection: report r, CI, and r².
Decision rule: if r≈0, say "no linear association." Then check the plot and a nonlinear dependence measure before saying "unrelated." Zero correlation rules out a line, not a relationship.
Curated: · Written: · Reviewed:
Q&A-52How many random 32-bit identifiers can you generate before a collision is likely?
About 77,000. That is the birthday bound for a 32-bit space: √(2 × 2³² × ln 2) ≈ 77,000, and at that many identifiers the probability of at least one collision is 50%.
Assumptions: identifiers are drawn uniformly and independently from the full 2³² = 4,294,967,296 values, nothing checks for duplicates at generation time, and "likely" means 50%.
Why the square root and not the space size: collisions are a property of pairs, not items. n identifiers form n(n−1)/2 ≈ n²/2 pairs, and each pair matches with probability 1/N. Expected collisions ≈ n²/(2N); for small probabilities the count is close to Poisson, so
P(at least one collision) ≈ 1 − e^(−n²/(2N))
Setting that to 0.5 gives n ≈ 1.177√N = 1.177 × 65,536 ≈ 77,000.
| Identifiers issued | Approximate P(at least one collision) |
|---|---|
| 10,000 | 1.2% |
| 77,000 | 50% |
| 200,000 | 99% |
| 1,000,000 | above 99.99% |
The threshold moves fast with the risk you will accept. Solving n = √(2Np) for a one-in-a-million chance gives n ≈ 93: under a hundred identifiers before a 32-bit random ID is a bad bet at that tolerance. At 1% it is about 9,300.
Sizing rule: compute n from the total identifiers the system will ever issue over the ID's lifetime (every event, every retry, every environment sharing the space) not from today's rate. A service emitting 50,000 events a day crosses 77,000 in under two days, so a 32-bit random request ID is a coin flip on collision inside its first week of production.
If you need more headroom: 64 random bits push the 50% point to about 5.1 billion; UUIDv4 carries 122 random bits; ULID carries 80 random bits plus a 48-bit timestamp. If you do not need randomness, a central counter or a shard-prefix-plus-counter removes the birthday problem entirely, at the cost of coordination or a fixed width.
Detect it rather than assume it: a unique index on the ID column turns a collision into a failed insert you can alert on, and a periodic count(distinct id) against count(*) catches collisions in stores that do not enforce uniqueness.
Curated: · Written: · Reviewed:
Q&A-44A colleague says "we have a million rows, so everything is normal". What are they conflating?
They're conflating two different distributions. The central limit theorem says the sampling distribution of the mean approaches normal as n grows. It says nothing about the distribution of the data itself. A million rows makes your estimate of the mean precise; it does not make the underlying values normal. If the data is log-normal, it stays log-normal at a million rows, at a billion rows, forever.
There's a second conflation in the same sentence: the law of large numbers says the sample mean converges to the population mean. That's a statement about a point estimate, not about shape. People say "large n, so normal" and mean either one; neither licenses the claim they're making. There's also a pun doing quiet work: "normal" meaning "fine" versus "normal" meaning "normally distributed".
The mechanism: the CLT is a statement about sqrt(n)(x̄ − μ) → N(0, σ²). The thing that converges is the distribution of the average, and the rate of convergence depends on the skewness and kurtosis of the data (Berry–Esseen). The data's own CDF doesn't move at all. So "n is large" buys you a tighter confidence interval on the mean. It buys you nothing on a p99.
Where this bites: any rule that takes the mean and standard deviation of the raw data and assumes a normal shape. Take a latency metric that's log-normal with median 100 ms and σ = 1.0 on the log scale. Then:
- mean = exp(4.6052 + 0.5) = 164.9 ms
- sd = 164.9 × sqrt(e − 1) = 216.1 ms
An alert set at mean + 2 sd = 597 ms is supposed to fire 2.3% of the time. Actually P(X > 597) = P(Z > (ln 597 − 4.6052)/1) = P(Z > 1.787) = 3.7%. That's 62% more pages than budgeted, and it gets worse the further into the tail you go:
| Quantile | Normal rule (mean + z·sd) | Empirical |
|---|---|---|
| 95th | 520 ms | 518 ms |
| 99th | 668 ms | 1,024 ms |
| 99.9th | 833 ms | 2,198 ms |
The 95th percentile looks fine: that's the trap. The rule is calibrated where nobody cares and wrong where the SLO lives.
Two more failure modes worth naming. First, the CLT needs finite variance; a Pareto tail with α ≤ 2 has none, and the sample mean doesn't converge to anything stable no matter how many rows you have. Second, "a million rows" is only a million independent samples if they're independent. A million requests from 100 users is closer to 100 effective samples, and the CLT's n is the effective one.
So when someone justifies a method with "we have a million rows", ask which claim they need. If it's an interval on a mean, the CLT is the right tool and n is the right lever. If it's a quantile, a threshold, or anything that depends on the shape of the data, plot the empirical quantiles against the normal quantiles implied by the mean and sd before you trust the rule. The sample size doesn't enter that check.
Curated: · Written: · Reviewed:
Q&A-45You model daily signups as binomial. What assumptions are you making, and how would you notice they have failed?
What I'd say in the first minute. The binomial treats a day's signups as the sum of n independent Bernoulli trials sharing one probability p. That is four separate claims: n is fixed and known, trials are independent, p is the same for every user within the day, and p is the same across days. Signup data usually breaks the last two, and the way you catch it is to compare the observed day-to-day variance against the variance the model implies. If observed variance is larger, the model is overdispersed and every interval and p-value built on it is too tight.
The assumptions, spelled out.
- Fixed n. You know how many people could have signed up each day (visitors, invites sent) and that number isn't itself random in a way correlated with p.
- Independence. One user signing up doesn't move another's odds: no virality, no shared outage, no shared campaign landing at once.
- Constant p within a day. Every user in the day has the same conversion probability, regardless of channel, device or intent.
- Constant p across days. The underlying rate doesn't drift with weekday, spend or season.
How you notice. Compute the dispersion index: observed variance of daily counts divided by the binomial variance n p̂(1−p̂). Under the model it is 1. Then run a Pearson chi-square over D days, Σ (Oᵢ − Eᵢ)² / Eᵢ, against df = D − 1; for 30 days the 5% critical value is 42.6. The ratio alone is noisy: with 30 days its standard error is roughly √(2/(D−1)) ≈ 0.26, so 1.09 is indistinguishable from 1 while 5.15 is not. Finally, look at the residuals for structure: lag-1 autocorrelation, a weekday pattern, a step at a campaign launch. Overdispersion with structure points at a specific cause; overdispersion without it points at unmodelled user heterogeneity.
| Period | Mean daily signups | Binomial variance | Observed variance | Ratio | 95% half-width |
|---|---|---|---|---|---|
| Stable month | 420 | 418 | 455 | 1.09 | ±40 → ±42 |
| Campaign month | 610 | 606 | 3,120 | 5.15 | ±48 → ±110 |
(The binomial variance is n p̂(1−p̂); with p̂ ≈ 0.005 and n ≈ 84,000 visitors it lands just under the mean. Half-widths are 1.96 × √variance.)
Two mechanisms worth naming. First, if n is itself random, traffic varies day to day, the count is Poisson-binomial and Var = E[n]p(1−p) + p²Var(n), which inflates variance even when p is genuinely constant. Second, if p varies across users (paid vs organic, mobile vs desktop), the mixture is overdispersed by construction even though each user is a clean Bernoulli.
What it costs you. In a control chart, binomial limits on a campaign month flag routine variation as an incident. In an A/B test, the same inflation makes a null result look significant: a ratio of 5 means the true standard error is √5 ≈ 2.2× the assumed one, so a nominal p = 0.01 is closer to 0.2.
What to do instead. Beta-binomial if you want a generative model with a closed-form predictive: it assumes p is drawn from a Beta, which is a real assumption, not a free fix. Quasi-binomial (R's quasibinomial) if you only need honest standard errors; it rescales them by the estimated dispersion and leaves point estimates alone. Negative binomial when counts are unbounded. A hierarchical model with day-level random effects when you also want to attribute the variation to spend or weekday.
Binomial is still the right call when you condition on a fixed cohort (one day's 84,000 visitors, one invite batch) and the rate is stable within it. The failure is treating a month of heterogeneous days as if it were one such cohort.
Curated: · Written: · Reviewed:
Q&A-46Support tickets arrive at about 40 a day. When is a Poisson model appropriate, and when is it not?
The 60-second version. Poisson is the right model when tickets arrive as independent events at a constant rate: the count in any window of length t is Poisson(λt), counts in disjoint windows are independent, and the variance equals the mean. At 40/day that pins the standard deviation at √40 ≈ 6.3 and the 99th percentile at 55. It is the wrong model as soon as arrivals cluster or the rate itself moves, because both push the variance above the mean and every threshold derived from Poisson variance comes out too tight.
What you are actually assuming. Two things, and they fail separately. Independence: one ticket does not make another more likely. Constant rate: λ does not change with time of day, day of week, or release schedule. Variance-equals-mean is a consequence of those two, not a third assumption, which makes it the cheapest thing to test.
Test it. Take daily counts over a few months and compute the dispersion index D = s²/x̄ on rolling four-week windows. D near 1 supports Poisson. D materially above 1 means overdispersion, and you need to know which assumption broke, because the fixes differ.
- Clustering. One incident opens 30 tickets in an hour. The daily count is then a compound process, a random number of tickets per incident, and it is overdispersed even when incidents themselves arrive as a Poisson process. Fix: negative binomial, a Gamma-Poisson mixture that keeps the mean and adds a dispersion parameter.
- Rate variation. Weekday/weekend cycles, release days, seasonal load. Fix: a Poisson GLM with day-of-week and holiday covariates, or a non-homogeneous rate. Negative binomial is the fallback when you cannot name the covariate.
Why it matters: alert thresholds.
| Model | Variance/mean | 99th percentile daily count |
|---|---|---|
| Poisson (mean 40) | 1.0 by assumption | 55 |
| Negative binomial (fitted) | 7.5 | 90 |
Curated: · Written: · Reviewed:
Q&A-47Which common analyses genuinely require normally distributed data, and which do not?
Two classes, split by what the analysis makes a claim about.
Claims about a mean (or a sum, or a regression coefficient) do not require normal data. The sampling distribution of a mean is approximately normal for large n by the central limit theorem, provided the variance is finite. A t-test, a confidence interval for a mean, an ANOVA F-test and OLS coefficient inference all sit here: they need the errors to be roughly symmetric and homoscedastic, not the raw data to be normal. Rank tests (Wilcoxon, Mann-Whitney, Kruskal-Wallis), permutation tests and the bootstrap drop the assumption entirely.
Claims about individual observations, or about the shape of the distribution, do require it. A prediction interval for one future delivery, a tolerance interval, a quantile estimate, control limits on an individuals chart, and capability indices (Cp/Cpk) are statements about the population distribution, not about a mean. Normality is a real assumption here, and "we have plenty of data" does not discharge it: more data sharpens your estimate of the wrong shape.
The reason is that the CLT protects the average, not the individual. Averaging n draws collapses the tail; a single draw keeps it.
| Analysis | Normal data required? | What actually matters |
|---|---|---|
| CI or test for a mean, large n | No | finite variance; n large enough for the CLT |
| t-test / t-CI, small n | Yes (data or residuals) | exact only under normality; heavy tails break it |
| Prediction interval for one observation | Yes | the data's own shape |
| Tolerance interval (95%/99%) | Yes | tail shape |
| Quantile / percentile estimate | No | precision depends on density near the quantile |
| Individuals control chart (X̄ ± 3σ) | Yes | individual observations |
| X̄–R chart (subgroup means) | No, asymptotically | subgroup means |
| Cp / Cpk capability index | Yes | assumes normal tails |
| OLS coefficients, large n | No | Gauss-Markov: zero-mean, homoscedastic, uncorrelated errors |
| Regression prediction interval | Yes (of residuals) | residual shape |
| ANOVA F-test | Robust for balanced, large n | group sizes and variance homogeneity |
| Wilcoxon / Mann-Whitney / Kruskal-Wallis | No | distribution-free |
| Permutation / bootstrap | No | exchangeability / representative resampling |
| Chi-square test of independence | No (asymptotic) | expected cell counts ≥ 5 |
Worked example. Delivery times are exponential with mean 30 min, so σ = 30. A normal-based 95% prediction interval for one delivery is 30 ± 1.96×30 = [−28.8, 88.8] min. The exact 95% interval is [−30 ln 0.975, −30 ln 0.025] = [0.76, 110.7] min. Two things are wrong with the normal interval: its lower bound is a negative time, and its upper bound sits at 88.8 min, above which e^(−88.8/30) = 5.2% of deliveries fall. Twice the 2.5% it promises. Total coverage still looks fine (94.8%) because the negative lower bound swallows the whole lower tail, which is exactly why nominal coverage alone is not a sufficient check.
How to detect it: plot the empirical distribution (a QQ plot against the fitted normal, or just the histogram) before using any method in the second class, and compute empirical coverage of the interval on held-out data. Shapiro-Wilk or Anderson-Darling tell you whether the data are normal; they do not tell you whether the method cares.
Two traps worth naming. First, regression: Gauss-Markov gives unbiased OLS coefficients with no normality at all, so coefficient CIs are fine for large n, but the prediction interval for a new point depends on the residual distribution, and that is where normality bites. Second, residuals versus the marginal distribution of Y: a skewed Y with a good predictor can have symmetric residuals, and it is the residuals that matter for inference.
Curated: · Written: · Reviewed:
Q&A-43You add two metrics together. When does the variance of the sum equal the sum of the variances, and when does that matter?
I would settle expectation and variance of a sum against the simplest defensible comparison, so any extra structure has to earn its place.
Expectations always add, and variances add only when the terms are uncorrelated, so a positive correlation between components makes the sum more variable than the parts suggest.
Concretely, estimate the covariance rather than assuming it away, include the cross term when propagating uncertainty into a composite metric, and check the sign of the correlation because it decides whether combining components damps or amplifies noise.
The reason for that specificity is a failure I have seen: A composite health score treated its four components as independent and reported an interval roughly 25% too narrow, because three of the components moved together during incidents. Exactly the periods the score existed to flag.
The cross term is the whole difference here.
| Quantity | Value |
|---|---|
| Var(A) | 4.0 |
| Var(B) | 9.0 |
| Corr(A, B) | 0.6 |
| Var(A + B) assuming independence | 13.0 |
| Var(A + B) with 2 x 0.6 x 2 x 3 | 20.2 |
I would not consider it settled without evidence: Compare the analytically propagated interval against a bootstrap of the composite; a gap says a covariance term was dropped.
Independence is an assumption about the data, not a property of addition.
Curated: · Written: · Reviewed:
Q&A-42Walk through how you would update a prior belief about a conversion rate after observing 12 conversions in 300 visitors.
The update is a beta-binomial conjugate update: turn the prior into pseudo-counts, add the observed counts, and read the posterior off the resulting beta. No integral, no sampling.
Write the prior as Beta(a₀, b₀), where a₀ and b₀ are pseudo-successes and pseudo-failures. A weakly informative choice is Beta(8, 192): mean 4%, worth 200 visitors of evidence. Observing 12 conversions in 300 visitors adds 12 successes and 288 failures, and because beta is conjugate to the binomial the posterior is just Beta(a₀+x, b₀+n−x):
posterior = Beta(8+12, 192+288) = Beta(20, 480).
The posterior mean is 20/500 = 0.040: the prior mean (4%) and the sample proportion (12/300 = 4%) averaged with weights 200/500 and 300/500. Here they agree, so the point estimate doesn't move. The interval is what carries the information.
from scipy.stats import beta
a0, b0 = 8, 192 # prior: 4% mean, 200 pseudo-visitors
a, b = a0 + 12, b0 + 288 # 12 conversions, 288 non-conversions
print(a, b) # 20 480
print(round(a / (a + b), 4)) # 0.04
print(beta.ppf([0.025, 0.975], a, b).round(3)) # [0.025 0.059]
So report 4.0% with a 95% credible interval of roughly 2.5%–5.9%. If the decision threshold sits inside that interval, the data hasn't decided.
How much the prior matters. With 300 visitors, not much, as long as the prior isn't absurdly strong. Same data, three priors:
| prior | posterior | mean | approx. 95% interval |
|---|---|---|---|
| Beta(8, 192) | Beta(20, 480) | 4.0% | 2.5%–5.9% |
| Jeffreys Beta(0.5, 0.5) | Beta(12.5, 288.5) | 4.2% | 2.2%–6.7% |
| flat Beta(1, 1) | Beta(13, 289) | 4.3% | 2.1%–6.9% |
Intervals to one decimal, from the same beta.ppf call. The point estimate moves 0.3pp and the width about 0.6pp across these. Now make the prior strong (Beta(80, 1920), 2000 pseudo-visitors) and the posterior is Beta(92, 2208), mean 4.0%, with 300 visitors barely moving it. That is the failure mode to watch: a prior heavy enough to ignore the data.
Failure modes.
- Wrong likelihood. Binomial assumes each visitor converts at most once and independently. If a visitor can convert repeatedly, or conversions cluster by session or device, use a beta-binomial or a hierarchical model with a per-segment rate.
- Peeking. The posterior is valid for the sample size you fixed in advance. Stopping the moment the interval clears a threshold and reporting that interval overstates confidence.
- Reporting the mean alone. 4.0% from Beta(20, 480) and 4.0% from Beta(4, 96) share a mean and have very different intervals: the latter is roughly 1%–8.6%. Ship the interval.
When not to use this. With no defensible prior and few observations, a flat or Jeffreys prior is fine; the table shows it costs little. If the rate is genuinely time-varying (promo periods, seasonality) a static beta is the wrong model, and you want a state-space or hierarchical one.
Curated: · Written: · Reviewed:
Q&A-26What does a 95% confidence interval actually promise, and how should you describe one to a non-statistician?
What I'd say first. Assume a frequentist interval from a correctly specified model, with the true effect fixed and unknown. Then the 95% is a property of the procedure, not of this interval. Build intervals the same way across many samples and 95% of them cover the true value. Once you've computed one, it either contains the truth or it doesn't: the 95% no longer applies to it.
To a non-statistician: "This is the range of effects the data doesn't rule out. It is not a 95% chance the truth is in here."
Why it behaves that way. Coverage is a long-run frequency over the sampling distribution. The randomness lives in the data, and therefore in the endpoints, not in the parameter. That is exactly why you cannot say "95% probability the true effect is between a and b" without a prior.
Alternatives, and when they win.
- Bayesian credible interval. With a prior, the posterior supports a direct probability statement, "95% probability the effect is in this range", which is what most stakeholders already think a CI says. Use it when you have a defensible prior and the audience needs that direct statement. The cost: the answer depends on the prior, and a weakly informative prior on a small sample can move the interval more than the data does.
- Point estimate plus a decision threshold. If the decision is a single go/no-go against a known break-even, fund only if the effect exceeds $0, you need the estimate and its standard error against that threshold, not the full interval. Simpler to act on, but it hides how close the call is.
Failure mode. A launch was approved on a point estimate of +$1.2M whose interval ran from -$0.3M to +$2.7M. The plan committed spend justified only near the upper end and carried no contingency for the lower one. The decision was read at the point estimate, not at both ends:
| Scenario | Annual effect | Decision |
|---|---|---|
| Lower bound | -$0.3M | do not fund the expansion |
| Point estimate | +$1.2M | fund |
| Upper bound | +$2.7M | fund and accelerate |
The interval is the input to the decision, not the decision. Read the decision at both endpoints; if it flips between them, you don't have a decision yet: you have a range that needs more data or a smaller, reversible commitment.
Curated: · Written: · Reviewed:
Q&A-28Define statistical power without using the word "power", and explain why a low-powered significant result is a problem.
Definition. The probability that a test rejects the null hypothesis when the true effect is exactly the size you specified. Fix α, fix n, fix the effect you care about; the number that falls out is the chance you detect it. It is a property of the design, not of the data you collected.
Why a significant result from a low-detection design is a problem. Two failures compound.
Conditional on reaching significance, the estimate is biased away from zero. The test fires only when noise pushes the estimate past the threshold, so the surviving estimates are the upper tail of the estimator's distribution. Direction is usually right; magnitude is not. This is the winner's curse.
The result is also fragile. A design with 20% detection that did fire got lucky; the same design run again usually misses. The number is not reproducible at the same n.
The damage is that the inflated magnitude becomes the planning assumption. A 20%-detection study reported +40%; the replication at 80% detection found +6%; the roadmap had been sized against +40%.
The arithmetic. One-sided α = 0.05, normal approximation, true effect +6%:
E[estimate | significant] = true effect + SE × φ(z_α − z) / Φ(z − z_α), with z = true effect / SE.
| Detection probability | True effect | Mean of estimates that reach significance |
|---|---|---|
| 20% | +6% | +16.5% |
| 50% | +6% | +8.9% |
| 80% | +6% | +6.8% |
Worked for the 20% row: detection 20% means z − 1.645 = −0.842, so z = 0.803 and SE = 6/0.803 = 7.47. φ(−0.842) = 0.280; 0.280/0.20 = 1.40; conditional mean = 6 + 7.47 × 1.40 = 16.5%.
Alternatives, and when each is right.
- Enlarge n until detection is adequate. Default when the effect is plausible and the decision is expensive.
- Sequential or group-sequential design with alpha spending. Lets you stop early on a large effect at a small sample-size cost; right when you cannot commit to a fixed n.
- Run it as exploratory and pre-commit not to plan against the magnitude. Report the interval, not the point estimate, and use it only to size the next study.
- Accept low detection when a miss is cheap and the study is for hypothesis generation. A pilot that says "something may be here" is worth running if you will not act on the magnitude.
- Estimation framing, confidence interval or posterior, instead of a significance threshold. The interval width states what you can claim and does not reward the tail.
Failure modes and how to catch them.
- Winner's curse: significant estimate exceeds the truth. Catch it by comparing the reported effect to the MDE; an effect near the MDE is inflated.
- Non-replication: same design, same n, no effect. Catch it by replicating before the roadmap.
- A null read as "no effect": absence of evidence. Catch it by checking whether the interval excludes the effect you care about.
What I would do. Compute the MDE for the n I can get. If MDE exceeds any plausible effect, enlarge n, switch to a sequential design, or downgrade to exploratory with the magnitude pre-registered as unusable for planning. When a small study does reach significance, treat the magnitude as an upper bound and confirm it before anyone builds on it.
Curated: · Written: · Reviewed:
Q&A-25An executive asks whether p = 0.03 means there is a 97% chance the feature works. How do you answer?
No. A p-value of 0.03 is not a 97% chance the feature works. The two numbers aren't complements, and the p-value isn't a statement about the feature at all.
A p-value answers exactly one question: if the feature did nothing, how often would we see data at least this extreme? It's a probability about the data, computed under the assumption that the null is true. It is not the probability that the null is true, and it is not the probability that the alternative is true. Those are posterior probabilities, and a posterior needs a prior.
What I'd hand the executive instead is the effect and its interval, in the units they already use:
| Statement | True? |
|---|---|
| "If the feature did nothing, data this extreme would show up 3% of the time." | yes |
| "The lift is +1.4%, 95% interval +0.1% to +2.7%: about +$41k to +$1.1M a year." | yes |
| "There's a 97% chance the feature works." | no |
The middle row is the one that supports a decision. It says how big the effect is and how much uncertainty is left, and it stays meaningful if you delete the p-value entirely. The first row is the correct reading of the p-value, and it's worth saying out loud once so the executive can hear how different it is from what they asked. The third row is the misreading.
If the executive genuinely wants a probability that the feature works, that's a legitimate ask: it just needs a different calculation. Take the effect estimate and its standard error, combine them with a prior on the effect size, and report the posterior probability that the effect exceeds the threshold that matters. The answer will depend on the prior, so state it. A weakly informative prior centered on zero is usually the honest default, and it will typically give a posterior well below 97% for a result this marginal.
Two things make the 0.03 look stronger than it is. The first is peeking: if the test was checked repeatedly and stopped when it crossed 0.05, the nominal p-value understates the false-positive rate, because each look is another chance to cross. The second is multiplicity: a 40-metric panel at α = 0.05 has a 1 − 0.95^40 ≈ 87% chance of producing at least one "significant" metric even when nothing is happening. A plan built on three such readings (one peeked, one from the panel) can rest on nothing, and the assumption it encodes tends to fail within a quarter.
So the answer to the executive is no, and here's the number that does mean what you want. Restate every readout as an effect with an interval in business units, and check whether the recommendation still holds when the p-value is dropped. If it does, the p-value was never carrying the decision.
Curated: · Written: · Reviewed:
Q&A-27When would you set alpha to 0.10 rather than 0.05, and when would you go the other way?
Core answer. Set α from the relative cost of the two errors, not from convention. Loosen to 0.10 when a false negative is much more expensive than a false positive and you are traffic-constrained; tighten to 0.01 when shipping an inert change is expensive or hard to reverse. The assumption that matters: at a fixed sample size α and power trade off, so moving α is really moving the type-I/type-II balance.
Mechanism. At a fixed n, α and β are coupled through n ≈ (z_{α/2}+z_β)² · Σp(1−p)/δ². Drop α from 0.05 to 0.10 and z_{α/2} falls from 1.96 to 1.645, which buys power for free: no extra traffic. Raise α to 0.01 and z_{α/2} rises to 2.576, so you lose power unless you pay for more samples. So "0.10 or 0.05?" is really "which error do I want to spend my fixed traffic on?"
Worked example. Baseline conversion 5%, want to detect a 10% relative lift (5.0% → 5.5%), two-sided. At α=0.05, 80% power: n per arm = (1.96+0.84)² · [0.05·0.95 + 0.055·0.945] / 0.005² = 7.84 · 0.0995 / 0.000025 ≈ 31,200 per arm.
Hold n at 31,200 and move α:
- α=0.05 → z_β = 2.80 − 1.96 = 0.84 → power 80%, β=0.20.
- α=0.10 → z_β = 2.80 − 1.645 = 1.155 → power ≈ 88%, β≈0.12.
Now price the errors. Say a false positive (shipping an inert change) costs $50k in build and maintenance, a false negative (missing a real lift) costs $200k in foregone revenue, and the prior on a real effect is 0.3. Expected cost = P(H0)·α·C_FP + P(H1)·β·C_FN:
- α=0.05: 0.7·0.05·50k + 0.3·0.20·200k = $1.75k + $12.0k = $13.75k
- α=0.10: 0.7·0.10·50k + 0.3·0.12·200k = $3.5k + $7.2k = $10.7k
Here 0.10 wins by ~$3k because the false negative dominates and the extra power is free at this n. Flip the costs, make the false positive $500k (an irreversible migration you cannot roll back), and 0.05 wins: 0.7·0.05·500k + 12k = $29.5k versus 0.7·0.10·500k + 7.2k = $42.2k. That is the whole decision.
| Situation | C_FP (ship inert) | C_FN (miss real) | α |
|---|---|---|---|
| Cheap reversible UI tweak, traffic-limited | Low | Moderate | 0.10 |
| Expensive irreversible migration | Very high | Moderate | 0.01 |
| Safety regression check | Low (a rollback) | Very high | 0.10, with power ≥ 0.90 |
Failure mode. I have seen a safety-relevant change tested at α=0.05 with 60% power. That means a real regression had a 40% chance of passing unnoticed, and the team read the non-significant result as evidence of safety. The α was fine; the power was the bug. If you loosen α to buy power, check the power you actually bought: don't just move the threshold and assume.
When not to. Don't re-litigate α after seeing the result. Fix it from costs before the test, write the reasoning down, and if you genuinely need both low α and high power, buy more traffic rather than pretending the trade-off away.
Curated: · Written: · Reviewed:
Q&A-30You are comparing conversion rates of 4.1% and 4.4% across two arms of 60,000 users each. Which test, and why that one?
The first 90 seconds
Two-proportion z test. It is the same test as a chi-square on the 2×2 table (z² = χ² with 1 df, identical p-value) so use whichever your tooling prints.
Why this one: the outcome is Bernoulli, the arms are independent, and every expected cell count is in the thousands, so the normal approximation to the binomial is accurate. That is the entire justification. It is not "the sample is large"; it is "no expected cell is small."
Assumptions I would state before running it: independent randomisation per user, one outcome per user, a pre-registered horizon with no peeking, and this is the primary metric.
The numbers
| Control | Treatment | |
|---|---|---|
| Users | 60,000 | 60,000 |
| Conversions | 2,460 | 2,640 |
| Rate | 4.10% | 4.40% |
Pooled p = 5,100/120,000 = 0.0425. SE_pooled = √(0.0425 × 0.9575 × (2/60,000)) = 0.001165. z = 0.003/0.001165 = 2.58, two-sided p = 0.010. The unpooled SE for the interval is 0.001165 as well, they agree to four decimals at these rates, so the 95% CI on the difference is 0.003 ± 1.96 × 0.001165 = (0.0007, 0.0053). Relative lift: 0.003/0.041 = 7.3%.
# Python 3.11, statsmodels 0.14
from statsmodels.stats.proportion import proportions_ztest, confint_proportions_2indep
count, nobs = [2460, 2640], [60000, 60000]
z, p = proportions_ztest(count, nobs) # pooled SE under H0
lo, hi = confint_proportions_2indep(2640, 60000, 2460, 60000, method="wald")
print(f"z={z:.2f} p={p:.3f} diff 95% CI = ({lo:.4f}, {hi:.4f})")
# z=2.58 p=0.010 diff 95% CI = (0.0007, 0.0053)
confint_proportions_2indep takes (count1, nobs1, count2, nobs2) and returns the interval for p1 − p2, so treatment goes first to keep the sign positive.
Alternatives, and when they win
- Fisher's exact: exact but conservative. At these counts it matches the z test to about three decimals, so run it as a sanity check. It is the right default once any expected cell drops below ~5.
- Chi-square with Yates' continuity correction: slightly conservative. Without the correction it is identical to the z test (z² = 6.63 = χ²₁).
- Bootstrap: re-derives the same normal approximation here. Worth the compute only for statistics without a closed form: a ratio of ratios, or revenue per user with a heavy tail.
- t test on the 0/1 indicators: same answer at n = 60,000, and it is what people reach for. It is the wrong tool at small n, where it hands back intervals containing impossible values.
Failure modes
- Peeking. Checking daily for a week gives a family-wise false-positive rate near 1 − 0.95⁷ ≈ 30% under independent looks (correlated looks inflate less, but still materially). Pre-register the horizon, or use a Pocock/O'Brien-Fleming boundary or a confidence sequence.
- Wald interval at small counts. At 2 successes in 40, the Wald 95% interval for the proportion is 0.05 ± 1.96 × 0.0345 = (−0.018, 0.118). Impossible values, and real coverage well under 95%. Use Wilson, Agresti-Coull, or Fisher.
- Sample ratio mismatch. If the arms come back 60,000 vs 54,000, the randomiser or the logging is broken: test the ratio before testing the metric.
- Non-independence. Repeat sessions from one user shrink the effective n and make the SE too small. Cluster by user.
The part that decides the outcome
At 60,000 per arm, 80% power at α = 0.05 two-sided corresponds to an MDE of about 0.33pp absolute (≈8% relative on a 4.1% baseline). The 0.3pp you observed sits just under that MDE, so p = 0.01 is about what you would expect: this experiment can resolve the effect, but with no margin. If the business needs to act on a 0.1pp difference, the design is roughly 10× too small: about 640,000 users per arm.
Curated: · Written: · Reviewed:
Q&A-31Revenue per user is heavily right-skewed. Does that invalidate a t test on the difference in means?
Short answer. No: skew alone doesn't invalidate the t test. The t test assumes the sampling distribution of the mean is approximately normal and that the sample variance is a stable estimate of σ². Skew and kurtosis decide how large n must be for those two things to hold, and revenue's tail can push that n far past what teams actually run. So the question isn't "is revenue skewed" but "has the mean converged, and is the variance estimate dominated by a handful of rows".
Why skew matters, and how much. The CLT error for the mean is bounded by Berry-Esseen: sup_x |F_n(x) − Φ(x)| ≤ C·ρ/(σ³√n), with ρ = E|X−μ|³ and C ≤ 0.4748. The ratio ρ/σ³ is the skewness-like term, and halving the error costs 4× the sample. A lognormal with σ_log = 1, a plausible shape for revenue, has skewness ≈ 6.2 and excess kurtosis ≈ 111, against 0 and 0 for a normal. That is why "n > 30" says nothing here.
There is a second failure the CLT statement hides. The t statistic is (x̄−μ)/(s/√n), and s² is itself a sum of squares of heavy-tailed values. When a few observations carry most of the variance, s² is noisy and the ratio is not t-distributed no matter how normal x̄ looks. That is the mechanism behind intervals that miss.
What to check. Three cheap diagnostics:
- Top-k share: the fraction of the arm's total revenue coming from the top 1% (or top 10) users. If it is 40%, the mean is effectively a handful of numbers plus noise.
- Leave-one-out: recompute the mean and the interval after dropping the single largest observation. A large swing means the variance estimate is unstable.
- Bootstrap vs t: percentile-bootstrap the difference in means and compare intervals on the same data.
import numpy as np
from scipy import stats
rng = np.random.default_rng(0)
# lognormal revenue: median $20, sigma_log = 1 -> skew ~6.2, excess kurtosis ~111
a = rng.lognormal(mean=np.log(20), sigma=1.0, size=30_000)
b = rng.lognormal(mean=np.log(20), sigma=1.0, size=30_000)
diff = b.mean() - a.mean()
se = np.sqrt(a.var(ddof=1)/len(a) + b.var(ddof=1)/len(b))
t_crit = stats.t.ppf(0.975, df=len(a)+len(b)-2)
print("t interval", diff - t_crit*se, diff + t_crit*se)
boot = np.array([rng.choice(b, b.size).mean() - rng.choice(a, a.size).mean()
for _ in range(10_000)])
print("bootstrap interval", np.percentile(boot, [2.5, 97.5]))
for name, x in (("a", a), ("b", b)):
print(name, "top-11 share", round(np.sort(x)[-11:].sum() / x.sum(), 3))
If the t and bootstrap intervals agree to within a few percent of the interval width, the t test is defensible. If they disagree (different sign, or non-overlapping) the normal approximation has not converged and the t interval is the one to distrust.
Illustrative numbers (hypothetical, same shape as the failure above):
| Metric | t interval | Bootstrap interval | Top 11 users' share |
|---|---|---|---|
| Revenue per user | -$0.12 to +$0.94 | -$0.41 to +$1.55 | 40% |
| Revenue capped at 99th pct | +$0.05 to +$0.31 | +$0.05 to +$0.32 | 4% |
Alternatives, and what each changes. Capping or winsorizing at a pre-registered percentile (99th) makes the estimand "mean of capped revenue": a different, more stable quantity; state that in the writeup. Mann-Whitney and Hodges-Lehmann answer a different question (shift in distribution, not the mean). A log transform turns the estimand into a ratio of geometric means and breaks with zero-revenue users. Quantile regression on the 90th percentile is right when the business cares about the tail rather than the average.
Failure modes to name. Coverage collapse: a nominal 95% t interval covering well below nominal; measure it by simulating from the observed distribution (parametric bootstrap). Sign flip: t significant, bootstrap straddling zero. Variance dominated by one row: s² moving by a large factor on leave-one-out. Welch's Satterthwaite df rescues none of these: it fixes unequal variances, not heavy tails.
Curated: · Written: · Reviewed:
Q&A-32You measured the same 500 users before and after a change. Why is a paired test different, and when is it wrong to use one?
What a strong candidate says first. Pairing is variance reduction, not identification. Analysing within-user differences removes the between-user component of the variance, so the standard error shrinks by about √(1−ρ), where ρ is the correlation between a user's before and after measurement. At ρ = 0.7 that is a 45% smaller SE, and because sample size scales with variance, roughly a third of the users for the same power. What pairing does not do is remove anything that changed between the two measurements. Same 500 users, no concurrent control, and the time trend is inside your estimate.
The arithmetic. With n = 500, σ = 20 points on the engagement score, ρ = 0.7:
- Unpaired SE of the difference = σ√(2/n) = 20 × √(2/500) = 1.27 points
- Paired SE = σ√(2(1−ρ)/n) = 20 × √(0.6/500) = 0.69 points
At 80% power and two-sided α = 0.05 (multiplier ≈ 2.8), the minimum detectable effect is 3.5 points unpaired versus 1.9 points paired. To detect a 2-point effect you need about 470 paired users or about 1,570 unpaired. (Illustrative figures.)
When pairing is right. When the pairs are randomised: the same unit under both conditions in a counterbalanced crossover, matched pairs assigned to arms at random, or a pre-registered pre-period inside a user-level randomised experiment. In the last case the pre-period is a covariate, not a control: randomisation is still doing the identification.
When it is wrong. Whenever the two measurements are separated by something other than the treatment and there is no concurrent control. Also when the pairing itself was created by the treatment or by selection on the outcome, and when the paired sample is a non-random subset because of attrition.
Failure modes and how to catch them.
| Failure | What it looks like | Detection |
|---|---|---|
| Time-varying confound | Seasonality, another release, a marketing push | Concurrent untreated group; difference-in-differences; a pre-period placebo |
| Regression to the mean | Paired sample selected on an extreme before value | Run the same before/after on an untreated group selected the same way |
| Carryover / period effect | Crossover where period 1 bleeds into period 2 | Counterbalance order, washout, test treatment×period interaction |
| Differential attrition | Paired analysis drops anyone missing either measurement | Compare dropout and baseline covariates by arm; report ITT alongside |
| Post-treatment pairing | Matching on engagement measured during the experiment | Ask whether the matching variable could have been affected by the treatment |
| Autocorrelated differences | Time series where the paired t-test SE is too small | ACF of the differences; cluster-robust or Newey–West SEs |
The confound, concretely (illustrative):
| Group | Before | After | Change (points) | Change (%) |
|---|---|---|---|---|
| Treated | 12.4 | 14.3 | +1.9 | +15.3% |
| Untreated control | 11.9 | 13.5 | +1.6 | +13.4% |
| Difference-in-differences | +0.3 | +1.9 pp |
The paired test on the treated group alone reports +15.3%. The concurrent control says 13.4% of that was going to happen anyway. The effect of the change is +0.3 points.
Alternatives worth naming. An unpaired two-sample test is valid under randomisation and costs power. ANCOVA, regress the after score on the before score and the arm indicator, is usually more powerful than a paired t-test and handles baseline imbalance. A crossover wants a mixed model with period and carryover terms, not a paired t-test.
If you have precision without identification, you have a tighter interval around a number you cannot attribute to the change.
Curated: · Written: · Reviewed:
Q&A-29A colleague switches to a one-sided test after the result comes in, arguing you only care about improvements. Is that legitimate?
I would treat one-sided versus two-sided tests as a design question first and a computation question second.
A one-sided test is defensible only when it is chosen before the data and when a negative effect would lead to the same action as no effect, and switching after seeing the direction halves the p-value without adding any information.
Concretely, fix the sidedness in the analysis plan, and where a regression genuinely matters, which is almost always, because shipping harm is not the same as shipping nothing: keep the two-sided test and put the harm question in a guardrail.
The reason for that specificity is a failure I have seen: A borderline result at two-sided p = 0.07 was re-reported as one-sided p = 0.035 and shipped, and the feature was rolled back six weeks later after a regression that the original test had already hinted at.
The same z statistic under both readings.
from scipy.stats import norm
z = 1.81
print(f"two-sided p = {2 * (1 - norm.cdf(abs(z))):.3f}") # 0.070
print(f"one-sided p = {1 - norm.cdf(z):.3f}") # 0.035
I would not consider it settled without evidence: Compare the analysis plan's registered sidedness against the readout; any mismatch means the threshold moved after the data arrived.
Halving a p-value after seeing the sign is not a test, it is a decision dressed as one.
Curated: · Written: · Reviewed:
Q&A-35When would you use Bonferroni and when would you use a false discovery rate procedure?
Use Bonferroni when one false positive is expensive and the family is small: a ship/no-ship call on a primary endpoint, a safety signal, a confirmatory test. Use Benjamini-Hochberg when you are screening many candidates to produce a ranked list for follow-up and can live with a known fraction of that list being wrong.
Mechanism. Bonferroni rejects a test when p ≤ α/m. It bounds the family-wise error rate, the probability of one or more false positives anywhere in the m tests, at α. That bound holds under any dependence structure, which is why it survives correlated tests, and also why it is blunt: at m = 2,000 and α = 0.05 the per-test threshold is 0.000025, and a real effect needs a large sample to clear it.
Benjamini-Hochberg sorts the m p-values ascending, finds the largest k with p(k) ≤ (k/m)·q, and rejects the first k. It bounds the false discovery rate, the expected proportion of false positives among the rejected hypotheses, at q. The bound holds under independence or positive regression dependence (PRDS); under arbitrary dependence use Benjamini-Yekutieli, which scales the threshold by Σ(1/i) and costs real power.
The trade-off is power against the guarantee. Bonferroni controls the probability of any error; BH controls the share of errors among what you report. At the same α, BH rejects at least as many hypotheses as Bonferroni, usually many more.
Worked example (hypothetical counts, exact arithmetic). One screen of 2,000 features, α = 0.05, q = 0.10, the same p-values run through all three rules:
| Procedure | Threshold | Discoveries | Expected false among them |
|---|---|---|---|
| Uncorrected, α = 0.05 | 0.0500 | 143 | up to ~100 |
| Bonferroni | 0.05/2000 = 0.000025 | 0 | 0 |
| Benjamini-Hochberg, q = 0.10 | data-dependent | 34 | ~3 |
The uncorrected 143 hits are mostly noise: under the global null you would expect 2,000 × 0.05 = 100 false positives. Bonferroni returns nothing, and the screen reads as a dead end. BH returns 34 candidates with about 34 × 0.10 ≈ 3 expected false, which is a list you can afford to chase. Same data, different question, different answer.
Failure modes. Defining the family after seeing the p-values, quietly dropping the tests that did not come out, invalidates both procedures; fix the family in the analysis plan. Running BH on negatively correlated tests without the BY correction. Reading FDR as a per-item guarantee: q = 0.10 says roughly one in ten of the reported discoveries is false on average, not that any single discovery has a 10% chance of being false. And reporting only whichever procedure produced a significant result.
In practice: state the family and the procedure before looking at p-values, then report the discovery count under both so the reader can see what the choice cost. The two procedures answer different questions, so the choice follows from what a false positive costs.
Curated: · Written: · Reviewed:
Q&A-8Your experiment dashboard reports 40 metrics and two are significant at p < 0.05. What have you learned?
What a strong candidate says in the first two minutes
You have learned that the dashboard produced two hits, and almost nothing else. Forty independent tests at α = 0.05 under a global null give an expected 2.0 false positives, and P(at least one hit) = 1 − 0.95⁴⁰ ≈ 87%. Two hits is the modal outcome of a change that does nothing at all.
The first question is not statistical, it is procedural: was one of these two the pre-registered primary metric? If the primary metric was declared before the run and it is the p = 0.004 one, that is a signal worth chasing. If both hits came out of an undifferentiated 40-metric panel, you have no evidence of an effect and should not staff anything on them.
Control the error rate across the family you actually tested
Two credible corrections, and they answer different questions:
- Bonferroni (family-wise error rate): reject only if p ≤ α/m = 0.05/40 = 0.00125. Controls the probability of any false rejection at 5%. Very conservative with 40 correlated metrics; you will miss real effects.
- Benjamini-Hochberg (false discovery rate): sort p-values ascending, reject H₍₁₎…H₍ₖ₎ where k is the largest i with p₍ᵢ₎ ≤ (i/m)·α. Controls the expected proportion of false discoveries among rejections. Valid under independence and under positive regression dependence, which is the realistic case for a dashboard of correlated product metrics. Teams usually run it at q = 0.10 rather than 0.05.
Worked trace on the two hits
m = 40, α = 0.05. The two significant metrics sorted ascending:
| Rank i | Metric | p₍ᵢ₎ | BH threshold (i/40)·0.05 | Reject? |
|---|---|---|---|---|
| 1 | Support contacts | 0.004 | 0.00125 | no |
| 2 | Coupon views | 0.031 | 0.00250 | no |
| 3–40 | remaining 38 | > 0.05 | ≥ 0.00375 | no |
p₍₁₎ = 0.004 > 0.00125 and p₍₂₎ = 0.031 > 0.00250, so k = 0: BH rejects nothing. Bonferroni agrees: 0.004 > 0.00125. Raising the FDR to q = 0.10 gives thresholds 0.0025 and 0.005; still nothing survives. Both hits are consistent with noise.
Failure mode to name
A team reported a 3.2% lift in a niche support metric pulled from a 40-metric panel, staffed a quarter of engineering on it, and found nothing when the metric was tested alone in a dedicated experiment. The tell was visible in the original readout: the metric was not pre-registered, and its p-value would not have survived any family-wise correction. Detection is cheap: run the same 40-metric panel on an A/A split and count how many cross p < 0.05. You will see roughly two, every time, which is the number the team should have expected.
What to do instead
Pre-register one primary metric and test it at the nominal level. Put guardrail metrics on non-inferiority bounds, not significance. Run BH across the exploratory set and label survivors as hypotheses for a follow-up experiment with its own pre-registered primary, not as findings. If a follow-up confirms one, you have earned the claim; the original panel never did.
Curated: · Written: · Reviewed:
Q&A-9What is a guardrail metric, and how should it be treated differently from the metric you are trying to move?
Where analyses go wrong on guardrail metrics is usually one step upstream of the statistic being argued about.
A guardrail exists to detect harm the team is not looking for, so it is tested for the absence of a regression rather than the presence of a gain, and it keeps its decision authority even when it is not significant.
Concretely, define each guardrail with an explicit non-inferiority bound, block the ship when the confidence interval for the guardrail extends past that bound, and do not require significance to act: a wide interval that admits real harm is itself the finding.
The reason for that specificity is a failure I have seen: A recommendation change shipped on engagement while the page latency guardrail was up 84 ms with an interval spanning zero, and the latency regression cost more conversions across the quarter than the recommendation change gained.
Guardrails are read against a bound, not against zero.
| Guardrail | Bound | Estimate | 95% interval | Verdict |
|---|---|---|---|---|
| p95 latency | +50 ms | +84 ms | -12 to +180 ms | block, interval exceeds bound |
| Error rate | +0.1 pp | +0.02 pp | -0.03 to +0.07 pp | pass |
| Unsubscribes | +5% | +1% | -2% to +4% | pass, interval inside bound |
I would not consider it settled without evidence: Report each guardrail as an interval against its bound rather than as a pass or fail, so a test too small to detect the harm is visibly too small.
Failing to reject harm is not the same as showing there is none.
Curated: · Written: · Reviewed:
Q&A-2A stakeholder asks how long an A/B test needs to run. What do you need from them before you can answer, and how do you compute it?
What I need before I can answer
Three numbers and one decision rule.
- The baseline rate of the primary metric: current conversion, click-through, whatever it is. Without it, "5% lift" means nothing.
- The smallest effect that would change the decision: the MDE. Stakeholders skip this one, and it is the one that sets the duration.
- Eligible traffic per day and the split.
- The decision rule: what ships at +0.5%, at +2%, at flat? If no outcome changes what you do, don't run the test.
Then I compute.
The computation
For a two-proportion test at 5% two-sided alpha and 80% power, sample per arm is roughly 16·p(1−p)/δ², where p is the baseline and δ the absolute effect. (16 is (1.96+0.84)² rounded.)
from math import ceil
def per_arm(baseline: float, relative_lift: float) -> int:
delta = baseline * relative_lift
return ceil(16 * baseline * (1 - baseline) / delta**2)
for lift in (0.05, 0.02, 0.01):
n = per_arm(0.040, lift)
print(f"{lift:.0%} lift -> {n:,} per arm -> {ceil(2 * n / 50_000)} days at 50k/day")
At a 4% baseline and 50k eligible users/day, that loop prints:
| Relative lift | δ (absolute) | n per arm | Duration |
|---|---|---|---|
| 5% | 0.002 | 153,600 | 7 days |
| 2% | 0.0008 | 960,000 | 39 days |
| 1% | 0.0004 | 3,840,000 | 154 days |
The same test is a one-week test or a five-month test depending entirely on the effect you claim to care about. Round up to whole weeks so every day of the week is represented equally.
Trade-offs, and when not to use this
Fixed-horizon sizing assumes you fix n in advance and don't look until it's done. If you peek and stop when p < 0.05, the false positive rate is no longer 5%: five equally spaced unadjusted looks push it to roughly 14%. If the business needs continuous monitoring (guardrails, safety, revenue), use a sequential design instead: group-sequential with alpha spending (O'Brien-Fleming) or an always-valid confidence sequence. The cost is a modestly larger maximum sample; the benefit is stopping early on a large effect.
CUPED is orthogonal and usually worth it: use the pre-period value of the metric as a covariate. If pre and post correlate at ρ, variance falls by ρ² and required sample falls by the same fraction. At ρ = 0.7 that is roughly half the sample: 154 days becomes ~77.
Fixed-horizon is the wrong tool when traffic is unpredictable and you can't commit to n, or when the metric is a ratio with a moving denominator (revenue per session when sessions shift).
Failure mode
The one I've seen: a team powered a test for a 5% lift, ran it ten days, and reported flat as "the feature does nothing." Ten days buys 5% detection at this traffic; a 1% effect needs 154. The readout could not have seen anything smaller than 5%. Report the MDE next to every null result.
Curated: · Written: · Reviewed:
Q&A-11Your experiments are underpowered and you cannot get more traffic. What can you do with the data you already have?
Underpowered means the confidence interval is too wide to decide, not that the data is useless. With no more traffic you have three levers: cut variance on the same users, change the inference procedure, or change what you measure and how you pool. The highest-yield move is usually variance reduction with a pre-experiment covariate.
CUPED (controlled experiments using pre-experiment data) works because the covariate X is fixed before randomisation, so it cannot be affected by treatment. Regress the outcome Y on X and subtract the predictable part: Y_adj = Y - theta*(X - mean(X)), where theta = Cov(Y,X)/Var(X). The treatment effect estimate is unchanged in expectation; the variance drops to 1 - rho^2 of the original, where rho is the correlation between Y and X. With rho = 0.8, that is 36% of the original variance and a 40% narrower interval.
import numpy as np # Python 3.11, numpy 1.26
# y: post-period metric, x: pre-period covariate, t: 0/1 treatment
theta = np.cov(y, x, ddof=1)[0, 1] / np.var(x, ddof=1)
y_adj = y - theta * (x - x.mean())
# run the usual difference in means on y_adj
Same 60,000 users, same effect:
| Estimator | Estimated lift | 95% interval | Interval width |
|---|---|---|---|
| Difference in means | +1.9% | -0.4% to +4.2% | 4.6 pp |
| CUPED, rho = 0.8 | +1.9% | +0.5% to +3.3% | 2.8 pp |
The failure mode is a covariate window that overlaps the experiment. A team I worked with used a pre-period that ran two days into the treatment window; treatment leaked into the covariate and biased the estimate toward zero by about a fifth. Detect it by checking covariate balance across arms (standardised mean difference, not just a p-value) and verifying every covariate timestamp is strictly before assignment. Run the adjusted estimator on an A/A split: it should return zero with a narrower interval.
The other branches, in the order I would try them:
| Move | What it buys | When it applies |
|---|---|---|
| Sequential / always-valid inference | Same power, but you can stop early when the effect is large | You need a decision sooner and will peek |
| Metric change: proxy, winsorize, delta method | Higher sensitivity | Heavy tails or ratio metrics |
| Switchback / cluster designs | Reduces interference bias | Marketplace or network effects |
| Pooling past experiments (meta-analysis) | Borrows strength across experiments | Same metric, similar population |
| Prioritisation / decision-theoretic | Ship on directional evidence | Cost of error low, cost of delay high |
Sequential tests do not add power; they let you spend alpha over time so peeking is legal. A proxy metric that moves earlier can be worth more than a noisy long-run metric. Pooling only works if the experiments share a metric and population; otherwise you are averaging apples and oranges.
If none of these narrow the interval enough, the data cannot settle the question. Decide on the cost of being wrong versus the cost of waiting, and say so explicitly.
Curated: · Written: · Reviewed:
Q&A-101Traffic is fixed at four weeks and the team wants to know if the test is even worth running. How do you compute the smallest lift it can detect, and what do you do when that lift is bigger than any effect the team would act on?
The short answer
I need four things before I can size anything: the baseline rate of the metric (or its mean and per-user variance), the smallest effect worth shipping, alpha, and power. Then I invert the power formula for whichever quantity is fixed. If traffic is fixed, I solve for the minimum detectable effect and tell the team what lift this test can and cannot see: before launch, not after.
The inputs, and where variance enters
| Input | Where it comes from | Effect on n |
|---|---|---|
| Baseline p (or mean μ, variance σ²) | Last 4 weeks, same population, same metric definition | n ∝ p(1−p) for rates; n ∝ σ² for means |
| MDE Δ | Smallest lift that would change a decision | n ∝ 1/Δ² |
| α (two-sided) | 0.05 default; 0.10 if a false positive is cheap | n ∝ (z₁₋α/₂)² |
| Power 1−β | 0.80 default; 0.90 for expensive or one-shot tests | n ∝ (z₁₋β)² |
For a two-proportion test, per arm:
n ≈ (z₁₋α/₂ + z₁₋β)² · [p₁(1−p₁) + p₂(1−p₂)] / (p₁−p₂)²
At α = 0.05 and power 0.80 the leading constant is (1.96 + 0.84)² = 7.85.
Worked arithmetic
Conversion at p = 5%, want to detect a 10% relative lift (0.5pp):
n ≈ 7.85 · (2 · 0.05 · 0.95) / 0.005² = 7.85 · 0.095 / 0.000025 ≈ 29,800 per arm, ~60k users total.
Revenue per user, σ = $40, want to detect $0.50:
n ≈ 2 · 7.85 · 40² / 0.5² = 2 · 7.85 · 1600 / 0.25 ≈ 100,000 per arm.
Same traffic, wildly different answer, because n scales with σ², not with the mean. That is the single most useful thing to say in the room.
Inverting: with 15,000 per arm and p = 5%, MDE ≈ 2.80 · √(2 · 0.05 · 0.95 / 15000) ≈ 0.70pp, about 14% relative. If the team would only ship on a 3% lift, this test cannot answer their question.
When required n exceeds traffic
In rough order of preference:
- Cut variance, not sample. CUPED with a pre-period covariate typically removes 30–60% of σ², and since n ∝ σ² that is a 1.4–2.5× reduction in required n. Pre-registered winsorizing at the 99.9th percentile does the same for revenue.
- Change the metric. Binary conversion is far less variable than revenue per user. If the intervention acts on conversion, power on conversion.
- Accept a larger MDE and write it down. "This test detects ≥8% relative at 80% power; a null is not evidence of no effect." That sentence prevents the worst outcome, which is shipping on a null.
- Extend the window, but only within novelty and seasonality limits, usually 2–4 weeks. Longer is not free.
- Don't run it. If the MDE is 15% and the plausible effect is 2%, the test is a coin flip with extra steps. Propose a bigger intervention, a longer-horizon holdout, or a different design.
Failure modes
- Dilution. If only 40% of treated users are exposed, the ITT effect is 0.4× the exposed effect. To detect a 5% effect among exposed you must power for 2% ITT: 6.25× the sample.
- Wrong variance. Using per-session variance when randomisation is per-user understates σ² by the design effect 1 + (m−1)ρ. At m = 5 sessions per user and ρ = 0.3, that is 2.2×.
- Peeking. The calculation assumes one look. Continuous monitoring at α = 0.05 pushes the false-positive rate toward 30%; an always-valid or group-sequential boundary costs 10–20% more sample.
- Baseline from the wrong slice. A rate measured on all users when the test runs on new users only will be wrong in the direction that matters.
Curated: · Written: · Reviewed: —
Q&A-104Traffic is fixed for the quarter and the effect you need to detect is half the size your last test could resolve. How do you get there?
With a fixed traffic budget, the only way to detect a smaller effect is to reduce the variance of the estimator. Sample size scales as n ∝ σ²/Δ², so halving the detectable effect Δ requires four times the users. If you can't get them, you cut σ².
The standard tool is CUPED (controlled experiments using pre-experiment data). Pick a pre-treatment covariate X: usually the same metric measured in the pre-period, e.g. revenue per user in the two weeks before the test. Fit θ = Cov(Y, X) / Var(X) on pre-period data (or on the control arm), then analyse the adjusted outcome:
Y_adj = Y - θ(X - X̄)
Because X is measured before randomisation, it is independent of treatment assignment, so E[Y_adj | T=1] - E[Y_adj | T=0] = E[Y | T=1] - E[Y | T=0]. The treatment effect estimate stays unbiased. The variance of the difference becomes (1 - ρ²) times the original, where ρ is the correlation between X and Y.
Worked example. Your last test could resolve a 2% lift on revenue per user with 100,000 users per arm. You need to detect 1%: a quarter of the effect size, so four times the sample, 400,000 per arm. You have 100,000. Pre-period revenue correlates with post-period revenue at ρ = 0.9. Variance reduction factor is 1 - 0.81 = 0.19, so effective sample size multiplies by 1/0.19 ≈ 5.26. Your 100,000 users now behave like 526,000. The smallest detectable effect shrinks by √5.26 ≈ 2.29, so you can resolve 2% / 2.29 ≈ 0.87%: slightly better than the 1% you needed. At ρ = 0.7 the multiplier is only 1.96, so you'd resolve 2% / 1.4 ≈ 1.43% and still be short.
Other levers:
- Unit choice. Randomising at a coarser unit (user instead of session, account instead of user) can reduce variance if outcomes are correlated within the unit, but it costs you sample size because you have fewer independent units. Randomise at the level where the treatment is applied.
- Stratification or block randomisation on the pre-period metric. This is CUPED-free variance reduction: sort users by pre-period revenue, form blocks, randomise within blocks. It removes between-block variance and guarantees balance.
- One-sided vs two-sided. A one-sided test at α = 0.05 has the same power as a two-sided test at α = 0.10, so it buys you roughly 20% sample size reduction, but only if you genuinely never care about detecting harm, and you must pre-register it. Switching after seeing the data is p-hacking.
- Longer runs. Extending the test increases n, but with a fixed traffic budget you're just spreading the same users over more days. It also exposes you to novelty effects: the early lift from a new UI often decays, so the average effect over a longer window can be smaller and the variance estimate is contaminated by time trends.
What to avoid:
- Post-treatment covariates. Using a metric measured after the treatment starts (e.g. clicks during the test) as a covariate breaks the independence assumption and biases the effect estimate. The covariate must be pre-treatment.
- Peeking. Checking results repeatedly and stopping when significant inflates the false positive rate. With CUPED you still need a fixed horizon or a sequential test.
- Fitting θ on the full experiment data including the treatment effect. In practice fitting on pre-period data or the control arm is safest; the pooled estimator is unbiased but can be slightly less efficient if the treatment changes the covariate-outcome relationship.
Detect failure by checking covariate balance across arms before the test and verifying the empirical variance reduction matches 1 - ρ².
Curated: · Written: · Reviewed: —
Q&A-1Your team wants to A/B test a new checkout flow and asks you to "measure everything". How do you decide what the experiment is actually powered on?
Core answer, first 90 seconds
Assume a two-arm randomised split, sessions as the unit, 50/50, fixed horizon, one primary metric. The primary metric is the one the ship decision turns on. Everything else is either a guardrail with a bound stated in advance or an exploratory readout with no decision authority. I write the decision rule and the powering calculation before traffic splits, and I size the experiment for the primary metric alone.
For a checkout flow the primary metric is revenue per session. It is the number the business actually ships on. Conversion rate is the tempting alternative and it is the wrong one here: a new flow can lift conversion while dropping revenue per session if it pushes people onto smaller baskets or free-shipping thresholds.
The arithmetic that sets the sample
Two-sided alpha 0.05 (z = 1.96), power 0.80 (z = 0.84):
n per arm = 2 * (1.96 + 0.84)^2 * sd^2 / delta^2 = 15.7 * sd^2 / delta^2
Hypothetical figures for the worked example: revenue per session has mean $100 and sd $60. MDE 1.5% relative = $1.50. sd/delta = 40, squared = 1600, so 15.7 * 1600 = 25,100 sessions per arm, 50,200 total. At 5,000 sessions/day that is 10 days.
Ten days is often too long, so pre-period covariance adjustment is the lever. If a user's pre-experiment revenue correlates with their in-experiment revenue at rho = 0.6, CUPED removes rho^2 = 36% of the variance, and variance scales the sample linearly: 25,100 * 0.64 = 16,100 per arm, about 6.4 days. The other lever is a larger MDE, never a second primary metric.
Why "measure everything" is the trap
Fourteen metrics each tested at alpha = 0.05 give a probability of at least one chance-significant result of 1 - 0.95^14 = 51%. A launch decision made on whichever metric reached significance is close to a coin flip. That is the entire argument for one primary metric, and it is arithmetic, not taste.
| Field | Committed value |
|---|---|
| Primary metric | Revenue per session |
| MDE | 1.5% relative (~$1.50 at a $100 mean) |
| Sample | ~25,100 sessions per arm at 80% power, alpha 0.05 |
| Guardrails | Checkout error rate (<= +0.2 pp), p95 page latency (<= +50 ms) |
| Decision rule | Ship if primary is significant at alpha 0.05 and the point estimate clears the MDE; block if any guardrail crosses its bound |
| Secondary (no authority) | Add-to-cart rate, coupon usage, support contacts |
Alternatives and when to pick them
A composite overall evaluation criterion instead of a single primary makes sense when no one number captures the decision; its cost is that a collapsed component hides inside the average, so it still needs guardrails. A sequential or always-valid test lets you read daily and stop early, at roughly 20-30% more sample than a fixed-horizon test of the same power; take it when the product genuinely cannot wait 10 days. If the primary is a ratio, randomise by the denominator unit and use a delta-method or bootstrap variance, because a plain t-test understates it.
Failure modes and how I detect them
Metric switching after the fact. Detect with a timestamped pre-registration doc, and run quarterly A/A tests to confirm the pipeline's false-positive rate really is 5%.
Variance miss on heavy-tailed revenue. Detect by comparing the realised sd at readout against the sd the sample was sized on. A larger realised sd means the true MDE is wider than registered and the test is underpowered regardless of the p-value.
Sample ratio mismatch. Detect with a chi-square test on assignment counts, alarm at p < 0.001. A skewed split invalidates the readout no matter which metric moved.
Guardrail regression. Detect against the pre-stated non-inferiority bound: error rate up more than 0.2 percentage points, or p95 latency up more than 50 ms, blocks the ship even with a clean primary.
Peeking at a fixed-horizon p-value. Detect by logging readout access; if anyone looked on day 3 and you are not running a sequential test, that p-value is not interpretable.
Curated: · Written: · Reviewed:
Q&A-4A 50/50 experiment delivered 51,204 users to control and 49,102 to treatment. Should you read the result?
My answer to sample ratio mismatch begins with how the data came to exist, because sampling contaminates everything after it.
A split that deviates from its intended ratio by more than chance allows means the randomisation or the logging is broken, and the treatment effect estimated from a broken assignment is not interpretable at any p-value.
Concretely, run a chi-square goodness-of-fit test against the intended ratio, treat a small p-value as a defect rather than a finding, and debug the pipeline: bot filtering applied after assignment, a redirect that drops slow clients, exposure logged on render rather than on assignment: before reading any metric.
The reason for that specificity is a failure I have seen: A mismatch of roughly 1% was dismissed as noise for three experiments before someone noticed the treatment bundle timed out on slow connections, which removed the least engaged users from treatment and manufactured a lift in every engagement metric.
The check is one chi-square line, and 1% is not noise at this scale.
from scipy.stats import chisquare
observed = [51_204, 49_102]
expected = [sum(observed) / 2] * 2
stat, p = chisquare(observed, expected)
print(f"chi2={stat:.1f} p={p:.2e}") # chi2=44.0 p=3.2e-11
I would not consider it settled without evidence: Compute the split test automatically on every experiment and block the readout when it fails, rather than leaving it to whoever remembers to look.
A broken assignment does not produce a noisy estimate; it produces a confident wrong one.
Curated: · Written: · Reviewed:
Q&A-5You are testing a change to a shared team workspace. Do you randomise users, sessions, or accounts, and what breaks if you choose wrong?
I would treat choosing the unit of randomisation as a design question first and a computation question second.
Randomise at the level at which the treatment is delivered and at which the outcome is correlated, because units inside the same account see each other's treatment and their outcomes are not independent.
Concretely, pick the coarsest unit that still gives enough independent units for the required power, randomise on that unit's identifier, and compute standard errors clustered at the same level rather than at the row level.
The reason for that specificity is a failure I have seen: A workspace feature randomised per session gave both arms to the same team, so half the treated behaviour was actually control users reacting to a colleague's change, and the measured effect was roughly a third of what a team-level rerun found.
Same experiment, two units, and the interval that changes.
| Randomisation unit | Independent units | Estimated lift | 95% interval |
|---|---|---|---|
| Session | 412,000 | +0.9% | +0.6% to +1.2% |
| User | 96,000 | +1.4% | +0.7% to +2.1% |
| Account (correct) | 3,100 | +2.6% | +0.4% to +4.8% |
I would not consider it settled without evidence: Compare the row-level standard error against the cluster-robust one on the same data; a large gap says the unit of analysis and the unit of randomisation disagree.
The unit of analysis has to match the unit of assignment, or the interval is fiction.
Curated: · Written: · Reviewed:
Q&A-38Your rows are page views but your treatment was assigned per user. What goes wrong if you ignore that?
What goes wrong
Two things, and only one of them is about standard errors.
- The standard errors are too small. Page views from the same user share unobserved user-level shocks: a heavy user is heavy all week. Treating 180,000 rows as 180,000 independent observations overstates the information in the data. The effective sample size is closer to the number of users than the number of rows.
- The estimand changes. A page-view-level regression weights each user by their view count, so it estimates a view-weighted average effect. Randomization was at the user level, so the quantity it identifies is the user-average effect. Those differ whenever heavy users respond differently from light ones.
Mechanism
With m rows per user and intra-cluster correlation ρ, the variance of the treatment effect is inflated by the design effect DEFF = 1 + (m − 1)ρ, so the standard error is inflated by √DEFF. At m = 18 and ρ = 0.14, DEFF ≈ 3.4 and the SE roughly doubles. The point estimate does not move; only the interval does.
Worked example (hypothetical, but internally consistent)
180,000 page views from 10,000 users, 18 views each, treatment assigned per user.
| Analysis | Rows | Effect | SE | p |
|---|---|---|---|---|
| Page-view rows, treated as independent | 180,000 | 0.0069 | 0.0021 | 0.001 |
| Page-view rows, SEs clustered by user | 180,000 | 0.0069 | 0.0039 | 0.08 |
| One row per user (user mean) | 10,000 | 0.0058 | 0.0039 | 0.14 |
The naive analysis calls it significant at p = 0.001. Clustering the SEs by user keeps the point estimate and widens the interval to p = 0.08. Aggregating to one row per user also moves the point estimate, from 0.0069 to 0.0058, because the user-average effect weights a light user the same as a heavy one; p = 0.14.
What to do
- Cluster at the assignment unit, the user, not at the row level and not at an intermediate level like session.
- Or aggregate to one row per user before testing. With equal cluster sizes the two agree closely; with unequal sizes they answer different questions, so pick the one matching your estimand.
- Report ρ and DEFF next to the result so the inflation is visible rather than assumed away.
- Check the cluster count. 10,000 users is plenty. Below roughly 40 clusters, cluster-robust SEs are themselves downward biased; use a wild cluster bootstrap or randomization inference at the user level.
Failure modes
- Few clusters: SEs stay too small, and the t-approximation with G − 1 degrees of freedom is poor.
- Cluster size correlated with treatment: if the treatment arm happens to hold the heavy users, the view-weighted estimate is biased for the user-average effect, and clustering SEs will not fix it.
- Clustering at the wrong level: clustering at session when assignment is per user leaves the user-level correlation in the errors.
- Believing clustering fixes the estimand: it does not. Clustering widens the interval around the view-weighted estimate; only aggregation or weighting gives you the user-average effect.
Ten thousand rows from ten thousand users is ten thousand observations. Ten thousand rows from five hundred users is five hundred observations wearing a costume.
Curated: · Written: · Reviewed:
Q&A-3Six days into a two-week test the dashboard shows p = 0.03. The product manager wants to ship today. What do you say?
No. Not on this number, and not today.
A p-value of 0.03 is a 3% false-positive rate only if the test was analysed once, at the horizon it was designed for. Six days into a two-week test, it wasn't.
Assumptions I'd state out loud before answering: the test was pre-registered for 14 days, there is one primary metric, the plan has no sequential design or alpha-spending boundary, and nobody has adjusted for the peeks already taken. If any of those is false, if the plan already specified a day-6 interim with a spending function, then 0.03 may be a valid boundary crossing and I'd want to see the boundary before saying anything.
Under a true null, a fixed-horizon p-value is uniform on [0, 1]. Every extra look is another draw from that distribution, and you stop at the first draw below 0.05. The probability that at least one of k looks crosses is not 5%; it climbs with k. Daily peeks across 14 days puts it near 20%.
10,000 A/A simulations, true effect zero, two-proportion test, 14 days of accrual, daily peeks:
| Stopping rule | Times "significant" | Effective false positive rate |
|---|---|---|
| Fixed horizon, day 14 only | 503 of 10,000 | 5.0% |
| Peek daily, stop at first p < 0.05 | 1,970 of 10,000 | 19.7% |
| Alpha-spending boundary, peek daily | 486 of 10,000 | 4.9% |
What I'd actually say to the PM is three options, in order of preference.
- Wait. Report the day-14 fixed-horizon p. If it is still under 0.05, ship with a claim that holds up.
- Ship now on expected value, not on significance. If the feature is cheap to reverse and the downside is bounded, ship it as a staged rollout with a holdback and make no significance claim. That is a business decision, and it is honest about what the data supports.
- For the next test, pre-register the sequential design: O'Brien-Fleming alpha spending with a small alpha at the interim, or an always-valid confidence sequence (mSPRT) you can monitor continuously. Pick it before the test starts, not after a favourable peek.
The failure mode is not hypothetical. A team I worked with peeked daily on a two-week test and shipped on the first significant day. Their effective false positive rate was near 20%, and three of the five features they shipped that quarter failed to reproduce in a holdout. Detection is cheap: simulate the team's actual stopping rule on A/A data (same traffic, same peek schedule, same threshold) and count how often it declares a winner. That number describes the process; the single p-value does not.
The p-value describes the procedure that produced it. Daily peeking is a different procedure.
Curated: · Written: · Reviewed:
Q&A-40Leadership wants a live dashboard they can act on at any moment. How do you make that statistically legitimate?
What I'd say in the first 90 seconds. The dashboard's problem isn't the interval, it's the stopping rule. "Act at any moment" means leadership picks the moment to stop after seeing the data, and a fixed-horizon 95% interval has no coverage guarantee under that. So before touching the chart I'd pin down two things: which metric is the decision metric, and what is the smallest effect worth acting on. Then I'd publish an interval that is valid under optional stopping: a confidence sequence, or a group-sequential boundary with a pre-registered look schedule, and keep the fixed-horizon estimate as the final readout once the planned sample is in.
Why the fixed-horizon interval breaks. A p-value or interval computed at one look assumes one look. Check the same data five times and flag anything under 0.05, and the family-wise error is 1 − 0.95⁵ = 22.6%, not 5%. Under genuinely continuous monitoring it is worse than that: a random walk crosses any fixed boundary with probability 1 (law of the iterated logarithm), so the realised type-I error drifts toward 1. The dashboard isn't lying about the arithmetic; it's lying about the guarantee.
Two ways to fix it.
Group sequential. Pre-specify K looks and an alpha-spending function (Lan–DeMets). O'Brien–Fleming-style boundaries spend almost nothing early and converge to the fixed-horizon critical value at the end, so you pay little sample size; Pocock-style boundaries spend evenly, are easier to explain to leadership, and cost more. The catch is that the schedule is part of the design: a dashboard that looks whenever someone opens the tab is not a group-sequential design.
Confidence sequences / always-valid p-values. A sequence of intervals (Lₜ, Uₜ) with P(θ ∈ (Lₜ, Uₜ) for all t) ≥ 1 − α. Valid at every t with no schedule, which is what a live dashboard actually needs. The mSPRT construction gives always-valid p-values from a likelihood ratio against a mixture alternative.
The cost is width, and it is front-loaded. Illustrative widths for a two-arm test, 95% two-sided; the ratio, not the absolute numbers, is the point:
| Day | Fixed-horizon width | Always-valid width |
|---|---|---|
| 2 | 6.1 pp | 14.8 pp |
| 7 | 3.2 pp | 5.6 pp |
| 14 | 2.3 pp | 3.4 pp |
| 28 | 1.6 pp | 2.2 pp |
Early on the always-valid interval is roughly 2–3× wider and it converges to within ~1.2× of the fixed-horizon width by the planned end. That is the honest price of being able to look, and it should be on the dashboard, not hidden.
Failure modes I'd guard against.
- Multiplicity. Twelve metrics at 95% gives 12 × 0.05 = 0.6 expected false flags per read even with valid intervals. Pre-register one primary metric; label the rest directional.
- Non-stationarity. Novelty and primacy effects, ramp-up, bot traffic, weekday mix. The interval stays valid for the estimand, but the estimand moves. Detect with a sample-ratio-mismatch chi-square and a pre-period A/A test.
- A stopping rule nobody wrote down. Simulate the dashboard's actual viewing behaviour under a null effect and report the realised error rate for that behaviour, not the nominal one.
If people will look continuously, the interval has to be built for continuous looking, and the decision rule written down before the first look.
Curated: · Written: · Reviewed:
Q&A-36Your company reports experiments as "probability treatment is better". What does that require, and what changes?
Reporting "probability treatment is better" means reporting P(θ > 0 | data): a posterior probability. It requires three things a p-value does not: a prior on the effect, a likelihood for the metric, and a pre-registered decision threshold. It changes three things: when you are allowed to stop, what the threshold means, and what has to be written down before the experiment starts.
What it requires
- A prior on the effect. P(θ > 0 | data) ∝ prior × likelihood. The prior is part of the number, not a nuisance parameter you can leave implicit.
- A likelihood for the metric. Normal on relative lift, beta-binomial on conversion, whatever matches the metric's scale. The probability is only defined relative to that model.
- A threshold. 0.95 is a convention, not a significance level. It has to be chosen and written down before the readout.
What changes
Stopping. Under a fixed-horizon frequentist test, peeking inflates Type I error. A posterior is valid at any stopping time: you can look daily and stop when P(θ > 0) crosses the threshold, and the posterior is still the posterior. What you cannot do is pick the threshold after seeing the curve. Pre-register it, or you have just moved the goalposts.
Threshold semantics. 0.95 is not α = 0.05. With a flat prior and a normal likelihood, P(θ > 0) = 1 − (one-sided p). So a 95% bar is a two-sided p of 0.10, not 0.05. If you want the old bar, the threshold is 0.975. And the threshold should come from expected loss, not habit: if shipping a regression costs 10× a missed win, the bar sits below 0.95.
What the number means. The probability is over the parameter given this data and this prior, not over hypothetical repetitions. It does not come with a long-run error rate, so you cannot tell finance "5% of shipped wins will be false." That guarantee is gone and has to be replaced by prior sensitivity and a pre-registered threshold.
Governance. The prior and the threshold get pre-registered and versioned like the metric definition. The prior's provenance is part of the experiment record. With many metrics, either pre-register one primary metric or use a hierarchical prior: a per-metric P(θ > 0) with no multiplicity story is the same multiple-comparisons problem in new clothes.
The failure mode
A prior fitted on shipped experiments only. Shipped experiments are a selected sample: they won because they won. An optimistic prior centred at +3% with sd 2% gives P(θ > 0) = 0.93 with no data at all. The readout then mostly reports the prior.
Illustrative numbers, normal likelihood, observed relative lift +1.4%, standard error 1.3%:
| Prior on relative lift | Posterior mean | P(effect > 0) |
|---|---|---|
| Flat | +1.4% | 0.86 |
| Historical, mean 0, sd 2% | +1.0% | 0.82 |
| Optimistic, mean +3%, sd 2% | +1.9% | 0.96 |
The last row is the problem: the prior alone already gives 0.93, so the data moved the number three points. Report the posterior under the chosen prior and under a flat one. A gap of more than a few points means the conclusion is carried by the prior, and the prior is the thing to argue about.
A posterior with an undisclosed prior is an opinion with an interval attached.
Curated: · Written: · Reviewed:
Q&A-37You want to show a cheaper model is "no worse" than the current one. Why does a non-significant difference not prove that?
The first thing I would pin down about equivalence and non-inferiority testing is which question the number is supposed to answer.
Failing to reject a difference is compatible with a large difference the study could not detect, so demonstrating similarity requires testing against a stated margin rather than against zero.
Concretely, agree the largest regression that would still be acceptable, size the study to detect that margin, and conclude non-inferiority only when the confidence interval for the difference lies entirely on the acceptable side of it.
The reason for that specificity is a failure I have seen: A vendor swap was approved on p = 0.44 from a study whose interval ran from -8% to +3%, and the eventual regression was 5%. Well inside what the study had never ruled out.
Three results against a -2% non-inferiority margin.
| Study | Difference | 95% interval | Non-inferior |
|---|---|---|---|
| A | -0.3% | -1.2% to +0.6% | yes |
| B | -0.3% | -8.0% to +7.4% | undetermined, too small |
| C | -3.1% | -4.0% to -2.2% | no |
I would not consider it settled without evidence: Compare the confidence interval against the agreed margin explicitly in the readout, rather than reporting the difference's p-value against zero.
Proving similarity means excluding the difference that matters, not failing to find one.
Curated: · Written: · Reviewed:
Q&A-21Your experiment ends with an estimate of +0.2% and a 95% interval from -1.1% to +1.5%. What is your recommendation?
Recommendation: don't ship on this result. The interval decides, not the point estimate. −1.1% to +1.5% is consistent with a 1.1% regression and a 1.5% gain, so it rules out neither. +0.2% is not evidence of a small positive effect; it is the centre of an interval that contains zero.
Two assumptions drive the call. First, there is a minimum effect worth shipping, δ: the smallest lift that pays for the feature's build and its ongoing cost. Second, shipping a neutral-or-negative change is not free. If δ is around +1.0%, the answer is extend or redesign. If δ is +1.5% or higher, only large effects justify the work, the interval sits entirely below δ and you decline.
Now check whether the test could have decided at all. The interval is symmetric, so the standard error is half-width / 1.96 = 1.3 / 1.96 ≈ 0.66 percentage points. The minimum detectable effect at 80% power, two-sided 5%, is (1.96 + 0.84) × SE ≈ 2.80 × 0.66 ≈ 1.9pp. The design could only reliably detect effects of roughly 1.9pp or larger: bigger than a 1.0% ship threshold. It was never sized to answer the question it was asked. That is the finding to report, not "no effect."
Sizing the extension: to detect 1.0pp at 80% power you need SE ≈ 1.0 / 2.80 ≈ 0.36pp. Since SE scales as 1/√n, that is (0.66 / 0.36)² ≈ 3.4× the sample. "Extend" means roughly 3–4× the traffic, not another week.
| Estimate | 95% interval | Ship threshold δ | Reading |
|---|---|---|---|
| +0.2% | −1.1% to +1.5% | +1.0% | inconclusive; MDE 1.9pp > δ, extend ~3.4× |
| +0.2% | −1.1% to +1.5% | +1.5% | interval below δ, decline |
| +0.2% | −1.1% to +1.5% | +0.1% | straddles δ, still undecided |
One case where shipping is defensible: the downside is bounded, the change is cheap to revert, it adds no infrastructure cost, and you can monitor it. Then ship and watch, but call it a bet under uncertainty, not a win.
Failure modes worth naming. Reading "not significant" as "no effect" and killing the feature: a later, larger test found +0.9% that the original design could never have detected, and the removal itself cost a quarter of rework. Stopping the moment the interval first excludes zero in either direction. Reporting the point estimate without the interval, which hides exactly the information the decision needs.
So the practice to carry: report every null as an interval alongside the design's MDE, so "we saw nothing" and "we could not have seen it" stay distinguishable. Absence of evidence is evidence of absence only when the design could have found it.
Curated: · Written: · Reviewed:
Q&A-22Your team ships 30 experiments a quarter, each with a small positive effect. How do you know the sum is real?
First I'd clarify what "the sum is real" means, because it can mean two things. It can mean the aggregate lift on the north-star metric equals the sum of the individual readouts, or it can mean the shipped changes together moved the metric at all. The first is almost never true. The second is what you actually have to defend, and it needs a persistent holdout to measure.
The assumptions I'd state up front are that each experiment was randomized at the user level, that the control is the current production experience, and that the north-star is something like revenue or weekly active usage measured over a comparable window.
Here's the direct answer. The sum of shipped effects is a forecast, not a measurement, and it's biased upward for three structural reasons.
First, the baseline moves. Experiment 12's control already contains the wins from experiments 1 through 11, so its measured lift is incremental to a baseline that has already improved. Adding the readouts assumes the effects compose additively on top of each other, and they don't when two changes touch the same funnel step.
Second, selection. You only ship experiments that cleared significance, so the shipped set is a truncated sample of the effect distribution. Winner's curse means the reported effect of a shipped experiment is on average larger than its true effect, because the ones that got lucky are the ones that passed.
Third, interference and dilution. Two changes to the same surface can overlap, and a change that helps a small segment gets diluted in the aggregate metric.
So I'd carve out a persistent holdout: a fixed random slice of users, say 5%, excluded from every launch for a fixed period, a quarter or a year, and not used for any other experiment. At the end of the period you compare the treated population against the holdout on the north-star. That gap is the accountable number.
Illustrative numbers, not from a real deployment:
| Quarter | Sum of shipped effects | Holdout-measured effect | Ratio |
|---|---|---|---|
| Q1 | +3.8% | +1.2% | 0.32 |
| Q2 | +4.1% | +1.5% | 0.37 |
| Q3 | +3.2% | +0.9% | 0.28 |
The ratio is the useful part. If the holdout shows roughly a third of the summed estimate, you discount future forecasts by that factor and you stop reporting the sum as if it were the result.
Failure modes to watch. Holdout contamination, where someone ships a change to the holdout slice by accident: detect it with a sample-ratio-mismatch check on every launch and an alert if holdout exposure is nonzero. A holdout that's too small, where the aggregate effect is 1-2% and the holdout is 1% of traffic, so you can't resolve it; size the holdout against the smallest aggregate effect you care about, not against a single experiment. Drift, where holdout users behave differently over a long window, so compare on a pre-registered metric and check for differential churn. And the holdout costs you the revenue you're withholding, which is the real price of the measurement.
Alternatives when a persistent holdout isn't viable. A switchback or staggered rollout if you can't withhold users, or a hierarchical model over the experiment set that shrinks individual estimates toward the pooled mean. Those are weaker, because they model the composition rather than measure it.
The sum of the wins is a forecast; the holdout is the measurement.
Curated: · Written: · Reviewed:
Q&A-7A redesign shows a 9% engagement lift in week one and 2% in week three. Which number do you report?
I would settle novelty and primacy effects against the simplest defensible comparison, so any extra structure has to earn its place.
Report the effect on the population and horizon the decision applies to, which means a stabilised estimate from users who have lived with the change, not the first-exposure reaction that a permanent rollout will never repeat.
Concretely, split the readout by time since first exposure rather than by calendar week, plot the effect against exposure age, and take the decision number from the range where the curve has flattened; extend the test if it has not flattened.
The reason for that specificity is a failure I have seen: A navigation redesign shipped on a 9% first-week lift and settled at a 1.1% loss by week six, and because the rollout was global there was no untreated group left to detect it.
Effect by exposure age, which is not the same as calendar week.
| Days since first exposure | Estimated lift | 95% interval |
|---|---|---|
| 0-2 | +9.1% | +7.8% to +10.4% |
| 3-7 | +4.6% | +3.5% to +5.7% |
| 8-21 | +2.0% | +0.9% to +3.1% |
| 22-56 | -1.1% | -2.0% to -0.2% |
I would not consider it settled without evidence: Hold back 5% of traffic from the rollout for eight weeks and compare it against the treated population once the novelty window has passed.
The first week measures how different the change is, not how good it is.
Curated: · Written: · Reviewed:
Q&A-20The outcome you care about is 12-month retention, but the test runs for three weeks. What do you do?
I would not treat the three-week test as a 12-month retention test. I’d run the three weeks for mechanism and guardrails, but I would not make the retention call from it. The decision comes from one of three things: a long-term holdout, a surrogate validated on the same class of change, or a bounded launch with a pre-committed rollback. Which one depends on the cost of being wrong and whether the change is reversible.
| Option | What it gives | Cost | When to use |
|---|---|---|---|
| Extend to 12 months | Direct retention read | Slow, seasonal, interference | Irreversible, high-stakes change |
| Long-term holdout | Direct retention on 1–5% held back | Foregone lift on holdout | Launch can be staged |
| Surrogate | Fast read from short-term metric | Needs validation, can invert | Same change class as calibration |
| Ship with guardrail | Fastest path | Only catches bounded, reversible harm | Downside is small and rollback is real |
If I use a surrogate, I fit the mapping on completed experiments where both the three-week metric and 12-month retention are observed. I restrict it to the same change class. I pre-register the mapping and the decision threshold. I quantify the extra uncertainty the mapping adds. I re-validate as new experiments complete. A surrogate is the wrong tool when the change alters the mechanism the surrogate depends on: notification and interruption, pricing, trust, or anything that shifts who stays in the sample.
A team used week-one sessions as a surrogate for annual retention. It had been calibrated on content-ranking changes. They shipped a notification change: sessions rose 6%, annual retention fell 3%. The surrogate was measuring engagement, not the interruption cost. Detection is a back-test by change class. If notification changes had 4/9 sign accuracy, I would not use that surrogate for notifications.
Hypothetical arithmetic: baseline 12-month retention is 40%. Past content experiments give +1% relative week-1 sessions → +0.3 pp 12-month retention, 95% CI ±0.8 pp. The three-week test shows +2% sessions. Predicted retention lift is +0.6 pp, CI [−1.0, +2.2] pp. It includes zero. I would not launch on that. I would launch to a 2% holdout for 12 months and read retention directly, or if the change is reversible, ship with a guardrail that rolls back if week-4 retention drops.
So: run the three weeks, but don’t answer the retention question with it. Answer it with a holdout or a validated surrogate; if neither is available, treat the three-week result as a guardrail and keep the launch reversible.
Curated: · Written: · Reviewed:
Q&A-23The experiment won. How do you go from a 50/50 test to 100% of traffic?
"Won" means one thing: the pre-registered primary metric moved in the right direction at the pre-registered significance level, and no guardrail was violated at 50%. If the win came from a secondary metric or a segment you chose after seeing the data, you don't ramp: you re-test. Given a real win, the move to 100% is a staged ramp with a retained holdout, not a flag flip. 50% is the largest treated share you have measured, and a class of effects only appears above it.
Why 50% → 100% is a measurement, not a formality
Two things change as the treated share grows.
Share-dependent effects. Anything with a shared resource or a two-sided market behaves differently at 90% than at 50%: a cache that was warm because half the traffic was still on the old path, a rate limiter sized for the test's QPS, a marketplace where supply and demand are both partly treated, a support queue that only sees a new failure mode at volume. None of these show up in a 50/50 test, because the untreated half is still absorbing them.
Statistical sensitivity. Shrinking the control arm from 50% to 5% costs a factor of √10 ≈ 3.2 in the smallest effect you can detect. Concretely, with 2M users, a 10% baseline conversion, 80% power and α = 0.05: at 1M per arm you can detect a 1.2% relative lift; at 100k control you need 3.8%. That is acceptable, because the primary metric is already decided. Guardrails are operational metrics (error rate, p95, queue depth) with much larger effect sizes and far lower variance, so a 5% holdout reads them comfortably.
Keep the holdout fixed and the assignment stable
The holdout must not shrink as the ramp widens, and a user must not change arms mid-ramp or the comparison is contaminated. Hash the user into a fixed bucket and put control at the bottom, so widening the ramp only converts untreated users into treated ones:
# Python 3.12. Pure function of user_id: a user's arm never changes as the ramp widens.
def arm(user_id: str, treatment_share: float, holdout_share: float = 0.05) -> str:
b = int(hashlib.sha256(user_id.encode()).hexdigest()[:8], 16) / 0xFFFFFFFF
if b < holdout_share:
return "control" # bottom bucket, never shrinks
if b < holdout_share + treatment_share:
return "treatment"
return "untreated" # becomes treatment as the share grows
The ramp plan
| Stage | Treated | Holdout | Hold | Rollback trigger |
|---|---|---|---|---|
| 1 | 50% | 5% | 3 days | error rate +0.1 pp vs holdout |
| 2 | 75% | 5% | 3 days | p95 latency +50 ms vs holdout |
| 3 | 90% | 5% | 3 days | queue depth > 1,000 for 15 min |
| 4 | 95% (soak) | 5% | 8 weeks | primary metric −0.5% vs holdout |
Stage 4 assigns 100% of traffic (95% treated, 5% held out) and the long hold is where slow guardrails (7-day retention, support ticket rate) become readable. Hold each stage for at least one full business cycle and at least the longest guardrail's measurement window; a 7-day retention guardrail is unreadable after two days. Read guardrails against the same bounds used in the test, and record the share at which any of them starts to move: that number is the real capacity limit, and it belongs in the runbook.
Failure modes
- Share-dependent saturation. A feature that passed at 50% saturated an internal service at 85% and caused a two-hour outage, because nothing between 50% and 100% had been measured and there was no stopping rule.
- Interference. Treated and control users interact (marketplace, social graph, shared cache) so the control arm stops being a clean counterfactual as the treated share rises. Detect it by checking whether the control arm's own metric drifts as the ramp widens.
- A rollback that isn't one action. If reverting needs a deploy, the trigger is decoration. The ramp must be a flag flip, tested before stage 1.
Curated: · Written: · Reviewed:
Q&A-10An experiment is flat overall. A colleague finds a significant 7% lift for new users on mobile. Do you ship for that segment?
No, not on this evidence. A 7% lift found in one cell of a flat experiment is a hypothesis about a segment, not a result about one. Two things have to be true before it changes a rollout: the interaction survives a multiplicity correction over a family you committed to in advance, and a dedicated experiment reproduces it. Neither is true yet.
The flat overall is a constraint, not a contradiction. If new-mobile is 8% of traffic, a 7% lift there moves the overall by 0.08 × 7% = 0.56%. A test powered to see 2% overall cannot see that, so flatness doesn't refute the segment effect, but it also gives you no confirmation, and if the overall estimate is near zero the lift has to be offset by a negative effect in the other 92% of traffic. Ask whether that offset is plausible before you believe the cell.
The search space is the whole argument. "New users on mobile" is one cut among many. Tenure × device × region × plan at 4×3×2×4 is 96 cells; at α = 0.05 you expect 4.8 of them to cross by chance. Holm's threshold for the smallest p in a family of 96 is 0.05/96 = 0.00052, not 0.05. And under a pure null the largest of 96 z-statistics sits near 2.35 (the √(2 ln 96) ≈ 3.02 approximation, corrected to ≈ 2.35), so a winning cell at z ≈ 2 is weaker than the noise floor of the search you just ran.
Test the interaction, not the subgroup. "Is the effect nonzero for new-mobile users?" is a subgroup test: underpowered in a small cell, and it never asks the question that decides shipping. "Does the effect differ between new-mobile and everyone else?" is an interaction, and that is the one that matters.
import statsmodels.formula.api as smf
# Pre-registered family: does the effect differ by tenure and by device?
m = smf.ols("revenue ~ treated * is_new + treated * is_mobile", data=df).fit()
print(m.pvalues[m.pvalues.index.str.contains(":")])
# Read the interaction terms, not the treated main effect.
# Correct with Holm over the registered family, not a raw 0.05.
import math
cuts = 4 * 3 * 2 * 4 # tenure x device x region x plan
print(cuts, "cuts ->", cuts * 0.05, "expected false positives at alpha=0.05")
print("Holm threshold for the smallest p:", 0.05 / cuts)
# n per arm for a 7% relative lift, 80% power, two-sided 5%:
# n = 2 * (1.96 + 0.84)**2 * (cv / 0.07)**2
for cv in (0.8, 1.0, 2.0):
print(f"cv={cv}: {math.ceil(2 * 2.80**2 * (cv / 0.07)**2):,} per arm")
Output: 96 cuts -> 4.8 expected false positives at alpha=0.05; Holm threshold for the smallest p: 0.0005208333333333333; cv=0.8: 2,048 per arm; cv=1.0: 3,200 per arm; cv=2.0: 12,800 per arm. Revenue is heavy-tailed, so CV above 1 is the normal case. Compare that to the new-mobile cell's actual n: if the cell has fewer than ~3,200 per arm at CV ≈ 1, a 7% "significant" hit is inside the winner's-curse zone, and the honest point estimate is shrunk toward zero.
Failure modes to check before believing it. Composition: new-mobile users may be a different plan mix, so the "segment effect" is a mix shift. Stratify within the cell. Post-hoc definition: "new" as 7 vs 30 days and "mobile" as app vs mobile web are each another cut. Novelty: read the effect by week; new users respond to any change and the lift decays. Peeking: if the segment was found mid-flight, the p-value is not valid at all.
What I'd do. Don't ship. Ask how many cuts were examined before this one, re-run it as an interaction with Holm over the registered family, and if it survives, run a dedicated experiment with the segment as the primary analysis, powered per the arithmetic above. If the change is cheap and reversible, ship behind a flag with a holdback and a pre-registered readout date, but that is a decision to keep learning, not a decision that the lift is real.
Curated: · Written: · Reviewed:
Q&A-19Treatment wins in every country but loses overall. How is that possible, and which number goes in the summary?
I would settle Simpson's paradox in a readout against the simplest defensible comparison, so any extra structure has to earn its place.
A pooled comparison weights each subgroup by its share of the arms, so when the arms have different subgroup mixes the aggregate can reverse every subgroup it is made of.
Concretely, check the composition of the arms before comparing them, and if it differs, either report the mix-adjusted effect using a common weighting or fix the assignment defect that produced the imbalance and rerun.
The reason for that specificity is a failure I have seen: A pooled readout showed a 1.4% loss while every country showed a gain, because a redirect bug pushed a disproportionate share of a low-converting market into treatment; the summary went to leadership before anyone compared the mixes.
Every stratum favours treatment; the pooled rate does not.
| Country | Control | Treatment | Treatment share of arm |
|---|---|---|---|
| A (high converting) | 30% of 8,000 | 32% of 2,000 | 20% |
| B (low converting) | 5% of 2,000 | 6% of 8,000 | 80% |
| Pooled | 25.0% | 11.2% | — |
I would not consider it settled without evidence: Report the arm composition alongside the aggregate, and standardise both arms to a common subgroup weighting before quoting a single number.
An aggregate over differently composed groups is a statement about the composition.
Curated: · Written: · Reviewed:
Q&A-12One customer spent $94,000 during your pricing test, and it flipped the result. What is the defensible way to handle that?
The 90-second answer. First check the row is real and attributable: duplicate rows, test accounts, cents-vs-dollars, an account that was never randomised. If it survives that, the question is not "how do I remove it" but "what am I estimating". If the decision is about total revenue, the whale is part of the answer and winsorising it away answers a different question. If the decision is about per-customer behaviour, the whale is noise and you should have used a robust estimator. Either way you report both numbers and let the reader see the sensitivity. You do not pick the cut that agrees with the conclusion you already wanted.
Why one row can do this. Take 200,000 customers per arm, control mean $12.40, treatment mean $12.87: a +$0.47 (+3.8%) lift. The $94,000 customer contributes 94,000 / 200,000 = $0.47 to the treatment mean. That single row is the entire measured effect, and it is 0.0005% of the arm.
What to run. The t-test is invalid here: the sample SD is dominated by the whale and the CLT has not kicked in. Use a permutation test on the revenue vector: no distributional assumption, and its null distribution is exactly the "what if the whale had landed in control" question.
import numpy as np
rng = np.random.default_rng(0)
rev = np.concatenate([treatment, control]) # one row per customer
n_t = len(treatment)
def stat(x, n_t):
return x[:n_t].mean() - x[n_t:].mean()
obs = stat(rev, n_t)
null = np.array([stat(rng.permutation(rev), n_t) for _ in range(10_000)])
p = (np.abs(null) >= np.abs(obs)).mean()
With one dominant row the null distribution is lumpy: the p-value is close to "how often does the whale land in treatment", roughly 50%. That is the honest answer: the experiment cannot distinguish the price change from the whale.
Then check attribution. Pull the customer's pre-period spend. If they were already spending at that level, the treatment did not cause it, and a CUPED or difference-in-differences adjustment on pre-period revenue removes most of the contamination. If they went from $0 to $94k, that is either a genuine treatment effect or a pipeline bug: read the raw event log before believing it.
Report the sensitivity, don't choose it. Illustrative figures for the 200,000-per-arm example above:
| Estimator | Lift | 95% interval |
|---|---|---|
| Raw mean | +$0.47 (+3.8%) | +$0.02 to +$0.92 |
| Drop the $94k row | $0.00 | -$0.05 to +$0.05 |
| Median per customer | +$0.01 | -$0.03 to +$0.05 |
| CUPED on pre-period spend | +$0.06 | -$0.11 to +$0.23 |
The decision. If the whale's spend is real and the decision is about total revenue, the defensible move is to extend the test or decide on business grounds with this table in hand, not to declare a winner. If the decision is about per-customer behaviour, the median or a pre-registered winsorisation is the estimand, and the raw mean is the sensitivity check. For the next test, register the estimand, the cap and the decision rule before unblinding; a rule chosen after you have seen which arm the outlier landed in is a decision about the result, not about the data.
Curated: · Written: · Reviewed:
Q&A-13Only 38% of users assigned to treatment actually opened the new feature. Do you analyse the ones who used it?
The first thing I would pin down about non-compliance and intention-to-treat is which question the number is supposed to answer.
Compare the groups as randomised, because the users who chose to engage differ from those who did not in ways the randomisation no longer controls, and the moment you filter on a post-assignment behaviour the comparison stops being an experiment.
Concretely, report the intention-to-treat effect as the primary result, and if the effect on the compliers is the quantity of interest estimate it with an instrumental-variables approach that uses assignment as the instrument rather than by filtering the data.
The reason for that specificity is a failure I have seen: An onboarding test analysed on "users who completed the tour" showed a 34% retention lift that was almost entirely selection: the users who finished a five-step tour were already the users who would have stayed.
Three estimates from one experiment, and only two are identified.
| Analysis | Estimate | Identified by randomisation |
|---|---|---|
| Intention-to-treat (as assigned) | +3.2% | yes |
| Complier average effect (assignment as instrument) | +8.4% | yes |
| Completers vs everyone in control | +34% | no, selection |
I would not consider it settled without evidence: Compare the pre-period behaviour of compliers and non-compliers within the treatment arm; a large gap shows what filtering on engagement would import into the estimate.
Filtering on something the treatment caused destroys the only thing randomisation bought you.
Curated: · Written: · Reviewed:
Q&A-6You are testing a change to a two-sided marketplace's ranking. Why can a standard user-level A/B test mislead you here?
The useful framing for interference between treatment and control units is what decision changes if the estimate moves by its own uncertainty.
When treated units compete with control units for a shared finite resource, the treatment effect measured against those controls includes what treatment took from them, so the difference overstates what a full rollout would deliver.
Concretely, switch to a design that isolates the interference: randomise by market or region, run a switchback across time, or randomise the supply side, and accept the smaller number of independent units in exchange for an estimate that generalises to full traffic.
The reason for that specificity is a failure I have seen: A ranking change measured a 6% booking lift in a user-level test and delivered under 1% at full rollout, because most of the measured gain was demand redirected from control users onto the same limited inventory.
Three designs on the same ranking change.
| Design | Independent units | Measured lift | Reproduced at rollout |
|---|---|---|---|
| User-level split | 1,200,000 | +6.0% | no |
| Market-level split | 48 cities | +1.3% | yes |
| Switchback, hourly | 720 hour-slots | +1.1% | yes |
I would not consider it settled without evidence: Run the winning arm as a market-level or switchback test in a subset of regions and compare the effect to the user-level estimate before committing to the forecast.
In a marketplace, one arm's gain is often the other arm's loss, and the difference counts it twice.
Curated: · Written: · Reviewed:
Q&A-14You need to test a dispatch algorithm where every unit affects every other. How do you design the experiment?
The 90-second answer. The estimand is the effect of running policy A across the whole market for a time slot versus running policy B for a slot: a market-time average treatment effect, not a per-driver effect. Because every unit shares supply with every other, unit-level randomisation is out: control drivers' outcomes are contaminated by treated drivers' behaviour. So randomise time. Split the day into slots, flip a coin per slot for which policy the whole market runs, block the randomisation on day-of-week × hour-of-day so demand drift can't masquerade as a treatment effect, and analyse at the slot level with slot as the unit of observation. Discard the opening minutes of each slot as washout, because trips dispatched under the previous policy are still completing.
Slot length is the whole design. It has to exceed the system's memory, how long a dispatch decision keeps shaping supply, and stay short enough to give you many slots. Those pull in opposite directions.
Illustrative carryover diagnostic, same market, three slot lengths, effect measured on the first third versus the last third of each slot:
| Slot length | Effect, first third | Effect, last third | Carryover |
|---|---|---|---|
| 15 min | +0.4% | +5.8% | severe |
| 60 min | +2.9% | +3.4% | mild |
| 180 min | +3.1% | +3.2% | none detected |
A gap between the thirds means the slot is shorter than the memory: the first third is still running the old policy. Pick the shortest slot where the two thirds agree, then throw the first third away anyway.
The cost of long slots is variance, and it is brutal. Slots inside a day are correlated: same demand curve, same driver pool. Fifteen-minute slots over 14 days give 1,344 slots, but with a within-day intraclass correlation of 0.3 the design effect is 1 + (96 − 1) × 0.3 ≈ 29.5, so the effective sample size is about 1,344 / 29.5 ≈ 46, not 1,344. Power the test on independent slots, not slots. Blocking on hour-of-day and analysing within-day pairs claws some of it back.
Alternatives, and when they beat a switchback.
- Cluster randomisation across markets: assign whole cities or regions to A or B. Valid when drivers and orders don't cross cluster boundaries. It wins when the effect takes days to appear (incentive changes, repositioning rules) and no affordable slot is long enough. It costs you: 20 cities is 20 clusters, so the interval is wide and you need synthetic controls or a staggered difference-in-differences to recover power.
- Edge or bipartite randomisation: randomise the policy per driver-order pair, or within matched pairs of near-identical orders. Keeps the comparison inside one market and one time window, so there is no time confound. It leaks through shared supply, so it only holds when the treated fraction is small.
- Interleaving: run both policies on the same request and compare the ranked outputs. Good for ranking quality, useless for anything with a supply-side equilibrium.
When not to use a switchback. If the policy's effect outlives any slot you can afford: a pricing change, a contract change, a repositioning rule that plays out over hours: the arms measure each other and no slot length fixes it. Move to cluster randomisation over many markets, or a staggered rollout analysed as difference-in-differences.
Failure modes to watch. Carryover, detected by the first-third versus last-third split above. Time-varying effect: a peak-hour-only gain averages away, so estimate per stratum rather than pooling. Deterministic policy in a deterministic market: if the same state always yields the same dispatch, the randomisation has to be at the slot, not the request. And metric leakage: count only trips assigned and completed inside the slot, or you credit the wrong arm.
Curated: · Written: · Reviewed:
Q&A-15A feature was rolled out to Canada but not the United States. How would you estimate its effect, and what has to be true for that estimate to mean anything?
Start with the design, not the number. Canada got the feature, the US didn't, and nothing was randomized, so the honest framing is a difference-in-differences estimate with the US as the control. The estimate is only as good as the assumption that the two regions would have tracked each other without the rollout.
The estimate is four cells:
| Before | After | Change | |
|---|---|---|---|
| Canada (treated) | 48.2 | 55.9 | +7.7 |
| United States (control) | 51.0 | 55.4 | +4.4 |
| Difference-in-differences | +3.3 |
Canada moved +7.7 points, the US +4.4, so the estimate is +3.3 points. That is the number I would report, with the caveat attached.
What has to be true for it to mean anything:
- Parallel trends. Absent the rollout, Canada's counterfactual path is the US path shifted by a constant. That is unobservable, so you falsify it rather than assert it: run the same specification on the pre-period alone, and on a placebo year. A prior-year placebo gives Canada +2.9 and the US +2.8, a DiD of +0.1: near zero, which is what you want. A non-zero pre-period "effect" means the assumption fails and the +3.3 is not an effect.
- No spillovers. Canadian users must not affect US users. A shared backend, cross-border referrals, or US users seeing Canadian screenshots breaks the control.
- No concurrent region-specific shock. A currency move, a competitor launch, a holiday, or a pricing change hitting one side. This is the one that actually bites.
- Stable composition. No differential migration or signup surge in one region over the window.
Inference is the part people skip. With one treated unit, clustering at the unit level gives you a single cluster and the standard errors are meaningless. You need a permutation test: pretend each untreated region was treated and see where Canada's +3.3 falls in that distribution, or synthetic control.
Alternatives, and when DiD on two units is the wrong tool:
- Geo-holdout experiment. If you can randomize regions before launch, do that. It is the only design that gives a real counterfactual, and it makes the parallel-trends assumption unnecessary.
- Synthetic control. One treated unit, many donor regions: build a weighted combination of untreated regions that matches Canada's pre-period, then compare. Better than picking a single control when no one region tracks Canada well.
- Interrupted time series. No clean control at all: model the pre-period trend and look for a break. Weakest of the three, because any concurrent shock looks like an effect.
DiD on two units is the wrong tool when the pre-periods don't track, when there is one treated unit and you cannot run placebo inference, or when the rollout is confounded with a region-specific event.
A failure I have seen: a pricing rollout credited with a 12% revenue gain that was mostly a currency move. The pre-period plot, drawn afterwards, showed the two regions had already been diverging for five months.
So: report +3.3, show the pre-trend plot, run the placebo, and name the assumption you are leaning on.
Curated: · Written: · Reviewed:
Q&A-16You cannot randomise, but you have a variable that shifts uptake for reasons unrelated to the outcome. What are you assuming when you use it?
My answer to instrumental variables begins with how the data came to exist, because sampling contaminates everything after it.
An instrument identifies the effect only if it moves the treatment, affects the outcome through no other channel, and is unrelated to the unobserved determinants of the outcome, and the second of those cannot be tested from the data.
Concretely, show the first stage is strong rather than assuming it, argue the exclusion restriction from institutional knowledge of why the instrument varies, and report that the estimate applies to the units the instrument actually moves rather than to the whole population.
The reason for that specificity is a failure I have seen: A study instrumented adoption with a marketing email send, which also carried a discount code, so the instrument affected the outcome directly and the estimate absorbed the discount's effect entirely.
First stage, reduced form, and the ratio that is the estimate.
| Quantity | Estimate | Standard error |
|---|---|---|
| First stage: instrument on uptake | +0.21 | 0.02 (F = 110) |
| Reduced form: instrument on outcome | +1.68 | 0.44 |
| Instrumental-variables estimate (1.68 / 0.21) | +8.0 | 2.2 |
I would not consider it settled without evidence: Report the first-stage F statistic and the reduced form separately, so a weak instrument shows as a wide and unstable second stage rather than hiding inside a ratio.
A weak instrument gives an answer with more bias than the comparison it was meant to fix.
Curated: · Written: · Reviewed:
Q&A-17Loyalty status is awarded at exactly 1,000 points. How can that threshold give you a causal estimate?
I would treat regression discontinuity as a design question first and a computation question second.
Units just above and just below a sharp cutoff are comparable in everything except the assignment, so the jump in the outcome at the cutoff estimates the effect for units at that margin and only for them.
Concretely, fit flexible functions of the running variable on each side within a bandwidth, read the effect as the discontinuity at the cutoff, and check that the density of the running variable and every pre-treatment covariate are continuous there.
The reason for that specificity is a failure I have seen: A loyalty analysis reported a large benefit at the threshold while the histogram of points showed a spike immediately above 1,000. Customers were topping up to qualify, so those just above were self-selected and not comparable to those just below.
Bandwidth sensitivity and the manipulation check.
| Bandwidth (points) | Estimated jump | 95% interval |
|---|---|---|
| +/- 50 | +$14.10 | +$3.20 to +$25.00 |
| +/- 100 | +$13.40 | +$6.90 to +$19.90 |
| +/- 400 | +$27.80 | +$22.10 to +$33.50 |
| Density test at cutoff | p = 0.001 | manipulation present |
I would not consider it settled without evidence: Run a density test on the running variable at the cutoff and re-estimate at several bandwidths; an estimate that swings with the bandwidth is fitting curvature rather than a jump.
The design is only credible if units cannot precisely control which side of the line they land on.
Curated: · Written: · Reviewed:
Q&A-18You matched treated and untreated users on 30 covariates and the groups now look identical. Is the comparison causal?
No, not on the strength of the balance table. Matching on 30 covariates buys you exchangeability conditional on those 30. It says nothing about the ones you didn't measure. The balance table is evidence about the matching procedure, not about identification. The comparison is causal only if you can defend the assumption that no unmeasured confounder remains, and that is an assumption you argue for, not a result you read off a table.
What the table does buy you: if every standardised mean difference (SMD) is under roughly 0.1 (Austin's rule of thumb, Statistics in Medicine, 2009), you have removed the observed confounding the matching was designed to remove. That is real. It is just not the whole question.
| Covariate | SMD before | SMD after |
|---|---|---|
| Tenure months | 0.62 | 0.03 |
| Sessions per week | 0.48 | 0.02 |
| Region mix | 0.31 | 0.04 |
| Employer-paid (never collected) | — | — |
The last row is the shape of every unmeasured confounder: something that drives both selection into treatment and the outcome, that nobody instrumented. You cannot compute an SMD for it, which is exactly the problem.
Quantify how much unmeasured confounding it would take. For a binary outcome, convert the estimate to a risk ratio and compute the E-value (VanderWeele & Ding, Annals of Internal Medicine, 2017): E = RR + sqrt(RR × (RR − 1)). Say the matched estimate is RR = 1.22 with a 95% CI of [1.08, 1.38].
- Point estimate: 1.22 + sqrt(1.22 × 0.22) = 1.22 + 0.52 = 1.74
- CI bound: 1.08 + sqrt(1.08 × 0.08) = 1.08 + 0.29 = 1.37
Read that as: an unmeasured confounder would need to be associated with both treatment and outcome by a risk ratio of 1.74 each, above and beyond the 30 covariates, to explain the point estimate away; 1.37 each to push the CI to include 1. Then the judgement call is whether a variable that strong is plausible in this system. In a subscription product, "who pays" plausibly is. In a lab experiment on a physical process, probably not.
Failure modes that survive a clean balance table:
- Post-treatment covariates. If any of the 30 was measured after treatment started, matching on it adjusts away part of the effect or opens a collider path. Check the measurement timestamp of every covariate, not just its balance.
- Off-support treated units. Report how many treated units found no acceptable match and what they look like. If the dropped units are systematically the heaviest users, you have an ATT on a trimmed population, not the effect you wanted.
- Variance. Matching induces dependence between units, so naive standard errors are too small. Use Abadie–Imbens standard errors; the bootstrap is not valid for nearest-neighbour matching with a fixed number of matches (Abadie & Imbens, Econometrica, 2008).
- Score balance is not covariate balance. If you matched on a propensity score, balance on the score does not imply balance on every covariate. Check the covariates directly.
When not to use it: if you can run a randomized holdout, a switchback, or a stepped-wedge, do that instead. Matching is what you reach for when randomization is off the table, and it should be reported as such.
What to put in front of a reviewer: the SMD table, the count and profile of unmatched treated units, the E-value for both the point estimate and the CI bound, and the single unmeasured confounder you consider most threatening, with your argument for why it is not strong enough to close the gap.
Curated: · Written: · Reviewed:
Q&A-24Your observational model says the feature drives a 20% lift. The experiment says 2%. Which is wrong?
The observational number is the wrong one. Randomisation breaks the link between treatment and everything that predicts the outcome; an observational estimate of adopters versus non-adopters does not, so it measures who adopts as much as what the feature does. Assume the experiment was actually randomised, passed a sample-ratio check, and had power to detect something near 2%, if any of those fail, the experiment is the suspect instead.
Why the gap is almost always selection: people who adopt a feature are not a random slice. They are heavier users, more engaged, more likely to be on the outcome path anyway. The observational model fits the outcome on adoption and attributes all of that pre-existing difference to the feature.
The cheap test that settles it: run the observational estimator on the control arm alone. In control nobody received the feature, so any lift it reports there is bias by construction.
Hypothetical numbers, same method throughout:
| Estimator | Data | Estimate |
|---|---|---|
| Observational, adopters vs non-adopters | Full population | +20.4% |
| Observational, same method | Control arm only | +18.9% |
| Randomised difference in means | Both arms | +2.1% |
The control-arm run recovers 18.9 points where the true effect is zero. That is the selection term. Subtract it and the observational estimate lands near the randomised one: the 20% was never a feature effect.
When the observational estimate can still be the better one: if the experiment is underpowered (minimum detectable effect above 2%), if treatment was diluted (few users actually saw the feature), if the horizon is too short for a delayed effect, or if the experiment population is not the population you are deciding about. Those are the deciding criteria: check them before you discard the experiment, not after. A placebo test on control, a sample-ratio check, and the exposure rate tell you which of the two to trust.
Averaging is defensible only when both estimates identify the same quantity and differ by noise. Here they do not: one is a causal estimate, the other is a correlation. Averaging them weights bias equally with identification. I have watched a team average 20% and 2% into 11%, plan headcount on it, and miss by 5x when the experiment's number held.
So: trust the randomised 2%, use the observational model for heterogeneity and external validity, and never let it move the point estimate.
Curated: · Written: · Reviewed:
Q&A-53In a regression of spend on tenure and plan, the tenure coefficient is 4.2. What exactly does that mean?
The 60-second answer. 4.2 is the slope of the fitted surface along the tenure axis: among customers on the same plan, one extra month of tenure goes with $4.20 more spend, on average, in this sample. Three things have to be pinned down before you say that out loud. Units: spend in dollars, tenure in months, if tenure were coded in years the same number reads as $4.20 per year. Conditioning set: plan is held fixed, so this is a within-plan comparison, not the raw tenure–spend correlation. Status: it is an association in the fitted model, not a causal effect, unless the design identifies one.
What "holding plan fixed" actually does. The coefficient is the slope you get from regressing spend on the part of tenure that plan cannot explain (Frisch–Waugh–Lovell). Concretely, with two plans:
| Plan | Tenure (mo) | Spend ($) |
|---|---|---|
| Basic | 1 | 10.0 |
| Basic | 2 | 14.2 |
| Basic | 3 | 18.4 |
| Pro | 10 | 60.0 |
| Pro | 11 | 64.2 |
| Pro | 12 | 68.4 |
Within each plan the slope is exactly 4.2. Pool the six rows and regress spend on tenure alone and the slope is 5.51: the pooled fit is dragged up by the fact that Pro customers both stay longer and spend more. The 4.2 is what survives after plan absorbs that. If plan has more than two levels, the same logic holds against the full set of plan dummies. If you add a tenure×plan interaction, you get a separate slope per plan and 4.2 is only one of them.
Failure modes, and how you catch them.
- Omitted confounder. Acquisition channel, engagement, or price paid at signup can drive both tenure and spend. Detect by adding covariates and watching the coefficient move; report the range it moves over rather than a single number.
- Extrapolation. The slope is only supported over the tenure range in the data. Plot the tenure histogram; if the 95th percentile is 36 months, a claim about month 60 is out of support. (An interval like $3.10–$5.30 is illustrative: use the one your model reports.)
- Interaction. If the slope differs by plan, the single 4.2 is an average that describes no plan. Add the interaction and test it.
- Measurement error in tenure. Billing-system tenure that disagrees with the customer's own count attenuates the coefficient toward zero.
- Wrong standard errors. Spend is skewed and tenure is clustered by signup cohort; unclustered SEs understate the interval. Cluster at the cohort level and check.
Same coefficient, three defensible readings and one that is not.
| Statement | Supported |
|---|---|
| "Among customers on the same plan, each extra month is associated with $4.20 more spend." | yes |
| "The 95% interval is roughly $3.10 to $5.30 over tenures of 1 to 36 months." | yes, if that is what the model reports |
| "Beyond 36 months the same slope applies." | no, out of support |
| "Extending a customer's tenure by a month yields $4.20." | no, not identified |
If you need the causal version. The coefficient supports a counterfactual only if the data contains variation in tenure that is unrelated to everything else moving spend. Options: randomize a retention incentive and read the intent-to-treat effect; instrument tenure with something that shifts it but not spend (a pricing change, a billing migration); difference-in-differences around a policy change; or a regression discontinuity at a tenure threshold such as a loyalty tier at month 12. Absent one of those, report 4.2 as a conditional association with its interval and its support, and say so plainly. Holding other variables fixed in a model is not the same as holding them fixed in the world.
Curated: · Written: · Reviewed:
Q&A-54Adding a variable to your model flips the sign of a coefficient you had already reported. What happened?
What happened, in the first minute. You almost certainly watched omitted variable bias: the first coefficient was answering a different question than the one you thought you asked. A coefficient absorbs the effect of anything correlated with its predictor and with the outcome that the model leaves out. Add that variable and the coefficient moves to the partial effect.
Do not report the flip until you have ruled out three cases where the new number is the wrong one: the added variable is a mediator or a collider, the flip is inside the noise, or the added variable is nearly collinear with the predictor.
The mechanism, with numbers. Bias = (effect of the omitted variable on the outcome) × (coefficient from regressing the omitted variable on the included predictor). Illustrative figures, spend in dollars, chat = monthly chat hours, account size in $k:
- True process: spend = 12·chat + 40·account_size + ε
- Chat users have smaller accounts: account_size = 30 − 1.25·chat
- Short regression: 12 + 40 × (−1.25) = 12 − 50 = −$38
The −$38 is not a small effect that got corrected; it is a real number about a different quantity: the association between chat and spend with account size left free to vary.
| Specification | Chat coefficient | What it is |
|---|---|---|
| spend ~ chat | −$38 | 12 direct + (−50) omitted-variable bias |
| + account_size | +$12 | bias removed |
| + account_size + tenure | +$9 | second, smaller confound from tenure |
| + account_size + tenure + region | +$9 | stable |
The 12 → 9 drift is a second confound, not noise; the estimate is stable after that.
Before you trust the flip. Compare the two confidence intervals. [−60, −16] to [+2, +22] is a real flip. [−5, +3] to [+1, +6] is nothing, and the honest report is "the coefficient is indistinguishable from zero in both specifications."
Failure modes that make the new number wrong.
- Collider. You added a common effect of predictor and outcome: support tickets filed, if both chat use and spend cause tickets. Conditioning on it induces association where there was none. Detect: the DAG marks it as a collider, and the coefficient moves in a direction the DAG does not predict.
- Mediator. chat → engagement → spend. Controlling for engagement blocks the indirect path; +$12 is the direct effect only, and it is not the total effect the decision needs.
- Suppression. The added variable correlates with the predictor and barely with the outcome. |b| grows or flips with no confounding involved.
- Collinearity. VIF above 5–10 (rule of thumb) between the added variable and the predictor. Bootstrap the coefficient; if the sign flips across resamples, you have instability, not confounding.
- Measurement error in the added variable attenuates its coefficient and leaves residual confounding behind.
What to report. The nested sequence, the DAG that fixed the adjustment set, and both numbers with the decision each supports. If a plan was already drafted on −$38, that is the incident: the fix is drawing the DAG before fitting, not choosing the specification after.
Curated: · Written: · Reviewed:
Q&A-55Two predictors correlate at 0.95. Does that break your model, and what do you do about it?
I would settle multicollinearity against the simplest defensible comparison, so any extra structure has to earn its place.
Collinearity inflates the standard errors of the affected coefficients without biasing them or harming prediction, so it is a problem for interpreting individual coefficients and not for the fitted values.
Concretely, decide first whether the model is for prediction or for interpretation; leave collinear predictors alone when predicting, and when interpreting, combine them into one construct, drop one on subject-matter grounds, or use a penalised fit and report the pair jointly rather than separately.
The reason for that specificity is a failure I have seen: A team dropped a predictor because its variance inflation factor was 21 and reported the survivor's coefficient as the effect of the construct, which doubled the apparent effect because the dropped variable's contribution was now attributed to it.
Individually insignificant, jointly significant.
| Term | Coefficient | Standard error | p |
|---|---|---|---|
| Sessions | 0.42 | 0.31 | 0.18 |
| Page views | 0.19 | 0.14 | 0.17 |
| Joint F test on both | 0.002 |
I would not consider it settled without evidence: Report the joint test on the collinear set alongside the individual coefficients, since the pair is often significant when neither member is.
Collinearity makes coefficients uncertain, and dropping one makes the other wrong.
Curated: · Written: · Reviewed:
Q&A-56Your residual plot fans out as fitted values grow. What is affected, and what is not?
Short answer: the coefficients survive; the standard errors, and every test and interval built on them, do not.
Under E[e|X] = 0, OLS is unbiased and consistent whether or not the errors are homoskedastic. The normal equations (X'X)β̂ = X'y never touch σ², so the point estimates are the same numbers you would get under any assumed error variance. What breaks is the classical covariance formula Var(β̂) = σ²(X'X)⁻¹, which assumes a single common variance. The correct covariance is (X'X)⁻¹ X'ΩX (X'X)⁻¹ with Ω = diag(σᵢ²). When the fanning is driven by the regressors that grow with fitted values, the classical formula typically understates those coefficients' standard errors, so t-statistics inflate and you get false positives.
| Quantity | Affected? | Why |
|---|---|---|
| β̂ point estimates | No | Unbiased and consistent under E[e|X] = 0; σ² never enters the normal equations |
| Classical SEs, t, p, CI | Yes | σ²(X'X)⁻¹ assumes one variance for every observation |
| F and Wald tests | Yes | Built on the same covariance matrix |
| Efficiency | Yes | Gauss-Markov needs homoskedasticity; OLS is no longer BLUE, WLS/GLS is if Ω is known |
| Prediction intervals | Yes | Interval width should vary with x; robust SEs do not fix this |
| Fitted values, residuals, R² | No | The fit is unchanged; only the inference around it moves |
What to do. Use heteroskedasticity-robust standard errors as the default, not as a rescue after a failed test. White (1980) HC0 is the baseline; HC3 is the safer choice in small samples because HC0 is biased downward with high-leverage points. If the fanning tracks a group structure (stores, cohorts, regions), use clustered SEs instead: robust SEs understate when errors are correlated within clusters. If the variance function is known or estimable, WLS or feasible GLS recovers the efficiency OLS lost, but FGLS with a badly estimated Ω can be worse than OLS in small samples.
Failure modes. Robust SEs fix inference, not specification: if the fanning comes from an omitted variable, the wrong functional form, or a skewed/count outcome, the coefficients themselves may be biased and robust SEs will report confident-looking intervals around a wrong estimate. Check the specification first. Robust SEs also do nothing for prediction intervals, and they do not restore efficiency. Finally, HC0 with n small and a few high-leverage points can understate SEs badly: use HC3 or a wild bootstrap.
Illustrative fit (hypothetical numbers, not a real dataset):
| Term | Coefficient | Classical SE | Robust SE | p (classical) | p (robust) |
|---|---|---|---|---|---|
| Discount depth | 1.84 | 0.85 | 1.47 | 0.03 | 0.21 |
| Tenure | 0.42 | 0.09 | 0.10 | < 0.001 | < 0.001 |
The coefficient that drives the fanning is the one whose p-value moves; the well-identified term barely changes. Report both sets of SEs: a large gap is itself the diagnostic.
Curated: · Written: · Reviewed:
Q&A-57A logistic coefficient is 0.7. What can you tell a product manager from that number alone?
Where analyses go wrong on reading logistic regression coefficients is usually one step upstream of the statistic being argued about.
A logistic coefficient is a change in log odds, so it converts to an odds ratio of about 2.0 and to a probability change that depends entirely on the baseline rate the unit starts from.
Concretely, exponentiate for the odds ratio, then convert to marginal effects at representative baseline rates, and report the probability change at the baselines that actually occur in the population rather than at a single average.
The reason for that specificity is a failure I have seen: An odds ratio of 2.0 was reported to a product team as "doubles conversion"; at the 40% baseline of the segment in question the actual increase was 17 percentage points, not 40, and the forecast was overstated by more than double.
One odds ratio, four baselines, four different product statements.
| Baseline probability | Odds | Odds x 2.0 | New probability | Change |
|---|---|---|---|---|
| 1% | 0.0101 | 0.0202 | 1.98% | +0.98 pp |
| 10% | 0.111 | 0.222 | 18.2% | +8.2 pp |
| 40% | 0.667 | 1.333 | 57.1% | +17.1 pp |
| 80% | 4.00 | 8.00 | 88.9% | +8.9 pp |
I would not consider it settled without evidence: Report average marginal effects computed over the observed covariate distribution, alongside the odds ratio.
Doubling the odds doubles the probability only when the probability is small.
Curated: · Written: · Reviewed:
Q&A-58You add an L1 penalty and half your coefficients go to zero. Can you report the survivors as the important drivers?
No, not as a ranking of importance. A zero coefficient means "given the other predictors in this model, this one adds nothing to the fit". It does not mean the predictor is irrelevant. And a nonzero coefficient means "the solver kept this one", which among correlated predictors is close to a coin flip.
Why the arbitrariness is structural
L1's constraint region is a cross-polytope with corners on the axes, and the optimum lands on a corner: that geometry is what produces exact zeros. When two predictors are correlated, the loss is nearly flat along the ridge between them, so which corner the solver lands on is decided by noise in the sample. Refit on a different sample and the survivor flips.
Two consequences worth stating out loud:
- With
nrows andppredictors, lasso returns at mostmin(n, p)nonzero coefficients. Atp = 500candidate features andn = 200rows you cannot get more than 200, however many are real. - The penalty is scale-dependent. A predictor measured in small units carries a larger coefficient for the same effect and is penalised harder, so standardise first or part of the selection is a units artefact.
What to report instead
It depends on the question being asked.
- "Which predictors does this design identify?" Report selection frequencies. Refit on
Bbootstrap resamples (500 is a reasonable default), record how often each predictor is nonzero, and quote the frequency rather than the membership of one fit. Meinshausen & Bühlmann's stability selection formalises this and suggests keeping predictors above a threshold in the 0.6–0.9 range; below that, treat the predictor as unresolved rather than dropped. - "Which predictors matter?" Use an importance measure that survives correlation: elastic net or group lasso, which keep correlated predictors in or out together; permutation or drop-column importance computed on held-out data with the full model; or refit-based inference (debiased or selective inference) if you need p-values. Coefficient magnitude from the penalised fit is not one of these: it is shrunk toward zero by construction.
Illustrative failure
A team ran lasso on 40 candidate features from a product analytics table, took the 6 nonzero coefficients, and ordered the roadmap by |β|. On 500 bootstrap refits:
| Predictor | β in reported fit | Retained in 500 refits |
|---|---|---|
| Sessions per week | 0.31 | 98% |
| Support contacts | −0.22 | 91% |
| Feature A adoption | 0.18 | 46% |
| Feature B adoption | 0.17 | 44% |
| Discount depth | 0.09 | 38% |
| Tenure (months) | 0.04 | 71% |
Three of the six appear in fewer than half the resamples. Feature A and Feature B are correlated at r ≈ 0.9; in most resamples the solver kept one and zeroed the other, and which one varied. The roadmap item that "mattered most" was a coin flip between two near-duplicate columns.
What I'd say in the room
Report the selection frequency for every candidate, state the resample count and the threshold, and say plainly that the survivors are a prediction basis, not a ranking. If the ask is importance, change the method, not the wording.
Curated: · Written: · Reviewed:
Q&A-59What do you actually look at after fitting a regression, and what does each plot rule out?
I look at four plots and one number, in the order that would change my conclusion fastest: residuals vs fitted, partial-residual plots against each predictor, residuals vs time or cluster, and Cook's distance. The decision rule is whether the pattern changes the thing I'm about to report (the sign or interval of a coefficient, or a prediction interval) not whether the plot looks tidy.
| Diagnostic | Assumption it tests | Failure looks like | What it can't tell you |
|---|---|---|---|
| Residuals vs fitted | Linearity, constant variance | Curvature, fanning | Which predictor is missing |
| Partial-residual vs each predictor | Missing non-linearity in that term | Systematic bend | Whether the bend survives the other terms (partial-residual plots do) |
| Residuals vs time or cluster | Independence | Runs, autocorrelation (Durbin–Watson for lag 1) | Dependence at lags you didn't plot |
| Scale-location, √|std resid| vs fitted | Heteroscedasticity | Widening band | Anything about functional form |
| Normal QQ | Residual normality | Heavy tails, skew | Coefficient CIs at large n (CLT covers those); prediction intervals it does affect |
| Cook's D, DFBETA | No single row driving the fit | Spikes above 1, or above 4/n | Whether the row is wrong or just unusual |
A clean plot is weak evidence; a pattern is strong evidence against. A mild bend in residuals vs fitted at n=200 is noise. The same bend at n=10,000 is a missing term.
When this matters less. If the deliverable is a prediction and you're validating on held-out data, cross-validation error and calibration dominate: fanning residuals that don't move out-of-sample error are a footnote. If the deliverable is a coefficient with a confidence interval, the same fanning inflates or deflates the OLS standard error and the interval is wrong, so it's the whole game. Regularized and tree-based models don't assume constant variance or linearity, so these plots test nothing they rely on.
When an assumption fails, the fix depends on which one. Curvature: add the term or a spline, refit, confirm the bend is gone. Fanning: log or Box-Cox the response, or HC3 robust standard errors if you only need coefficient inference. Robust errors do not fix prediction intervals. Dependence: cluster-robust errors or mixed effects; that repairs the interval, not the point forecast. Influence: report both fits, or switch to a robust regression (Huber, MM) and say so.
Worked example. With n=200 and p=3, the leverage rule of thumb is h_ii > 2p/n = 0.03. A row at h=0.9 with a residual of 2s gives Cook's D = e²/(p·s²) · h/(1−h)² = (4/3)·(0.9/0.01) = 120, against a common cutoff of 1. One row is the fit.
That is not hypothetical. A model passed on its overall fit statistic while three of 4,000 rows carried enough influence to set the sign of the main coefficient. It surfaced only when leverage was plotted, after the result had been circulated. Detection is DFBETA: refit without the top-influence rows and watch whether the coefficient's sign or interval moves. If it does, the full-sample number is not the finding: the sensitivity is.
Curated: · Written: · Reviewed:
Q&A-60When is an interaction term the right way to express "the effect is different for different users"?
The 90-second answer. "Different for different users" is ambiguous, and the ambiguity decides the tool. If it means a few pre-specified segments (new vs returning, iOS vs Android) then one model with an interaction term is the right expression: it estimates the difference in slopes directly and gives you a standard error for that difference. If it means each user has their own effect, an interaction term cannot express it; a product of two columns has one coefficient, not one per user. So the first move is to ask how many levels the moderator has and whether you want a testable coefficient or a per-user prediction.
Assuming the segment case, three things have to hold for the interaction to be the right form. The moderator is pre-specified, not chosen after scanning dozens of candidates. Both main effects are in the model: dropping them forces the slope to be zero in the reference group and makes the interaction uninterpretable. Continuous moderators are centred, so the main effect reads as the slope at the mean rather than at zero, which is often outside the data.
Alternatives and when they win.
| Approach | Gives you | Use when | Cost |
|---|---|---|---|
| Interaction in one model | One coefficient for the slope difference, with a standard error | Few pre-specified levels, want a testable difference | Needs adequate counts in every cell |
| Stratified fits (separate model per group) | Each group's own coefficients | Groups are few and you don't want to share structure | No direct test of the difference; comparing coefficients across fits ignores their covariance |
| Hierarchical / random slopes | Per-group or per-user effects, shrunk toward the mean | Many levels, thin groups, want to borrow strength | Assumes exchangeability; shrinkage biases individual estimates toward the population |
| CATE learner (T-/S-/X-learner, causal forest) | A per-unit effect estimate | Goal is prediction of heterogeneity, not a coefficient | No clean inference; needs cross-fitting to avoid overfitting |
The deciding criteria are the number of moderator levels, whether you need a testable coefficient or a prediction, and the smallest cell count.
Failure modes. The one that bites is a thin cell. Take a 2×2 with these counts:
| New users | Returning users | |
|---|---|---|
| Control | 18,400 | 42,900 |
| Treatment | 41 | 43,100 |
The standard error of the interaction coefficient is roughly sqrt(σ²(1/n11 + 1/n10 + 1/n01 + 1/n00)). With σ = 1 that is sqrt(5.4e-5 + 2.3e-5 + 0.0244 + 2.3e-5) ≈ 0.156. The 41-cell supplies 99.6% of that variance; the other three cells together supply 0.4%. The interaction is a statement about 41 people wearing the costume of a 100,000-person study. Report cell counts next to the coefficient so this is visible.
Two further failure modes matter. Interpreting a main effect when an interaction is present is a mistake: it is the slope at moderator = 0, meaningless unless centred. Extrapolating a continuous moderator beyond its observed range is another. And if you scan many candidate moderators, correct for the comparisons; the interaction you keep is the one that survived, not the one you predicted.
What I'd say to close: an interaction is a claim about a difference, and differences cost more data than levels. If the cells can't support it, say so and fall back to a stratified fit or a partial-pooling model rather than reporting a coefficient the data can't carry.
Curated: · Written: · Reviewed:
Q&A-61Why would you add store or user fixed effects to a panel regression, and what do they cost you?
The first thing I would pin down about fixed effects is which question the number is supposed to answer.
Fixed effects absorb every time-invariant difference between units, which removes confounding from unobserved stable characteristics and simultaneously removes the ability to estimate any predictor that does not vary within a unit.
Concretely, use fixed effects when identification comes from within-unit variation over time, check there is enough of that variation to estimate the coefficient at all, and cluster standard errors at the fixed-effect level.
The reason for that specificity is a failure I have seen: A model with store fixed effects reported a near-zero coefficient on store format, which was constant per store, so the term was absorbed entirely and the reported null was a mechanical artefact of the specification.
Within-unit variation decides what the specification can see.
| Predictor | Between-store variance | Within-store variance | Estimable with store fixed effects |
|---|---|---|---|
| Store format | 0.61 | 0.00 | no |
| Weekly promotion depth | 0.22 | 0.34 | yes |
| Staffing hours | 0.18 | 0.29 | yes |
I would not consider it settled without evidence: Report within-unit variation for each predictor of interest; a predictor with none cannot be identified in this specification.
Fixed effects trade unobserved confounders for the variables that do not move.
Curated: · Written: · Reviewed:
Q&A-62Your daily metric is strongly autocorrelated. What does that break in a standard regression?
The 90-second answer
Assume OLS on daily rows, stationary positively autocorrelated errors, and no lagged dependent variable on the right-hand side. Then the coefficients stay unbiased and consistent, but every standard error, t-statistic, p-value and confidence interval is wrong: too narrow. The row count is not the sample size.
Why the standard errors shrink
OLS assumes E[εε'] = σ²I. With AR(1) errors, ε_t = ρε_{t-1} + u_t, the off-diagonal entries are ρ^k σ². The true covariance of β̂ is σ²(X'X)⁻¹X'ΩX(X'X)⁻¹. For a stationary regressor the ratio to the naive σ²(X'X)⁻¹ is roughly (1+ρ)/(1-ρ). At ρ = 0.86 that is 13.3, so the honest standard error is about 3.65× the printed one: a t-stat of 3.65 collapses to 1.0. Effective sample size follows the same factor: n_eff ≈ n(1-ρ)/(1+ρ).
| Residual autocorrelation ρ | Rows | Approximate effective sample |
|---|---|---|
| 0.0 | 400 | 400 |
| 0.5 | 400 | 133 |
| 0.86 | 400 | 30 |
A weekly report once claimed a significant upward trend from 400 days of data whose residuals had ρ = 0.86. The trend was indistinguishable from noise once the interval was widened by that factor.
What breaks, and what does not
Does not break: point estimates, under strict exogeneity of the regressors. Breaks: inference, forecast intervals, and any comparison that leans on R² or information criteria across specifications with different residual dependence. Breaks badly: add y_{t-1} as a regressor and serial correlation makes OLS biased and inconsistent, because the lagged regressor is correlated with the error by construction. That is the case people miss.
Detect it
Durbin-Watson only sees AR(1) and is invalid once a lagged dependent variable is present. Use Breusch-Godfrey (any order, valid with lagged regressors) or Ljung-Box on the residuals.
import statsmodels.api as sm
from statsmodels.stats.diagnostic import acorr_breusch_godfrey
# y, X: 400 daily rows; X carries a linear trend column
ols = sm.OLS(y, sm.add_constant(X)).fit()
lm, pval, _, _ = acorr_breusch_godfrey(ols, nlags=5)
print(lm, pval) # LM statistic, p-value
# statsmodels default maxlags = floor(4*(T/100)**(2/9)) = 5 at T=400
hac = sm.OLS(y, sm.add_constant(X)).fit(
cov_type="HAC", cov_kwds={"maxlags": 5})
print(ols.bse["trend"], hac.bse["trend"]) # naive vs HAC SE
Failure modes of the fix
Newey-West is consistent but biased downward in small samples: too few lags under-covers, too many inflate the variance and kill power. It assumes stationarity: with a unit root in the regressor or the errors, HAC can be inconsistent. Prais-Winsten or Cochrane-Orcutt assume AR(1) exactly and are fragile if the dependence is higher-order. If you are forecasting rather than testing, model the dynamics directly (ARIMAX or a dynamic regression) instead of treating dependence as a nuisance. And if the daily series is a noisy proxy for a slower process, aggregating to weekly is the honest move.
Four hundred correlated days is not four hundred observations.
Curated: · Written: · Reviewed:
Q&A-63How do you tell whether a demand forecast is any good?
The 90-second answer
Ask what the forecast feeds before asking how accurate it is. A forecast that sets safety stock and one that sets a weekly truck plan have different cost functions, so "good" means different things for each. Then: backtest with rolling origins, score with a metric that matches that cost function, and compare against the incumbent process and a seasonal-naive baseline on identical folds. A model that cannot beat last year's same week has not earned its pipeline.
How to run the backtest
Roll the origin forward. At each origin, fit on data up to that point only, forecast the full horizon you actually deploy, and score against what happened. No leakage: no future covariates, no scalers or feature statistics computed over the whole series.
import numpy as np, pandas as pd
def seasonal_naive(train, horizon, period=52):
last = train.iloc[-period:].to_numpy()
return np.array([last[i % period] for i in range(horizon)])
def rolling_origin(y, horizon, min_train, model_fn):
rows = []
for origin in range(min_train, len(y) - horizon + 1):
train = y.iloc[:origin]
actual = y.iloc[origin:origin + horizon].to_numpy()
pred = model_fn(train, horizon)
base = seasonal_naive(train, horizon)
rows.append({
"origin": y.index[origin - 1],
"wape_model": np.abs(actual - pred).sum() / np.abs(actual).sum(),
"wape_base": np.abs(actual - base).sum() / np.abs(actual).sum(),
})
return pd.DataFrame(rows)
y is weekly demand indexed by period; min_train is the first origin you trust (say 104 weeks). WAPE is sum|actual − pred| / sum|actual|: it stays finite on low-volume series where MAPE divides by near-zero.
Pick the metric from the cost, not from habit
- Symmetric cost, high volume: WAPE or MASE.
- Asymmetric cost (stockout ≫ holding): pinball loss at the quantile you actually order at, not the mean.
- Intermittent demand: MASE, or a count model's log-likelihood. MAPE is meaningless there.
Compare against the right baseline, and look at segments
Aggregate accuracy hides the segment that pays the bills. Illustrative weekly SKU demand, 4-week horizon, 26 origins:
| Segment | Units | Seasonal-naive WAPE | Model WAPE | 80% interval coverage |
|---|---|---|---|---|
| A: 12 high-volume SKUs | 71% | 9.2% | 8.8% | 78% |
| B: 140 mid-volume SKUs | 22% | 14.6% | 12.1% | 74% |
| C: 900 intermittent SKUs | 7% | 41% | 38% | 61% |
| All | 100% | 11.4% | 10.6% | 76% |
The headline is a 0.8-point win. The decision-relevant line is coverage: on segment C the 80% intervals contain the actual 61% of the time, so safety stock computed from them under-protects. Point accuracy improved; the thing the planner relies on did not.
Failure modes and how you catch them
- Leakage: a fold's WAPE drops 30%+ versus a strictly causal rerun. Rebuild features inside the loop and compare.
- Horizon mismatch: backtesting 1-step and deploying 4-week. Score at the deployed horizon.
- Tuning on the test folds: hold out the last N origins entirely and touch them once.
- Bias, not just absolute error: a +3% bias on 400,000 units/week at $8 unit cost and 22%/yr holding is 0.03 × 400,000 × 8 × 0.22 / 52 ≈ $400/week, about $21k/year of inventory nobody asked for.
- Drift: track WAPE per origin. A rising trend means retrain cadence, not a new model.
Curated: · Written: · Reviewed:
Q&A-64Sales dropped 30% week over week. How do you decide whether that is a problem?
Start by pinning down what "sales" means and whether the number is real, then whether the drop is broad or narrow, then whether it is expected, and only then whether it is a regression. Each check is cheap and eliminates a whole class of explanations, so the order saves you from investigating a pipeline break as if it were a demand collapse.
Is the number real? Check freshness, row counts and upstream job status before anything else. If yesterday's partition is missing, or the event volume feeding the metric fell by roughly the same 30%, you have a tracking or ETL break, not a sales problem. A tag dropped in a release looks exactly like a demand crash in the dashboard.
Is it broad or narrow? Split by channel, region, product, new versus returning, and platform. A 30% total drop concentrated in one channel is a specific break: a feed paused, a price change, a checkout bug on one platform. A drop spread evenly across every segment is far more likely to be real demand or a calendar effect.
Is it expected? Compare against the same week in prior years, not the adjacent week. Week over week compares two different points in the annual cycle, so most of what it measures is the calendar.
| Check | What it rules out | Signal that ends the triage |
|---|---|---|
| Freshness, row counts, job status | pipeline or tracking break | partition missing, event volume down ~30% |
| Segment split | mix shift, single-channel break | drop concentrated in one channel or region |
| Same week prior years | holiday and trading-day effects | prior years also fell 28–31% that week |
| Noise band vs history | ordinary weekly variance | z-score inside ±2 |
Is it outside normal variance? Take the last 52 weeks of week-over-week changes and compute their mean and standard deviation. Suppose, as a labelled hypothetical, the mean is −0.4% and the standard deviation is 5.8%. A −30% week is z = (−30 − (−0.4)) / 5.8 ≈ −5.1, far outside anything the metric does on its own. If the same week fell 28% and 31% in the two prior years, that −30% sits inside the seasonal expectation and the z-score against raw history is the wrong baseline: you want the z-score against the same week in prior years.
The decision rule. Escalate when the drop is broad across segments, not explained by the calendar, outside the noise band for that week of the year, and its timing lines up with a deploy, pricing change or outage. Fix the data when it is narrow and the inputs moved with it. Keep watching when it is calendar-shaped and prior years agree.
Trade-offs. Week over week is the right lens when the metric is stable and the business runs on a weekly cadence: B2B bookings, weekly cohorts. It is noisy for consumer metrics driven by daily traffic. Year over year buys you the seasonal baseline but costs responsiveness: a genuine step change hides inside a normal seasonal dip, so you need both, YoY for expectation and WoW for detection. Comparing against a forecast instead of last week buys sensitivity, but a stale model manufactures false alarms.
Failure modes. Treating a data break as a business event burns days of incident review on a tag that was removed. Treating a real regression as seasonality means finding out a month later. Detect both by alerting on the metric and its inputs together (event volume, job success, freshness) and requiring the drop to persist across two consecutive refreshes before paging.
Curated: · Written: · Reviewed:
Q&A-65Your classifier outputs probabilities. How do you pick the cutoff?
I would treat choosing a classification threshold as a design question first and a computation question second.
The threshold follows from the cost of each error type and the capacity of whatever acts on the positives, not from 0.5, which is only optimal when the two errors cost the same and the classes are balanced.
Concretely, write the cost of a false positive and a false negative in one unit, sweep the threshold over the validation set computing expected cost at each point, choose the minimum, and re-check the choice when either the base rate or the downstream capacity changes.
The reason for that specificity is a failure I have seen: A default cutoff of 0.5 on a queue with 40 analysts produced 900 daily alerts against a capacity of 300, and the effective policy became whichever third of the alerts happened to be looked at first.
Threshold sweep with a false negative priced at 12 times a false positive.
| Threshold | Alerts per day | False negatives | Expected daily cost |
|---|---|---|---|
| 0.20 | 1,410 | 12 | $1,554 |
| 0.35 | 620 | 21 | $872 |
| 0.50 | 300 | 44 | $828 |
| 0.65 | 140 | 96 | $1,292 |
I would not consider it settled without evidence: Report expected cost and alert volume across the threshold sweep, so the chosen point is visibly the minimum under stated capacity.
The cutoff is a business decision the model cannot make for you.
Curated: · Written: · Reviewed:
Q&A-66Your model ranks well but its scores are not usable as probabilities. Why does that matter and how do you fix it?
AUC and NDCG are rank statistics: they are invariant under any strictly increasing transform of the score. Run every score through x ↦ x³ and the ordering, and therefore the metric, is unchanged. So a model can rank perfectly and still report 0.91 on cases that fire at 0.68. That is harmless until a decision multiplies the score by something: expected value = p × order size, a cost-weighted threshold, averaging scores across models, or a number shown to a human as "risk". Then the score is a probability whether or not you call it one. If the score is only ever used to sort (no multiplication, no threshold tied to a cost ratio) calibration buys you nothing and you can skip it.
Fix it on held-out data, in this order.
- Split off a calibration set the model never trained on. Training-set scores are overfit toward 0 and 1 and will look calibrated when they are not. If you have no spare data, use out-of-fold predictions.
- Measure before you fix. Reliability diagram plus expected calibration error (ECE), and Brier score and log loss: both proper scoring rules, minimized only by the true probability, so they penalize miscalibration that AUC ignores.
| Predicted bin | n | Mean predicted | Observed | Gap |
|---|---|---|---|---|
| 0.0–0.2 | 52,000 | 0.06 | 0.05 | 0.01 |
| 0.2–0.4 | 21,000 | 0.29 | 0.24 | 0.05 |
| 0.4–0.6 | 14,000 | 0.51 | 0.41 | 0.10 |
| 0.6–0.8 | 8,000 | 0.71 | 0.55 | 0.16 |
| 0.8–1.0 | 5,000 | 0.91 | 0.68 | 0.23 |
ECE = Σ (nᵢ/N)·|gapᵢ| = 0.52(0.01) + 0.21(0.05) + 0.14(0.10) + 0.08(0.16) + 0.05(0.23) = 0.054. The model is overconfident, and the error grows with the score: exactly the region a value-weighted queue spends its capacity on. A case scored 0.91 with a true rate of 0.68 has its expected value overstated by 0.91/0.68 ≈ 34%.
- Fit a monotone map on the calibration set.
# Python 3.12, scikit-learn 1.5
from sklearn.isotonic import IsotonicRegression
from sklearn.linear_model import LogisticRegression
iso = IsotonicRegression(out_of_bounds="clip").fit(scores_cal, y_cal)
p_iso = iso.predict(scores_test)
platt = LogisticRegression().fit(scores_cal.reshape(-1, 1), y_cal)
p_platt = platt.predict_proba(scores_test.reshape(-1, 1))[:, 1]
Isotonic regression is nonparametric and monotone; it needs roughly 1,000+ calibration points and returns a step function, so scores tie in flat regions: awkward if you later blend or average them. Platt scaling fits a two-parameter sigmoid: robust with a few hundred points, but it assumes the miscalibration has that shape and cannot fix a non-monotone one. For a multiclass neural net, temperature scaling, one parameter dividing the logits, is the usual first move (Guo et al., ICML 2017).
- Re-measure on a split the calibration map never saw, overall and per segment. A globally calibrated model can be badly miscalibrated inside a minority segment; if so, fit per-segment maps or add the segment as a feature.
Two failure modes to watch. Calibration is a property of the score and the distribution it was fit on: if the serving base rate drifts, the map is stale. Monitor ECE on recent traffic and refit, or correct the logit by the log odds ratio of the new prior if you know it. And a calibration set drawn from a different population than production (last quarter's traffic, a different region) will produce a map that is confidently wrong.
Curated: · Written: · Reviewed:
Q&A-67Your positive class is 0.5% of the data. Should you oversample it?
No, not by default. Oversampling doesn't add information about the minority class; it re-weights the loss. At 0.5% positives the first move is class weights, or a decision threshold picked on the true distribution. If you do oversample, you must correct the model's intercept back to the population base rate and evaluate on data that still has the 0.5% rate.
Why it moves the probabilities
A probabilistic classifier's intercept encodes the log-odds of the training base rate. Train on a 50/50 resample and the intercept encodes logit(0.5) = 0 instead of logit(0.005) = −5.29. Every score is now calibrated to a world where half the traffic is positive. Ranking is largely preserved; the numbers attached to the scores are not.
The correction is exact for logistic regression and a good approximation for any logit output layer: subtract 5.29 from the logit, equivalently multiply the predicted odds by 0.005025, before you read a score as a probability.
Options, and when each is right
| Approach | Ranking | Calibration | Use when |
|---|---|---|---|
class_weight='balanced' | unchanged vs oversampling | intercept shifts; same correction | default for LR, SVM, tree ensembles |
| Random oversampling (duplication) | same as weights | same shift | learner exposes no weight parameter |
| SMOTE | can change ranking | same shift | low-dimensional, continuous features |
| Threshold tuned on true distribution | untouched | untouched: raw scores kept | you need a decision, not a probability |
Duplication makes exact copies, so a tree can split on rows it has memorised. SMOTE interpolates between minority neighbours and in high dimensions places points where no real data exists. Neither is free.
If the only consumer is a top-k review queue, resampling is nearly harmless, and nearly pointless, since AUC and PR-AUC barely move. If a downstream cost model consumes the probability, resampling without the intercept correction is a bug.
The failure this prevents
A team trained on a 50/50 resample and evaluated on a 50/50 holdout: precision 0.94 at recall 0.7. On live traffic at 0.5%, precision was 0.07.
Holdout of 10,000 positives and 10,000 negatives: recall 0.7 gives 7,000 TP; precision 0.94 gives 447 FP, so FPR = 4.47%. Production at 0.5% over 1,000,000 rows: 5,000 positives, 995,000 negatives. TP = 3,500, FP = 0.0447 × 995,000 ≈ 44,500.
| Evaluation set | Base rate | TP | FP | Precision @ recall 0.7 |
|---|---|---|---|---|
| Rebalanced holdout | 50% | 7,000 | 447 | 0.94 |
| Production-rate sample | 0.5% | 3,500 | 44,500 | 0.07 |
Same model, same threshold, two evaluation sets. The 4.47% false-positive rate is invisible at a 50% base rate and dominates at 0.5%.
Detection
Hold out a sample at the production rate and report precision there, never on the resampled split. Alert when the observed positive rate in the scored stream drifts from 0.5%, and run a PSI check on the score distribution after every retrain. A precision drop from 0.9 to 0.07 with unchanged AUC is the signature of a base-rate mismatch, not a broken model.
Curated: · Written: · Reviewed:
Q&A-33How would you get a confidence interval for the 90th percentile of session length, where no closed form is convenient?
Assumptions I'd state first. Session length is one number per session (seconds); sessions belong to users; the unit of independence is the user, because a user's sessions share a device, a habit and a network. The estimand is the 90th percentile of the population of session lengths, not of users. Percentile convention matters: I'd use numpy's default linear interpolation and say so, because the answer moves by a second or two depending on it.
The answer. Cluster bootstrap. Resample users with replacement, pool every session belonging to the resampled users, recompute the 90th percentile, repeat 10,000 times, take the 2.5th and 97.5th percentiles of that distribution.
Why the unit is the whole answer. The bootstrap's only assumption is that the resampling scheme mirrors the data-generating process. Resampling rows treats 40 sessions from one heavy user as 40 independent observations. Hypothetical arithmetic: 2,000 users × 5 sessions = 10,000 sessions, within-user ICC ρ = 0.3. Kish design effect = 1 + (5−1)(0.3) = 2.2, so effective n ≈ 4,545. The asymptotic SE of a quantile is √(p(1−p)/n) / f(q_p), so a row-level interval comes out about 1/√2.2 ≈ 0.67× the cluster interval: a third too narrow.
import numpy as np
def p90_ci(df, draws=10_000, seed=0):
"""df: columns user_id, seconds. Cluster bootstrap over users."""
rng = np.random.default_rng(seed)
by_user = {u: g["seconds"].to_numpy() for u, g in df.groupby("user_id")}
users = np.array(list(by_user))
stats = np.empty(draws)
for i in range(draws):
picked = rng.choice(users, size=len(users), replace=True) # users, not rows
stats[i] = np.percentile(np.concatenate([by_user[u] for u in picked]), 90)
return np.percentile(stats, [2.5, 97.5])
Checked against numpy 1.26 / Python 3.11.
Trace of the mechanics on a dataset small enough to enumerate. Three users: u1 = [10, 12, 14, 16, 18], u2 = [20, 22], u3 = [100]. Full-data p90 = 45.4. Resampling 3 users with replacement gives 27 equally likely ordered draws across 10 distinct multisets:
| resampled users | draws | p90 |
|---|---|---|
| u1,u1,u1 | 1 | 18.0 |
| u1,u1,u2 | 3 | 19.8 |
| u1,u1,u3 | 3 | 18.0 |
| u1,u2,u3 | 6 | 45.4 |
| u2,u2,u1 | 3 | 22.0 |
| u2,u2,u2 | 1 | 22.0 |
| u2,u2,u3 | 3 | 68.8 |
| u3,u3,u1 | 3 | 100.0 |
| u3,u3,u2 | 3 | 100.0 |
| u3,u3,u3 | 1 | 100.0 |
Sorted, the 27 values are 18.0 (×4), 19.8 (×3), 22.0 (×4), 45.4 (×6), 68.8 (×3), 100.0 (×7). The 2.5/97.5 interval is [18.0, 100.0]. That is a useless interval, and that is the lesson: with 3 clusters the resampling distribution has 10 distinct values, so the interval is essentially the range of the data. Resolution is set by the number of clusters, not the number of sessions. A few hundred users is the floor for a 90th percentile; I'd want endpoints stable across seeds to the precision being reported.
Alternatives. BCa corrects the percentile interval's undercoverage on skewed statistics, which a heavy-tailed session distribution is; it costs a jackknife pass over users for the acceleration. If percentile and BCa disagree by more than the reporting precision, report BCa and say so. The exact order-statistic interval (P(X₍ₖ₎ ≤ q_p) = P(Binomial(n, p) ≥ k)) is distribution-free and needs no resampling, but it assumes iid, so here it is only a sanity check on the unclustered case. The delta method needs a density estimate at the 90th percentile, exactly where data is thinnest; I'd skip it.
Failure modes. Row-level resampling gives an interval too narrow by roughly √deff: detect it by running both and comparing widths. Too few clusters collapses the interval toward the sample range, as in the trace: detect it by plotting the bootstrap distribution and checking endpoint stability across seeds. Non-stationarity: if the window spans a release, a user resampled from a different week carries the wrong distribution, so block by week as well as user. Ties in discrete session lengths make the percentile a step function and the bootstrap distribution lumpy; state the convention and report the interval as [a, b].
How I'd check it. Run the row bootstrap and the cluster bootstrap on the same data; the width ratio should land near √deff. Then simulate from a generative model with a known p90 and measure coverage at 95% over 1,000 replications: that is the only thing that tells you the interval is calibrated.
Curated: · Written: · Reviewed:
Q&A-34Your metric is a ratio of two sums and you distrust the analytic standard error. What is a defensible alternative?
The 90-second answer. Run a permutation test. Shuffle the treatment labels at the unit of randomisation, recompute the ratio on every shuffle, and read where the observed statistic falls in that distribution. Under the null that assignment is unrelated to outcome, relabelling is exchangeable, so the p-value is exact for that null and needs no assumption about how the ratio is distributed. A ratio of two sums has no closed-form variance that survives a heavy-tailed denominator; the permutation distribution builds the null empirically instead of approximating it.
Why the analytic SE fails here. For R = N/D the delta method gives Var(R) ≈ (1/D²)·[Var(N) − 2R·Cov(N,D) + R²·Var(D)]. The covariance term is the one people drop, and it is rarely zero: a user with more sessions contributes to both N and D. Drop it and the SE is wrong in a direction set by the sign of Cov(N,D). The second failure is the linearisation itself: it is a first-order Taylor expansion, so it degrades as the denominator's relative variance grows. Revenue per session, cost per conversion, clicks per impression all have denominators with CV above 1, which is exactly where the approximation is worst.
Code (Python 3.11, NumPy 1.26):
import numpy as np
def permutation_p(num, den, treated, draws=20_000, seed=0):
# treated: boolean array, one entry per randomised unit (user or cluster)
obs = num[treated].sum() / den[treated].sum() \
- num[~treated].sum() / den[~treated].sum()
rng, extreme = np.random.default_rng(seed), 0
for _ in range(draws):
flag = rng.permutation(treated) # same count treated, new assignment
diff = num[flag].sum() / den[flag].sum() \
- num[~flag].sum() / den[~flag].sum()
extreme += abs(diff) >= abs(obs)
return (extreme + 1) / (draws + 1)
Trace. Four users, two per arm. Each user is (num, den): A(10, 100), B(6, 60), C(2, 40), D(3, 60). Observed assignment is A, B treated.
| Assignment | Treated ratio | Control ratio | diff |
|---|---|---|---|
| AB / CD | 16/160 = 0.1000 | 5/100 = 0.0500 | +0.0500 |
| AC / BD | 12/140 = 0.0857 | 9/120 = 0.0750 | +0.0107 |
| AD / BC | 13/160 = 0.0813 | 8/100 = 0.0800 | +0.0013 |
| BC / AD | 8/100 = 0.0800 | 13/160 = 0.0813 | −0.0013 |
| BD / AC | 9/120 = 0.0750 | 12/140 = 0.0857 | −0.0107 |
| CD / AB | 5/100 = 0.0500 | 16/160 = 0.1000 | −0.0500 |
Observed diff is +0.0500. Two of the six relabelings are at least as extreme in absolute value, so the exact two-sided p is 2/6 = 0.333. The code's (extreme+1)/(draws+1) returns 3/7 = 0.429 on the same enumeration; the +1 keeps the test valid when you sample rather than enumerate. Note the ceiling: with four users and two per arm there are only six relabelings, so no computation can produce a two-sided p below 2/6 ≈ 0.333. Resolution is set by the design, not the draw count.
When not to use it, and how it fails.
- Few clusters. If randomisation is by cluster, shuffle clusters, not users. With 4 clusters (2 per arm) there are C(4,2) = 6 relabelings and the minimum two-sided p is ≈ 0.33: the test cannot reject at α = 0.05. Use a cluster-robust analytic SE or a wild cluster bootstrap instead.
- Weak null. Permutation is exact for the sharp null (no effect for any unit). If variances differ across arms, that is not the hypothesis you want; studentise the statistic or bootstrap.
- Cost. Each draw is O(n). 20,000 draws over 10M users is 2×10¹¹ element operations in the naive loop: vectorise across draws or permute a subsample.
- Resolution. Minimum p is 1/(draws+1). 20,000 draws floors at 5×10⁻⁵: fine for α = 0.05, not for a 0.001 gate.
- Ties. Discrete ratios produce ties; use
>=with the +1 correction or the test is anti-conservative.
Detection. Run both tests and compare. If the analytic p and the permutation p disagree by more than Monte Carlo noise, the analytic formula's assumptions do not hold for this metric: most often the dropped covariance term. Check that the shuffle unit matches the randomisation unit before trusting either number.
Curated: · Written: · Reviewed:
Q&A-39Your metric is total revenue divided by total sessions, both of which vary. How do you put an interval on it?
Clarify, then answer. The estimand is a ratio of two random sums, and the unit of analysis is whatever you randomised: the user, not the session. Build per-user totals N_i (revenue) and D_i (sessions), then linearise around the ratio:
R = ΣN_i / ΣD_i
Var(R) ≈ Var(N_i − R·D_i) / (n · μ_D²)
The residual N_i − R·D_i is what carries the covariance. Expanding it gives Var(N) − 2R·Cov(N,D) + R²·Var(D). Drop the last two terms and you have the common shortcut of dividing the numerator's standard error by the mean denominator: correct only if the denominator is fixed by design. Report R ± t_{n−1,0.975}·SE, not 1.96·SE: the linearised variance is a first-order approximation and runs low in small samples.
Which way the shortcut errs. The correction is −2R·Cov(N,D) + R²·Var(D), and its sign is not fixed. For revenue and sessions per user the covariance is large and positive, a user with more sessions has more revenue, so the shortcut is too wide, often by a lot. It understates only when the denominator is noisy and nearly uncorrelated with the numerator, e.g. revenue per page view where views are a weak proxy for exposure.
Code.
import numpy as np
from scipy.stats import t
def ratio_ci(num, den, alpha=0.05):
"""num, den: per-user totals. Delta method, covariance retained."""
num, den = np.asarray(num, float), np.asarray(den, float)
n = num.size
r = num.sum() / den.sum()
resid = num - r * den # carries -2R*Cov + R^2*Var(D)
se = np.sqrt(resid.var(ddof=1) / (n * den.mean()**2))
tq = t.ppf(1 - alpha / 2, n - 1)
return r, r - tq * se, r + tq * se
def ratio_boot(num, den, B=10_000, seed=0):
"""Cluster bootstrap: resample users, not sessions."""
rng = np.random.default_rng(seed)
num, den = np.asarray(num, float), np.asarray(den, float)
n = num.size
idx = rng.integers(0, n, size=(B, n))
r = num[idx].sum(axis=1) / den[idx].sum(axis=1)
return np.percentile(r, [2.5, 97.5])
Drop users with zero sessions before either call, or a resample can divide by zero.
Worked arithmetic. Eight users, revenue roughly proportional to sessions. Hypothetical, and n=8 is far too small for a real interval: this is to show the sign and size of the correction.
| D | N | N − R·D |
|---|---|---|
| 1 | 4 | −1.056 |
| 2 | 9 | −1.111 |
| 3 | 14 | −1.167 |
| 4 | 19 | −1.222 |
| 5 | 26 | 0.722 |
| 6 | 31 | 0.667 |
| 7 | 36 | 0.611 |
| 8 | 43 | 2.556 |
R = 182/36 = 5.056, μ_D = 4.5. Var(N) = 185.07, Var(D) = 6.00, Cov(N,D) = 33.29.
Var(N − R·D) = 185.07 − 2(5.056)(33.29) + 5.056²(6.00) = 185.07 − 336.55 + 153.35 = 1.87
Correct: SE = √(1.87/162) = 0.107, CI 5.06 ± 2.365(0.107) = [4.80, 5.31].
Fixed denominator: SE = √(185.07/162) = 1.069, CI [2.53, 7.58].
The covariance term does almost all the work.
When to bootstrap instead. Revenue is heavy-tailed, so the linearisation is a first-order approximation of a skewed distribution. Prefer the cluster bootstrap when clusters are few (under ~30, where the percentile interval undercovers and you want BCa or a t-percentile), when CV_D is not small, or when the residual distribution is visibly skewed.
Follow-ups. Ratio of totals is not the mean of per-session ratios: averaging revenue per session weights a one-session user the same as a fifty-session user and answers a different question. For an A/B test you want the interval on the difference of ratios: Var(R_t − R_c) = Var(R_t) + Var(R_c) for independent arms; Deng, Knoblich and Lu's KDD 2018 delta-method guide covers that case. Fieller's theorem gives a ratio interval without linearising, at the cost of being unbounded when the denominator is not significantly different from zero.
Curated: · Written: · Reviewed:
Q&A-91Your bootstrap gives a slightly different interval each run. Is that a bug?
The 60-second answer. No: provided the wobble is smaller than the digits you report. A bootstrap interval is an estimate of an estimate: you approximate the sampling distribution of your statistic with B resamples drawn from a finite random stream, so the endpoint itself carries Monte Carlo error that shrinks as 1/√B. It is a bug when the run-to-run spread is larger than the precision you quote, or when the variation comes from unseeded global RNG state rather than from B.
Clarify first: which interval (percentile, BCa, bootstrap-t), how many resamples, and to how many digits the number is being reported.
Why it wobbles. The endpoint is a function of B draws. Across independent runs its standard deviation is roughly σ*/√B, where σ* is the spread of the bootstrap replicates of that endpoint. Quadrupling B halves the wobble; 10× B cuts it to about a third.
| B | MCSE at σ* = 0.5 | stable digits |
|---|---|---|
| 500 | 0.022 | first |
| 5,000 | 0.007 | second |
| 50,000 | 0.002 | third |
σ* = 0.5 is a plausible spread for a tail quantile at n ≈ 100; for a sample mean at n = 200 it is nearer 0.07, and the same B buys two more digits. The point is that you compute σ* from the replicates you already have and set B so that σ*/√B sits below half a unit of the last digit you print.
Trade-offs. More draws is the cheapest fix, but it removes noise, not bias.
- Percentile vs BCa. The percentile interval is transformation-respecting but biased when the bootstrap distribution is skewed. BCa adds a bias correction z0 and an acceleration a. If percentile and BCa endpoints differ by more than the MCSE, that gap is bias, and no amount of B closes it. Plot the replicate distribution; if it is visibly skewed, report BCa.
- Bootstrap vs analytic. For a mean, a t-interval; for a proportion, Wilson; for a ratio of large counts, the delta method. These carry zero Monte Carlo error and should be preferred when they exist. The bootstrap earns its place for quantiles, maxima, ratios of small counts, and fitted-model contrasts where no closed form is available.
- Bootstrap-t / studentized. Better coverage in small samples and skewed data, at the cost of a nested bootstrap and more compute.
When pinning the seed is wrong. Pinning is right for regression tests, for a number you publish, and for reproducing a colleague's figure. It is wrong when it is used to hide the MC error: a pinned seed makes the interval reproducible, not accurate, and the number you quote still carries σ*/√B of error. It is wrong when the seed is chosen after seeing which interval looks best. And it is wrong when the real problem is bias: pinning a biased percentile interval just makes the wrong answer repeatable. A pinned seed also does not survive a change in B, in library version, or in the order of a parallel reduction.
Failure mode and detection. Two analysts, same data, intervals differing in the second decimal, a day spent hunting a data bug: the run had used 500 draws, whose MCSE was larger than the difference. Detect it by rerunning with 10 seeds and comparing the observed spread to σ*/√B. If the observed spread is much larger than the MCSE, the cause is elsewhere: unseeded global RNG, nondeterministic thread scheduling, or iteration over an unordered container.
In practice. Pass an explicit generator, np.random.default_rng(seed), rather than relying on global state, record the seed and B alongside the result, and choose B so the MCSE is below the last digit you report.
Curated: · Written: · Reviewed:
Q&A-49Revenue per customer spans four orders of magnitude. What does modelling log revenue change about your conclusions?
The first thing I would pin down about modelling revenue on a log scale is which question the number is supposed to answer.
A model on the log scale estimates multiplicative effects on the median-like centre of the distribution, which is a different quantity from the additive effect on the mean that the business usually books.
Concretely, decide which quantity the decision needs, and if it is total revenue, either model the untransformed outcome with a robust estimator or transform back with the correction the log-normal implies rather than exponentiating the coefficient alone.
The reason for that specificity is a failure I have seen: A team exponentiated a log-scale coefficient and reported an 8.0% revenue lift; the mean-scale effect was 2.7% because the residual variance differed between arms and the naive back-transformation has no term for that, and the revenue forecast was overstated by roughly $600,000.
Back-transforming a log-scale effect to the mean scale.
import numpy as np
beta = 0.0770 # effect on the log scale
var_treated, var_control = 0.80, 0.90 # residual variance is not the same in both arms
# Exponentiating alone is the ratio of medians, and the mean-scale ratio only
# equals it when the two arms share a residual variance.
print(f"median ratio: {np.exp(beta) - 1:.1%}") # 8.0%
print(f"mean ratio: {np.exp(beta + (var_treated - var_control) / 2) - 1:.1%}") # 2.7%
I would not consider it settled without evidence: Compare the model's implied total against the observed total on held-out data; a systematic shortfall reveals a missing back-transformation correction.
The average of the logs is not the log of the average, and finance books the second one.
Curated: · Written: · Reviewed:
Q&A-68Your revenue total doubled after adding a join. What happened and how do you prevent it?
Most of the judgement in joins that silently multiply rows sits in what the comparison group is, not in the arithmetic.
A join on a key that is not unique on the joined side produces one output row per match, so any aggregate computed after that join counts the same fact once per duplicate.
Concretely, assert the cardinality you expect before joining, verify the key is unique on the dimension side, aggregate to the grain you need before joining rather than after, and compare row counts before and after every join as a matter of routine.
The reason for that specificity is a failure I have seen: A revenue report joined orders to a customer table that held one row per customer per address change, which multiplied every order by that customer's address history and overstated the quarter by 34%.
The check that belongs before the join, not after the number is wrong.
-- Fails loudly if the dimension is not one row per key.
select customer_id, count(*) as rows
from dim_customer
group by customer_id
having count(*) > 1
limit 10;
-- Or aggregate to the grain first, then join.
with orders_by_customer as (
select customer_id, sum(amount) as revenue from orders group by customer_id
)
select c.region, sum(o.revenue)
from orders_by_customer o join dim_customer_current c using (customer_id)
group by c.region;
I would not consider it settled without evidence: Run a uniqueness check on the join key against the dimension table, and compare the fact table's row count before and after the join.
A join is a filter and a multiplier, and the second one is silent.
Curated: · Written: · Reviewed:
Q&A-69A WHERE clause on status <> 'cancelled' dropped rows you expected to keep. Why?
The 90-second answer
The rows you lost have status IS NULL. NULL is not a value, it is the absence of one, so status <> 'cancelled' does not evaluate to true for those rows: it evaluates to unknown. WHERE keeps only rows whose predicate is true, so unknown rows are discarded exactly like false ones. Nothing errored; the filter did what three-valued logic says it should.
What you do next depends on what NULL means in that column. If NULL means "we don't know the status", dropping it may be right, but then count it and say so. If NULL means "not applicable" or "never cancelled", you want status IS DISTINCT FROM 'cancelled' (Postgres; MySQL spells it <=>; SQL Server has no null-safe operator, so you write (status <> 'cancelled' OR status IS NULL)), or COALESCE(status, '') <> 'cancelled'. The durable fix is NOT NULL DEFAULT on the column so the question cannot come up again.
Why: the truth table
status | status <> 'cancelled' | status = 'cancelled' | NOT (status <> 'cancelled') |
|---|---|---|---|
'active' | TRUE | FALSE | FALSE |
'cancelled' | FALSE | TRUE | TRUE |
NULL | UNKNOWN | UNKNOWN | UNKNOWN |
Note the third row: NOT (status <> 'cancelled') is not the same predicate as status = 'cancelled'. Both are unknown for NULL, so a filter and its apparent negation can each drop the same rows.
Reconcile the counts before you argue about the number
Run the four predicates side by side on a 50,000-row accounts table where 4,000 rows are 'cancelled' and 4,100 have a NULL status:
select
count(*) as all_rows, -- 50000
count(*) filter (where status <> 'cancelled') as not_equal, -- 41900
count(*) filter (where status is distinct from 'cancelled') as distinct_from, -- 46000
count(*) filter (where status is null) as null_status, -- 4100
count(status) as non_null_status -- 45900
from accounts;
not_equal is short by exactly null_status: 41,900 + 4,100 = 46,000 = distinct_from. That reconciliation is the evidence. If the gap does not equal the NULL count, your problem is somewhere else and you should stop blaming NULL.
Where else this bites
status NOT IN ('cancelled', NULL)returns zero rows.x NOT IN (a, NULL)expands tox <> a AND x <> NULL, and the second conjunct is unknown for every row.- A predicate on the nullable side of a
LEFT JOINplaced inWHEREinstead ofONsilently converts it to an inner join, because the NULL-extended rows fail the predicate. - Aggregates skip NULLs rather than failing:
AVG(amount)divides by the non-null count, andSUM(amount)over an all-NULL group returns NULL, not 0.COUNT(status)andCOUNT(*)answer different questions. GROUP BY statusputs every NULL in one group, andDISTINCTtreats NULLs as equal to each other: the opposite of what=does.CHECK (status <> 'cancelled')passes whenstatusis NULL, because a CHECK rejects only FALSE. A unique index likewise permits many NULLs.
Cost of the fix
IS DISTINCT FROM and COALESCE(...) are not sargable against a plain index on status, so on a large table they force a scan. If that filter is hot, add an expression index on COALESCE(status, '') or make the column NOT NULL with a backfill: the latter is a migration, but it removes the class of bug rather than one instance of it.
Curated: · Written: · Reviewed:
Q&A-70You need each customer's order sequence number and running total without collapsing rows. How?
What I'd say first: use window functions. One pass over orders, every row preserved, sequence and running total computed per customer.
-- PostgreSQL 16
SELECT
customer_id,
order_id,
ordered_at,
ROW_NUMBER() OVER w AS order_seq,
SUM(amount) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY ordered_at, order_id);
PARTITION BY customer_id restarts both counters per customer. ORDER BY ordered_at, order_id: order_id is the unique tiebreak that makes the sequence deterministic. The explicit ROWS frame is what makes the total cumulative per row rather than per peer group.
Trace. Four rows:
| customer_id | order_id | ordered_at | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-05 | 40 |
| 1 | 102 | 2024-01-05 | 25 |
| 1 | 103 | 2024-01-09 | 10 |
| 2 | 201 | 2024-01-03 | 60 |
Output:
| customer_id | order_id | order_seq | running_total |
|---|---|---|---|
| 1 | 101 | 1 | 40 |
| 1 | 102 | 2 | 65 |
| 1 | 103 | 3 | 75 |
| 2 | 201 | 1 | 60 |
The trap. Drop order_id from the ORDER BY and drop the frame clause:
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at)
With an ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats all rows tied on the ORDER BY keys as one peer group. Both 2024-01-05 rows now report 65, then 103 reports 75. The totals stay monotonic, so nothing looks broken until someone sums the column. ROW_NUMBER() under the same ORDER BY is worse: 101 and 102 can come back in either order, and which one gets seq 1 depends on the plan. A re-run after an index change can renumber history. RANK and DENSE_RANK would repeat a number across the tied rows, which is not a sequence.
Two fixes, both needed: a unique tiebreak in every window ORDER BY, and an explicit ROWS frame whenever the metric is cumulative.
Alternatives, and when not to use this. GROUP BY customer_id gives you the final total per customer and nothing else: it collapses rows, which is the requirement here. A correlated subquery (SELECT SUM(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id AND o2.ordered_at <= o.ordered_at) is O(n²) without a matching index and silently wrong on ties, since <= pulls in the peer row. A self-join has the same problem. Window functions are the right tool; reach for GROUP BY only when you genuinely want one row per customer.
Failure modes to name. Non-deterministic ordering, as above: detect it by running the query twice and diffing, or by asserting COUNT(DISTINCT order_seq) = COUNT(*) per customer. NULLs in ordered_at sort last in Postgres ASC, so undated orders land at the end of the sequence; decide that explicitly with NULLS FIRST/LAST. Window functions can't be filtered in WHERE: wrap in a CTE and filter on order_seq outside. And the sort is real work: one sort per distinct window definition (identical definitions share one), sized by the largest partition. A customer with 5M orders can spill past work_mem; an index on (customer_id, ordered_at, order_id) can let Postgres feed sorted input and skip the sort, which EXPLAIN (ANALYZE) shows as a WindowAgg over an Index Scan instead of a Sort.
Verification. Run the query twice and compare row-by-row; keep the tiebreak; check EXPLAIN for the sort.
Curated: · Written: · Reviewed:
Q&A-71Write the shape of a query that reports week-N retention by signup cohort. What decisions does it force you to make?
A week-N retention query is three decisions wired into SQL: what the cohort is keyed on, what counts as a return, and what window "week N" covers. Here is the shape, then the decisions it forces.
with cohorts as (
select user_id,
date_trunc('week', signed_up_at) as cohort_week
from users
),
activity as (
select c.cohort_week,
floor(extract(epoch from e.occurred_at - c.cohort_week) / 604800)::int as week_n,
e.user_id
from cohorts c
join events e using (user_id)
)
select cohort_week,
week_n,
count(distinct user_id) as retained
from activity
where week_n between 0 and 8
and cohort_week <= current_date - interval '9 weeks'
group by 1, 2
order by 1, 2;
Postgres. date_trunc('week', ...) lands on Monday 00:00, so week_n = 0 is the signup week itself and week_n = 1 is the first full week after it. 604800 is 7 x 86400 seconds. The output is long format (one row per (cohort, week)) and you pivot in the BI layer.
1. Cohort key. Signup week, signup day, or first-activity week. Signup week gives ~52 cohorts a year with enough users each to be stable; signup day gives 365 thin ones. First-activity week reclassifies users who signed up and did nothing, which flatters activation and hides a broken signup funnel.
2. What counts as returning. Any event, or one specific event? Counting any event picks up a background sync ping and inflates the number. Counting a core action (session start, first meaningful write) drops lurkers. This choice usually moves the number more than a quarter of product work does.
3. Bounded or cumulative. week_n = 4 exactly, or week_n >= 4. Bounded shows decay and can rise or fall week to week; cumulative is monotone and hides churn. Both are defensible; the table has to say which one it is.
4. Where week numbering starts. Is week 0 the signup week or the first week after? An off-by-one here shifts every cell by a week and nobody notices, because the table still looks like a triangle.
5. Incomplete cohorts. A cohort that signed up three weeks ago has no week-8 data. The cohort_week <= current_date - interval '9 weeks' predicate drops it. The alternative, showing it as 0% or as a partial count, makes the right edge of the triangle look like a retention collapse when it is just missing time.
6. Denominator. Distinct users in the cohort, or users still subscribed and not deleted? Filter deleted users out of the denominator and retention rises as churn removes people from the base.
Where this bites: two teams report week-4 retention of 41% and 63% for the same product. One measured bounded week 4, the other cumulative since signup. Both charts are titled "week 4 retention." Neither number is wrong; the labels are. (Illustrative figures.)
You can detect it without arguing. Run both definitions over the same data and diff the cells. If the gap is more than a point or two, the definition is producing the movement, not the product.
So publish the definition next to the table: cohort key, return event, bounded or cumulative, denominator, and the timestamp the table was generated. When someone disputes a number, reproduce it under their definition before you dispute back.
Curated: · Written: · Reviewed:
Q&A-72Define a session from raw events with a 30-minute inactivity gap, in SQL.
Clarify first: a session is a maximal run of events for one user where each event is within 30 minutes of the previous one. The event that opens a new session belongs to the new session, not the old one. Assume Postgres 16, events(user_id bigint, occurred_at timestamptz), append-only, and that 30 minutes is a product decision I was given rather than one I'm choosing.
Then: order per user, measure the gap to the previous row with LAG, mark boundaries, and take a running sum of the marks. The session id exists only in the query result.
with marked as (
select user_id,
occurred_at,
case when occurred_at - lag(occurred_at) over (partition by user_id order by occurred_at)
> interval '30 minutes'
then 1 else 0 end as is_new_session
from events
),
sessionised as (
select user_id,
occurred_at,
sum(is_new_session) over (partition by user_id order by occurred_at) as session_seq
from marked
)
select user_id,
session_seq,
min(occurred_at) as started_at,
max(occurred_at) as ended_at,
count(*) as event_count
from sessionised
group by user_id, session_seq;
The first row per user has lag(...) = null; null > interval is null, so the case falls to 0 and the first session is numbered 0 without a special case. The default RANGE frame means two events sharing an occurred_at get the same running sum and land in the same session, which is what you want: adding a tiebreaker to the ORDER BY would split them.
Trace, one user, five events:
| occurred_at | gap | is_new_session | session_seq |
|---|---|---|---|
| 10:00 | – | 0 | 0 |
| 10:10 | 10m | 0 | 0 |
| 10:45 | 35m | 1 | 1 |
| 10:50 | 5m | 0 | 1 |
| 11:30 | 40m | 1 | 2 |
Three sessions: 10:00–10:10 (2 events), 10:45–10:50 (2), 11:30 (1).
Alternatives, and when this is the wrong tool
- Materialised sessions table, assigned at ingest. Keep
sessions(user_id, session_id, last_seen_at); on each event, ifoccurred_at - last_seen_at <= 30 minreuse the session, else insert a new one. Reads become a join instead of a full re-scan, andsession_idis durable so other tables can reference it. The cost is a read-modify-write per event, and it breaks on out-of-order arrival: a late event that lands inside an existing gap should have merged two sessions, and the table can't retroactively merge them. - Self-join /
NOT EXISTS. A session starts where no earlier event for the same user falls in the preceding 30 minutes. Works on engines without window functions, but it's O(n²) per user without a supporting index, and it's what people write when they can't useLAG. - Upstream in the stream processor. Flink session windows or Spark's
session_windowdo exactly this on an unbounded stream, merging windows as events arrive and emitting when the gap expires. That's the right answer when sessions must appear within seconds; the price is that every session is emitted at least 30 minutes late, and backfills run through a different code path than live traffic, so the two can disagree.
One-pass SQL is the wrong tool when session_id must be stable and referenced by other tables, this query renumbers everything downstream the moment a late event is inserted, when the stream is unbounded and you need sessions in near-real-time, or when the table is large enough that a per-user sort on every dashboard load is the cost you're paying. It is the right tool for backfills, ad-hoc analysis, and for pinning down the semantics that an incremental version has to match.
Failure modes
- Per-device instead of per-user. Partitioning by
(user_id, device_id)splits a cross-device journey; I've seen that inflate session counts by 22% and make average session length look like a regression. Partition by user, and if device matters, report it as an attribute of the session rather than a key. - Timezone.
timestamptzarithmetic is absolute, so DST is a non-issue.timestampwithout a zone stores wall-clock, so a gap spanning a DST fall-back reads as 90 minutes when 30 elapsed. - Late events. Inserting an event at 10:20 into the trace above merges sessions 0 and 1 and renumbers session 2 to 1. Anything keyed on
session_seqis now wrong.
Validate by reconciling: sum(event_count) from the sessionised result must equal count(*) from events, and hand-trace a few users with known journeys against the output.
Curated: · Written: · Reviewed:
Q&A-73Your pipeline delivers at least once, so events repeat. How do you count them correctly?
Clarify first. "Count" can mean two things: deliveries (what the pipeline moved) or distinct logical events (what happened). Almost every business metric wants the second. I'll assume the producer stamps each event with a stable id that survives retries, and that retries are bounded by some horizon: say 30 days. If either assumption is false, the answer changes.
The answer. Deduplicate on the event id, not on the row. The row differs on ingestion timestamp, offset, and metadata, so a row-level dedup counts retries as new events. Keep one row per event id with a deterministic tiebreak, then aggregate.
-- Postgres 16. raw_events is append-only: one row per delivery.
-- event_id is producer-supplied and stable across retries.
with ranked as (
select event_id, payload, ingested_at,
row_number() over (
partition by event_id
order by ingested_at asc, source_offset asc
) as rn
from raw_events
where ingested_at >= now() - interval '30 days' -- >= retry horizon
)
select event_id, payload, ingested_at
from ranked
where rn = 1;
Trace on three rows:
| event_id | ingested_at | source_offset |
|---|---|---|
| e1 | 10:00:00 | 5 |
| e1 | 10:00:03 | 9 |
| e2 | 10:00:01 | 6 |
Partition e1 orders to 10:00:00 (rn=1) and 10:00:03 (rn=2); e2 is alone (rn=1). Output is e1@10:00:00 and e2@10:00:01. Count 2, not 3. The count itself is then select count(*) from deduped, or select count(distinct event_id) from raw_events where ingested_at >= now() - interval '30 days' if you don't need the rows.
Alternatives, and when they're better.
| Approach | Dedup point | Cost | Breaks when |
|---|---|---|---|
| Keyed window dedup (above) | Stage before aggregation | State per key for the window | Retry lands after the window |
Idempotent upsert into a keyed sink (MERGE / ON CONFLICT) | Sink | Storage per key, no re-scan | Key unstable, or sink not transactional |
count(distinct event_id) | Query time | Full scan, not incremental | Raw retention shorter than retry horizon |
| Exactly-once producer (Kafka transactions, Flink checkpoint + idempotent sink) | Source/sink boundary | Transaction coordinator, checkpoint state | Re-read from an external source, or a non-transactional sink |
| HyperLogLog | Query time | Bounded memory, ~2% error | You need exact counts |
The upsert is the one I'd pick for a streaming sink: it makes the dedup idempotent, so a replay of the dedup job itself doesn't double-count. The windowed query is fine for batch. Exactly-once at the producer is the strongest guarantee but only inside the system boundary: the moment you re-read from an external source or write to a non-transactional sink, duplicates are back.
When not to use keyed dedup. If the producer doesn't emit a stable id, or reuses ids across restarts, the key is wrong and you'll undercount. Fall back to a content hash or a natural key, and accept that two genuinely distinct events with identical content collapse into one.
Failure modes and how to see them.
- Window shorter than the retry horizon: a late retry is counted as new. Detect with
count(*) - count(distinct event_id)per source per day; a step change means the horizon moved. - Key reuse across producer restarts: undercount. Detect by grouping on event_id and counting distinct payload hashes: anything above 1 is a collision.
- Non-deterministic tiebreak:
order by ingested_atalone leaves ties, so the chosen row varies between runs. Detect by rerunning the dedup and diffing the output set.
Curated: · Written: · Reviewed:
Q&A-74Why does moving a condition from HAVING to WHERE change both the result and the runtime?
The deciding question is whether the predicate references a grouping key. Three cases, and only one is a free move.
- Predicate on a column in the
GROUP BYlist →WHEREandHAVINGreturn identical rows.WHEREis faster. - Predicate on an aggregate (
sum(amount) > 150) →HAVINGonly;WHEREcannot reference an aggregate. - Predicate on a column that is neither grouped nor aggregated (
ordered_at) →HAVINGis illegal in standard SQL.WHEREis the only legal placement, and it changes what the aggregate sees.
Case 3 is where the premise bites: the result changes because WHERE removes rows before the aggregate runs, so sum(amount) is computed over a smaller set.
Worked example. Table orders(customer_id, ordered_at, amount), range-partitioned by month on ordered_at:
| customer_id | ordered_at | amount |
|---|---|---|
| 1 | 2025-11-05 | 100 |
| 1 | 2026-01-10 | 50 |
| 2 | 2026-02-01 | 200 |
| 3 | 2025-12-20 | 300 |
select customer_id, sum(amount)
from orders
where ordered_at >= date '2026-01-01'
group by customer_id;
-- 1 | 50
-- 2 | 200
The HAVING version of the same predicate:
select customer_id, sum(amount)
from orders
group by customer_id
having ordered_at >= date '2026-01-01';
Postgres rejects it: ERROR: column "orders.ordered_at" must appear in the GROUP BY clause or be used in an aggregate function. MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5) rejects it too, error 1055. With that mode disabled MySQL runs it and evaluates the predicate against an arbitrary row of each group: customer 1's group is included or excluded depending on whether the engine picks 2025-11-05 or 2026-01-10, and the sum is 150 either way. Nondeterministic, not a slower version of the same answer.
Runtime. On a hypothetical 48-partition table, 1M rows per partition, 48M rows total:
WHERE: the planner prunes to the 3 partitions covering 2026-01 onward.EXPLAIN (ANALYZE)showsPartitions removed: 45, ~3M rows into the HashAggregate, ~1.2 s.HAVING(if it ran):Partitions removed: 0, 48M rows into the HashAggregate, then the group filter discards most of the output. ~19 s.
Those seconds are illustrative for that table shape; the ratio is what to expect, not the absolute numbers.
Even in case 1, where the results match, the plans differ. where customer_id > 900000 on 100M rows with 1M distinct customers feeds 10M rows into the hash table; having customer_id > 900000 builds a 1M-entry table over all 100M rows and then discards 90% of it. Same answer, different memory and time.
Before moving a predicate:
- On a grouping key? Move it; results are preserved.
- On an aggregate? It stays in
HAVING. - On anything else? It belongs in
WHERE, and the aggregate's input changes: confirm that is what you want.
Failure modes:
- Pruning defeated by a function on the partition key:
where date_trunc('month', ordered_at) >= date '2026-01-01'scans every partition, same asHAVING. Detect inEXPLAIN:Partitions removed: 0. - Implicit cast on the partition key (
ordered_atstored as text) disables pruning the same way. - MySQL legacy mode accepting a non-grouped
HAVINGpredicate without error: checksql_modebefore trusting the result.
Detection: EXPLAIN (ANALYZE, BUFFERS), comparing rows entering the Aggregate node and the Partitions removed count between the two placements.
Curated: · Written: · Reviewed:
Q&A-75Compute a four-step funnel where each step must happen after the previous one for the same user. What is the trap?
The trap is that counting each step independently ignores order. Count users who did step 3 and users who did step 2 as separate populations and a user who reached step 3 without step 2 still lands in the step-3 count, so step 3 can outnumber step 2 and the step-to-step rate prints above 100%.
Assumptions: funnel_events(user_id, step, occurred_at), one row per event, occurred_at in UTC, and the funnel must complete inside a stated window. 7 days below. Postgres; FILTER is standard SQL and has been supported since 9.4.
The fix is two moves: take each user's first occurrence of each step, then require the timestamps to be strictly increasing, with the window bounding the whole sequence.
with firsts as (
select user_id,
min(occurred_at) filter (where step = 'view') as t1,
min(occurred_at) filter (where step = 'cart') as t2,
min(occurred_at) filter (where step = 'pay') as t3,
min(occurred_at) filter (where step = 'confirm') as t4
from funnel_events
group by user_id
)
select count(t1) as s1,
count(*) filter (where t2 > t1
and t2 <= t1 + interval '7 days') as s2,
count(*) filter (where t3 > t2 and t2 > t1
and t3 <= t1 + interval '7 days') as s3,
count(*) filter (where t4 > t3 and t3 > t2 and t2 > t1
and t4 <= t1 + interval '7 days') as s4
from firsts;
count(t1) counts non-null first views; every later filter is null-safe, because null > t1 is null and the row is not counted. A self-join on user_id with t_next > t_prev per step is the same logic written the long way.
Trace it on six users, all events inside the window:
| user | view t1 | cart t2 | pay t3 | confirm t4 |
|---|---|---|---|---|
| u1 | 1 | 2 | 3 | 4 |
| u2 | 1 | 2 | 3 | — |
| u3 | 1 | 2 | — | — |
| u4 | 1 | — | — | — |
| u5 | — | — | 3 | 4 |
| u6 | — | — | 3 | 4 |
Counting steps independently gives 4, 3, 4, 3: step 2 → step 3 reads 4/3 = 133%. The ordered query gives 4, 3, 2, 1. u5 and u6 are the deep-link case: they entered at pay and never carted, so they inflate step 3 under independent counting and drop out of the ordered funnel.
Failure modes worth naming:
- First-occurrence-per-step is an approximation. A user whose first cart precedes their first view but whose second cart follows it is dropped. When that matters, match the sequence instead:
match_recognize(Oracle, Snowflake) or a lateral join that picks the earliest qualifying next step per user. - Strict
>matters. Two events with identical timestamps fail the comparison and the user drops out; with second-granular timestamps, carry a monotonic sequence column or accept the loss. - The window is a parameter, not a detail. 7 days and 30 days are different funnels; state which one you mean.
- Late-arriving events change
min(occurred_at)after the fact. Recompute on a schedule or gate on a watermark. - Clock skew across services can invert two timestamps. Store UTC and compare on one clock.
Test: assert the counts are non-increasing across steps. An ordering bug then fails loudly instead of shipping a 133% conversion rate.
Curated: · Written: · Reviewed:
Q&A-76A customer's plan changed in March. How do you attribute February's revenue to the right plan?
Core answer. "Plan" is a mutable attribute of the customer, and revenue facts carry their own timestamp. So this is a point-in-time join: attribute each February order to the plan that was in force at the order's timestamp, not the plan the customer is on today. The March change closes the February plan's validity interval and opens a new one; February's orders fall inside the old interval and stay there.
Concretely, keep the customer dimension as a Type 2 slowly changing table: one row per (customer_id, plan) interval, valid_from inclusive and valid_to exclusive, with the open row using a sentinel far-future date rather than NULL. A NULL valid_to silently drops every open row out of the < comparison.
-- Postgres (date_trunc is stable across 12-16)
select date_trunc('month', o.ordered_at) as month,
d.plan,
sum(o.amount) as revenue
from orders o
join dim_customer_history d
on d.customer_id = o.customer_id
and o.ordered_at >= d.valid_from
and o.ordered_at < d.valid_to
group by 1, 2;
Trace it. Dimension rows for customer 42:
| customer_id | plan | valid_from | valid_to |
|---|---|---|---|
| 42 | basic | 2023-06-01 | 2024-03-04 |
| 42 | enterprise | 2024-03-04 | 9999-12-31 |
An order from 42 on 2024-02-17 satisfies 2024-02-17 >= 2023-06-01 and 2024-02-17 < 2024-03-04, so it lands on basic. An order on 2024-03-10 lands on enterprise. February is February's plan.
Alternatives and when to pick them.
| Design | Attribution rule | Pick it when | What it costs |
|---|---|---|---|
| Type 2 validity ranges | fact timestamp inside the interval | default; changes are recorded in order and you need point-in-time correctness | range join, and backdated corrections force you to restate intervals |
| Periodic snapshot | join the fact to the dimension snapshot for the period | the business rule is "plan as of period close", not "plan when the order was placed" | storage grows with dimension × periods; intra-period changes vanish |
| Event stream / change log | join the fact to the latest change event at or before the fact time | changes arrive out of order or are backdated, and you want the raw log immutable | every query pays as-of logic unless you materialise the ranges |
The deciding question is what the business means by "the right plan". If it means the plan in force when the revenue was earned, use the as-of join. If it means the plan the account held when the month closed, a month-end snapshot matches the rule and is cheaper to build. If corrections are frequent and backdated, keep the append-only change log and derive the intervals from it, so a restatement is a rebuild rather than an edit.
Failure mode. Joining historical facts to the current-state table. Every customer who upgraded in March shows their new plan against February revenue, so the February tier mix is wrong in both directions: the tier people upgraded into is overstated, the one they left is understated. Detect it by reconciling: revenue grouped by plan must sum to the ungrouped total for the same period, and a customer who changed plan must appear under both plans in the months they held each. The same as-of logic applies to any mutable dimension attribute (region, segment, owner) not just plan.
Curated: · Written: · Reviewed:
Q&A-77Your exploratory query takes nine minutes. What do you look at before asking for a bigger warehouse?
The short version. Before asking for a bigger warehouse, find out whether the query is reading data it doesn't need. In a columnar warehouse, elapsed time tracks bytes scanned, so the question is: how many bytes did this query read, and how many does the answer actually require? If those numbers are close, more compute is the honest answer. If they're far apart, the query is the problem.
Assumptions. Columnar engine (Snowflake, BigQuery, Redshift-class), a partitioned or clustered table, and an exploratory query where I care about iteration speed rather than a one-off report. If the table is unpartitioned and unclustered, the first fix is the table layout, not the warehouse.
Where the bytes go, in the order I check.
- Partition pruning: filter on the partition column directly.
- Column pruning: select only the columns needed; a
select *on a wide table can multiply the scan. - Predicate pushdown: keep predicates sargable so the engine filters before the join, not after.
- Join shape: a broadcast or a pre-aggregated side beats shuffling a large table repeatedly.
- Sampling and materialisation: sample during exploration; when the same scan repeats, materialise a narrow intermediate table once.
The failure I've seen most often is a predicate that hides the partition column from the planner:
-- No pruning: the function hides the partition column from the planner.
where date_trunc('day', event_ts) between date '2026-08-01' and date '2026-08-07'
-- Prunes: the planner sees a range on the partition column itself.
where event_ts >= timestamp '2026-08-01' and event_ts < timestamp '2026-08-08'
That rewrite took one query from 2.1 TB scanned per run to 34 GB and 22 seconds: same result set.
When more compute is the right call. Three cases where I stop rewriting and size up:
- Irreducible scan. Bytes scanned is already close to bytes the result needs: a full-history aggregation with no partition key, a distinct count over every row, a wide join where both sides are genuinely needed. No predicate fixes this; the engine must touch the data.
- Deadline-driven one-off. A one-hour job that unblocks a decision today. If the rewrite is a day of engineering and the larger warehouse bills a few dollars for the run, take the compute and fix the query later.
- Concurrency, not query. The query is already minimal but queues behind other work; the warehouse is undersized for the concurrency, and a bigger size buys throughput, not a faster plan.
How I decide with evidence. Compare bytes scanned against bytes the result requires, before and after each change:
| Bytes scanned vs. required | Reading |
|---|---|
| ≈ equal | Compute is the lever; size up |
| 10× or more | Pruning is the lever; rewrite first |
Failure modes to rule out before concluding. Stale statistics producing a bad plan; the small-file problem inflating scan overhead; skew concentrating work on one node; an exploding join multiplying rows before the filter. Each shows up in the plan as a scan or shuffle far larger than the input.
Rewriting the predicate is usually the cheaper fix, but "usually" isn't a rule. The ratio of bytes scanned to bytes required is.
Curated: · Written: · Reviewed:
Q&A-78Should your analytics layer mirror the production schema?
No: mirror the grain, not the schema. Model fact tables at the grain your questions are asked at, resolve the joins once in the transformation layer, and let the OLTP schema change without rewriting every dashboard.
Assumptions that decide this: the source is a normalised OLTP schema (3NF, mutable rows, history in separate tables), the analytics layer serves several consumers, and the queries are read-heavy and repeated. If the source is already an append-only event stream at the analytical grain, there is nothing to mirror: you read it.
Why mirroring fails. A normalised schema is tuned for consistent single-row writes; a reporting layer is tuned for repeated scans at a fixed grain. Mirroring pushes the same join logic into every downstream query, where each consumer resolves it independently and can get it wrong independently. The failure is not CPU: a star join against a small dimension is cheap on a columnar warehouse. The failure is fan-out: joining a fact to a one-to-many history table multiplies rows and silently inflates every aggregate.
Worked example. Orders: o1 (customer c1, $100), o2 (c1, $50), o3 (c2, $200). Subscription history has 2 rows for c1 and 1 for c2.
select sum(o.amount)
from orders o
join subscription_history s on s.customer_id = o.customer_id;
Trace: o1 matches 2 history rows, o2 matches 2, o3 matches 1 → 100+100+50+50+200 = 500. True order revenue is 100+50+200 = 350. The join overstated it by 150, or 43%, with no error raised. Three teams writing that join three ways is how one board pack ends up with four revenue numbers.
What to build instead. A small number of fact tables at explicit grains, conformed dimensions, and the join resolved once in transformation. Point-in-time joins belong in the model, not in each query:
-- one row per order, plan as of the order date
select o.order_id, o.amount, s.plan_id
from orders o
join subscription_history s
on s.customer_id = o.customer_id
and o.ordered_at >= s.valid_from
and o.ordered_at < s.valid_to;
| Table | Grain | Joins resolved |
|---|---|---|
| fct_order | One row per order | customer, plan as of order date |
| fct_subscription_day | One row per subscription per day | plan, price, discount |
| dim_customer_history | One row per customer per attribute period | — |
State the grain on the table definition, not in a wiki page each reader has to find.
When mirroring is right. Keep an immutable raw copy of the source (landing/staging) for audit, replay and backfill: that is not the same as exposing the OLTP schema to consumers. If you have one consumer, one question, and no history tables, a straight copy is fine and cheaper than a model.
Failure modes and how to catch them. Fan-out double counting: assert row count and sum before and after every join, and reconcile the headline metric against the raw source before publishing. Late-arriving or missing dimension rows: an inner join drops facts silently. Monitor null FK rate and fact row count against source row count. Schema drift in OLTP (renamed column, new status value) changing semantics without breaking the pipeline: contract tests on source columns plus freshness and volume checks.
Resolve the join once, or resolve it differently in every notebook.
Curated: · Written: · Reviewed:
Q&A-79Your long-running report queries the production database directly. What can go wrong beyond load?
Assume Postgres 16, an OLTP primary, and a report that runs 30 s to 10 minutes. Load is the obvious cost; the ones that cause incidents are the four below.
1. The report has no single point in time. Under READ COMMITTED each statement takes a fresh snapshot, so a multi-statement report can read orders at T1 and payments at T2 and reconcile two different databases. Under REPEATABLE READ the snapshot is taken at the first statement and held for the whole transaction: internally consistent, but that is also what makes the next three problems worse.
2. It holds back vacuum. An open snapshot pins xmin. Autovacuum cannot remove any row version newer than that, so dead tuples accumulate for the report's whole duration. Hypothetically, on a table taking 5,000 updates/s, a 90 s report strands ~450,000 dead tuples; at ~120 bytes per version that is ~54 MB of bloat per run, compounding if the report runs every few minutes. On a replica with hot_standby_feedback = on, the replica's snapshot is fed back to the primary, so the primary's vacuum is held back too.
3. Its ACCESS SHARE lock blocks DDL, and the DDL then blocks everything. This is the one that pages people.
-- t=0s, session A (report)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(amount) FROM orders WHERE created_at < now(); -- snapshot taken here
-- ... 90 s of work, transaction still open
-- t=10s, session B (migration)
ALTER TABLE orders ADD COLUMN channel text; -- wants ACCESS EXCLUSIVE, queues behind A
-- t=11s, session C (checkout)
SELECT * FROM orders WHERE id = $1; -- ACCESS SHARE, queues behind B
C waits 79 s for a lookup that normally takes 0.3 ms. The report did not just burn CPU; it serialized the application behind a DDL statement. Detect with SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';. Mitigate by setting lock_timeout = '2s' on migrations so they fail fast instead of queueing, and by keeping reports off the primary.
4. It pins a pool connection. A 20-connection pool with a 90 s report every 30 s loses 15% of its capacity to reporting, and the app's p99 degrades before the database looks busy.
5. On a replica, lag and replay conflict. The report reads stale data by definition, and max_standby_streaming_delay defaults to 30 s: if WAL replay needs to remove a row version the report is reading, the replica cancels it with "canceling statement due to conflict with recovery".
| Option | Consistency | Cost on primary | Staleness | Use when |
|---|---|---|---|---|
| Primary, READ COMMITTED | None across statements | Locks, xmin hold, cache churn | 0 | Single aggregate under ~1 s |
| Primary, REPEATABLE READ | Snapshot at first statement | Same, longer xmin hold | 0 | Multi-statement report that must reconcile |
| Replica | Snapshot on replica | None, unless hot_standby_feedback | Seconds to minutes | Report tolerates staleness |
| Materialized view / CDC to warehouse | Snapshot at refresh | Refresh cost, not query cost | Refresh interval | Same heavy report, repeatedly |
The deciding question is whether the report must be internally consistent (then snapshot) and whether it tolerates staleness (then replica). A direct primary read is fine when it is one statement, under a second, on a table nobody is migrating.
A reconciliation report that read orders and payments twelve minutes apart in one READ COMMITTED transaction reported a $240,000 discrepancy that was entirely orders paid during the query's own run. Rerun pinned to a fixed timestamp: if the number changes, it was read skew, not a real discrepancy. On MySQL/InnoDB the same long read transaction shows up as purge lag and a growing history list length rather than an xmin horizon.
Curated: · Written: · Reviewed:
Q&A-80When would you reach for a document or graph store instead of SQL for an analytical question?
The 90-second answer
Two questions decide it: is the traversal depth known when I write the query, and is the unit of retrieval a whole aggregate or a set of rows?
Assumptions: the data is already loaded in both stores, and the question is analytical, an aggregate or a traversal, not transactional.
Default stays relational. Reach for a graph store when the question is a variable-length path (connected components, shortest path, "who is within N hops") where N is a parameter rather than a literal in the query. Reach for a document store when each record is a heterogeneous nested aggregate you fetch whole and rarely join across: product catalogues with per-category attributes, event payloads whose schema drifts. Neither replaces SQL for aggregation; both are worse at it.
Why the shape decides it
A relational planner builds a fixed join tree. A recursive CTE re-runs a join per iteration, and the engine has no bound on how many iterations a cyclic graph needs. Graph engines keep adjacency with the edge, so a traversal touches only the edges it walks: cost tracks the subgraph visited, not the table size. Document stores win on read locality: one fetch returns the whole aggregate, no joins, no wide sparse table. They lose on anything spanning documents.
Worked example (hypothetical figures)
Fraud-ring analysis over an account-to-account transfer edge list:
| Formulation | Depth reached | Runtime | Rings found |
|---|---|---|---|
| Seven self-joins in SQL | 7 | 6h 10m | 412 |
| Connected components over the same edge list | unbounded | 4m | 486 |
The 74 missing rings are the point: the SQL version did not fail, it returned a smaller answer with no error.
The truncation is silent
WITH RECURSIVE reach(acct, depth) AS (
SELECT acct, 0 FROM seeds
UNION ALL
SELECT e.dst, r.depth + 1
FROM reach r JOIN edges e ON e.src = r.acct
WHERE r.depth < 7
)
SELECT DISTINCT acct FROM reach;
Rows at depth 7 are emitted but not expanded, so anything reachable only at depth 8 is absent, and nothing in the result marks it. Drop the WHERE and a cyclic edge list never terminates, PostgreSQL 14 added a CYCLE clause for that, but the depth cap is still a business assumption baked into the query text.
When not to
- Graph store: full scans, bulk aggregation, set operations over all nodes. Traversal from a high-degree node, a shared merchant account with 10^6 edges, fans out and can be slower than the join it replaced.
- Document store: cross-aggregate joins, referential integrity, ad-hoc aggregation. Denormalised copies drift; you catch it by reconciling counts against the source of truth.
- SQL with a recursive CTE: fine when depth is bounded and small, or when the graph is a tree.
Before committing to a store, run both formulations on the same edge list and compare result completeness alongside runtime. A faster query that returns fewer rows is exactly the failure mode here.
Curated: · Written: · Reviewed:
Q&A-81A number in last month's deck cannot be reproduced today. What should have been in place?
Where analyses go wrong on making a query result reproducible is usually one step upstream of the statistic being argued about.
A query result is a function of the query text, the data as of a timestamp, and the definitions in force, so reproducibility requires all three to be recorded rather than only the number.
Concretely, store the query alongside the result, pin the read to a snapshot or an as-of timestamp, version the metric definitions, and record the execution time and the code revision with every published figure.
The reason for that specificity is a failure I have seen: A board figure could not be reproduced because a late-arriving backfill changed six weeks of history and the deck recorded only the number, so nobody could tell whether the metric definition or the data had moved.
What travels with a published figure.
| Field | Value |
|---|---|
| Metric | Weekly active accounts |
| Definition version | v4 (2026-07-02) |
| Data as of | 2026-08-31 06:00 UTC |
| Query revision | a1f39c2 |
| Value | 184,402 |
I would not consider it settled without evidence: Re-run the stored query against the recorded snapshot and confirm it returns the published figure, as a routine check rather than an investigation.
A number without its query and its timestamp is an anecdote.
Curated: · Written: · Reviewed:
Q&A-82A colleague's pandas transformation takes 40 minutes on 5 million rows. What do you change first?
First 90 seconds
Profile before rewriting. Time the transform on a 100k-row sample and run cProfile/%prun on it. If the top frame is pandas.core.apply or iterrows, the fix is vectorisation, and on numeric work that is usually a two-orders-of-magnitude win. 40 minutes over 5M rows is 480 µs per row: that is Python-level per-row overhead, not data volume. The vectorised equivalent of the same arithmetic runs in tens of milliseconds on all 5M rows.
Then: replace the row-wise call with column expressions, validate the new column against the old on a sample that includes the tier boundaries, and only then delete the old path.
Mechanism
apply(..., axis=1) builds a Series per row and calls your Python function on it: one object allocation plus one interpreter dispatch per row. iterrows is worse: it constructs a Series per row and coerces dtypes, often to object. Vectorised ops push a single C loop over a contiguous buffer with no per-row Python object.
The change
import numpy as np
import pandas as pd
# Before: one Python call per row.
df["fee"] = df.apply(lambda r: r.amount * (0.03 if r.amount < 100 else 0.02), axis=1)
# After: one compiled pass over the column.
df["fee"] = df["amount"] * np.where(df["amount"] < 100, 0.03, 0.02)
Trace on three rows: amount 40 → 40 × 0.03 = 1.20; amount 100 → 100 × 0.02 = 2.00 (the < keeps 100 in the 0.02 tier in both forms); amount 250 → 250 × 0.02 = 5.00. Identical outputs.
For more than two branches, np.select or pd.cut with explicit bins beats nesting np.where:
rate = pd.cut(df["amount"], bins=[-np.inf, 100, 500, np.inf],
labels=[0.03, 0.02, 0.015], right=False).astype(float)
df["fee"] = df["amount"] * rate
Validate before you trust it:
s = df.sample(100_000, random_state=0)
old = s.apply(lambda r: r.amount * (0.03 if r.amount < 100 else 0.02), axis=1)
new = s["amount"] * np.where(s["amount"] < 100, 0.03, 0.02)
assert np.allclose(old, new)
Include rows where amount == 100 in the sample; boundary semantics are where rewrites break.
If the row function does a lookup
A per-row dict lookup becomes df["key"].map(lookup) or a merge: hash lookups in C instead of Python. A per-row regex becomes df["s"].str.extract(...).
Failure modes
np.whereevaluates both branches. If one branch divides or parses, usenp.selector masked assignment instead.apply(axis=1)silently returns object dtype when the function returns mixed types; downstream arithmetic then drops back to Python speed.- Vectorised ops allocate a full intermediate array per expression. A float64 column at 5M rows is 40 MB, so this is rarely the bottleneck, but chained expressions can spike memory.
- If it is still slow after vectorising, look at
groupby.apply(replace withagg/transform), object-dtype string columns, and merges that multiply row counts.
When not to vectorise
State carried between rows (a running balance, the previous row's value) is not a column expression. Reach for shift, cumsum, expanding, or groupby().cumsum() first; if the dependency is genuinely sequential, a compiled loop (numba, Cython) or chunked processing is the honest answer. Per-row network calls are a batching problem, not a vectorisation one.
Curated: · Written: · Reviewed:
Q&A-83How do you stop a pandas merge from silently changing your row count?
Declare the cardinality you expect, let pandas enforce it, then assert the shape anyway, because validate checks key uniqueness, not that the right rows matched.
before = len(orders)
merged = orders.merge(
customers, on="customer_id", how="left",
validate="many_to_one", # raises MergeError if customers.customer_id repeats
indicator=True,
)
assert len(merged) == before, f"row count {before} -> {len(merged)}"
print(merged["_merge"].value_counts())
validate="many_to_one" raises pandas.errors.MergeError when the right frame's key isn't unique. indicator=True adds a _merge column with left_only / right_only / both, so unmatched keys surface as a left_only count instead of a column of nulls you notice three dashboards later. The default is validate="many_to_many", which checks nothing: writing it explicitly is decoration.
Alternatives, and what each one misses
| Approach | Catches | Misses |
|---|---|---|
validate= on the merge | duplicate keys on the declared side | wrong keys matching, offsetting drop+duplicate |
customers["customer_id"].is_unique pre-check | the same thing, before the merge, with a message you control | the same thing |
merged.groupby(key).size() audit | fan-out magnitude per key | silent row loss |
| Surrogate key / hash of the natural key | collisions from trimming, case, encoding | nothing about cardinality |
Row-count + sum(amount) assert | net row change and value inflation | swaps that preserve both |
Pre-checking uniqueness is worth it when the frame is reused across several merges: you pay the check once and get a readable error naming the offending key. validate is worth it when the merge is one line in a chain and you want the failure at the point of the mistake.
When validate is the wrong tool
- Intended fan-out. A range join or a date-spine explosion is supposed to multiply rows;
validatefires on correct code. Assert the expected multiplier instead. - Multi-key merges.
validatechecks the tuple. A unique(customer_id, version)tuple with a duplicatedcustomer_idpasses cleanly. - Cross joins. No key, nothing to validate.
What it cannot see
- NaN keys match each other. Unlike SQL, pandas joins
NaNtoNaN, so two null-keyed rows fan out with no error and noleft_onlymarker. - String keys that differ by whitespace or case produce
left_onlyrows rather than an exception. Normalise with.str.strip().str.lower()before merging, or you get a silent partial join. - Offsetting changes. An inner join that drops 3 rows and duplicates 3 others keeps
len(merged)constant. Assert onsum(amount)and on the set of keys, not just the count.
A worked example: 10,000 orders merged left onto a customers table carrying historical versions, 6% of customer_id values duplicated. Row count goes 10,000 → 10,600, revenue inflates by the same 6%, and nothing raises. With validate="many_to_one" the merge throws before the number is ever reported.
In the transform itself, assert row count and one measure sum on both sides of every merge, and log _merge.value_counts(). That pair catches fan-out and row loss; validate alone catches only the first.
Curated: · Written: · Reviewed:
Q&A-84Why does assigning to a filtered slice of a DataFrame sometimes not change anything?
The 90-second answer. Chained indexing is two operations, not one. df[df.amount > 1000] runs DataFrame.__getitem__ and builds a new DataFrame; ["amount"] = 1000 then runs __setitem__ on that new object. Whether the write reaches your frame depends on whether the new object shares memory with it, and that follows NumPy's indexing rule, not the syntax. Basic slicing returns a view; fancy indexing (an integer or boolean array) returns a copy. df[mask] is fancy row indexing, so it copies, and the write lands in a temporary that is dropped. df.loc[mask, "amount"] = 1000 is a single __setitem__ on the frame itself, so it always writes through.
That is also why it is sometimes: df["amount"][df["amount"] > 1000] = 0 does write through, because df["amount"] is a basic column slice (a view) and Series.__setitem__ with a boolean key writes in place. Swap the order and the copy appears.
Why the two cases differ. A DataFrame holds its data in blocks: one 2D NumPy array per dtype. df["amount"] hands back a Series wrapping that block's array without copying: a strided view, so in-place mutation is visible in the frame. df[mask] goes through take, which applies np.take to the block's array and allocates a new one. Row selection cannot be a view: the selected rows are not contiguous in the block, so they have to be gathered.
pandas flags the losing pattern with SettingWithCopyWarning. The subsetting __getitem__ records a weak reference to the parent (_is_copy, set by _take_with_is_copy when the index changed), and __setitem__ on that object warns because the write may not reach it. It is a heuristic keyed to how the object was produced, not to whether the data is actually shared, so it also fires on a subset you deliberately kept as a standalone frame.
What Copy-on-Write changed. CoW is opt-in in pandas 2.x (pd.options.mode.copy_on_write = True) and the default in pandas 3.0. Under CoW, indexing returns objects that share data lazily and any write to one copies the data it touches first. Chained assignment becomes a guaranteed no-op on the parent rather than an unpredictable one, and SettingWithCopyWarning is no longer raised: the ambiguity it warned about is gone.
Trace.
import pandas as pd
df = pd.DataFrame({"amount": [500, 1500, 2500]})
df[df["amount"] > 1000]["amount"] = 1000
print(df["amount"].tolist()) # [500, 1500, 2500] — wrote to a copy
df.loc[df["amount"] > 1000, "amount"] = 1000
print(df["amount"].tolist()) # [500, 1000, 1000] — one __setitem__
df["amount"] > 1000 is [False, True, True]. df[mask] gathers rows 1 and 2 into a new frame; ["amount"] = 1000 sets on that frame and it is discarded. The .loc form indexes and assigns in one call on df.
Failure mode and detection. The dangerous version is a cleaning step that clips outliers on a filtered subset: it looks correct in a notebook, where the intermediate object is still alive to inspect, and silently does nothing in a scheduled job, so downstream metrics are computed on unclipped data until someone checks. Detect it by asserting the post-condition, assert df["amount"].max() <= 1000, rather than trusting the statement to have applied. In pandas 2.x, pd.set_option("mode.chained_assignment", "raise") turns the warning into an exception during development.
What to do instead. Use .loc/.iloc for any conditional write. Call .copy() when a subset is meant to be independent. For a whole-column transform, df["amount"] = df["amount"].clip(upper=1000) sidesteps the question entirely.
Curated: · Written: · Reviewed:
Q&A-85A 12 GB CSV will not load on your machine. What do you do short of moving to a cluster?
Clarify, then assume. Two things decide this: how much RAM the machine has, and whether I need the whole file in memory or just an answer out of it. Assume 16 GB RAM, pandas 2.x on Python 3.11, and a 12 GB CSV of roughly 50M rows × 10 columns. The file is 12 GB on disk, but pandas will not use 12 GB: a string column stored as object costs an 8-byte pointer plus a Python string object per value, so a naive read_csv can want 40-60 GB. The answer is not more RAM or a cluster; it is loading less.
Three moves, cheapest first.
- Narrow the read:
usecols+ explicitdtype+chunksize. If you only need an aggregate, you never materialize the frame at all.
import pandas as pd
dtypes = {"user_id": "int64", "country": "category",
"device": "category", "amount": "float32", "ts": "int64"}
usecols = ["user_id", "country", "device", "amount", "ts"]
total = 0.0
for chunk in pd.read_csv("events.csv", usecols=usecols,
dtype=dtypes, chunksize=1_000_000):
total += chunk["amount"].sum()
print(total)
Trace: read_csv with chunksize returns a TextFileReader; each iteration parses 1,000,000 rows into a frame with the declared dtypes, and the previous chunk is dropped. Peak memory is one chunk, 1M × (8 + 2 + 2 + 4 + 8) bytes ≈ 24 MB of column data plus parse overhead, not 12 GB. The loop keeps a single float. Output is one number.
- If you need the frame repeatedly, convert once to Parquet and read only the columns you need. Do the conversion streaming so it does not blow up either:
import pyarrow.csv as pv
import pyarrow.parquet as pq
reader = pv.open_csv("events.csv") # streaming reader
writer = pq.ParquetWriter("events.parquet", reader.schema)
for batch in reader: # bounded by block_size (1 MiB default)
writer.write_table(batch)
writer.close()
table = pq.read_table("events.parquet", columns=["user_id", "amount"])
Parquet is columnar and dictionary-encodes repeated strings, so the 12 GB CSV typically lands at 1.5-2 GB on disk, and reading two of ten columns decodes a few hundred MB.
- If it still does not fit, query the file in place with DuckDB or Polars streaming: SQL over the CSV with a hard memory cap and spill to disk:
import duckdb
duckdb.sql("SET memory_limit='4GB'")
duckdb.sql("SELECT country, avg(amount) FROM 'events.csv' GROUP BY country").show()
Why narrowing works, with numbers. Illustrative for 50M rows:
| Column | Naive dtype | RAM | Narrowed dtype | RAM |
|---|---|---|---|---|
| user_id | object | 2.9 GB | int64 | 0.4 GB |
| country | object | 2.9 GB | category (200 levels) | 0.1 GB |
| device | object | 2.9 GB | category (12 levels) | 0.05 GB |
| amount | float64 | 0.4 GB | float32 | 0.2 GB |
9.1 GB of column data becomes 0.75 GB. category stores the smallest integer code that fits the level count (int16 for 200 levels, int8 for 12) plus one dictionary.
When each move is wrong.
| Move | Peak RAM | Right when | Wrong when |
|---|---|---|---|
| usecols + dtypes + chunksize | one chunk | you need an aggregate or a filtered subset | you need random access, joins, or a groupby over the whole file |
| CSV → Parquet once | one batch | you will query the data more than once, or need a column subset | it is a single one-off pass: you paid the conversion for nothing |
| DuckDB / Polars streaming | bounded by memory_limit | SQL, joins, groupby over the whole file | downstream code needs pandas objects: you pay a conversion at the end |
Failure modes. int64 on a column with blanks raises ValueError: Integer column has NA values; use nullable Int64 or clean first. category on a high-cardinality column is a loss: 5M distinct values means a 5M-entry dictionary plus int32 codes, no smaller than object and slower to group; check nunique() on a sample before declaring it. A groupby inside the chunk loop returns per-chunk results, so you must combine them (sum of sums, weighted means). A Parquet schema inferred from the first batch can mis-type later batches: pass an explicit schema. Detect all of it with df.memory_usage(deep=True) per column and peak RSS from psutil.Process().memory_info().rss or /usr/bin/time -v.
Curated: · Written: · Reviewed:
Q&A-86You need to compute a summary over a file larger than RAM. How do you structure the code?
What I'd say first
Before writing code I'd pin down three things: is the summary a single pass over independent records (sum, count, min/max, distinct count, quantile), does the source arrive as a stream of lines or records, and does the answer have to be exact? If it's "single pass, line-oriented, approximate is acceptable for the heavy parts", the structure is a generator chain feeding bounded accumulators. The source size then never enters the memory budget: only the accumulator does.
Why generators
A generator yields one item and holds only the current one plus its own frame. for line in fh reads a line at a time; a generator expression wrapping it does the same. So a pipeline of generator expressions runs in memory proportional to the largest accumulator, not the file. The trap is that any operation needing the whole sequence (sorted(), list(), set(), a join, a max over a materialized list) reintroduces O(n) state and the constant-memory property is gone.
The code
from datasketches import hll_sketch
def summarise(path):
total, count, uniques = 0.0, 0, hll_sketch(14) # ~0.8% error, fixed 16 KB
with open(path) as fh:
for line in fh:
user, amount = line.rstrip("\n").split(",")
total += float(amount)
count += 1
uniques.update(user)
return total, count, uniques.get_estimate()
total and count are O(1). uniques is an HLL sketch with lg_k=14: 2^14 one-byte registers, so 16 KB regardless of how many distinct users appear, with relative error around 1.04/sqrt(2^14) ≈ 0.8%. That is the whole point: the distinct count is the one summary that would otherwise need a set proportional to cardinality.
Trade-offs
Exact distinct count needs a set or a sort, both O(distinct). If the interviewer insists on exact, you either accept the memory or spill to disk and sort-merge. Approximate structures (HLL for cardinality, t-digest or KLL for quantiles, count-min sketch for heavy hitters) trade a bounded error for bounded memory. Quantiles are the other common trap: an exact median needs all values; KLL gives you it in fixed space.
If the summary needs a join against a second dataset, streaming alone is not enough: you either broadcast the smaller side into a dict (bounded by that side) or sort-merge both.
Failure modes
- An accumulator that grows with input: a
setfor distinct users, a list of all amounts for a median. Detected by watching RSS climb linearly with lines processed. - A generator that silently materializes:
sorted(fh),list(...), or a comprehension that builds a list before aggregating. - Per-line overhead dominating: Python-level per-line work is on the order of a microsecond, so at 400M lines that is minutes of interpreter time before any I/O. Batch or move to a vectorized/compiled reader if throughput matters.
Verification
Run it and sample RSS (e.g. resource.getrusage or /proc/self/status) every N lines; it should be flat, not rising. That is the check that separates a real streaming pipeline from one that only looks like it.
Curated: · Written: · Reviewed:
Q&A-87Your analysis takes eleven parameters passed as a dictionary. Why is that a problem?
A dictionary has no declared shape. Nothing states which of the eleven keys exist, what types they hold, or what the defaults are, so a misspelled key is not an error, it is a silent fallback, and the analysis runs with a value nobody chose. The failure moves from construction time to the point of use, and often it does not fail at all.
Three things follow from that. A typo like significance_level instead of alpha is accepted and ignored. A wrong type, alpha="0.01", is accepted and blows up later inside a comparison, or worse, compares as a string. And the set of parameters an analysis accepts is written down nowhere, so the only way to learn it is to read the body.
Here is the silent fallback, traced:
def run_analysis(cfg: dict) -> dict:
alpha = cfg.get("alpha", 0.05)
return {"alpha": alpha}
run_analysis({"metric": "revenue_per_session", "significance_level": 0.01})
# -> {'alpha': 0.05}
The caller asked for 0.01. The function returned 0.05 and reported no problem. Every downstream readout is now computed at the wrong threshold, and nothing in the run's output records that a parameter was dropped.
Declare the configuration instead. Types, defaults and validation live in one place, and the instance serialises into the run's output so the parameters that produced a result travel with it:
from dataclasses import dataclass, asdict
@dataclass(frozen=True, slots=True) # Python 3.10+
class ExperimentConfig:
metric: str
alpha: float = 0.05
power: float = 0.80
winsorise_at: float | None = 0.99
def __post_init__(self):
if not 0 < self.alpha < 0.5:
raise ValueError(f"alpha out of range: {self.alpha}")
ExperimentConfig(metric="revenue_per_session", significance_level=0.01)
# TypeError: __init__() got an unexpected keyword argument 'significance_level'
ExperimentConfig(metric="revenue_per_session", alpha=0.9)
# ValueError: alpha out of range: 0.9
cfg = ExperimentConfig(metric="revenue_per_session", alpha=0.01)
result["config"] = asdict(cfg) # {'metric': ..., 'alpha': 0.01, 'power': 0.8, 'winsorise_at': 0.99}
The typo now fails at construction, before any data is touched. frozen=True stops a downstream step from mutating a shared config mid-run; slots=True keeps the object small and makes accidental attribute creation an error.
A dict is still the right call in three places. When you are forwarding to a backend that itself takes **kwargs (a plotting library, a model constructor) a dict is the natural currency and wrapping it buys nothing. When you are exploring in a notebook and the parameters change every cell, a dataclass is ceremony. And when config arrives from YAML or JSON, it is a dict at the boundary; the fix is to parse it into the typed object immediately and never let the raw dict past that line. The rule is that the dict is a transport format, not the interface.
Alternatives worth naming: TypedDict gives static checking but no runtime validation and no defaults, so a typo still slips through at runtime. Pydantic adds coercion and rich validation at the cost of a dependency and slower construction. Plain keyword-only arguments (def run_analysis(*, metric, alpha=0.05, ...)) are the lightest option and give you the same construction-time errors, but they do not give you a single object to serialise into the run record.
To catch regressions, add a round-trip test: build a config, serialise it into a result, reconstruct the config from that output, and assert the two are equal. It fails the moment a parameter is added to the dataclass but not captured in the output. Pair it with a test that constructs the config with a deliberately misspelled key and asserts TypeError: that is the behaviour you are buying.
Curated: · Written: · Reviewed:
Q&A-88How do you make sure every analysis run records its inputs and cleans up after itself?
The 90-second answer. Wrap the run in a context manager. Entry records the inputs; exit writes the manifest and releases the scratch resources. Both happen on every exit path (success, exception, KeyboardInterrupt) because they live in finally, not on the last line of the script. The part people get wrong is assuming finally is enough: it does not run when the process is killed, so cleanup also has to be idempotent and keyed by a run id that a sweeper can find later.
import json, os, subprocess, tempfile, time, uuid
from contextlib import contextmanager
@contextmanager
def analysis_run(name, config, snapshot_ts, warehouse):
run_id = f"{name}-{uuid.uuid4().hex[:8]}"
started = time.time()
rev = subprocess.check_output(["git", "rev-parse", "HEAD"], text=True).strip()
scratch = f"scratch_{run_id}"
warehouse.execute(f"CREATE SCHEMA {scratch}")
manifest = {"run_id": run_id, "name": name, "rev": rev, "config": config,
"as_of": snapshot_ts, "scratch": scratch}
try:
yield manifest
manifest["status"] = "ok"
except BaseException as exc:
manifest["status"] = f"failed: {exc!r}"
raise
finally:
manifest["seconds"] = round(time.time() - started, 1)
try:
warehouse.execute(f"DROP SCHEMA IF EXISTS {scratch} CASCADE")
except Exception as cleanup_exc:
manifest["cleanup_error"] = repr(cleanup_exc)
_write_manifest(manifest)
def _write_manifest(manifest):
os.makedirs("runs", exist_ok=True)
fd, tmp = tempfile.mkstemp(dir="runs")
with os.fdopen(fd, "w") as f:
json.dump(manifest, f)
os.replace(tmp, f"runs/{manifest['run_id']}.json")
Trace it. Success: CREATE SCHEMA scratch_orders-9f2c1a04, yield hands the manifest to the body, body returns, status = "ok", finally drops the schema and os.replace publishes runs/orders-9f2c1a04.json. Exception in the body: status = "failed: ValueError('empty partition')", raise re-raises, finally still drops the schema and writes the manifest, then the caller sees the original ValueError. Cleanup failure: the DROP raises, cleanup_error is recorded, the manifest is still written, and the original exception still propagates. Cleanup never masks the real error.
Record inputs as content, not pointers. A manifest that says config: "configs/orders.yaml" is worthless once that file is edited. Hash the config, store the snapshot watermark or partition bound, and record row counts per input table. Then a run is reproducible from the manifest alone.
Failure modes.
- SIGKILL, OOM kill, spot-instance preemption.
finallynever runs. Fix: a sweeper that dropsscratch_*schemas older than twice the p99 run duration, plus a reconciliation query joining live scratch schemas against manifests. Alert on any scratch schema with no manifest. - Manifest write fails (disk full, read-only mount). The run's inputs are gone. The temp-file-plus-
os.replaceabove means a reader never sees a half-written manifest; alert on runs started with no manifest row. - Shared scratch names. Two parallel runs of the same job collide. Keying by
run_idfixes it; a barescratch_ordersdoes not. - Resources you keep warm. A long-lived cluster or connection pool should not be torn down per run. The context manager is for per-run resources only.
Alternatives. try/finally is the same contract with more boilerplate. Orchestrator-level hooks (Airflow's on_success_callback/on_failure_callback, Dagster run-level hooks) survive process death better because the scheduler owns the state, at the cost of a round trip to the scheduler. For a single-process job, the context manager is enough.
Detection. Alert on scratch schemas older than 2× p99 run duration, and on days where manifests written < runs started. Illustrative arithmetic: 100 failed runs a quarter each leaving a 40 GB scratch schema is 4 TB of orphaned tables by year-end. Invisible until someone queries the catalog.
Test it. Force an exception inside the block and assert the manifest exists with status starting failed: and the scratch schema is gone. That test is the only thing that proves the cleanup path runs.
Curated: · Written: · Reviewed:
Q&A-89You need to pull 8,000 API pages. Threads, processes, or async, and why?
Async, with a bounded connection pool, unless you're stuck on a blocking client, in which case a thread pool. Processes are wrong here: the work is waiting on sockets, not burning CPU.
Why. CPython releases the GIL around socket reads and writes, so threads do overlap network waits. Async gets the same overlap on one thread and pays roughly 1 KB per in-flight request instead of a thread stack (8 MB reserved by default on Linux, ulimit -s). At 8,000 pages that difference is the whole reason to prefer async: 64 threads reserve ~512 MB of address space; 64 async tasks are noise.
Arithmetic. Say p50 is 120 ms per page and p99 is 900 ms (hypothetical, but typical for a JSON API over TLS):
- Serial: 8,000 × 0.12 s = 960 s ≈ 16 min.
- 64 concurrent, perfect overlap: 8,000 / 64 = 125 waves × 0.12 s ≈ 15 s.
- Realistic: tail latency and 429s push it to 45–90 s. If you see 16 min you're serial; if you see 15 s you're being rate-limited and don't know it.
Code.
import asyncio, httpx
async def fetch_all(urls, concurrency=64):
limits = httpx.Limits(max_connections=concurrency,
max_keepalive_connections=concurrency)
sem = asyncio.Semaphore(concurrency)
async with httpx.AsyncClient(limits=limits, timeout=10.0) as client:
async def one(u):
async with sem:
r = await client.get(u)
r.raise_for_status()
return r.text
return await asyncio.gather(*(one(u) for u in urls))
Trace: gather schedules 8,000 coroutines and returns their results in input order. The semaphore admits 64 at a time; each admitted coroutine takes a connection from the pool, awaits the response, releases both. The other 7,936 sit suspended on the semaphore, costing a coroutine object each. max_connections=64 and the semaphore are deliberately equal so the pool never queues behind the semaphore.
Failure modes.
- 429 / 403 from rate limits. Detect by counting non-2xx by status; back off with jitter, don't just retry.
- Pool exhaustion: if
max_connections< concurrency, requests queue inside httpx and your "64 concurrent" is a lie. Keep them equal. - Retry storms: 8,000 × 3 retries on a 5xx is 24,000 requests. Cap total attempts and add a circuit breaker.
- Head-of-line: any CPU work (parsing, regex) inside
one()blocks the event loop for all 64. Push parsing to a process pool.
When not to. If you're on requests and can't add a dependency, ThreadPoolExecutor(max_workers=64) with a shared Session is fine: same overlap, more memory. Use processes only for the CPU-bound parse step, and pass bytes, not parsed objects, across the boundary.
Curated: · Written: · Reviewed:
Q&A-90Your revenue total is off by $0.03 from finance. Where does that come from?
The useful framing for floating point in financial aggregates is what decision changes if the estimate moves by its own uncertainty.
Binary floating point cannot represent most decimal fractions exactly, so summing many values accumulates representation error, and the size of the error depends on the order of summation.
Concretely, store money as integer minor units or as a decimal type, perform the aggregation in that type, and round once at presentation with an explicitly stated rounding rule rather than at each intermediate step.
The reason for that specificity is a failure I have seen: A daily reconciliation drifted by a cent or two per invoice and by $91 over a year, because line items were rounded to cents individually in one pipeline and once at the total in the other, and two teams spent a week comparing queries before anyone compared the rounding step.
Three defensible-looking implementations, three different totals.
from decimal import Decimal, ROUND_HALF_UP
rows = ["19.995", "2.005", "8.615"] # line totals before rounding to cents
print(sum(round(float(r), 2) for r in rows)) # 30.619999999999997, and 2.005 rounds down
print(round(sum(float(r) for r in rows), 2)) # 30.62
cents = Decimal("0.01")
print(sum(Decimal(r).quantize(cents, ROUND_HALF_UP) for r in rows)) # 30.63
print(sum(map(Decimal, rows)).quantize(cents, ROUND_HALF_UP)) # 30.62
I would not consider it settled without evidence: Recompute the aggregate in a decimal or integer type and compare against the floating-point total; a difference at the cent scale identifies the cause.
Money is a decimal quantity, and binary floats are not.
Curated: · Written: · Reviewed:
Q&A-92You need distinct counts and duplicate detection over 500 million records. What structures do you reach for?
The 60-second answer. Exact distinct counting needs state proportional to the number of distinct keys, there is no way around that, so at 500M keys the real question is whether the decision can tolerate a bounded error. If it can, use HyperLogLog for cardinality and a Bloom filter for membership. If it cannot, you pay for the set and shard it.
Sizing the exact option. A Python set of 500M ints: each int object is 28 bytes (32 above 2^30), plus roughly 8 bytes per slot in a table sized to about 2^30 slots at a 2/3 load factor. That lands near 48 GB measured, which is why the nightly job died.
HyperLogLog. Hash each key to 64 bits, split into p bits of register index and the rest; each register stores the maximum count of leading zeros seen. Cardinality is the harmonic mean of 2^register, bias-corrected. Standard error is 1.04/√m where m = 2^p registers.
- p=14 → m=16384 registers, 6 bits each = 98304 bits = 12 KB, error 1.04/128 = 0.81%.
- p=12 → m=4096, 3 KB, error 1.04/64 = 1.63%.
Redis's HLL is p=14: 12 KB per key, 0.81% standard error. The same arithmetic.
Bloom filter. m = -n ln p / (ln 2)^2 bits, k = (m/n) ln 2 hashes. For n=500M and p=0.01: m ≈ 4.79e9 bits ≈ 600 MB, k=7. No false negatives; 1% false positives.
Cost against error for the same 500M identifiers.
| Structure | Memory | Answer type | Error |
|---|---|---|---|
| Python set | ~48 GB | Exact distinct | 0 |
| HyperLogLog, p = 14 | 12 KB | Approximate distinct | ~0.81% |
| Bloom filter, 1% target | ~600 MB | Membership | 1% false positive |
Failure modes. HLL is mergeable, union is register-wise max, so you can shard and combine, but it cannot answer "is X present". Bloom cannot delete (use a counting Bloom or cuckoo filter if you need removal) and saturates as you approach n: past the design n, the false positive rate climbs steeply. Both are sensitive to hash quality; a weak hash on sequential IDs inflates error.
Validation. Compare the sketch against an exact count on a day the exact count is affordable, and confirm the error is inside the stated bound.
When not to. If the decision can see the difference: billing on exact unique users, or dedup where a false positive drops a real record: pay for exactness: shard the set, or sort and merge.
Curated: · Written: · Reviewed:
Q&A-93You need to match each transaction to the nearest preceding price quote across two large sorted files. How do you avoid a nested loop?
Core answer (first 90 seconds)
Assumptions I'd state up front: both files are sorted ascending on the same time key, timestamps are comparable (same clock, same units), and "preceding" includes a quote stamped exactly at the trade time. Given that, the answer is one merge pass with two indices (O(n + m) time, O(1) extra memory) because the match position is monotonic: as trades advance in time, the last quote at or before them can only move forward, so the quote pointer never rewinds.
def asof_match(trades, quotes):
"""Both sorted ascending by time. Returns the last quote at or before each trade."""
out, j = [], 0
for t in trades:
while j + 1 < len(quotes) and quotes[j + 1].time <= t.time:
j += 1
out.append((t, quotes[j] if quotes and quotes[j].time <= t.time else None))
return out
Trace it on trades at times [10, 25, 40] and quotes at [5, 20, 30, 35]:
t=10:quotes[1].time=20 <= 10? No.j=0.quotes[0].time=5 <= 10→ match(10, q5).t=25:quotes[1]=20 <= 25→j=1;quotes[2]=30 <= 25? No. Match(25, q20).t=40:quotes[2]=30 <= 40→j=2;quotes[3]=35 <= 40→j=3;j+1=4is not< 4, stop. Match(40, q35).
Six pointer steps total (3 trade + 3 quote) versus 12 comparisons for the nested loop on this sample.
Why the gap is large at scale
4M trades against 9M quotes is up to 3.6e10 comparisons nested; the merge pass is 13M steps. In Python at roughly 1e7–1e8 interpreted steps/sec that is minutes versus hours; the ratio holds in compiled languages too. The nested loop's cost is the product; the merge's is the sum.
Alternatives and when the two-pointer is wrong
- Database as-of join (
pandas.merge_asof, DuckDBASOF JOIN, kdb+aj): same algorithm underneath, but you pay load and sort. Use it when the data already lives in the store or the query is ad hoc. - Hash join: equality only. For as-of you'd bucket by time window and hash inside the bucket: approximate, and it needs a window bound you may not have.
- Sort-merge: if the inputs are unsorted, the O(n log n) sort dominates and the merge is just the cheap half.
- Files that don't fit in memory: the two-pointer is the right tool here. It is streaming-friendly. Read both files with buffered iterators, keep one quote of lookahead, memory stays O(1). This is exactly where loading into a database loses.
- When not to use it: data already indexed in a DB and you need one-off lookups; you need the nearest quote in both directions or all quotes in a window (a different join); or you need parallelism. The pass is inherently sequential, so range-partition both files by time and run independent passes per partition if throughput matters.
Failure modes to detect
- Ties: several quotes at the same timestamp. Take the last, and say so.
- No preceding quote: emit
Noneexplicitly; silently dropping the row hides a boundary bug. - Clock skew or unit mismatch between files: matches look plausible but are wrong. Check min/max timestamp overlap and units before running.
- Unsorted input: the pointer stalls and every trade matches the first quote. Assert monotonicity in a cheap pre-pass, or check it during the merge.
Curated: · Written: · Reviewed:
Q&A-94You need the smallest score cutoff that keeps daily alerts under 300. How do you find it?
Answer
Assume the alert fires when a candidate's score is at or above the cutoff, and that the alert count is monotone in the cutoff: raising the cutoff never increases the count. Under those assumptions the smallest cutoff that keeps the day at or under 300 alerts is exactly the 300th-largest score of the day. Score every candidate once, select the 300th largest, use that value. No search is needed.
Worked example. Eight candidates scored [0.91, 0.88, 0.72, 0.65, 0.60, 0.44, 0.31, 0.12], budget 3. Sorted descending, the 3rd value is 0.72. A cutoff of 0.72 fires 0.91, 0.88, 0.72: three alerts. Raise the cutoff just above 0.72 and only two fire. So 0.72 is the smallest cutoff with at most three alerts.
def cutoff_for_budget(scores, budget):
if budget <= 0:
return float("inf") # nothing may alert
if budget >= len(scores):
return float("-inf") # everything alerts
ordered = sorted(scores, reverse=True)
return ordered[budget - 1]
Selection is O(n) with quickselect, O(n log n) with a full sort; either way it is one scoring pass.
Binary search over the cutoff is the alternative, and it earns its place only in a narrower case: you cannot materialize the scores, the only thing you can run is a black-box count. "how many candidates score at or above t?", and each count is expensive. Bracket t between a low value known to keep the count under budget and a high value known to exceed it, and halve until the interval is smaller than your tolerance. For a [0,1] range and tolerance 1e-3 that is about ten count queries. But each count still evaluates every candidate, so ten queries cost ten scoring passes against the direct method's one. Binary search wins only when a count is cheaper than a full pass (an indexed range count on a pre-scored column, or a histogram bucket) and there you can usually read the 300th value directly anyway.
Failure modes:
- Non-monotone scoring. If the cutoff also gates a suppression rule, the count is not monotone and binary search returns an arbitrary point. Verify monotonicity on a sample first.
- Ties at the boundary. If several candidates share the 300th score, that cutoff fires more than 300 alerts. Set the cutoff just above the tied value, or define the tie-break explicitly.
- Precision. A float cutoff near the boundary can flip the count by one; pick the tolerance from the score's meaningful resolution, not float epsilon.
- Streaming. If candidates arrive over the day and you cannot rescore, keep a running top-300 min-heap and read the cutoff from its minimum at the end: still one pass, no search.
Key points a strong answer hits
- Names the assumption that makes the problem trivial: the count is monotone in the cutoff.
- Gives the direct answer, 300th-largest score, rather than a search.
- States the cost: one scoring pass, O(n) selection.
- Handles ties, since they are what actually breaks the "at most 300" guarantee.
- Knows when binary search is the right tool and why it usually is not.
What interviewers look for
Whether you spot that a monotone threshold problem is a selection problem, and whether you ask about ties and monotonicity before coding. They also listen for whether you separate "the count is a black box" from "the count is a column scan": the second case needs no search.
Weak-answer patterns
- Jumping to binary search on the cutoff without checking that the count is monotone.
- Sorting and returning the 300th value without saying what happens when scores tie.
- Treating the cutoff as a probability or a percentile rather than a score value.
- Ignoring that each count query in a search re-scores every candidate.
Follow-up questions
- What if the budget is per-hour rather than per-day?
- How do you keep the cutoff stable day to day so it does not oscillate?
- What changes if scores are recomputed by a model that ships weekly?
- How do you backfill the cutoff for a day whose scores arrive late?
Curated: · Written: · Reviewed:
Q&A-95You need the top 100 accounts by spend from a stream you can only read once. What do you use?
Use a bounded min-heap of size k=100. One pass over the stream, 100 slots of memory, O(n log k) time.
Mechanism
The heap holds the best 100 seen so far, and its root is the smallest of those 100, so the root doubles as the admission threshold. Fill the heap with the first 100 items, then for each later item compare its spend against heap[0][0] and replace only when it is larger. Anything that loses that comparison is discarded in O(1); anything that wins costs O(log k). At the end, sort the 100 survivors once for presentation.
import heapq
def top_k(stream, k=100):
heap = []
for account, spend in stream:
if len(heap) < k:
heapq.heappush(heap, (spend, account))
elif spend > heap[0][0]:
heapq.heapreplace(heap, (spend, account))
return sorted(heap, reverse=True)
heapreplace pops the root and pushes the new item in one sift-down, which is cheaper than heappop followed by heappush.
Trace on [("a",10), ("b",30), ("c",20), ("d",40)], k=2:
| item | heap after | why |
|---|---|---|
| a,10 | [(10,a)] | len < k, push |
| b,30 | [(10,a),(30,b)] | len < k, push |
| c,20 | [(20,c),(30,b)] | 20 > root 10, replace |
| d,40 | [(30,b),(40,d)] | 40 > root 20, replace |
sorted(..., reverse=True) returns [(40,d),(30,b)]: the correct top 2.
Alternatives and when they win
- Full sort. O(n log n) and holds all n rows resident. For 300M rows that is the version that spills to disk; a top-100 report built this way took 40 minutes, while the heap version used 100 slots and finished in roughly the time to read the input.
- Quickselect. O(n) average, but it needs the whole array in memory and is not streaming.
- Approximate heavy hitters (count-min sketch, Space-Saving). Use when k is large or memory is tight; they bound error rather than returning exact ranks.
- Distributed. Each shard keeps its own top 100, then merge the shard heaps. Exact, because the global top 100 is a subset of the union of per-shard top 100s.
Failure modes
- The stream is updates, not final totals. If events are spend increments per account, a heap over raw events ranks events, not accounts. Aggregate account→total in a hash map first, then top-k; memory becomes O(distinct accounts).
- k approaches n. Memory is O(k); once k is a large fraction of the stream, a sort is simpler and not much slower.
- Ties at the boundary. Equal spends are kept in arbitrary order. Add account id as a secondary key in the tuple if the report must be deterministic.
- Ascending stream. Every item replaces, giving the O(n log k) worst case; a descending stream rejects almost everything in O(1) per item.
Checking it
Run the heap and a full sort over a sample of the same stream and diff the two sets. Log heap[0][0] as the running threshold, if it stops moving, the stream has gone cold or the comparison key is wrong.
Curated: · Written: · Reviewed:
Q&A-96You need a 7-day rolling active-user count updated hourly over years of history. What is the efficient shape?
The core answer
A distinct count is not subtractable, so you cannot slide the window by adding the entering hour and subtracting the leaving one. The efficient shape is to pre-aggregate each hour into a mergeable distinct-user structure, then compute the 7-day window as the union of the 168 hourly buckets that fall inside it.
Assumptions worth stating up front: hourly granularity is acceptable (the window boundary lands on an hour), users carry a stable id, and "active" means the user produced at least one event in that hour.
Why the naive slide fails
For a sum or a count, total += entering; total -= leaving is O(1) per step. For a distinct count there is no inverse: you cannot "un-see" a user who appeared in the leaving hour, because they may also appear in a still-in-window hour. Recomputing the distinct count from raw events at every hourly step is what makes the job slow: it re-scans 168 hours of raw events per output row.
The shape
Two storage choices for the per-hour bucket:
| Exact set per hour | HyperLogLog per hour | |
|---|---|---|
| Stored value | set of user ids | 16384-register sketch (p=14) |
| Merge | set union | register-wise max |
| Size at 1M DAU | ~8 MB/hour | ~12 KB/hour |
| One year of buckets | ~70 GB | ~105 MB |
| Error | 0 | ~0.81% (1.04/√16384) |
Union is associative and commutative, so the 7-day window is just the merge of 168 buckets. With HLL that is 168 × 16384 ≈ 2.75M register operations: a few milliseconds, versus re-scanning raw events.
# hourly_buckets: hour_index -> set of user ids (exact variant)
def rolling_active(hourly_buckets, hours, window=168):
out = {}
for end in range(window - 1, hours):
users = set()
for h in range(end - window + 1, end + 1):
users |= hourly_buckets.get(h, set())
out[end] = len(users)
return out
With HLL, replace users |= bucket with a register-wise max into a scratch sketch, then read its cardinality. Because the merge is cheap, you do not need incremental maintenance at all: recomputing each window from 168 buckets is the fast path. If 168 merges ever mattered, a segment tree over hourly sketches makes each window O(log n) merges.
Trade-offs
- Exact vs HLL. Use exact sets for the recent window if you need billing-grade numbers; HLL for the multi-year history where 0.81% is acceptable. You can keep both: exact for the last 7 days, HLL for everything older.
- Recompute vs pre-aggregate. Pre-aggregation is what makes years of history tractable; the cost is one row per hour per segment and a one-time backfill.
- Storage vs accuracy. Raising p to 16 cuts error to ~0.41% at 4× the sketch size (~48 KB/hour, ~420 MB/year).
Failure modes
- Sketch error. 0.81% at p=14 means a 1M count can read ±8k. Detect by periodically comparing the HLL estimate against an exact recount on a sampled hour.
- Late-arriving events. An hour's bucket is wrong until late events land. Keep raw events for a retention window (e.g. 7 days), recompute affected hourly sketches, and upsert. Detect with a per-bucket watermark (max event time seen) and alert when events arrive past it.
- Backfill over years. Rebuilding old buckets requires the raw events; once they are dropped, those buckets are frozen and cannot be corrected. Decide the retention window before you need it.
- Time zone. Fix the hour boundary in UTC, or day-boundary drift silently shifts every window.
Validate by comparing the bucketed union against a full recomputation on a bounded slice before trusting it over the full history.
Curated: · Written: · Reviewed:
Q&A-97You have overlapping subscription periods per customer and need total covered days. How do you compute it?
State the assumption first: "covered days" means distinct calendar days on which the customer had at least one active subscription, counted once. Then pin the boundary convention, because it decides the comparison. Use half-open intervals [start, end): a subscription ending 2024-03-31 and the next starting 2024-03-31 are contiguous, not overlapping, and no day is counted twice. With inclusive end dates the merge test becomes start <= end + 1 day and every span is one day too long: a customer on 12 monthly plans silently gains 12 days.
Then sort by start and sweep: extend the current span while the next start is at or before the current end, emit it otherwise. O(n log n) for the sort, O(n) for the sweep, O(k) memory for k merged spans. If the input already arrives sorted by (customer_id, start), the sweep is O(n) with O(1) extra state per customer.
from datetime import date
def covered_days(periods):
"""periods: (start, end) date pairs, half-open [start, end)."""
merged = []
for start, end in sorted(periods):
if merged and start <= merged[-1][1]:
last_start, last_end = merged[-1]
if end > last_end:
merged[-1] = (last_start, end)
else:
merged.append((start, end))
return sum((end - start).days for start, end in merged)
Trace on four rows for one customer:
| # | (start, end) | merged after |
|---|---|---|
| 1 | (2024-01-01, 2024-02-01) | [(01-01, 02-01)] |
| 2 | (2024-01-15, 2024-03-01) | [(01-01, 03-01)] |
| 3 | (2024-03-01, 2024-03-10) | [(01-01, 03-10)] |
| 4 | (2024-04-01, 2024-04-11) | [(01-01, 03-10), (04-01, 04-11)] |
Row 2 starts before the open span's end, so it extends it; row 3 starts exactly at the end, which the half-open convention treats as contiguous; row 4 leaves a gap and opens a new span. Result: 69 + 10 = 79 days. Summing the raw rows gives 31 + 46 + 9 + 10 = 96: 17 days, 21.5%, of double count.
Where to do the merge. If the rows live in Postgres, do it there and ship one number per customer instead of tens of millions of rows. A gaps-and-islands window query is one sort plus two passes:
WITH ordered AS (
SELECT customer_id, start_date, end_date,
max(end_date) OVER (PARTITION BY customer_id
ORDER BY start_date, end_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
FROM subscriptions
), marked AS (
SELECT *, CASE WHEN prev_max_end IS NULL OR start_date > prev_max_end
THEN 1 ELSE 0 END AS new_island
FROM ordered
), islands AS (
SELECT *, sum(new_island) OVER (PARTITION BY customer_id
ORDER BY start_date, end_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island_id
FROM marked
)
SELECT customer_id, sum(max_end - min_start) AS covered_days
FROM (SELECT customer_id, island_id, min(start_date) AS min_start, max(end_date) AS max_end
FROM islands GROUP BY customer_id, island_id) s
GROUP BY customer_id;
ROWS rather than the default RANGE frame matters: with duplicate start dates, RANGE pulls tied rows into the frame and the island boundary moves. On PostgreSQL 14+, range_agg(daterange(start_date, end_date, '[)')) folds the same rows into a merged multirange in one aggregate, and sum(upper(r) - lower(r)) over unnest gives the days.
Application code wins when the spans feed further logic (grace periods, plan-change proration, per-customer rules) or when the data is already in memory. A third option is a day bitmap per customer: OR the bits, popcount the result. A 3-year window is 1096 bits ≈ 137 bytes, so 10M customers is ~1.4 GB: fine in a columnar store, not in a per-request dict. It answers "was day d covered" in O(1) and makes the touching-interval question disappear, but it cannot represent open-ended ranges or spans longer than the window.
Failure modes. datetime subtraction across a DST boundary yields 23 or 25 hours and .days truncates to 0: convert to date in a fixed zone (UTC or the billing zone) before counting. A NULL end_date for an active subscription must be mapped to the report date explicitly, or the row is dropped. In SQL, max(end_date) over the wrong frame with duplicate starts misplaces island boundaries. Check the result by asserting merged spans are disjoint and strictly increasing, and by reconciling a sample of customers against count(distinct day) from a day-grain calendar join.
Curated: · Written: · Reviewed:
Q&A-98You have pairwise matches between records and need to group them into entities. What is the algorithm, and where does it go wrong?
The short answer. Grouping pairwise matches into entities is connected components on the match graph: nodes are records, an edge means "these two are the same entity," and each component is one entity. Union-find does it in near-linear time. The algorithm is trivial; the failure mode is that it is monotone (edges only ever merge components, never split them) so one false positive edge silently welds two real entities together and no later evidence can undo it.
Assumptions worth stating up front: matches are pairwise and already thresholded to a binary decision; you have no reliable negative evidence ("these two are definitely different"); and a false merge costs more than a false split.
Mechanism. Union-find with path compression, then bucket nodes by root:
# Python 3.11
def components(edges, cap=5_000):
parent = {}
def find(x):
parent.setdefault(x, x)
while parent[x] != x:
parent[x] = parent[parent[x]] # path halving
x = parent[x]
return x
for a, b in edges:
ra, rb = find(a), find(b)
if ra != rb:
parent[ra] = rb
groups = {}
for node in parent:
groups.setdefault(find(node), []).append(node)
return groups, [g for g in groups.values() if len(g) > cap]
components([("a","b"),("b","c"),("x","y")]) returns {"c": ["a","b","c"], "y": ["x","y"]} and an empty oversize list. The second return value is the review queue: components past cap are surfaced, not silently published.
Where it goes wrong. Transitivity turns one bad edge into one bad entity of unbounded size. A component of n records needs at least n−1 edges to hold together, so if edge precision is p, the chance a component is clean is roughly p^(n−1). At p = 0.99 that is 0.99^99 ≈ 0.37 for a 100-record component: about 63% of them contain at least one bad edge. Precision that looks fine per pair is not fine per entity. A single spurious edge through a shared corporate email domain merged 340,000 records into one entity, and the customer table listed one account with $40M in spend.
Alternatives, and when plain transitive closure is the wrong choice.
| Approach | What it optimizes | Cost | Reach for it when |
|---|---|---|---|
| Transitive closure (union-find) | Consistency: every accepted match is honored | O(E·α(N)) | Edge precision is very high (>0.999) and components stay small |
| Correlation clustering | Minimize disagreements: cut edges + kept non-edges | NP-hard; Pivot/C4 give a 3-approximation | You have negative signals too, and false merges are the expensive error |
| Hierarchical agglomerative with a size cap | Merge by similarity, stop before a component exceeds a bound | O(N²) naive; blocking makes it tractable | Entity size is bounded by domain knowledge (one SSN, one EIN) |
| High-precision edges only (mutual-best, k-vote) | Precision, at the cost of recall | Cheap | A merge is costly and you can tolerate splits |
Plain transitive closure is the wrong tool when edge precision is below ~0.99 and components can grow past a few dozen nodes, when entity size is bounded by the domain, or when you cannot review the largest components before publishing. In those cases correlation clustering or a size-capped agglomerative pass is the honest choice.
Detection. Report the component size distribution after every run and inspect the largest components before publishing the grouping. A heavy tail, one component orders of magnitude larger than the rest, is the signature of a spurious bridge edge, not of a genuinely huge entity.
Curated: · Written: · Reviewed:
Q&A-99You need the minimum number of edits to reconcile two versions of a customer name list. How do you approach it?
Clarify first: "minimum number of edits" usually means the edit script (which characters to insert, delete or substitute) not just the count. If you only need the count, say so, because that changes the memory you need. I'll assume character-level Levenshtein with unit costs, and that the two lists are aligned by a key (customer id) so you're comparing one name pair at a time, not every pair against every other.
Core answer: this is edit distance with backtracking. Fill the DP table of prefix costs, then walk from (m, n) back to (0, 0), emitting the operation each step implies.
def reconcile(a: str, b: str) -> list[tuple[str, str, str]]:
m, n = len(a), len(b)
dp = [[0] * (n + 1) for _ in range(m + 1)]
for i in range(m + 1):
dp[i][0] = i
for j in range(n + 1):
dp[0][j] = j
for i in range(1, m + 1):
for j in range(1, n + 1):
cost = 0 if a[i - 1] == b[j - 1] else 1
dp[i][j] = min(dp[i - 1][j] + 1, dp[i][j - 1] + 1, dp[i - 1][j - 1] + cost)
ops, i, j = [], m, n
while i > 0 or j > 0:
if i > 0 and j > 0 and a[i - 1] == b[j - 1] and dp[i][j] == dp[i - 1][j - 1]:
i, j = i - 1, j - 1
elif i > 0 and dp[i][j] == dp[i - 1][j] + 1:
ops.append(("delete", a[i - 1], "")); i -= 1
elif j > 0 and dp[i][j] == dp[i][j - 1] + 1:
ops.append(("insert", "", b[j - 1])); j -= 1
else:
ops.append(("substitute", a[i - 1], b[j - 1])); i, j = i - 1, j - 1
return ops[::-1]
Trace on a = "Jon", b = "John". The table (rows are prefixes of a, columns of b):
| "" | J | Jo | Joh | John | |
|---|---|---|---|---|---|
| "" | 0 | 1 | 2 | 3 | 4 |
| J | 1 | 0 | 1 | 2 | 3 |
| Jo | 2 | 1 | 0 | 1 | 2 |
| Jon | 3 | 2 | 1 | 1 | 1 |
Distance is 1. Backtrack from (3,4): a[2]='n' == b[3]='n' and dp[3][4]==dp[2][3], so step diagonally to (2,3). There a[1]='o' != b[2]='h'; dp[2][3]=1 equals dp[2][2]+1=1, so emit insert 'h' and move to (2,2). Then 'o'=='o' and 'J'=='J' step diagonally to (0,0). Reversed, the script is [("insert", "", "h")]: one edit, which is the minimum.
Alternatives and when not to use this. difflib.SequenceMatcher gives opcodes in one call and is what you reach for when you want readable diffs, but it implements Ratcliff-Obershelp, not minimal edit distance, so its script can be longer than optimal. Banded DP, only filling cells within k of the diagonal, is O(k·min(m,n)) and is the right call when you expect names to differ by a couple of characters and the strings are long. For matching records rather than characters, character DP is the wrong tool: normalize (casefold, strip punctuation, NFC) and compare token sets or Jaro-Winkler, because "Jon Smith" vs "Smith, Jon" is a reordering, not a 10-edit string.
Failure modes. The full table is O(mn) memory; the two-row variant halves that but cannot reconstruct the script, so keep the full table when you need the edits. Ties in the backtracking produce different but equally minimal scripts: pin the tie-break order if you diff outputs across runs. Unicode matters: "é" as e + combining accent scores 2 against a precomposed "é", so normalize to NFC before comparing. And don't run this pairwise across two lists of N names: that's N² alignments; block on a canonical key first.
Verification: check the tabulated script against a brute-force search over all edit sequences on strings of length ≤ 6, then confirm the script applied to a reproduces b.
Curated: · Written: · Reviewed:
Q&A-100Your notebook pulls 80 million rows to compute a group mean. What is wrong with that, and where should the line be?
The short answer
The pull is the wrong shape. You are moving 80 million rows to produce 40 numbers. A group mean is a sufficient statistic: SUM(x) and COUNT(x) per group are all you need, and that is two numbers per group. The warehouse computes them by scanning only the region and amount columns and doing partial aggregation per worker; the 80M rows never cross the network. The line is: push down anything that reduces cardinality or is expressible as a sufficient statistic, and pull only what the client can compute and what fits in memory.
Why the pull costs what it costs
Three costs stack, and the network is only the first.
- Transfer: 80M rows over two columns is roughly 1.3 GB of raw values (8-byte float plus a short string), and 3-6 GB if it goes over the wire as CSV or JSON. At 100 MB/s that is 30-60 s before pandas parses a single value. Illustrative arithmetic, not a measurement.
- Parse and materialize: pandas builds an 80M-row frame. One float64 column is 640 MB, and
groupbyallocates a hash table over the keys plus a copy of the grouped values. - Compute: the groupby runs single-threaded on one machine, while the warehouse spreads the same aggregation across workers reading columnar storage.
Then the failure modes. The notebook OOMs when the column count doubles. A dropped connection at 90% restarts the whole pull. Every rerun re-transfers.
The correctness trap
This is the part people miss: the two placements can disagree on the answer, not just the cost.
# pandas 1.1+
df = pd.read_sql("SELECT region, amount FROM orders", conn) # 80M rows
df.groupby("region")["amount"].mean() # dropna=True by default
pandas groupby drops NaN keys by default (dropna=True), so rows with a NULL region disappear from the output. SQL GROUP BY keeps NULL as its own group. Same data, different group count. Float summation order differs too: numpy sums pairwise, the warehouse sums per partial aggregate, so the low bits of a large sum can differ. Neither is wrong, but a notebook that silently drops a group is.
Where the line is
Push down filters, joins, aggregations, window functions, top-k, distinct counts, and anything expressible as sum/count/min/max/sum-of-squares. Pull results small enough to hold comfortably, a few hundred MB as a sane ceiling, plus the work the warehouse genuinely cannot do: iterative model fitting, simulation, plotting, per-row inference with a Python-only library.
If you do need raw rows, for feature engineering or a training set, pull once into Parquet on local disk and reuse it rather than re-querying per cell.
Decide it by measurement, not taste: run both placements and compare bytes transferred and wall time. The bytes number is the one that generalizes to the next query.
Curated: · Written: · Reviewed:
Q&A-102A test reads -1.0% overall while treatment wins on both mobile and desktop. What has to be true for that to happen, and how do you tell a mix shift from a broken randomiser?
The overall number is a weighted average of the segment rates, and the weights are each arm's own segment mix. A reversal means the two arms have different mixes. In a randomised test that is a bug signal before it is a statistical curiosity, so I check the mix before I pick a number to report.
| Mobile | Desktop | Overall | |
|---|---|---|---|
| Control | 60,000 users, 2.0% (1,200) | 40,000 users, 8.0% (3,200) | 100,000, 4.40% |
| Treatment | 80,000 users, 2.2% (1,760) | 20,000 users, 8.8% (1,760) | 100,000, 3.52% |
| Effect | +0.2pp | +0.8pp | -0.88pp |
Treatment wins in both segments and loses 0.88pp overall, because treatment is 80% mobile and control is 60% mobile, and mobile converts at a quarter of desktop's rate. Three things can produce that, and they need different responses.
1. Broken randomisation or device-dependent logging. Test the split directly. Expected 70,000/70,000 mobile and 30,000/30,000 desktop; observed 60/80 and 40/20 gives chi-square = 9,524 on 2 df. That is not chance. Look for a redirect that fails on desktop, a device-detection branch in the assignment service, or a client SDK that drops desktop events. The overall estimate is confounded here: it is not a number you can report, only a number you can fix and re-run.
2. The segment is post-treatment. If "mobile" really means "used the mobile app after assignment", you are conditioning on a consequence of treatment. The arithmetic is real and the causal reading is meaningless. Check the segment's timestamp against the assignment timestamp.
3. Genuine heterogeneity with clean randomisation. Then the aggregate cannot reverse. It is a weighted average of the segment effects with the same weights in both arms, so it sits between them. You have a difference in effect size, not a paradox. Test it with a treatment x device interaction, not two separate segment p-values: two p-values will disagree by chance often enough to matter.
Which level to report follows from which case you are in:
- SRM present: neither number. The experiment is invalid until the pipeline is fixed.
- Clean, interaction not significant: report the overall. Segment cuts are exploratory and need multiplicity control.
- Clean, interaction significant and pre-registered: report the segment effects, and if you need one headline number, report the population-weighted effect, not the arm-weighted one.
Standardisation is the arithmetic for that last case: sum w_s * tau_s over segments, with population weights. At 70/30 mobile/desktop, 0.7 x 0.2 + 0.3 x 0.8 = +0.38pp. That answers "what happens if we ship to everyone", which is usually the question the business is actually asking.
import statsmodels.formula.api as smf
# With balanced randomisation the treatment coefficient is the
# population-weighted average of the segment effects.
m = smf.ols("converted ~ treated + C(device)", data=df).fit()
print(m.params["treated"], m.conf_int().loc["treated"])
Failure modes worth naming: slicing into segments after seeing a flat overall result and reporting the one that moved; calling a 20,000-user mobile slice "significant" without an interaction test; and reporting the raw arm-weighted average as the population effect. The first two are peeking, the third is the Simpson's reversal itself.
Curated: · Written: · Reviewed: —
Q&A-103You ship a churn model with offline AUC 0.94. In production it scores 0.52: barely better than a coin flip. Walk me through how you find and fix the leak.
A 0.94 offline AUC that lands at 0.52 in production is the signature of leakage, not drift. Drift moves you from 0.94 to maybe 0.88. A collapse to chance means the model learned a relationship that exists in the training table and does not exist at scoring time. I'd assume the label is "churned within 30 days" and the features come from a warehouse table, then check three things in order.
1. Is any feature computed after the label? Compare the feature's as-of timestamp to the label's. The usual suspects: cancellation_reason, account_status, days_since_last_login computed at extract time rather than at the observation date, or an aggregate like avg_monthly_usage taken over the account's whole lifetime. For a churned account, lifetime usage includes the months after churn where usage is zero, so the average drops. The model learns "low average usage = churn", but at scoring time you only have usage up to today, and every account's average is still high.
Ablation is the fast test: drop the feature, refit, watch the AUC. If it falls from 0.94 to 0.71, you found it. Permutation is the second: shuffle y, refit, and if AUC stays above 0.6, a feature is carrying the label.
2. Was any transformer fit before the split? Target encoding is the classic. Fit the encoder on the full dataset and split afterwards, and the validation rows' labels are baked into the training features.
# WRONG: encoder sees validation labels
enc = TargetEncoder()
X_enc = enc.fit_transform(X, y)
X_tr, X_va, y_tr, y_va = train_test_split(X_enc, y)
# RIGHT: encoder fits inside each fold
pipe = Pipeline([
("prep", ColumnTransformer([
("te", TargetEncoder(target_type="binary"), ["merchant_id"]),
], remainder="passthrough")),
("clf", LogisticRegression(max_iter=1000)),
])
cross_val_score(pipe, X, y, cv=TimeSeriesSplit(n_splits=5), scoring="roc_auc")
sklearn.preprocessing.TargetEncoder (scikit-learn 1.3+) cross-fits internally, so it is safe inside a pipeline. The same rule covers imputers, scalers, and any fit that touches y.
3. Was the split random on time-ordered data? If rows are account-months and you call train_test_split(shuffle=True), the model sees March while predicting February. Use TimeSeriesSplit or a fixed cutoff date, and make sure the feature window ends before the label window starts.
| Leak | Where it hides | Symptom | Fix |
|---|---|---|---|
| Target leakage | Feature computed after label | One feature dominates importance | Point-in-time feature build |
| Train/test contamination | Encoder or scaler fit before split | Offline AUC ≫ production | Fit inside the CV fold |
| Temporal leakage | Random split on time series | Great on shuffle, poor on future | Time-based split |
In production, log the feature vector at scoring time and compare its distribution to training. A feature that is near-constant at scoring time but varied in training is the leak. And the test for whether something is a leak is availability, not correlation: if the feature is genuinely known at scoring time, it is a legitimate feature, however predictive.
The fix that generalises: build every feature as of a timestamp, not as of "now". Each training row should be reproducible from data available at that row's observation date. Then the offline number is one you can defend.
Curated: · Written: · Reviewed: —