# anandjha90/end-to-end-data-analytics-project_banking architecture

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

- Interactive diagram: https://gitdiagram.com/anandjha90/end-to-end-data-analytics-project_banking
- Repository: https://github.com/anandjha90/end-to-end-data-analytics-project_banking

## Overview

##### Project type and purpose
- This is an end-to-end data analytics pipeline for “Czechoslovakia Banking Financial Data Analysis.”
- It ingests raw banking CSVs, performs data cleaning and transformation via SQL scripts (including automated stored procedures), and delivers insights through a Power BI dashboard.

##### File structure
- Top-level directories:
- AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS: SQL scripts and tests for scheduling stored-procedure execution
- Datasets_Raw / Datasets_Cleaned: raw CSV sources vs. cleaned CSV outputs
- SQL_SCRIPT: transformation scripts, ERD model, and Snowflake documentation
- README.md, Problem_statement.pdf, Data Manipulation docx, Dashboard_PBI.pdf
- Key configuration/build: the .mwb ERD file, Snowflake continuous loading docx, czec_bank_json_policies.txt (likely policy specs).

##### README for additional insights
- Eight source tables (Account, Card, Client, Disposition, District, Loan, Order, Transaction).
- Objectives: data cleaning, exploratory analysis, predictive modelling, answer business questions.
- Outputs: cleaned datasets, SQL-based transformation, Power BI dashboard.

