Feeder Vellmoor Distribution · control room Synthetic data Sun 02:00

Definitions / transformer_overload

Transformer overload

detector, runs hourly

A monitored distribution transformer loaded past its nameplate rating for consecutive hours: cumulative heating, not a passing peak.

assets · Head of Asset Management · version 1

The most recent run of complete hourly readings above threshold_pct of the rating, ending within the last two hours, lasting at least min_hours, within look_back_hours. One row per transformer, started at the run's first hour.

Latest change: First version.

SQL
WITH recent AS (
  SELECT r.transformer_id, r."at", r.load_kva, t.rating_kva, t.feeder_id,
         r.load_kva > t.rating_kva * {threshold_pct} / 100.0 AS over
  FROM transformer_readings r JOIN transformers t ON t.id = r.transformer_id
  WHERE r."at" > {now} - INTERVAL ({look_back_hours}) HOUR AND r."at" + INTERVAL 1 HOUR <= {now}
),
grouped AS (
  SELECT *, sum(CASE WHEN over THEN 0 ELSE 1 END) OVER (PARTITION BY transformer_id ORDER BY "at"
                                                        ROWS UNBOUNDED PRECEDING) AS grp
  FROM recent
),
runs AS (
  SELECT transformer_id, any_value(feeder_id) AS feeder_id, any_value(rating_kva) AS rating_kva,
         min("at") AS started_at, max("at") AS last_hour, count(*) AS hours, max(load_kva) AS peak_kva
  FROM grouped WHERE over GROUP BY transformer_id, grp
)
SELECT 'transformer' AS subject_type, CAST(r.transformer_id AS TEXT) AS subject_id, r.transformer_id, r.feeder_id,
       r.started_at, CASE WHEN r.peak_kva >= 1.5 * r.rating_kva THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'transformer_id', r.transformer_id,
         'transformer', t.code,
         'feeder_id', r.feeder_id,
         'rating_kva', r.rating_kva,
         'peak_kva', r.peak_kva,
         'peak_pct', round(100.0 * r.peak_kva / r.rating_kva, 1),
         'hours_over', r.hours,
         'last_hour', r.last_hour
       ) AS evidence
FROM runs r JOIN transformers t ON t.id = r.transformer_id
WHERE r.hours >= {min_hours} AND r.last_hour + INTERVAL 1 HOUR > {now} - INTERVAL 2 HOUR
QUALIFY row_number() OVER (PARTITION BY r.transformer_id ORDER BY r.started_at DESC) = 1
ORDER BY r.started_at, r.transformer_id

Tests

meaningtestresultobservedexpected
assetscount_betweenpass0between 0.0 and 30.0
assetsuniquepass{'duplicates': 0}transformer_id unique
assetsdetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object