Profiling Datasets

45.1 What Profiling Is, and What It Isn't (18 min)

Profiling means systematically describing a dataset: for each column, its type, completeness, range, distribution, cardinality and typical values. It answers "what does this data normally look like?", the knowledge you need to write warning-level expectations sensibly.

Profiling proposes; economists decide. A profile of a series containing a past unit error would happily propose a range that includes the error. Every drafted expectation must be reviewed by someone who knows the economics.

Older versions of GX shipped automatic profilers ("Data Assistants"). GX 1.x Core focuses on expectations and validation, so we'll build a small, transparent profiler of our own with DuckDB, which is fast, easy to understand, and gives full control.

45.2 Fast Profiles with DuckDB SUMMARIZE (23 min)

Recall Day 13: SUMMARIZE computes a profile of every column in one statement. Quality scripts always open the warehouse read-only.

Create quality/day45_profile_basics.py:

import duckdb
import pandas as pd

from gx_helpers import PROJECT_ROOT

DB = str(PROJECT_ROOT.parent / "macro.duckdb")
pd.set_option("display.width", 200, "display.max_columns", 20)

with duckdb.connect(DB, read_only=True) as con:
    prof = con.sql("summarize select * from marts.macro_monthly").df()

print(prof[["column_name", "column_type", "min", "max", "avg", "std",
            "q25", "q50", "q75", "approx_unique", "null_percentage", "count"]])

Read the output column by column and note anything surprising:

• null_percentage: which columns have gaps, and why? (US yields before FRED coverage? CAD/USD before 2017, Day 6?)
• min / max: any value that makes an economist frown?
• approx_unique vs count: is month unique (as it must be)?

Quantiles beyond the quartiles

SUMMARIZE gives quartiles. For thresholds we need the tails:

with duckdb.connect(DB, read_only=True) as con:
    tails = con.sql("""
        select
            quantile_cont(unemployment_rate, [0.001, 0.01, 0.5, 0.99, 0.999]) as unemp_q,
            quantile_cont(headline_yoy,      [0.001, 0.01, 0.5, 0.99, 0.999]) as cpi_q
        from marts.macro_monthly
    """).fetchone()
print(tails)

quantile_cont(col, [list]) returns several quantiles at once as a list.

Try it 45.2

Profile raw_dlt.statcan_cpi_full (a large, all-text table). Which columns have the lowest cardinality? What distinct values does status take, and how often? Why is this raw-layer profile useful for the boundary suite?

with duckdb.connect(DB, read_only=True) as con:
    print(con.sql("summarize select * from raw_dlt.statcan_cpi_full")
             .df()[["column_name", "approx_unique", "null_percentage"]])
    print(con.sql("""select status, count(*) as n from raw_dlt.statcan_cpi_full
                     group by 1 order by n desc""").df())

45.3 A Profiler That Drafts Expectations (33 min)

Now automate the step from profile to draft expectations, written as a YAML spec in specs/drafts/ for human review.

The drafting rules

The margins are deliberately generous. Warnings should flag the genuinely unusual, not normal variation.

Create quality/profile_assets.py:

"""Profile warehouse relations with DuckDB and draft GX expectation specs for review.

Usage:  python quality/profile_assets.py macro_monthly=marts.macro_monthly ...
Writes: quality/specs/drafts/<asset>.yml
"""
import sys

import duckdb
import yaml

from gx_helpers import PROJECT_ROOT

DB = str(PROJECT_ROOT.parent / "macro.duckdb")
DRAFT_DIR = PROJECT_ROOT / "specs" / "drafts"
NUMERIC = ("TINYINT", "SMALLINT", "INTEGER", "BIGINT", "HUGEINT", "FLOAT", "DOUBLE", "DECIMAL")


def is_numeric(col_type):
    return col_type.upper().startswith(NUMERIC)


