DEV Community

Cover image for Landing Catch-Basin Sensor Events in a Fabric Eventhouse
Aniket Hingane
Aniket Hingane

Posted on Edited on

Landing Catch-Basin Sensor Events in a Fabric Eventhouse

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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],
    }
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
  3. Regenerate or keep the CSV. python data/generate_basin_telemetry.py recreates the bundled file with seed 11.
  4. Run the laptop brief. python main.py --chart images/alert-chart.png
  5. Run the tests. pytest -q
  6. Optional dashboard. uvicorn api:app --reload then open the local root path.
  7. Fabric path. Follow docs/eventhouse-ingest.md: create BasinPulseEventhouse, Get data from local file into BasinReadings, first-row header on, then run kql/01_explore.kql.

Prerequisites on the Fabric side are ordinary. A workspace on a Fabric-enabled capacity. Permission to create items. The sample CSV. That is the whole gate for this PoC.

A few setup notes from my run.

  1. The Eventhouse is the container. The KQL database is where the table lives. The queryset is the workbench. Mixing those three names in conversation is how I confuse myself later.
  2. Local-file ingest is a one-shot path. Up to 1,000 files, each up to 1 GB uncompressed, per the Fabric local-file docs. 6,000 rows is nowhere near that ceiling.
  3. Compute can pause when idle. The first query after a pause can take a few seconds. That is not a failed ingest.
  4. OneLake availability can wait. I want the table queryable in KQL first.

Let's Run

On seed 11 the laptop brief prints this shape.

BasinPulse catch-basin telemetry brief
rows=6000 alerts=51 ok=5949 alert_rate=0.85%
threshold WaterLevelCm > 85.0

DISTRICT       AVG_CM  READINGS
Millbrook       52.45      2104
Riverview       52.25      2066
Hillcrest       51.78      1508
Harbor          51.42       322

DISTRICT       ALERTS
Millbrook          19
Riverview          18
Hillcrest          11
Harbor              3

peak basin-009 @ 109.92 cm (Millbrook) 2026-11-01T16:08:00Z
watch first: Millbrook (19 ALERT)
Enter fullscreen mode Exit fullscreen mode

I treat that block as the contract. After ingest, BasinReadings | count should return 6,000. The district summarize should return four records in the same order of averages. The ALERT project should return 51 rows. The ALERT summarize should put Millbrook first.

The first alert in the file is basin-007 in Hillcrest at 88.02 cm on 2026-11-01T00:05:30Z. That row matters to me because it is early and ordinary. It is not the peak. It proves the status column flips for a modest crossing, not only for theatrical spikes. The peak comes later the same day. If I had only looked at the peak, I would have designed the whole brief around one basin.

Harbor's three alerts are easy to ignore. I kept them in the chart. A watch order that drops the quiet district teaches the wrong habit. In my opinion, the brief should show the thin end of the map even when the action is elsewhere.

The FastAPI dashboard reads the same build_report function. There is no second calculator behind the HTML. When I changed the alert threshold in a scratch branch and forgot to regenerate the CSV, the dashboard and the terminal both looked wrong in the same way. That sameness is the feature.

Alert chart

Edge cases I hit while wiring this

  1. Wizard header off. If first-row header is disabled, the column names become Column1 and the status filter returns nothing useful. Turn it on. Look at the preview.
  2. Placeholder queryset text. Leaving YOUR_TABLE_HERE in the editor produces a syntax error that looks like your query is wrong. Clear the sample. Run one statement at a time.
  3. Recomputing the threshold in KQL. where WaterLevelCm > 90 while the CSV used 85 creates a false "ingest lost rows" panic. Filter on Status for the explore path.
  4. Regenerating with a new seed and keeping old prose. The README numbers are part of the experiment. Change seed, change README, change tests expectations, or do not change seed.
  5. Treating average level as the watch order. On this seed averages and alerts agree on first place. They will not always. I rank by alerts because spikes are the thing I said I cared about.

What I would change next

Continuous ingest through Eventstream is the obvious sequel. I would keep the same table schema and land live readings instead of a CSV drop. Materialized views for latest-per-basin and hourly averages by district are the second sequel. I already know those patterns from other Fabric work. I did not add them here because they would hide the create-and-ingest lesson under a second lesson.

