How namratajambhulkar/nyc311-elt-data-pipeline works
Project Type and Purpose
• This is an end-to-end ELT data pipeline for NYC 311 complaint data.
• It extracts data via a Python script, loads JSON files to AWS S3, transforms data in Snowflake using dbt, orchestrates with Prefect, automates via GitHub Actions, and visualizes in Power BI.
File Structure
• ingestion/ → Python extract script (fetch_2024_nyc311.py)
• nyc311_dbt/ → dbt project (staging → marts/dim & marts/fct)
• prefect/ → Prefect flow orchestration (flow.py)
• .github/workflows/ → GitHub Actions CI (dbt-run.yml)
• dashboard/ → Power BI dashboard file (.pbix) and screenshots
• doc_pngs/ → existing architecture & lineage screenshots
Main Components
a. Extraction Layer
• Python script (ingestion/fetch_2024_nyc311.py)
• Dependencies: requests, boto3
b. Landing Storage
• AWS S3 bucket (raw JSON files)
c. Data Warehouse
• Snowflake (raw stage, transformed staging and marts)
• dbt models organized in staging/, marts/dim/, marts/fct/
• dbt macros, tests, docs
d. Orchestration & Scheduling
• Prefect (prefect/flow.py) coordinating extract→load→transform→test→docs
• GitHub Actions (dbt-run.yml) triggering dbt runs on push to main
e. Visualization
• Power BI dashboard (dashboard/nyc311_dashboard.pbix)
f. Documentation & Monitoring
• dbt docs, lineage graphs (doc_pngs/)
• logs/ and nyc311_dbt/logs/ for dbt run outputs
Architectural Patterns & Design Principles
• ELT pattern: Extract → Load → Transform in-database
• Modular dbt project: separation of staging vs. dimensional/fact layers
• Star schema in marts: dim_agency, dim_complaint_type, dim_date, dim_location, dim_status + fct_311_2024
• Infrastructure as code: GitHub Actions for CI/CD, Prefect for pipeline orchestration
• Clear separation of concerns: ingestion, storage, transformation, orchestration, visualization
Detail Level
• Environment boundaries (local dev vs. cloud) if needed.
• Configuration files (.env → Prefect & dbt credential injection).
• Critical tests (dbt tests) and documentation generation.
• Logging paths (logs/, nyc311_dbt/logs/) to indicate operational monitoring.