# anandvai/sql_data_warehouse_project architecture

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

- Interactive diagram: https://gitdiagram.com/anandvai/sql_data_warehouse_project
- Repository: https://github.com/anandvai/sql_data_warehouse_project

## Overview

##### Project type and purpose
- This is a Data Warehouse & Analytics project built with a Medallion Architecture (Bronze, Silver, Gold).
- Purpose: Ingest raw ERP/CRM CSV data, cleanse/transform it through staged layers, model into a star-schema data warehouse, and produce SQL‐based reports for analytics.

##### File structure
- Top‐level “datasets/” holds source CSV files (ERP & CRM).
- “scripts/bronze , scripts/silver , scripts/gold” contain ETL SQL scripts for each layer.
- “ETL_Updated_Data/bronze, silver, gold” directories mirror the layered data outputs as CSV exports.
- “Fact_&_Dim_Report_final_Data/gold” holds final report exports.
- “tests/” contains SQL quality‐check scripts for Silver and Gold.

##### README insights
- Confirms usage of SQL Server Express for hosting the DW.
- Highlights the Bronze → Silver → Gold ETL pipeline, star schema modeling for dimensions (“dim_customers”, “dim_products”) and fact table (“fact_sales”), and reporting scripts.

##### Main components & interactions
a. Source Systems (CSV files)
- datasets/erp_*.csv and datasets/crm_*.csv
b. Bronze Layer (Raw Ingestion)
- SQL scripts: scripts/bronze/ddl_bronze.sql & procedure_load_bronze.sql
- Storage: ETL_Updated_Data/bronze/*.csv and staging tables in SQL Server
c. Silver Layer (Cleansed & Standardized)
- SQL scripts: scripts/silver/ddl_silver.sql & procedure_load_silver.sql
- Storage: ETL_Updated_Data/silver/*.csv and intermediate tables
- Quality checks: tests/quality_checks_silver.sql
d. Gold Layer (Analytics‐Ready Star Schema)
- SQL scripts: scripts/gold/ddl_gold.sql (creates dim and fact tables)
- Storage: ETL_Updated_Data/gold/*.csv & SQL Server permanent tables
- Quality checks: tests/quality_checks_gold.sql
e. Reporting / BI
- SQL-based reports: Fact_&_Dim_Report_final_Data/gold/*.csv
- Consumers: SSMS, BI dashboards
f. Supporting components
- SQL Server Express (database engine)
- SSMS (GUI)
- Notion (project management)

##### Architectural patterns & key principles
- Medallion Architecture: clear layering (Bronze raw, Silver curated, Gold modeled)
- Star schema modeling in Gold for high‐performance analytics
- Modular ETL: each layer has its own SQL scripts, schema, and data stores
- Data quality enforced by separate test scripts
- Version control for scripts and docs

7. Docs/drawio files off to the side, showing architecture and data flow documentation.

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Source
    Source["Source CSV Files (datasets/)"]:::data
    click Source "https://github.com/anandvai/sql_data_warehouse_project/tree/main/datasets/"

    %% Bronze Layer
    subgraph "Bronze Layer"
        BronzeDDL["DDL Bronze ETL"]:::bronze
        click BronzeDDL "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/bronze/ddl_bronze.sql"
        BronzeLoad["Load Procedure Bronze"]:::bronze
        click BronzeLoad "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/bronze/procedure_load_bronze.sql"
        BronzeStorage(("Bronze Storage\nCSV & Staging")):::bronze
        click BronzeStorage "https://github.com/anandvai/sql_data_warehouse_project/tree/main/ETL_Updated_Data/bronze/"
    end

    %% Silver Layer
    subgraph "Silver Layer"
        SilverDDL["DDL Silver ETL"]:::silver
        click SilverDDL "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/silver/ddl_silver.sql"
        SilverLoad["Load Procedure Silver"]:::silver
        click SilverLoad "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/silver/procedure_load_silver.sql"
        SilverStorage(("Silver Storage\nCSV & Intermediate")):::silver
        click SilverStorage "https://github.com/anandvai/sql_data_warehouse_project/tree/main/ETL_Updated_Data/silver/"
        SilverQC{"Quality Checks\n(Silver)"}:::test
        click SilverQC "https://github.com/anandvai/sql_data_warehouse_project/blob/main/tests/quality_checks_silver.sql"
    end

    %% Gold Layer
    subgraph "Gold Layer"
        GoldDDL["DDL Gold ETL\n(Star Schema)"]:::gold
        click GoldDDL "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/gold/ddl_gold.sql"
        GoldStorage(("Gold Storage\nDimension & Fact")):::gold
        click GoldStorage "https://github.com/anandvai/sql_data_warehouse_project/tree/main/ETL_Updated_Data/gold/"
        GoldQC{"Quality Checks\n(Gold)"}:::test
        click GoldQC "https://github.com/anandvai/sql_data_warehouse_project/blob/main/tests/quality_checks_gold.sql"
    end

    %% Reporting
    Reporting["Reports & BI Outputs\n(Fact_&_Dim_Report_final_Data)"]:::report
    click Reporting "https://github.com/anandvai/sql_data_warehouse_project/tree/main/Fact_&_Dim_Report_final_Data/gold/"

    %% Supporting Components
    InitDB["Database Init\n(SQL Server Express)"]:::support
    click InitDB "https://github.com/anandvai/sql_data_warehouse_project/blob/main/scripts/init_database.sql"
    Docs["Documentation\n(docs/)"]:::docs
    click Docs "https://github.com/anandvai/sql_data_warehouse_project/tree/main/docs/"
    Readme["Project Overview\n(README.md)"]:::docs
    click Readme "https://github.com/anandvai/sql_data_warehouse_project/blob/main/README.md"

    %% Main Flows
    Source --> BronzeDDL --> BronzeLoad --> BronzeStorage
    BronzeStorage --> SilverDDL --> SilverLoad --> SilverStorage
    SilverStorage --> GoldDDL --> GoldStorage --> Reporting

    %% Quality Feedback
    SilverStorage --> SilverQC --> SilverDDL
    GoldStorage --> GoldQC --> GoldDDL

    %% Environment Boundary
    subgraph "SQL Server Express Environment"
        InitDB
        BronzeDDL
        BronzeLoad
        BronzeStorage
        SilverDDL
        SilverLoad
        SilverStorage
        SilverQC
        GoldDDL
        GoldStorage
        GoldQC
        Reporting
    end

    %% Documentation Side
    Docs
    Readme

    %% Styles
    classDef bronze fill:#cd7f32,stroke:#333,stroke-width:1px;
    classDef silver fill:#c0c0c0,stroke:#333,stroke-width:1px;
    classDef gold fill:#ffd700,stroke:#333,stroke-width:1px;
    classDef test fill:#f4a460,stroke:#333,stroke-width:1px,stroke-dasharray: 5 5;
    classDef data fill:#f0e68c,stroke:#333,stroke-width:1px;
    classDef report fill:#add8e6,stroke:#333,stroke-width:1px;
    classDef support fill:#98fb98,stroke:#333,stroke-width:1px;
    classDef docs fill:#e0ffff,stroke:#333,stroke-width:1px;
```
