64-61 Data Cycle Project — Use Case 2

Apartments Domotic — Architecture Draft

Medallion architecture (Bronze → Silver → Gold)  ·  Data range: 18 Aug – 27 Oct 2023

Sources Data Sources
MySQL DB (pidb)
Buildings · Rooms · Sensors
Devices · DIErrors
SQL · static
JSON Sensors ×2
Plugs · doors/windows · motions
meteos · humidity · consumption
every 1 min
Weather CSV (sFTP)
Meteo2 folder
Temp · humidity · rain · radiation
daily ~7am
Ingestion scripts (Python + cron) — iso copy, no modification
Bronze Raw Storage
Raw JSON Files
Immutable sensor copy
/bronze/sensors/YYYY/MM/DD/HH/
File system
Raw Weather CSVs
Daily forecast files
/bronze/weather/YYYY/MM/DD/
File system
MySQL Snapshot
Periodic dump of static DB
for audit & decoupling
DB dump / CSV
ETL — flatten JSON, clean, deduplicate, normalize timestamps, join with metadata
Silver Clean Data
Sensor Events
Flattened rows · room + type
+ value + ts · outliers flagged
DB table
Apartment Metadata
Buildings · Rooms · Devices
joined, enriched, tz-aligned
DB table
Weather (clean)
Standardized units · aligned
to apartment location & time
DB table
DIErrors (clean)
Parsed messages · linked to
sensor/room · severity tagged
DB table
Aggregation — hourly/daily rollups, KPI computation, DWH star schema load
Gold Business Data
Energy KPIs
Consumption per room/apt
Daily & hourly totals
Fact table
Occupation Facts
Motion + door → presence
Minutes occupied per room
Fact table
Environmental Facts
Temp · humidity · CO2 · light
Anomaly flags per room
Fact table
Sensor Reliability
Error counts · uptime %
Missing data gaps
Fact table
ML training on Silver/Gold — predictions written back to Gold
ML / DS Data Science
Energy Forecast
Time series · next 24h
ARIMA / LSTM
Python · KNIME
Occupation Forecast
Predict room presence
patterns + weather features
Python · KNIME
Anomaly Detection
Sensor drift · unusual
consumption flagging
Python
BI tool reads Gold layer + ML predictions directly
BI / Viz Dashboards
Energy Dashboard
Consumption over time
Per room / apt + forecast
PowerBI / SAC
Occupation Dashboard
Presence heatmap
Daily / hourly patterns
PowerBI / SAC
Environment Dashboard
Temp · humidity · CO2
vs. weather correlation
PowerBI / SAC
Sensor Health
Error timeline · uptime
Missing data visualization
PowerBI / SAC

Data frequencies

  • JSON Sensors (×2 files) every 1 min
  • Weather forecast CSV (sFTP Meteo2) daily ~7am
  • MySQL static DB (Buildings, Rooms…) static
  • Bronze ingestion triggered by cron matches source
  • Silver ETL runs after each ingestion near real-time
  • Gold aggregation runs hourly/daily scheduled

Open questions

  • Gold layer: SQL Server on VM, SQLite, or flat Parquet?
  • ETL stack: pure Python scripts or KNIME / N8N?
  • BI tool: PowerBI Desktop or SAP Analytics Cloud?
  • JSON structure: need to inspect actual sensor files
  • Presence logic: what threshold for motion → occupied?
  • VM role: just DB host, or also runs ingestion pipeline?