# manishreddykovvuri/data-warehousing-assignment architecture

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

- Interactive diagram: https://gitdiagram.com/manishreddykovvuri/data-warehousing-assignment
- Repository: https://github.com/manishreddykovvuri/data-warehousing-assignment

## Overview

##### Project Type and Purpose
- This is a data‐warehousing project that implements an end‑to‑end ETL pipeline to consolidate HR, Finance, and Operations data into a star schema and expose KPI views for analytics.
- Key goals: raw data ingestion from Excel, cleaning/transformation in Python, staging in PostgreSQL, dimensional modeling (star schema), SCD Type 2 for employee history, audit & DQ logging, and KPI view layer.

##### File Structure
- 02_Extract_and_transform_raw_data/
- A2_hr_etl.py, B2_finance_etl.py, C2_ops_etl.py – domain‑specific extract/transform scripts
- ET_combined.py – orchestrates all three ETL flows in one run
- 03_load_into_fact_and_dim_tables/
- A3_load_dim_emp.sql – SCD2 load for dim_employee
- B3_load_fin_facts.sql, C3_load_dim_ops.sql – loads for other dims/facts
- TL_combine.sql – combined fact table loading
- 04_KPI/
- KPIs.sql – view definitions for headcount, expenses, downtime, etc.
- requirements.txt, Excel files (dirty source data)

##### Extract Insights from README
- Star schema with shared dims: employee, department, time, expense_type, process, location
- Fact tables: fact_employee (daily snapshot), fact_expenses, fact_downtime
- Staging layer in `dw.staging_*` tables
- Audit and data quality logs in `dw.audit_log` and `dw.data_quality_log`
- Hybrid ETL/ELT: Python cleans and stages, SQL performs dimensional loads and SCD2 logic
- Role‑based access controls applied at view layer (HR, Finance, Super User roles)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Source Systems
    subgraph "Source Systems"
        HR["HR_Dataset_Dirty.xlsx"]:::sourceetl
        Finance["Finance_Dataset_Dirty.xlsx"]:::sourceetl
        Ops["Operations_Dataset_Dirty.xlsx"]:::sourceetl
    end

    %% ETL Layer
    subgraph "ETL Layer"
        A2["Python ETL - HR"]:::sourceetl
        B2["Python ETL - Finance"]:::sourceetl
        C2["Python ETL - Operations"]:::sourceetl
        ETComb["ETL Orchestration"]:::sourceetl
    end

    %% Staging Area
    subgraph "Staging Area"
        SE["dw.staging_employee"]:::staging
        SF["dw.staging_finance"]:::staging
        SO["dw.staging_operations"]:::staging
    end

    %% Data Warehouse Load
    subgraph "Data Warehouse Load"
        DEmp["dw.dim_employee (SCD2)"]:::warehouse
        DOps["dw.dim_operations"]:::warehouse
        FExp["dw.fact_expenses"]:::warehouse
        FComb["dw.fact_combined"]:::warehouse
    end

    %% Logging & Monitoring
    subgraph "Logging & Monitoring"
        DQ["dw.data_quality_log"]:::logging
        Audit["dw.audit_log"]:::logging
    end

    %% Presentation Layer
    subgraph "Presentation Layer"
        KPI["KPI Views"]:::presentation
        Users["BI Tools / Users"]:::presentation
    end

    %% Documentation & Dependencies
    subgraph "Project Docs"
        Req["requirements.txt"]:::logging
        RD["README.md"]:::logging
    end

    %% Data Flows
    HR -->|reads| A2
    Finance -->|reads| B2
    Ops -->|reads| C2

    ETComb --> A2
    ETComb --> B2
    ETComb --> C2

    A2 -->|writes| SE
    B2 -->|writes| SF
    C2 -->|writes| SO

    A2 -->|logs DQ| DQ
    B2 -->|logs DQ| DQ
    C2 -->|logs DQ| DQ

    SE -->|SCD2 logic| DEmp
    SF -->|loads| FExp
    SO -->|loads| DOps

    DEmp -->|loads| FComb
    FExp -->|combines| FComb
    DOps -->|combines| FComb

    DEmp -->|logs Audit| Audit
    FExp -->|logs Audit| Audit
    DOps -->|logs Audit| Audit
    FComb -->|logs Audit| Audit

    DEmp --> KPI
    FExp --> KPI
    DOps --> KPI
    FComb --> KPI

    KPI --> Users

    %% Click Events
    click A2 "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/02_Extract_and_transform_raw_data/A2_hr_etl.py"
    click B2 "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/02_Extract_and_transform_raw_data/B2_finance_etl.py"
    click C2 "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/02_Extract_and_transform_raw_data/C2_ops_etl.py"
    click ETComb "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/02_Extract_and_transform_raw_data/ET_combined.py"
    click DEmp "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/03_load_into_fact_and_dim_tables/A3_load_dim_emp.sql"
    click DOps "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/03_load_into_fact_and_dim_tables/C3_load_dim_ops.sql"
    click FExp "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/03_load_into_fact_and_dim_tables/B3_load_fin_facts.sql"
    click FComb "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/03_load_into_fact_and_dim_tables/TL_combine.sql"
    click KPI "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/04_KPI/KPIs.sql"
    click Req "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/requirements.txt"
    click RD "https://github.com/manishreddykovvuri/data-warehousing-assignment/blob/main/README.md"

    %% Styles
    classDef sourceetl fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef staging fill:#FFA500,stroke:#333,stroke-width:1px
    classDef warehouse fill:#90EE90,stroke:#333,stroke-width:1px
    classDef presentation fill:#DDA0DD,stroke:#333,stroke-width:1px
    classDef logging fill:#D3D3D3,stroke:#333,stroke-width:1px
```