def draft_for(con, asset, relation, margin=0.10):
    prof = con.sql(f"summarize select * from {relation}").df()
    n_rows = int(prof["count"].iloc[0])
    critical, warning = [], []

    for _, c in prof.iterrows():
        col, typ = c["column_name"], str(c["column_type"])
        if col.startswith("_"):                              # skip technical columns
            continue
        null_pct = float(c["null_percentage"])
        tag = {"plan_id": "DRAFT", "source": "profiler", "review": "REQUIRED"}

        if null_pct == 0:
            critical.append({"type": "ExpectColumnValuesToNotBeNull",
                             "kwargs": {"column": col},
                             "meta": {**tag, "dimension": "completeness"}})

        if int(c["approx_unique"]) >= n_rows and n_rows > 0:
            exact = con.sql(f"select count(distinct {col}) = count(*) from {relation}").fetchone()[0]
            if exact:
                critical.append({"type": "ExpectColumnValuesToBeUnique",
                                 "kwargs": {"column": col},
                                 "meta": {**tag, "dimension": "uniqueness"}})

        if is_numeric(typ):
            lo, hi, mean = con.sql(f"""
                select quantile_cont({col}, 0.001), quantile_cont({col}, 0.999), avg({col})
                from {relation} where {col} is not null
            """).fetchone()
            if lo is not None:
                pad = margin * (hi - lo if hi > lo else abs(hi) or 1)
                warning.append({"type": "ExpectColumnValuesToBeBetween",
                                "kwargs": {"column": col, "min_value": round(lo - pad, 4),
                                           "max_value": round(hi + pad, 4), "mostly": 0.999},
                                "meta": {**tag, "dimension": "plausibility",
                                         "observed_p001_p999": [round(lo, 4), round(hi, 4)]}})
                if mean and mean > 0:
                    warning.append({"type": "ExpectColumnMeanToBeBetween",
                                    "kwargs": {"column": col, "min_value": round(mean / 3, 4),
                                               "max_value": round(mean * 3, 4)},
                                    "meta": {**tag, "dimension": "plausibility",
                                             "observed_mean": round(mean, 4)}})

        elif typ.upper() in ("VARCHAR", "TEXT") and int(c["approx_unique"]) <= 20:
            values = [r[0] for r in con.sql(
                f"select distinct {col} from {relation} where {col} is not null order by 1"
            ).fetchall()]
            warning.append({"type": "ExpectColumnValuesToBeInSet",
                            "kwargs": {"column": col, "value_set": values},
                            "meta": {**tag, "dimension": "validity"}})

    warning.append({"type": "ExpectTableRowCountToBeBetween",
                    "kwargs": {"min_value": int(0.9 * n_rows)},
                    "meta": {"plan_id": "DRAFT", "dimension": "completeness",
                             "observed_rows": n_rows}})

    return {"asset": asset, "batch_definition": "full", "owner": "macro-team",
            "critical": critical, "warning": warning}


def main(pairs):
    DRAFT_DIR.mkdir(parents=True, exist_ok=True)
    with duckdb.connect(DB, read_only=True) as con:
        for pair in pairs:
            asset, relation = pair.split("=")
            spec = draft_for(con, asset, relation)
            path = DRAFT_DIR / f"{asset}.yml"
            with open(path, "w") as f:
                f.write(f"# DRAFT generated by profile_assets.py from {relation}.\n"
                        "# REVIEW every item: profiles describe history, not correctness.\n")
                yaml.safe_dump(spec, f, sort_keys=False, allow_unicode=True)
            print(f"✓ {path.relative_to(PROJECT_ROOT)}  "
                  f"({len(spec['critical'])} critical, {len(spec['warning'])} warning drafts)")


if __name__ == "__main__":
    main(sys.argv[1:] or ["macro_monthly=marts.macro_monthly"])

Run it:

python quality/profile_assets.py \
    macro_monthly=marts.macro_monthly \
    fct_open_economy_daily=marts.fct_open_economy_daily
cat quality/specs/drafts/macro_monthly.yml | head -60

