NYC Taxi ETL Project – FareSense

Project Overview

FareSense is an end-to-end ETL pipeline for NYC Taxi trip data. It leverages Airflow , Azure Blob Storage , and Snowflake to implement a Bronze → Silver → Gold architecture:

• Bronze Layer: Raw data ingestion from Azure Blob Storage
• Silver Layer: Cleaned and validated data in Snowflake
• Gold Layer: Aggregated summary tables for reporting and BI dashboards

The workflow is automated with Airflow DAGs, including a Master DAG that orchestrates the sequence from ingestion to Snowflake staging.

---

Project Structure

text

FareSense/
├── dags/                # Airflow DAGs
├── logs/                # Runtime logs
├── plugins/             # Custom plugins
├── scripts/             # Utility scripts
├── configs/             # Configuration files
├── docker-compose.yml   # Airflow + Postgres setup
├── requirements.txt     # Python dependencies
├── .gitignore           # Files/folders to ignore in Git
└── README.md            # Project documentation

---

Setup Instructions

1. Clone Repository

bash

git clone <repo-url>
cd FareSense

2. Install Dependencies

bash

pip install -r requirements.txt

3. Start Airflow with Docker

bash

docker-compose up -d
docker ps  # Verify containers

4. Initialize Airflow

bash

docker exec -it <airflow_container_name> bash
airflow db init
airflow users create \
  --username admin \
  --firstname Admin \
  --lastname User \
  --role Admin \
  --email admin@example.com \
  --password admin

5. Access Airflow UI

• URL: http://localhost:8080
• Login: admin / admin

---

ETL Workflow

Dataset

• Source: NYC Taxi & Limousine Commission (TLC) trip records from nyc.gov
• Example file used for testing: green_tripdata_2020-01.csv
• Full datasets are available at the official NYC portal.

DAGs Overview

• azure_blob_test.py – Tests Azure Blob connection
• copy_to_bronze.py – Copies raw CSV files from source/ to bronze/
• bronze_to_snowflake.py – Loads Bronze data into Snowflake staging
• nyc_taxi_master_dag.py – Orchestrates all DAGs sequentially

Workflow Diagram

text

Azure Blob Storage (source)
       ↓
azure_blob_test.py
       ↓
copy_to_bronze.py
       ↓
bronze_to_snowflake.py
       ↓
Snowflake (Silver & Gold transformations)

---

Data Architecture

Bronze Layer

• Raw data ingestion from Azure Blob Storage
• Maintains original data format
• No transformations applied

Silver Layer

• Data validation and cleaning
• Schema enforcement
• Deduplication and quality checks
• Stored in Snowflake staging tables

Gold Layer

• Business-level aggregations
• Optimized for reporting and analytics
• Summary tables for BI dashboards

---

Technology Stack

• Orchestration: Apache Airflow
• Cloud Storage: Azure Blob Storage
• Data Warehouse: Snowflake
• Containerization: Docker
• Language: Python