Profiling a new data set before trusting it

Why this matters

The most expensive mistakes in analysis are made in the first ten minutes, by assuming a data set is what its name says it is. A file called "all customers" may be filtered. A column called "amount" may be in two currencies. A key called "customer_id" may be unique and still hide the same customer twice.

Data profiling is the systematic first look that finds these things before they reach a result. The exam treats exploration as a distinct step with its own techniques, and profiling is where it starts. This lesson walks through a profile of the course's raw customer file, customers_raw.csv, which turns up most of the problems profiling exists to catch.

The lesson

Row counts, column counts and what the data claims to be

Start with the shape: how many rows and columns, and does that match what the data is supposed to contain?

The raw customer file has 314 rows and 8 columns. That number means nothing until it is compared with something. If the business says it has about 300 customers, 314 is a question: are 14 of these rows duplicates, test records, or genuinely new customers? If it had said 3,000, the file would be a partial extract and every result drawn from it would be wrong.

The same first look checks the header: are the column names what you expected, are any blank or duplicated, and does each one mean what its name suggests? And it checks the period: if the data should cover a year, do its dates actually span that year, or does it stop in September?

Write the expected answers down before looking. A profile that confirms an expectation is quick. A profile compared with no expectation cannot surprise you, and surprise is the point.

Data types as loaded against data types as meant

Next, compare how each column was loaded with what it means.

Tools infer types when reading a file, and they infer from what they see. In the raw customer file, signup_date holds dates in two formats, ISO (2024-03-15) and day-first (15/03/2024). No single date format fits both, so most tools load the column as text. Everything then goes wrong quietly: sorting the text puts 04/02/2022 first and 31/01/2023 last, while the real signup dates run from January 2022 to October 2025.

The checks are simple: for each column, what type was it loaded as, what type should it be, and how many values fail to convert to that type? A numeric column loaded as text usually means a stray character somewhere, such as a currency symbol, a comma used as a thousands separator, or the word "none". An identifier loaded as a number usually means lost leading zeros. The count of values that fail conversion is the size of the problem.

Value distributions, ranges and unexpected categories

For each column, look at what values it actually contains.

For numeric columns, find the minimum, maximum, mean, median and count of missing values. Impossible values stand out immediately: a negative quantity, an age of 212, a date in the future. The practice order file has three negative quantities, which a single MIN(quantity) reveals.

For categorical columns, count the distinct values and their frequencies. This is where inconsistency shows. The raw customer file's region column should have four values. It has sixteen: East, South, North and West, plus twelve variants such as WEST, east, N. and Sth. Each variant is a separate category to any tool that groups by region, so a regional report built on this file would show sixteen regions with the small ones missing from the totals of the real four.

For text columns, look at lengths, leading and trailing spaces, and a sample of values. Twenty-five names in the raw file carry stray spaces that make "Ada" and " Ada" different values.

A frequency table or a quick histogram of every column is one of the most productive things an analyst can do with a new data set.

Keys: are they unique, and are they complete

Keys deserve their own check, because joins, counts and deduplication all rely on them.

Two questions matter. Is the key unique: does each value appear once? And is it complete: is it ever blank? In the raw customer file, customer_id passes both tests: 314 rows, 314 distinct identifiers, none missing.

It is still not a trustworthy key for "one row per customer", because the same person has been entered twice under two identifiers. Fourteen customers appear twice, their second record differing only in the capitalisation of the surname and email. A uniqueness check on customer_id cannot see that. A check on a natural key, the real-world attributes that identify a person, can: comparing emails case-insensitively finds all fourteen pairs.

This is the general lesson of key profiling: a technical key tells you the rows are distinct records, not that they are distinct things.

Writing the profile down so the next person need not repeat it

A profile is only useful if it outlives the moment. Record it, briefly:

• the source, file name and date received;
• row and column counts, and the expected counts they were compared with;
• each column's intended type, the number of values that failed conversion, and the number missing;
• ranges for numeric and date columns, and distinct values for categories;
• key checks, with any duplicates found;
• every problem noticed, with a count.

For the raw customer file, that record would say, among other things: 314 rows for 300 real customers, 14 near-duplicate pairs, 16 region spellings for 4 regions, 20 missing emails, 30 dates in day-first format and 25 padded names. Each of those numbers becomes a test for the cleaning that follows: after cleaning, there should be 300 customers, 4 regions and 0 padded names, and if there are not, the cleaning is not finished.

Profiling can be done by hand with queries, with a few lines of code, or with a profiling tool that produces the whole report automatically. The method matters less than doing it every time, before any analysis, and keeping the result.