# joshua-omolewa/retailstore_etl_pipeline_project architecture

> Architecture diagram of the GitHub repository joshua-omolewa/retailstore_etl_pipeline_project, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-02-23). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/joshua-omolewa/retailstore_etl_pipeline_project
- Repository: https://github.com/joshua-omolewa/retailstore_etl_pipeline_project

## Overview

##### What this project is (type + purpose)
This repo is an **end-to-end batch ETL data pipeline** for a retail store, built primarily on **AWS + Snowflake + Spark**, orchestrated by **Airflow**. The goal is to:
- **Extract** raw OLTP tables from **Snowflake** daily into an **S3 staging (input) bucket**
- **Transform** into a **weekly aggregated fact table** using **Spark on EMR**

The “product” here is not a web app; it’s a **data platform workflow** with scheduling, orchestration, transformation, cataloging, and BI consumption.

##### What the file tree tells you about the system structure

- `Crerate schema and load data to SNOWFLAKE.sql`
→ Snowflake OLTP schema + initial loading (source system setup)

- `snowflake_loading_data_to_s3 -final.sql`
→ Snowflake **stored procedure + task (cron)** that performs **COPY INTO S3** (extraction layer)

- `lambda_function.py` + `send_email.py`
→ AWS **Lambda gatekeeper**: checks S3 for expected daily files; triggers Airflow; sends email via **SES** when missing

- `airflow_dag.py`
→ Airflow DAG that submits and monitors the **EMR Spark step** (orchestration layer)

- `spark_files/workflow_entry.py` and `spark_files/job.zip`
→ Spark job entrypoint and packaged dependencies; defines transformation to weekly metrics and writes **Parquet** to output S3

- `img/*`

##### Snowflake (OLTP / RAW schema)
- Tables: inventory, sales, store, product, calendar (from README)

##### S3 Input (Staging) Bucket
- Daily CSV extracts named like `inventory_<date>.csv`, `sales_<date>.csv`, etc.
- Format: **CSV**, “SINGLE=TRUE HEADER=TRUE” per the SQL

##### AWS Lambda
- “Scan S3 for yesterday’s files”
- “Trigger Airflow DAG”
- “If missing → send SES email”

##### Airflow on EC2 (Docker Compose)
- Airflow scheduler/webserver + DAGs
- Airflow triggers EMR step (`airflow_dag.py`)
##### Amazon EMR (Spark/Hadoop)
- Receives `spark-submit` step
- Reads from S3 input, transforms, writes to S3 output

##### S3 Output Bucket
- Output: **Parquet** weekly fact table (and possibly partitions)
##### AWS Glue Crawler + Glue Data Catalog
- Crawls output bucket; creates/updates table definitions
##### Amazon Athena
- Queries Glue Catalog tables

##### Superset on EC2 (Docker container)
- Connects to Athena for dashboards

##### Architectural patterns/design principles to highlight (because they’re core here)

- **Batch ETL, event/time-driven**
- Snowflake extraction is scheduled (cron task)
- AWS pipeline start is scheduled (CloudWatch) and conditional (data present)

- **Landing zone + curated zone**
- S3 input bucket = landing/staging (raw CSV)
- S3 output bucket = curated (Parquet; analytics-optimized)

- **Decoupled orchestration**
- Lambda is a lightweight gate + notifier
- Airflow is the workflow orchestrator
- EMR is the compute engine

- **Schema-on-read + catalog**
- Glue Catalog + Athena query Parquet in S3

- **Operational alerting**
- SES email path for missing daily batch

##### Lambda checks S3
- if present → trigger Airflow DAG
- else → SES email alert
##### Athena + Superset query

This makes the control logic unambiguous.

##### Step 4 — Add component-level details only where the repo has code
Don’t invent microservices that aren’t in the tree. Instead, zoom into:
- `lambda_function.py` (S3 check → trigger Airflow → notify)
- `airflow_dag.py` (DAG tasks, EMR step submission + monitoring)
- `spark_files/workflow_entry.py` (transformations, output paths, partitioning assumptions)

##### Clear labels
- **bucket names** as logical roles: “S3 Input (staging/raw)” and “S3 Output (curated/parquet)”
- Schedule labels near triggers: “12:00 AM MST” and “12:05 AM MST”

- “CSV extract” into S3 input
- “Parquet weekly aggregates” into S3 output

##### “Don’t overthink it” detail checklist (include as many components as possible)
To maximize accuracy and usefulness, annotate these near the relevant nodes:
- IAM roles/policies (at least mention: Lambda role, Airflow/EC2 role, EMR instance profile, S3 access)
- Logging/monitoring:
- CloudWatch logs for Lambda
- Airflow logs (on EC2)
- EMR step logs (often to S3 / CloudWatch)
- Data partitioning strategy (if the Spark job partitions by week/date, label it on S3 output)
- Failure path:
- “Missing file → SES email”
- “EMR step failed → Airflow marks DAG failed”

That set will fully capture what the README describes and what the code files implement.

## Diagram (Mermaid)