a. Components
1. Data Sources
– Raw CSV files (Datasets_Raw/*)
2. Ingestion & Transformation Layer
– SQL_SCRIPT directory: ETL scripts, stored procedures
– AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS: scheduling/automation engine
3. Data Storage
– Relational database (Snowflake or MySQL as per .mwb & docs)
– Cleaned CSVs as staging (Datasets_Cleaned/*)
4. Analytical Layer
– Ad-hoc SQL queries (automation_query.sql, TASK_CREATION_SCRIPT.sql)
5. Visualization
– Power BI dashboard (Dashboard_PBI.pdf)
6. Documentation & Metadata
– ER.pdf, Problem_statement.pdf, czec_bank_json_policies.txt

b. Relationships & data flows
- Raw CSVs → Ingestion scripts → Staging (Cleaned CSVs)
- Staging → Stored procedures/automated tasks → Database tables
- Database → Ad-hoc queries → BI semantic model
- BI model → Power BI dashboard
- ERD & metadata feed back into design and governance

c. Architectural patterns & design principles
– ETL pipeline pattern: Extract (raw CSV) → Transform (SQL scripts) → Load (database)
– Modular automation: separate directory for automating stored-procedure tasks
– Layered approach: source, staging, curated, presentation
– Schema-on-read vs. schema-on-write: raw CSVs are schemaless until ingested

d. Relevant technologies & frameworks
– SQL (MySQL Workbench .mwb ERD)
– Snowflake (continuous data loading docx)
– Power BI for dashboard
– Shell or task scheduler for automation scripts

##### Detailed capture
- Don’t over-generalize: include the “AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS” component explicitly.
- Both raw CSV folders and cleaned CSV folders.
- Call out any external services or policies (e.g., JSON policy file for compliance).

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Source Layer
    subgraph "Source Layer"
        RawCSV["Raw CSV Files"]:::dataSource
    end

    %% Transformation & Automation Layer
    subgraph "Transformation & Automation" 
        ETL_CORE["Core Transformation SQL Scripts"]:::transformation
        ETL_ADDITIONAL["Additional SQL Scripts"]:::transformation
        ETL_SF_LOAD["Snowflake Continuous Load Script"]:::transformation
        SP_AUTOMATION["Stored Procedure Automation Engine"]:::transformation
        TASK_CREATION["Task Creation Script"]:::transformation
        AUTO_QUERY["Automation Query Script"]:::transformation
        TESTS["Automation Tests"]:::transformation
    end

    %% Storage Layer
    subgraph "Storage Layer"
        StagingCSV["Cleaned CSV Files (Staging)"]:::storage
        DB["Relational DB (Snowflake/MySQL)"]:::storage
        ERD_MODEL["ERD Model (.mwb)"]:::storage
        ER_DIAGRAM["ER Diagram (PDF)"]:::storage
    end

    %% Analytical Layer
    subgraph "Analytical Layer"
        ADHOC["Ad-hoc SQL Queries"]:::storage
        TESTS2["Query Tests"]:::storage
    end

    %% Visualization Layer
    subgraph "Visualization Layer"
        BI_MODEL["BI Semantic Model"]:::bi
        PBI["Power BI Dashboard"]:::bi
    end

    %% Documentation & Metadata
    subgraph "Documentation & Metadata"
        README["Project README"]:::docs
        PROBLEM["Problem Statement"]:::docs
        DATA_DOC["Data Manipulation Doc"]:::docs
        SF_DOC["Snowflake Continuous Load Doc"]:::docs
        POLICIES["Compliance Policies"]:::docs
    end

    %% Data Flows
    RawCSV -->|"Extract"| ETL_CORE
    RawCSV --> ETL_ADDITIONAL
    RawCSV --> ETL_SF_LOAD
    ETL_CORE -->|Transform| StagingCSV
    ETL_ADDITIONAL --> StagingCSV
    ETL_SF_LOAD --> StagingCSV
    StagingCSV -->|"Load via SP"| SP_AUTOMATION
    SP_AUTOMATION --> DB
    TASK_CREATION --> SP_AUTOMATION
    AUTO_QUERY --> SP_AUTOMATION
    SP_AUTOMATION --> TESTS
    DB -->|"Query"| ADHOC
    ADHOC --> BI_MODEL
    BI_MODEL -->|"Visualize"| PBI

    %% Documentation Flows
    ERD_MODEL --> DB
    ER_DIAGRAM --> DB
    POLICIES --> DB
    README --> ETL_CORE
    PROBLEM --> ETL_CORE
    DATA_DOC --> ETL_CORE
    SF_DOC --> ETL_SF_LOAD

    %% Click Events
    click RawCSV "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/tree/main/Datasets_Raw/"
    click StagingCSV "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/tree/main/Datasets_Cleaned/"
    click ETL_CORE "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/CZEC_BANK_DATA_TRANSFORMATION (1).sql"
    click ETL_ADDITIONAL "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/SQL_SCRIPT (1).sql"
    click ETL_SF_LOAD "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/cont_data_load.sql"
    click ERD_MODEL "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/ERD (1).mwb"
    click ER_DIAGRAM "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/Diagram/ER.pdf"
    click TASK_CREATION "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS/TASK_CREATION_SCRIPT.sql"
    click AUTO_QUERY "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS/automation_query.sql"
    click TESTS "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/tree/main/AUTOMATING_STORED_PROCEDURE_THROUGH_TASKS/test"
    click PBI "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/DASHBOARD_PBI.pdf"
    click README "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/README.md"
    click PROBLEM "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/Problem_statement.pdf"
    click DATA_DOC "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/Data Munupulation _cleaning.docx.pdf"
    click SF_DOC "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/Snowflake_continuous_data_loading (1) (1).docx"
    click POLICIES "https://github.com/anandjha90/end-to-end-data-analytics-project_banking/blob/main/SQL_SCRIPT/czec_bank_json_policies (1).txt"

    %% Classes
    classDef dataSource fill:#bfdbfe,stroke:#1e3a8a,color:#1e3a8a,stroke-width:1px;
    classDef transformation fill:#bbf7d0,stroke:#166534,color:#166534,stroke-width:1px;
    classDef storage fill:#fed7aa,stroke:#9a3412,color:#9a3412,stroke-width:1px;
    classDef bi fill:#ddd6fe,stroke:#5b21b6,color:#5b21b6,stroke-width:1px;
    classDef docs fill:#e5e7eb,stroke:#374151,color:#374151,stroke-width:1px;
```
