30 days of electricity data in 180 ms
Engineered hourly electricity‑consumption aggregation variants in
Python, SQL and Bash + Jq, with the Python benchmark processing one
30‑day JSONL dataset in 180ms.
2025

Situation. The company processes large volumes of heterogeneous
electricity data coming from multiple data sources, each with its
own reporting frequency — ranging from hourly intervals to 15‑minute
intervals, and in some cases, irregular timestamps. This variability
poses a challenge when trying to create a coherent, comparable
dataset. To support accurate energy analytics, this data needs to be
normalized into consistent hourly consumption values, aggregated per
zone.

Task. The objective was to take raw event data from a historical
dataset (in JSONL format) and convert it into hourly‑aligned
electricity consumption figures, structured as one row per hour and
per zone. Specifically, the task involved:

  * Aligning timestamped data to strict hourly intervals,

  * Aggregating total electricity production within each interval by
summing mix values,

  * Incorporating cross‑border exchanges by adding imports and
subtracting exports,

  * Outputting the final values in a structured, scalable format
suitable for further analysis.

  * The solution also needed to be efficient enough to scale for
large time windows (30+ days) and across multiple countries/zones.

Action. To approach this task, three different variants of the
solution were implemented using Python, JQ (for command‑line JSON
processing), and SQL, each optimized for different contexts:

Python was chosen for its flexibility, ease of data manipulation,
and ability to handle in‑memory transformations efficiently. Using
pure Python functions, a pipeline was built that:

  * Parsed the JSONL files into data frames

  * Resampled the time series data to hourly intervals

  * Aggregated production and calculated net electricity consumption
(production + imports − exports)

  * Exported the results as CSV or loaded them into a lightweight
SQLite database for inspection.

JQ was used to create a quick, minimal‑dependency solution for
command‑line environments, built as a JQ filter chain.

In PostgreSQL the JSONL data was imported, normalized tables
created, and a series of SQL queries written.

To evaluate performance, each solution was benchmarked with both
1‑day and 30‑day historical datasets.

Result. The Python implementation emerged as the fastest and most
scalable solution, completing:

  * 1‑day data processing in just 34ms

  * 30‑day dataset in 180ms

This confirmed its suitability for handling larger timeframes while
maintaining sub‑second performance. It also offered clear,
maintainable code that could be easily extended to multi‑zone
processing or integrated into an ETL pipeline.

Overall, the multi‑tool approach demonstrated flexibility in tool
usage, strong performance optimization, and robust handling of
time‑alignment and aggregation challenges in real‑world electricity
data pipelines.

Categories:
1Databases	/categories/databases/	engineer.company	70
1PostgreSQL	/categories/postgresql/	engineer.company	70
1SQL	/categories/sql/	engineer.company	70
1Data Engineering	/categories/data-engineering/	engineer.company	70
1Data Pipelines (ETL/ELT)	/categories/data-pipelines/	engineer.company	70
1Data Analytics	/categories/data-analytics/	engineer.company	70
1Automation & CI/CD	/categories/automation/	engineer.company	70
1Performance Tuning	/categories/performance/	engineer.company	70
1Python	/categories/python/	engineer.company	70

Services:
1Database Performance Tuning	/services/database-performance/	engineer.company	70
1Data Pipeline Development (ETL/ELT)	/services/data-pipeline-development/	engineer.company	70
1Data Analytics & BI Dashboards	/services/data-analytics-bi/	engineer.company	70

hThis page on the web	URL:https://engineer.company/portfolio/a-month-of-meter-readings-in-180-milliseconds/	engineer.company	70
