Engineered hourly electricity consumption aggregation pipeline in Python / SQL / Bash + Jq, achieving 180ms for 30‑day datasets across heterogeneous JSONL sources.

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 structured 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.