# sanketb04/data-warehousing-using-postgressql- architecture

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

- Interactive diagram: https://gitdiagram.com/sanketb04/data-warehousing-using-postgressql-
- Repository: https://github.com/sanketb04/data-warehousing-using-postgressql-

## Overview

##### Project type and purpose
- This is a data-engineering project implementing a modern data warehouse on PostgreSQL.
- It follows the Medallion Architecture (Bronze → Silver → Gold) to ingest raw CSV data, cleanse and standardize it, then produce business-ready star schemas for reporting.

##### File structure
- Top-level directories:
- datasets/ (raw CSV inputs from ERP and CRM)
- scripts/bronze, /silver, /gold (ETL layers)
- tests/ (SQL queries validating Silver and Gold outputs)
- Key configuration: init_database.sql to set up schemas.
- Each layer directory contains DDL (schema definitions) and stored-procedure scripts for loading/transformation.

##### README for additional insights
- Confirms purpose: build data warehouse, perform ETL, model star schemas, run analytics.

##### Elements
- Clear labels: “datasets/erp/*.csv”, “Bronze Schema”, “Silver Schema”, “Gold Schema”, “Analytics Queries”
- Star schema (centered star or node graph in gold block)

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Source Systems
    subgraph "Source Systems"
        direction TB
        crm_cust_info["cust_info.csv"]:::source
        crm_prd_info["prd_info.csv"]:::source
        crm_sales_details["sales_details.csv"]:::source
        erp_CUST_AZ12["CUST_AZ12.csv"]:::source
        erp_LOC_A101["LOC_A101.csv"]:::source
        erp_PX_CAT_G1V2["PX_CAT_G1V2.csv"]:::source
    end

    %% Initialization
    initDB["init_database.sql"]:::process

    %% Bronze Layer
    subgraph "Bronze Schema"
        direction TB
        ddlBronze["ddl_bronze.sql"]:::bronze
        procBronze["proc_load_bronze.sql"]:::bronze
    end

    %% Silver Layer
    subgraph "Silver Schema"
        direction TB
        ddlSilver["ddl_silver.sql"]:::silver
        procSilver["proc_load_silver.sql"]:::silver
    end

    %% Gold Layer
    subgraph "Gold Schema"
        direction TB
        ddlGold["ddl_gold.sql"]:::gold
    end

    %% Analytics / Reporting
    subgraph "Analytics & Tests"
        direction TB
        queriesSilver["queries_silver.sql"]:::analytics
        queriesGold["queries_gold.sql"]:::analytics
        BI["BI Dashboards"]:::analytics
    end

    %% Documentation
    docs["data_catalog.md"]:::docs

    %% Connections
    crm_cust_info --> procBronze
    crm_prd_info --> procBronze
    crm_sales_details --> procBronze
    erp_CUST_AZ12 --> procBronze
    erp_LOC_A101 --> procBronze
    erp_PX_CAT_G1V2 --> procBronze

    initDB --> ddlBronze
    ddlBronze --> procBronze
    procBronze --> ddlSilver
    ddlSilver --> procSilver
    procSilver --> ddlGold
    ddlGold --> queriesSilver
    ddlGold --> queriesGold
    queriesSilver --> BI
    queriesGold --> BI

    initDB --> docs

    %% Click Events
    click crm_cust_info "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/crm/cust_info.csv"
    click crm_prd_info "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/crm/prd_info.csv"
    click crm_sales_details "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/crm/sales_details.csv"
    click erp_CUST_AZ12 "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/erp/CUST_AZ12.csv"
    click erp_LOC_A101 "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/erp/LOC_A101.csv"
    click erp_PX_CAT_G1V2 "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/datasets/erp/PX_CAT_G1V2.csv"
    click initDB "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/init_database.sql"
    click ddlBronze "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/bronze/ddl_bronze.sql"
    click procBronze "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/bronze/proc_load_bronze.sql"
    click ddlSilver "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/silver/ddl_silver.sql"
    click procSilver "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/silver/proc_load_silver.sql"
    click ddlGold "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/scripts/gold/ddl_gold.sql"
    click queriesSilver "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/tests/queries_silver.sql"
    click queriesGold "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/tests/queries_gold.sql"
    click docs "https://github.com/sanketb04/data-warehousing-using-postgressql-/blob/main/docs/data_catalog.md"

    %% Styles
    classDef source fill:#f0f0f0,stroke:#888,stroke-width:1px
    classDef process fill:#e8e8e8,stroke:#555,stroke-width:1px
    classDef bronze fill:#cd7f32,stroke:#8b4a0e,stroke-width:1px
    classDef silver fill:#c0c0c0,stroke:#888,stroke-width:1px
    classDef gold fill:#ffd700,stroke:#b8860b,stroke-width:1px
    classDef analytics fill:#add8e6,stroke:#31708f,stroke-width:1px
    classDef docs fill:#e0e0e0,stroke:#666,stroke-width:1px
```
