Data profiling

In one line

Data profiling is looking at the data before you use it: what's actually in each column, how often, in what range, with what gaps. It is the least glamorous hour of any project and the one that most reliably prevents a confident wrong answer.

Why it's skipped, and what it costs

Everyone skips it because the data dictionary exists. The dictionary says gender is M/F. So you write your analysis against M/F.

Profile the column and you find: M, F, m, f, Male, MALE, 1, 2, U, O, NULL, and 4,000 empty strings. The dictionary described the intent. The database holds the history — every import, every migration, every system that fed it since 2009.

The data dictionary is a claim. The profile is evidence. They routinely disagree, and the disagreement is where your bug lives.

What to actually look at

For every column, mechanically:

• Row count and distinct count. A "unique" patient id with fewer distinct values than rows tells you something important in one line.
• Null rate. And then: is it null, empty string, 'NULL' the literal text, 'N/A', or a space? Those are four different nulls and they'll each break something different.
• Min, max, mean. A weight of 0 kg. An age of 999. A future date of birth.
• Value frequency, top 20. Where you find the M/Male/1 problem, and the placeholder.
• Length distribution for identifiers. A column of 6-digit MRNs with a few 5-digit ones means leading zeros were stripped somewhere upstream.
• Format patterns. Dates as DD/MM/YYYY and MM/DD/YYYY in one column is real and it's unrecoverable for days 1–12.

The health-specific tells

Things that mean something specific in clinical data:

• 1900-01-01 or 01-01 birthdays. Placeholders typed to escape a mandatory field. Cluster on January 1st and you've found a registration workflow, not a cohort.
• Round numbers. Weights at 70, heights at 170, BPs at 120/80. Humans estimate; devices don't. A spike at round values is a spike of guesses.
• A single value dominating. 60% of records with the same referring physician usually means a default that nobody changes.
• Time-of-day clustering. Vitals all recorded at 14:00 means batch charting from memory, not observation.
• Impossible sequences. Discharge before admission. Medication before birth. Death date followed by encounters.
• Bimodal distributions — usually two source systems merged, with different conventions, never reconciled.

Each of these is a workflow discovered through data. That's the real gift of profiling: it tells you how the hospital actually works, as opposed to how the process document says it does.

Tools

WhiteRabbit (OHDSI) is the health-specific one: it scans a source database and produces a profile report designed for exactly this — and its companion Rabbit-in-a-Hat turns that into an ETL design. If you're heading toward OMOP, it's the standard first step, and the WhiteRabbit entry covers the pairing.

Otherwise: SQL will do it. GROUP BY and COUNT answer most of the questions above, and pandas.describe() plus value_counts() answers the rest. The tool matters far less than the habit.

The rule

Never write the analysis before you've read the data. And when you profile, write down what you found — because the next person will otherwise assume the dictionary too, and rediscover the same thing at the same cost.