Technical Documentation

The Data Cycle project (Use Case 2) processes IoT sensor data from two smart apartments in Valais, Switzerland. The system ingests raw sensor readings (temperature, humidity, CO2, motion, door/window, energy consumption) and weather forecasts, transforms them through a medallion architecture (Bronze, Silver, Gold), and serves them to BI dashboards and ML models.

The system follows the medallion architecture pattern with three data layers, each serving a distinct purpose. Data flows from external sources through Bronze (raw), Silver (cleaned), and Gold (aggregated) before reaching BI tools and ML models.

source 1 — sensor JSON (SMB share)

Two apartments (Jimmy, Jeremie) generate one JSON file per minute per apartment, stored on an SMB network share (Z:\). Each file contains nested sensor readings: plugs, doors/windows, motions, meteo stations, humidity sensors, and consumption meters.

JSON structure (top-level fields)

sensor types — fields extracted per category

source 2 — MySQL database (school network)

Static reference tables from the school’s MySQL database (pidb at 10.130.25.152:3306). Contains building metadata, room details, sensor mappings, device inventories, and energy profiles.

source 3 — weather CSV (sFTP)

Daily weather forecast files from an sFTP server (/Meteo2). File format: Pred_YYYY-MM-DD.csv. Each CSV has columns: Time, Value, Prediction, Site, Measurement, Unit.

watcher.py — orchestrator

Continuous loop running every 60 seconds. Instead of scanning 245k+ files on SMB (~72s), it predicts the next expected filenames from the last known timestamp and checks with .exists() calls (~0.01s). A nightly full scan at midnight catches any missed files. Daily at 07:30, it triggers the weather pipeline as a subprocess.

bulk_to_bronze.py — sensor file copy

Copies new JSON files from SMB to local Bronze storage. Two modes: prediction (default, fast) and full scan (--full flag). Uses 16 parallel threads. Files are stored in timestamped folders: bronze/jimmy/YYYY/MM/DD/HH/filename.json. Skips files that already exist locally.

weather_download.py — sFTP download

Connects to the sFTP server via paramiko, lists CSV files in the remote directory, downloads new ones to bronze/weather/YYYY/MM/DD/. Configurable retry logic (default: 3 attempts, 600s delay). Logs to logs/weather_download.log.

flatten_sensors.py — JSON to sensor_events

Parses Bronze JSON files, extracts all sensor readings, normalizes room names (Bhroom to Bathroom, Bdroom to Bedroom, etc.), flags outliers, and upserts into silver.sensor_events. Uses 8 parallel workers with batches of 2,000 files. Watermark tracking via silver.etl_watermark prevents reprocessing.

room normalization

Raw JSON room names are inconsistent. The flatten function maps them to standardized names:

outlier detection bounds

Values outside these physical bounds are flagged with is_outlier = TRUE but not removed. This preserves data for audit while allowing BI/Gold to filter.

import_mysql_to_silver.py — dimension tables

Snapshots 10 reference tables from the school MySQL database into PostgreSQL Silver. All columns imported as TEXT (type casting done in Gold). Idempotent: DROP + CREATE on each run.

clean_weather.py — weather CSV processing

Reads raw weather CSVs from Bronze, validates required columns (Time, Value, Prediction, Site, Measurement, Unit), cleans timestamps as UTC, filters by WEATHER_MIN_YEAR, deduplicates by timestamp/site/measurement, maps 4 measurement types to standardized columns, replaces sentinel values (-99999.0) with NULL, pivots from long to wide format, flags outliers, and upserts into silver.weather_clean. Watermark via silver.weather_watermark. Logs row-drop counts at each step to logs/clean_weather.log.

weather outlier bounds

create_gold.py — schema creation

Creates the gold schema with admin privileges, then creates all dimension and fact tables as the app user (so the app user owns them). Idempotent: uses CREATE TABLE IF NOT EXISTS. Creates indexes on date_key, apartment_key, room_key for fast BI queries.

populate_gold.py — 9-step process

BI dashboards (planned)

row-level security (RLS)

