🌊 Paris Flood Forecasting Pipeline

An end-to-end data engineering pipeline and machine learning framework designed to predict early-warning flood risks for gauging stations along the Seine River basin, using daily hydrometric data.

---

📌 Project Overview & Current Status

This repository is organized into two primary modules:

Module	Status	Description
Data Engineering (data_engineering/)	✅ Completed	An idempotent, incremental ETL pipeline that ingests raw hydrometric readings from France's Hub'Eau API, cleans and standardizes the dataset, and performs SQL MERGE upserts into a local SQL Server data warehouse.
Data Science (data_science/)	🚧 In Progress	A multi-horizon binary classification framework predicting 3-day, 7-day, and 14-day flood risk per gauging station. Models are tracked via MLflow and served through a Gradio web application.

---

🏗️ System Architecture

The system flows from raw upstream observations through a staging/curated SQL Server warehouse and into the modeling layer, with live predictions logged back to the warehouse for monitoring.

• Hub'Eau API — France's open hydrometric data platform, the primary upstream source of daily water-level readings.
• ETL Pipeline — Extracts, transforms, and loads readings into a staging table, then MERGE-upserts them into the curated table.
• SQL Server Warehouse — Holds paris_flood_dataset_staging (replaced each run), paris_flood_dataset (curated, deduplicated), and prediction_log (live predictions with backfilled actuals).
• Data Science Layer — Reads curated data for training (load_training_data()), serves predictions, and writes them back via log_prediction(), with compute_prediction_error() reconciling predictions against backfilled actuals.

---

⚙️ Data Engineering Pipeline (ETL)

The ETL pipeline operates on an incremental, idempotent execution model, safely syncing upstream observations without creating duplicate historical records.

extract.py

• load_existing_data() — reads the current curated table to establish a baseline.
• determine_update_range() — compares the last recorded date against yesterday to compute a start_date.
• fetch_all_data() — paginates per station against the Hub'Eau API (or a mock source), fetching only new rows.

transform.py

• remove_duplicates() — in-memory deduplication on the (station, date, value) key.
• rename_to_english() — maps the French Hub'Eau schema to an English column schema.
• postprocess() — derives types, categories, the flood_alert flag, and enforces row ordering.

load.py

• write_to_staging() — replaces the staging table on every run.
• merge_staging_into_curated() — performs a SQL MERGE upsert into the curated table, keyed on station_code + record_date.

The result is written to dbo.paris_flood_dataset, a curated table with a UNIQUE constraint on station_code + record_date — the next run reads this table back in, closing an idempotent loop.

---

🎯 Machine Learning Problem Statement

Goal: Predict flood risk at 5 independent gauging stations with actionable lead times of 3, 7, and 14 days in advance — rather than same-day reporting — giving emergency planners and infrastructure managers real time to act.

Each station is modeled independently, since station-specific hydrological behavior (baseline levels, rainfall response, local geography) does not necessarily generalize across stations.

Target Variable

For each station $s$ and forecast horizon $h \in {3, 7, 14}$ days, the target is a binary indicator:

y(s, t, h) = 1   if water_level_mm(s, t+h) > 6000 mm
           = 0   otherwise

where $t$ is the reference ("as-of") date and $t+h$ is the target date, $h$ days later, at the same station. In the warehouse schema this corresponds to the derived column flood_alert_in_{h}d, computed by shifting the existing flood_alert flag $h$ days into the past relative to its originating date.

This yields 15 independent binary classification models in total (5 stations × 3 horizons). Predictors are restricted to information available strictly before the reference date $t$ (lagged and rolling statistics of water_level_mm, plus calendar features), so that no target-date information leaks into the features.

Data Sources

Source	Description
Hub'Eau API	Primary upstream source — France's open hydrometric data platform, providing daily elaborated water-level observations (HIXnJ: daily maximum) per gauging station.
ETL pipeline	Idempotent, incremental extraction/transformation pipeline that pulls new observations, translates/cleans the schema, deduplicates, and computes the flood_alert flag.
SQL Server warehouse (dbo.paris_flood_dataset)	Curated table of record: station code, record date, water level, flood alert, observation metadata, and station geolocation. Single source of truth for all training and feature engineering.
dbo.prediction_log	Captures every live prediction (station, horizon, predicted outcome, latency, and later-backfilled actual outcome) for post-deployment monitoring and error analysis.

