Staging and cleaning rules
Built by
scripts/build_staging.py into data/staging.duckdb (rents: data/staging_rents.duckdb).
Inputs and safety checks
- Inputs are exactly the raw files listed in
data/raw/manifest.json(written byscripts/download.py), never a glob of the raw folder. Every dataset inscripts/datasets.jsonis required; a missing dataset or file, an empty file, a size different from the one recorded at download, or files from two different snapshots of one dataset stop the build before anything is written. - The database is built into a new file and moved over the old one only after the checks below pass, so a failed build never leaves a half-written staging file and a rebuild does not grow the file.
transaction_idmust be unique (a doubled snapshot would otherwise double every count), and the share of values that fail type conversion must stay small (transactions: date and price 1%, area 5%; rents: start date and rent 1%, area 25%), otherwise the build stops: the source format has probably changed.- Dates are judged against the snapshot date of the dataset (from the file names), not the date of the run, so a rebuild of the same snapshot always gives the same flags.
- Quality flags are never NULL: a value that cannot be parsed counts as not OK.
- DuckDB limits:
WIZI_DUCKDB_MEMORY(default 1500MB) andWIZI_DUCKDB_THREADS(default 2).
Transactions (DLD 470061)
- One row per
transaction_id; ids are unique in the source. - Date:
instance_date(registration date). Rows outside 2000-01-01..snapshot date are flaggedq_date_ok = false(the source contains impossible years such as 1416, likely Hijri dates). - Group: Sales, Mortgages, Gifts from
trans_group_en. - Registration:
Off-Plan Properties= Oqood (off-plan),Existing Properties= Title Deed (ready). - Segment: Unit+Flat = Apartment; Unit+Stacked Townhouses = Townhouse; Unit+Hotel Apartment/Hotel Rooms = Hotel Apartment; property type Villa = Villa (the source does not separate villas from townhouses); Land; Building; Office; Shop; Other.
- Bedrooms: parsed from
rooms_en("Studio" = 0, "N B/R" = N). Missing for many villas. - Area:
procedure_areais in square metres; converted with 1 m² = 10.7639 sq ft. Price per sq ft =actual_worth/ area in sq ft. - Quality flags: price >= AED 10,000 (
q_price_ok), area between 100 and 2,000,000 sq ft (q_area_ok). - Residential sales view
sales_residential: Sales group, Residential usage, segment Apartment/Villa/Townhouse, all quality flags true.
Rents (DLD 468586, Ejari)
- Deduplicated on
contract_id+line_number, keeping the latest load. The source has no duplicates today, so the check costs one count; when duplicates appear, only the duplicated keys are resolved (latestload_timestamp). - Staging keeps every Ejari line (one per property of a contract);
rents_market,yieldsand the commercial rents ofcommercial_marketcount one row percontract_id(its lowestline_number), because the second line of a one-property contract repeats the first. - Start dates later than the snapshot date + 400 days are flagged
q_date_ok = false. - Month = contract start month. Annual rent =
annual_amount. Contract type New / Renew. - Bedrooms parsed from the Ejari property sub type. Quality flags as above with rent >= AED 1,000.
Other tables
Projects, developers, service charges, valuations, units, buildings and lookups are typed copies of the source with no row filtering except valuations (dates outside 2000..snapshot date dropped).