Lab 03 · City of Calgary open data

Where Calgary is building

Every building permit since 2015. How many homes were permitted, how long it took, and where.

New homes permittedin 2025: 22,481
Of new homes were apartmentsby units permitted in 2025: 49.6%
Median wait for a new-home permitapplications in 2025: 14 days

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

Ask the permit data

Claude writes the SQL, your browser runs it. Guardrails

Loading Ask the data…

Dashboard

Source: City of Calgary Open Data, Building Permits (c2es-76ed). Applications since 2015.

Loading Calgary permits…

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.

Incremental loads on a change watermark
CRM pipeline reports that catch deals that changed stage, as well as new deals.
Tests that warn instead of hide
Bad lead or campaign rows get flagged to an owner instead of quietly skewing a report.
A written data contract
Sales and marketing agree on what “qualified lead” means before anyone builds on it.

Decisions and trade-offs

What I chose, why, and what it cost.

Watermark on the City’s update time, merge on permit number
Why
Status changes on old permits are caught, not only new applications.
Trade-off
Each run pulls more rows than “new since yesterday”. Correct counts are worth it.
Commit the Parquet files to the repo
Why
A deploy never depends on the City’s API being up.
Trade-off
Git history grows a few MB a week. I’d move to object storage past about 100 MB.
Warnings never block a refresh. Errors always do.
Why
Real source issues stay visible without stopping fresh data.
Trade-off
Someone has to read the warnings, so the page shows them to everyone.

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: 235,585
Tests: 9 pass · 3 warn

Lineage

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

Pulls changes from data.calgary.ca right now. Changes stay in this tab.

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

stg_permitsdbt/models/permits/stg_permits.sql

235,585 rows · built in 402 ms · last production run

Typed, renamed and deduplicated. Zero coordinates become NULL. One row per permit, latest version wins.

select
    permitnum                                              as permit_id,
    cast(cast(applieddate as timestamp) as date)           as applied_date,
    cast(try_cast(issueddate as timestamp) as date)        as issued_date,
    cast(try_cast(completeddate as timestamp) as date)     as completed_date,
    statuscurrent                                          as status,
    permittypemapped                                       as permit_type,
    permitclassmapped                                      as permit_class,
    permitclassgroup                                       as class_group,
    workclassgroup                                         as work_group,
    coalesce(try_cast(housingunits as integer), 0)         as housing_units,
    try_cast(estprojectcost as double)                     as est_cost,
    try_cast(totalsqft as double)                          as sqft,
    communityname                                          as community,
    nullif(round(try_cast(latitude as double), 4), 0)      as lat,
    nullif(round(try_cast(longitude as double), 4), 0)     as lon,
    try_cast(source_updated_at as timestamp)               as updated_at
from {{ ref('raw_permits') }}
where permitnum is not null
qualify row_number() over (partition by permitnum order by source_updated_at desc) = 1

Column	Type	Null %
permit_id	VARCHAR	0
applied_date	DATE	0
issued_date	DATE	6.1
completed_date	DATE	8.5
status	VARCHAR	0
permit_type	VARCHAR	0
permit_class	VARCHAR	0
class_group	VARCHAR	0
work_group	VARCHAR	0
housing_units	INTEGER	0
est_cost	DOUBLE	10.3
sqft	DOUBLE	76.2
community	VARCHAR	0
lat	DOUBLE	0.1
lon	DOUBLE	0.1
updated_at	TIMESTAMP	0

Data quality tests

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

✓ passpermit_class is a known valueaccepted_values · permits0

Catches a new category upstream before it silently drops out of charts. severity: error

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

    select
        permit_class as value_field,
        count(*) as n_records

    from main.permits
    group by permit_class

)

select *
from all_values
where value_field not in (
    'Residential','Non-Residential','Unspecified'
)
) dbt_test

✓ passwork_group is a known valueaccepted_values · permits0