```mermaid
flowchart TD
  %% =========================
  %% Context / Actors (Top)
  %% =========================
  subgraph "System Context"
    DE["Data Engineer"]:::actor
    DA["Data Analyst"]:::actor
  end

  %% =========================
  %% Source System (Snowflake)
  %% =========================
  subgraph "Snowflake Account (Source / Extraction)"
    SFRAW[("Snowflake OLTP / RAW Schema\n(inventory,sales,store,product,calendar)")]:::db
    SFTASK["Snowflake Task (cron)\n12:00 AM MST"]:::trigger
    SFPROC["Stored Procedure\nCOPY_INTO_S3()"]:::compute
  end

  %% =========================
  %% AWS Data Platform
  %% =========================
  subgraph "AWS Account / VPC (Batch ETL Data Platform)"
    %% Landing / Staging
    S3IN[("S3 Input Bucket\nLanding/Staging (CSV)")]:::storage

    %% Triggering / Control Plane
    CWRULE["CloudWatch Rule\n12:05 AM MST"]:::trigger
    LAMBDA["AWS Lambda Gatekeeper\n- Scan S3 for yesterday files\n- Trigger Airflow DAG\n- Missing → notify"]:::compute
    SES["Amazon SES\nEmail Notifications"]:::external

    %% Orchestration / Compute
    subgraph "Orchestration & Compute"
      AIRFLOW["Airflow on EC2\n(Docker Compose)\nScheduler + Webserver + DAGs"]:::compute
      EMR["Amazon EMR\nSpark Cluster"]:::compute
      SPARKJOB["Spark ETL Job\nWeekly Aggregation"]:::compute
    end

    %% Curated Storage + Metadata + Query
    S3OUT[("S3 Output Bucket\nCurated (Parquet)\nWeekly Aggregated Fact Table")]:::storage
    GLUECRAWLER["AWS Glue Crawler"]:::compute
    GLUECAT[("Glue Data Catalog\nTables/Metadata")]:::db
    ATHENA["Amazon Athena\nSQL Query Engine"]:::compute

    %% BI / Consumption
    SUPERSET["Superset on EC2\n(Docker Container)"]:::compute

    %% Observability / IAM (annotations)
    CWLOGS["CloudWatch Logs\n(Lambda)"]:::note
    AFLOGS["Airflow Logs\n(EC2)"]:::note
    EMRLOGS["EMR Step Logs\n(S3/CloudWatch)"]:::note
    IAM["IAM Roles/Policies\n(Lambda,EC2/Airflow,EMR,S3 access)"]:::note
  end

  %% =========================
  %% Data Plane (solid, darker)
  %% =========================
  SFRAW -->|"COPY INTO (external stage)\nCSV extract (SINGLE,HEADER)"| S3IN
  S3IN -->|"Spark read\nDaily CSV files"| EMR
  EMR -->|"runs"| SPARKJOB
  SPARKJOB -->|"Spark write\nParquet weekly aggregates"| S3OUT
  GLUECRAWLER -->|"crawl output\nschema discovery"| GLUECAT
  ATHENA -->|"uses metadata"| GLUECAT
  ATHENA -->|"reads Parquet in S3"| S3OUT
  SUPERSET -->|"BI queries"| ATHENA

  %% =========================
  %% Control Plane (dashed, lighter)
  %% =========================
  SFTASK -.->|"triggers"| SFPROC
  SFPROC -.->|"executes COPY INTO"| SFRAW

  CWRULE -.->|"triggers"| LAMBDA
  LAMBDA -.->|"list/verify expected\ninventory_<date>.csv, etc."| S3IN
  LAMBDA -.->|"missing-file → send alert"| SES
  LAMBDA -.->|"REST/API trigger\nDAG run"| AIRFLOW

  AIRFLOW -.->|"submit step\n(AddStep/spark-submit)"| EMR
  AIRFLOW -.->|"monitor status\nsuccess/failure"| EMR
  EMR -.->|"step failed → propagate"| AIRFLOW

  %% =========================
  %% Ops / Notes (dashed associations)
  %% =========================
  LAMBDA -.->|"logs"| CWLOGS
  AIRFLOW -.->|"logs"| AFLOGS
  EMR -.->|"logs"| EMRLOGS
  LAMBDA -.->|"assume role"| IAM
  AIRFLOW -.->|"instance role"| IAM
  EMR -.->|"instance profile"| IAM
  S3IN -.->|"permissions"| IAM
  S3OUT -.->|"permissions"| IAM

  DE -.->|"monitors DAGs / failures"| AIRFLOW
  DE -.->|"receives alerts"| SES
  DA -.->|"build dashboards"| SUPERSET

  %% =========================
  %% Click Events (from component_mapping)
  %% =========================
  click SFRAW "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/Crerate schema and load data to SNOWFLAKE.sql"
  click SFPROC "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/snowflake_loading_data_to_s3 -final.sql"
  click SFTASK "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/snowflake_loading_data_to_s3 -final.sql"
  click LAMBDA "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/lambda_function.py"
  click SES "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/send_email.py"
  click AIRFLOW "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/airflow_dag.py"
  click SPARKJOB "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/spark_files/workflow_entry.py"
  click EMR "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/blob/main/spark_files/job.zip"
  click SUPERSET "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/tree/main/img/"
  click S3IN "https://github.com/joshua-omolewa/retailstore_etl_pipeline_project/tree/main/raw_data/"

  %% =========================
  %% Styles (color is required)
  %% =========================
  classDef actor fill:#FFF7D6,stroke:#A67C00,stroke-width:1.5px,color:#2B2B2B
  classDef db fill:#E3F2FD,stroke:#1565C0,stroke-width:1.5px,color:#0D2A4A
  classDef storage fill:#E8F5E9,stroke:#2E7D32,stroke-width:1.5px,color:#123B1A
  classDef compute fill:#F3E5F5,stroke:#6A1B9A,stroke-width:1.5px,color:#2D0F3F
  classDef trigger fill:#FFE0E0,stroke:#C62828,stroke-width:1.5px,color:#4A0B0B
  classDef external fill:#ECEFF1,stroke:#455A64,stroke-width:1.5px,color:#1E2A30
  classDef note fill:#FFFDE7,stroke:#9E9D24,stroke-width:1px,color:#3A3A10,stroke-dasharray: 4 3
```
