# aditya-7-7/sql-data-warehouse-analytics architecture

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

- Interactive diagram: https://gitdiagram.com/aditya-7-7/sql-data-warehouse-analytics
- Repository: https://github.com/aditya-7-7/sql-data-warehouse-analytics

## Overview

##### Project type and purpose
- This is a Data Warehouse + Analytics project built on Microsoft SQL Server.
- It implements a Medallion (Bronze/Silver/Gold) architecture, ingesting CSV files from CRM and ERP sources, performing ETL, modeling data into star schemas, and supporting analytics/reporting.

##### File structure
- datasets/ : raw CSV files organized by source (data-analytics, source_crm, source_erp).
- scripts/
- bronze/           – DDL + load procedures for Bronze layer.
- silver/           – DDL + load procedures for Silver layer.
- gold/             – DDL for modeling Gold tables (dimensions + fact).
- analytics/        – SQL scripts generating reports and CSV snapshots from Gold.
- init_database.sql – creates database/schema scaffolding.
- tests/             : quality check SQL scripts for Silver and Gold layers.
- docs/              : documentation placeholder.
- data-catalogue.md  : metadata/catalog definitions.

##### Key README insights
- ETL Automation via T-SQL stored procedures.
- Bronze: raw ingestion; Silver: cleansing/enrichment; Gold: star schemas & aggregations.
- Analytics scripts produce business-ready CSV outputs.
- Tech stack: SQL Server, T-SQL, CSV, star schema modeling.

##### Main components
a. Data Sources
- CRM system → CSV dumps
- ERP system → CSV dumps
b. Ingestion / Bronze Layer
- Stored procedures (scripts/bronze) load raw CSV into bronze tables.
c. Transformation / Silver Layer
- Procedures (scripts/silver) perform cleansing, normalization, enrichment.
d. Modeling / Gold Layer
- DDL (scripts/gold) builds dim_customers, dim_products, fact_sales in star schema.
e. Analytics & Reporting
- SQL scripts (scripts/analytics) generate aggregated outputs and CSVs for BI.
f. Metadata & QA
- tests/ quality_checks_silver.sql, quality_checks_gold.sql.
g. BI consumers (Power BI, Tableau, Excel) pulling Gold/CVS outputs.

##### Relationships & interactions
- Bronze tables → Silver transformation → Silver tables.
- Silver tables → Gold modeling → Gold tables/views.
- Gold tables → Analytics scripts → CSV outputs → BI tools.
- QA scripts run against Silver & Gold tables; metadata docs reference each layer.

##### Architectural patterns & principles
- Medallion Architecture (bronze/silver/gold layered).
- Star schema in Gold (dimension + fact tables for performance).
- Modular ETL stored procedures and DDL scripts.
- Automated quality checks for data integrity and lineage.

##### Technologies & frameworks
- Microsoft SQL Server (database / engine).
- T-SQL stored procedures and scripts.
- CSV file ingestion.
- BI integration: CSV snapshots for downstream tools.

