What Is Data Profiling?
Data profiling is the process of examining a dataset to understand its content, structure, and quality characteristics. It answers the question: "What is actually in this data?" before you make assumptions about what should be there.
Profiling is exploratory — it's how you discover the problems you didn't know to look for.
The Six Profiling Activities
1. Structure analysis How many rows and columns? What are the data types of each column? Are there any unexpected columns or missing expected columns?
2. Content analysis For each column: what are the values? What's the range (min, max)? What's the distribution? For text columns: what are the most common values? Any patterns?
3. Completeness measurement For each column: what % of values are non-null? What % are non-empty strings (a non-null empty string is effectively a missing value)? Is completeness consistent across the dataset or clustered in specific rows?
Sohovi profiles every column in your dataset for completeness and flags the exact rows where values are missing — free to try.
4. Uniqueness measurement For the primary key column: are there duplicates? For other key columns: how many distinct values? What % of the column is unique?
5. Pattern and format analysis For text columns: do values follow consistent patterns? Phone numbers — consistent format? Dates — consistent format? Email addresses — consistent format?
6. Relationship analysis For datasets with relationships: do foreign key values match primary key values in the related table? Are there orphaned records?
Profiling Tools
Python pandas:
- df.info() — column types and null counts
- df.describe() — statistics for numeric columns
- df.nunique() — distinct counts per column
- df.value_counts() — frequency distribution for a column
SQL:
- SELECT COUNT(*), COUNT(column_name), COUNT(DISTINCT column_name) FROM table — completeness and uniqueness
- SELECT MIN(column_name), MAX(column_name), AVG(column_name) — range analysis
- SELECT column_name, COUNT() FROM table GROUP BY column_name ORDER BY COUNT() DESC — distribution
OpenRefine: GUI tool for profiling and cleaning, no coding required
Soda.io, Great Expectations, dbt tests: Framework-based profiling for automated, repeatable assessment
What Profiling Tells You (and Doesn't)
Profiling reveals: structural problems, completeness, uniqueness, format inconsistencies, and distribution anomalies.
Profiling cannot reveal: accuracy (whether values correctly represent reality). That requires external verification.
Start with profiling. Use it to identify where to apply more expensive accuracy checks.
