← Back to Home
DATA ENGINEERING • V5.0.1 CLOUDFORMATION IaC & EVENT-DRIVEN ELT

AWS E-Commerce Modern Data Pipeline (V5)

Role: Data Engineer (Personal CloudOps Project) Timeline: Oct 2026 – Nov 2026 (Tags v4.0.1 → 5.0.1) Stack: AWS (CloudFormation, S3, SQS + DLQ, CloudWatch, SNS, Redshift Serverless, IAM), Apache Airflow 3 (Astro CLI, Cosmos), dbt Core, Docker

1. Dataset, Scope & Engineering Objectives

Project Type Personal Cloud Engineering Project (AWS First Cloud Journey Standard)
Dataset Source Olist Brazilian E-Commerce Public Dataset (Kaggle, ~120 MB across 9 CSVs)
Data Scale & Range 99,441 orders, 112,650 order items (Sep 2016 – Oct 2018) → 4 BI CSVs (~56 MB)
Verification Status 5.0.0 live-verified E2E on AWS; 5.0.1 hardened (21/21 unit tests & CI pass)

Scheduled batch polling pipelines waste warehouse compute on empty runs and risk data loss when partial loads fail or malformed files enter a queue. The goal of this project was to evolve a manual batch ETL prototype (V2) into a reproducible, fault-tolerant, push-based CloudOps platform (V5.0.1) while keeping idle AWS compute cost at $0.

2. V5.0.1 System Architecture & Evolution (V2 → V4 → V5)

Amazon S3 (raw/*.csv) → SQS Main Queue (20s Long Poll, 900s Timeout) → Airflow 3 AssetWatcher → Task 1 (Two-Phase TEMP TABLE Swap + Guards) → Task 2 (Cosmos dbt Marts & 11 Tests) → Task 3 (Deterministic Archive + Audit COMPLETED + Safe ACK)
AWS E-Commerce Data Pipeline V5.0.1 System Architecture
End-to-End V5.0.1 Architecture: CloudFormation IaC, S3 Versioning, SQS + DLQ, 4 CloudWatch Alarms + SNS, Airflow 3 AssetWatcher, and Redshift Serverless. (Click image to open full resolution)
Dimension V2 (Manual Baseline) V4 (v4.0.1 — Data Core) V5 (5.0.0 → 5.0.1 — CloudOps)
Provisioning Manual AWS Console / CLI Manual AWS infra + 3-job GitHub Actions CI AWS CloudFormation IaC (15 core + 4 conditional resources), cfn-lint CI, Native Auto-Rollback, make cfn-delete
Orchestration Manual trigger (schedule=None) Airflow 3 Asset + AssetWatcher (20s SQS Long Poll, zero */5 cron) AssetWatcher hardened for Airflow 3 Mapping accessor + VisibilityTimeout=900s heartbeat
Ingestion & Atomicity Static COPY / TRUNCATE Per-table TEMP TABLE + DELETE/INSERT + Empty-Stage Guard Two-Phase Batch Staging + CSV Header Guard + ops.pipeline_ingestion_audit + Two-Proof Safe ACK
Failure & Alerting Silent failure / manual log check Transaction rollback + ACK retry safety SQS DLQ (maxReceiveCount=3) + 4 CloudWatch Alarms + SNS Email + DLQ Redrive
AWS data pipeline architecture evolution timeline from V2 to V5.0.1
Architectural evolution across V2, V3, V4 (v4.0.1), V5.0.0 (live E2E verified), and V5.0.1 (13 cross-review blind spots hardened).

3. Key Engineering Mechanisms & Bug Fixes

  • Declarative CloudFormation IaC (Dual Circular Dependency Fix): infra/cloudformation/template.yaml provisions S3 (AES256, Versioning, Lifecycle, HTTPS-only bucket policy), SQS + DLQ, split least-privilege IAM (RedshiftS3ReadOnlyRole, AirflowRuntimePolicy, DlqOperatorPolicy), 4 CloudWatch Alarms, and SNS. Resolved circular references between S3RawBucket ↔ SqsMainQueuePolicy and SqsMainQueue ↔ SqsDeadLetterQueue by constructing deterministic ARNs with !Sub instead of cross-referencing !GetAtt.
  • Live Airflow 3 Task SDK Bug Caught & Fixed: During live testing, extract_messages_from_asset_events initially skipped runs because Airflow 3's context["triggering_asset_events"] returns a TriggeringAssetEventsAccessor (collections.abc.Mapping, not a plain dict). Updated include/sqs_parser.py to validate against Mapping with unit-test regression coverage.
  • Two-Phase Batch Staging (DB Atomicity vs. Cross-System Orchestration): In Redshift, TRUNCATE auto-commits immediately and cannot be rolled back if COPY fails. Task 1 validates headers (validate_csv_header), stages all batch CSVs into session TEMP TABLEs, and enforces ensure_non_empty_stage (> 0) before touching target tables. Only when 100% of batch files pass staging does Phase B run DELETE + INSERT and write SWAPPED into ops.pipeline_ingestion_audit inside a single autocommit=False Redshift transaction (DB-level atomicity—not a distributed 2PC transaction across S3/SQS).
  • At-Least-Once Recovery, Deterministic Archive & Safe ACK: SQS enforces at-least-once delivery (not strict exactly-once transport), so crashes between S3 archiving, audit logging, and SQS DeleteMessage trigger controlled redelivery within VisibilityTimeout=900s. To handle duplicates idempotently, each S3 event uses a deterministic event_token (versionId / eTag / sequencer) mapping retries across different DAG Runs to the same audit_id and archive/<audit_id>/<filename> path. If a raw S3 key is missing on redelivery, Task 1 and Task 3 invoke verify_safe_to_ack_when_raw_missing()—permitting SQS ACK only when both ops.pipeline_ingestion_audit shows ARCHIVED/COMPLETED and the archived S3 key exists—while deferring multi-record SQS ACKs until the full batch finishes.

