Lab 02 · Machine learning

What’s a Calgary home worth?

Every home’s 2026 assessment, and a value model trained in Python. It explains each estimate.

Median detached home2026 assessment: $719K
Homes assessed at $1M or moreof all homes: 9.2%
Highest-value communityby median detached value: Bel-Aire

Computed by the pipeline on Oct 7, 2026. See how

Ask the housing data

Claude writes the SQL, your browser runs it. Guardrails

Loading Ask the data…

Dashboard

Every home in Calgary has a 2026 assessed value: the City’s estimate of what it would have sold for on July 1, 2025. Here are about 488,000 of them, by community, type and age. Calgary’s sale prices aren’t open data, so assessments are the best public view of home values.

Source: City of Calgary Open Data, Current Year Property Assessments (4bsw-nn7w) joined to the City's property use codes (5843-8tyj). 2026 roll.

Loading Calgary housing…

Value model

A machine-learning model that estimates a home’s assessed value from five public facts: community, property type, zoning, year built and lot size. It explains every estimate, and it’s judged against the obvious baseline, not against nothing.

It’s XGBoost, trained in Python. The same Python file trains in the weekly pipeline and, through Pyodide, in your browser.

Loading value model…

Why it matters

Anyone can generate a dashboard now. The value is knowing what to build, and what it’s for.

In a sales or marketing team

The same techniques, applied to revenue data.

An explainable model, scored against a simple rule
Lead and churn scores reps trust, because each score shows why and beats the rule of thumb.
A fixed holdout set
Honest results for a model or campaign, measured on customers it never saw.
Retraining in minutes
Analysts test an idea the same afternoon instead of filing an engineering ticket.

Decisions and trade-offs

What I chose, why, and what it cost.

Train in Python with XGBoost, score in TypeScript
Why
One Python file trains weekly and in your browser. Scoring needs no Python download.
Trade-off
Two languages to keep in step. A parity test checks they agree.
Encode community and zoning out of fold
Why
A home’s own value can’t leak into its features.
Trade-off
Training is slower. Without it, the accuracy numbers would be inflated.
Predict the assessed value, and say so plainly
Why
Sale prices aren’t open data in Calgary.
Trade-off
Less exciting than a “price predictor”, but it’s true.

Pipeline

Every number above comes from this pipeline: dbt models in raw, cleaned and business-ready layers, with tests and a contract. An Airflow DAG runs it weekly. You can run it in your browser too.

Last production run: 2026-10-07
Trigger: Local run
Duration: 0.8 s
Rows extracted: 490,373
Tests: 7 pass · 2 warn

Lineage

Click a node for its SQL, schema and row counts.

Checks the NHL for games newer than the snapshot. Changes stay in this tab.

1 Source
→
2 Bronze
→
3 Silver
→
4 Gold
→
5 Checks
→
6 Model
→
7 Browser

stg_homesdbt/models/housing/stg_homes.sql

487,726 rows · built in 403 ms · last production run

Typed and labelled. Use codes joined to the City's own descriptions, zoning reduced to its primary district, and the 1800 placeholder for an unknown build year turned into NULL.

-- Homes only: no parking stalls, storage units, vacant land or whole rental buildings.
select
    try_cast(a.roll_number as bigint)                               as roll_number,
    try_cast(a.roll_year as integer)                                as roll_year,
    a.comm_name                                                     as community,
    a.sub_property_use                                              as use_code,
    u.description                                                   as use,
    case a.sub_property_use
        when 'R110' then 'Detached' when 'R111' then 'Detached'
        when 'R120' then 'Duplex'
        when 'R401' then 'Townhouse' when 'R402' then 'Townhouse'
        when 'R201' then 'Condo apartment' when 'R301' then 'Condo apartment'
    end                                                             as property_group,
    nullif(trim(split_part(a.land_use_designation, ',', 1)), '')    as zoning,
    -- The City records 1800 when the build year is unknown.
    nullif(try_cast(try_cast(a.year_of_construction as double) as integer), 1800) as year_built,
    try_cast(try_cast(a.land_size_sf as double) as integer)         as lot_sqft,
    try_cast(try_cast(a.assessed_value as double) as integer)       as assessed_value,
    cast(try_cast(a.mod_date as timestamp) as date)                 as mod_date
from {{ ref('raw_assessments') }} a
left join {{ ref('raw_use_codes') }} u on u.code = a.sub_property_use
qualify row_number() over (partition by a.roll_number order by a.mod_date desc) = 1

