NYC Taxi Data Engineering

Large trip-level mobility datasets can become difficult to analyze when raw files are queried directly or loaded without a clear schema and indexing strategy. The project focused on converting raw NYC yellow taxi trip data into a structured PostgreSQL analytics workflow that supports efficient time-based, spatial, passenger, and revenue analysis.

• NYC yellow taxi trip records at trip-level granularity
• More than 40 million records in the analytical pipeline
• Raw Parquet files as the ingestion source
• Cleaned and transformed PostgreSQL analytical tables
• Time, location, passenger, and fare/revenue-related fields
• PostgreSQL as the primary analytical database

1. 01

   Data ingestion

   Loaded raw taxi trip files into a structured workflow from Parquet sources.

2. 02

   Data cleaning

   Handled schema consistency, column types, missingness, and analytical readiness.

3. 03

   Database modeling

   Created PostgreSQL tables designed for time-filtered and analytical queries.

4. 04

   Indexing

   Added indexing strategies to improve query speed on high-volume filters.

5. 05

   SQL analytics

   Extracted temporal, spatial, passenger, and revenue insights through optimized SQL.

• Used PostgreSQL instead of flat-file querying for repeatable analytics
• Converted raw Parquet data into cleaned analytical tables
• Applied indexing for time-filtered queries
• Focused on reproducible SQL workflows
• Measured query latency before and after optimization

• Temporal trip patterns
• Location-based trip behavior
• Passenger count patterns
• Fare/revenue analysis
• Query performance testing

This project demonstrates large-scale ETL thinking, relational schema design, query optimization, SQL analytics, and the ability to turn raw data into a structured analytical database workflow.

• Analysis depends on source data consistency
• Larger production workflows would need orchestration and monitoring
• Additional geospatial enrichment could improve spatial analysis
• Further optimization could include partitioning or materialized views