4. Dimensional Modeling, Quality Scope & FinOps Transparency

dbt Core data lineage graph from 9 Bronze sources to 7 Silver views and 3 Gold Star Schema tables
dbt Data Lineage: 9 raw_data Bronze sources → 7 public_staging Silver views → 3 public_marts Gold Star Schema tables.
  • Multi-Source Incremental Modeling & Customer Grain Trade-off: fct_orders (delete+insert) uses an affected_order_ids CTE across all 4 upstream tables (stg_orders, stg_order_items, stg_payments, stg_reviews) so late updates on older orders (>3 days) are captured. In Olist, every order assigns a new surrogate customer_id even when the same buyer returns (customer_unique_id). dim_customers deliberately keeps customer_id grain (99,441 rows) so downstream BI Star Schema joins with fct_orders remain strictly 1:1 without fan-out, while lifetime metrics (total_orders, lifetime_spend) are window-aggregated by customer_unique_id.
  • Scope of Automated Testing & CI (Separated by Execution Layer):
    • 21 Python Unit Tests + 3 DAG Pytests (Executed on every GitHub Actions CI run): Verify SQS event parsing, Airflow 3 Mapping accessors, header/empty-stage guards, multi-record deferred ACKs, recovery gates, and DAG import/retry metadata alongside Ruff, cfn-lint, and Astro Docker build.
    • Offline dbt parse (Executed in GitHub Actions CI): Validates SQL syntax, model compilation, and schema YAML graphs at $0 AWS cost—this checks structural graph validity rather than running data assertions against a live warehouse.
    • 11 dbt Schema Tests (Declared in schema.yml & executed live in 5.0.0): Validate warehouse data contracts (unique, not_null, relationships between fct_orders and dim_customers, and accepted_values on delivery_status) against live Redshift Serverless during the 5.0.0 E2E verification run.
  • Transparent FinOps Mechanics: Idle compute stays at $0 when local Astro stops polling (astro dev stop), because Redshift Serverless (4 RPUs) bills RPU-seconds only during active queries and AssetWatcher spawns 0 empty DAG runs. SQS Long Polling (~104k requests/month if kept running 24/7) fits inside the 1M/month AWS Free Tier, backed by a $5/month AWS Budget alert (persistent S3/Redshift storage billed separately per GB/month).

5. Live AWS Verification Evidence (5.0.0 Run + 5.0.1 Hardening)

All AWS screenshots below were captured live on us-east-1 during the 5.0.0 release verification (Happy Path 17/17 + Empty-CSV Poison Isolation → DLQ → CloudWatch Alarms → SNS Gmail → DLQ Redrive recovery). Following 5.0.0, release 5.0.1 patched 13 cross-review edge cases verified by 21/21 unit tests, cfn-lint, and aws cloudformation validate-template (pending next live stack re-run).

Airflow 3 AssetWatcher 17 of 17 green task run on AWS V5.0.0
Happy Path (17/17 Tasks): Triggered automatically by AssetWatcher on S3 file arrival with zero scheduled empty runs.
Redshift ops.pipeline_ingestion_audit state transition ledger
Audit Ledger: ops.pipeline_ingestion_audit logging FAILED (0-row guard) and recovery SWAPPED → ARCHIVED → COMPLETED.
AWS CloudWatch 4 alarms entering ALARM state on poison CSV isolation
Fault Isolation & CloudWatch: Poison 0-row CSV moved to SQS DLQ after 3 retries, triggering CloudWatch ALARM state.
Amazon SNS real-time email alert delivered to Gmail from DLQ alarm
SNS Alert Delivery: Real-time incident notification delivered to Gmail via PipelineAlertTopicPolicy before DLQ Redrive recovery.