Spanish Housing Radar

The problem

Spotting a genuinely under-priced flat means comparing it against the right peers — same neighbourhood, similar size — not a city-wide average. Doing that by hand across portals is impractical. I wanted a pipeline that ingests listings continuously and scores each one against local benchmarks, so a good deal is quantified, not a gut feeling.

The hard part isn't the z-score, it's the honesty. Spanish listings are sparse at neighbourhood level, and a flat compared only against its own barrio is often compared against itself — which yields a z-score of 0 and a meaningless "fair" verdict. The interesting engineering is deciding which benchmark a listing has earned, and telling the user which one it got.

Architecture

Key decisions & why

• A hierarchical benchmark, and the grain is a column. The score is a z-score of €/m², but a listing needs enough comparables for that to mean anything. So the model builds three benchmark grains and picks the finest one with at least 8 comparables — neighbourhood → district → city — always within the same operation and property type. benchmark_level records which grain actually scored each listing, and the app shows it ("scored vs city"). Rows that fall back to a thin city grain are flagged low_confidence rather than hidden.
• Neutral, not extreme, when dispersion is zero. A benchmark built from a single comparable has a standard deviation of 0 or NULL, which would divide to infinity and snap the score to ±3. Coalescing that case to 0 keeps a thin benchmark from manufacturing a fake bargain — a bug that would have looked exactly like a great find.
• Idempotent upserts on ingest. Listings reappear across daily runs. Upserting on the natural key (source_name, source_id) means re-running a flow never creates duplicates, which keeps the benchmarks honest and makes the pipeline safe to retry.
• Search-card scraping over detail-page scraping. ~1 Scrapfly credit per request versus 25–29 with JS rendering. The trade-off is no per-listing coordinates. For a benchmark engine, breadth of comparables matters more than per-listing depth.
• A free feed so the app is never dead. The INE Tempus3 house-price index is keyless and free, and grounds asking prices against transaction-based official reality. It keeps refreshing even when listing scraping is parked.

-- pick the FINEST benchmark grain with enough comparables
case
    when nb.n >= {{ min_comps }} then 'neighbourhood'
    when di.n >= {{ min_comps }} then 'district'
    else 'city'
end as benchmark_level,
-- ...then a clamped z-score, neutral when dispersion is zero
round(greatest(-3.0, least(3.0,
    coalesce((price_per_sqm - benchmark_median_ppsqm)
             / nullif(benchmark_stddev_ppsqm, 0), 0)
)), 3) as ppsqm_z_score
-- score = 50 centred, clamped 0-100
round(greatest(0, least(100, 50 - ppsqm_z_score * (50.0 / 3.0))), 1)
    as opportunity_score

Results

Every PR is validated against an isolated CI schema (ruff, pytest, dbt build), so changes to scoring logic can't silently break production data. Model and test counts come from the project's manifest.json; the same DAG is published as live dbt docs (opens in a new tab), so the lineage and every test are inspectable.

Honest status

As of July 2026: listing scraping is paused — Scrapfly credits are exhausted — so listing counts are not growing right now. The pipeline is not frozen: the keyless INE house-price-index feed refreshes the warehouse on a weekly cron, so the app keeps showing current official market context. Because coverage is thin, most listings currently benchmark at city grain, and the app says so per listing rather than implying neighbourhood precision it doesn't have.

What I'd do differently now