What Is Data Profiling in ETL? Types and Techniques

I once loaded 2 million customer records into a warehouse without profiling them first. Three weeks later we found out 40% had broken email formats.

And the marketing team? They’d already launched campaigns on that data.

That expensive mess taught me why profiling belongs BEFORE any ETL job runs. So let me save you the same three weeks 👇

📌 TL;DR: Data profiling in ETL is the systematic inspection of source data before you move it, so bad values, format chaos, and hidden dependencies surface early. Five techniques cover it: column, data type, pattern, dependency, and duplicate profiling. Profile at extraction, again after transformation, and continuously for critical tables.

Here’s the map for this page:

What profiling delivers, and what skipping it costs
The five profiling techniques, with real examples
Where profiling ends and quality or analysis begins
When to run it, what to measure, and what usually goes wrong

What Is Data Profiling in ETL?

Data profiling in ETL is the analysis of source data to discover its structure, content, quality, and relationships before you extract, transform, and load it. Think of it as a health check for your data.

You examine what you actually have before deciding how to process it. That’s the whole idea.

Here’s the thing. Raw data rarely matches its documentation. Fields hold surprises, formats wander, and nulls hide in the columns nobody checks.

Profiling drags those realities into the light, so you can design transformations that hold up in production. IBM’s profiling primer and the Wikipedia entry both frame it the same way, if you want a vendor-neutral second opinion.

What Does Data Profiling Achieve?

Profiling gives you an evidence-based picture of your source data, so you stop guessing and start engineering. I’ve watched teams skip it to save a day. They always pay it back with interest.

Prevents Pipeline Failures

Profiling catches missing values, wrong types, and outliers before they crash a load. It also surfaces schema drift detection issues, meaning a source quietly changed shape since your last run.

The math is simple. Catching a problem upstream costs minutes. Fixing corrupted downstream tables costs days.

Speeds Up Transformation Design

Knowing the real data types, cardinalities, and value distributions makes mapping faster. Cardinality just means how many distinct values a column holds. Without profiling you’re guessing at all three.

Enables Compliance

Profiling flags personal and sensitive fields before they move through your pipeline. You can’t mask what you don’t know exists. It feeds data governance directly, giving stewards evidence instead of opinions.

Builds Stakeholder Trust

Scorecards built from profiling metrics give business users something concrete. They stop second-guessing reports, because they can see the data quality numbers behind them.

🔍 Reality check: In every profiling engagement I've run, a small number of sources produced most of the quality issues. Rank your findings by source before you plan any cleanup, because fixing the top two usually beats fixing the next twenty.

What Are the Types of Data Profiling?

There are five profiling techniques, and each one inspects a different part of your data. Let me walk them with examples from real projects.

Column Profiling

Column profiling examines one attribute at a time: completeness, distribution, and basic statistics. I always start here. For each column I calculate:

Completeness: share of non-null values. Formula: 1 - (null_count / row_count)
Distinct count: unique values, using approximate algorithms like HyperLogLog on big tables
Descriptive stats: min, max, mean, median, standard deviation, and percentiles (P50, P95, P99)

One retail dataset I profiled had a “country” field that was 99% complete but held 847 distinct values. The expected count was around 195. That single finding exposed years of free-text data entry.

Data Type Profiling

Type profiling checks whether values match their declared types and finds the mismatches. I’ve found string columns hiding numeric IDs. I’ve found date columns storing epoch timestamps, ISO strings, and MM/DD/YYYY all at once.

It also covers length analysis, character encoding, locale formatting, and nested JSON structure. Boring work. Saves entire sprints.

Pattern Profiling

Pattern profiling uses regular expressions and semantic checks to validate formats. These are the patterns I test on almost every project:

Pattern type	Example regex	Use case
Email	`.+@.+\..+`	Contact validation
Phone (US)	`\d{3}-\d{3}-\d{4}`	Telecom records
Postal code	`\d{5}(-\d{4})?`	Address standardization
Date (ISO)	`\d{4}-\d{2}-\d{2}`	Temporal fields

I once profiled a “phone number” field that contained fax numbers, extensions, international formats, and a few customer notes. Pattern profiling caught all of it before we tried to normalize the column.

