Airbnb Data Scientist Interview Preparation Guide - Entry Level

Direct answer

A north-star metric (NSM) is the single measure a team rallies around because it best captures durable customer value and predicts long-term business health. The primary metric is the nearer-term business key performance indicator (KPI) the organization is actively driving this quarter or half. Secondary metrics diagnose or drive movement in the primary metric. Vanity metrics look good on a chart but do not reliably track health, and guardrail metrics are constraints you watch to make sure progress on the others isn't bought at someone else's expense.

Structured elaboration

These five sit on the same dashboard for different jobs: one aspirational, one operational, several diagnostic, one to be treated with suspicion, and one defensive.

The relationship across the tree: the NSM sits at the top as the value signal; the primary metric is often an input to the business case operationalized for the current period; secondary metrics are the levers that move the primary metric; guardrails constrain the search space; vanity metrics are explicitly excluded from the decision-making set even though they may still be reported.

Worked example

Suppose a marketing team runs a giveaway campaign. Free-trial signups rise from 2,000 to 2,600 (a vanity-metric jump), but only a fraction of new signups reach the NSM's bar of completing the core workflow at least twice in month one.

Suppose 5.5% of the original 2,000 signups reached the NSM bar ($2000 \times 0.055 = 110$ accounts), while only 5.0% of the new, giveaway-driven 2,600 reached it ($2600 \times 0.05 = 130$ accounts):

The vanity metric (signups) grew 30% while the NSM grew only 18%, and the underlying activation rate actually fell (5.5% down to 5.0%). This is exactly the gap a north-star metric is supposed to expose: a channel can inflate top-of-funnel numbers while quietly degrading the quality of what comes through it.

Trade-offs & pitfalls

• Treating the primary metric as if it were the NSM conflates a short-term operating target with a long-term value signal; a single quarter's revenue push (aggressive discounting, for instance) can hit MRR while damaging the NSM.
• Guardrails have to be chosen adversarially: think about what a team would be tempted to sacrifice to hit the primary metric, not just list whatever stability numbers are convenient to pull.
• Vanity metrics are not inherently useless as operational counters; the mistake is promoting them to decision-driving status alongside the NSM.
• Over-indexing on a single NSM number can suppress attention to guardrails; a senior answer shows awareness of the whole portfolio, not just the headline metric.

Direct answer

CUPED (Controlled-experiment Using Pre-Experiment Data, the variance-reduction method published by Deng, Xu, Kohavi, and Walker at Microsoft, WSDM 2013) adjusts the experiment outcome $Y$ by subtracting a scaled, pre-experiment covariate $X$ that is correlated with $Y$ but unaffected by treatment. The optimal scaling coefficient is $\theta^* = \mathrm{Cov}(X,Y)/\mathrm{Var}(X)$, and the payoff is that the adjusted outcome's variance shrinks by a factor of $(1-\rho^2)$, where $\rho$ is the correlation between $X$ and $Y$, which under a fixed minimum detectable effect and fixed power translates directly into a required sample size that shrinks by the same factor. The method breaks down if $X$ is itself affected by treatment, because then part of the true treatment effect gets subtracted out along with the noise.

Structured elaboration

Deriving the optimal coefficient

