# Data dictionary — WP22 SHARP-HPI Source snapshot: `data/raw/vraiprix-20261021.db` (SQLite `.backup` of the Vrai Prix production database, taken 2026-10-21 00:30 on M3U96a; SHA-256 `2816d8dd…b34461`). Three tables are used: `transactions` (769,995 rows), `units` (3,747,008 assessment-roll units), `market_index` (268 rows, the site's own median-$/m² series). Full DDL: `docs/snapshot_schema.sql`. ## 1. `transactions` (raw layer `data/interim/raw_transactions.parquet`) | Column | Type | Meaning | Provenance / caveats | |---|---|---|---| | `id` | text | Transaction identifier (22-char random key assigned by Vrai Prix at ingestion) | unique (0 duplicates) | | `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 | | `amount` | real | Sale price in CAD, as declared in the deed | provider floor: **no sale below $50,000 is collected** | | `street`, `city` | text | Civic address and municipality name from the feed | `city` = roll municipality name in 99.3 % of cases | | `lat`, `lng` | real | Geocoded coordinates (WGS84/NAD83) | 100 % present; 100 % fall inside an SDA municipal polygon | | `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) | | `year_built` | int | Year of construction | = roll `annee_construction` in 99.2 % of matched cases → roll-derived | | `floor_area` | real | Floor area (m²) | = roll `aire_etages_m2` in 94.1 % of matched cases → roll-derived | | `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 | | `id_provinc` | text | Provincial assessment-unit identifier (23 chars: municipality code + matricule + sequence) | present 99.1 %; a `units` row exists for 94.8 % | | `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 | | `land_area` | real | Lot area (m²) | = roll `superficie_terrain_m2` in 98.1 % of matched cases | ## 2. `units` (assessment roll, matched layer `raw_units_matched.parquet`, stock layer `raw_units_stock.parquet`) Single **current** vintage of each municipality's roll (reference dates `dat_cond_marche` 2022-07-01, 2023-07-01 or 2024-07-01 for 99.8 % of matched sales; the triennial rolls in force in 2026). Characteristics are therefore *post-sale* for most transactions: a lot sold in 2021 and built on in 2022 now carries the house's attributes (rule R1 `built_after_sale` removes those cases), and renovations after the sale are not observable. | Column | Meaning | Use | |---|---|---| | `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) | | `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 | | `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é`) | | `annee_construction`, `annee_estimee` (`R` = real) | year built | age | | `aire_etages_m2` | gross floor area of storeys (m²) | floor area | | `superficie_terrain_m2`, `front_terrain_m` | lot area, frontage | land | | `nb_etages`, `nb_logements`, `nb_locaux_non_resid`, `nb_chambres_locatives` | storeys, dwelling units, non-residential premises, rental rooms | classification, controls | | `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 | | `genre_construction` | `De plain-pied`, `À étages entiers`, `À niveaux décalés`, `À étage mansardé`, `Unimodulaire`, … | control | | `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 | | `valeur_terrain`, `valeur_batiment`, `valeur_role`, `valeur_anterieure` | current roll values | land/building decomposition diagnostics, stock weights | | `est_*`, `p10`, `p90`, `est_hedo` | Vrai Prix's own estimates | **not used** | ## 3. Variables that are NOT available Bedrooms, bathrooms, parking/garage, basement, construction quality grade, renovation history, waterfront, occupancy, transaction conditions (financing, arm's-length status, seller type), buyer/seller identity, deed type, listing information, images. Arm's-length status is therefore inferred from price screens only (§10 of the protocol). No imagery-derived characteristics are used in the headline index. ## 4. Geography layer (`data/interim/geo_transactions.parquet`) From the MRNF SDA 1:20 000 boundaries, version V2026-07 (`data/external/SDA_SOURCE.md`): `mun_code` (5-digit geocode), `mrc_code` (2-digit), `region_code` (2-digit administrative region), `comet_code` (metropolitan community CMM 663 / CMQ 664), `borough_code`; MAMH register (`MUN.csv`, 2026) adds population, designation, constitution date. Fine location: nested metric grid cells `grid500`, `grid1k`, `grid2k`, `grid5k` (NAD83 / Quebec Lambert, EPSG:32198); the estimator picks, per class, the finest cell with at least `min_n` sales (see `hedonic.assign_loc_fe`). Census metropolitan areas (Statistics Canada) could not be downloaded (bot check); CMM/CMQ are used instead. ## 5. Cleaned layer (`data/processed/clean_transactions.parquet`) — added columns `property_class` ∈ {house, condo, plex, multi, cottage, mobile, null}, `class_source` (roll / provider / none), `subtype` (detached, semi, row, integrated), `type_conflict`, `built_after_sale`, `new_construction`, `roll_is_land`, `roll_non_residential`, `exact_duplicate`, `same_parcel_same_date_diff_price`, `multi_parcel_candidate`, `shared_parcel`, `parcel_sales_2021_2026`, `assessment_at_sale`, `assessment_ratio_outlier`, `price_above_cap`, `floor_area_missing`, `year_built_missing`, `exclude_reason` (first matching rule), `exclude_reason_headline`, `in_headline_sample`. ## 6. Analytic layer (`data/processed/analytic_transactions.parquet`) Headline sample only; adds `age`, `floor_area_m2`, `land_area_m2`, `storeys`, `n_units`, `genre`.