# falowo95/sp500_etl_pipeline architecture

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

- Interactive diagram: https://gitdiagram.com/falowo95/sp500_etl_pipeline
- Repository: https://github.com/falowo95/sp500_etl_pipeline

## Overview

##### Project Overview & Main Components
- The project is built to extract S&P 500 stock data from the Tiingo API, transform it using PySpark (and optionally DBT for model definitions), and load the processed data into a cloud environment (Google Cloud Storage and BigQuery) for visualization via Google Data Studio.
- Key technologies include Airflow (for orchestrating the ETL pipelines through DAGs), Docker (for containerizing Airflow), Terraform (for provisioning and managing cloud infrastructure), and PySpark (for data transformation).

##### Identifying Primary Components
a. External Data Source:
- Tiingo API: The external source where raw S&P 500 data is fetched.
b. Cloud Infrastructure (Provisioned via Terraform):
- Google Cloud Storage (GCS): Acts as the data lake for raw and intermediate datasets.
- BigQuery: Serves as the storage for transformed data and the eventual source for the dashboard.
- VM Instance: Hosts the overall processing environment (and runs Docker/ Airflow).
c. ETL & Orchestration:
- Airflow: The core orchestrator running within Docker containers (as indicated by the Dockerfile, docker-compose.yaml, and supporting files). Within Airflow:
- DAGs Directory: Contains individual DAG files (e.g., sp_500_data_processing_dag.py, dbt_run_dag.py) that specify different ETL tasks.
- Helper Functions: Files like helper_functions.py and stock_data_transform.py encapsulate transformation logic.
- DBT Project: Under dags/dbt/dbt_sp500, a DBT project is embedded to further define transformation models.
d. Supporting Artifacts:
- Credentials: The creds directory stores AWS credentials (likely used by xcom or for S3 integration, as suggested by includes/s3_xcom_backend.py).
- Logs: Directory capturing runtime logs from scheduler and dag_processor_manager.
- Infrastructure Code: Terraform directory defines all cloud infrastructure specifications.
- Configuration Files: Files such as airflow.cfg, environment variables (.env in instructions), and global_config.py provide runtime settings.

##### Relationships and Interactions
- Data Extraction (Airflow tasks) downloads data and stores raw data in GCS.
- Transformation tasks (using PySpark within Airflow) take data from GCS, process it, and then load the transformed output into BigQuery.
- Optionally, integration with DBT for model management might be represented as a builder module within the transformation phase.
- Infrastructure components (GCS, BigQuery, and the VM instance) are provisioned, managed, and updated with Terraform.
- Docker containers encapsulate the Airflow environment, ensuring consistent runtime across development/deployment.
- Credentials and configuration files ensure secure and correct connection between different services (e.g., GCP credentials, AWS credentials for certain components, etc.).

##### Architectural Patterns & Design Principles
- ETL Pipeline Pattern: Visually, separate the extraction, transformation, and load phases.
- Containerization: Highlight the Docker containers that encapsulate and isolate the Airflow runtime environment.
- Infrastructure as Code (IaC): Represent Terraform as the tool managing infrastructure components on GCP.
- Modular Design: Show how separate directories for DAGs, DBT, helper functions, and configuration contribute to a modular and maintainable design.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Layer
    subgraph "External Sources"
        TI["Tiingo API"]:::external
    end

    %% ETL Pipeline Layer
    subgraph "ETL Pipeline"
        subgraph "Airflow & Containerization"
            AF["Airflow (Docker Container)"]:::orchestration
            DAG1["DAG: sp_500_data_processing"]:::orchestration
            DAG2["DAG: dbt_run_dag"]:::orchestration
            DAG3["DAG: aws_connection_test_dag"]:::orchestration
            AF --> DAG1
            AF --> DAG2
            AF --> DAG3
        end

        subgraph "Transformation Tasks"
            PS["PySpark Transformation"]:::orchestration
            HF["Helper Functions"]:::orchestration
            DBT["DBT Transformation"]:::orchestration
            DAG1 -->|"triggers"| PS
            DAG1 -->|"calls"| HF
            DAG2 -->|"initiates"| DBT
        end
    end

    %% Cloud Infrastructure Layer
    subgraph "Cloud Infrastructure"
        GCS["Cloud Storage (GCS)"]:::cloud
        BQ["Data Warehouse (BigQuery)"]:::cloud
        VM["VM Instance"]:::infrastructure
        DAG1 -->|"stores_raw_data"| GCS
        PS -->|"loads_data"| BQ
        DBT -->|"updates_data"| BQ
    end

    %% Infrastructure Management Layer
    subgraph "Infrastructure Management"
        T["Terraform (IaC)"]:::infrastructure
        T -->|"provisions"| VM
        subgraph "Containerization Setup"
            DF["Dockerfile"]:::infrastructure
            DC["docker-compose.yaml"]:::infrastructure
        end
    end

    %% Configuration & Supporting Components
    subgraph "Configuration & Supporting"
        GC["GCP Config"]:::infrastructure
        GS["GCP Service"]:::infrastructure
        CRED["AWS Credentials"]:::support
        S3["S3 XCom Backend"]:::support
        LOGS["Logs & Monitoring"]:::support
        GC ---|"includes"| GS
    end

    %% Data Flow Connections
    TI -->|"data_extraction"| DAG1
    GCS -->|"transformation_source"| PS

    %% Click Events
    click DAG1 "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/sp_500_data_processing_dag.py"
    click DAG2 "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/dbt_run/dbt_run_dag.py"
    click DAG3 "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/aws_connection_test_dag.py"
    click PS "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/stock_data_transform.py"
    click HF "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/helper_functions.py"
    click DBT "https://github.com/falowo95/sp500_etl_pipeline/tree/main/dags/dbt_sp500"
    click GC "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/config/gcp_config.py"
    click GS "https://github.com/falowo95/sp500_etl_pipeline/blob/main/dags/config/gcp_service.py"
    click T "https://github.com/falowo95/sp500_etl_pipeline/tree/main/terraform"
    click DF "https://github.com/falowo95/sp500_etl_pipeline/tree/main/Dockerfile"
    click DC "https://github.com/falowo95/sp500_etl_pipeline/blob/main/docker-compose.yaml"
    click CRED "https://github.com/falowo95/sp500_etl_pipeline/tree/main/creds"
    click S3 "https://github.com/falowo95/sp500_etl_pipeline/blob/main/includes/s3_xcom_backend.py"
    click LOGS "https://github.com/falowo95/sp500_etl_pipeline/tree/main/logs"

    %% Styles
    classDef external fill:#ffcccc,stroke:#cc0000,stroke-width:2px;
    classDef orchestration fill:#ccffcc,stroke:#006600,stroke-width:2px;
    classDef cloud fill:#cce5ff,stroke:#004085,stroke-width:2px;
    classDef infrastructure fill:#e6ccff,stroke:#663399,stroke-width:2px;
    classDef support fill:#ffffcc,stroke:#999900,stroke-width:2px;
```
