Data profiling

What is data profiling?

Data profiling is the systematic inspection of a dataset before you build a report, migration, model, or pipeline on top of it. You do not look at one interesting row. You look at the table as a whole: which values occur, how often they occur, which fields are empty, whether dates and numbers use the expected format, and whether the supposed key is really unique.

Think of it as a survey before buying a house. You check the roof, the wiring, and the damp spots before signing, not after moving in. Profiling does the same for a table. It gives you a column-by-column picture of what is usable, what is suspicious, and what needs cleanup first.

The output is a data profile: counts, distributions, examples, warnings, and sometimes correlations. The profile does not fix the data. It tells you where to look.

What do you measure?

Most profiling work comes down to a small set of practical measurements.

• Value distribution
  Which values occur, and how often? A country column with Belgium, Belgie, BE, B, and Belgique is already telling you how messy reporting by country will be.

• Missing and invalid fields
  How much of the column is blank, null, malformed, or stored as an error? A VAT number missing for thirty percent of customers is better found before invoicing or migration.

• Distinct and unique values
  Distinct counts how many different values exist. Unique counts values that occur exactly once. On a key column, the difference between row count and distinct count exposes duplicates.

• Minimum, maximum, and range
  Low and high values catch obvious problems: birth dates in the future, negative prices, a discount above 100 percent, or a year written as 0204 instead of 2024.

• Patterns and formats
  Do values follow the expected pattern? Email addresses need an at sign. Belgian postal codes have four digits. Dates should not be a mix of text, Excel serial numbers, and local formats.

When do you profile data?

You profile data whenever you are about to rely on a source you do not yet know well.

For a new CRM, ERP, accounting package, webshop, or API, profiling shows how the source is actually filled in, which is often more useful than what the documentation promises. For a migration, it tells you in advance which records will fail the new validation rules. For a data model or dashboard, it tells you whether the keys and grouping fields are trustworthy enough to use.

A report built on unprofiled data can look polished and still count the wrong thing. The profile is the cheap check before that happens.

A messy customer table

Imagine an export of 8,000 customers from an old CRM. Before building a customer dashboard, you run a profiling pass.

The country column has fourteen distinct values, even though almost all customers are Belgian. The email column is empty for eighteen percent of rows, and a few filled-in addresses miss the at sign. The birth date column runs from 1900 to 2064. The customer_id column has 8,000 rows but only 7,850 distinct values, so 150 customers are duplicated.

In half an hour you know which cleanup work comes first. Without that pass, you would probably discover the problem later, when the dashboard count does not match anyone's expectation.

Profiling versus data quality

Profiling and data quality belong together, but they are not the same thing. Profiling measures the current state. Data quality is the work of defining rules, improving the data, and keeping it good.

The order matters. First profile the table. Then write rules based on what you learned. If the customer_id profile shows duplicates, the follow-up rule is simple: customer_id must be unique. That rule can then become a data test that runs every time new data is loaded.

Profiling is often a snapshot before or during discovery. Data testing is the repeatable control. Data observability watches production data continuously and looks for freshness, volume, schema, distribution, and lineage problems that may not be covered by explicit tests.

For duplicates, deduplication is the cleanup discipline. For core records such as customers, suppliers, and products, master data management may be the longer-term answer. Profiling is usually the first measurement that shows how large the problem is.

Tools you may meet

Power Query
Power Query in Power BI and Excel has built-in profiling features: Column quality, Column distribution, and Column profile. They show valid, error, and empty percentages, plus distinct and unique counts. One practical trap: Power Query may profile only the first 1,000 rows unless you switch the setting to use the full dataset.

Python profiling tools
The Python package many teams knew as pandas-profiling later became ydata-profiling. In 2026 the maintained package name moved again to fg-data-profiling. You may see all three names in older projects. The idea is the same: generate an HTML report with column statistics, missing values, distributions, warnings, and examples from a dataframe.

Great Expectations, GX, and Soda
These tools are more often used for ongoing validation than one-off profiling. Great Expectations, now often branded as GX Core, defines expectations and validates data batches against them. Soda uses SodaCL checks for missing values, duplicates, row counts, freshness, schema, and custom SQL. Both can turn profiling insights into controls that run in the pipeline.

What to watch out for

A sample can lie
Profiling the first 1,000 rows is useful for exploration, but it can miss rare errors. Use the full dataset when the result will drive a migration, contract, or production rule.

Outliers are not always errors
A very large order may be a typo, but it may also be a real enterprise deal. Profiling tells you what is unusual. A domain expert decides what is wrong.

Do not stop at the report
A beautiful profile that nobody acts on is shelfware. Convert the findings into cleanup work, data tests, ownership, and better source-system rules.

Profile again after cleanup
The second profile is how you prove that the cleanup worked. It also catches new problems introduced by the fix.