An end-to-end cloud data engineering and analytics pipeline built on Microsoft Azure to ingest, transform, query, and visualize vehicle complaint data from the National Highway Traffic Safety Administration (NHTSA).
The pipeline retrieves the source data from the NHTSA website, processes the raw complaint files in Azure, converts the data into manufacturer-specific Parquet files, analyzes Ford complaint data with Synapse Serverless SQL, and presents the results through interactive Power BI dashboards.
NHTSA Website → Azure Data Factory → Azure Storage → Mapping Data Flow → Parquet → Synapse Serverless SQL → Power BI
The pipeline follows these stages:
-
Web Data Ingestion
Azure Data Factory retrieves the NHTSA vehicle complaint ZIP file from the web source. -
Extraction & Raw Storage
The ZIP archive is extracted and the raw pipe-delimited TXT complaint data is stored in Azure Storage. -
Data Transformation
An Azure Data Factory Mapping Data Flow:- Reads the raw TXT dataset
- Filters invalid manufacturer records
- Derives output file names from manufacturer names
- Converts the data from TXT to Parquet
-
Manufacturer-Based Output
Complaint records are separated into manufacturer-specific Parquet files such as:Ford.parquet Toyota.parquet Honda.parquet Chevrolet.parquet ... -
Serverless SQL Analytics
Azure Synapse Serverless SQL is used to create an external table over the Ford Parquet data and run analytical SQL queries. -
Power BI Visualization
Power BI connects to the Ford complaint data and provides interactive dashboards for complaint volume, injuries, deaths, and incident trends.
| Technology | Purpose |
|---|---|
| Azure Data Factory | Data ingestion and transformation orchestration |
| Azure Storage / Data Lake | Raw and transformed data storage |
| ADF Mapping Data Flow | TXT-to-Parquet transformation |
| Apache Parquet | Columnar storage for analytics |
| Azure Synapse Analytics | Serverless SQL querying |
| SQL | External tables, validation, and analysis |
| Power BI | Interactive dashboards and visualization |
| Git / GitHub | Source control and portfolio documentation |
The transformation layer uses an Azure Data Factory Mapping Data Flow.
Raw NHTSA TXT
↓
Filter invalid manufacturer rows
↓
Derive manufacturer-based filename
↓
Write Parquet
↓
Manufacturer-specific files
The output filename is derived from the manufacturer field, producing separate files such as Ford.parquet.
This approach creates analytics-friendly columnar files while keeping manufacturer datasets independently accessible.
The downstream analytical portion of the project focuses on Ford vehicle complaints.
Azure Synapse Serverless SQL is used to create:
dbo.FordExternalTable
over the Ford Parquet dataset.
The repository includes SQL scripts for:
sql/
├── 01_create_external_table.sql
├── 02_validate_external_table.sql
└── 03_f150_crash_count_by_year.sql
One analysis examines the number of reported crashes involving the Ford F-150 by model year.
Example:
SELECT
TRY_CAST([YEARTXT] AS INT) AS ModelYear,
COUNT(*) AS CrashCount
FROM dbo.FordExternalTable
WHERE LTRIM(RTRIM([MODELTXT])) = 'F-150'
AND TRY_CAST([YEARTXT] AS INT) BETWEEN 1990 AND 2010
AND UPPER(LTRIM(RTRIM([CRASH]))) IN ('Y', 'TRUE')
GROUP BY TRY_CAST([YEARTXT] AS INT)
ORDER BY ModelYear ASC;The Power BI report provides multiple views of Ford vehicle complaint data.
The main dashboard summarizes overall complaint volume and severity using metrics including:
- Total complaints
- Total injuries
- Total deaths
- Injuries per 1,000 complaints
- Deaths per 1,000 complaints
It also provides filtering by model year, state, crash status, and date.
Analyzes deaths across Ford models and model years and highlights the vehicle/model-year combinations associated with the highest reported death counts.
Explores injury counts across vehicle models, model years, and reporting years.
Examines overall Ford complaint frequency and trends across different vehicle model years.
azure-nhtsa-vehicle-complaint-pipeline/
│
├── adf/
│ └── Azure Data Factory deployment artifacts
│
├── docs/
│ └── architecture-diagram.png
│
├── powerbi/
│ ├── NHTSA_Ford_Complaints_Analysis.pbip
│ ├── NHTSA_Ford_Complaints_Analysis.Report/
│ └── NHTSA_Ford_Complaints_Analysis.SemanticModel/
│
├── sample-data/
│ └── ford_complaints_sample.csv
│
├── screenshots/
│ ├── 01_ford_complaint_severity_dashboard.png
│ ├── 02_ford_complaint_death_analysis.png
│ ├── 03_ford_complaint_injury_analysis.png
│ └── 04_ford_complaint_incident_analysis.png
│
├── sql/
│ ├── 01_create_external_table.sql
│ ├── 02_validate_external_table.sql
│ └── 03_f150_crash_count_by_year.sql
│
└── README.md
The full NHTSA dataset is not stored in this repository.
A small Ford complaint sample is included for reference:
sample-data/ford_complaints_sample.csv
The full dataset is processed through the Azure pipeline.
- Built an end-to-end Azure data pipeline from an external web source to BI dashboards
- Automated ingestion and extraction using Azure Data Factory
- Transformed raw pipe-delimited TXT data into Parquet
- Separated output into manufacturer-specific Parquet files
- Queried Ford data using Synapse Serverless SQL
- Created external tables for analytics without requiring a traditional dedicated warehouse
- Developed SQL-based Ford and F-150 complaint analysis
- Built interactive Power BI dashboards for complaint severity, injuries, deaths, and incident trends
- Organized Power BI using the Git-friendly PBIP project format
The ingestion and transformation pipeline processes NHTSA vehicle complaint data across manufacturers.
The analytical case study then focuses specifically on Ford, using:
Ford.parquet
↓
Synapse Serverless SQL
↓
dbo.FordExternalTable
↓
SQL Analysis
↓
Power BI Dashboards
This separation demonstrates how a general-purpose data pipeline can support manufacturer-specific downstream analytics.
Vehicle complaint data is provided by the National Highway Traffic Safety Administration (NHTSA).
Roozbeh Khodayari
Data Science | Data Engineering | Azure | SQL | Power BI