An end-to-end data engineering project built on real IoT sensor data from two smart apartments in Switzerland — turning hundreds of thousands of raw sensor files into clean, business-ready dashboards.
The build
The project ingests 245k+ raw sensor files into a medallion pipeline (Bronze → Silver → Gold), landing 15M+ rows in the Silver layer alone. From that refined data, it ships dashboards for energy monitoring, environment tracking, presence detection and sensor health — the kind of views someone could actually make decisions from. The interesting part isn't any single step; it's the shape of the whole thing: raw, messy sensor output at one end, clean and trustworthy information at the other, every transformation traceable in between. That's the whole point of a medallion architecture — each layer has one job (raw capture, then cleaning and conforming, then business logic), so the pipeline stays debuggable and reliable as it scales. Designing for that from the start is what lets 245k files become something you'd actually stake a decision on.
Under the hood
Every ETL script is idempotent and resume-capable — you can interrupt it, re-run it, and never get duplicates, thanks to watermark tracking and ON CONFLICT upserts. A lightweight custom watcher polls every 60 seconds and predicts the next expected files instead of rescanning all 245k of them (≈0.4s vs ≈80s), with ingestion running in parallel across up to 16 threads. Bronze lives as raw, immutable files; Silver and Gold in PostgreSQL, with a Gold star schema feeding Power BI dashboards secured by per-apartment row-level security, plus KNIME workflows for occupancy and energy forecasting.
Highlights
- Ingests 245k+ raw IoT sensor files into a structured medallion pipeline
- 15M+ rows refined in the Silver layer
- Idempotent, resume-capable ETL — safe to interrupt and re-run, no duplicates
- Business-ready dashboards: energy, environment, presence & sensor health, with per-apartment row-level security
- Architected to scale from 2 apartments on one VM to 100+ without a rewrite
What surprised me
I assumed the scary part would be the 245k files. It wasn't — once the pipeline was structured, volume was almost the easy bit. The real problem was time: new data arrives every minute and has to be processed now, not eventually, and my first approach rescanned everything on every cycle. That's fine at small scale and unusable the moment it grows. The fix was to stop rescanning and start predicting — a lightweight watcher that anticipates the next expected files instead of re-reading all 245k, bringing a cycle down to around six seconds. The surprise wasn't the scale; it was learning that "real-time" is a design decision you make early, not a speed you bolt on later.