Data profiling and validation

“Data profiling techniques” on a Python job ad means: before you build on a dataset, you can say what is in it — types, nulls, cardinality, distributions, duplicates, freshness — and you turn what you learn into checks that run every time the data arrives. Profiling is the one-off look; validation is the same look, automated, in the pipeline.

What a profile answers

The reason to do it before modelling anything: a column called amount that is a string with commas in 3% of rows, a customer_id that is 12% null after a source migration, a status with 47 distinct spellings of four values. Every one of these becomes a production incident if it is found by the model instead of by you.

In pandas, by hand

import pandas as pd

def profile(df: pd.DataFrame) -> pd.DataFrame:
    """One row per column: the numbers you actually read first."""
    out = pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "null_pct": df.isna().mean().round(4) * 100,
        "n_unique": df.nunique(dropna=True),
        "unique_pct": (df.nunique(dropna=True) / len(df) * 100).round(2),
        "example": df.apply(lambda s: s.dropna().iloc[0] if s.notna().any()
                            else None),
    })
    num = df.select_dtypes("number")
    out.loc[num.columns, "min"] = num.min()
    out.loc[num.columns, "p50"] = num.median()
    out.loc[num.columns, "max"] = num.max()
    out.loc[num.columns, "zeros_pct"] = (num == 0).mean() * 100
    return out.sort_values("null_pct", ascending=False)

print(profile(df).to_string())
print("duplicate keys:", df.duplicated(["order_id"]).sum())
print(df["status"].value_counts(dropna=False).head(20))    # the 47 spellings

df.describe(include="all") and df.info() are the ten-second version; the function above is what you keep, because it puts null rate and cardinality — the two numbers that predict trouble — in the first columns.

Two habits

Two habits: profile per source or per month, not just overall, because a 2% null rate overall can be 40% in last week’s file; and look at value_counts of every low-cardinality column, which is where the inconsistent spellings live.

For a report to share, ydata-profiling (the old pandas-profiling) renders all of this plus correlations and histograms as an HTML page from one call. It is slow on wide data — sample first — and it is a document, not a check.

Profiling in the database

Do not pull a 200-million-row table into pandas to count nulls. The warehouse profiles itself:

SELECT
  count(*)                                   AS rows,
  count(*) - count(customer_id)              AS customer_id_nulls,
  count(DISTINCT customer_id)                AS customer_id_distinct,
  min(created_at), max(created_at),          -- freshness
  percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS amount_p50,
  count(*) FILTER (WHERE amount <= 0)        AS non_positive_amounts
FROM orders
WHERE created_at >= now() - interval '30 days';

PostgreSQL also keeps a free profile in pg_stats — null_frac, n_distinct, most_common_vals per column, refreshed by ANALYZE — which is the planner’s view of the data and a fine first look. For a sample in pandas, TABLESAMPLE or ORDER BY random() LIMIT 100000; never SELECT * into memory to profile.

From profile to validation

The profile tells you the rules; validation enforces them on every run. In Python the two libraries are pandera (schema-as-code for DataFrames) and Great Expectations (expectation suites with reports); in the warehouse, dbt tests and Soda (ETL, orchestration, and dbt).

import pandera.pandas as pa

Orders = pa.DataFrameSchema(
    {
        "order_id": pa.Column(str, unique=True),
        "customer_id": pa.Column(str, nullable=False),
        "amount": pa.Column(float, pa.Check.gt(0)),
        "status": pa.Column(str, pa.Check.isin(["new", "paid", "shipped",
                                                 "cancelled"])),
        "created_at": pa.Column("datetime64[ns]"),
    },
    checks=pa.Check(lambda df: df["created_at"].max()
                    >= pd.Timestamp.now() - pd.Timedelta(days=1),
                    error="stale: no rows in the last 24h"),
    strict=True,                    # unexpected columns are an error too
)

validated = Orders.validate(df, lazy=True)   # collect every failure, not the first

Each rule above came from a profile: uniqueness from the duplicate count, the enum from value_counts, the positivity from min, freshness from max(created_at). The schema lives in the repo next to the pipeline, runs at ingestion, and fails the run on the checks that matter. Decide per rule whether a breach blocks or warns; block everything and the checks get disabled, warn on everything and nobody reads them.

Where it breaks in production

• Profiled once, in a notebook, a year ago. The source changed; the profile in someone’s memory did not. Profiles are re-run per delivery, and the deltas are the alert.
• The overall average. 2% null overall, 40% in the newest partition. Profile by source and by time.
• Strings that look like numbers. "1,234.56", "N/A", "-" in a numeric column; astype(float) fails on the third and the pipeline silently coerces or dies. Profile the type inferred from values, not the declared one.
• Validation without a policy. A failing check that no one owns is a Slack message that scrolls by. Every check has a severity and an owner.
• Profiling the whole table in pandas. Out of memory at 03:00. Sample, or profile in SQL.
• Duplicates from the join, not the source. A one-to-many join doubled the rows; the profile of the joined frame shows it, the profile of the source does not. Profile after every join.

Related

• Pandas — Fundamentals and Indexing - the operations the profile is built from.
• When Pandas Breaks Down — and What to Reach For - when to profile in DuckDB or Polars instead.
• ETL, orchestration, and dbt - quality checks in the pipeline.
• How a B-tree index works - ANALYZE and what the planner keeps.
• Data leakage - what a profile finds before a model does.