Draft files live in specs/drafts/, which build_suites.py ignores because it only reads specs/*.yml. Nothing drafted goes live until a person moves it.

Look critically at the drafts. The profiler will propose not_null on columns that are complete today but have legitimately missing history, and ranges for derived columns that are meaningless to constrain. Reviewing drafts is the real work, and it's economists' work.

45.4 Profiling Changes, Not Just Levels (20 min)

Day 41's Story 5 (an unemployment jump from 6.4% to 16.4%) passes every level check. Plausibility lives in changes.

A query asset of changes

Add to QUERY_ASSETS in quality/setup_gx.py:

    "unemployment_rate_changes": """
        select obs_date,
               value,
               value - lag(value) over (order by obs_date) as change_pp
        from intermediate.int_macro__observations
        where series_name = 'unemployment_rate'
    """,

and give it a monthly batch definition on obs_date (in MONTHLY_PARTITIONS). Re-run python quality/setup_gx.py.

Profile the changes

with duckdb.connect(DB, read_only=True) as con:
    print(con.sql("""
        with u as (
            select obs_date, value - lag(value) over (order by obs_date) as change_pp
            from intermediate.int_macro__observations
            where series_name = 'unemployment_rate'
        )
        select
            round(quantile_cont(abs(change_pp), 0.95), 2)  as p95,
            round(quantile_cont(abs(change_pp), 0.99), 2)  as p99,
            round(quantile_cont(abs(change_pp), 0.995), 2) as p995,
            round(max(abs(change_pp)), 2)                  as max_abs,
            count(*) filter (where abs(change_pp) > 1.5)   as months_above_1_5
        from u
    """).df())

The spec, with a deliberate two-level design

quality/specs/unemployment_rate_changes.yml:

asset: unemployment_rate_changes
batch_definition: full
owner: macro-team

warning:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: change_pp, min_value: -1.5, max_value: 1.5, mostly: 0.99}
    meta:
      plan_id: Q03
      dimension: plausibility
      rationale: >
        Over the full history, at least 99% of monthly changes are within ±1.5pp
        (calibrated from the Day 45 profile). Tolerates rare genuine shocks (e.g. 2020);
        fails if large jumps become common.

And for the newest month, which is what a daily pipeline cares about, a stricter check on the monthly batch. Add a second spec file, quality/specs/unemployment_rate_changes_latest.yml:

asset: unemployment_rate_changes
batch_definition: by_month          # latest month only
owner: macro-team

warning:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: change_pp, min_value: -1.5, max_value: 1.5}
    meta:
      plan_id: Q03b
      dimension: plausibility
      rationale: "A new month moving more than 1.5pp needs human confirmation before briefing."

Two spec files for the same asset but different batches: one judges history, the other news. To keep suite names unique, the builder (Day 46 version) will include the batch in the suite name when a spec's batch isn't full.

Why is "change_pp of the latest month" computed correctly on a monthly batch? The lag() is calculated in the query before GX filters to one month, so the latest row still knows the previous month's value. Order of operations matters, just as with WHERE before window functions (Day 15).

45.5 Distributional Expectations: Catching Unit Errors and Drift (20 min)

Row-level ranges miss errors that shift the whole distribution. Aggregate expectations catch them.

Example: a quantile fingerprint for USD/CAD daily returns

  - type: ExpectColumnQuantileValuesToBeBetween
    kwargs:
      column: usd_cad_logret
      quantile_ranges:
        quantiles: [0.05, 0.5, 0.95]
        value_ranges: [[-0.012, -0.002], [-0.001, 0.001], [0.002, 0.012]]
    meta:
      plan_id: Q26
      dimension: plausibility
      rationale: >
        Typical daily FX moves: roughly ±0.5% at the 5th/95th percentiles, median near zero.
        Ranges calibrated from the Day 45 profile (adjust to your data).

(Replace the numbers with those from your own profile: quantile_cont(usd_cad_logret, [0.05, 0.5, 0.95]).)

Example: detecting a "stuck" series

If ingestion accidentally forward-fills one value for months, the number of distinct values in a recent window collapses. A query asset of the last 60 business days plus:

  - type: ExpectColumnUniqueValueCountToBeBetween
    kwargs: {column: usd_cad, min_value: 30}
    meta: {plan_id: Q27, dimension: timeliness,
           rationale: "Over 60 business days USD/CAD should take many distinct values; few means a stuck feed."}

Try it 45.5

Simulate a unit error in pandas: take macro_monthly, divide yield_10yr by 100, validate the Q24 mean expectation (ExpectColumnMeanToBeBetween(column="yield_10yr", min_value=1, max_value=8)) against the modified DataFrame with the pandas fallback from Day 43, and confirm it fails while the row-level range check from 44.4 (−1 to 25) still passes.

import duckdb
import great_expectations as gx
from gx_helpers import get_context, PROJECT_ROOT

context = get_context()
with duckdb.connect(str(PROJECT_ROOT.parent / "macro.duckdb"), read_only=True) as con:
    df = con.sql("select month, yield_10yr from marts.macro_monthly").df()
df["yield_10yr"] = df["yield_10yr"] / 100            # the simulated unit error

try:
    src = context.data_sources.get("pandas_fallback")
except Exception:
    src = context.data_sources.add_pandas(name="pandas_fallback")
try:
    asset = src.get_asset("unit_error_test")
except Exception:
    asset = src.add_dataframe_asset(name="unit_error_test")
try:
    bd = asset.get_batch_definition("whole")
except Exception:
    bd = asset.add_batch_definition_whole_dataframe(name="whole")
batch = bd.get_batch(batch_parameters={"dataframe": df})

E = gx.expectations
print("range  :", batch.validate(E.ExpectColumnValuesToBeBetween(
    column="yield_10yr", min_value=-1, max_value=25)).success)        # True: missed!
print("mean   :", batch.validate(E.ExpectColumnMeanToBeBetween(
    column="yield_10yr", min_value=1, max_value=8)).success)          # False: caught

45.6 Mini-Project: Review Drafts into Final Specs (23 min)

1. Profile every GX asset: python quality/profile_assets.py asset=schema.table ... for the table assets (for query assets, profile the underlying query in DuckDB by hand, as in 45.4).
2. For each draft, create or update the final spec in specs/ by:
   • keeping items that match a plan check, and assigning the real plan ID;
   • editing thresholds using economic judgement (round sensibly; widen where history is short);
   • deleting items that constrain meaningless things (e.g. ranges on IDs, not-null on columns with legitimate historical gaps);
   • adding a rationale to every kept item.
3. Record your review decisions in quality/specs/REVIEW_LOG.md: what the profiler proposed, what you kept, changed or removed, and why.
4. Run python quality/build_suites.py.

Here is an example REVIEW_LOG.md entry:

## macro_monthly — 2026-09-30 (reviewer: A. Economist)
- DRAFT not_null(us_yield_5yr): REMOVED — FRED coverage starts later than panel start; gaps are legitimate.
- DRAFT between(headline_yoy, -1.3, 9.1): CHANGED to [-3, 10], mostly 0.99 (Q23) — history too short
  to rule out deflation deeper than 2009.
- DRAFT mean(yield_10yr, 0.9, 8.1): KEPT as [1, 8] (Q24) — unit-error detector.
- DRAFT in_set(...): none (no low-cardinality text columns).

Day 45 checklist

• I can profile any relation quickly with SUMMARIZE and tail quantiles
• profile_assets.py drafts expectations into specs/drafts/, never directly into live suites
• I profile changes as well as levels, and check plausibility on both history and the newest month
• I use aggregate expectations (mean, stdev, quantiles, unique counts) to catch unit errors and stuck feeds
• Every live threshold has a rationale, and my review decisions are logged

git add quality/ && git commit -m "Day 45: profiling, draft expectations, reviewed specs" && git push