Each apartment owner only sees their own data. Implemented via Power BI RLS roles filtering on apartment_key. Three roles: role_jimmy, role_jeremie (filtered), and role_admin (unfiltered, all apartments).

ML models

Built with KNIME Analytics Platform. Two use cases at the 15-minute room grain: room occupancy prediction and room energy consumption prediction. Each use case is implemented as a benchmark workflow (model comparison & selection) plus a prediction workflow (operational scoring & database writing). Results land in gold.fact_prediction. See Section 09 for the full methodology.

Context and scope

This section presents the KNIME workflows developed for the Data Cycle Project in order to address two analytical use cases at room level:

For each use case, the implementation follows a two-workflow logic:

The four workflows documented in this report are therefore the following: Motion_Benchmark, Motion_Prediction, Consumption_Benchmark, Consumption_Prediction. This separation between benchmarking and prediction reflects a deliberate methodological choice: the benchmark workflow is used for model comparison and selection, whereas the prediction workflow is intended for operational scoring and database writing.

Part I — Occupancy prediction workflows

1. Business objective

The objective of the first use case is to predict whether a room is occupied during a 15-minute time window. Since the source system does not provide a direct occupancy indicator, occupancy is inferred from motion sensor events.

The target variable is defined as follows:

Consequently, each final observation represents one room, in one apartment, at one specific 15-minute timestamp, with an associated binary occupancy status.

1.1 Selection of relevant rooms

Following exploratory analysis, only the following rooms were retained for the occupancy model: Kitchen, Living Room, Office. The remaining rooms were excluded because they did not provide a sufficiently consistent motion signal to support a reliable target construction.

2. Source data and selected variables

The main source table for the occupancy use case is sensor_events. The useful fields selected from this source include apartment, room, sensor_type, field, value, unit, timestamp, and is_outlier. Initial filtering removes outliers, external zones, and all records not relevant to occupancy modeling.

3. Shared preparation logic for Motion workflows

The benchmark and prediction workflows for occupancy share the same preparation logic. This common part ensures that the selected model is trained and later applied on data built according to the same transformation rules.

3.1 Database access

The workflow begins with the standard database access chain: PostgreSQL Connector → DB Table Selector → DB Row Filter → DB Column Filter → DB Reader. This stage isolates the relevant subset of sensor event data required for subsequent local KNIME transformations.

3.2 Time standardisation

A Time Rounder node is used to round the raw event timestamp to a 15-minute grain. This operation defines the future modeling grain as the combination of apartment, room, rounded timestamp.

3.3 Construction of the base grid

A GroupBy node is used to build a base grid containing all distinct combinations of apartment, room, 15-minute window. This base grid acts as the backbone of the final dataset. It ensures that all subsequent aggregated signals are aligned on the same temporal structure.

3.4 Construction of the occupancy label

A dedicated motion branch is created from the filtered source data: Row Filter on sensor_type = motion and field = motion → Rule Engine → GroupBy → Column Renamer. The logic is the following:

This procedure yields a binary and traceable occupancy target derived from observable sensor behaviour.

3.5 Integration of complementary behavioural signals

Two additional branches are prepared to enrich the occupancy dataset.

Door branch. This branch filters the source data on sensor_type = door and field = open, creates a binary open flag, and aggregates it into door_open_count and door_open_any.

Window branch. This branch follows the same logic for windows and produces window_open_count and window_open_any.

Each aggregated branch is reintegrated into the base grid through a Left Join on apartment, room, timestamp (Rounded). Missing values are replaced with 0 usingMissing Value, so that the absence of an event is interpreted as absence of activity rather than missing data.

3.6 Time-derived explanatory variables

Time-derived features are created using Date&Time Part Extractorand Rule Engine. The resulting variables are: Hour, Day of Week (Number), Day of Week (Name), Month (Number), Month (Name), is_weekend. These variables allow the model to capture temporal regularities in room occupancy.

3.7 Room filtering for modeling

A Row Filter is used to retain only the rooms previously identified as usable for occupancy prediction: Kitchen, Living Room, Office.

3.8 Lag-based temporal memory

