Data profiling a registration file before you trust a single number

An analyst joined a show team in October and was handed the registration export with a request for a demographic breakdown by Friday. She built it. The job function chart had a bar labelled Other holding 61 per cent of the audience, and by the time anyone asked why, the chart was in the exhibitor prospectus.

Data profiling a registration file is the step that would have caught it in twenty minutes. It is a one-off pass that describes what is actually in every column, run before anybody builds anything and read by a person rather than by a monitor.

Most teams skip it because it feels like preamble. It is the cheapest hour in the whole exercise.

What a profile actually is

Jack Olson wrote the standard treatment in 2003, Data Quality: The Accuracy Dimension, published by Morgan Kaufmann. The description on it is unusually direct about method: the book is about assessing the quality of corporate data and improving its accuracy using the data profiling method, written by one of the original developers of data profiling technology.

The idea is older than any tool. Before you can say whether data is right, you have to say what it contains, and the contents of a production table are almost never what the schema and the field labels imply. A column called country holds country names, country codes, a few region names, several hundred blanks, and one value that is a phone number because somebody's form tabbed wrong.

A profile only describes. It produces no pass, no fail and no threshold. What it produces is a page per column that somebody reads and reacts to, and the reactions become the rules you monitor later.

The distinction from monitoring is worth holding onto, because tooling blurs it. Monitoring answers whether the file changed since last time. Profiling answers what the file contains, which is a question you can only ask usefully when you have no priors, and every organiser inheriting a registration platform from a predecessor is in exactly that position.

Five things to compute for every column

This is the whole method, and it fits in one query per column on any database you already have.

Row count. The table total, and the count per event edition if the file holds several. This is the number everything else is a proportion of, and it is the number most likely to be quietly wrong because of a join that fanned out.

Null rate. The share of rows where the column has no value. Compute it as a proportion, not a count, so you can compare a 41 per cent phone gap against a 3 per cent country gap without arithmetic.

Distinct count. How many different values appear. On a key column this should equal the row count. On a categorical column it should be small. On a free text column it will be enormous, and the ratio of distinct values to rows is the fastest signal you have.

Minimum and maximum. On dates and numbers this catches the impossible values in one look. A created timestamp with a minimum in 1900 means a default is leaking through. A maximum eighteen months in the future means somebody typed a year wrong.

The twenty most frequent values, with their counts. This is the one that changes plans, and it is the one most profiling scripts omit because it does not fit in a summary row.

What does the top twenty actually tell you?

It tells you whether a field can be grouped by.

Take a file of 38,900 registrations. The country column holds 96 distinct values, and the twenty most frequent of them cover 37,300 rows, which is 95.9 per cent of the file. That is a categorical field. A chart grouped by country will show real structure, the long tail is 1,600 rows across 76 values, and a rule that country must come from a fixed list is enforceable.

The job title column in the same file holds 11,842 distinct values. The twenty most frequent cover 4,110 rows, which is 10.6 per cent of the file. Nothing can be grouped by that column. Any chart built on it will bucket 89.4 per cent of the audience into whatever the fallback is, which is how you get a bar labelled Other holding most of the show.

The coverage figure is what converts a vague feeling that job titles are messy into a decision. Below about a fifth, the field needs a normalisation pass before it can carry a report, and normalising free text titles in three passes is a project with a scope you can now estimate: 11,842 strings, a synonym map, and a taxonomy to land on.

Reading the actual twenty is worth another five minutes. On job title you will see the top entries are usually Owner, Manager, President, Sales Manager, and one or two that are not job titles at all, such as a company name or the word Yes, which tells you the form has a field ordering problem that will keep producing bad rows until somebody fixes it.

Run the same coverage figure on every categorical column and the file sorts itself into three groups. Fields above about 90 per cent coverage are safe to report on today. Fields between a fifth and 90 per cent are usually a controlled list with a free text escape hatch, and the work there is small: map the tail onto the list and close the hatch. Fields under a fifth need a taxonomy and a mapping project before any chart built on them means anything.

The distinct count on its own is a weaker signal than coverage, which is why I would not rank fields by it. A country column with 96 distinct values and a job function column with 96 distinct values look identical on that statistic and behave completely differently, because one has a head and the other does not.

Turning a profile into rules

Once the profile is read, the reactions become checks that run on every load, and the vocabulary for those checks is already standard.

The open source expectation library maintained by the Great Expectations project names them almost exactly as a profile produces them. ExpectTableRowCountToBeBetween covers the row count. ExpectColumnValuesToNotBeNull and ExpectColumnProportionOfNonNullValuesToBeBetween cover the null rate, the second being the one you want on a field that is allowed to be partly empty. ExpectColumnUniqueValueCountToBeBetween and ExpectColumnProportionOfUniqueValuesToBeBetween cover distinct counts, and ExpectCompoundColumnsToBeUnique covers the case where uniqueness lives across two columns rather than one. Membership of a value domain is expect_column_distinct_values_to_be_in_set.

The order matters more than the tooling. A monitor written before the profile encodes what you assumed the data contained. A monitor written after the profile encodes what it actually contains, and the difference shows up as a check that either never fires or fires on every load and gets switched off within a month.

The thresholds also come from the profile. A rule that country must be non-null on 100 per cent of rows will fail forever on a file that has never been better than 94 per cent. A rule set at 93 per cent, with a note saying the profile found 93.9, is a rule that fires when something changes, which is the only thing a monitor is good for. The per column mechanics of that number, including how to stop placeholder values passing a not-null test, are in the completeness rate you compute per field.

Which quality dimension does each statistic serve?

The mapping is close to one to one, which is why the profile is a sensible first step rather than a separate exercise.

Null rate is completeness. Distinct count against row count on a key column is uniqueness. Membership of a value list, and minimum and maximum on a numeric or date column, are validity. A cross-column rule, such as postcode format against country, is consistency. The maximum of a created timestamp compared against the show close date is timeliness.

Accuracy has no profile statistic, and it never will, because accuracy needs a comparison against the world outside the file. The six dimension names come from a DAMA UK working group paper of October 2013, adopted by the UK Government Data Quality Hub in its framework of December 2020, and binding each of them to a named field is the step that turns a profile into something you can report.

One consequence of that mapping is worth stating. If the profile is your input to the dimension scores, then the dimension scores can never be more current than the profile, and a profile run once in 2024 is describing a form that has since changed twice. Re-profile whenever the registration form changes, whenever a new source starts feeding the file, and once a year regardless.

Where this stops

A profile describes one file at one moment. It says nothing about whether the file is the right file, and on event data that is a real gap. An export missing every onsite registration will profile beautifully, because every row in it is complete, valid and unique. Profiling has no way to see the rows that are not there.

It also treats every column as independent. The five statistics per column will never tell you that job title and company are systematically blank together on rows from one acquisition channel, and that pattern is usually the most useful thing in the file, because it points at a source rather than at a field. Cross-column and cross-source patterns need a second pass that groups by source and recomputes the same statistics per group, which doubles the work and is worth it on any file with more than two upstream systems feeding it.

The last limit is human. A profile of a registration file with sixty columns produces sixty pages, and nobody reads sixty pages. Cut it to the columns that appear in a published report, plus every key, and let the rest wait until somebody asks. A profile that gets read on twelve columns beats a complete one that gets filed.

Pick the categorical field in your registration file that you are least sure about, usually job title or industry, and run one query: count of rows, count of distinct values, and the twenty most frequent values with their counts. Divide the top twenty's total by the row count. That single fraction tells you whether the next report grouped by that field is worth building or needs a governed record underneath it first.