Power BI • SQL • Python • Water data

Rainfall, River Flow and Storm Overflows

An end-to-end analysis of open data for Devon and Cornwall: how rainfall drives river flow, how closely storm-overflow discharges follow rainfall, and a six-page Power BI dashboard with the method and limits stated.

GitHub Repo View Report (PDF) Power BI Dashboard

Project Overview

The question: how does rainfall drive river flow in the South West, and how closely do storm-overflow discharges follow rainfall? The analysis uses open data only (Open Government Licence v3): daily rainfall and river flow from the Environment Agency Hydrology API, and the 2025 Event Duration Monitoring data published by South West Water.

The workflow was Python (pandas, scikit-learn) for cleaning, quality checks and a next-day flow model, 11 SQL queries (SQLite) for the summary tables, and Power BI for the dashboard. Data-quality decisions and limitations are documented throughout.

Data Snapshot

Daily Readings

36,162

7 rain gauges, 2 flow stations, 2015–2025

Overflow Events

209,711

1,179 sites, 2025

Readings Not "Good"

10.7%

Kept, flagged, never set to zero

Events Excluded

356

Longer than 7 days, likely monitor faults

Wet vs Dry Days

6.4×

Overflow events per day

Analytical Approach

Downloaded rainfall and flow automatically from the Hydrology API; checked each series for gaps, duplicates and quality flags
Built a regional rainfall index after confirming the gauges agree with each other (correlation 0.86–0.91)
Tested lags and antecedent rainfall, then compared a simple next-day flow model with persistence on held-out 2023–24 data
Joined 209,711 overflow events to daily rainfall and measured discharge hours per site
Built a Power BI data model (date and station dimensions, three fact tables) with DAX measures, then six report pages

Key Findings

The last 14 days of rain explain River Exe flow far better than same-day rain (correlation 0.77 with log-flow against 0.34) → antecedent wetness matters
A next-day flow model beats persistence on held-out data (NSE 0.85 against 0.78) but still overshoots some flood peaks
Low-flow days cluster in 2022 (133), 2018 (81) and 2025 (65), against 6 to 33 in other years → relevant to abstraction and supply planning
Overflows follow rainfall: about 1,570 events per day after wet days against about 250 otherwise
Rainfall is not the whole story: December 2025 had roughly 72,000 events against about 39,000 in January with almost the same rainfall
Impact is concentrated: the worst 10% of sites account for about 47% of discharge hours

Dashboard Views

Overview

Rain to River

Low Flow

Storm Overflows 2025

Data Quality

Next-day Flow Forecast

Data Quality and Limitations

One flow series had duplicate daily readings for a year; the 09:00 reading was kept for consistency
Suspect, Estimated and Unchecked readings are kept but flagged; missing values are never set to zero
2,207 overflow events have no site name; they are kept and reported as "Unnamed"
Correlation is not causation, and only one flow catchment is modelled
Overflow data is self-reported by the water company, covers one year, and event counts are not discharge volumes

SQL Example


-- Low-flow days per year (River Exe below its own 10th percentile)
WITH ranked AS (
  SELECT date, value,
         PERCENT_RANK() OVER (ORDER BY value) AS pr
  FROM fact_daily_readings
  WHERE station = 'Exe_Thorverton' AND value IS NOT NULL
)
SELECT substr(date, 1, 4) AS year,
       SUM(pr <= 0.10) AS low_flow_days,
       ROUND(MIN(value), 2) AS min_flow_m3s
FROM ranked
GROUP BY 1
ORDER BY 1;

This query produced the low-flow result above (2022: 133 days, 2018: 81, 2025: 65). The full set of 11 queries is in the repository (sql/analysis.sql).

Tools & Technologies

Power BI (DAX, Data Modelling, Dashboards)

SQL (SQLite: joins, aggregations, percentiles)

Python (pandas, scikit-learn, data quality checks)

Open data: Environment Agency Hydrology API, South West Water EDM data