It's common to walk into a data quality project wanting to write rules right away: "email shouldn't be empty," "the date has to be valid," "the ID has to be unique." The problem is those rules are based on assumptions about what the data looks like, not on what it actually looks like. Data profiling is the step that closes that gap: it's the systematic analysis of a dataset to understand its structure, content and relationships before deciding which rules actually make sense.
What data profiling actually is
Data profiling is the process of examining the data available in a source and gathering statistics and information about it: actual data types (not the ones declared in the schema), value ranges, percentage of nulls, unique values, format patterns, and relationships between columns or tables. It doesn't fix anything or impose rules; it simply documents how the data is today, defects included.
The three types of profiling
1. Structure discovery
Analyzes whether the data follows the expected format and type: how many records in the "date_of_birth" column are actually parseable dates? How many "zip_code" values have the correct length? This is where the first signs of structural inconsistency show up.
2. Content discovery
Examines the quality of individual values: nulls, duplicates, outliers, out-of-range values (an age of 340, a negative amount in a column that shouldn't have one). This is usually where you find the most obvious defects.
3. Relationship discovery
Identifies how tables connect to each other: implicit, undocumented foreign keys, functional dependencies, redundancies between columns that should share the same source of truth and don't.
Common techniques and metrics
| Technique | What it answers |
|---|---|
| Null analysis per column | What percentage of this column is empty? Is it systematic (always the same source) or scattered? |
| Cardinality and uniqueness | How many distinct values are there? Does it match what should be a unique key? |
| Value distribution | Is there an abnormal concentration in one value (e.g. 80% of orders with "country = Spain" when the business is international)? |
| Pattern detection | Does the phone, email or ID format follow a consistent pattern, or are multiple formats mixed together? |
| Cross-column analysis | Is the end date always after the start date? Is the amount including tax consistent with the base amount? |
Tools to do it
You don't always need a dedicated platform. For moderate volumes, libraries like ydata-profiling (formerly pandas-profiling) or great_expectations in exploratory mode generate a full report in minutes. For production-scale volumes, tools like Soda, Monte Carlo, or the profiling modules of catalogs like Collibra automate the process and keep it current as the data changes. Check the comparison of data quality tools if you need to pick one.
From profiling to rules: an example
Say you profile a customer table and find that the "email" column has 3% nulls, that 0.4% of values don't have a valid email format, and that there are 120 duplicate records with the same email but a different customer ID. That finding — not a generic assumption — is what lets you set reasonable thresholds: maybe the business accepts up to 2% nulls in email because it's not required at signup, but zero tolerance for duplicates because it affects billing. Without prior profiling, you'd have set an arbitrary threshold that's either too loose or triggers constant alerts nobody acts on.
Common mistakes
- Profiling once and never again: data changes with every new source or integration. Profiling should be repeated whenever the schema changes or a new source is added, not treated as a one-time exercise.
- Profiling only technical columns, not business ones: the fields that matter most for decisions (amounts, due dates, statuses) usually get less attention than technical IDs.
- Confusing profiling with cleaning: profiling documents, it doesn't fix. The fix comes later, in the data cleansing process.
If you've already defined your quality dimensions and now need to turn them into concrete thresholds, profiling is exactly the bridge you're missing before writing your first rule.