The Headline Contracts

Time	Block
0:00 – 0:28	49.1 "Unemployment rates remain between 0 and 100%", precisely
0:28 – 0:55	49.2 "Dates are continuous without gaps", precisely
0:55 – 1:03	Break
1:03 – 1:28	49.3 Accounting identities: consistency within the release
1:28 – 1:45	49.4 Cross-source and cross-release checks
1:45 – 1:53	Break
1:53 – 2:10	49.5 Diagnostic analyses: from red cross to explanation
2:10 – 2:30	49.6 Mini-project: the complete LFS contract

All checks today follow one design: a query asset that shapes the raw text into what the rule needs, plus expectations on it. Add each query to QUERY_ASSETS in quality/setup_gx.py, with a whole-table batch definition, and write its spec in quality/specs/incoming/ with checkpoint_group: incoming_lfs.

---

49.1 "Unemployment Rates Remain Between 0 and 100%", Precisely (28 min)

The curriculum's rule sounds simple, but turning it into a check forces precise decisions:

Question	Our decision
Which rows are "rates"?	characteristics measured in percent: Unemployment rate, Participation rate, Employment rate (from the profile)
Estimates only?	yes: standard errors are not rates and get their own rule (≥ 0)
All geographies, genders, ages, data types?	yes: every published rate, not just the national headline
What about suppressed values (value null)?	not a range violation, but completeness is checked separately (below)
Text that isn't a number?	a separate critical check: every non-null value must parse

The query asset

"incoming_lfs_rates": """
    select ref_date, geo, gender, age_group, data_type,
           labour_force_characteristics as characteristic,
           try_cast(value as double)    as rate
    from raw_incoming.statcan_lfs_full
    where statistics = 'Estimate'
      and labour_force_characteristics in
          ('Unemployment rate', 'Participation rate', 'Employment rate')
""",

"incoming_lfs_unparseable_values": """
    select vector, ref_date, value
    from raw_incoming.statcan_lfs_full
    where value is not null and try_cast(value as double) is null
""",

"incoming_lfs_standard_errors": """
    select vector, ref_date, statistics, try_cast(value as double) as se
    from raw_incoming.statcan_lfs_full
    where statistics <> 'Estimate'
""",

The spec: quality/specs/incoming/lfs_rates.yml

description: "Labour-market rates in the incoming LFS release. The curriculum's 0–100% contract."
asset: incoming_lfs_rates
batch_definition: full
checkpoint_group: incoming_lfs
owner: macro-team

critical:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: rate, min_value: 0, max_value: 100}
    meta:
      plan_id: L01
      dimension: validity
      rationale: >
        Unemployment, participation and employment rates are shares of a population:
        every published estimate, for every province, gender, age group and data type,
        must lie between 0% and 100%.
      notes:
        format: markdown
        content:
          - "**If this fails:** the release is quarantined; inspect `raw_quarantine`."

  - type: ExpectTableRowCountToBeBetween
    kwargs: {min_value: 1000}
    meta: {plan_id: L02, dimension: completeness,
           rationale: "Guards against a filter silently matching nothing (renamed members)."}

warning:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: rate, min_value: 0.5, max_value: 85, mostly: 0.999}
    meta: {plan_id: L03, dimension: plausibility,
           rationale: "Rates near 0% or near 100% are possible in tiny cells but deserve a look."}

And two short specs, lfs_parseable.yml (critical: ExpectTableRowCountToEqual: {value: 0} on incoming_lfs_unparseable_values) and lfs_standard_errors.yml (critical: se ≥ 0).

L02 is subtle but important. If StatCan renamed "Unemployment rate" to "Unemployment rate (%)", the query would return zero rows, and every range check on zero rows passes. A minimum row count turns "nothing to check" into a failure. Always pair filtered checks with a row-count check.

Suppression rule (completeness)

Missing values must be explained by a status code. From your profile, list the codes that accompany missing values (for example .. or x):

"incoming_lfs_unexplained_missing": """
    select vector, ref_date, status
    from raw_incoming.statcan_lfs_full
    where value is null
      and coalesce(trim(status), '') not in ('..', 'x', 'F')
""",

Spec: ExpectTableRowCountToEqual: {value: 0}, severity warning (plan L04). Adjust the code list to your profile.

---

49.2 "Dates Are Continuous Without Gaps", Precisely (28 min)

Again, precision first:

Question	Our decision
Gaps in what?	rows (reference periods), per series, where a series is a vector
Suppressed values?	a row with a missing value is still a period that exists, so it's not a gap. Value completeness is 49.1's suppression rule
Terminated series?	excluded: they're expected to stop
Between what dates?	each series' own first and last month

