# ramakrishnachepuri/sql_datawarehouse architecture

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

- Interactive diagram: https://gitdiagram.com/ramakrishnachepuri/sql_datawarehouse
- Repository: https://github.com/ramakrishnachepuri/sql_datawarehouse

## Overview

##### Project type and purpose
- This is a Data Warehouse & Analytics project implementing a Medallion Architecture (Bronze, Silver, Gold layers).
- The goal is to ingest raw CSV data from ERP and CRM sources into SQL Server, apply progressive transformations, and deliver business-ready fact and dimension tables for reporting and analytics.

##### File structure
- datasets/
– Source_Crm/*.csv (customer and product data, sales details)
– Source_erp/*.csv (ERP extracts for customers, locations, product categories)
- scripts/
– Bronze/ddl_bronze.sql & proc_load_bronze.sql (raw‐data table creation and load logic)
– Silver/ddl_silver.sql & proc_load_silver.sql (cleansing/standardization logic)
– Gold/Create ddl_gold.sql (star schema table DDLs)
- docs/data_catalog.md (detailed data dictionary)
- test/quality_checks_*.sql (data quality rules for silver and gold layers)
- README.md (project overview, architecture, and requirements)

##### README for architectural insights
- Confirms a three-layer Medallion approach:
– Bronze: raw ingestion
– Silver: cleansing & normalization
– Gold: star‐schema modeling for analytics
- Highlights SQL Server as the target platform and CSV as source format
- Emphasizes ETL pipelines, data modeling, and SQL-based analytics

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% External Sources
    subgraph "Source Systems"
        CRM[/CRM Source CSV Files/]:::external
        ERP[/ERP Source CSV Files/]:::external
    end

    %% Bronze Layer
    subgraph "Bronze Layer" 
        direction TB
        BronzeDDL["Bronze DDL Script"]:::bronze
        BronzeLoad["Bronze Load Procedure"]:::bronze
        BronzeDB[(Bronze Raw Tables)]:::bronze
    end

    %% Silver Layer
    subgraph "Silver Layer"
        direction TB
        SilverDDL["Silver DDL Script"]:::silver
        SilverLoad["Silver Load Procedure"]:::silver
        SilverDB[(Silver Cleaned Tables)]:::silver
    end

    %% Gold Layer
    subgraph "Gold Layer"
        direction TB
        GoldDDL["Gold Star Schema DDL"]:::gold
        GoldDB[(Gold Fact & Dimension Tables)]:::gold
    end

    %% Data Quality Tests
    subgraph "Data Quality Checks"
        QC_Silver["Silver Quality Checks"]:::test
        QC_Gold["Gold Quality Checks"]:::test
    end

    %% Documentation & Reporting
    Docs["Data Catalog"]:::docs
    README["Project Overview (README)"]:::docs
    Analytics["Analytics & Reporting"]:::analytics

    %% Data Flows
    CRM --> BronzeDDL
    ERP --> BronzeDDL
    BronzeDDL --> BronzeLoad
    BronzeLoad --> BronzeDB

    BronzeDB --> SilverDDL
    SilverDDL --> SilverLoad
    SilverLoad --> SilverDB

    SilverDB --> GoldDDL
    GoldDDL --> GoldDB

    GoldDB --> Analytics

    QC_Silver -.-> SilverDB
    QC_Gold -.-> GoldDB

    Docs --- Analytics
    README --- Analytics

    %% Click Events
    click CRM "https://github.com/ramakrishnachepuri/sql_datawarehouse/tree/main/datasets/Source_Crm"
    click ERP "https://github.com/ramakrishnachepuri/sql_datawarehouse/tree/main/datasets/Source_erp"
    click BronzeDDL "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/scripts/Bronze/ddl_bronze.sql"
    click BronzeLoad "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/scripts/Bronze/proc_load_bronze.sql"
    click SilverDDL "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/scripts/Silver/ddl_silver.sql"
    click SilverLoad "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/scripts/Silver/proc_load_silver.sql"
    click GoldDDL "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/scripts/Gold/Create ddl_gold.sql"
    click QC_Silver "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/test/quality_checks_silver.sql"
    click QC_Gold "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/test/quality_checks_gold.sql"
    click Docs "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/docs/data_catalog.md"
    click README "https://github.com/ramakrishnachepuri/sql_datawarehouse/blob/main/README.md"

    %% Styles
    classDef external fill:#ADD8E6,stroke:#000,stroke-width:1px
    classDef bronze fill:#B87333,stroke:#000,stroke-width:1px
    classDef silver fill:#C0C0C0,stroke:#000,stroke-width:1px
    classDef gold fill:#FFD700,stroke:#000,stroke-width:1px
    classDef test fill:#FFB6C1,stroke:#000,stroke-width:1px,stroke-dasharray: 5 5
    classDef docs fill:#98FB98,stroke:#000,stroke-width:1px
    classDef analytics fill:#E6E6FA,stroke:#000,stroke-width:1px
```