Dependency Profiling

Dependency profiling discovers relationships between columns and tables. It looks for functional dependencies, where one column’s value determines another’s, plus referential integrity between keys.

ZIP code usually determines city and state. So if your data maps ZIP 90210 to Chicago, this is the check that flags it.

Clean inputs pay off here. Feed it fields that already went through data cleansing and the real dependencies stand out much faster.

Uniqueness and Duplicate Profiling

Duplicate profiling finds records that appear more than once, both exact and fuzzy matches. I calculate duplicate rate as 1 - (unique_key_count / row_count).

Honestly, this one shocks teams the most. I audited a CRM expecting almost no duplicates and found nearly a quarter of contacts repeating with slight name variations, which is exactly why data deduplication belongs right after profiling.

One quick vocabulary note. Some tools group these five into three families instead: structure discovery (types and formats), content discovery (values and completeness), and relationship discovery (dependencies and keys). Same work, different labels, so don’t let the naming confuse you in a vendor demo.

Data Profiling vs Data Quality vs Data Analysis

These three overlap in conversation and mean different things in practice.

Profiling describes. It reports what the data looks like right now: 4% nulls, 847 distinct countries, 12% of emails failing the pattern. No judgment, just measurement.

Quality judges. It compares those measurements against rules you agreed on, then passes or fails the dataset. Profiling tells you the number, quality tells you whether the number is acceptable.

Analysis interprets. It asks what the data means for the business, like which region is growing. Profiling answers “what does this data look like?” while analysis answers “what should we do about it?”

Real-World Data Profiling Examples

Five findings from real projects, all caught before the load.

The 847-country problem. Free-text entry created “USA”, “U.S.A.”, “United States” and hundreds more. Column profiling found it in one query, and the fix was a lookup table plus a validation rule.

The phone field with everything in it. Faxes, extensions, international prefixes, and the occasional sentence. Pattern profiling separated the real numbers from the noise before normalization.

The ZIP that moved. Dependency profiling flagged postal codes mapped to the wrong cities, which usually means two source systems merged badly.

The CRM full of twins. Duplicate profiling found the same contacts under married names, nicknames, and typos. Exact matching had reported a clean file.

The mixed-currency amount column. One field held euros and dollars with no currency code beside it. Distribution profiling showed two obvious clusters, and that saved a quarterly report from being silently wrong.

When Should You Run Profiling in an ETL Pipeline?

Run profiling at three points: at extraction, right after transformation, and continuously on critical tables. Profiling once during onboarding is the mistake nearly everyone makes.

At extraction, you learn what the source really contains. After transformation, you confirm your own logic didn’t introduce defects. And continuously, you catch drift, because sources change without telling you.

Sampling makes this affordable. For large tables, stratified sampling (drawing from every segment, not just the top rows) gives accurate insight fast. Use full scans on the columns that drive joins, dates, and money, where one rare bad record breaks everything downstream.

Best Practices for Data Profiling in ETL

Based on dozens of implementations, these habits pay off every time.

Store Metrics Historically

Create a profile store, a dedicated table that tracks the same metrics over time:

profile_metrics(
  dataset, column, partition_date,
  row_count, null_count, distinct_count,
  min_val, p50, p95, p99, max_val,
  outlier_rate, dq_score, computed_at
)

History is what makes drift detection possible. I compare today’s distribution against last week’s using Population Stability Index, a standard measure of how far a distribution has shifted.

Convert Findings Into Rules

Profiling is exploration. The value shows up when discoveries become executable checks that run on every load. Tools like Great Expectations and dbt tests do exactly that:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: amount
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 100000

Alert With Context

Static thresholds create alert fatigue fast. Use adaptive baselines that expect seasonality, and require a few consecutive breaches before anyone gets paged.

When an alert does fire, attach data lineage context. The responder needs to know which upstream source changed and which dashboards are exposed, not just that a number moved.

Choose Tools by Team Skill

The landscape splits cleanly into three groups:

