Data profiling is the first thing to run on a table you didn’t build. It answers “what’s actually in here?” with numbers instead of assumptions.
This is the same definition DataKitchen uses in its open-source data profiling landscape, which compares the tools in depth.
What a profile measures
| Measure | What it tells you |
|---|---|
| Row count | How big the table is, and whether it changed |
| Null and blank share | Which columns are really optional |
| Distinct count | Whether a column could be a key, or is a short code list |
| Minimum and maximum | Impossible values: negative quantities, dates in 1900 or 2099 |
| Length range | Truncation, or text where a code was expected |
| Patterns | Formats such as AAA-9999, and the rows that break them |
| Frequent values | Placeholders such as N/A, UNKNOWN, or 0 |
| Inferred type | Dates or numbers stored as text |
These fall into three kinds of discovery: structure (format and consistency), content (errors in individual records), and relationships (how tables and columns connect).
A SQL Server example
Here is a hand-written profile of one column, Email, in a dbo.Customer table:
SELECT
COUNT(*) AS row_count,
COUNT(*) - COUNT(Email) AS null_count,
SUM(CASE WHEN LTRIM(RTRIM(Email)) = '' THEN 1 ELSE 0 END) AS blank_count,
COUNT(DISTINCT Email) AS distinct_count,
MIN(LEN(Email)) AS min_length,
MAX(LEN(Email)) AS max_length,
SUM(CASE WHEN Email NOT LIKE '%_@_%._%' THEN 1 ELSE 0 END) AS bad_format_count
FROM dbo.Customer;
SELECT TOP (10) Email, COUNT(*) AS occurrences
FROM dbo.Customer
GROUP BY Email
ORDER BY occurrences DESC;The first query gives the shape of the column. The second shows the most frequent values, which is where none@none.com and test@test.com turn up. Two habits of SQL Server to keep in mind: LEN ignores trailing spaces, and a case-insensitive collation counts Bob@x.com and bob@x.com as one distinct value.
That’s one column. A database with 300 tables and 5,000 columns needs this for every one, which is why data profiling is usually done by a tool.
From a profile to tests
A profile is a snapshot. Its value comes from what you do next:
- Fix hygiene problems the profile exposes: placeholders, mixed types, leading spaces.
- Turn observations into tests. A column that has never been null should stay that way. A status column with five codes should keep to those five.
- Add what the data can’t tell you. A profile learns what the data is, not what it should be. “Ship date is on or after order date” comes from a person.
DataOps TestGen does the first two automatically. It runs data profiling as SQL inside SQL Server, Azure SQL, Synapse, Fabric, and other databases, measures 55 characteristics per column, flags 32 kinds of hygiene issue, and generates tests from 34 test types. On SQL Server, Azure SQL, and Synapse, its sample-based data profiling uses the database’s own sampling for large tables, per TestGen’s 2025 release notes.
Open source is free for one user, one connection, and one project. Start at the data profiling install page, or see the open-source data profiling tools compared.