An AWS data engineering project for an online learning marketplace, covering batch and streaming pipelines, layered data processing, quality checks, and performance benchmarks.
For a more detailed walkthrough of the architecture and design decisions in Chinese, see the Chinese design documentation.
The idea came from an online skill-sharing platform I worked on during my internship. It was similar to Udemy, but the scope was broader: instructors could publish any subject or skill they were qualified and interested in teaching.
After the internship, I remained interested in the data architecture behind this type of platform, so I designed and built this project independently. All code and data in this repository were created for this project; no company code, internal data, or confidential information is included.
| Metric | Measured result |
|---|---|
| Batch baseline | 5,000,000 rows |
| Batch incremental run | 100,000 rows |
| Best incremental extraction rate | 41,638 rows/s |
| Streaming benchmark | 1,000,000 events |
| Producer throughput | 14,105 events/s |
| Consumer throughput | 13,010 events/s |
| Streaming p95 latency | 14.2 s |
| Duplicate keys | 0 |
| Rejected rows | 0 |
| Bronze / Silver row-count difference | 0 |
This view summarizes the measured AWS benchmark: batch and streaming throughput, end-to-end latency, and stage durations for the baseline and incremental runs.
Orders, payments, and user events come from different systems. Building reports directly from those sources can lead to inconsistent row counts, conflicting revenue definitions, and poor traceability when something goes wrong.
This project brings batch transactions and streaming behavior events into one data platform, where they are cleaned, validated, and organized before being used for analytics.
Batch transactions and streaming events enter through PostgreSQL and MSK, then move through S3, Glue, and Athena for layered processing and validation.
The project does not skip the source system by generating CSV files directly into S3. It first simulates an application data flow:
generate_reference_data.pycreates users, instructors, and courses in PostgreSQL.generate_transactions.pywrites related orders, order items, and payment attempts to PostgreSQL.generate_events.pycreates browsing, cart, checkout, and payment events and sends them to Kafka.- Downstream pipelines read only from PostgreSQL and Kafka, not from in-memory generator objects.
users
└── orders
├── order_items ── courses ── instructors
└── payments
- One user can place many orders.
- One order can contain many order items.
- Each order item references one course.
- One instructor can teach many courses.
- One order can have multiple payment attempts.
Refunds and enrollments are part of the broader product design, but they are not included in the implemented PostgreSQL MVP.
Python App Simulator
→ users / courses / orders / order_items / payments
→ RDS PostgreSQL
→ ECS Incremental Extractor
→ S3 Bronze Parquet
→ AWS Glue Silver
→ AWS Glue Gold
→ Athena / Iceberg Validation
Python Event Generator
→ ECS Producer
→ Amazon MSK Serverless
→ ECS Consumer
→ S3 Bronze Parquet
→ AWS Glue Silver
→ Athena Validation
The Python generators write data through normal SQL transactions. The ECS extractor then reads new or updated rows based on updated_at and a durable S3 watermark.
- The watermark is stored in S3 rather than on ephemeral ECS storage.
- PostgreSQL rows are fetched in chunks through a server-side cursor.
- Each chunk is serialized, uploaded, and released independently.
- A table watermark advances only after all of its objects upload successfully.
- Silver resolves overlapping extracts by primary key and update time.
The producer sends events to Amazon MSK Serverless. The consumer reads Kafka partitions, writes bounded Bronze Parquet files to S3, and Glue transforms them into Silver data.
- Kafka uses six partitions.
- Each event keeps its topic, partition, offset, and Kafka timestamp.
- Parse failures and invalid records remain visible.
- Event IDs are used for duplicate checks.
- Producer and consumer are bounded ECS tasks rather than always-on services.
| Layer | Responsibility |
|---|---|
| Bronze | Preserve source data and ingestion metadata. |
| Silver | Standardize, validate, deduplicate, and quarantine invalid rows. |
| Gold | Apply order and payment rules and produce analytics-ready data. |
The layers make failures easier to trace: source issues in Bronze, transformation issues in Silver, and business-rule issues in Gold.
Each Glue run records input, valid, duplicate, output, and rejected row counts together with the Job Run ID and processing time. Invalid rows are written to quarantine with their failure reasons. Athena checks keys, nulls, layer reconciliation, and Gold amount consistency.
ECS Extractor
→ Three Silver Glue Jobs in Parallel
→ Gold Glue Job
→ Athena MERGE INTO Iceberg
→ Athena Validation
The three Silver jobs run in parallel. Gold starts only after all three complete successfully.
- Generated and processed 5,000,000 transaction rows.
- Generator: 361.3 seconds at approximately 13,840 rows/s.
- Bronze extractor: 153.4 seconds at approximately 32,592 rows/s.
- 100,000-row incremental extraction: 2.4 seconds at approximately 41,638 rows/s.
- The 5M dataset was written as 100 bounded Parquet objects.
- Three Silver jobs and one Gold job completed successfully.
- Gold validation returned zero duplicate keys, null keys, amount mismatches, and multiple-success-payment orders.
- Producer and consumer each completed 1,000,000 events.
- Producer throughput: 14,104.97 events/s.
- Consumer throughput: 13,010.44 events/s.
- End-to-end latency: p50 9.45 s, p95 14.15 s, and p99 15.55 s.
- Bronze and Silver each contained 1,000,000 rows.
- Rejected rows, duplicate event IDs, and null event IDs were all zero.
The generator commits every 10,000 orders instead of holding the full 5M dataset in a Python list. The extractor uses a server-side cursor and reads 50,000 rows at a time. Each chunk is converted to Parquet, uploaded, and released before the next chunk is processed.
Memory usage therefore depends mainly on chunk size rather than total dataset size.
- ECS workloads run as bounded tasks and stop after completion.
- Glue uses two G.1X workers.
- Temporary MSK, IAM, and network resources were deleted after the streaming benchmark.
- Partition projection avoids recurring crawler runs.
- Baseline and incremental Glue runs cost an estimated $0.304 in total.
- The full incremental benchmark was estimated below $0.35, excluding the existing RDS instance.
| Path | Purpose |
|---|---|
extract_rds_to_s3.py |
Incremental RDS-to-S3 Bronze extractor |
stream_kafka_to_s3.py |
Kafka-to-S3 Bronze consumer |
generate_reference_data.py |
Creates users, instructors, and courses in PostgreSQL |
generate_transactions.py |
Creates orders, order items, and payments in PostgreSQL |
generate_events.py |
Generates streaming benchmark events |
glue/ |
Bronze, Silver, and Gold ETL jobs |
airflow/ |
Batch DAG and Iceberg MERGE SQL |
sql/ |
Athena DDL, validation, and reconciliation queries |
docs/aws/ |
ECS, Glue, MSK, and IAM configurations |
docs/evidence/ |
Benchmark metrics, audits, and Athena results |
The first view shows the synthetic 5M load-test window. The second uses a smaller multi-day sample to verify daily aggregation, payment outcomes, and event counts.
Synthetic load-test window 1.53M orders, 1.30M successful orders, $84.0M GMV, payment outcomes, and event distribution. |
Functional sample window Daily GMV, payment success, and the distribution of six streaming event types. |
Business values shown here are synthetic and are used to validate the pipeline and analytics workflow. They do not represent real revenue or customer growth.
The business pages use 90 days of synthetic data with daily variation, course concentration, and conversion differences. The engineering page uses measured benchmark results.
Business overview GMV, successful payments, conversion, average order value, daily GMV, and category performance. |
Payment funnel Funnel stages, payment success, failed payments, and retry recovery. |
Course and instructor performance Active courses, instructor GMV, course GMV, and category conversion rates. |
Engineering quality and cost Throughput, streaming latency, batch duration, data quality, and Glue cost. |
| Document | Topic |
|---|---|
| 01 Business Requirements | Goals, business rules, and metrics |
| 02 Source Systems | PostgreSQL and user-event sources |
| 03 Data Flow Architecture | End-to-end flow through each layer |
| 04 Domain Model | Entities, relationships, and business rules |
| 05 Bronze Layer | Raw data and ingestion metadata |
| 06 Silver Layer | Validation, deduplication, and quarantine |
| 07 Gold Layer | Facts, dimensions, metrics, and Iceberg |
| 08 Pipeline Design | Scheduling, stages, and monitoring |
| 09 Data Quality | Quality rules and invalid-record handling |
| 10 Dashboard Design | Reporting scope and metric usage |
This repository contains no passwords or AWS access keys. Benchmark workloads used short-lived ECS tasks and Glue jobs, and no benchmark ECS, Glue, or MSK compute remained running after validation.