spb/wp22_uqo

1<!-- Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca -->2# Data dictionary — WP22 SHARP-HPI34Source snapshot: `data/raw/vraiprix-20261021.db` (SQLite `.backup` of the Vrai Prix production database,5taken 2026-10-21 00:30 on M3U96a; SHA-256 `2816d8dd…b34461`). Three tables are used: `transactions`6(769,995 rows), `units` (3,747,008 assessment-roll units), `market_index` (268 rows, the site's own7median-$/m² series). Full DDL: `docs/snapshot_schema.sql`.89## 1. `transactions` (raw layer `data/interim/raw_transactions.parquet`)1011| Column | Type | Meaning | Provenance / caveats |12|---|---|---|---|13| `id` | text | Transaction identifier (22-char random key assigned by Vrai Prix at ingestion) | unique (0 duplicates) |14| `date` | date | **Single date field** of the sale as published by the land-registry feed (Journal de Québec / Journal de Montréal "Transactions immobilières", `api.qub.ca`). The feed publishes deeds roughly 10–13 days after this date (results/vintages). We cannot distinguish deed signing from registration; see provenance report §4. | 2021-01-04 → 2026-10-07 |15| `amount` | real | Sale price in CAD, as declared in the deed | provider floor: **no sale below $50,000 is collected** |16| `street`, `city` | text | Civic address and municipality name from the feed | `city` = roll municipality name in 99.3 % of cases |17| `lat`, `lng` | real | Geocoded coordinates (WGS84/NAD83) | 100 % present; 100 % fall inside an SDA municipal polygon |18| `property_type` | text | Provider classification at transaction time: `unifamilial`, `condo`, `plex`, `indéterminé` | at-transaction information (31 % of `indéterminé` 2021 sales are lots later built on) |19| `year_built` | int | Year of construction | = roll `annee_construction` in 99.2 % of matched cases → roll-derived |20| `floor_area` | real | Floor area (m²) | = roll `aire_etages_m2` in 94.1 % of matched cases → roll-derived |21| `building_type` | text | Provider building style (`single-story`, `full-story`, `mansard`, `split-level`, `single-wide`) | **backfilled in waves** (12 % present 2021-01 → 87 % 2025-01): never used as a regressor |22| `id_provinc` | text | Provincial assessment-unit identifier (23 chars: municipality code + matricule + sequence) | present 99.1 %; a `units` row exists for 94.8 % |23| `valeur_role` | real | **Assessment in force at the sale date** (equals the roll's previous value 53–84 % of the time for 2022–2024 sales and the current value 99.7 % for 2026 sales) | used only for the ratio flag and the assessment-gap diagnostics, never as a regressor |24| `land_area` | real | Lot area (m²) | = roll `superficie_terrain_m2` in 98.1 % of matched cases |2526## 2. `units` (assessment roll, matched layer `raw_units_matched.parquet`, stock layer `raw_units_stock.parquet`)2728Single **current** vintage of each municipality's roll (reference dates `dat_cond_marche` 2022-07-01, 2023-07-01 or292024-07-01 for 99.8 % of matched sales; the triennial rolls in force in 2026). Characteristics are therefore30*post-sale* for most transactions: a lot sold in 2021 and built on in 2022 now carries the house's attributes31(rule R1 `built_after_sale` removes those cases), and renovations after the sale are not observable.3233| Column | Meaning | Use |34|---|---|---|35| `id_provinc`, `matricule`, `code_mun`, `municipalite`, `arrond` | identifiers; `code_mun` = MAMH 5-digit geographic code | join key; municipality cross-check (99.96 % agreement with the spatial join) |36| `cubf`, `cubf_libelle` | Code d'utilisation des biens-fonds (1000 dwelling, 1100 cottage, 1211 mobile home, 19xx residential other, 5834 tourist residence, 9100 vacant land, …) | classification |37| `type_prop` | Vrai Prix roll class (`unifamilial`, `plex`, `condo_ou_multi`, `chalet`, `terrain`, `maison_mobile`, `autre`) | land detection only (condos are coded `unifamilial` + `Intégré`) |38| `annee_construction`, `annee_estimee` (`R` = real) | year built | age |39| `aire_etages_m2` | gross floor area of storeys (m²) | floor area |40| `superficie_terrain_m2`, `front_terrain_m` | lot area, frontage | land |41| `nb_etages`, `nb_logements`, `nb_locaux_non_resid`, `nb_chambres_locatives` | storeys, dwelling units, non-residential premises, rental rooms | classification, controls |42| `lien_physique` | physical link: `Détaché`, `Jumelé`, `En rangée 1 côté`, `En rangée plus de 1 côté`, `Intégré` | house subtype / condo |43| `genre_construction` | `De plain-pied`, `À étages entiers`, `À niveaux décalés`, `À étage mansardé`, `Unimodulaire`, … | control |44| `unite_voisinage` | code stored by the provider (unit-level: 379k distinct values for 449k matched sales) | **not usable** as a neighbourhood key; kept for record |45| `valeur_terrain`, `valeur_batiment`, `valeur_role`, `valeur_anterieure` | current roll values | land/building decomposition diagnostics, stock weights |46| `est_*`, `p10`, `p90`, `est_hedo` | Vrai Prix's own estimates | **not used** |4748## 3. Variables that are NOT available49Bedrooms, bathrooms, parking/garage, basement, construction quality grade, renovation history, waterfront,50occupancy, transaction conditions (financing, arm's-length status, seller type), buyer/seller identity, deed51type, listing information, images. Arm's-length status is therefore inferred from price screens only (§10 of52the protocol). No imagery-derived characteristics are used in the headline index.5354## 4. Geography layer (`data/interim/geo_transactions.parquet`)55From the MRNF SDA 1:20 000 boundaries, version V2026-07 (`data/external/SDA_SOURCE.md`): `mun_code` (5-digit56geocode), `mrc_code` (2-digit), `region_code` (2-digit administrative region), `comet_code` (metropolitan57community CMM 663 / CMQ 664), `borough_code`; MAMH register (`MUN.csv`, 2026) adds population, designation,58constitution date. Fine location: nested metric grid cells `grid500`, `grid1k`, `grid2k`, `grid5k` (NAD83 / Quebec Lambert, EPSG:32198);59the estimator picks, per class, the finest cell with at least `min_n` sales (see `hedonic.assign_loc_fe`).60Census metropolitan areas (Statistics Canada) could not be downloaded (bot check); CMM/CMQ are used instead.6162## 5. Cleaned layer (`data/processed/clean_transactions.parquet`) — added columns63`property_class` ∈ {house, condo, plex, multi, cottage, mobile, null}, `class_source` (roll / provider / none),64`subtype` (detached, semi, row, integrated), `type_conflict`, `built_after_sale`, `new_construction`,65`roll_is_land`, `roll_non_residential`, `exact_duplicate`, `same_parcel_same_date_diff_price`,66`multi_parcel_candidate`, `shared_parcel`, `parcel_sales_2021_2026`, `assessment_at_sale`,67`assessment_ratio_outlier`, `price_above_cap`, `floor_area_missing`, `year_built_missing`,68`exclude_reason` (first matching rule), `exclude_reason_headline`, `in_headline_sample`.6970## 6. Analytic layer (`data/processed/analytic_transactions.parquet`)71Headline sample only; adds `age`, `floor_area_m2`, `land_area_m2`, `storeys`, `n_units`, `genre`.72