# namratajambhulkar/nyc311-elt-data-pipeline architecture

> Architecture diagram of the GitHub repository namratajambhulkar/nyc311-elt-data-pipeline, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-08-23). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/namratajambhulkar/nyc311-elt-data-pipeline
- Repository: https://github.com/namratajambhulkar/nyc311-elt-data-pipeline

## Overview

##### 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.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Extraction Layer
    subgraph "Extraction Layer"
        direction TB
        PythonScript["Python Script\n(fetch_2024_nyc311.py)"]:::extraction
    end

    %% Cloud Infrastructure
    subgraph "Cloud Infrastructure"
        direction TB
        S3["AWS S3 (raw JSON)"]:::storage
        Snowflake["Snowflake Warehouse"]:::storage
    end

    %% Transformation Layer
    subgraph "Transformation (dbt)" 
        direction TB
        DBTConfig["dbt Project Config\ndbt_project.yml"]:::transform
        subgraph "Staging Models"
            direction TB
            StgSQL["stg_nyc311_2024.sql"]:::transform
            StgYML["schema.yml"]:::transform
        end
        subgraph "Marts → Dimensions"
            direction TB
            DimAgency["dim_agency.sql"]:::transform
            DimComplaintType["dim_complaint_type.sql"]:::transform
            DimDate["dim_date.sql"]:::transform
            DimLocation["dim_location.sql"]:::transform
            DimStatus["dim_status.sql"]:::transform
            DimYML["schema.yml"]:::transform
        end
        subgraph "Marts → Facts"
            direction TB
            Fct311["fct_311_2024.sql"]:::transform
            FctDebug["fct_debug_2024.sql"]:::transform
            FctYML["schema.yml"]:::transform
        end
        Macros["Macros Directory"]:::transform
        Tests["Tests Directory"]:::transform
        DBTLogs["dbt Run Logs\ndbt.log"]:::docs
    end

    %% Orchestration & CI/CD
    subgraph "Orchestration & CI/CD"
        direction TB
        PrefectFlow["Prefect Flow\n(flow.py)"]:::orchestrate
        GitHubCI["GitHub Actions\n(dbt-run.yml)"]:::orchestrate
    end

    %% Visualization
    subgraph "Visualization"
        direction TB
        PowerBI["Power BI Dashboard\n(nyc311_dashboard.pbix)"]:::visualize
        Screenshots["Dashboard Screenshots"]:::visualize
    end

    %% Documentation & Monitoring
    subgraph "Documentation & Monitoring"
        direction TB
        DocLineage["Lineage Screenshots"]:::docs
        ExecLogs["Pipeline Execution Logs\n(logs/dbt.log)"]:::docs
    end

    %% Data Flow
    PythonScript -->|"writes JSON"| S3
    S3 -->|"COPY INTO raw tables"| Snowflake
    Snowflake -->|"dbt seed/stage"| StgSQL
    StgSQL --> StgYML
    StgSQL --> DimAgency
    StgSQL --> DimComplaintType
    StgSQL --> DimDate
    StgSQL --> DimLocation
    StgSQL --> DimStatus
    DimAgency --> DimYML
    DimComplaintType --> DimYML
    DimDate --> DimYML
    DimLocation --> DimYML
    DimStatus --> DimYML
    DimYML --> Fct311
    DimYML --> FctDebug
    Fct311 --> FctYML
    FctDebug --> FctYML
    FctYML --> PowerBI

    %% Orchestration flows
    PrefectFlow -->|runs script| PythonScript
    PrefectFlow -->|loads to S3| S3
    PrefectFlow -->|triggers dbt| DBTConfig
    PrefectFlow -->|generates docs| DBTLogs
    GitHubCI -.->|on push to main triggers| DBTConfig
    DBTConfig --> StgSQL
    DBTConfig --> DimAgency
    DBTConfig --> Fct311
    DBTConfig --> Macros
    DBTConfig --> Tests

    %% Documentation & Monitoring flows
    DBTLogs --> DocLineage
    ExecLogs --> PrefectFlow

    %% Click Events
    click PythonScript "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/ingestion/fetch_2024_nyc311.py"
    click DBTConfig "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/dbt_project.yml"
    click StgSQL "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/staging/stg_nyc311_2024.sql"
    click StgYML "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/staging/schema.yml"
    click DimAgency "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/dim_agency.sql"
    click DimComplaintType "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/dim_complaint_type.sql"
    click DimDate "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/dim_date.sql"
    click DimLocation "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/dim_location.sql"
    click DimStatus "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/dim_status.sql"
    click DimYML "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/dim/schema.yml"
    click Fct311 "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/fct/fct_311_2024.sql"
    click FctDebug "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/fct/fct_debug_2024.sql"
    click FctYML "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/models/marts/fct/schema.yml"
    click Macros "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/tree/main/nyc311_dbt/macros/"
    click Tests "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/tree/main/nyc311_dbt/tests/"
    click DBTLogs "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/nyc311_dbt/logs/dbt.log"
    click PrefectFlow "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/prefect/flow.py"
    click GitHubCI "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/.github/workflows/dbt-run.yml"
    click PowerBI "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/dashboard/nyc311_dashboard.pbix"
    click Screenshots "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/tree/main/dashboard/screenshots/"
    click DocLineage "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/tree/main/doc_pngs/"
    click ExecLogs "https://github.com/namratajambhulkar/nyc311-elt-data-pipeline/blob/main/logs/dbt.log"

    %% Styles
    classDef extraction fill:#cce5ff,stroke:#333,stroke-width:1px
    classDef storage fill:#d4edda,stroke:#333,stroke-width:1px
    classDef transform fill:#ffe5b4,stroke:#333,stroke-width:1px
    classDef orchestrate fill:#e2d6f5,stroke:#333,stroke-width:1px
    classDef visualize fill:#fff3cd,stroke:#333,stroke-width:1px
    classDef docs fill:#f0f0f0,stroke:#333,stroke-width:1px
```