The following nodes are used to generate lag features: Sorter → Lag Column → Column Renamer → Missing Value. The following lag variables are created:occupied_lag_1, motion_count_lag_1,door_open_count_lag_1, window_open_count_lag_1. These features encode the immediate past state of the room and provide short-term temporal memory to the model.

3.9 Column selection for machine learning

A Column Filter is then used to retain only the useful modeling variables: occupied, apartment, room, Hour, Day of Week (Number), is_weekend, occupied_lag_1, motion_count_lag_1, door_open_count_lag_1, window_open_count_lag_1.

3.10 Encoding of categorical variables

A One to Many node is applied to apartment and room. This transformation converts categories into binary indicators and avoids introducing an artificial ordinal relationship between rooms or apartments.

3.11 Target formatting

Because the selected learners are nominal classification models, the targetoccupied is converted from numeric to string using aNumber to String node.

4. Workflow 1 — Motion_Benchmark

The purpose of the benchmark workflow is to compare several classification models and select the most suitable one for room occupancy prediction.

Train/test split. A Table Partitioner node is used to split the dataset into 80% training data and 20% test data. The selected strategy is First rows, which is consistent with the temporal nature of the use case because the dataset is chronologically ordered before partitioning, thereby preserving a sequential split between past observations and more recent observations.

Candidate models. Three classification branches are prepared: Logistic Regression, Decision Tree, Random Forest.

Evaluation procedure. Each branch follows the same evaluation logic: Learner → Predictor → Scorer → ROC Curve (JavaScript). The benchmark results are consolidated into a comparison table.

Model selection criterion. The final model is selected on the basis of AUC.

Methodological role of the workflow. The benchmark workflow is used exclusively to prepare the training and test data, compare alternative learners, evaluate model performance, and justify the final model selection. It is not used for database writing.

5. Workflow 2 — Motion_Prediction

The purpose of the prediction workflow is to apply the selected occupancy model to prepared data, reconstruct a readable business output, and write the final prediction table into PostgreSQL.

Shared logic with the benchmark workflow. This workflow reuses the same preparation chain as the benchmark workflow (data access, time rounding, base grid construction, target and feature engineering, room filtering, categorical encoding). This ensures strict consistency between model training and operational scoring.

Model application. The selected learner and predictor are Logistic Regression Learner and Logistic Regression Predictor. The predictor generates the column Prediction (occupied).

Reconstruction of a business-readable output. AJoiner is used to reattach readable business columns to the prediction result, including apartment, room, timestamp (Rounded), observed occupied value. The join is performed on RowID, assuming row identity has been preserved throughout the preparation pipeline.

Final table prepared for PostgreSQL. After renaming and enrichment, the final table contains the following columns. The constantsmodel_name = logistic_regression and target = Presenceare added.

Database writing. The final prediction table is written into PostgreSQL using PostgreSQL Connector → DB Writer. A possible target table isfact_prediction.

Business meaning of the result. Each written row represents one room, in one apartment, at one specific 15-minute timestamp, with the predicted occupancy, the observed occupancy, and the model identifier. This makes it possible to answer the operational question: “Will this room be occupied at this precise timestamp?” The resulting table can subsequently be aggregated in Power BI to answer broader analytical questions such as room occupancy patterns by day, hour, or apartment.

Part II — Energy consumption prediction workflows

6. Business objective

The objective of the second use case is to predict the average room energy consumption over a 15-minute time window. The target is defined astarget_power_w = average power_w observed during the 15-minute window. Each observation therefore represents one room, in one apartment, at one precise 15-minute timestamp, with an observed mean power value. The final model aims to answer the following question: “What is the expected average power consumption of this room at this precise timestamp?”

7. Source data and selected variables

The main source table for the energy use case is fact_energy_minute. The workflow also uses the following supporting tables: dim_datetime,dim_room, dim_apartment, fact_weather_hour.

7.1 Selected variables from fact_energy_minute

The useful columns are datetime_key, room_key, apartment_key, power_w. A first filtering step keeps only valid measurements: is_valid = TRUE andpower_w IS NOT NULL.

7.2 Role of the dimensions

