Inflation Adjustment with CPI — Lessons

Nominal vs. Real Wages

Comparing $50,000 in 2021 to $52,000 in 2023 looks like a 4% raise. But if inflation was 10% over that period, the worker's purchasing power actually fell. Nominal wages are dollar amounts as published by the source; real wages adjust for inflation to enable meaningful comparison across time periods.

Without inflation adjustment, a multi-vintage wage trend chart is misleading. Every occupation appears to have rising wages simply because the dollar is worth less each year. Real wages separate genuine labor market shifts from background price-level changes.

The CPI-U Deflation Formula

The Bureau of Labor Statistics publishes the Consumer Price Index for All Urban Consumers (CPI-U), which measures average price changes over time. The pipeline uses CPI-U annual averages to convert nominal wages into constant dollars.

real_wage = nominal_wage × (CPI_base_year / CPI_observation_year)

Example:
  Nominal wage 2021:     $50,000
  CPI-U 2021:            270.970
  CPI-U 2023 (base):     304.702
  Real wage (2023$):     $50,000 × (304.702 / 270.970) = $56,226

CPI_BASE_YEAR = 2023 in the project. All real wages are expressed in 2023 dollars. The formula scales older wages up (since prices were lower in earlier years) and leaves the base year unchanged. A 2023 nominal wage equals its real wage because the ratio is 1.0.

The SQL Pattern

The compute_real_wages function writes inflation-adjusted values into fact_derived_series using a single INSERT…SELECT that joins wage observations with their corresponding CPI values through the time dimension.

INSERT INTO fact_derived_series (...)
SELECT
    real_metric_key,
    nominal_metric_key,
    obs.occupation_key,
    obs.geography_key,
    obs.period_key,
    obs.comparability_mode,
    ROUND(obs.observed_value * (base_cpi / fpi.index_value), 0),
    'cpi_deflation',
    run_id
FROM fact_time_series_observation obs
JOIN dim_time_period tp ON obs.period_key = tp.period_key
JOIN fact_price_index_observation fpi
    ON fpi.period_key = obs.period_key
WHERE obs.metric_key = nominal_metric_key
  AND obs.observed_value IS NOT NULL
  AND fpi.index_value > 0

The join through dim_time_period aligns each wage observation with its corresponding CPI value for the same year. The WHERE clause guards against division by zero (fpi.index_value > 0) and NULL propagation (obs.observed_value IS NOT NULL). The result is rounded to whole dollars since sub-dollar precision in annual wages is spurious.

Base Year Selection

The project uses 2023 as the base year because it is the latest complete year with published CPI-U data. All real wage values are denominated in "2023 dollars," which makes them directly comparable to the most recent nominal wages users see in current BLS publications.

Important: Changing the base year shifts all real values proportionally but doesn't change the relative ordering or growth rates. A $56,226 real wage in 2023 dollars becomes roughly $52,000 in 2021 dollars — same purchasing power, different unit.

If the project later ingests 2024 CPI data, the base year constant can be updated and all derived series recomputed. Because fact_derived_series is fully regenerated on each time-series refresh, there is no migration — just change the constant and re-run.

Two Derived Wage Metrics

The pipeline produces two inflation-adjusted metrics, each paired with its nominal counterpart:

Metric	Base Metric	Formula
real_mean_annual_wage	mean_annual_wage	nominal × (CPI_2023 / CPI_year)
real_median_annual_wage	median_annual_wage	nominal × (CPI_2023 / CPI_year)

Both are stored in fact_derived_series with derivation_method = 'cpi_deflation'. They are never mixed with base observations in fact_time_series_observation — the separation ensures that downstream queries always know whether they are working with nominal or real values based on which table they read from.