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.

$$\text{Null rate}_c = \frac{\text{count}(\text{missing in column } c)}{\text{count}(\text{total rows})}$$

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.