# eppursimuove9/bank-loan-analysis architecture

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

- Interactive diagram: https://gitdiagram.com/eppursimuove9/bank-loan-analysis
- Repository: https://github.com/eppursimuove9/bank-loan-analysis

## Overview

##### Project type and purpose
- This is a data-analysis/BI pipeline focused on bank loan portfolio performance. It uses raw loan data (CSV), SQL for extraction and transformation, and Tableau for interactive visualization. The goal is to generate actionable insights and dashboards for stakeholders.

##### File structure
- Top-level artifact:
- data/financial_loan.csv → raw source data
- sql_queries/01_*.sql → ETL and aggregation scripts
- assets/*.png → finalized dashboard screenshots
- docs/… → problem statement and query documentation
- No application servers or traditional front/back ends—this is an ETL + visualization pipeline.
- No container, infra as code, or deployment manifests—this runs ad hoc or via a simple script/IDE.

##### Key insights from README
- Methodology: three stages—Data Extraction & Aggregation (SQL), EDA, and Visualization in Tableau.
- Users interact with a Tableau Public dashboard (assets store screenshots).
- Stakeholders use filters on loan status, purpose, grade.

a. Main components
1. Raw Data Layer
– CSV file (financial_loan.csv) stored on disk or data lake
2. ETL/SQL Processing Layer
– SQL Scripts (01_kpi_summary.sql, …) executed against the CSV (via a SQL engine like SQLite, Postgres, or a data-warehouse)
– Intermediate tables/views for cleaned and aggregated data
3. Documentation & Queries
– docs/ contains problem statement and detailed query explanations
4. Visualization Layer
– Tableau Desktop / Tableau Public
– Interactive dashboard hosted on Tableau Public
5. Assets
– Static images of dashboards for README or reporting
6. End Users / Stakeholders
– Business analysts, risk managers viewing dashboards

b. Relationships & interactions

c. Architectural patterns & design principles
– ETL pipeline: separate extraction (raw CSV), transformation (SQL), load into BI tool
– Modular SQL scripts for each analytical objective (KPI summary, status analysis, dimensional analysis)
– Single source of truth: the processed tables feed all dashboard visuals
– Documentation-driven: query docs and problem statement guide pipeline development

d. Technologies, frameworks, libraries
– Storage: flat file (CSV) or data-warehouse
– Processing: SQL engine (specify Postgres/SQLite/big query as used)
– BI: Tableau Desktop & Tableau Public
– Version control: Git (via .gitignore, LICENSE)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Layer
    subgraph "Data Layer"
        RawData["financial_loan.csv"]:::storage
    end

    %% Processing Layer
    subgraph "Processing Layer"
        SQLEngine["SQL Engine"]:::processing
        subgraph "SQL Queries"
            KPI["01_kpi_summary.sql"]:::processing
            Status["02_loan_status_analysis.sql"]:::processing
            Dim["03_dimensional_analysis.sql"]:::processing
        end
        AggData["Aggregated Tables/Views"]:::storage
    end

    %% Documentation Layer
    subgraph "Documentation"
        DocsDir["docs/"]:::docs
        ProblemStmt["BANK LOAN REPORT PROBLEM STATEMENT.pdf"]:::docs
        QueryDoc["BANK LOAN REPORT QUERY DOCUMENT.pdf"]:::docs
    end

    %% Visualization Layer
    subgraph "Visualization"
        TableauDesktop["Tableau Desktop"]:::presentation
        Assets["assets/"]:::presentation
        TableauPublic["Tableau Public Dashboard"]:::presentation
    end

    %% User Layer
    Stakeholders["End Users/Stakeholders"]:::user

    %% Data Flow
    RawData -->|"INGEST"| SQLEngine
    SQLEngine -->|"EXECUTE"| KPI
    SQLEngine -->|"EXECUTE"| Status
    SQLEngine -->|"EXECUTE"| Dim
    KPI -->|"GENERATE"| AggData
    Status -->|"GENERATE"| AggData
    Dim -->|"GENERATE"| AggData

    AggData -->|"CONNECT"| TableauDesktop
    TableauDesktop -->|"EXPORT PNG"| Assets
    TableauDesktop -->|"PUBLISH"| TableauPublic
    TableauPublic -->|"SERVES DASHBOARD"| Stakeholders
    Stakeholders -->|"INTERACT"| TableauPublic

    %% Click Events
    click RawData "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/data/financial_loan.csv"
    click SQLEngine "https://github.com/eppursimuove9/bank-loan-analysis/tree/main/sql_queries/"
    click KPI "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/sql_queries/01_kpi_summary.sql"
    click Status "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/sql_queries/02_loan_status_analysis.sql"
    click Dim "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/sql_queries/03_dimensional_analysis.sql"
    click DocsDir "https://github.com/eppursimuove9/bank-loan-analysis/tree/main/docs/"
    click ProblemStmt "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/docs/BANK LOAN REPORT PROBLEM STATEMENT.pdf"
    click QueryDoc "https://github.com/eppursimuove9/bank-loan-analysis/blob/main/docs/BANK LOAN REPORT QUERY DOCUMENT.pdf"
    click Assets "https://github.com/eppursimuove9/bank-loan-analysis/tree/main/assets/"

    %% Styles
    classDef storage fill:#cce5ff,stroke:#333,stroke-width:1px
    classDef processing fill:#ffe5b4,stroke:#333,stroke-width:1px
    classDef docs fill:#e8e8e8,stroke:#333,stroke-width:1px
    classDef presentation fill:#d4edda,stroke:#333,stroke-width:1px
    classDef user fill:#f0f0f0,stroke:#333,stroke-width:1px
```