The dimension tables are used as follows: dim_datetime provides the actual timestamp via timestamp_utc; dim_room provides the readable room label room_name; dim_apartmentprovides the readable apartment label apartment_id.

7.3 Weather source

The weather-enrichment branch uses fact_weather_hour. Selected weather variables are prediction_date, temperature_c, humidity_pct, precipitation_mm, radiation_wm2. These variables are not treated as observed weather only, but as forecast-related explanatory variables used to improve future-oriented consumption prediction.

8. Shared preparation logic for Consumption workflows

The benchmark and prediction workflows for energy consumption share the same preparation logic.

8.1 Data access and first joins

The workflow begins with the following chain: PostgreSQL Connector → DB Table Selector → DB Row Filter → DB Column Filter → DB Joiner with dim_datetime → DB Joiner with dim_room → DB Joiner with dim_apartment → DB Reader. The objective is to extract a minimal but readable energy dataset before local time transformations.

8.2 Construction of the 15-minute energy target

The following nodes are used: Time Rounder → GroupBy → Column Renamer. The minute-level timestamp is first rounded to the 15-minute grain. AGroupBy then aggregates all measurements per room_key, apartment_key, timestamp_rounded. The target is created astarget_power_w = AVG(power_w). The workflow also keeps room_name, apartment_id, reading_count. The reading_count variable allows the workflow to monitor the number of minute-level values that contributed to each 15-minute aggregated observation.

8.3 Weather forecast preparation and anti-leakage logic

A dedicated weather branch is built in parallel.

8.3.1 Weather branch preparation. The branch begins with DB Table Selector on fact_weather_hour → DB Column Filter → DB Joiner with dim_datetime → DB Reader → Column Renamer. This createsweather_target_hour_ts, which represents the hour for which the weather forecast is valid.

8.3.2 Forecast-date control. Because several forecasts exist for the same target weather hour, a filtering rule is required to prevent information leakage. The following nodes are used: Date&Time to String → String to Date&Time → Rule Engine → Row Filter. The implemented rule keeps only weather rows such that prediction_date < target weather date. This implies that forecasts produced on the same day as the target hour are excluded, and only forecasts produced on previous days are accepted. This choice was made in order to reduce the risk of using information that would be unrealistically close to the predicted time.

8.3.3 Selection of the most recent valid forecast. Among the remaining admissible forecast rows, the workflow retains the most recent one for each target weather hour. This is achieved through Sorter → GroupBy → Column Renamer. The sorting order is (1) weather_target_hour_ts ascending, (2) prediction_date descending. The subsequent GroupBy keeps the first valid weather record for each target hour, thereby selecting the most recent admissible forecast.

8.4 Join between weather and energy data

On the energy branch, a second hourly rounding is created fromtimestamp_rounded using a Time Rounder. This producesweather_hour_ts. A Joiner is then used to attach the selected weather forecast to the energy dataset onweather_hour_ts = weather_target_hour_ts. Rows without weather information are removed after the join.

8.5 Time-derived explanatory variables

The enriched energy dataset is further processed usingDate&Time Part Extractor and Rule Engine. The resulting time-based variables are: hour, day_of_week_number, month_number, is_weekend.

8.6 Lag-based temporal memory

The temporal structure of the energy dataset is handled through Sorter → Group Loop Start → multiple Lag Column nodes → Loop End → Column Renamer → Row Filter. Lag computation is done inside grouped loops in order to prevent contamination across rooms and apartments.

The selected lag variables are power_lag_1,power_lag_4, power_lag_96, power_lag_672. These lags capture respectively the previous 15-minute observation, the previous hour, the same time one day earlier, and the same time one week earlier. Rows for which the required lag history is not available are removed before modeling.

8.7 Column selection for machine learning

A Column Filter is used to retain the explanatory variables required for the learners. The retained set includes: target_power_w, hour, day_of_week_number, month_number, is_weekend, power_lag_1, power_lag_4, power_lag_96, power_lag_672, temperature_c, humidity_pct, precipitation_mm, radiation_wm2, room_name, apartment_id. Technical columns used only for joining or control are removed before the learners.

