Building a Production-Grade EV Grid Resilience Pipeline

When I started the Data Engineering Zoomcamp, I had a project brief, three Databricks notebooks, and a goal to build something that actually looked like production data engineering. By the end, I had a fully orchestrated pipeline running on AWS, dbt, Airflow, and Docker — pulling real EV charging station data, correlating it with live weather conditions, and surfacing grid risk and revenue exposure through a dashboard.

This is the story of how I got there.

The Problem

VoltStream Energy Solutions — a fictional municipal EV charging network operator — manages 500+ charging stations but has no visibility into how weather conditions affect station availability. Maintenance is reactive. Failures are discovered after the fact. There is no way to anticipate grid strain events before they cause downtime and lost revenue.

The goal was to build an automated data system that continuously integrates operational station data with environmental conditions — enabling VoltStream to detect patterns, transition from reactive to proactive maintenance, and quantify financial exposure from station downtime.

Starting Point — Three Notebooks

My initial solution was built in Databricks using the Medallion architecture:

• Bronze — raw JSON from the Open Charge Map API stored in Delta tables

• Silver — flattened, deduplicated, cleaned station data with a custom schema

• Gold — a Star Schema with Fact_Station_Status, Dim_Station, and Dim_Weather, plus a risk score and estimated revenue at risk

The notebooks worked. But they were tightly coupled to Databricks, used dbutils for secrets and widgets, had no portability, and mixed business logic into the wrong layers. The weather API was being called inside the Silver notebook. The Gold layer read from a table literally named risk_df — a variable name that leaked into production storage.

A working solution is not the same as a production-grade solution.

The Refactor — A Real Data Stack

The updated architecture replaced the notebook approach with a proper pipeline:

APIs → Python Ingestion Scripts → S3 Bronze → Python Processing → S3 Silver

    → Redshift COPY → dbt Transformations → Gold Schema → Metabase Dashboard

Orchestrated end to end by Apache Airflow, containerised with Docker.

What Each Layer Does

Ingestion — Two Python scripts (ev_ingest.py and weather_ingest.py) handle data collection. The EV script uses a watermark stored in S3 — on first run it performs a full historical load, on subsequent runs it fetches only records modified since the last run. The weather script is dynamic — it reads distinct lat/lon coordinate pairs from the Silver EV data in S3, rounds them to one decimal place to create geographic weather zones, and fetches one API call per zone rather than one per station. This reduced API calls by 85%.

Storage — Raw NDJSON files land in voltstream-bronze, partitioned by date. Cleaned data lands in voltstream-silver. Both buckets live on AWS S3.

Processing — Two more Python scripts transform Bronze to Silver. The EV script properly flattens all connections for each station — not just the first one, which was the original notebook bug that silently discarded 60% of connector data. The weather script converts Celsius to Fahrenheit and standardises the zone join keys.

Orchestration — An Airflow DAG with seven tasks runs daily at midnight:

1. run_pipeline — launches the Docker pipeline container

2. truncate_ev and truncate_weather — clears stale Redshift data (in parallel)

3. copy_ev and copy_weather — loads fresh Silver data via COPY (in parallel)

4. dbt_run — builds all six models

5. dbt_test — runs 25 data quality tests

If any task fails, everything downstream is skipped automatically.

Transformation — Four dbt Gold models build the analytical layer:

• dim_station — one row per charging station, static descriptive attributes

• dim_weather — one row per weather zone with current conditions

• fct_risk — joins stations to weather, calculates a risk score (0-100) based on weather severity, temperature extremes, wind speed, and station power draw

• fact_station_status — incremental fact table that accumulates history over time

Dashboard — Metabase connects directly to the Gold schema in Redshift and surfaces four views: total estimated revenue at risk, average risk score by weather condition, maintenance priority list sorted by risk score, and a weather condition filter that drives all three visuals simultaneously.

Key Findings

After running the pipeline against real US EV station data:

• $231,875 estimated hourly revenue at risk across the monitored network

• Rain produces the highest average risk score of all weather conditions

• DC fast chargers (>50kW output) carry elevated baseline grid risk regardless of weather due to high power draw

• 89 distinct weather zones were sufficient to cover 150 unique station+connection combinations — confirming the zone grouping strategy worked

Engineering Decisions Worth Explaining

Why Pandas instead of PySpark? The dataset fits comfortably in memory. Pandas keeps the scripts portable — no cluster needed. If the dataset scales beyond 500k rows, PySpark on EMR is the natural migration path.

Why TRUNCATE + COPY instead of upsert? The public schema in Redshift is a landing zone, not the analytical layer. dbt handles deduplication and history in the Gold models. Keeping the landing zone simple — wipe and reload — eliminates merge complexity and makes the pipeline easy to reason about.

Why is weather fetched dynamically? Hardcoded weather zones would drift out of sync as new stations are added. Reading zones from Silver EV data means coverage automatically expands whenever new stations are ingested — no manual updates.

Why dbt for Gold instead of more Python? Business logic written in SQL is testable, version-controlled, and readable by analysts who may not write Python. The 25 tests catch data quality issues before they reach the dashboard. not_null, unique, and accepted_values tests run automatically on every pipeline run.

What I Learned

The gap between “it works in a notebook” and “it works in production” is significant — and mostly invisible until you try to cross it. Secrets management, idempotency, pagination, error handling, schema naming, watermarking, and layer separation are all things that notebooks let you ignore but a real pipeline cannot.

The most important architectural principle I internalised: each layer should do one thing. Ingestion ingests. Silver cleans. Gold transforms. dbt tests. Airflow orchestrates. When a layer tries to do two things — like calling an API from inside a transformation job — it becomes harder to test, harder to debug, and harder to extend.

Full Code and Documentation

The complete project is on GitHub — including all Python scripts, the Airflow DAG, dbt models, detailed AWS setup instructions, a local reproduction guide, dbt command reference, and all SQL used in the project.

Here’s my homework solution: https://github.com/stephandoh/zoomcamp_capstone793748/tree/main

You can sign up here: https://github.com/DataTalksClub/data-engineering-zoomcamp/