Data Quality Principles

Today uses only DuckDB and your existing warehouse. GX arrives tomorrow. Principles first, tools second.

41.1 Why Data Quality Matters: Five Macro Failure Stories (20 min)

Each story is a realistic pattern. For each, ask: would our pipeline have caught it, and where?

Story 1 — The unit error. An API starts reporting a yield as 0.0375 instead of 3.75 (a decimal instead of a percent). Types are still DOUBLE and nothing is null. The real-rate chart suddenly shows policy at −3%.

Story 2 — The stale series. A provider pauses a series. Ingestion "succeeds" every day, with zero new rows, and the dashboard proudly displays a two-month-old unemployment rate as "latest".

Story 3 — The silent revision. StatCan revises six months of an LFS series. The pipeline absorbs the new values (Day 27's merge), and last week's briefing no longer matches today's numbers, with no record of why.

Story 4 — The cross-source disagreement. CPI All-items arrives through two paths: the WDS vector (Day 10) and the full table (Day 18). After a partial refresh they disagree for the latest month. Which is right?

Story 5 — The plausible-looking outlier. A monthly unemployment rate jumps from 6.4% to 16.4%: a keying error at the source, or a typo in a manual seed. It's inside 0–100%, so range checks pass.

Try it 41.1

Fill in the table for our pipeline as it stands after Day 40.

41.2 The Dimensions of Data Quality (25 min)

"Good data" means different things. Practitioners break it into dimensions, each needing different checks:

Notes:

• Accuracy is the hardest. You rarely have an independent "truth". Reconciliation against published headline figures (e.g. the CPI YoY in StatCan's Daily release) is the practical proxy.
• Plausibility is where economists add the most value. Only someone who knows the economy can say that a 3pp monthly jump in unemployment is only plausible in April 2020.
• Dimensions interact: a stale value (timeliness) can also make a consistency check fail.

Try it 41.2

For each column, name the two most important dimensions and one concrete check: (a) usd_cad in fct_open_economy_daily; (b) headline_yoy in macro_monthly; (c) status_flag in stg_statcan__cpi.

41.3 Contract-Driven Data Engineering (23 min)

A data contract is an agreement between a producer and a consumer of data about what the data will look like and how it will behave.

Producer ───────── contract ─────────► Consumer
(StatCan, dlt,       • schema               (dashboards, forecasting,
 dbt models)         • semantics            causal models, analysts)
                     • quality expectations
                     • freshness (SLA)
                     • owner & escalation

The elements of a contract

Why "contract-driven"?

In contract-driven engineering, you write the expectations first, as an explicit, reviewed agreement, and then build and monitor pipelines against them. Three consequences:

1. Quality becomes testable. "Good data" stops being a feeling and becomes a list of executable expectations.
2. Failures have owners. Each expectation says who acts when it fails, and how urgently.
3. Change is negotiated. A producer can't silently rename a column or change a unit, because the contract (and the validation) would break.

Great Expectations is a tool for writing the quality part of such contracts in executable form. The expectations you'll write are readable statements, "expect column value values to be between 0 and 100", that double as documentation.

Severity: not every failure is equal

41.4 Auditing Our Pipeline: Coverage and Gaps (25 min)

Before adding checks, map what exists. Open the DuckDB CLI on the production warehouse:

cd /workspaces/macro-data-course
duckdb macro.duckdb -readonly

Inventory: what does dbt already test?

cd macro_dbt && source ../.venv/bin/activate
dbt ls --resource-type test --output name | sed 's/_[0-9a-f]\{10\}$//' | sort | head -60
cd ..

Group the tests by dimension. You'll find strong coverage of uniqueness, integrity and validity (ranges), some timeliness, and almost nothing for consistency across sources, plausibility of changes or accuracy.

Prototype the missing checks in SQL

A quality check is ultimately a query. Prototype each gap in DuckDB before encoding it in GX.

Consistency: do the two CPI paths agree?

select
    v.obs_date,
    v.value     as cpi_vector,
    t.cpi       as cpi_table,
    v.value - t.cpi as diff
from intermediate.int_macro__observations as v
join staging.stg_statcan__cpi as t
  on t.month = v.obs_date and t.geo_code = 'CA' and t.component = 'All-items'
where v.series_name = 'cpi_all_items'
  and abs(v.value - t.cpi) > 1e-9
order by v.obs_date desc;

Zero rows is ideal. If the vector has a newer month than the table (the table wasn't republished yet, Day 30's change detection), that's a timing difference, not an error. The expectation must handle it, for example by comparing only months present in both, which the inner join already does.

Plausibility: how large are monthly changes in unemployment?

with u as (
    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'
)
select obs_date, value, round(change_pp, 1) as change_pp
from u
order by abs(change_pp) desc nulls last
limit 10;

You'll see the spring-2020 pandemic months at the top, then a long tail of small changes. This distribution tells you where to set a plausibility threshold (next block).

Timeliness: latest date per series, in one view

select s.series_name, s.frequency, max(o.obs_date) as latest,
       current_date - max(o.obs_date) as age_days, s.max_staleness_days
from seeds.seed_series as s
left join intermediate.int_macro__observations as o using (series_id)
group by all
order by age_days desc;

41.5 Rules vs Statistics: Setting Thresholds from History (23 min)

Two kinds of expectations:

For statistical checks, use quantiles of historical behaviour rather than guesses.

with u as (
    select 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.50), 2)  as p50,
    round(quantile_cont(abs(change_pp), 0.95), 2)  as p95,
    round(quantile_cont(abs(change_pp), 0.99), 2)  as p99,
    round(max(abs(change_pp)), 2)                  as max_abs_change
from u
where change_pp is not null;

The mostly idea

Real economies have genuine extremes (2008, 2020). A check like "monthly change below 1.5pp" would fail during COVID. That's not bad data, it's history. GX solves this with mostly: an expectation passes if at least a given share of rows satisfy it.

Economist's rule: use rule-based checks for definitions (critical), statistical checks with mostly for history (warnings), and always let a human confirm a statistical alarm before calling it an error.

Try it 41.5

Compute the 99th percentile of the absolute daily log return of USD/CAD from marts.fct_open_economy_daily, and propose a plausibility threshold for a single day's move.

select
    round(100 * quantile_cont(abs(usd_cad_logret), 0.99), 2)  as p99_pct,
    round(100 * max(abs(usd_cad_logret)), 2)                  as max_pct
from marts.fct_open_economy_daily
where usd_cad_logret is not null;

41.6 Mini-Project: The Data Quality Plan (20 min)

Create quality/DATA_QUALITY_PLAN.md. This document is the specification for everything you'll build on Days 42–50. Fill in at least 12 checks covering every dimension from 41.2.

mkdir -p quality

Use this template:

# Data Quality Plan — Canadian Macro Warehouse

## Scope
Assets protected: raw_dlt (StatCan, BoC, FRED), staging, intermediate, marts.
Consumers: notebook dashboards (exposures), forecasting (Days 61–70), causal models (Days 71–80).

## Severity levels
- CRITICAL: stop publication, alert immediately
- WARNING: publish, flag, alert for review
- INFO: record in Data Docs only

## Checks
| ID | Asset | Dimension | Expectation (plain English) | Type | Severity | Owner | Implemented in |
|----|-------|-----------|-----------------------------|------|----------|-------|----------------|
| Q01 | unemployment_rate (StatCan vector) | Validity | value between 0 and 100 | rule | CRITICAL | macro team | GX (Day 43) |
| Q02 | unemployment_rate | Completeness | no missing months since first observation | rule | CRITICAL | macro team | GX (Day 48) |
| Q03 | unemployment_rate | Plausibility | monthly change ≤ 1.5pp for 99.5% of months | statistical | WARNING | macro team | GX (Day 45) |
| Q04 | CPI vector vs full table | Consistency | equal for every month present in both | rule | CRITICAL | macro team | GX (Day 43) |
| Q05 | stg_statcan__cpi | Uniqueness | one row per month × geo × component × base | rule | CRITICAL | macro team | dbt (Day 36) |
| Q06 | … | Timeliness | … | … | … | … | … |
| … | | | | | | | |

## Open questions
- Who confirms statistical alarms, and within what time?
- How are StatCan revisions communicated to dashboard users?

Guidance:

• Where dbt already covers a check, say so. Don't duplicate it in GX without a reason.
• Every CRITICAL check should be rule-based (definitions), and every statistical check should usually be a WARNING.
• Include at least one accuracy check (e.g. latest CPI YoY equals the figure published in StatCan's Daily, entered manually in a small reference file).

Day 41 checklist

• I can analyse a data failure by dimension and say where it would be caught
• I can explain the parts of a data contract and why contract-driven engineering helps
• I audited existing dbt tests and prototyped missing checks in SQL
• I set statistical thresholds from historical quantiles, and understand mostly
• quality/DATA_QUALITY_PLAN.md lists ≥ 12 checks with dimension, severity, owner and tool

git add quality/DATA_QUALITY_PLAN.md
git commit -m "Day 41: data quality principles and plan"
git push