Same guard for the work type. severity: error

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

    select
        work_group as value_field,
        count(*) as n_records

    from main.permits
    group by work_group

)

select *
from all_values
where value_field not in (
    'New','Improvement','Demolition','Unspecified'
)
) dbt_test

! warnCompleted on or after issuedexpression_is_true · permits28 rows

Upstream date entry issue. Flagged so time-to-complete metrics can exclude them. severity: warn

select count(*) as failures from (
select * from main.permits where not (completed_date is null or issued_date is null or completed_date >= issued_date)
) dbt_test

✓ passhousing_units is between 0 and 2,000expression_is_true · permits0
A negative or huge unit count is a data entry error, not a building. severity: error
```
select count(*) as failures from (
select * from main.permits where not (housing_units between 0 and 2000)
) dbt_test
```
! warnIssued on or after appliedexpression_is_true · permits1 rows
A permit can't be issued before it's applied for. Flagged, not dropped. severity: warn
```
select count(*) as failures from (
select * from main.permits where not (issued_date is null or issued_date >= applied_date)
) dbt_test
```

✓ passCoordinates fall inside Calgaryexpression_is_true · permits0

Swapped or mistyped coordinates would put permits in another city. severity: error

select count(*) as failures from (
select * from main.permits where not (lat is null or (lat between 50.80 and 51.25 and lon between -114.35 and -113.80))
) dbt_test

✓ passapplied_date is never nullnot_null · permits0

Every permit has an application date. severity: error

select count(*) as failures from (
select applied_date
from main.permits
where applied_date is null
) dbt_test

! warnCoordinates are presentnot_null · permits166 rows
Some permits have no location. They stay in totals but can't be mapped. severity: warn
```
select count(*) as failures from (
select lat
from main.permits
where lat is null
) dbt_test
```

✓ passpermit_id is never nullnot_null · permits0

Every row is addressable. severity: error

select count(*) as failures from (
select permit_id
from main.permits
where permit_id is null
) dbt_test

✓ passSource updated in the last 8 daysrecency · permits0
If the City stops publishing, the refresh fails instead of serving stale data quietly. severity: error
```
select count(*) as failures from (
select max(updated_at) as latest from main.permits
having max(updated_at) < DATE '2026-10-07' - interval 8 day
) dbt_test
```
✓ passAt least 200,000 rowsrow_count_at_least · permits0
Guards against a partial extract replacing the full table. severity: error
```
select count(*) as failures from (
select count(*) as row_count from main.permits having count(*) < 200000
) dbt_test
```

✓ passpermit_id is uniqueunique · permits0

The primary key holds. severity: error

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

from main.permits
where permit_id is not null
group by permit_id
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	235,585	0.8 s	✓ 9 pass · ! 3 warn
Oct 7, 2026, 7:48 p.m.	Local	235,585	0.9 s	✓ 9 pass · ! 3 warn
Oct 7, 2026, 7:46 p.m.	Local	235,585	0.9 s	✓ 9 pass · ! 3 warn
Oct 7, 2026, 6:59 p.m.	Local	235,585	0.8 s	✓ 9 pass · ! 3 warn
Oct 7, 2026, 6:23 p.m.	Local	235,585	15.7 s	✓ 9 pass · ! 3 warn
Oct 7, 2026, 6:21 p.m.	Local	235,585	61.6 s	✓ 9 pass · ! 3 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: permits: permit_id
Consumers: Lab dashboards, Ask the data (Claude-generated SQL)
Source: City of Calgary Open Data · Building PermitsOpen Government Licence – City of Calgary

permit_id is unique and never null.
Dates are DATE values. No timestamps, no time zones.
Missing coordinates are NULL, never 0.
A failing error-level test blocks the refresh. The last good snapshot keeps serving.
Schema changes land in this repo, reviewed, before they ship.

Served files

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

permits.parquet235,585 rows · 3.19 MB
sha256 d78a1bdbad9094ff…