Data Profiling in ETL Testing: Checks, SQL and Examples

A row count tells you how many records arrived. It does not tell you whether they are right. Data profiling summarises what is inside each column (nulls, distinct values, ranges, lengths, patterns) so you can spot problems before writing detailed test cases and confirm the target looks like the source after the load.

What Is Data Profiling in ETL Testing?

Data profiling is the process of collecting statistics about a dataset's structure and content. In ETL testing it is used twice:

• Before design: profile the source to discover real-world data issues (unexpected nulls, codes not in the spec, dates in the future) and turn them into test cases or questions for the business
• After the load: profile the target and compare it with the source. Matching counts with different profiles mean values were changed, truncated or defaulted along the way

What to Profile: Column Profiling Checks

Column Profiling with SQL

One query per table gives you a profile row you can save and compare:

SELECT COUNT(*)                                              AS row_count,
       COUNT(DISTINCT customer_id)                           AS distinct_ids,
       SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END)        AS null_emails,
       ROUND(100.0 * SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_email_pct,
       MIN(signup_date)                                      AS min_signup,
       MAX(signup_date)                                      AS max_signup,
       MIN(LENGTH(full_name))                                AS min_name_len,
       MAX(LENGTH(full_name))                                AS max_name_len,
       SUM(lifetime_value)                                   AS total_ltv
FROM dw.dim_customer;

For code columns, look at the value distribution:

SELECT country_code, COUNT(*) AS cnt
FROM dw.dim_customer
GROUP BY country_code
ORDER BY cnt DESC;
-- Watch for NULL, 'UNKNOWN' or a default value that appears far more often than in the source

And for formats, count values that break the expected pattern:

SELECT COUNT(*) AS invalid_emails
FROM dw.dim_customer
WHERE email NOT LIKE '%_@_%._%';

Comparing Source and Target Profiles

Run the same profile on source (or staging) and target, then compare side by side. Any difference must be explained by a mapping rule.

This is why profiling catches defects that a row count never will: the counts above match perfectly while three columns are wrong.

Profiling with Python (pandas)

For files or large comparisons, a few lines of pandas produce the same profile for any table:

import pandas as pd

def profile(df):
    return pd.DataFrame({
        "nulls": df.isna().sum(),
        "distinct": df.nunique(),
        "min": df.min(numeric_only=False),
        "max": df.max(numeric_only=False),
    })

src = pd.read_csv("customers_source.csv")
tgt = pd.read_csv("customers_target.csv")

diff = profile(src).compare(profile(tgt), result_names=("source", "target"))
print(diff)   # empty output = profiles match

Mixed-type columns may need converting before min/max. To build this into a repeatable suite, see the Python ETL testing framework or Great Expectations.

When to Profile

• At the start of a project, on every source table in scope
• After the first full load, comparing source and target
• After any change to transformation code (as part of regression)
• On a schedule in production, to detect drift in source data

Profiling pairs naturally with metadata testing: metadata checks the container, profiling checks the contents.

ETL testing process & templates: ETL testing process · ETL test plan template · ETL testing checklist · SCD testing · metadata testing · ETL test cases

Frequently Asked Questions

What is data profiling in ETL testing?

Data profiling collects statistics about each column, such as null counts, distinct values, min and max, lengths and value frequencies, to find data issues and to confirm the target data matches the source after an ETL load.

What is column data profiling?

Column data profiling is profiling at column level: for each column you measure nulls, distinct values, ranges, lengths and patterns, then compare the results between source and target.

What is the difference between data profiling and data validation?

Profiling summarises what the data looks like. Validation checks the data against specific expected results, such as a business rule. Profiling often reveals what you need to validate.

Which tools are used for data profiling?

Plain SQL works on any database. Python with pandas is common for files and large comparisons, and tools such as Great Expectations and many ETL platforms include built-in profiling.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures