2

Fleet Efficiency & Safety Intelligence

A data-analytics and business-intelligence build that fuses ~18M engine-telemetry records with vehicle specs and safety ratings in a snowflake data warehouse, then forecasts fuel efficiency and flags high-risk vehicles to guide public-fleet decisions.

Fleet Efficiency & Safety Intelligence

Turning 18M telemetry records into fleet-renewal decisions

Project Overview

A business-intelligence build that helps a public-sector fleet operator decide which vehicles to maintain, replace, or electrify first. It fuses three public datasets—~18 million OBD-II engine-telemetry records, a vehicle-specifications catalogue, and official NHTSA safety ratings—into a single analytical model, then layers forecasting and anomaly detection on top to turn raw signals into policy-level recommendations. The scenario is modelled on a Kenyan government fleet (police, fire, ambulance, administrative), using public data with a simulated fleet mapping.

What It Does

  • Data warehouse. A snowflake schema—a Fact_Engine_Telemetry table with derived measures (fuel-efficiency proxy, CO₂ estimate, efficiency score) around six dimensions (date/time, vehicle, geography, environment, trip, safety), with surrogate keys and SCD Type-2 on the vehicle dimension.
  • Three-source ETL. Loads the large telemetry file in chunks with Dask, derives efficiency metrics and driving-mode features, geo-enriches by county from lat/long, and joins vehicle specs to safety ratings on make + model + year with fuzzy matching.
  • Advanced analytics. Prophet forecasts fuel-efficiency trends; K-means clusters driving patterns (efficient / normal / inefficient); Isolation Forest flags fuel-consumption anomalies; Random Forest classifies high-risk or replacement-candidate vehicles.
  • Decision output. A combined efficiency-and-safety index that surfaces the vehicles that are both thirsty and low-rated—the ones worth replacing first—alongside quantified fuel-cost and emissions estimates.

Technology Stack

  • Warehouse & ETL: Python (pandas + Dask for the 18M-row file, fuzzywuzzy for record linkage), SQLAlchemy, a snowflake-schema relational model.
  • Analytics / ML: scikit-learn (K-means, Isolation Forest, Random Forest) and Prophet (time-series forecasting), with matplotlib visual analysis.
  • Scope: an end-to-end, executed analysis—data-warehouse design, ETL, modelling, and insight—in a single reproducible notebook.

Skills Demonstrated

  • Data engineering—dimensional (snowflake) warehouse design and a real three-source ETL, including out-of-core processing of ~18M records and fuzzy record linkage.
  • Analytics & ML—forecasting, clustering, and anomaly / risk classification applied to a concrete decision, not in isolation.
  • BI storytelling—translating engine telemetry into efficiency, safety, cost, and emissions insight a decision-maker can act on.
  • Domain framing—structuring a public-sector fleet problem (efficiency + safety + emissions) into measurable KPIs and recommendations.

A data-warehouse-backed analytics build that turns millions of telemetry records into fleet-renewal decisions.