Independent project · Analytics engineering / Data quality
EV Charging Data: Unified Schema
Three public datasets in three incompatible shapes, conformed into one tested dbt schema, with every number traceable to evidence.
As of v0.1.0
The problem
Consolidating differently shaped data feeds into one reliable reporting model is a pattern I work with professionally. I wanted a public example of it that anyone could inspect, so I built one on open data.
The project uses three public operators’ charging-session logs. Each one has its own problems:
- Boulder, Colorado: one file holds two overlapping deliveries, and the timestamps come in mixed formats.
- Cary, North Carolina: timestamps are in true UTC, but there is no end time.
- UK Department for Transport: the release is split across four files whose headers, date formats, and duration units disagree.
None of the three publishes how many ports each station has. That means the denominator of every utilization figure has to be inferred.
What I built
As of v0.1.0, the pipeline lands 440,575 rows. It accepts 357,606 of them as sessions and quarantines the other 82,969. Every quarantined row is kept with its reasons, and nothing is dropped silently.
- Ingestion: each file is landed as strings with hashes, then checked against a declared contract per source.
- Warehouse: a bronze, silver, and gold dbt warehouse on DuckDB, with enforced contracts on the gold models, unit tests, and a quarantine.
- Station-day fact: built on a full date spine, with sessions split at local midnight and with daylight-saving days handled.
- Reconciliation: rows, sessions, and energy are reconciled from raw to gold, and every residual is classified.
- Claim checking: a CI check fails the build when a documented number has no committed artifact behind it.
The whole thing runs on GitHub Actions at no cost.
Public source files → contract-aware landing → bronze → silver standardization → session fact / quarantine → station-day gold fact → reconciliation + claim artifacts → findings
Three decisions that shaped it
Define the metric intentionally
Utilization is a ratio, and ratios can’t be averaged across days. Every rollup sums the used minutes and the available minutes separately, then divides once. A test proves that the averaged version gives a different answer on this data.
Treat inferred capacity as a bound, not a fact
- Port counts are the larger of two lower bounds: published connector IDs, and the concurrency a station reaches on at least five separate days.
- Because ports can only be undercounted, the published utilization is an upper bound.
- I publish the range across denominator definitions, not a single number:
- Boulder’s connected-time utilization is 11.4% under the production definition, and ranges from 10.5% to 12.4% across all of them.
- The UK figure ranges from 8.7% to 11.4%.
Make incremental loads provably correct
- A re-delivered file can change a value that is part of a session’s identity.
- A row-by-row merge would count that session twice.
- The session fact therefore replaces a whole source whenever any of its files change.
- A test shows that a late re-delivery, with one changed value and one vanished row, produces the same result as a full rebuild.
What the data said
Idle time is large, but most of it blocks nobody
Found
- In Boulder, 45.1% of connected time is idle after charging ends.
- Only up to 12.4% of connected time is idle while every inferred port was occupied.
- That is up to 28% of the idle total.
Why it matters
The headline idle figure overstates what an idle fee could recover by 3.6 times, and even the smaller number is a ceiling.
What I’d tell the decision-maker
- Quote the smaller figure, and say “up to.”
- Pilot at multi-port stations around midday.
- Queue data would change my view.
A publisher’s stated rule didn’t describe its own exclusions
Found
The UK publisher says it excluded zero-energy and very short sessions. Of the 48,619 rows in its excluded file:
- 20,699 meet that rule.
- 17,677 are ordinary sessions excluded for unstated reasons.
- 9,258 are duplicates of events moved to another release.
Why it matters
The publisher’s stated explanation does not fully describe the contents of the excluded file.
What I’d tell the decision-maker
Treat the publisher’s split as a label, keep every row, and apply one explicit rule of your own.
Where the data proved the plan wrong
- Daylight-saving resolution: I assumed the database resolved ambiguous daylight-saving times to the first occurrence. An empirical test showed it uses the second.
- A phantom port: rounding a session end by up to thirty seconds created a port that did not exist at a single-port station. I caught it because two reports disagreed.
- A false reproducibility claim: after release, a reproducibility claim turned out to be false for two tables, because a non-deterministic pick was choosing station attributes. It now uses a majority rule with a tie-break, and a test that compares two independent builds byte for byte.
How it was built
The project was AI-assisted, using Claude Code. It worked under a written brief with phase-gated review, which means I approved or amended every phase. The brief and sixteen decision records are in the repository.
It is an independent project on open data and contains no employer code, data, or business rules.
Stack: Python, SQL, dbt-core, DuckDB, pytest, GitHub Actions, GitHub Pages.