How joshua-omolewa/retailstore_etl_pipeline_project works
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.