Define the adjusted outcome as $Y' = Y - \theta (X - E[X])$. Subtracting a constant, $\theta E[X]$, does not change variance, so $Y'$ has the same variance as $Y - \theta X$:
$\mathrm{Var}(Y') = \mathrm{Var}(Y) - 2\theta\,\mathrm{Cov}(X,Y) + \theta^2 \mathrm{Var}(X)$
This is a quadratic in $\theta$, minimized where its derivative with respect to $\theta$ is zero:
$\frac{d}{d\theta}\mathrm{Var}(Y') = -2\,\mathrm{Cov}(X,Y) + 2\theta\,\mathrm{Var}(X) = 0 \quad \Rightarrow \quad \theta^* = \frac{\mathrm{Cov}(X,Y)}{\mathrm{Var}(X)}$
Subtracting $\theta(X - E[X])$ does not shift the mean of $Y'$, since $E[X - E[X]] = 0$, so this adjustment is unbiased for the mean of $Y$, and therefore unbiased for the treatment effect, as long as $X$ is unaffected by treatment (more below).

Variance reduction in terms of rho

Substituting $\theta^*$ back into the variance expression, writing $\sigma_X^2 = \mathrm{Var}(X)$ and $\sigma_Y^2 = \mathrm{Var}(Y)$:
$\mathrm{Var}(Y') = \sigma_Y^2 - 2\theta^*\,\mathrm{Cov}(X,Y) + (\theta^*)^2\sigma_X^2 = \sigma_Y^2 - \frac{\mathrm{Cov}(X,Y)^2}{\sigma_X^2}$
Using $\rho = \mathrm{Cov}(X,Y)/(\sigma_X \sigma_Y)$, so $\mathrm{Cov}(X,Y)^2 = \rho^2 \sigma_X^2 \sigma_Y^2$:
$\mathrm{Var}(Y') = \sigma_Y^2 - \frac{\rho^2 \sigma_X^2 \sigma_Y^2}{\sigma_X^2} = \sigma_Y^2(1 - \rho^2)$
CUPED reduces the variance of the outcome used in the treatment-effect estimate by exactly a factor of $(1-\rho^2)$: a covariate correlated at $\rho = 0.5$ removes $\rho^2 = 25\%$ of the outcome variance; at $\rho = 0.7$ it removes $\rho^2 \approx 49\%$. The relationship is quadratic in $\rho$, so weakly correlated covariates, $\rho$ below roughly 0.3, remove under 9% of variance and buy very little.

How required sample size scales with rho

The standard two-sample sample-size formula for a fixed minimum detectable effect (MDE), significance level, and power is proportional to the outcome variance: $n \propto \sigma^2 / \text{MDE}^2$, holding alpha and power fixed; that base relationship itself belongs to standard power-analysis mechanics, not to CUPED. Since CUPED only changes the variance term, from $\sigma_Y^2$ to $\sigma_Y^2(1-\rho^2)$, and leaves the MDE and the estimator's unbiasedness unchanged, the required sample size scales the same way:
$\frac{n_{\text{CUPED}}}{n_{\text{raw}}} = 1 - \rho^2$
A pre-experiment covariate correlated at $\rho = 0.7$ with the outcome would let you reach the same statistical power with about $1 - 0.49 = 0.51$, roughly half the sample, or equivalently run the same sample for about half the calendar duration.

What breaks if X is affected by treatment

The derivation above relies on $\theta$ being estimated from data where $X$ has no relationship to treatment assignment. If $X$ is measured after treatment starts, or is otherwise influenced by it, then $X$ itself carries part of the treatment signal, and subtracting $\theta(X - E[X])$ subtracts part of that signal out of $Y'$ along with the noise, biasing the estimated treatment effect toward zero. This is the same failure mode as conditioning on a variable that sits on the causal path between treatment and outcome: the fix is procedural, not statistical, use only strictly pre-experiment values of $X$, measured before randomization occurs, computed identically for both arms.

Worked example

Suppose a pre-experiment covariate is a user's prior 28-day spend, and analysis of historical (pre-experiment) data gives stated, illustrative moments $\sigma_X^2 = 400$, $\sigma_Y^2 = 100$, and $\mathrm{Cov}(X,Y) = 120$. Then:
$\theta^* = \frac{\mathrm{Cov}(X,Y)}{\mathrm{Var}(X)} = \frac{120}{400} = 0.3$
$\rho = \frac{\mathrm{Cov}(X,Y)}{\sigma_X \sigma_Y} = \frac{120}{\sqrt{400}\sqrt{100}} = \frac{120}{20 \times 10} = 0.6$
$\mathrm{Var}(Y') = \sigma_Y^2(1-\rho^2) = 100 \times (1 - 0.36) = 64$
The adjusted outcome variance drops from 100 to 64, a 36% reduction, and the required sample size for the same MDE and power drops to $1 - 0.36 = 0.64$, 64% of the original sample.

Trade-offs and pitfalls

• $\theta$ itself must be estimated, from pooled or historical data, which adds a small amount of estimation noise not captured in the idealized derivation above; in practice this is usually negligible if $\theta$ is estimated on a large enough historical sample, but it is not literally zero cost.
• CUPED buys nothing for users with no pre-experiment history, since $X$ is undefined or has to be imputed for them; teams typically report CUPED-adjusted results for existing users and a separate, unadjusted analysis for new users rather than silently imputing.
• The variance reduction is entirely a function of $\rho$; picking a covariate that is easy to compute but weakly correlated with the actual outcome metric wastes engineering effort for little statistical payoff, so the covariate should be chosen and validated on historical data before committing to it, not assumed.
• The most common real-world mistake is using a covariate window that overlaps with treatment start, even by a day, because of a timezone or pipeline lag; this silently reintroduces the treatment-affected-X bias described above while looking, superficially, like a valid pre-experiment covariate.

Direct answer
For a multi-variant cohort retention analysis where cohorts vary in size and some observations are right-censored (a user hasn't reached day-30 yet because they signed up recently), model retention as a time-to-event process, compute survival estimates that correctly account for censoring rather than treating not-yet-observed users as churned, and communicate the result with a survival curve, an uncertainty band, and a single plain-language summary rather than the raw statistical machinery.

Structured elaboration

1. Why simple retention percentages break with censoring: computing "day-30 retention" naively for users who signed up 10 days ago and haven't yet had the chance to reach day 30 either wrongly excludes them or wrongly counts them as churned; a survival-analysis approach correctly treats them as "still at risk, not yet observed to fail or survive."
2. Survival curve: a step function estimating the probability of "surviving" (remaining active) past each time point, correctly incorporating every user's actual observed follow-up time rather than requiring every user to have reached the same fixed horizon.
3. Uncertainty: a confidence band around the survival curve, which naturally widens at later time points and for smaller cohorts, since fewer users have been observed that long.
4. Comparing cohorts of different sizes: because the method inherently accounts for each cohort's number of at-risk users at each time point, cohorts of very different sizes can still be validly compared on the same survival-curve chart, with the smaller cohort's wider confidence band visually signaling its lower certainty.
5. Communicating to a non-technical audience: present the survival curve as "percent of users still active after N days," the confidence band as a plain "how sure we are" shaded region, and give one summary statistic (e.g. "half of new users are still active after roughly 45 days") rather than the underlying statistical estimator's name or formula.

Worked example
Comparing two cohorts, one with 5,000 users observed for a full 90 days and a newer one with 800 users only observed for 20 days so far: a naive day-30 retention calculation would either drop the newer cohort's still-active-but-not-yet-30-days users or wrongly mark them all as failures, while treating the underlying data as a time-to-event process correctly uses every user's actual observed duration and produces a valid (if wider-banded, appropriately less certain) survival estimate for the newer cohort. Concretely: at day 20, the older 5,000-user cohort's estimated survival is 68% (95% CI: 65% to 71%, a 6-percentage-point-wide band), while the newer 800-user cohort's estimated survival at that same day is 71% (95% CI: 62% to 79%, a 17-percentage-point-wide band). The point estimates are close (68% vs. 71%), but the newer cohort's band is nearly three times as wide, correctly signaling that far fewer of its users have actually been tracked out to day 20, so that estimate should be trusted less than the older cohort's tighter one, even though both are computed by the exact same method.

Trade-offs and pitfalls
The most damaging shortcut is silently excluding right-censored (not-yet-observed) users from the denominator, which systematically biases retention estimates, especially for the most recent, still-accumulating cohorts.

Direct answer
Grid search is exhaustive and simple but wastes trials in high dimensions; random search covers more of the space per trial; Bayesian optimization is sample-efficient for expensive, low-to-moderate-dimensional, low-noise objectives; Hyperband trades a small risk of discarding a late bloomer for large speedups by exploiting cheap partial evaluations; population-based training is the right tool when hyperparameters should themselves change during a single training run.

Structured elaboration

• Grid search: fully parallel (every point is independent), degrades sharply as dimensionality grows (a 5-value grid over 5 hyperparameters is already 3,125 combinations), and handles noisy objectives poorly since it never revisits or refines a promising region.
• Random search: also fully parallel, scales much better with dimensionality (Bergstra & Bengio's classic result: it finds comparably good configurations in a fraction of grid search's trials when only a few hyperparameters actually matter), still doesn't adapt based on what it's already learned.
• Bayesian optimization: sequential by nature (each new point depends on the surrogate fit to all previous points), which limits parallelism (though batched/async variants exist); handles noisy objectives by modeling noise explicitly in the surrogate, but the surrogate model itself degrades in high dimensions (roughly beyond 15-20 continuous hyperparameters, the standard Gaussian-process surrogate stops being reliable).
• Hyperband/Successive Halving: highly parallel within each rung, and its core trick is spending most of the budget only on configurations that already look promising at a cheap fidelity; the real risk is discarding a configuration whose LEARNING CURVE is slow to start but eventually wins, a genuine failure mode when candidate configurations have very different convergence speeds.
• Population-based training: unlike the others, it doesn't pick hyperparameters once, it evolves them DURING training, which is the right fit when the ideal hyperparameter schedule genuinely changes over the course of training (e.g. a learning rate that should decay differently depending on how training is progressing) rather than being one fixed best value.
  When to prefer which: grid for a tiny, cheap, low-dimensional space where exhaustiveness itself has value (e.g. regulatory documentation); random as a solid, nearly cost-free default upgrade over grid; Bayesian opt when each trial is genuinely expensive (hours) and you have a modest number of hyperparameters; Hyperband/ASHA (Asynchronous Successive Halving) when trials are cheap to partially evaluate (most neural network training) and you want the search wall-clock time down; population-based training (PBT) when you're training one long run and want the hyperparameter schedule itself to adapt.

Worked example
Tuning a transformer with 6 continuous/discrete hyperparameters where a single full training run takes 8 hours: pure grid search over even 3 values per hyperparameter is 729 runs, infeasible. Bayesian optimization over ~30-50 full runs is a realistic, sample-efficient choice here. If instead the model trains in 20 minutes and you can afford thousands of partial runs, ASHA lets you explore far more configurations for the same total compute by killing bad ones early.

Trade-offs & pitfalls
It's tempting to always reach for the most sophisticated method (Bayesian or Hyperband); for a very cheap, very low-dimensional search, plain random search with a healthy trial budget is often just as effective and far simpler to implement and debug.

Requirements & constraints:

• Functional: return top-K candidate items for a user in <100ms; support 10M+ items, frequent metadata updates (minutes), offline retraining weekly.
• Non-functional: recall-focused retrieval, scalable, consistent.

High-level architecture:
User features -> user-tower model -> user embedding
Item metadata/content -> item-tower model -> item embedding
ANN index (item embeddings) + retrieval service -> top-N candidates -> reranker/rescorer -> final list

Offline training flow:

• Prepare labeled training pairs (user, positive item, negatives) from interactions; augment with metadata.
• Train two-tower with contrastive loss (e.g., InfoNCE or sampled softmax) and L2-normalize outputs.
• Validate with offline metrics (recall@K, MRR) and A/B test candidate set diversity.
• Export item-tower weights and batch-compute item embeddings in a Spark/Beam job; store embeddings in object store and push to index builder.

Building & serving ANN index:

• Use Faiss (IVF+PQ or HNSW) depending on latency/accuracy trade-off. For high recall + low latency at 10M+, IVF-PQ with GPU indexing or HNSW for dynamic updates.
• Build index in a separate job: load item embeddings, train IVF centroids if used, add vectors, persist shards.
• Serve via a microservice (gRPC) with sharded indices across nodes (consistent hashing); each shard holds ~1-5M vectors in memory/SSD-mmap.
• Use asynchronous pre-warming on startup and expose top-K retrieval endpoint.

Refreshing embeddings on metadata changes:

• Item updates: on metadata change event (Kafka), compute new item embedding via lightweight item-tower inference service; write new embedding to embedding store and to a "delta" queue.
  • For mostly-static index (IVF-PQ): accumulate deltas and perform incremental batch index rebuilds (minutes cadence) or use an append-only HNSW shard for recent items and merge periodically.
  • For HNSW: support online add/delete for immediate consistency.
• User embeddings: compute on request using latest user features; also precompute for active users via streaming jobs and cache in Redis with TTL and version tag.
• Maintain embedding versioning; retrieval includes model version header to ensure consistency between user embedding and index.

Ensuring <100ms latency:

• Precompute and cache user embeddings for active users; compute on-demand only if cache miss.
• Shard ANN and colocate shards with vector store; parallel scatter-gather retrieval across shards with early stopping to reduce work.
• Use async RPCs, connection pooling, and CPU/GPU optimized Faiss kernels.
• Keep vector dimension compact (e.g., 64-128) and use PQ to reduce memory and increase throughput.
• Employ LRU cache for popular query results, and use result-level caching with short TTLs.
• Monitor tail latency and set SLA-based autoscaling for ANN nodes.

Additional considerations:

• Soft-fallback to metadata-based candidate generation if ANN down.
• Periodic full rebuild (nightly/weekly) to compact index and apply global PQ retraining.
• Instrument recall drift (online holdout), latency, QPS, and use canary deployments for model/index changes.

Direct answer
An executive or public postmortem communication keeps the same underlying facts as the internal engineering document but changes structure and depth: lead with impact and resolution status in plain language, compress the technical root cause into one or two sentences a non-specialist can follow, and route anything sensitive, legally uncertain, or still under investigation through legal or compliance review before it goes out, rather than including it by default.

Structured elaboration

• Lead with what the audience actually needs. Executives and customers care first about impact (who was affected, how badly, for how long) and current status (is it fixed, is it safe now), not the internal technical mechanism. Put that first, not buried after a long technical narrative.
• Compress, don't omit, the root cause. A one or two sentence plain-language root cause ("a configuration change removed a safeguard that normally limits how much traffic a single request can trigger") is usually enough; the internal document's full technical detail isn't needed here and can overwhelm or confuse rather than reassure.
• Say what's being done, concretely. Vague reassurance ("we take this seriously and are reviewing our processes") reads as evasive. Specific, verifiable commitments ("we are adding an automated safeguard, expected within two weeks") build more trust even when the news is bad.
• Route sensitive content through review before drafting is even final. Anything touching legal exposure, an ongoing investigation, regulatory disclosure requirements, or third-party or customer data (for example a possible PII exposure) needs legal or compliance sign-off on both content and timing, since public/customer communication commitments here can create legal exposure of their own if stated imprecisely.
• Don't minimize real impact to make the story feel better. Understating severity or hedging around clear facts, once discovered (and it usually is), costs far more trust than a direct, honest account would have.

This same discipline extends past software outages: a public account of a failed research study that led to a wrong decision, or a partnership failure with a strategic account, follows the identical shape (impact first, plain-language cause, concrete next steps), adapted in vocabulary but not in structure.

Worked example
An internal postmortem for a data-exposure incident runs several pages with full technical detail about the specific misconfigured storage permission, exact timestamps, and internal system names. The customer-facing version: a short notice stating what data was potentially exposed (in plain terms, not internal system jargon), the window of exposure, what's being done for affected customers specifically, and what changed technically (again in plain terms: "we've added an additional access control layer and are auditing all similar configurations") without naming the specific internal service or engineer. Legal reviews the draft specifically for regulatory disclosure requirements in relevant jurisdictions before it ships, and the technical team confirms every factual claim in the customer version traces back to something actually verified in the internal postmortem, not to speculation.

Trade-offs and pitfalls
The most common failure is either two extremes: an overly technical public statement that reads as evasive because it's incomprehensible, or an overly vague one that reads as evasive because it says nothing concrete. A second common failure is treating legal review as a final rubber-stamp rather than involving it early enough to shape what can honestly and safely be said, which under time pressure to communicate fast, teams sometimes skip.

Direct answer: Detect schema drift by computing a small structured "snapshot" of every ETL (extract, transform, load) run (columns present, dtypes, null rates, and cheap distribution summaries), comparing it against a stored baseline snapshot, and alerting when the diff crosses a severity threshold. The hard part is not computing the diff, it is choosing thresholds and storage that keep false-positive alerts rare enough that people keep trusting the system.

Structured elaboration: architecture

ETL run (pandas) -> schema-checker step -> snapshot store (object store + metadata DB) -> diff vs. baseline -> alerting / dashboard

1. Schema-checker: what to capture per run

• Column set (added / removed columns since baseline).
• Per-column dtype.
• Per-column null rate.
• Per-column cheap distribution summary: quantiles for numeric columns, top-k value frequencies for categorical/text columns.

import pandas as pd, json, hashlib

def snapshot(df):
    s = {}
    for c in df.columns:
        col = df[c]
        s[c] = {
            "dtype": str(col.dtype),
            "null_rate": float(col.isna().mean()),
            "nuniq": int(col.nunique(dropna=True)),
        }
        if pd.api.types.is_numeric_dtype(col):
            s[c].update({"q25": col.quantile(0.25), "q50": col.quantile(0.5), "q75": col.quantile(0.75)})
        else:
            top = col.fillna("__NA__").value_counts().head(5).to_dict()
            s[c]["top"] = top
    s["_hash"] = hashlib.sha256(json.dumps(s, sort_keys=True, default=str).encode()).hexdigest()
    return s

Verified against a concrete fixture:

df = pd.DataFrame({
    "id": [1, 2, 3, 4, 5],
    "amount": [10.5, 20.1, None, 15.0, 30.2],
    "category": ["a", "b", "a", None, "c"],
})
print(snapshot(df))

Actual output: id gets {'dtype': 'int64', 'null_rate': 0.0, 'nuniq': 5, 'q25': 2.0, 'q50': 3.0, 'q75': 4.0}, amount gets {'dtype': 'float64', 'null_rate': 0.2, 'nuniq': 4, 'q25': 13.875, 'q50': 17.55, 'q75': 22.625}, and category gets {'dtype': 'str', 'null_rate': 0.2, 'nuniq': 3, 'top': {'a': 2, 'b': 1, '__NA__': 1, 'c': 1}} plus a stable _hash. This confirms the function correctly captures dtype, null rate, cardinality, and a compact distribution summary for both numeric and categorical columns.

2. Drift rules, ordered by how much they should scare you

• Column add/remove: a plain set difference between the current and baseline column lists. Treat as HIGH severity, this is the change most likely to crash a downstream consumer outright.
• Dtype change: any mismatch against the baseline dtype. HIGH severity by default, but allow a configured whitelist of known-safe casts (e.g. int64 widening to float64 when nulls are newly introduced) so you don't page someone for an intentional, reviewed change.
• Null-rate shift: compare the current null rate to the baseline's null rate; flag when the absolute change exceeds a configured threshold (e.g. +10 percentage points) or the relative change is large (e.g. 3x) relative to normal run-to-run variance.
• Distribution drift: for numeric columns, compare quantiles directly or use a two-sample statistical distance (Kolmogorov-Smirnov test, or a Wasserstein/earth-mover's distance) between the current and baseline value distributions. For categorical columns, compare the top-k frequency table with a chi-squared test or track the share of previously-unseen category values. Require a minimum sample size before running a statistical test, otherwise small batches produce noisy, spurious alerts.

3. Storage

• Store the full structured snapshot (JSON) per run in an object store, with a lightweight metadata row (run id, timestamp, content hash, pointer to the object, row count, status) in a relational table for fast querying and trend charts.
• Keep two references: a manually-approved "golden" baseline snapshot for drift comparison, and a rolling window of the last N runs for trend analysis (so a slow, multi-run drift is visible even if no single run trips a threshold).

4. Alerting

• Route by severity: HIGH to a paging channel, MEDIUM/LOW to a Slack channel or dashboard-only.
• Every alert should carry the specific before/after values (not just "drift detected"): which columns changed, old vs. new dtype or null rate, the statistical test's p-value, and a handful of example rows illustrating the shift, so the person paged doesn't have to re-run the check manually to understand it.

5. Automation

• Run the schema-checker as a step inside the ETL's own orchestration (Airflow, Dagster, or equivalent). A HIGH severity result can fail or quarantine the run outright; MEDIUM/LOW can continue but still notify, so a benign but real schema evolution doesn't need to break the pipeline every time.
• Maintain an explicit whitelist / migration process for intentional schema changes, so an expected column rename doesn't repeatedly re-trigger the same alert.

Trade-offs and pitfalls

• Storing full value histograms per run is expensive at scale, top-k frequencies and quantiles are almost always enough signal at a fraction of the storage.
• Statistical tests need enough rows to be meaningful, for small or bursty batches, prefer simple heuristic thresholds (percentage-point null-rate change, dtype equality) over a p-value that will be noisy on 200 rows.
• The single biggest failure mode of a drift system is alert fatigue: too-sensitive thresholds train people to ignore the channel, and by the time a real break happens nobody is watching. Start with generous thresholds and tighten only once you have enough historical runs to know normal variance.
• A snapshot-and-diff system detects drift after the fact, it does not prevent a bad schema from landing; pairing it with an explicit, versioned schema contract that the ETL validates against on write catches the same class of problem earlier and with less noise.

Direct answer

Inter-annotator agreement measures how consistently two or more people label the same items; Cohen's kappa improves on simple percent agreement by subtracting out the agreement you'd expect from chance alone, given how often each annotator uses each label. A low kappa alongside high percent agreement usually means the labels are heavily imbalanced (one category dominates), so two annotators can agree often just by both defaulting to the common label, without actually agreeing meaningfully on the harder, rarer cases.

Why kappa can diverge sharply from percent agreement

Percent agreement treats every match the same, whether it's a genuinely informative agreement on a rare case or a trivial agreement on the overwhelmingly common category that either annotator would have guessed anyway. Kappa's chance-correction penalizes exactly that: if one label is used 90% of the time by both annotators, a large share of their raw agreement is arithmetically guaranteed by that imbalance alone, and kappa strips that out to show what's left once you account for it.

Worked example

Two annotators labeling 100 items into neutral, positive, or negative categories, where neutral dominates (used roughly 90 times by each annotator): the confusion matrix shows 87 exact matches out of 100, an 87% raw agreement. But because both annotators use "neutral" so overwhelmingly often, the chance-expected agreement (computed from how often each annotator uses each label) comes out to about 80.6%. Cohen's kappa, the gap between actual and chance agreement scaled by the room available above chance, comes out to only about 0.33, a modest agreement level despite the impressive-looking 87% raw number, because most of that 87% was arithmetically expected anyway given how skewed the label distribution is.

Trade-offs and pitfalls

A low kappa alongside high raw agreement is a strong signal to look specifically at the RARE categories' agreement rate, not the overall rate, since that's usually where the real disagreement (and the real ambiguity in the labeling guidelines) is hiding.

Direct answer: Naming collisions across teams (two features with a similar name and a subtly different definition, or two teams both computing something like "user_engagement" differently) are best prevented with a required namespace convention plus automated similarity detection at review time, and resolved when they do occur through an explicit consolidation-or-alias governance flow rather than letting both versions silently coexist.

Structured elaboration:

• Namespacing: require every feature name to be qualified by its owning team or domain (e.g. growth.engagement_score versus retention.engagement_score), which doesn't prevent semantic collisions but at least makes it structurally impossible for two teams' features to literally collide on the same bare name.
• Metadata strategy: every feature's catalog entry should include enough of its actual computation logic (not just a free-text description) that an automated similarity check can compare two candidates' underlying logic, not just their names.
• Automated similarity detection: at feature-review time, compare a new feature's name, description, and transformation logic against the existing catalog (textual similarity on descriptions, structural similarity on the underlying SQL/code, or distributional similarity on computed values) and flag likely duplicates for human review before they ship.
• Governance flow for resolution: when a genuine collision is found (two features that ARE measuring the same underlying concept), the resolution is either to consolidate onto one canonical feature with the other becoming a deprecated alias, or, if they're subtly-but-genuinely different, to rename both so the distinction is explicit in the name itself, never to just leave two ambiguously-named things both live.

Worked example: Two teams each independently build a feature they call user_engagement: one computes it as a weighted sum of session count and average session duration, the other as a binary "active in the last 7 days" flag. A downstream analyst pulling user_engagement without knowing which team's version they got would draw completely different conclusions depending on which table they queried; an automated review-time similarity check on the NAME alone would have flagged this collision before either feature shipped, giving the teams a chance to either genuinely consolidate or rename to growth.engagement_weighted_score and retention.active_7d_flag so the ambiguity can't happen silently.

Trade-offs and pitfalls: Namespacing alone doesn't solve the deeper problem: two features can have completely different names and still measure the same underlying concept (the semantic collision the automated similarity check is meant to catch), or the same name and genuinely mean different things by design (in which case the fix is a documentation/naming clarity problem, not a technical dedup problem).

Direct answer: Deduplicating rows per (user_id, event_type) while keeping the latest by event_time is a classic window-function pattern: rank rows within each (user_id, event_type) partition by event_time descending, and keep only rank 1.

Structured elaboration: Postgres's ROW_NUMBER() window function assigns a sequential rank within each partition according to the specified ordering; partitioning by (user_id, event_type) and ordering by event_time DESC means the most recent row for each user/event_type pair gets rank 1, and filtering to rank 1 in an outer query keeps exactly one row per group, which is precisely the requested deduplication.

WITH ranked AS (
  SELECT
    event_id,
    user_id,
    event_time,
    properties,
    properties->>'type' AS event_type,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, properties->>'type'
      ORDER BY event_time DESC
    ) AS rn
  FROM events
)
SELECT event_id, user_id, event_time, properties, event_type
FROM ranked
WHERE rn = 1;

Worked example (verified by execution): For sample rows (1, 101, '2024-05-01T10:00:00', {"type":"click"}), (2, 101, '2024-05-01T11:00:00', {"type":"click"}), and (3, 101, '2024-05-01T09:00:00', {"type":"view"}), running the query above returns exactly two rows: event_id 2 (the later of the two click events for user 101, event_time 11:00) and event_id 3 (the only view event for that user), correctly dropping event_id 1 as the older duplicate click. This was verified by loading the sample rows into an in-memory SQLite equivalent of the logic (using an equivalent ROW_NUMBER() window function, which SQLite also supports) and confirming the same two rows survive.

Trade-offs & pitfalls: ROW_NUMBER() requires a strict tie-break; if two rows for the same (user_id, event_type) share the identical event_time exactly, the ordering between them is otherwise arbitrary unless a secondary ORDER BY key (such as event_id) is added to make the result deterministic, which matters for reproducibility if this query is ever re-run and expected to produce identical output. On a large events table, this pattern requires a full scan and sort within each partition; if (user_id, properties->>'type') is a frequently-filtered or frequently-deduplicated dimension, an index on user_id (and, if the JSONB extraction is expensive at scale, a generated/indexed column for event_type rather than extracting it inline on every query) meaningfully improves performance over repeatedly parsing JSONB and sorting the whole table.