Project Overview

This project builds a full, scalable data pipeline on 3.4 million NYC Yellow Taxi trip records using PostgreSQL. The goal was to take raw, unstructured trip data and transform it — through a structured three-layer architecture — into clean, business-ready KPIs an operations team could actually use.

Business Challenge

Raw taxi trip data at this scale (3.4M+ records) is too large and too messy to query directly for business decisions. The challenge was designing a pipeline that cleans, structures, and aggregates this data reliably, using SQL techniques that scale.

Key Results

3.4MRows Processed
3-LayerMedallion Architecture
BronzeRaw Ingestion
GoldBusiness KPIs

Medallion Architecture Design

The pipeline follows the industry-standard Bronze/Silver/Gold pattern, separating raw ingestion from cleaned data from final business-layer outputs:

Bronze Layer

Raw ingestion of 3.4M trip records, unaltered and unfiltered

Silver Layer

Cleaned, validated, and deduplicated trip records

Gold Layer

Aggregated business KPIs ready for reporting

SQL Techniques Applied

  • CTEs (Common Table Expressions): Used to break complex transformations into readable, modular steps
  • Window Functions: Applied for running totals, trip ranking, and time-based comparisons across the dataset
  • Subqueries: Used for KPI calculations requiring nested aggregation logic
  • Data Type Correction: Fixed inconsistent timestamp and fare formatting across the raw source

Data Cleaning & Transformation

Before reaching the Gold layer, the dataset went through deep cleaning: removing invalid trip records (negative fares, impossible timestamps), standardising data types, and deduplicating overlapping entries — ensuring the final business layer reflects trustworthy, analysis-ready data.

Methodology & Approach

  1. Bronze Ingestion: Loaded raw 3.4M-row taxi trip dataset into PostgreSQL without transformation
  2. Silver Cleaning: Applied validation rules, removed invalid records, corrected data types
  3. Gold Aggregation: Built KPI tables using CTEs and window functions for reporting
  4. Pipeline Documentation: Structured the full ETL process to be reusable and reproducible

Tools & Technologies

PostgreSQL CTEs Window Functions Data Modelling ETL Medallion Architecture

Value Delivered

  • A reusable ETL pipeline structure applicable to any large-scale raw dataset
  • Clean, validated business KPIs ready for reporting or dashboarding
  • Demonstrated ability to design data architecture at scale (3.4M+ rows)
  • Reproducible SQL logic documented for handover to another analyst