Success Metrics

Because flood events are rare relative to normal-flow days, target classes are significantly imbalanced — accuracy alone would be misleading (a model that always predicts "no flood" would score deceptively well). Models are instead evaluated on:

Metric	Role
Recall (Sensitivity)	Primary metric. Missing a real flood (false negative) is far costlier than a false alarm, given the early-warning goal.
Precision	Tracked alongside recall to avoid a model that over-predicts floods indiscriminately, eroding trust in alerts.
F1-Score	Harmonic mean of precision and recall; single balanced summary statistic for MLflow experiment comparison.
ROC-AUC	Threshold-independent separability measure, used to compare candidate model families before a decision threshold is chosen.
Lead-time accuracy	Evaluated per horizon (3/7/14 days) independently, since performance may degrade at longer horizons.
Prediction latency	Operational metric — flagged if a single Gradio prediction exceeds the 500 ms warning limit.
Population Stability Index (PSI)	Post-deployment drift monitor comparing live serving inputs to the training distribution (warning at PSI ≥ 0.10, critical at PSI ≥ 0.25).

Scope Note: This problem statement covers the modeling objective as currently defined. It does not commit to a specific model family (to be selected during experimentation and logged via MLflow) or a specific decision threshold (to be tuned against the recall/precision trade-off once initial models are trained).

---

📂 Project Directory Structure

paris-flood-pipeline/
├── .env                          # Local environment secrets & DB connection strings
├── pyproject.toml                # Project configurations & dependency specs
├── requirements.txt              # Environment dependencies
├── data_engineering/              # [COMPLETED] Data Engineering Module
│   ├── db/
│   │   ├── create_warehouse.sql   # SQL Server schema setup scripts
│   │   ├── setup_database.py      # Database initialization runner
│   │   └── test.sql               # SQL validation queries
│   ├── config.py                  # API & database configurations
│   ├── extract.py                 # Hub'Eau API ingestion logic
│   ├── transform.py               # Data cleaning, normalization, & translation
│   ├── load.py                    # SQL Server staging & MERGE upserts
│   ├── validate.py                # Post-run data quality audits & reporting
│   ├── logging_config.py          # Structured logging configuration
│   └── pipeline.py                # Pipeline orchestration engine
├── data_science/                  # [IN PROGRESS] Machine Learning Module
├── docs/                          # Documentation & assets
│   ├── images/                    # Diagram assets
│   └── ml_problem_statement.pdf   # Detailed ML specification
├── logs/                          # Execution log storage
│   └── pipeline.log               # Automated run logs
├── scripts/                       # Runner scripts
│   └── run_pipeline.py            # Entry point for running the ETL pipeline
└── tests/                         # Unit and integration test suite

---

🚀 Quickstart & Usage

1. Requirements & Prerequisites

• Python 3.10+
• Microsoft SQL Server instance configured with database access credentials

2. Environment Setup

Clone the repository, initialize a virtual environment, and install dependencies:

git clone https://github.com/your-username/paris-flood-pipeline.git
cd paris-flood-pipeline

python -m venv .venv
source .venv/bin/activate  # On Windows: .venv\Scripts\activate

pip install -r requirements.txt

Set up your connection string inside a .env file at the project root:

DB_SERVER=localhost
DB_NAME=ParisFloodWarehouse
DB_USER=your_user
DB_PASSWORD=your_password

3. Initialize Warehouse Database

Run the database setup script to provision table schemas, default constraints, and unique indexes:

python data_engineering/db/setup_database.py

4. Execute the ETL Pipeline

Run the full ingestion, transformation, load, and validation cycle:

python scripts/run_pipeline.py

---

📝 License

Distributed under the MIT License. See LICENSE for more information.