Gas Notification Pipeline

Developer · 2024–2025

Problem

Every hour, the gas engineering team was checking a general consumption dashboard that wasn't built for their needs. When something looked wrong, the process was: open a SQL IDE, write queries, run them across all monitored meters, and review results by hand. That was the workflow for 25+ high-volume industrial customers. It was time-consuming, easy to miss things, and any incident only got caught on the next scheduled check.

System Design

The fault detection rule was straightforward by design: if a meter reports zero consumption for four or more consecutive readings, the system treats it as a fault and triggers a notification. A single zero can be legitimate. Four in a row is a pattern that warrants immediate attention. The engineering team defined this threshold based on their operational experience, and encoding it as an automated rule meant nothing slipped through between manual checks.

Detection runs entirely in SQL using CTEs and window functions, comparing each reading against its rolling history in a single pass across all monitored meters. When the threshold is breached, an automated email goes out to the engineering team. What used to require someone to notice, open a SQL IDE, write the query, and review results now happens without any manual intervention.

What I Built

The internal scheduler manages the full pipeline with event-based triggers: hourly meter data ingestion feeds into the detection logic, which runs fault classification and dispatches alerts when thresholds are breached. A separate monitoring dashboard tracks meter communication delays rather than consumption directly. The key metric was how long it took to receive a reading from each meter, classified into three severity tiers: under 1.5 hours (normal), 1.5 to 3 hours (elevated), and over 3 hours (critical). An interactive heatmap visualized delay patterns across all customers over daily, weekly, and monthly windows, making it easy to spot which meters were consistently slow to report and identify systemic communication issues before they became operational problems.

Architecture

Hourly meter data ingestion
  → SQL CTEs + window functions (anomaly detection)
  → Threshold + pattern-based fault classification
  → Automated alert dispatch → gas engineering team
  → Monitoring dashboard (alert history + pipeline health)

Results

92% reduction in manual tracking time, measured by comparing the old process (opening SQL, writing queries, reviewing results on a set schedule) against the new workflow of reading a single alert email. Detection shifted from reactive to proactive. The engineering team now gets notified within minutes of a fault pattern forming rather than catching it on the next scheduled check.

Stack

Next Project