🚀 Data Engineering Project: Vote Analysis Pipeline

📌 Overview

This project demonstrates an end-to-end data engineering pipeline that ingests vote data, processes it incrementally, and identifies anomalous (outlier) weeks using SQL-based analytics.

The solution is designed with a production mindset, ensuring idempotency, data consistency, and clean analytical outputs.

---

🏗️ Architecture

JSON Data → Staging Table → Deduplication → Final Table (votes) → Outlier Analysis

---

⚙️ Tech Stack

• Python
• DuckDB (OLAP database)
• SQL
• Docker (for execution environment)

---

🔄 Ingestion Pipeline

• Reads JSONL vote data

• Loads data into staging table

• Applies deduplication using window functions

• Performs upsert using INSERT OR REPLACE

• Ensures:

  • Incremental loads
  • No duplicate records
  • Idempotent execution

---

📊 Outlier Detection Logic

• Aggregates votes weekly
• Calculates average votes per week
• Identifies outliers using:

[ |1 - (x_i / \bar{x})| > 0.2 ]

• Outputs:

  • Year
  • Week Number
  • Vote Count

---

🚀 How to Run

1. Ingestion

poetry run exercise ingest-data

2. Outlier Calculation

poetry run exercise run-outliers

3. Run Tests

poetry run exercise check-ingestion
poetry run exercise check-outliers

---

✅ Key Features

• Idempotent ingestion pipeline
• Incremental data processing
• Clean and deduplicated analytical table
• SQL-based anomaly detection
• Lightweight and efficient (DuckDB)

---

📈 Possible Improvements

• Add data quality checks (null validation, schema enforcement)
• Partitioning for large-scale datasets
• Integration with cloud storage (S3 / ADLS)
• Scheduling using Airflow

---

💡 Assumptions

• Id is unique for each vote
• Latest record is determined by creation_date
• Input data is well-structured JSON
• All vote types are treated equally

---

👨‍💻 Author

Mohit Singh Senior Data Engineer