# iamsohaib7/e-commerce-etl architecture

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

- Interactive diagram: https://gitdiagram.com/iamsohaib7/e-commerce-etl
- Repository: https://github.com/iamsohaib7/e-commerce-etl

## Overview

##### Main Components
- Data Sources: Represent the raw data files (CSV files in the Dataset/Raw-Dataset directory) as the starting point.
- Data Extraction Layer:
– Include components for extracting data from the PostgreSQL database (referenced in the README) and from Amazon S3 using the scripts in the Extract directory (db_to_s3.py, extract_s3.py).
- Intermediate Storage (Amazon S3):
– Show that extracted data is temporarily stored in Amazon S3.
- Data Transformation Layer:
– Represent the notebook and any transformation scripts (Transform/transform.ipynb) which use Pandas or PyArrow for cleaning and processing.
- Data Loading Layer:
– Include the loading scripts found in the Load directory (db_loader.py, s3_loader.py, snowflake_loader.py) that push data into the data warehouse.
- Data Warehouse (Snowflake):
– Clearly represent the Snowflake data warehouse where structured and transformed data is stored.
- Downstream Analytical Layer:

##### Relationships and Data Flow
- Following transformation, indicate the flow into the Data Loading component, which connects to the Snowflake data warehouse.

##### Architectural Patterns and Design Principles
- ETL Pipeline Pattern: Clearly denote the extract, transform, and load stages as distinct but connected components.
- Data Flow / Pipeline: Illustrate the linear flow from raw data ingestion to the final BI layer.
- Decoupling of Components: Emphasize the separation between storage (S3, Postgres, Snowflake), transformation (Python/Pandas scripts), and presentation (BI tools), showcasing modular design.
- Scalability Considerations: Note that using intermediate storage (Amazon S3) and a cloud data warehouse (Snowflake) enables scaling as data volumes increase.

##### Specify Relevant Technologies and Frameworks
- Python scripts for ETL (extraction, transformation, and loading).
- PostgreSQL for the initial data repository.
- Amazon S3 as an intermediate storage layer.
- Snowflake as the target data warehouse.
- Pandas and PyArrow for data transformation.
- Power BI/Tableau for reporting and dashboarding.
- Docker (docker-compose.yml) if containerization is used for deploying parts of the pipeline.

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "Data Sources"
        A["Raw Data (CSV Files)"]:::datasource
        B["PostgreSQL Database"]:::datasource
    end

    subgraph "Extraction Layer"
        C["Extract from PostgreSQL (db_to_s3.py)"]:::extraction
        D["Extract from Amazon S3 (extract_s3.py)"]:::extraction
    end

    E["Amazon S3 Intermediate Storage"]:::storage

    subgraph "Transformation Layer"
        F["Transformation (Pandas Notebook)"]:::transformation
    end

    subgraph "Loading Layer"
        G["DB Loader"]:::loading
        H["S3 Loader"]:::loading
        I["Snowflake Loader"]:::loading
    end

    J["Snowflake Data Warehouse"]:::warehouse

    K["BI Dashboard (Power BI/Tableau)"]:::analytics

    L["Orchestration (Docker)"]:::orchestration

    %% Data Sources to Extraction Layer
    B --> C
    A --> D

    %% Extraction Layer to Intermediate Storage
    C --> E
    D --> E

    %% Intermediate Storage to Transformation Layer
    E --> F

    %% Transformation Layer to Loading Layer
    F --> G
    F --> H
    F --> I

    %% Loading Layer to Data Warehouse
    G --> J
    H --> J
    I --> J

    %% Data Warehouse to BI Dashboard
    J --> K

    %% Orchestration linking to Extraction Layer (dotted arrows)
    L -.->|orchestrates| C
    L -.->|orchestrates| D

    %% Click Events
    click A "https://github.com/iamsohaib7/e-commerce-etl/tree/main/Dataset/Raw-Dataset/E-Commerce/"
    click C "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Extract/db_to_s3.py"
    click D "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Extract/extract_s3.py"
    click F "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Transform/transform.ipynb"
    click G "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Load/db_loader.py"
    click H "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Load/s3_loader.py"
    click I "https://github.com/iamsohaib7/e-commerce-etl/blob/main/Load/snowflake_loader.py"
    click L "https://github.com/iamsohaib7/e-commerce-etl/blob/main/docker-compose.yml"

    %% Styles
    classDef datasource fill:#AED6F1,stroke:#2980B9,stroke-width:2px;
    classDef extraction fill:#F9E79F,stroke:#F1C40F,stroke-width:2px;
    classDef storage fill:#A9DFBF,stroke:#27AE60,stroke-width:2px;
    classDef transformation fill:#FAD7A0,stroke:#E67E22,stroke-width:2px;
    classDef loading fill:#D2B4DE,stroke:#8E44AD,stroke-width:2px;
    classDef warehouse fill:#F5B7B1,stroke:#C0392B,stroke-width:2px;
    classDef analytics fill:#D5F5E3,stroke:#16A085,stroke-width:2px;
    classDef orchestration fill:#F8C471,stroke:#D35400,stroke-width:2px;
```
