# duyocd/data-warehouse-project architecture

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

- Interactive diagram: https://gitdiagram.com/duyocd/data-warehouse-project
- Repository: https://github.com/duyocd/data-warehouse-project

## Overview

##### Project Type and Purpose
- This is a **data warehouse & analytics project** demonstrating a modern Medallion Architecture (Bronze-Silver-Gold) built on SQL Server.
- It ingests raw CSV files from two source systems (ERP, CRM), applies successive ETL transformations, models data into a star schema, and produces business-ready analytics.

##### File Structure
- Top-level directories:
- `datasets/` holds raw CSVs for ERP and CRM.
- `scripts/` contains three subfolders: `bronze/`, `silver/`, `gold/`—each housing DDL and ETL stored procedures.
- `tests/` holds SQL quality-check scripts for silver and gold layers.
- Supporting files: `README.md`, `LICENSE`, `.gitignore`, `requirements.txt`.

##### Extract Key Architectural Insights from the README
- The pipeline follows Bronze (raw ingestion) → Silver (cleansing & normalization) → Gold (star schema modeling) layers.
- Focuses on SQL Server as the execution engine, with CSV ingestion, transformation stored procedures, and analytics queries.

- **Clear Labels**: Name each component (e.g., “Bronze-DB”, “Silver-ETL”, “Customer_Dim”).
- Gears for ETL jobs
- Files for CSVs
- Shields for quality checks
- Charts for analytics

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Source Systems
    CRM_CSV["CRM CSVs (File)"]:::file
    ERP_CSV["ERP CSVs (File)"]:::file

    %% Bronze Layer
    subgraph "Bronze Layer"
        B_INIT["DB Init Script"]:::file
        B_DDL["Bronze DDL"]:::bronze
        B_ETL["Bronze ETL Jobs (Gear)"]:::bronze
        B_DB["Bronze-DB (Cylinder)"]:::bronze
    end

    %% Silver Layer
    subgraph "Silver Layer"
        S_DDL["Silver DDL"]:::silver
        S_ETL["Silver ETL Jobs (Gear)"]:::silver
        S_DB["Silver-DB (Cylinder)"]:::silver
    end

    %% Gold Layer
    subgraph "Gold Layer"
        G_ETL["Gold Modeling Jobs (Gear)"]:::gold
        G_DB["Gold-DB (Cylinder)"]:::gold
        subgraph "Star Schema"
            Fact["Fact Table"]:::table
            Dim1["Dim Customers"]:::table
            Dim2["Dim Products"]:::table
            Dim3["Dim Dates"]:::table
        end
    end

    %% Analytics & Reporting
    Analytics["Analytics & Reporting (Chart)"]:::chart

    %% Testing & Quality
    subgraph "Testing & Quality"
        Q_SILV["Quality Checks Silver (Shield)"]:::shield
        Q_GOLD["Quality Checks Gold (Shield)"]:::shield
    end

    %% Documentation
    subgraph "Documentation"
        Doc_ARCH["Data Architecture"]:::doc
        Doc_FLOW["Data Flow"]:::doc
        Doc_MODEL["Data Model"]:::doc
        Doc_CAT["Data Catalog"]:::doc
        Doc_NAM["Naming Conventions"]:::doc
    end

    %% Connections
    CRM_CSV --> B_ETL
    ERP_CSV --> B_ETL
    B_INIT --> B_DDL
    B_DDL --> B_ETL
    B_ETL --> B_DB
    B_DB --> S_ETL
    S_DDL --> S_ETL
    S_ETL --> S_DB
    S_DB --> G_ETL
    G_ETL --> G_DB
    G_DB --> Fact
    Fact --> Dim1
    Fact --> Dim2
    Fact --> Dim3
    G_DB --> Analytics

    S_DB --> Q_SILV
    Q_SILV --> S_DB
    G_DB --> Q_GOLD
    Q_GOLD --> G_DB

    %% Click Events
    click CRM_CSV "https://github.com/duyocd/data-warehouse-project/tree/main/datasets/source_crm/"
    click ERP_CSV "https://github.com/duyocd/data-warehouse-project/tree/main/datasets/source_erp/"
    click B_DDL "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/bronze/ddl_bronze.sql"
    click B_ETL "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/bronze/proc_load_bronze.sql"
    click B_INIT "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/init_database.sql"
    click S_DDL "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/silver/ddl_silver.sql"
    click S_ETL "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/silver/proc_load_silver.sql"
    click G_ETL "https://github.com/duyocd/data-warehouse-project/blob/main/scripts/gold/ddl_gold.sql"
    click Q_SILV "https://github.com/duyocd/data-warehouse-project/blob/main/tests/quality_checks_silver.sql"
    click Q_GOLD "https://github.com/duyocd/data-warehouse-project/blob/main/tests/quality_checks_gold.sql"
    click Doc_ARCH "https://github.com/duyocd/data-warehouse-project/blob/main/docs/data_architecture.drawio"
    click Doc_FLOW "https://github.com/duyocd/data-warehouse-project/blob/main/docs/data_flow.drawio"
    click Doc_MODEL "https://github.com/duyocd/data-warehouse-project/blob/main/docs/data_model.drawio"
    click Doc_CAT "https://github.com/duyocd/data-warehouse-project/blob/main/docs/data_catalog.md"
    click Doc_NAM "https://github.com/duyocd/data-warehouse-project/blob/main/docs/naming_conventions.md"

    %% Styles
    classDef bronze fill:#cd7f32,stroke:#333,color:#fff;
    classDef silver fill:#C0C0C0,stroke:#333,color:#000;
    classDef gold fill:#FFD700,stroke:#333,color:#000;
    classDef file fill:#aaf,stroke:#333;
    classDef shield fill:#f88,stroke:#333;
    classDef chart fill:#8f8,stroke:#333;
    classDef doc fill:#bbf,stroke:#333;
    classDef table fill:#fff,stroke:#333;
    class CRM_CSV,ERP_CSV file
    class Doc_ARCH,Doc_FLOW,Doc_MODEL,Doc_CAT,Doc_NAM doc
    class Analytics chart
    class Q_SILV,Q_GOLD shield
```
