Common data-quality traps by dataset type
The four generic checks are the same for every table. What changes is what each kind of data habitually lies about. One row per dataset type, with the lie, the tell, and the quantities you would build.
The first-pass interrogation asks four questions of any table: rows, axis, values, derived quantities. The fifth question is domain-specific: what does this kind of data lie about? The table below is the answer for the dataset types that come up most, and for a type not on it, five minutes of searching <data type> data quality issues produces the equivalent row.
The table
| Shape | One row is | What it lies about | Quantities you would build |
|---|---|---|---|
| Daily equity panel (many tickers × dates) | one ticker on one day, OHLCV | splits and spin-offs (an unadjusted 7-for-1 looks like an 86% crash); survivorship (only today’s index members are in the file); scale (volume spans orders of magnitude across names) | close-to-close return, forward return, relative volume, trailing vol, cross-sectional ranks |
| Futures | one contract-day, or a stitched continuous series | contract rolls put fake jumps in a stitched price series; expiry truncates histories | roll-adjusted returns, days to expiry, open interest changes |
| FX | one pair per timestamp | no real volume; 24-hour days with no natural close; weekend gaps; triangular arbitrage ties pairs together | returns at a fixed daily cut, realised vol, cross rates |
| Options / vol surface | one strike-expiry per day | expiry and moneyness drift daily; stale quotes on illiquid strikes; puts and calls disagree through carry | implied vol by moneyness bucket, term structure slope, put-call skew |
| Credit / bonds | one issue per date, sparse | sparse and stale quotes; ratings are categories with an order; heavy missingness that is not random | spread changes, rating buckets, duration-matched returns |
| Fundamentals / macro | one company-quarter, or one release | look-ahead: a Q1 number dated 31 March was published in May; revisions overwrite history | features lagged by publication date, first-release vintages |
| Intraday / order book | one trade or quote | session boundaries (auctions, halts); microstructure noise (bid-ask bounce makes reversal appear from nothing); timestamps in mixed clocks | bar aggregation, signed volume, spreads, mid-price returns |
| Non-financial tabular (trips, energy, web events) | an event, or an entity-period | leaked target columns (a “total” that contains the target); labels that aren’t labels (zero means “not recorded”); duplicated events; ambiguity about what a row is | whatever the question needs, after the leakage check |
Things that are in almost every file
Whatever the type, expect at least one of:
- nulls in a few columns, concentrated in a way that means something (the early rows, one source, one group);
- a handful of impossible values (a high below its low, zero volume, a negative duration, a 0-passenger trip);
- short histories for some groups, because they were listed, created or instrumented late;
- extreme values that are either real events or unadjusted artefacts, and no time to check them all.
On the daily equity file used across this site, the counts are:
import pandas as pd
df = pd.read_csv("all_stocks_5yr.csv")
df["date"] = pd.to_datetime(df["date"])
df = df.rename(columns={"Name": "ticker"}).sort_values(["ticker", "date"])
df["ret"] = df.groupby("ticker")["close"].pct_change()
per = df.groupby("ticker").size()
print("tickers with < 1000 rows:", (per < 1000).sum(), "of", len(per))
print("rows with |return| > 25%:", (df["ret"].abs() > 0.25).sum())
print("null opens:", df["open"].isna().sum(), "| duplicate (date, ticker):", df.duplicated(["date", "ticker"]).sum())
print("high < low:", (df["high"] < df["low"]).sum(), "| zero volume:", (df["volume"] == 0).sum())tickers with < 1000 rows: 22 of 505
rows with |return| > 25%: 46
null opens: 11 | duplicate (date, ticker): 0
high < low: 1 | zero volume: 4The three that decide results
Most of the rows in the table are inconveniences. Three of them change what a result means, and they need to be named in the report whether or not they can be fixed:
- Survivorship. If the universe was chosen with knowledge of who survived, anything that buys losers is flattered, because the losers that kept losing are not in the file. It cannot be fixed without a point-in-time universe. It can be named.
- Look-ahead through publication lag. A feature that uses a number before anyone could have known it produces a result that nobody could have had. Fix by lagging to the publication date; see forward targets and leakage.
- Leaked target columns. A column that is an arithmetic function of the target makes any model look perfect. Find it before modelling; see leaked columns and labels that aren’t labels and the taxi case study.
Splits versus real moves
Of the 46 extreme daily moves on the equity file, some are earnings or trial results, and some are stock splits, spin-offs and bad prints. Two by eye is enough to decide a policy; see extreme values: real events or artefacts?.