We check three aspects of continuity.

(a) No internal gaps per series

The counting trick from Day 18: if a series spans N months from first to last, it must have exactly N distinct months.

"incoming_lfs_series_gaps": """
    with s as (
        select vector, cast(ref_date || '-01' as date) as month
        from raw_incoming.statcan_lfs_full
        where coalesce(terminated, '') <> 't'
    )
    select vector,
           min(month)                                    as first_month,
           max(month)                                    as last_month,
           date_diff('month', min(month), max(month)) + 1 as expected_months,
           count(distinct month)                         as actual_months
    from s
    group by vector
    having date_diff('month', min(month), max(month)) + 1 <> count(distinct month)
""",

(b) Release completeness: every active series reaches the latest month

A release where most series end in the newest month but some stop a month earlier is incomplete, even if no series has an internal gap.

"incoming_lfs_series_not_at_latest": """
    with last_by_series as (
        select vector, max(ref_date) as last_ref
        from raw_incoming.statcan_lfs_full
        where coalesce(terminated, '') <> 't'
        group by vector
    )
    select vector, last_ref,
           (select max(ref_date) from raw_incoming.statcan_lfs_full) as release_latest
    from last_by_series
    where last_ref < (select max(ref_date) from raw_incoming.statcan_lfs_full)
""",

(ref_date is 'YYYY-MM' text, which sorts correctly as text. That's one reason ISO-style date formats are so convenient.)

(c) No future periods, and a fresh latest period

"incoming_lfs_period_bounds": """
    select max(cast(ref_date || '-01' as date))                       as latest_month,
           date_diff('month', max(cast(ref_date || '-01' as date)),
                     cast(date_trunc('month', current_date) as date)) as months_behind
    from raw_incoming.statcan_lfs_full
""",

The spec: quality/specs/incoming/lfs_continuity.yml (three assets, three small specs)

# lfs_continuity_gaps.yml
description: "No missing months inside any active LFS series — the curriculum's continuity contract."
asset: incoming_lfs_series_gaps
batch_definition: full
checkpoint_group: incoming_lfs
owner: macro-team
critical:
  - type: ExpectTableRowCountToEqual
    kwargs: {value: 0}
    meta:
      plan_id: L10
      dimension: completeness
      rationale: >
        Every active series must have one row for every month between its first and last
        observation. A gap breaks year-over-year calculations (Day 5, Day 19).

# lfs_continuity_latest.yml
asset: incoming_lfs_series_not_at_latest
batch_definition: full
checkpoint_group: incoming_lfs
owner: macro-team
critical:
  - type: ExpectTableRowCountToEqual
    kwargs: {value: 0}
    meta: {plan_id: L11, dimension: completeness,
           rationale: "A release must update every active series to the same latest month."}

# lfs_continuity_bounds.yml
asset: incoming_lfs_period_bounds
batch_definition: full
checkpoint_group: incoming_lfs
owner: macro-team
critical:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: months_behind, min_value: 0}
    meta: {plan_id: L12, dimension: validity, rationale: "No reference period in the future."}
warning:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: months_behind, max_value: 2}
    meta: {plan_id: L13, dimension: timeliness,
           rationale: >
             The LFS is usually released early in the month after the reference month,
             so the latest period should be at most ~2 months behind today.}

Try it 49.2

Why do we check continuity on rows (periods) rather than non-missing values? Construct a small example where a gap check on non-missing values would raise a false alarm.

Answer

Suppose a youth unemployment rate for a small province is suppressed (x) in one month because too few respondents fell in that cell. The row for that month exists, with an empty value and a status code. A value-based gap check would call it a "missing month", but it's a legitimate, documented confidentiality suppression. Checking continuity on rows, and completeness of values separately via the suppression rule, keeps the two concepts apart and avoids false alarms.

---

49.3 Accounting Identities: Consistency Within the Release (25 min)

Labour-market statistics obey definitions that link characteristics together. These make powerful consistency checks, because a processing error in one characteristic breaks the identity.

Identity	Definition
Labour force = Employment + Unemployment	who is in the labour force
Unemployment rate = 100 × Unemployment ÷ Labour force	the headline rate
Participation rate = 100 × Labour force ÷ Population	labour-force participation

Published values are rounded (counts to 0.1 thousand, rates to 0.1pp), so recomputed values won't match exactly, especially in small cells (Try it 48.4). We use two tiers:

