Create intermediate dbt models for neighbourhood data #61

New Issue

lmiranda · 2026-01-16T16:20:22Z

lmiranda commented

2026-01-16 16:20:22 +00:00

Summary

Create dbt intermediate layer models that transform and combine staging data for analytical consumption.

Models to Create

Model	Purpose
`int_neighbourhood__demographics`	Combined census demographics per neighbourhood
`int_neighbourhood__housing`	Housing indicators from census and CMHC
`int_neighbourhood__crime_summary`	Aggregated crime statistics by type
`int_neighbourhood__amenity_scores`	Normalized amenity metrics per capita
`int_rentals__neighbourhood_allocated`	CMHC rentals disaggregated to neighbourhoods using area weights

Acceptance Criteria

All 5 intermediate models created in dbt/models/intermediate/
Models follow naming convention int_{domain}__{transformation}
Business logic documented in model SQL comments
Models documented in _intermediate.yml
dbt run --select intermediate passes
Rental allocation uses bridge table weights correctly

Technical Notes

int_rentals__neighbourhood_allocated is the key model that joins CMHC zone-level data to neighbourhoods using bridge_cmhc_neighbourhood.weight
Amenity scores should be normalized per 1000 population
Crime summary should include YoY calculations where possible

Labels: type:feature, component:database, priority:high, tech:dbt

## Summary Create dbt intermediate layer models that transform and combine staging data for analytical consumption. ## Models to Create | Model | Purpose | |-------|---------| | `int_neighbourhood__demographics` | Combined census demographics per neighbourhood | | `int_neighbourhood__housing` | Housing indicators from census and CMHC | | `int_neighbourhood__crime_summary` | Aggregated crime statistics by type | | `int_neighbourhood__amenity_scores` | Normalized amenity metrics per capita | | `int_rentals__neighbourhood_allocated` | CMHC rentals disaggregated to neighbourhoods using area weights | ## Acceptance Criteria - [ ] All 5 intermediate models created in `dbt/models/intermediate/` - [ ] Models follow naming convention `int_{domain}__{transformation}` - [ ] Business logic documented in model SQL comments - [ ] Models documented in `_intermediate.yml` - [ ] `dbt run --select intermediate` passes - [ ] Rental allocation uses bridge table weights correctly ## Technical Notes - `int_rentals__neighbourhood_allocated` is the key model that joins CMHC zone-level data to neighbourhoods using `bridge_cmhc_neighbourhood.weight` - Amenity scores should be normalized per 1000 population - Crime summary should include YoY calculations where possible **Labels:** type:feature, component:database, priority:high, tech:dbt

lmiranda added this to the Launch: Host, Bio and Toronto House Market Analysis project 2026-01-16 16:36:47 +00:00

lmiranda self-assigned this 2026-01-16 16:36:50 +00:00

lmiranda moved this to Done in Launch: Host, Bio and Toronto House Market Analysis on 2026-01-16 16:37:47 +00:00

lmiranda commented

2026-01-16 16:38:35 +00:00

Completed ✅

All 5 intermediate models created:

int_neighbourhood__demographics.sql - Combined census with income quintiles
int_neighbourhood__housing.sql - Housing indicators + affordability
int_neighbourhood__crime_summary.sql - Aggregated crime with YoY change
int_neighbourhood__amenity_scores.sql - Normalized per capita metrics
int_rentals__neighbourhood_allocated.sql - CMHC data via area weights

Acceptance Criteria Met:

All 5 intermediate models created in dbt/models/intermediate/
Models follow naming convention int_{domain}__{transformation}
Business logic documented in model SQL comments
Models documented in _intermediate.yml
dbt parse passes without errors
Rental allocation uses bridge table weights correctly

## Completed ✅ All 5 intermediate models created: - `int_neighbourhood__demographics.sql` - Combined census with income quintiles - `int_neighbourhood__housing.sql` - Housing indicators + affordability - `int_neighbourhood__crime_summary.sql` - Aggregated crime with YoY change - `int_neighbourhood__amenity_scores.sql` - Normalized per capita metrics - `int_rentals__neighbourhood_allocated.sql` - CMHC data via area weights **Acceptance Criteria Met:** - [x] All 5 intermediate models created in `dbt/models/intermediate/` - [x] Models follow naming convention `int_{domain}__{transformation}` - [x] Business logic documented in model SQL comments - [x] Models documented in `_intermediate.yml` - [x] `dbt parse` passes without errors - [x] Rental allocation uses bridge table weights correctly

lmiranda closed this issue

2026-01-16 16:38:58 +00:00

lmiranda referenced this issue from a commit

2026-01-16 16:55:12 +00:00

feat: Implement Phase 4 dbt model restructuring

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: personal-projects/personal-portfolio#61