A second fact grain for field visits is tempting. A visit is not a reading. Bolting inspected onto the reading row throws away history the moment a second visit happens. If I extend the experiment, the next file I want is a visit log with its own timestamp, not a boolean on the sensor packet.

Theory I am willing to defend, and theory I am not

I am willing to defend a few plain claims from this PoC.

  1. Event-like data wants an event-shaped store. A catch-basin feed is closer to a log than to a slowly changing dimension. Eventhouse matches that shape without forcing me into a warehouse modeling ceremony first.
  2. Status belongs next to the measurement that caused it. Re-deriving alert policy in every consumer is how two briefs diverge on a Tuesday.
  3. A pinned sample beats a "realistic" random file. Realism that cannot be cited is not useful in a write-up. Seed 11 is citeable.
  4. Count queries are not busywork. BasinReadings | count is the cheapest way to learn the wizard lied to you.

I am not willing to defend the 85 cm line as hydrology. It is a dial I set so the sample would teach. A real basin network would bring stage-discharge curves, known invert elevations, and a maintenance history I do not have in this file. Pretending otherwise would turn an Eventhouse rehearsal into cosplay. I would rather say the threshold is experimental and keep the ingest lesson clean.

I also refuse the fantasy that a bar chart of alerts is a dispatch system. Millbrook leading the count is a prompt to look, not a work order. If I had wired a fake ticket creator into this repo, the article would have become a story about automation theater. I wanted a story about landing rows and agreeing on names.

Ethics and honesty limits of the sample

Synthetic stormwater data can still mislead if I present it as measured. Every district name here is invented. Every level is drawn from a generator I wrote. The alert rate is a consequence of my spike probability, not a field study. I say that in the README and I am saying it again here because blog posts have a way of getting quoted without the hedges. If somebody copies the CSV into a slide titled "city flood risk," that is a misuse. The correct title is "experimental Eventhouse ingest sample."

Privacy is light in this file because there are no people in it. That is a design choice, not an accident. The moment I add crew identifiers or address-level assets, the ethics section gets longer than the KQL section. I kept the grain at basin and district so the experiment stayed about telemetry mechanics.

Failure modes I still expect

Even with the tests green, I expect a few familiar failures when I re-run this months later.

  1. Fabric capacity moved, workspace lost Eventhouse create rights, and the portal error looks unrelated to CSV shape.
  2. I upload an old CSV while the README quotes a new seed.
  3. I run two explore queries highlighted together and get a syntax error that I blame on the table.
  4. I turn on OneLake availability, query from another engine too early, and decide the Eventhouse ingest failed because the Delta copy had not flushed yet.
  5. I compare laptop averages rounded to two decimals with a KQL average shown at full precision and invent a discrepancy that is only display.

Those are boring failures. Boring failures are the ones that eat evenings. Writing them down is part of the PoC for me.

Closing Thoughts

The part I trust is the order of work. Write the question. Pick one reading grain. Name a district only when a basin points at it. Keep the threshold in the writer. Land the file in an Eventhouse. Prove the count. Rank the alerts. Keep a laptop mirror that refuses to flatter a bad ingest.

The part I do not trust is the cutoff. 85 cm, 1.2 percent spikes, +28 cm, seed 11. I chose them so this sample would have a winner, a near rival, a middle district, and a quiet end. A different sample would make those numbers look arbitrary, because they are. The useful claim is not that Millbrook needs trucks. The useful claim is that I can point at a status column and a KQL filter and say "this is the high water I meant," and a second notebook cannot quietly mean something else.

If I extend the experiment, the next change I actually want is a live ingest path, not a fifth chart. The current one still cannot tell me whether basin-009 stayed high after 16:08. It can tell me that, under rules I wrote down, Millbrook leads the alert count, Harbor stays quiet, and 6,000 rows made it into the table I named. That was the disagreement I was trying to make visible.

The code, the chart, and the GIF are in https://github.com/aniket-work/BasinPulse.

Disclaimer

The views and opinions expressed here are solely my own and do not represent the views, positions, or opinions of my employer or any organization I am affiliated with. The content is based on my personal experience and experimentation and may be incomplete or incorrect. Any errors or misinterpretations are unintentional, and I apologize in advance if any statements are misunderstood or misrepresented.

Tags: python, fabric, opensource, devops

Top comments (0)