Tier	Tolerance	Severity	Catches
gross error	±2pp for rates; ±1 thousand for counts	critical	misaligned columns, swapped characteristics, unit errors
rounding-consistent	±0.3pp for rates, for at least 99% of cells	warning	subtler processing issues

The query asset

"incoming_lfs_identities": """
    with e as (
        select ref_date, geo, gender, age_group, data_type,
               labour_force_characteristics as lfc,
               try_cast(value as double)    as v
        from raw_incoming.statcan_lfs_full
        where statistics = 'Estimate'
    ),
    w as (
        select ref_date, geo, gender, age_group, data_type,
               max(v) filter (where lfc = 'Population')          as pop,
               max(v) filter (where lfc = 'Labour force')        as lf,
               max(v) filter (where lfc = 'Employment')          as emp,
               max(v) filter (where lfc = 'Unemployment')        as unemp,
               max(v) filter (where lfc = 'Unemployment rate')   as ur,
               max(v) filter (where lfc = 'Participation rate')  as pr
        from e
        group by all
    )
    select *,
           lf - (emp + unemp)                      as lf_error_thousands,
           ur - 100 * unemp / nullif(lf, 0)        as ur_error_pp,
           pr - 100 * lf / nullif(pop, 0)          as pr_error_pp
    from w
    where lf is not null and emp is not null and unemp is not null
""",

This is the conditional-aggregation pivot from Day 14, applied to raw text on the fly.

The spec: quality/specs/incoming/lfs_identities.yml

description: "Accounting identities linking LFS characteristics, with rounding-aware tolerances."
asset: incoming_lfs_identities
batch_definition: full
checkpoint_group: incoming_lfs
owner: macro-team

critical:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: lf_error_thousands, min_value: -1, max_value: 1}
    meta: {plan_id: L20, dimension: consistency,
           rationale: "Labour force = employment + unemployment; >1,000 persons off means a real error."}
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: ur_error_pp, min_value: -2, max_value: 2}
    meta: {plan_id: L21, dimension: consistency,
           rationale: "Published unemployment rate must match unemployment ÷ labour force (gross-error tier)."}
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: pr_error_pp, min_value: -2, max_value: 2}
    meta: {plan_id: L22, dimension: consistency}

warning:
  - type: ExpectColumnValuesToBeBetween
    kwargs: {column: ur_error_pp, min_value: -0.3, max_value: 0.3, mostly: 0.99}
    meta: {plan_id: L23, dimension: consistency,
           rationale: >
             Rounding of published counts explains small differences, largest in small cells.
             More than 1% of cells beyond ±0.3pp suggests a processing issue.}

Profile the error columns first (quantile_cont(abs(ur_error_pp), [0.5, 0.99, 0.999])) and adjust the tolerances. The goal is zero false alarms on a good release while still catching real errors.

Economist's note: identities are among the strongest quality checks available, because they're true by definition, not by statistical tendency. National accounts (GDP = C + I + G + NX, up to chained-dollar non-additivity; Day 20) and price indexes (weighted components; Day 19) offer similar checks. Look for them in every dataset you validate.

---

49.4 Cross-Source and Cross-Release Checks (18 min)

Cross-source: the vector we ingest vs the full table

Since Day 10 we've fetched the national unemployment rate as vector v2062815, and on Day 10 you verified its title by hand. The full LFS table contains the same vector, so we can check that the two paths agree and that the vector belongs to this table.

"incoming_lfs_vector_check": """
    select t.ref_date,
           try_cast(t.value as double)                 as table_value,
           o.value                                     as vector_value,
           abs(try_cast(t.value as double) - o.value)  as abs_diff
    from raw_incoming.statcan_lfs_full as t
    join raw_dlt.statcan_observations as o
      on o.series_id = t.vector
     and o.date = cast(t.ref_date || '-01' as date)
    where t.vector = 'v2062815'
""",

Spec (lfs_vector_check.yml):

• critical ExpectColumnValuesToBeBetween(column="abs_diff", max_value=1e-9): the two sources agree (plan L30);
• critical ExpectTableRowCountToBeBetween(min_value=100): the vector really is in this table, with a long overlap (plan L31). If it isn't, the join returns nothing and L30 would pass vacuously.

