Silent Data Errors That Survive Every Cleaning Pipeline

SELECT id, first_name, last_name, department_id, salary FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY salary DESC, department_id DESC ) AS rn FROM ms_employee_salary ) s WHERE rn = 1 ORDER BY id ASC;

Output:

That ROW_NUMBER() trick is the right answer here.

It's also what a warehouse does when ingesting a slowly changing dimension with no effective-dated column: "most recent" becomes a guess, and any feature joined on employee_id downstream inherits it. Use that inferred "current" salary to recreate a prediction made 6 months earlier, and the historical row gets information from its own future. That's the leak.

A related Amazon question is a time-window bug, not a leakage bug: users with a second purchase 1 to 7 days after their first, ignoring same-day purchases.

Swap that strict BETWEEN 1 AND 7 for a loose <= 7, and same-day, multi-item baskets start counting as returning users, a retention number inflated by rows that were never a second visit. The query sets its own definition of "return visit" here; no future information reaches the model.

The fix is structural: store both when an event happened and when the row was written, and join features with a strict event_time <= feature_time inequality, never a bare MAX().

Survivorship bias is a denominator problem: the rows that would tell you the truth were removed before your query ran. The classic case is hedge fund index returns calculated only over funds still trading, dropping every fund that closed. The same shape shows up whenever someone reports on "customers who purchased in 2024" without noticing that this already excludes everyone who churned in Q1.

This Meta question asks for the percentage of users who are both from the US and have an "open" status, using a table called fb_active_users.

Data: 23 rows, with a status column marking each user "open" or "closed."