# cvincent27/de_05_etl_vlille_spark architecture

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

- Interactive diagram: https://gitdiagram.com/cvincent27/de_05_etl_vlille_spark
- Repository: https://github.com/cvincent27/de_05_etl_vlille_spark

## Overview

##### Project Type & Purpose
- This project is focused on extracting data from an external API (dataMEL), cleaning and transforming it using PySpark, and then loading it into a MySQL database. Airflow is used to orchestrate the ETL (Extract, Transform, Load) process, and PowerBI is mentioned for subsequent data reporting.
- It is essentially a data pipeline project that involves external integration, data transformation, and automation/orchestration.

##### Analyzing the File Structure
- Top-level items:
- Files like .gitattributes, .gitignore, and requirements.txt indicate standard project configuration.
- DE_ETL_VLille.ipynb suggests that there may be notebook demonstrations or testing workflows.
- The README provides the overview of the pipeline steps and technical stack.
- Directories and key files:
- The “config” directory (and spark_config.json) and settings files hint at centralized configuration management.
- The “src” folder contains core pipeline scripts: “extract.py”, “transform.py”, “load.py”, and “config.py”. These clearly map to the three main phases of the ETL process.

##### Key Components
a. External Data Source:
- DataMEL API—a REST API from which V'Lille stations data is extracted.
b. Data Extraction:
- “extract.py” component responsible for making API calls (using the “requests” library).
- This component should feed into the first stage of the ETL.
c. Data Transformation:
- The “transform.py” component utilizes PySpark for cleaning and formatting data.
- Mention aspects like date formatting, column additions (e.g., nb_places_total, % remplissage), and possibly the creation of intermediate DataFrame representations.
d. Data Loading:
- “load.py” loads the transformed data into the MySQL database.
e. Orchestration:
- Airflow acts as the scheduler that connects and triggers these tasks.
- That Airflow manages sequencing (as DAGs) across extraction, transformation, and loading.
f. Configuration:
- Configuration elements (from the config directory and spark_config.json) to show how parameters for the extraction and Spark processing are managed.
g. Reporting:
- Although not directly part of the ETL code, include PowerBI as a downstream component that consumes the data stored in MySQL for generating reports.

##### Relationships and Data Flow
1. From the external API (dataMEL) → Extraction component
2. From Extraction → Transformation (handled by PySpark)
3. From Transformation → Loading component → MySQL database
- Airflow as the overarching orchestrator that links these steps, initiating each phase of the ETL pipeline.

##### Architectural Patterns & Technologies
- ETL Pipeline: The classic separation into Extract, Transform, and Load stages.
- Configuration Management: Centralized settings stored in the config directory and JSON config files.
- Orchestration: Airflow’s DAGs ensuring scheduled and orderly execution.
- Data Processing: Usage of PySpark for scalable data transformation.
- External Integration: API-driven extraction using requests.
- Database: MySQL for data persistence.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% ETL Pipeline Subgraph
    subgraph "ETL Pipeline"
        A1["External API (dataMEL)"]:::external
        B1["Extract Module (extract.py)"]:::etl
        B2["Transform Module (transform.py)"]:::etl
        B3["Load Module (load.py)"]:::etl
        B4["MySQL Database"]:::database
    end

    %% Orchestration Subgraph
    subgraph "Orchestration"
        O1["Airflow Scheduler"]:::orchestrator
    end

    %% Configuration Subgraph
    subgraph "Configuration"
        C1["Settings (config/settings.yaml)"]:::config
        C2["Spark Config (spark_config.json)"]:::config
        C3["ETL Config (src/config.py)"]:::config
    end

    %% Reporting & Testing Nodes (placed separately)
    P1["PowerBI Reporting"]:::reporting
    T1["Testing Notebook (DE_ETL_VLille.ipynb)"]:::notebook

    %% Data Flow Connections
    A1 -->|"extracts"| B1
    B1 -->|"sendsData"| B2
    B2 -->|"processes"| B3
    B3 -->|"loads"| B4
    B4 -.->|"serves"| P1

    %% Airflow orchestration triggers for ETL stages
    O1 ---|"triggers"| B1
    O1 ---|"triggers"| B2
    O1 ---|"triggers"| B3

    %% Configuration management feeding ETL modules
    C1 ---|"configures"| B1
    C1 ---|"configures"| B2
    C1 ---|"configures"| B3
    C2 ---|"configures"| B2
    C3 ---|"configures"| B1

    %% Testing Notebook relation (for demonstration)
    T1 ---|"validates"| B1

    %% Click events for components
    click B1 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/src/extract.py"
    click B2 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/src/transform.py"
    click B3 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/src/load.py"
    click C1 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/config/settings.yaml"
    click C2 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/spark_config.json"
    click C3 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/src/config.py"
    click T1 "https://github.com/cvincent27/de_05_etl_vlille_spark/blob/main/DE_ETL_VLille.ipynb"

    %% Styles
    classDef external fill:#f9c74f,stroke:#f9844a,stroke-width:2px;
    classDef etl fill:#90be6d,stroke:#43aa8b,stroke-width:2px,stroke-dasharray: 5,5;
    classDef orchestrator fill:#577590,stroke:#277da1,stroke-width:2px,stroke-dasharray: 2,2;
    classDef database fill:#f94144,stroke:#f3722c,stroke-width:2px;
    classDef config fill:#f8961e,stroke:#f9844a,stroke-width:2px;
    classDef notebook fill:#90caf9,stroke:#42a5f5,stroke-width:2px;
    classDef reporting fill:#a0c4ff,stroke:#90e0ef,stroke-width:2px;
```