8.8 Encoding of categorical variables

A One to Many node is applied to room_name and apartment_id. This transformation enables the regression models to exploit room-specific and apartment-specific effects.

9. Workflow 3 — Consumption_Benchmark

The benchmark workflow is used to compare several regression models and select the best one for room-level energy consumption prediction.

Train/test split. A Table Partitioner node is used with the following configuration: 80% training data, 20% test data, strategyFirst rows. This preserves a temporally coherent split between past observations used for training and more recent observations used for evaluation.

Candidate models. Three regression branches are prepared: Linear Regression, Simple Regression Tree, Tree Ensemble Regression.

Evaluation procedure. Each branch follows the same evaluation pattern: Learner → Predictor → Numeric Scorer. Two evaluation perspectives are considered.

9.4.1 Mean Signed Difference on the full test set. The full test set is used to compute the Mean Signed Difference (MSD). This metric indicates the direction of the average bias: a positive MSD means the model tends to underestimate actual consumption; a negative MSD means the model tends to overestimate actual consumption; a value close to zero indicates a limited global bias.

9.4.2 Mean Absolute Percentage Error on positive targets only.Because the Mean Absolute Percentage Error (MAPE) cannot be computed when the actual target is equal to zero, an additional filtered evaluation branch is built. A Row Filter first retains only rows for which the real target is strictly positive, after which a second Numeric Scorercomputes the MAPE on this subset. This approach does not modify the model itself. It only restricts the scope of the MAPE calculation to the rows where percentage error is mathematically meaningful.

Model selection criterion. The benchmark results are consolidated into a final comparison table. The selected model is chosen using the following two-step rule:

Methodological role of the workflow. This workflow is dedicated to model comparison, quality assessment, and final learner selection. It is not intended for database writing.

10. Workflow 4 — Consumption_Prediction

The objective of the prediction workflow is to apply the selected energy model, reconstruct a readable room-level prediction table, and write the results into PostgreSQL.

Shared logic with the benchmark workflow. This workflow reuses the same preparation logic as the benchmark workflow, including database extraction, 15-minute target construction, weather forecast enrichment, time-based features, lag engineering, and categorical encoding. This ensures that operational scoring is consistent with the benchmark preparation logic.

Model application. The selected modeling nodes are Linear Regression Learner and Regression Predictor. The predictor returns the predicted room-level power value.

Reconstruction of a business-readable output. AJoiner is used to reattach readable fields to the prediction result, such as apartment, room, timestamp_rounded, actual observed power value. This makes it possible to move from a purely technical scoring dataset to a table that can be directly interpreted by business or BI users.

Final table prepared for PostgreSQL. After renaming and enrichment, the final table contains the following columns. The constantsmodel_name = linear_regression and target = Consumptionare added.

Database writing. The final result is written into PostgreSQL using PostgreSQL Connector → DB Writer. A possible target table isfact_prediction.

Business meaning of the result. Each output row represents one room, in one apartment, at one specific 15-minute timestamp, with the predicted average power consumption, the observed average power consumption, and the model identifier. This allows the workflow to answer the operational question: “What is the predicted energy consumption of this room at this precise timestamp?” The resulting prediction table can later be aggregated in Power BI to analyse patterns such as room-level load, apartment-level usage, peak time windows, or the influence of weather on consumption.

11. Methodological synthesis

The four workflows follow a coherent methodological framework. First, each use case relies on a strict separation between benchmarking, which supports model comparison and justified selection, and prediction, which supports operational scoring and database integration. Second, the chosen grain of 15 minutes provides a balance between stability and temporal precision — it reduces minute-level noise while preserving a meaningful operational time resolution. Third, both use cases rely on explicit feature engineering: time-derived explanatory variables, lag features, categorical encoding, and, for energy consumption, weather forecast enrichment under an anti-leakage rule. Finally, the prediction workflows are designed not only to score data, but also to reconstruct business-readable outputs suitable for downstream reporting and dashboarding.

12. Executive summary

13. Final project result