(As on Day 41, a newer month in one source than the other isn't a disagreement: the inner join only compares months present in both.)

Cross-release: how much did this release revise?

StatCan revises LFS estimates, and seasonally adjusted series are typically revised each January for recent years. Compare the incoming release with the currently published one:

"incoming_lfs_revisions": """
    select n.vector, n.ref_date, n.geo,
           try_cast(o.value as double) as published_value,
           try_cast(n.value as double) as incoming_value,
           abs(try_cast(n.value as double) - try_cast(o.value as double)) as abs_revision_pp
    from raw_incoming.statcan_lfs_full as n
    join raw_dlt.statcan_lfs_full      as o using (vector, ref_date)
    where n.labour_force_characteristics = 'Unemployment rate'
      and n.statistics = 'Estimate'
      and abs(try_cast(n.value as double) - try_cast(o.value as double)) > 0.3
""",

Spec: warning ExpectTableRowCountToEqual(value=0) (plan L32), with a rationale that makes the calendar explicit:

    meta:
      plan_id: L32
      dimension: consistency
      rationale: >
        Outside the annual seasonal-adjustment revision (typically January), published
        unemployment rates should rarely be revised by more than 0.3pp. In January this
        warning is expected: review and acknowledge it in the release log.

This asset needs a published raw_dlt.statcan_lfs_full to compare against, so register it after the first successful publish (Day 50). It's a warning, never critical: revisions are a normal part of official statistics, and our job is to make them visible, not to block them.

---

49.5 Diagnostic Analyses: from Red Cross to Explanation (18 min)

A failing expectation says that something is wrong; the person on call needs to know where and why. Keep diagnostic queries next to the specs, in quality/diagnostics/, one per critical check.

quality/diagnostics/lfs_missing_months.sql: which months are missing, for which series (for L10):

-- Lists every missing month inside every active LFS series in raw_incoming.
with s as (
    select vector, cast(ref_date || '-01' as date) as month
    from raw_incoming.statcan_lfs_full
    where coalesce(terminated, '') <> 't'
),
bounds as (
    select vector, min(month) as first_month, max(month) as last_month
    from s group by vector
),
expected as (
    select b.vector, cast(m.month as date) as month
    from bounds as b,
         range(b.first_month, b.last_month + interval 1 month, interval 1 month) as m(month)
)
select e.vector, e.month
from expected as e
anti join s using (vector, month)
order by e.vector, e.month
limit 100;

The date-spine plus anti-join technique is from Day 14. range(...) here is a lateral table function: it's evaluated once per row of bounds, producing each series' own calendar.

quality/diagnostics/lfs_identity_outliers.sql: the worst identity violations with their context (for L20–L23):

-- Paste the incoming_lfs_identities query as a CTE named ident, then:
select ref_date, geo, gender, age_group, data_type, lf, emp, unemp, ur,
       round(ur_error_pp, 2) as ur_error_pp
from ident
order by abs(ur_error_pp) desc nulls last
limit 20;

Reference each diagnostic in its spec's notes, so Data Docs tells the reader which query to run next:

      notes:
        format: markdown
        content:
          - "**Diagnose with:** `quality/diagnostics/lfs_missing_months.sql`"

---

49.6 Mini-Project: The Complete LFS Contract (20 min)

1. Add every query asset from today to quality/setup_gx.py (except incoming_lfs_revisions, which comes after the first publish).
2. Write all specs in quality/specs/incoming/ with checkpoint_group: incoming_lfs, calibrated to your profile.
3. Add the checks to DATA_QUALITY_PLAN.md (IDs B01–B07, L01–L32), each with dimension, severity and owner.
4. Rebuild and audit:

source .venv-gx/bin/activate
python quality/setup_gx.py && python quality/build_suites.py
python quality/validate_incoming.py statcan_lfs_full; echo "exit: $?"

5. Goal: a genuine release passes all critical checks. If a critical check fails on real data, decide with evidence (diagnostics) whether the data is wrong or the check is miscalibrated, and write the decision in quality/specs/REVIEW_LOG.md.
6. Write lighter contracts for incoming_cpi and incoming_gdp: the boundary structure (required columns, ref_date format, unique (vector, ref_date), row count) plus continuity per vector. The LFS queries are templates, since only the table name and dimension names change.

Day 49 checklist

• "Rates between 0 and 100%" is implemented for every published rate, with a row-count guard and separate rules for standard errors and suppressed values
• "Dates continuous" is implemented per series (internal gaps), per release (all series reach the latest month) and against the calendar (no future periods, freshness)
• Accounting identities are checked with rounding-aware, two-tier tolerances
• The ingested vector is checked against the full table; revisions are measured against the published release
• Every critical check has a diagnostic query referenced in its notes
• A genuine release passes all critical checks; calibration decisions are logged

git add quality/ && git commit -m "Day 49: LFS contract — rates, continuity, identities, cross-source" && git push