##### Element checklist
- Clear component labels (e.g., “Bronze Raw Tables”).
- Callouts for test scripts and data catalog.
- External services / BI consumers shown at the edge.
- Versioning or script folders noted for traceability.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Infrastructure
    init["DB Init Scaffold\n(init_database.sql)"]:::infra

    %% Data Sources
    subgraph "Data Sources"
        DS1["CRM CSV Files"]:::data-source
        DS2["ERP CSV Files"]:::data-source
    end

    %% Bronze Layer
    subgraph "Bronze Layer (scripts/bronze)"
        bronze_ddl["DDL Bronze\n(ddl_bronze.sql)"]:::bronze
        bronze_proc["Load Procedures\n(proc_load_bronze.sql)"]:::bronze
        bronze_tbl["Bronze Raw Tables"]:::storage
    end

    %% Silver Layer
    subgraph "Silver Layer (scripts/silver)"
        silver_ddl["DDL Silver\n(ddl_silver.sql)"]:::silver
        silver_proc["Load Procedures\n(proc_load_silver.sql)"]:::silver
        silver_tbl["Silver Tables"]:::storage
    end

    %% Gold Layer
    subgraph "Gold Layer (scripts/gold)"
        gold_ddl["Star Schema DDL\n(ddl_gold.sql)"]:::gold
        gold_tbl["Gold Star Schema Tables"]:::storage
    end

    %% Analytics & Reporting
    subgraph "Analytics & Reporting (scripts/analytics)"
        analytics_init["Analytics Sandbox Init\n(init_database.sql)"]:::infra
        analytics_proc["Analytics SQL Scripts\n(*.sql)"]:::analytics
        analytics_csv["CSV Outputs"]:::output
    end

    %% BI Consumers
    subgraph "BI Consumers"
        BI1["Power BI"]:::external
        BI2["Tableau"]:::external
        BI3["Excel"]:::external
    end

    %% QA & Metadata
    subgraph "QA & Metadata"
        qc_silver["Silver QA Checks\n(quality_checks_silver.sql)"]:::qa
        qc_gold["Gold QA Checks\n(quality_checks_gold.sql)"]:::qa
        data_cat["Data Catalogue\n(data-catalogue.md)"]:::qa
        docs["Documentation\n(docs/)"]:::qa
    end

    %% Data Flow
    init --> DS1
    init --> DS2
    DS1 --> bronze_proc
    DS2 --> bronze_proc
    bronze_ddl --> bronze_tbl
    bronze_proc --> bronze_tbl
    bronze_tbl --> silver_proc
    silver_ddl --> silver_tbl
    silver_proc --> silver_tbl
    silver_tbl --> gold_ddl
    gold_ddl --> gold_tbl
    gold_tbl --> analytics_proc
    analytics_init --> analytics_proc
    analytics_proc --> analytics_csv
    analytics_csv --> BI1
    analytics_csv --> BI2
    analytics_csv --> BI3

    %% QA Links
    qc_silver --> silver_tbl
    qc_gold --> gold_tbl
    data_cat -.-> bronze_tbl
    data_cat -.-> silver_tbl
    data_cat -.-> gold_tbl
    docs -.-> bronze_tbl
    docs -.-> silver_tbl
    docs -.-> gold_tbl

    %% Click Events
    click DS1 "https://github.com/aditya-7-7/sql-data-warehouse-analytics/tree/main/datasets/source_crm"
    click DS2 "https://github.com/aditya-7-7/sql-data-warehouse-analytics/tree/main/datasets/source_erp"
    click init "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/init_database.sql"
    click bronze_ddl "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/bronze/ddl_bronze.sql"
    click bronze_proc "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/bronze/proc_load_bronze.sql"
    click silver_ddl "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/silver/ddl_silver.sql"
    click silver_proc "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/silver/proc_load_silver.sql"
    click gold_ddl "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/gold/ddl_gold.sql"
    click analytics_init "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/analytics/init_database.sql"
    click analytics_proc "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/scripts/analytics/*.sql"
    click data_cat "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/data-catalogue.md"
    click docs "https://github.com/aditya-7-7/sql-data-warehouse-analytics/tree/main/docs/"
    click qc_silver "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/tests/quality_checks_silver.sql"
    click qc_gold "https://github.com/aditya-7-7/sql-data-warehouse-analytics/blob/main/tests/quality_checks_gold.sql"

    %% Styles
    classDef data-source fill:#AED6F1,stroke:#3498DB
    classDef bronze fill:#CD7F32,stroke:#B87333
    classDef silver fill:#BCC6CC,stroke:#A9A9A9
    classDef gold fill:#FFD700,stroke:#DAA520
    classDef analytics fill:#ABEBC6,stroke:#27AE60
    classDef qa fill:#F5B7B1,stroke:#C0392B
    classDef storage fill:#E8DAEF,stroke:#8E44AD
    classDef output fill:#A9DFBF,stroke:#229954
    classDef external fill:#FADBD8,stroke:#C0392B
    classDef infra fill:#D5DBDB,stroke:#566573
```