At this stage, the project includes a complete benchmark workflow for room occupancy prediction, a complete prediction workflow for room occupancy scoring, a complete benchmark workflow for room energy consumption prediction, a complete prediction workflow for room energy scoring, PostgreSQL-ready prediction tables, and a solid foundation for downstream Power BI analysis.

silver.sensor_events (main fact table)

silver.weather_clean

silver — other tables

gold schema — dimension tables

gold schema — fact tables

deployment steps (dev to prod)

health checks

The pipeline collects sensor data from real apartments, which means it processes personal information about identifiable households. This section documents how the project handles GDPR obligations (Regulation EU 2016/679) and the wider ethical considerations of continuous in-home sensing.

19.1 Data inventory

19.2 Legal basis (Art. 6)

As an academic research project, the most appropriate legal basis is consent (Art. 6(1)(a)) — residents are informed of the sensors and agree to participate in the study. This consent is obtained at apartment onboarding and may be withdrawn at any time, which triggers data erasure (see 19.3).

19.3 Data subjects' rights

19.4 Data minimisation & purpose limitation (Art. 5)

The project explicitly excludes the MySQL users, relationships, actions tables (containing names, emails, phone numbers, gamification data) from the silver layer (see etl/bronze_to_silver/import_mysql_to_silver.pycomment block). Stated purpose is energy efficiency research and occupancy analytics — secondary uses (e.g. commercial profiling) are explicitly out of scope.

19.5 Storage limitation (Art. 5(1)(e))

Current state: bronze JSON archives are kept indefinitely; silver and gold tables grow without retention. Recommended retention policy (to be implemented before any production deployment):

19.6 Integrity & confidentiality (Art. 32)

19.7 Risk assessment (DPIA-lite)

19.8 Anonymisation strategy + threat model

The pipeline is a toolmaker, not the data controller. Each deployment of the project (via the install wizard) creates a new instance under the deployer's control: their VM, their DB credentials, their decisions about who gets dashboard access. So the realistic threat surface for personal data is the Power BI dashboard accessed by non-technical users(e.g. residents, evaluators, teachers) — not the underlying tables, which are only reachable by the deployer's technical staff.

Anonymisation effort is therefore concentrated where it actually matters: at the gold dimension layer, since Power BI consumes from gold. Bronze and silver retain identifiable values for legitimate research use — protected by VM access control, RLS credentials, and (recommended) disk-level encryption configured by the deployer.

19.9 What gets masked in gold (implemented)

On every populate_dimensions run (idempotent), the following columns of gold.dim_apartment are sanitised:

We deliberately keep the name column (a common first name like “jimmy”). The legal and practical reasoning:

19.10 Deployer obligations

What we provide vs what each deployer must configure on their installed instance:

19.11 Ethics beyond GDPR

19.12 Compliance status

The current pipeline is sized for two apartments and was developed on a single Windows VM with one PostgreSQL instance. This section projects what happens as the number of apartments, the time horizon, and the number of concurrent dashboard users grow.

20.1 Current data rates (per apartment)

20.2 1-year projections

20.3 Bottlenecks at scale

20.3.1 Bronze lifecycle (single-VM deployments)

On a single-VM deployment without object storage, bronze accumulates roughly 5 GB per apartment per year. Once it crosses ~50 GB, scans (filesystem walks, full-mode bulk_to_bronze) get noticeably slower.

The project ships with scripts/cleanup_bronze.py as a pragmatic mitigation:

20.4 Recommended evolution path

20.5 Power BI RLS at scale

The current dashboard defines one Power BI role per apartment (role_jimmy, role_jeremie, role_admin). This works for 2–5 apartments but becomes unmanageable at scale: each new apartment requires a .pbix edit + republish.

Recommended evolution for > 5 apartments: replace per-apartment roles with a single dynamic role driven by a user-to-apartment mapping table.

Add to gold (one-time setup):

This pattern also strengthens the GDPR posture: each user's access is explicitly granted via the mapping table (auditable via SQL), rather than being implicit in a role assignment.

20.6 Compute & cost projections (rough)

For 50 apartments, 5 years of retention, on Swiss-hosted cloud infrastructure (rough order-of-magnitude figures, not vendor quotes):