# myke-ajayi/dbt_pipeline architecture

> Architecture diagram of the GitHub repository myke-ajayi/dbt_pipeline, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-03-28). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/myke-ajayi/dbt_pipeline
- Repository: https://github.com/myke-ajayi/dbt_pipeline

## Overview

##### Project Type and Purpose
- Project Type: This is a data transformation and pipeline project. It leverages DBT (data build tool) for managing SQL-based transformations and DuckDB for data storage and querying.
- Purpose: To ingest source datasets related to NYC farmers markets and community gardens ("farms"), load them using Python scripts into DuckDB, and then use DBT to transform the raw data into well-defined tables (markets, farms, and farm_to_market). Additionally, the project supports data validation, testing (with integration tests and reusable macros), and CI/CD practices.

##### File Structure for Key Architectural Insights
- Dbt_pipeline Directory:
- Contains the majority of DBT-specific components: models (organized into staging, intermediate, and product layers), profiles, packages, integration tests, macros (both generic tests and jinja helpers), and configuration files (dbt_project.yml).
- The presence of folders such as “models”, “target”, “dbt_packages”, and “packages.yml” clearly indicates the use of DBT best practices with a modular, layered architecture.
- DuckDB Directory:
- Contains Python scripts (export.py, load.py, transform.py) and SQL files that define raw data ingestion and preprocessing. This indicates that part of the ETL/ELT process is handled outside DBT.
- Data Directory:
- Stores the raw source files (in data/source_data) and the final outputs (CSV, parquet, shapefiles) produced by the pipeline.
- CI/CD and Testing:

##### Mapping Out the Main Components and Their Relationships
- Data Sources:
- External raw data from NYC open data portals, manually downloaded and placed in data/source_data.
- Data Ingestion and Preprocessing:
- Python scripts (export.py, load.py, transform.py in the DuckDB folder) process and load the raw CSV/GeoJSON files into databases.
- Database Layer:
- DuckDB used as the underlying database engine. The data is stored in files (data/duckdb.db, data/farm_to_market_dbt.db) and managed through SQL scripts.
- DBT Layer (ETL/ELT Pipeline):
- dbt models organized into staging (raw or lightly transformed data), intermediate (aggregated or joined data), and product (final curated tables: markets, farms, farm_to_market).
- Reusable macros and generic tests (located in dbt_packages/dbt_utils and macros folders) help maintain consistency.
- The DBT DAG (as referenced by the included "Dbt_pipeline/docs/dbt-dag.png") quickly communicates the transformation dependencies.
- Testing and Validation:
- Integration tests and unit tests within the dbt_utils packages and integration_tests directories ensure that each transformation is correct.
- CI/CD Integration:
- Configuration files (CircleCI, GitHub workflows, tox.ini, pytest.ini) denote that automated testing and deployment pipelines are in place.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% 1. Data Sources
    subgraph "1. Data Sources"
        DS["External Data Sources"]:::dataSources
    end

    %% 2. Data Ingestion (DuckDB Scripts)
    subgraph "2. Data Ingestion (DuckDB Scripts)"
        E["Export.py"]:::ingestion
        L["Load.py"]:::ingestion
        T["Transform.py"]:::ingestion
        SQL["SQL Scripts"]:::ingestion
    end

    %% 3. Database Layer
    subgraph "3. Database Layer"
        RAW["DuckDB: Raw Data"]:::database
        DBTDB["DuckDB: DBT Data"]:::database
    end

    %% 4. DBT Pipeline
    subgraph "4. DBT Pipeline"
        CP["DBT Project Config"]:::dbt
        DP["DBT Profiles"]:::dbt
        ST["Staging Models"]:::dbt
        IM["Intermediate Models"]:::dbt
        PT["Product Models"]:::dbt
        PK["DBT Packages"]:::dbt
        IT["dbt_utils Integration Tests"]:::dbt
        MAC["dbt_utils Macros"]:::dbt
    end

    %% 5. CI/CD Integration
    subgraph "5. CI/CD Integration"
        CI["CircleCI Config"]:::cicd
        GH["GitHub Workflows"]:::cicd
        TOX["tox.ini"]:::cicd
        PY["pytest.ini"]:::cicd
    end

    %% Connections between components
    DS -->|"RawDataIngestion"| E
    DS -->|"RawDataIngestion"| L
    DS -->|"RawDataIngestion"| T
    DS -->|"RawDataIngestion"| SQL

    E -->|"loads raw data"| RAW
    L -->|"loads raw data"| RAW
    T -->|"loads raw data"| RAW
    SQL -->|"prepares raw data"| RAW

    RAW -->|"data for transformation"| ST

    CP -->|"configures"| ST
    DP -->|"configures"| ST

    ST -->|"aggregated into"| IM
    IM -->|"curated into"| PT

    PK --> IT
    PK --> MAC

    PT -->|"final output"| DBTDB

    %% CI/CD connections from final product
    PT -->|"triggers"| CI
    PT -->|"triggers"| GH
    PT -->|"triggers"| TOX
    PT -->|"triggers"| PY

    %% Styles
    classDef dataSources fill:#ff9999,stroke:#660000,stroke-width:2px;
    classDef ingestion fill:#ccffcc,stroke:#006600,stroke-width:2px;
    classDef database fill:#cce5ff,stroke:#003366,stroke-width:2px;
    classDef dbt fill:#ffffcc,stroke:#666600,stroke-width:2px;
    classDef cicd fill:#e6ccff,stroke:#330066,stroke-width:2px;

    %% Click Events
    click DS "https://github.com/myke-ajayi/dbt_pipeline/tree/main/data/source_data"
    click E "https://github.com/myke-ajayi/dbt_pipeline/blob/main/DuckDB/export.py"
    click L "https://github.com/myke-ajayi/dbt_pipeline/blob/main/DuckDB/load.py"
    click T "https://github.com/myke-ajayi/dbt_pipeline/blob/main/DuckDB/transform.py"
    click SQL "https://github.com/myke-ajayi/dbt_pipeline/tree/main/DuckDB/sql/"
    click RAW "https://github.com/myke-ajayi/dbt_pipeline/blob/main/data/duckdb.db"
    click DBTDB "https://github.com/myke-ajayi/dbt_pipeline/blob/main/data/farm_to_market_dbt.db"
    click CP "https://github.com/myke-ajayi/dbt_pipeline/blob/main/Dbt_pipeline/dbt_project.yml"
    click DP "https://github.com/myke-ajayi/dbt_pipeline/blob/main/Dbt_pipeline/profiles.yml"
    click ST "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/models/staging"
    click IM "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/models/intermediate"
    click PT "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/models/product"
    click PK "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/dbt_packages"
    click IT "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/dbt_packages/dbt_utils/integration_tests"
    click MAC "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/dbt_packages/dbt_utils/macros"
    click CI "https://github.com/myke-ajayi/dbt_pipeline/blob/main/Dbt_pipeline/dbt_packages/dbt_utils/.circleci"
    click GH "https://github.com/myke-ajayi/dbt_pipeline/tree/main/Dbt_pipeline/dbt_packages/dbt_utils/.github/workflows"
    click TOX "https://github.com/myke-ajayi/dbt_pipeline/blob/main/tox.ini"
    click PY "https://github.com/myke-ajayi/dbt_pipeline/blob/main/pytest.ini"
```
