Cover

How I Ingested Stormwater Telemetry and Queried High-Water Alerts in KQL

TL;DR

I built BasinPulse as a laptop experiment for one narrow question. If a wet week fills catch basins unevenly across four invented districts, can I land those readings in a Microsoft Fabric Eventhouse, prove the rows arrived, and get a watch order out of KQL without inventing a second definition of "high water" in a notebook?

The sample is 6,000 rows. Seed 11. Twenty basins. Readings every 30 seconds from 2026-11-01T00:00:00Z. Status flips to ALERT when WaterLevelCm crosses 85. On this seed that happened 51 times, 0.85 percent of the file. Millbrook leads with 19 alerts. Riverview has 18. Hillcrest 11. Harbor 3. The peak is basin-009 at 109.92 cm in Millbrook. The same numbers come out of python main.py on my machine and out of the explore queries in kql/01_explore.kql after a local-file ingest. The code is at https://github.com/aniket-work/BasinPulse.

This is a PoC. Riverview is not a city I work for. The Eventhouse path is the create-and-ingest lesson I wanted to rehearse on a stormwater story instead of another generic sensor dump.

Introduction

The first version of this experiment lived in a spreadsheet I opened after a rain overnight. I had pasted a few basin IDs, typed a water level, and decided "too high" in the header while the coffee was still hot. The next morning I opened a second sheet for turbidity and the cutoff drifted by five centimeters because I had forgotten the first sheet. Both sheets still looked official. They were two moods wearing column names.

Catch basins gave me a clean place to stop doing that. I did not want hospital beds, warehouse racks, or a ledger. I wanted a grate in a curb, a level sensor, a turbidity number, a rainfall tip, and a status that means one thing. Each row has a timestamp, a basin id, a district, three measurements, and a status. That is a small world. I can hold it in one CSV and one KQL table. I named the project BasinPulse. It is my PoC. The four districts are invented so the rows have a story. It is not a municipal system I operate, and it is not work I did for an employer.

I started from the question I actually wanted the brief to answer. Which district in this sample throws the most high-water alerts? "High water" had to become a number or the question was a shrug. I set the line at 85 centimeters. Then I had to say that out loud in one place, or the chart would invent a second line later. The generator writes ALERT when the level crosses that line. The KQL filter uses the same status. The laptop brief counts the same rows. From my experience, if the threshold lives only in a chart title, I will quietly move it when the picture looks kinder.

basin-009 is the boundary I care about for the peak. Millbrook, 109.92 cm, timestamp 2026-11-01T16:08:00Z. If I had written the alert rule as "level at or above 110," that row would have fallen out and the story would have flattened. I wanted the spike inside the queue, not sitting on a polite side of a round number.

Harbor is the holdout I care about for volume. Three hundred twenty-two readings. Three alerts. A district with almost no signal still has to show up in the average table, or I start pretending the map only contains busy basins. I kept Harbor in the sample on purpose so the watch order would have a quiet end.

Title diagram

What's This Article About?

I walk through how I built the ingest path, in the order I wish I had used the first time. The question comes first. Then the CSV grain. Then the Eventhouse container. Then the local-file wizard. Then the queryset. Then the laptop mirror that refuses to disagree with KQL. The code under Let's Get Cooking is the same code the tests run.

You will see why I pinned seed 11, why status is a column instead of a live calculation in every query, and why the laptop brief exists even though Fabric already has a queryset. Setup is the clone path on GitHub. The run section is the 6,000-row table, the Millbrook line, and the chart. If you only want the repo, start at https://github.com/aniket-work/BasinPulse. README.md has the same images and the same counts.

Tech Stack

I kept the list short. An Eventhouse experiment that needs a streaming mesh before the first table is an experiment I will not rerun on a weeknight.

  1. Python 3.11+ is the runtime I used for the generator, the brief, and the tests.
  2. The CSV under data/basin_telemetry.csv is the sample that lands in Fabric and on the laptop.
  3. Microsoft Fabric Eventhouse is the container. The default KQL database shares the Eventhouse name.
  4. KQL in the default queryset is how I prove the rows arrived and how I rank districts by ALERT.
  5. The analyzer in basinpulse/analyze.py mirrors those queries locally so a failed ingest cannot hide behind a pretty chart.
  6. FastAPI exposes GET /api/brief and serves static/dashboard.html.
  7. Matplotlib writes the ALERT-by-district bar chart.
  8. pytest locks the 6,000-row count, the four districts, and the alert threshold.

