# dbt-labs/jaffle_shop_duckdb architecture

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

- Interactive diagram: https://gitdiagram.com/dbt-labs/jaffle_shop_duckdb
- Repository: https://github.com/dbt-labs/jaffle_shop_duckdb

## Overview

##### Main Components
- Raw Data Sources: Represent the CSV seeds (located under the seeds/ directory) that hold the raw tables – raw_customers.csv, raw_orders.csv, and raw_payments.csv.
- ETL/Transformation Layer:
– Staging Models: Models under models/staging (e.g., stg_customers.sql, stg_orders.sql, stg_payments.sql) that clean or prepare the raw data.
– Core Models: Models under models/ (e.g., customers.sql, orders.sql) that build the final analytics-ready tables.
- Project Configuration and Metadata:
– dbt project configuration (dbt_project.yml) and profiles (profiles.yml) that define connection settings to the target data warehouse.
– Documentation, tests, and schema definitions (schema.yml files) that document each model’s structure and tests.
- Target Data Warehouse:
- Developer/Operational Tools:
– CLI Commands & Dev Environment: Components such as dbt CLI (commands like dbt run, dbt build, dbt docs generate/serve), Dockerfile, and Dev Container configuration (.devcontainer.json) that support development and deployment.
– Documentation Generation: dbt Docs that builds interactive documentation and testing capabilities.
– Version Control & CI/CD: GitHub workflows shown by the .github directory, indicating automated testing/integration.

##### Relationships and Data Flow
- That the CSV seeds provide the raw data which is “seeded” into the target database.
- How the staging models feed into the core models to produce analytical tables.
- Core models to the target data warehouse (DuckDB) where the final transformed tables reside.
- Dbt CLI and developer tools as peripheral components that trigger these transformation processes, documentation generation, and testing.

##### Architectural Patterns and Design Principles
- Layered Architecture: Clearly separate the project into layers—raw data (Seeds), intermediate transformations (Staging), and analytics-ready output (Core Models).
- Modular Design: Emphasize the separation of concerns, with individual SQL files performing distinct transformations and tests.
- Self-Contained Configuration: Note how configuration files (YAML files) manage settings, and integration scripts (e.g., Dockerfile, .devcontainer.json) ease the developer experience.
- Lightweight ETL pipelining using dbt commands to manage dependency flows.

##### Specific Technologies and Tools
- dbt: Core transformation and orchestration tool using SQL-based models, testing, and documentation.
- DuckDB: The in-process analytical database powering the project’s target data store.
- CSV Files: As the source for raw data.
- Docker and VSCode Dev Containers: For standardizing the development environment.
- GitHub Actions/Workflows: For continuous validation and automation, as seen in the .github/workflows directory.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Ingestion Layer: CSV Seeds
    subgraph "Data Ingestion (CSV Seeds)"
        seed1["raw_customers.csv"]:::seeds
        seed2["raw_orders.csv"]:::seeds
        seed3["raw_payments.csv"]:::seeds
    end

    %% Transformation Layer: ETL/Transformation
    subgraph "ETL/Transformation"
        subgraph "Staging Models"
            stg1["stg_customers.sql"]:::staging
            stg2["stg_orders.sql"]:::staging
            stg3["stg_payments.sql"]:::staging
        end
        subgraph "Core Models"
            core1["customers.sql"]:::core
            core2["orders.sql"]:::core
        end
    end

    %% Target Data Warehouse
    db["DuckDB Database (jaffle_shop.duckdb)"]:::db

    %% Configuration & Metadata
    subgraph "Configuration & Metadata"
        conf1["dbt_project.yml"]:::config
        conf2["profiles.yml"]:::config
        conf3["schema.yml (Core Models)"]:::config
        conf4["schema.yml (Staging Models)"]:::config
    end

    %% Developer & Operations Tools
    subgraph "Developer & Ops Tools"
        dt1["dbt CLI & Docs"]:::tools
        dt2["Dev Container (.devcontainer.json)"]:::tools
        dt3["Dockerfile"]:::tools
        dt4["CI/CD & Version Control (.github)"]:::tools
    end

    %% Data Flow from CSV Seeds to Staging Models
    seed1 -->|"feeds"| stg1
    seed2 -->|"feeds"| stg2
    seed3 -->|"feeds"| stg3

    %% Transformation Flow from Staging Models to Core Models
    stg1 -->|"transforms"| core1
    stg2 -->|"transforms"| core2

    %% Core Models materialize into DuckDB
    core1 -->|"materializes"| db
    core2 -->|"materializes"| db

    %% Configuration influences transformations and target DB
    conf1 ---|"configures"| stg1
    conf2 ---|"configures"| stg2
    conf3 ---|"defines"| core1
    conf4 ---|"defines"| core2
    conf1 ---|"connects"| db

    %% Developer Tools trigger transformations and deployment
    dt1 ---|"triggers"| stg1
    dt1 ---|"triggers"| core1
    dt1 ---|"executes"| db
    dt2 ---|"supports"| dt1
    dt3 ---|"supports"| dt1
    dt4 ---|"automates"| dt1

    %% Styles
    classDef seeds fill:#FFDDC1,stroke:#8B4513,stroke-width:2px,color:#000;
    classDef staging fill:#C1E1FF,stroke:#00008B,stroke-width:2px,color:#000;
    classDef core fill:#ADD8E6,stroke:#00008B,stroke-width:2px,color:#000;
    classDef db fill:#FFB6C1,stroke:#8B0000,stroke-width:2px,color:#000;
    classDef config fill:#C1FFC1,stroke:#006400,stroke-width:2px,color:#000;
    classDef tools fill:#E6C9FF,stroke:#800080,stroke-width:2px,color:#000;

    %% Click Events for CSV Seeds
    click seed1 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/seeds/raw_customers.csv"
    click seed2 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/seeds/raw_orders.csv"
    click seed3 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/seeds/raw_payments.csv"

    %% Click Events for Staging Models
    click stg1 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/staging/stg_customers.sql"
    click stg2 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/staging/stg_orders.sql"
    click stg3 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/staging/stg_payments.sql"

    %% Click Events for Core Models
    click core1 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/customers.sql"
    click core2 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/orders.sql"

    %% Click Events for Configuration & Metadata
    click conf1 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/dbt_project.yml"
    click conf2 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/profiles.yml"
    click conf3 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/schema.yml"
    click conf4 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/models/staging/schema.yml"

    %% Click Events for Developer & Ops Tools
    click dt2 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/.devcontainer.json"
    click dt3 "https://github.com/dbt-labs/jaffle_shop_duckdb/tree/duckdb/Dockerfile"
    click dt4 "https://github.com/dbt-labs/jaffle_shop_duckdb/blob/duckdb/.github/workflows/validate_on_platforms.yml"
```
