case study / system 04

Lima Real Estate Pipeline

A reproducible pipeline for transforming public real estate data, applying quality checks, loading a dimensional model and communicating findings through a dashboard.

Streamlit dashboard · demo pending validation Python ETL PostgreSQL
architecture / lima.datapipeline
PUBLIC
SOURCE
PYTHON
ETL
DASHBOARD
Streamlit
QUALITY
IQR
STAR
SCHEMA
PLOTLY
insights
01 / context

Turning scattered listings into questions we can analyze

Lima's real estate market has information spread across public listings and formats that are difficult to analyze directly. The project aims to build a complete workflow—from extraction to visualization—to answer questions about prices, districts, property types and listing features.

4layers: extraction, ETL, data and dashboard
IQRstatistical rule for flagging outliers
UPSERTrepeatable loading without duplicates

Users and scope

Designed as a portfolio and analysis project: technical users can review the pipeline, while business users can explore aggregated results through filters and charts.

02 / pipeline

From public data to business insight

Extraction

Retrieving public information and parsing embedded structures to create a reproducible working layer.

Transformation

Flattening structures, normalizing prices and features, and preparing analytical tables.

Storage

PostgreSQL with dimensions and facts for querying properties, locations, advertisers and metrics.

Visualization

A Streamlit/Plotly dashboard with rankings, distributions, a map, property types and linked filters.

03 / architecture

A dimensional model for reproducible analysis

Separating layers allows transformation and analysis without distributing raw data or tying the dashboard to one specific snapshot. The public repository contains transformation code, the schema, aggregated results and an analysis notebook.

public listings
      │
      ▼
Python extraction / parsing
      │
      ▼
transform + quality rules
      │
      ├── valid rows / flagged outliers
      │
      ▼
PostgreSQL Star Schema
      │
      ▼
Streamlit + Plotly dashboard

Published repository: aggregated data + ETL + schema + analysis
PythonPandasRequestsPostgreSQLSQLAlchemyStreamlitPlotlyJupyter
04 / data quality

Flagging problems while preserving history

One key decision was to avoid automatically deleting price outliers. The pipeline calculates IQR-based limits, adds a validity flag and makes analytical queries use only records marked valid. This preserves traceability and avoids presenting source errors as market conclusions.

  • UPSERT loading lets the process run more than once without duplicating entities.
  • The dimensional model separates location and advertiser dimensions from the fact table.
  • The repository does not include raw data or the complete automated collection code; it publishes aggregated results and analysis layers.
05 / workflow

AI-assisted technical planning

This repository includes a versioned technical planning prompt in `.github/prompts/plan-urbaniaDatos.prompt.md`. AI helped structure the plan, explore decisions and accelerate documentation; the data model, quality rules and results were reviewed manually.

Before the code

Planning helped separate extraction, transformation, storage, analysis and publication.

After the code

Results were validated through queries, aggregated metrics and reviewing the dashboard filters' behavior.

06 / evidence

Visible results and demo status

  • The README documents aggregated findings on prices, districts, property types and feature distributions.
  • The repository includes `schema.sql`, `load_db.py`, `app.py`, a notebook, aggregated CSV files and a dashboard GIF.
  • A Streamlit link exists, but public availability should be checked before presenting it as a reliable demo.
  • The documentation preserves the academic scope and avoids publishing the original source's raw data.
07 / roadmap

Making the pipeline easier to reproduce

The next stage is to restore a stable demo, add automated schema and quality checks, document a small test dataset and clearly separate local analysis from public visualization.