I skipped Eventstream for this PoC. Continuous ingest is a real next step. It is also a different failure mode, and I wanted the create-and-ingest path alone first. I skipped OneLake availability too. Turning that on later can expose the same table as Delta for other Fabric engines. I left it off so the lesson stayed on the Eventhouse and the queryset. I also skipped a hosted model. The brief I wanted is a table of counts, and a model that paraphrases "high water" would be a second definition.

Architecture

The picture matches the runtime. main.py and the dashboard both call build_report. That function asks analyze.py to load the CSV and score the same rules the KQL script uses. Sample rows stay in data/basin_telemetry.csv so the article, the tests, and the GIF describe one run. The Eventhouse path is documented in docs/eventhouse-ingest.md and exercised with kql/01_explore.kql.

Why Read It?

Read it if you have a folder of storm notes that each redefine the same operational word. This experiment fails in an obvious way. If the CSV says ALERT and a query invents WaterLevelCm > 90, the laptop brief and the queryset stop agreeing. I left that disagreement visible on purpose. The useful claim is not that Millbrook floods. The useful claim is that I can point at one threshold and one status column and say "this is the high water I meant."

A few choices are the ones I would repeat.

  1. Pin the sample. Seed 11, 6,000 rows, 30-second interval. If the file regenerates into a new story every run, the article becomes a moving target.
  2. Store status on the row. Computing the alert only in a query invites a second cutoff in a chart. The generator writes the status. The query filters it. The laptop counts it.
  3. Keep a laptop mirror. Fabric is where I want the long-lived table. The laptop brief is how I notice an ingest that quietly dropped rows.
  4. Rank districts by ALERT, not by average level alone. Millbrook and Riverview sit close on average. They do not sit close on spikes. The watch order follows the spikes.
  5. Leave a quiet district in the sample. Harbor exists so the brief cannot pretend every map cell is loud.

I do not think this replaces a stormwater SCADA system. I think it is a decent skeleton for a local Eventhouse rehearsal when the raw record is already a flat file. The GitHub project includes the tests and the GIF so you can see the same 51 alerts I am quoting here.

Let's Design

Before tables, I wrote the question and the cutoff on paper.

  1. Question. Which districts throw the most high-water alerts in this wet window?
  2. Grain. One reading, meaning one basin at one timestamp, not a daily rollup and not a work order.
  3. High water. WaterLevelCm > 85, stored as Status = "ALERT".
  4. Also show. Average level by district, first alert rows, and the single peak reading.

That list kept me from adding a column every time a noun sounded important. Crew, truck, and work order are real words around stormwater. They are not in the file, because none of my questions used them. I would rather add a join later than decorate the CSV now.

One fact, four districts

A telemetry model gets muddy when two things both claim to be the row. I made the reading the only fact. District is a label on the basin, assigned once when the sample is generated. Basin ids are basin-001 through basin-020. Each basin keeps one district for the whole file. That is a limit. A basin that moves between districts mid-storm would need a different grain, and then the rate would have to say whether the reading counts twice. I kept one district per basin so the watch order stayed honest.

  1. Riverview is the dense residential strip in the story. Plenty of readings. Eighteen alerts.
  2. Hillcrest sits higher in the fake topography. Eleven alerts. Enough signal to matter, not enough to win.
  3. Harbor is the quiet edge. Three alerts. I almost cut it, then kept it so the average table would have a thin column.
  4. Millbrook is the industrial-adjacent pocket that wins the watch order on this seed. Nineteen alerts. Peak reading lives here.

I am using "semantic" here only in the plain sense: names that mean the same thing in the CSV, the KQL table, and the laptop brief. I did not build an ontology service. I built a file and a scorer. An Eventhouse, in Fabric terms, is the container for event-like, time-based data. Inside it you keep one or more KQL databases, and each database holds tables. When you create an Eventhouse, Fabric also creates a default child KQL database with the same name, plus a default queryset so you can start querying immediately. That is the shape I leaned on.

I almost put the sample in a Lakehouse first "because everything else lives there." That would have been the wrong rehearsal. A Lakehouse is fine for batch tables and notebook work. This file is a time-ordered event feed with a status that flips on spikes. I wanted KQL over that feed with almost no ceremony. Eventhouse is the item type Fabric gives me for that shape. If the work had been a monthly rollup of cleaned basins, I would have started somewhere else. It was not.