Open-source: Great Expectations, Soda Core, Apache Griffin, and dbt tests. Flexible, no license cost, more setup work.
Cloud-native: AWS Glue DataBrew, Google Dataplex, Microsoft Purview, and warehouse-native profiling. Easiest if you’re already in that cloud.
Enterprise suites: Informatica, Talend, and Ataccama for large governed estates with formal stewardship.

There’s no universal best pick. AWS’s profiling overview is a decent neutral starting point if you want the fundamentals in one page.

💡 Field note: A profiling report nobody converts into a test is a PDF nobody reads. Every finding worth mentioning in a status meeting should leave the meeting as a rule in your pipeline, with an owner's name on it.

Common Data Profiling Mistakes

The same six mistakes show up on nearly every project I inherit.

Profiling once at onboarding. Sources evolve, so a clean report from March says nothing about August.
Sampling the head of the file. Bad records cluster at the end, where the legacy system got appended.
Profiling after the load. By then the corrupted rows are already in your marts and someone’s dashboard.
Treating nulls as the only defect. A complete column full of “N/A” strings passes a completeness check and still poisons your joins.
No owner for the findings. Reports circulate, nobody’s name is on the fix, nothing changes.
Thresholds with no seasonality. Your December volumes will page someone at 3am for no reason.

Number two is mine. In Hamburg in 2023, I profiled a 900,000-row partner export by sampling the first 10,000 rows, because it was fast and the sample looked immaculate.

The bad data lived in the tail. A legacy region had been appended last, with dates in DD/MM/YYYY instead of ISO. The load succeeded quietly, and our regional reporting was wrong for a month before anyone questioned it.

Now I sample across segments and run full scans on join keys, dates, and money columns. Slower. Right.

Which Data Profiling Metrics Matter?

Five metrics carry most of the value, and they’re all cheap to compute.

Completeness. Non-null share per column, tracked per load rather than once.
Cardinality. Distinct values against the expected count, which is how the country field gave itself away.
Validity. Share of values matching the agreed pattern or reference list.
Duplicate rate. One minus unique keys over total rows, on both exact and fuzzy keys.
Drift. Distance between today’s distribution and your stored baseline.

Profiling sits at the front of a chain of neighbors. It describes what you have, cleansing fixes it, deduplication collapses the copies, governance decides who owns the rules, and lineage tells you what breaks when a source shifts. Start with column profiling on your two most critical datasets this week, store the metrics, and turn the top three findings into tests. You got this.

Data Quality & Governance Terms

Frequently Asked Questions

What is meant by data profiling?

Data profiling is the systematic analysis of a dataset to understand its structure, content, quality, and relationships. It surfaces completeness, uniqueness, distributions, patterns, and anomalies, which then guide how the data gets processed, validated, and used downstream.

What are the three main types of data profiling?

The three families are structure discovery, content discovery, and relationship discovery. Structure covers types and formats, content covers values and completeness, and relationship covers dependencies between columns and tables. Thorough work uses all three.

What is the meaning of an ETL profile?

An ETL profile is the recorded analysis of source-data characteristics taken before or during extract, transform, and load. It shapes your transformation logic, exposes the quality issues that need fixing first, and sets the baseline metrics you monitor afterwards.

What is the difference between data analysis and data profiling?

Profiling examines the data’s structure and condition, while analysis interprets the data to answer business questions. Profiling is technical and descriptive. Analysis is interpretive, focused on what the numbers mean for a decision.

When should you run data profiling in an ETL pipeline?

Run it at extraction, again right after transformation, and continuously on critical tables. Extraction tells you what the source holds, the post-transformation pass proves your logic added no defects, and continuous profiling catches drift as sources change.

Is data profiling part of ETL?

Yes, profiling is the discovery step that sits in front of extraction and repeats inside the pipeline. It isn’t one of the three letters, but no serious ETL design happens without it, and modern pipelines run profiling checks on every load.

What is data profiling vs data quality?

Profiling measures what the data looks like, while data quality judges those measurements against agreed rules. Profiling reports 4% nulls. Quality decides whether 4% passes. One produces evidence, the other produces a verdict.

What is an example of data profiling?

A classic example is counting distinct values in a country column and finding 847 where you expected about 195. That one metric exposes free-text entry, missing validation, and the standardization work your transformation layer now has to do.