How falowo95/sp500_etl_pipeline works
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.