# shubhamdalvi1999/data-transformation-using-dbt architecture

> Architecture diagram of the GitHub repository shubhamdalvi1999/data-transformation-using-dbt, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-22). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/shubhamdalvi1999/data-transformation-using-dbt
- Repository: https://github.com/shubhamdalvi1999/data-transformation-using-dbt

## Overview

##### Project Type and Purpose
- This is a production-ready data pipeline (ETL/ELT) built with Apache Airflow (orchestration), DBT (transformations & tests), and PostgreSQL (data storage), all containerized via Docker Compose.
- It ingests weekly CFTC JSON and Eurostat Excel feeds, persists raw data, applies DBT SQL models, enforces data quality, and surfaces analytics-ready tables.

##### Key File-Structure Insights
- Airflow/
- dags/01_ingest_cftc_report.py & 02_ingest_eurostat_prices.py – two ingestion DAGs
- PostgreSQL/
- Dockerfile & Schema_definitions/Baseline_Schema.sql – raw & public schemas
- dsec_dbt/
- models/First_Transform/*.sql – raw→refined models
- dbt_project.yml, profiles.yml, macros/, seeds/, tests/
- docker-compose.yml – service orchestration for Airflow, Worker, Scheduler, DBT, PostgreSQL
- Vagrant-setup – optional local provisioning

##### Core Components
- CFTC API (JSON)
- Eurostat Excel feed
b. Orchestration Layer (rectangle grouping)
- Airflow Webserver & Scheduler (Gunicorn)
- Airflow Worker(s) / Celery Executors
- DAG definitions: CFTC Loader, Eurostat Loader, DBT run, Quality checks
- PostgreSQL Raw Schema (“raw.05_COT_…”, “raw.07_Eurostat_…” tables)
- PostgreSQL Public Schema (refined tables)
d. Transformation Layer (oval or rectangle)
- DBT Service container running dbt run & dbt test
- Models: 01_Refined_COT_Report (incremental), 02_Refined_Eurostat (full refresh)
- Data quality tests: not null, range checks, unique keys
- Docker Network linking Airflow, DBT, PostgreSQL
- Environment variables & profiles

##### Relationships and Data Flows
- Task → PostgresHook → Raw tables in “raw” schema
- Trigger DBT job → reads raw tables → writes refined tables to “public” schema
- DBT test suite → data quality results → if pass, surface final tables
- Airflow orchestrates weekly schedules; dependencies DAG→DBT→tests

##### Architectural Patterns & Principles
- Modular DAGs implementing hook-operator pattern (PostgresHook + pandas)
- Incremental vs full refresh modeling in DBT
- Separation of concerns: ingestion vs transformation vs testing
- Container isolation for reproducible environments

##### Essential Elements
- Component labels: service names, table names, model names
- Boundaries for Docker containers / network
- Scheduling annotations (weekly triggers)
- Materialization strategies (incremental vs table)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Sources
    CFTC["CFTC API (JSON)"]:::external
    Eurostat["Eurostat Excel Feed"]:::external

    %% Docker Network Boundary
    subgraph "Docker Compose Network"
        style DockerNetwork stroke-dasharray: 5 5

        %% Orchestration Layer - Airflow
        subgraph "Airflow"
            Web["Airflow Webserver"]:::orchestration
            Scheduler["Airflow Scheduler"]:::orchestration
            Worker["Airflow Worker"]:::orchestration
            Ingest1["01_ingest_cftc_report.py"]:::orchestration
            Ingest2["02_ingest_eurostat_prices.py"]:::orchestration
        end

        %% Transformation Layer - DBT
        subgraph "DBT"
            DBTService["DBT Service"]:::transform
            Model1["01_Refined_COT_Report"]:::transform
            Model2["02_Refined_Eurostat"]:::transform
            SchemaYML["schema.yml"]:::transform
            SourcesYML["sources.yml"]:::transform
            Macros["macros/"]:::transform
            TestsDir["tests/"]:::transform
            Seeds["seeds/"]:::transform
            Snapshots["snapshots/"]:::transform
        end

        %% Storage Layer - PostgreSQL
        subgraph "PostgreSQL"
            RawDB["Raw Schema"]:::storage
            PublicDB["Public Schema"]:::storage
        end

        %% Service Definitions
        DockerCompose["docker-compose.yml"]:::orchestration
    end

    %% Vagrant & Root Context
    Vagrantfile["Vagrantfile"]:::infrastructure
    Bootstrap["bootstrap-root.sh"]:::infrastructure
    RootDocker["Dockerfile"]:::orchestration
    Requirements["requirements.txt"]:::orchestration
    Readme["README.md"]:::orchestration
    DiagramFile["System_Diagram.drawio"]:::orchestration

    %% Data Flows & Dependencies
    CFTC -->|"HTTP GET → pandas DF"| Ingest1
    Eurostat -->|"HTTP GET → pandas DF"| Ingest2

    Scheduler -->|"schedule Fri ET"| Ingest1
    Scheduler -->|"schedule Thu GMT"| Ingest2
    Worker -->|runs| Ingest1
    Worker -->|runs| Ingest2

    Ingest1 -->|"PostgresHook(db) → to_sql replace"| RawDB
    Ingest2 -->|"PostgresHook(db) → to_sql replace"| RawDB

    Scheduler -->|"trigger dbt run"| DBTService
    Worker -->|"execute dbt run & test"| DBTService

    RawDB -->|"dbt run incremental"| Model1
    RawDB -->|"dbt run full_refresh"| Model2

    Model1 -->|"writes refined tables"| PublicDB
    Model2 -->|"writes refined tables"| PublicDB

    DBTService -->|"dbt test suite"| TestsDir
    TestsDir -->|"on pass"| PublicDB

    %% Click Events
    click Ingest1 "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/Airflow/dags/01_ingest_cftc_report.py"
    click Ingest2 "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/Airflow/dags/02_ingest_eurostat_prices.py"
    click DockerCompose "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/docker-compose.yml"
    click RawDB "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/PostgreSQL/Schema_definitions/Baseline_Schema.sql"
    click PublicDB "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/PostgreSQL/Schema_definitions/Baseline_Schema.sql"
    click RawDB "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/PostgreSQL/Dockerfile"
    click DBTService "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/dsec_dbt/Dockerfile"
    click DBTService "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/dbt_project.yml"
    click DBTService "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/profiles.yml"
    click Model1 "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/models/First_Transform/01_Refined_COT_Report.sql"
    click Model2 "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/models/First_Transform/02_Refined_Eurostat.sql"
    click SchemaYML "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/models/First_Transform/schema.yml"
    click SourcesYML "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/dsec_dbt/models/First_Transform/sources.yml"
    click Macros "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/dsec_dbt/macros/"
    click TestsDir "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/dsec_dbt/tests/"
    click Seeds "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/dsec_dbt/seeds/"
    click Snapshots "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/dsec_dbt/snapshots/"
    click Vagrantfile "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/vagrant-setup/Vagrantfile"
    click Bootstrap "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/vagrant-setup/bootstrap-root.sh"
    click RootDocker "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/tree/main/Dockerfile"
    click Requirements "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/requirements.txt"
    click Readme "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/README.md"
    click DiagramFile "https://github.com/shubhamdalvi1999/data-transformation-using-dbt/blob/main/System_Diagram.drawio"

    %% Styles
    classDef external fill:#D8B4FE,stroke:#9B4DCA
    classDef orchestration fill:#93C5FD,stroke:#1E3A8A
    classDef storage fill:#86EFAC,stroke:#166534
    classDef transform fill:#FDBA74,stroke:#C2410C
    classDef infrastructure fill:#E5E7EB,stroke:#6B7280.implode
```
