Online Tool Store
· 5 min read
How to Profile a Dataset Before You Use It
Manesh Jayawardhana
CIO & Co-founder
The analysis is finished, the chart is in the deck, and someone asks why the average order value fell in March.
It did not. A source system started sending nulls for a discount field, those nulls were treated as zero, and the average was computed over rows that should have been excluded. Profiling the data first would have shown it in ten seconds.
Profile before you analyse, not after
Analysis makes assumptions about data: that a column is populated, that a date is a date, that amounts are positive, that a category field has the categories you expect.
Every one of those is frequently untrue, and none of them announce themselves. The analysis runs, produces a number, and the number is wrong in a way that looks plausible.
A profile is the cheapest possible check. It takes seconds and it surfaces the assumptions that do not hold before they become conclusions.
What to read, in order
Null rate per column. The single most informative measure. A column that is 11% null when you expected 0% has a problem upstream, and any average computed over it is wrong unless nulls were handled deliberately.
Distinct value count. A column with one distinct value carries no information and usually means a filter was applied or a field stopped populating. A category column with far more distinct values than expected usually means inconsistent entry — “UK”, “uk”, “United Kingdom” and “U.K.” as four categories.
Type detection. A numeric column detected as text has at least one non-numeric value. That value is also the reason downstream arithmetic will fail.
Range and extremes. Minimum and maximum catch impossible values faster than anything else — negative quantities, dates in 1900, ages of 200.
Outliers. The interesting rows, which need looking at rather than removing.
| Finding | Usually means |
|---|---|
| Null rate jumped | Broken join or renamed field |
| One distinct value | Field stopped populating |
| Cardinality too high | Inconsistent entry — case, spelling |
| Negative where impossible | Data error or sign convention |
Outliers are findings, not noise
The instinct on seeing outliers is to remove them so they do not distort the analysis. Sometimes that is right; often it discards the most informative rows in the dataset.
An outlier is one of three things:
A data error — a misplaced decimal, a unit mix-up, a test record left in production. Worth fixing at source, and worth knowing about because there are usually more.
A genuine extreme — a real order that really was that large. Removing it makes the analysis wrong.
A different population — rows that should not be in this dataset at all. A B2B order in a consumer dataset is not an outlier, it is a filtering problem.
Only the first is safely removable, and distinguishing them requires looking at the rows rather than the statistics.
Check the joins before the columns
Profiling a single table misses the most common source of wrong analysis.
Most analysis joins tables, and a join that does not match cleanly produces problems no single-table profile reveals:
Unmatched rows dropped by an inner join, silently reducing the population.
Duplicate keys on one side, multiplying rows and inflating every sum.
Type mismatches — an id stored as text on one side and a number on the other, matching nothing.
Whitespace or case differences in key columns, which look identical and do not match.
Counting rows before and after every join, and comparing against what you expected, catches all of these in seconds. A join that changes the row count unexpectedly is the finding.
Common mistakes to avoid
- Analysing first and profiling only when something looks odd.
- Removing outliers before understanding them.
- Ignoring a column with one distinct value because it has no variance to affect anything — it is a signal that something upstream changed.
- Treating a high null rate as a data quality footnote rather than as a reason to stop.
- Uploading a dataset containing personal data to a hosted profiling service.
How to do it with Data Profiling Report Generator
The Data Profiling Report Generator profiles in the browser.
- Load the dataset before doing anything else with it.
- Read null rates and cardinality first.
- Check ranges for impossible values.
- Look at the outlier rows individually rather than deciding from the count.
Other data tools that keep files local are in the tools directory.
Frequently asked questions
Why profile before analysing?
Because analysis assumes things about the data that are frequently untrue — that a column is populated, that a date parses, that amounts are positive. Profiling surfaces those before they become conclusions.
What does a constant column mean?
Every row has the same value, which usually means a field stopped being populated or a filter was applied upstream. It carries no information and it is a signal that something changed.
Should outliers be removed?
Not automatically. Some are errors, some are the most interesting rows in the dataset, and some indicate a filtering problem. Looking at the rows is what distinguishes them.
Final thought
Read the null rates before the averages. An average over a column that is a tenth empty is a different number from the one you think you calculated.