Profiling a Raw Feed: Rows, Keys and Nulls

A researcher gets a new dataset, jumps straight to backtesting a signal idea, and two weeks later discovers the vendor silently changed their identifier scheme halfway through the history, so half the "signal" was actually a data artifact. Profiling, the unglamorous work of counting rows, checking what uniquely identifies each row, and measuring how often fields are missing, is what catches this before it wastes two weeks. It is boring on purpose; the goal is to find problems in an hour that would otherwise surface as a mysterious backtest anomaly months later.

The three checks

Row counts. Plot the number of rows per day (or per period) across the whole history. A sudden jump or drop is either a real event (a data vendor expanded coverage) or a quiet bug (a feed broke and started dropping records), either way, it needs an explanation before the data is trusted.

The key. Every dataset has some combination of columns that should uniquely identify a row, often something like (entity identifier, date). Checking that this combination really is unique, with no duplicate rows, catches join bugs before they silently double-count or drop data downstream.

Nulls and sentinel values. Missing data doesn't always show up as a clean null, vendors sometimes fill gaps with 0, -1, or 9999 instead of leaving a field blank. A quick tabulation of each column's most common values often surfaces these sentinel fillers immediately.

In words: the null rate for a column is simply the fraction of rows where that field is missing, and tracking this per column (and per time period, since null rates often change over a dataset's history) is the fastest way to spot a field that quietly stopped being populated partway through.

Worked example

A researcher profiles a new fundamentals dataset. Row count per quarter is stable at roughly 3,200 until eighteen months ago, when it drops to about 2,100 and stays there, a real change (the vendor cut smaller-cap coverage) that needs to be factored into any backtest spanning that boundary. Checking the key, (ticker, fiscal-quarter) is unique in 99.7% of rows, but 40 rows have exact duplicates, tracing one shows a company that changed tickers mid-quarter and got recorded under both, a fixable but easy-to-miss join bug. Tabulating the "revenue" column's values shows 0.00 appearing far more often (2.3% of rows) than a genuine zero-revenue company should, cross-checking against a couple of those tickers confirms 0.00 is being used as the vendor's null placeholder, not a real value, and needs to be recoded to a true missing value before any ratio involving revenue is computed.

What this means in practice

None of these three checks require sophisticated tools, a handful of group-by and count queries covers all of them, but skipping them is one of the most common causes of research that looks promising in a backtest and falls apart once someone traces why. Profiling is cheap; debugging a signal that turned out to be a data artifact, three weeks into a research sprint, is not.