Data profiling is the systematic examination of a dataset before relying on it. It measures what is present, what is absent, how values are distributed, which patterns repeat, which constraints appear to hold, where anomalies cluster and how the observed data compares with the schema or business assumptions that are supposed to describe it.
Before data can be cleaned, modelled or trusted, somebody has to ask what is actually there.
Profiling sits between acquisition and intervention. It does not fix the data. It produces evidence about the data so later work can distinguish an actual defect from an unusual but legitimate value, a schema mismatch from a business exception, or a missing field from a field that was never meant to exist.
ARTICLE ID: DATA.MANAGEMENT.053
Canonical function: structured discovery and diagnostic assessment of datasets before transformation or publication
Owner boundary: this article owns profiling and exploratory assessment. Data Quality owns ongoing fitness criteria; Data Observability and Monitoring owns continuous production monitoring; Data Cleaning and Standardisation owns remediation after evidence is established.
The Simple Answer
A trustworthy profiling route is:
Identify Dataset → Read Schema → Measure Shape → Profile Fields → Profile Relationships → Find Exceptions → Compare with Expectations → Investigate Causes → Record Evidence → Decide What Needs Repair
The key discipline is descriptive before corrective. Profiling should reveal the estate before it reshapes it.
Start with Dataset Identity
Every profile should identify the exact dataset version being examined.
- dataset name;
- owner;
- source;
- version or snapshot;
- extraction time;
- row count;
- column count;
- partition scope;
- intended grain;
- intended receiver.
Without a stable input identity, two profiling reports can appear to disagree while examining different snapshots.
Shape
Dataset shape begins with simple questions:
- How many records exist?
- How many fields?
- How are records partitioned?
- How many distinct entities?
- What time period is covered?
- Which sources contributed?
Unexpected changes in shape often reveal ingestion, filtering or duplication problems before deeper analysis begins.
Schema Discovery
Schema discovery compares what the system says should exist with what values actually appear.
- declared type vs observed type;
- required vs actually populated;
- declared maximum length vs observed length;
- allowed categories vs observed categories;
- declared precision vs actual values;
- expected nested structure vs actual shape.
A declared integer column containing strings is a schema violation. A declared string column containing only integers may be technically valid but still worth investigation.
Cardinality
Cardinality is the number of distinct values in a field.
Cardinality helps identify possible keys, categorical fields, identifiers and suspiciously low-variation columns.
- row count = 1,000,000 and distinct customer_id = 1,000,000 may indicate uniqueness;
- distinct gender_code = 3 suggests a controlled category;
- distinct status = 127 may suggest inconsistent free-text coding;
- distinct country = 1 in a supposedly global dataset may reveal a scope issue.
Uniqueness
Uniqueness tests determine whether a field or field combination identifies records without duplication.
Candidate keys should be tested for:
- duplicate values;
- nulls;
- reuse through time;
- source-system resets;
- format changes;
- scope boundaries.
A field that is unique in one snapshot may not be globally unique across history.
Null Profiles
Measure missingness by field, source, time, subgroup and record type.
A 2% null rate can be harmless if the field is optional and dangerous if the missing records all come from one branch or one software version.
See Missing Data and Imputation.
Zero, Blank and Sentinel Values
Profiling should search for values used as unofficial missing markers:
- 0;
- -1;
- 999;
- blank strings;
- “N/A”;
- “UNKNOWN”;
- “0000-00-00”;
- placeholder dates.
These values can pass schema checks while distorting downstream calculations.
Frequency Distributions
Frequency counts show how often categories or values occur.
They reveal:
- dominant categories;
- rare values;
- unexpected codes;
- spelling variants;
- default-value concentration;
- class imbalance;
- possible instrumentation bugs.
Numeric Distributions
For numerical fields, profile more than the mean.
- minimum;
- maximum;
- mean;
- median;
- standard deviation;
- selected quantiles;
- zero frequency;
- negative-value frequency;
- extreme-tail counts.
Distributions expose impossible values and legitimate skew that a single average can hide.
Outliers
An outlier is an observation unusually far from a reference distribution or pattern. It can represent:
- a data-entry error;
- a unit mismatch;
- a sensor malfunction;
- a genuinely rare event;
- a new business regime;
- fraud or abuse;
- a population not represented by the dominant pattern.
Profiling should flag outliers for investigation, not erase them automatically.
Range Checks
Range profiling compares observed values with plausible or permitted limits.
Some limits are hard domain constraints; others are soft expectations. Age = -3 is impossible. Age = 112 is unusual but possible. The response should differ.
Pattern Profiling
Text and identifier fields often follow recurring patterns.
- postal-code formats;
- email shapes;
- telephone formats;
- prefixes;
- identifier lengths;
- date formats;
- URL patterns.
Pattern counts reveal format drift and mixed source conventions.
Length Profiling
String lengths can reveal truncation, padded fields, identifiers entered into the wrong column and schema constraints that no longer match reality.
Whitespace and Case
Leading spaces, trailing spaces and inconsistent case create false distinct values and matching failures.
Profiling should measure them before standardising so the organisation knows which sources produce the issue.
Date Profiling
Profile date and timestamp fields for:
- minimum and maximum;
- future dates;
- impossible dates;
- timezone mix;
- format variation;
- unexpected concentration;
- event-before-creation contradictions;
- late-arriving records.
Cross-Field Rules
Some defects are visible only across fields.
- end_date before start_date;
- status = CLOSED but closed_at is null;
- country = Singapore but postal code has an incompatible format;
- student_level = Primary 3 but age is outside plausible bounds;
- currency = SGD but amount field was converted using USD assumptions.
Referential Integrity
Profile relationships between tables or entities:
- orphan foreign keys;
- parent records with no children where children are expected;
- duplicate child assignments;
- many-to-many relationships where one-to-many was expected;
- reference codes absent from the controlled vocabulary.
Functional Dependencies
A functional dependency exists when one field determines another under the intended model. For example, postal code may usually determine a region.
Violations can reveal stale reference mappings, inconsistent source systems or an incorrect assumption about the domain.
Duplicate Profiles
Profile exact and near duplicates, especially for candidate keys and entity representations.
See Data Deduplication and Entity Resolution.
Distribution by Source
Never assume a combined dataset behaves uniformly. Profile fields by source system, branch, vendor, instrument or ingestion path.
A global average can hide one source producing nearly all defects.
Distribution by Time
Profile by day, week, month or schema version to find shifts.
A sudden change in null rate, category frequency or mean after a deployment can reveal a pipeline or instrumentation change.
Drift During Profiling
Profiling compares one dataset with expectations; drift analysis compares profiles across time.
Useful drift signals include:
- new categories;
- disappearing categories;
- mean or quantile shifts;
- null-rate shifts;
- cardinality changes;
- new pattern classes;
- new source contributions.
Profile Against Contracts
Data contracts provide explicit expectations about schema, quality, freshness and semantics. Profiling turns those expectations into evidence at intake or exploration time.
See Data Contracts and Data Products.
Profile Before Migration
Migration projects often discover that legacy systems violate their own documented schemas. Profiling should measure the real source state before mapping and cutover.
See Data Migration and Legacy Modernisation.
Profile Third-Party Data
Purchased or licensed data should be profiled independently rather than trusted solely because a vendor supplied documentation.
See Third-Party Data Acquisition and Licensing.
Profile Before AI Training
AI datasets should be profiled for duplicates, missingness, label distributions, source composition, class imbalance, temporal coverage and contamination indicators before model training begins.
See AI Data Management.
Sampling Profiles
Very large datasets may be profiled on samples for speed. The sample should be designed to preserve rare or high-risk cases where they matter.
A random sample that misses all rare errors can produce an unjustifiably clean profile.
Approximate Profiling
Approximate distinct counts, quantiles and sketches can profile large datasets efficiently.
Approximation is appropriate when error bounds are understood and exact counts are unnecessary. The profile should state when a measure is approximate.
Profile Privacy-Safely
Profiling can expose sensitive values, especially top values, rare categories and free-text examples.
Profile outputs should follow classification and minimise raw sensitive examples.
Do Not Put Sensitive Values into Debug Reports
A profiling report can become a secondary data leak if it prints full names, identifiers or free-text contents for anomalies.
Use masked examples or controlled drill-down where appropriate.
Profile Results Need Ownership
A profile that finds an issue should route it to an accountable owner.
- source owner;
- data steward;
- pipeline owner;
- vendor manager;
- application team;
- domain expert.
Discovery without ownership creates dashboards of defects rather than repairs.
Profile Versioning
Store profiling method and dataset version together. A change in profiling logic can change defect counts even when the underlying data did not change.
Baseline Profiles
A baseline profile records the known healthy state of a dataset. Future profiles can compare against it to detect drift and regressions.
Baselines should be reviewed when legitimate business changes occur so monitoring does not treat expected evolution as permanent failure.
Profiling vs Monitoring
Profiling is usually deeper and exploratory. Monitoring is narrower and continuous.
Profiling discovers which checks matter; observability operationalises the most important checks in production.
Profiling vs Cleaning
Profiling asks “what is here?” Cleaning asks “what should be changed?” Mixing them too early makes it difficult to distinguish raw evidence from corrected output.
Education Example
An education system receives assessment exports from several schools. Profiling shows one school encodes absent students as 0, another leaves scores null, and a third uses “ABS”. The apparent performance difference is partly a representation difference.
The profile identifies the source-specific pattern before any averages are calculated or values are standardised.
Commerce Example
A retailer profiles product prices and finds most values between $1 and $2,000, with a few at $999,999. Investigation reveals the extreme value is a sentinel for “price on request”, not a real price. Profiling prevented the value from contaminating revenue analytics.
AI Example
A training corpus appears balanced by source count, but profiling shows one source contributes 70% of total tokens because its documents are much longer. Source representation should therefore be evaluated by the unit that matters to training, not just file count.
Common Failure Modes
- Schema equals reality: declared structure is never compared with actual values.
- Mean-only profiling: tails, nulls and categories disappear.
- Global profile hides local defect: one source produces nearly all bad data.
- Outlier equals error: rare legitimate cases are deleted.
- Null rate without reason: structural absence and collection failure are conflated.
- One snapshot forever: drift goes unnoticed.
- Profile then immediately mutate: raw evidence is lost.
- Sample misses rare defects: huge datasets look cleaner than they are.
- Profiling report leaks sensitive data: diagnostics create a privacy problem.
- No owner: discovered problems accumulate without remediation.
A Data Profiling Checklist
- Which exact dataset version is being profiled?
- What is the intended grain?
- What are row, column and entity counts?
- Which fields are unique or near-unique?
- What are null and sentinel-value rates?
- What distributions and quantiles matter?
- Which categories are unexpected or rare?
- Which text or identifier patterns appear?
- Which cross-field rules are violated?
- Which references are orphaned?
- Which source systems behave differently?
- How does the profile differ across time or versions?
- Which findings require investigation rather than automatic correction?
- Who owns each material issue?
- Can the profile be reproduced from the same dataset and profiling version?
A Maturity Ladder
- Counted: basic row and field counts are known.
- Profiled: nulls, cardinality and distributions are measured.
- Relational: cross-field and cross-table constraints are tested.
- Source-aware: profiles separate vendors, systems and partitions.
- Temporal: profiles compare across time and schema versions.
- Baseline-driven: healthy reference profiles support drift detection.
- Governed: findings route to owners and protect sensitive data.
- Adaptive: profiling evidence continuously improves contracts, cleaning and monitoring.
The Deeper Principle: Inspection Before Intervention
Data work becomes fragile when transformation begins before anyone understands the input. Profiling creates a disciplined inspection layer: it records the shape, exceptions, patterns and contradictions of the source before corrective logic changes them.
That makes profiling the diagnostic front door of trustworthy data management. It turns “this dataset looks fine” into evidence about what the dataset actually contains and what still needs explanation.
Data Management Series
- Data Quality
- Data Observability and Monitoring
- Missing Data and Imputation
- Data Deduplication and Entity Resolution
- Data Collection and Instrumentation
Final idea: profile first. The strongest cleaning, modelling and AI pipelines begin with a clear description of the evidence they actually received—not the schema, documentation or expectations they hoped to receive.