E-commerce Data Warehouse - ELT Pipeline on AWS

The problem

Pulling orders from an API into a database is the easy part. What makes a pipeline trustworthy is everything around it: what happens when a run dies halfway, when the API returns a payload the schema does not expect, when someone re-runs yesterday's job by mistake, and how anyone proves afterwards that the numbers in the dashboard match the source. This project was built to answer those questions against real infrastructure rather than a local toy setup.

Approach

• Orders are extracted from the Shopify GraphQL API with cursor pagination and throttle-aware retries, staged as raw JSONL in S3, and loaded into PostgreSQL on AWS RDS across three layers: raw JSONB for replay and audit, typed staging, and a curated star schema for BI.
• Loads are idempotent: upserts keyed by the Shopify GID with load_id lineage, so a retry after a partial failure changes nothing. Watermarks only advance after both the transforms and the quality gates pass.
• Validation is a gate, not a crash: every payload is checked against a pydantic model, rejects land in raw.rejects with a reason, and the run still completes. Every run records extracted, loaded and rejected counts in meta.load_audit.
• Quality gates run in SQL on every execution and fail the run: fact-vs-staging reconciliation, per-order revenue reconciliation so errors cannot cancel out across orders, orphan foreign keys, and duplicate natural keys.
• Transforms are SQL-first. The cumulative revenue curve and per-customer order sequence are window functions in the curated layer, not calculations hidden inside the BI tool.
• A FastAPI receiver ingests Shopify webhooks in real time alongside the batch run, verifying HMAC-SHA256 signatures in constant time and deduplicating by webhook id, then normalizing the REST payload into the same canonical record the GraphQL extractor produces so no downstream SQL changes.
• A second source system, a legacy SQL Server ERP reached over ODBC, supplies per-SKU cost and inventory, enriching the fact table with line cost and margin through the same watermark mechanism.
• The batch run is scheduled as an AWS Lambda triggered by EventBridge, declared in Terraform with a least-privilege IAM role and secrets read from SSM Parameter Store. GitHub Actions runs the test suite against a Postgres service container on every push.
• AWS provisioning is cost-guarded and automated with boto3: a zero-spend budget alert is created first, then S3 with a 30-day lifecycle, a least-privilege IAM user, and a free-tier RDS instance locked to a single IP.

What it proves

• 92 pytest tests covering extraction, loading, transforms, quality gates, webhook security and the ERP sync.
• Runs against real AWS infrastructure (RDS, S3, IAM, budgets), not a local simulation.
• Re-running or retrying a load is provably a no-op, and every run is auditable through meta.load_audit.
• Honest scope note kept in the README: the store is real but new, so order history is seeded through the same Admin API the pipeline consumes. Every other link in the chain runs against real infrastructure.