Compute on an Eventhouse can pause when idle, and wake-up can take a few seconds. Storage sits in two tiers I care about only enough to name: a cache tier for recent query speed and a standard retention tier for durable data. I left Capacity Planner and custom schedules alone for this PoC. A 6,000-row file does not need me to pretend I am tuning a always-on hot path. Knowing that pause exists stopped me from declaring the ingest broken the first time a query waited on a cold start.

Sequence

Why a municipal stormwater story, not another generic sensor dump

I have written enough "DeviceId / Temperature / Humidity" samples that my eyes skip them. Catch basins forced better column names. Level is not temperature. Turbidity is not humidity. District is not a cloud region. When the alert fires, the sentence I say out loud is "the grate is high," not "the sensor is warm." That sentence is the whole reason I picked this domain for the experiment. From my experience, a sample that sounds like a tutorial also gets treated like a tutorial: nobody argues with the threshold, nobody notices when Harbor disappears from the chart, nobody asks whether average level is the wrong ranking key.

The business problem I am rehearsing is small and practical. During a wet window, an ops desk wants a watch order. Not a digital twin. Not a flood forecast. A ranked list of districts where the high-water status already flipped, plus proof that the underlying rows actually landed. That is a real sentence I have heard in other domains, translated here into stormwater language so the Eventhouse practice would stick to something I would bother to re-open.

Columns are what the sensor could report

On each reading the columns are the things I could imagine coming off a cheap logger.

  1. Timestamp is UTC. The generator starts at 2026-11-01T00:00:00Z and steps thirty seconds. I refused local time because storm windows cross midnight and I did not want a DST argument in the first query.
  2. BasinId is the sensor identity. Twenty basins. The sample picks one at random per row so the file looks like a multiplexed feed, not a round-robin that is too tidy.
  3. District is the ops grouping I want the rate over. It is not a GPS point. I have no basemap in this PoC, so I refused to invent coordinates.
  4. WaterLevelCm is the level in centimeters. Base process is a Gaussian around 52 with a 12 cm spread. About 1.2 percent of rows get a +28 cm spike so the alert set is small and real.
  5. TurbidityNTU and RainfallMm are companions. They are not in the alert rule. I kept them so the table looks like a sensor packet, not a single-number toy.
  6. Status is ALERT or OK. The generator writes it. Queries filter it. The brief counts it.

Each of those names appears the same way in the CSV header, the Eventhouse mapping, and the Python dataclass. When I came back the next day, "85 cm" stopped me from flipping Harbor into the watch list just because the chart had empty space. The picture got less flattering. The metric got clearer.

Why status is stored, not recomputed everywhere

I almost left status out and wrote where WaterLevelCm > 85 in every query. That works until a chart uses 80 and a notebook uses 90. I have done that to myself. Storing status does not remove the need for a threshold. It forces the threshold to live in one writer. If I change the line later, I regenerate the CSV and re-ingest. The queryset does not silently disagree with the laptop brief because somebody edited one filter.

The 85 cm line lives in data/generate_basin_telemetry.py as ALERT_LEVEL_CM and again in basinpulse/analyze.py as a constant used for assertions. The KQL script filters on Status. That is intentional. The explore path should read the column the ingest landed, not re-litigate the policy in every cell.

Constraints the tests actually check

The tests are short on purpose.

  1. The sample has exactly 6,000 rows.
  2. Every ALERT row has WaterLevelCm above 85.
  3. The four district names are Riverview, Hillcrest, Harbor, and Millbrook.
  4. District reading counts sum to 6,000.
  5. Alert ranking is sorted descending.

I do not parse the README into the tests. The README quotes the same numbers so a future edit that changes the seed without changing the prose fails my eyes before it fails CI. From my experience, a test that only checks "alerts > 0" will green-light a broken story.

Let's Get Cooking

This section is the code I actually run. Each block is followed by why I wrote it that way.

Generate the sample once, then stop touching it

SEED = 11 ROW_COUNT = 6_000 INTERVAL = timedelta(seconds=30) START_TIME = datetime(2026, 11, 1, tzinfo=timezone.utc) BASINS = [f"basin-{i:03d}" for i in range(1, 21)] DISTRICTS = ["Riverview", "Hillcrest", "Harbor", "Millbrook"] SPIKE_PROBABILITY = 0.012 SPIKE_OFFSET_CM = 28.0 ALERT_LEVEL_CM = 85.0 