Column	Type	Null %
roll_number	BIGINT	0
roll_year	INTEGER	0
community	VARCHAR	0
use_code	VARCHAR	0
use	VARCHAR	0
property_group	VARCHAR	0
zoning	VARCHAR	0
year_built	INTEGER	0.2
lot_sqft	INTEGER	0.1
assessed_value	INTEGER	0
mod_date	DATE	0

Data quality tests

From the last production run. Warnings are real issues in the source, flagged rather than hidden.

✓ passproperty_group is a known valueaccepted_values · housing_homes0

Only the home types the model and dashboard expect. severity: error

select count(*) as failures from (
with all_values as (

    select
        property_group as value_field,
        count(*) as n_records

    from main.housing_homes
    group by property_group

)

select *
from all_values
where value_field not in (
    'Detached','Duplex','Townhouse','Condo apartment'
)
) dbt_test

✓ passOne assessment roll yearaccepted_values · housing_homes0

Mixing two roll years would blend two years of values into one model. severity: error

select count(*) as failures from (
with all_values as (

    select
        roll_year as value_field,
        count(*) as n_records

    from main.housing_homes
    group by roll_year

)

select *
from all_values
where value_field not in (
    2026
)
) dbt_test

! warnAssessed value is plausible ($50k to $20M)expression_is_true · housing_homes8 rows
Outliers are kept but flagged. The model trains on log value, which limits their pull. severity: warn
```
select count(*) as failures from (
select * from main.housing_homes where not (assessed_value between 50000 and 20000000)
) dbt_test
```

✓ passYear built is plausibleexpression_is_true · housing_homes0

Catches zeros and typos in construction year. severity: warn

select count(*) as failures from (
select * from main.housing_homes where not (year_built is null or year_built between 1875 and year(DATE '2026-10-07') + 1)
) dbt_test

✓ passroll_number is never nullnot_null · housing_homes0
Every home is addressable for incremental merges. severity: error
```
select count(*) as failures from (
select roll_number
from main.housing_homes
where roll_number is null
) dbt_test
```
✓ passEvery use code has a City labelnot_null · housing_homes0
If the City adds a code, the join fails loudly instead of producing unlabelled homes. severity: error
```
select count(*) as failures from (
select use
from main.housing_homes
where use is null
) dbt_test
```
! warnYear built is presentnot_null · housing_homes826 rows
Missing years are kept. The model routes missing values down their own branch. severity: warn
```
select count(*) as failures from (
select year_built
from main.housing_homes
where year_built is null
) dbt_test
```
✓ passAt least 400,000 homesrow_count_at_least · housing_homes0
Guards against a partial extract replacing the table and retraining the model on it. severity: error
```
select count(*) as failures from (
select count(*) as row_count from main.housing_homes having count(*) < 400000
) dbt_test
```

✓ passroll_number is uniqueunique · housing_homes0

One row per assessed property. severity: error

select count(*) as failures from (
select
    roll_number as unique_field,
    count(*) as n_records

from main.housing_homes
where roll_number is not null
group by roll_number
having count(*) > 1
) dbt_test

Run history

The last 6 production runs, newest first. Kept in manifest.json.

Run	Trigger	Rows in	Duration	Tests
Oct 7, 2026, 8:25 p.m.	Local	490,373	0.8 s	✓ 7 pass · ! 2 warn
Oct 7, 2026, 7:48 p.m.	Local	490,373	0.8 s	✓ 7 pass · ! 2 warn
Oct 7, 2026, 7:46 p.m.	Local	490,373	0.8 s	✓ 7 pass · ! 2 warn
Oct 7, 2026, 6:59 p.m.	Local	490,373	0.7 s	✓ 7 pass · ! 2 warn
Oct 7, 2026, 6:24 p.m.	Local	490,373	53.4 s	✓ 7 pass · ! 2 warn
Oct 7, 2026, 6:22 p.m.	Local	490,373	96.6 s	✓ 7 pass · ! 2 warn

Data contract

What consumers of this data can rely on. The tests above enforce every line of it.

Owner: Cody Chandler
Refresh: Weekly, Mondays at 10:00 UTC, via Airflow on GitHub Actions
Freshness SLA: 8 days
Keys: housing_homes: roll_number
Consumers: Lab dashboards, Value model, Ask the data (Claude-generated SQL)
Source: City of Calgary Open Data · Current Year Property AssessmentsOpen Government Licence – City of Calgary

Values are the City's 2026 assessments (market value as of July 1, 2025), not sale prices.
Homes only: detached, duplex, townhouse and condo apartment units.
No addresses or owner details are served to the browser.
The model retrains only after every error-level test passes.

Served files

Gold models written as Parquet and committed, so a deploy never depends on a third-party API.

housing-homes.parquet487,604 rows · 2.55 MB
sha256 3d15f504a35d7b69…