Vehicle Catalogue & Pricing ETL Pipeline
Async extraction, data normalization, and 10-sheet structured Excel catalogue generation across 1,940+ vehicle models and 2,230+ cities
The Problem & Target Context
Automotive pricing data in India is highly fragmented across variants, fuel types, and state-level RTO/tax structures, making automated financial comparisons prone to dirty data and missing variant mappings.
Engineering Approach & Core Principles
Built an asynchronous Python ETL pipeline that systematically ingests vehicle makes, models, and variant specifications, enforces structural and referential data-quality checks, and compiles an immutable 10-sheet Excel workbook with automated change detection.
What I Built & Individual Contributions
Engineered async HTTP clients with exponential backoff, variant normalizers, OpenPyXL 10-sheet workbook generators with formula formatting, baseline snapshot comparators, and automated pytest validation suites.
Architecture & Data Pipeline
Raw API Endpoints → Async Bounded Extractor → Canonical JSONL Normalizer → Data Quality Validator → Versioned Snapshot Store → 10-Sheet Styled Excel Master
[ Automotive API Endpoints (Cars, Bikes, Scooters) ]
│
▼
┌─────────────────────────────────────────────────────────┐
│ Async Extraction & Normalizer │
│ ├── Bounded Exponential Backoff & Rate Limiting │
│ ├── Canonical JSONL Serialization (Makes/Models/Specs)│
│ └── 2,235-City Multi-Tier Price Ingestion │
└────────────────────────────┬────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────┐
│ Validation & Delivery Engine │
│ ├── Structural & Referential Integrity Checks │
│ ├── 10-Sheet Formatted Excel Catalogue (OpenPyXL) │
│ └── Baseline Change Detection & Alert Engine │
└─────────────────────────────────────────────────────────┘Key Engineering Decisions & Trade-Offs
Engineered an asynchronous HTTP crawler with exponential backoff and payload validation to ingest 1,940+ vehicle models and 2,230+ cities. Enforced cross-sheet referential integrity checks and compiled an immutable 10-sheet OpenPyXL master workbook with baseline change detection.
Testing, Verification & Reproducibility
Pytest validation suite in 'tests/test_pipeline.py' verifying structural integrity, counts, and change classification across 2,292 active, discontinued, and upcoming variants.
Verified Outcomes & Metrics
2,292 variants normalized; 1,944 models validated; 10-sheet formatted master Excel generation with automated change tracking.
Verified Implementation
Inspected repository includes complete test suite ('pytest tests/test_pipeline.py'), detailed validation reports, and a verified 10-sheet master workbook cataloging 2,292 active and discontinued variants. Verification here represents code audit and local inspection, distinguishing functional code paths from external cloud provider accounts.
Boundaries & Known Limitations
Built for offline catalog ingestion and batch analytics; does not provide real-time consumer dealership quote guarantees.
Related Portfolio Projects
Explore companion systems in similar domains across AI engineering, quantitative finance, and developer infrastructure:
Collaborating on AI Engineering or Quantitative Systems?
I design and build end-to-end production software, quantitative models, and reliable AI architectures. Let’s discuss technical challenges or product collaboration.