I pinned the constants at the top of data/generate_basin_telemetry.py so the article and the file share one dial board. Seed 11 is arbitrary. What matters is that it is fixed. If I change the seed, I change the story, and then I have to rewrite every number in this post. I would rather regenerate on purpose than drift by accident.

def build_rows(seed: int = SEED, row_count: int = ROW_COUNT) -> list[tuple]: rng = random.Random(seed) district_of = {basin: rng.choice(DISTRICTS) for basin in BASINS} rows: list[tuple] = [] timestamp = START_TIME for _ in range(row_count): basin = rng.choice(BASINS) level = round(rng.gauss(52, 12), 2) if rng.random() < SPIKE_PROBABILITY: level = round(level + SPIKE_OFFSET_CM, 2) turbidity = round(max(1.0, rng.gauss(18, 8)), 1) rainfall = round(max(0.0, rng.gauss(0.4, 0.6)), 2) status = "ALERT" if level > ALERT_LEVEL_CM else "OK" rows.append( ( timestamp.strftime("%Y-%m-%dT%H:%M:%SZ"), basin, district_of[basin], level, turbidity, rainfall, status, ) ) timestamp += INTERVAL return rows 

The district map is built once per basin. That keeps a basin from teleporting between neighborhoods mid-file. The spike is a rare additive bump, not a second Gaussian. I tried a second process first and the alert rate jumped into a range that made the brief look like a constant emergency. 1.2 percent with a +28 cm bump gave me 51 alerts on seed 11, which is enough to rank and small enough to read.

I write the CSV with CRLF on purpose. Fabric's local-file wizard is fine with either line ending in practice, but a stable byte layout makes "did I regenerate?" a diff instead of a feeling.

Load the same columns the Eventhouse will see

@dataclass(frozen=True) class Reading: timestamp: str basin_id: str district: str water_level_cm: float turbidity_ntu: float rainfall_mm: float status: str def load_readings(path: Path) -> list[Reading]: rows: list[Reading] = [] with path.open("r", encoding="utf-8", newline="") as handle: reader = csv.DictReader(handle) for raw in reader: rows.append( Reading( timestamp=raw["Timestamp"], basin_id=raw["BasinId"], district=raw["District"], water_level_cm=float(raw["WaterLevelCm"]), turbidity_ntu=float(raw["TurbidityNTU"]), rainfall_mm=float(raw["RainfallMm"]), status=raw["Status"], ) ) return rows 

The dataclass field names are snake_case because I am in Python. The CSV headers stay PascalCase because that is what I want in KQL. Mapping at the edge is better than renaming the Fabric table to please the laptop. I have renamed columns "for convenience" before and then spent an evening chasing a mapping that no longer matched the wizard preview.

Mirror the KQL summarize and alert filters

def summarize_by_district(rows: list[Reading]) -> list[dict[str, Any]]: totals: dict[str, list[float]] = defaultdict(list) for row in rows: totals[row.district].append(row.water_level_cm) summary = [ { "district": district, "avg_level_cm": round(sum(levels) / len(levels), 2), "readings": len(levels), } for district, levels in totals.items() ] summary.sort(key=lambda item: item["avg_level_cm"], reverse=True) return summary def alert_rows(rows: list[Reading]) -> list[Reading]: return sorted( [row for row in rows if row.status == "ALERT"], key=lambda row: row.timestamp, ) def alerts_by_district(rows: list[Reading]) -> list[dict[str, Any]]: counts: dict[str, int] = defaultdict(int) for row in alert_rows(rows): counts[row.district] += 1 ranked = [ {"district": district, "alerts": count} for district, count in counts.items() ] ranked.sort(key=lambda item: item["alerts"], reverse=True) return ranked 

These three functions are the laptop twins of the explore queries. Average by district. Filter alerts. Count alerts by district. I deliberately sort averages by level and alerts by count, because those are different questions. Millbrook wins alerts. Millbrook also wins average on this seed, but Riverview is close on average and still one alert behind. If I had sorted only by average, I would have told a softer story.

def build_report(rows: list[Reading]) -> dict[str, Any]: alerts = alert_rows(rows) by_district = summarize_by_district(rows) alert_rank = alerts_by_district(rows) peak = max(rows, key=lambda row: row.water_level_cm) return { "row_count": len(rows), "alert_count": len(alerts), "ok_count": len(rows) - len(alerts), "alert_rate": round(100.0 * len(alerts) / len(rows), 2) if rows else 0.0, "district_summary": by_district, "alerts_by_district": alert_rank, "peak_basin": peak.basin_id, "peak_level_cm": peak.water_level_cm, "peak_district": peak.district, "peak_timestamp": peak.timestamp, "first_alerts": alerts[:8], "alert_threshold_cm": ALERT_LEVEL_CM, "preview": rows[:10], } 

The report is a plain dict so FastAPI can return it without a second schema layer. first_alerts is capped at eight because the ASCII table is for a terminal, not for a dump. The peak is computed across all rows, not only alerts, which is a small honesty check. On this seed the peak is an alert anyway. If a future seed produced a peak under 85, I would want to know my spike logic broke.

Print a brief a human can argue with

def format_ascii_table(report: dict[str, Any]) -> str: lines: list[str] = [] lines.append("BasinPulse catch-basin telemetry brief") lines.append( f"rows={report['row_count']} alerts={report['alert_count']} " f"ok={report['ok_count']} alert_rate={report['alert_rate']:.2f}%" ) lines.append(f"threshold WaterLevelCm > {report['alert_threshold_cm']}") # ... district averages, alert ranks, peak, first ALERT rows ... return "\n".join(lines) 

I care about the first lines more than the chart. If the terminal says 6,000 rows and 51 alerts, I can trust the Eventhouse count query when it says the same thing. If the Eventhouse says 5,980, the wizard did not finish, and no amount of dashboard polish fixes that. In my opinion, the ASCII table is the acceptance test that survives a bad wifi day.

The KQL side of the same brief

BasinReadings | count BasinReadings | summarize AvgLevelCm = avg(WaterLevelCm), Readings = count() by District | order by AvgLevelCm desc BasinReadings | where Status == "ALERT" | project Timestamp, BasinId, District, WaterLevelCm | order by Timestamp asc BasinReadings | where Status == "ALERT" | summarize Alerts = count() by District | order by Alerts desc 

Run those one at a time in the default queryset. Clear placeholder sample text first. KQL will throw a syntax error if a comment and a query share a line in a messy paste. I learned that the hard way and left a note in the .kql file for future me.

.show table BasinReadings schema as json is the last check. I want Timestamp as datetime, levels as numeric types, and status as string. If the wizard treated the header row as data, the schema looks wrong immediately.

The Fabric create-and-ingest path, in the order I actually click

I keep this sequence next to the code because the code alone does not land the table.

  1. Open a Fabric workspace that already sits on a Fabric-enabled capacity.
  2. Select New item, then Eventhouse, name it BasinPulseEventhouse, and wait out the short provision.
  3. Confirm the default KQL database and the default queryset exist. Same name family. Different jobs.
  4. Open the database, choose Get data, then Local file.
  5. Create table BasinReadings, upload basin_telemetry.csv, turn on first-row header, keep the auto mapping, finish.
  6. Wait for create table, create mapping, and data queuing to show success. Then look for about 6,000 rows in the activity tracker.
  7. Open the queryset, clear placeholders, run the explore file one statement at a time.

I wrote those steps into docs/eventhouse-ingest.md so I would not reconstruct them from memory next month. Memory is where my thresholds go to mutate. The doc is deliberately boring. Boring is the point.

The wizard creates an ingestion mapping for me. On this sample it is effectively BasinReadings_mapping. I do not hand-author a mapping for a flat CSV with a header row. I do glance at the preview. If Status shows up as a number, something is wrong. If Timestamp looks like a string of gibberish, the header toggle is off. Those two glances catch most of the pain I used to discover only after writing a wrong where clause.

Flow

Why the laptop mirror is not optional in this PoC

People sometimes ask why I bother with main.py if Fabric already has a queryset. As per me, the queryset is the destination truth after a good ingest. The laptop brief is the preflight and the differential. If the CSV on disk says 51 alerts and the Eventhouse says 40, I do not start redesigning Millbrook. I re-open the wizard summary. If both say 51 and my chart says 60, I broke the chart. The mirror exists so I can tell those failures apart without guessing.

api.py is the same idea with a browser skin. It calls build_report. It does not compute a second alert rate. I have shipped dashboards that drifted from the CLI by one filter. I am not doing that here on purpose.

Let's Setup

Step by step details can be found at:

  1. Clone the public repo at https://github.com/aniket-work/BasinPulse.
  2. Create a virtualenv inside the project and install requirements.txt.Source:https://dev.to/exploredataaiml/landing-catch-basin-sensor-events-in-a-fabric-eventhouse-40df