An end-to-end AWS data engineering project that combines real-time Kafka ingestion with incremental lakehouse processing and dimensional warehouse modeling.
The pipeline ingests ecommerce orders through Amazon MSK Serverless, stores immutable JSON events in Amazon S3, transforms the data through Bronze, Silver, and Gold layers with AWS Glue, maintains customer history using SCD Type 2 in Delta Lake, and publishes an analytics-ready star schema to Amazon Redshift Serverless.
Author: Adarsh Damarla
Region: us-east-1
Status: End-to-end implementation completed and validated
- Real-time event ingestion with Python, Amazon MSK Serverless, and MSK Connect
- Incremental ETL using AWS Glue job bookmarks
- Medallion architecture using JSON, Parquet, and Delta Lake
- Customer dimension with SCD Type 2 history
- Product dimension with SCD Type 1 updates
- Deterministic surrogate keys for repeatable, idempotent processing
- Redshift staging and
MERGEpatterns for safe warehouse reruns - Private VPC networking and service-specific IAM roles
- Data-quality and referential-integrity validation across the star schema
The ingestion path is real time through MSK and MSK Connect. Lakehouse and warehouse transformations run as incremental micro-batches, a practical balance between latency, cost, and portfolio scope.
| Layer | Format | Processing | Purpose |
|---|---|---|---|
| Kafka | JSON messages | Continuous | Durable streaming transport |
| Bronze | JSON | Append-only | Raw, replayable source events |
| Silver | Parquet | Incremental Glue bookmark | Validated, typed, deduplicated orders |
| Gold | Delta Lake | Incremental Delta merge | Stateful dimensions and sales facts |
| Redshift stage | Parquet | Current snapshot export | Safe Redshift-compatible load files |
| Redshift | Relational tables | Staging + merge | Analytics-ready dimensional model |
The producer maintains stable customers and periodically changes a customer's city and state. Each version receives a deterministic key:
customer_sk = SHA256(customer_id | customer_updated_at)
When a customer changes, the previous Delta row is closed and a new current row is inserted:
customer_sk customer_id city effective_from effective_to is_current
----------- ----------- --------- -------------- ------------ ----------
SK1 CUST-145 Old City T1 T2 false
SK2 CUST-145 New City T2 null true
Facts calculate the same version-aware customer_sk, preserving the customer attributes that were valid for each order.
| Service | Resource |
|---|---|
| Amazon S3 | adarsh-ecommerce-streaming |
| Amazon EC2 | ecommerce-kafka-client (t3.micro) |
| Amazon MSK Serverless | ecommerce-msk-cluster |
| Kafka topic | ecommerce-orders |
| Amazon MSK Connect | ecommerce-s3-sink-connector |
| Security group | ecommerce-kafka-client-sg |
| Security group | ecommerce-msk-sg |
| Security group | ecommerce-msk-connect-sg |
| AWS Glue | bronze-to-silver-orders |
| AWS Glue | silver-to-gold-ecommerce |
| AWS Glue | gold-to-redshift-stage |
| IAM | ecommerce-glue-role |
| IAM | ecommerce-redshift-role |
| Redshift Serverless | ecommerce-workgroup |
| Redshift database/schema | dev.ecommerce |
The AWS account ID and private MSK bootstrap endpoint are supplied as deployment-time values.
.
├── producer/
│ ├── README.md # Actual EC2 client and producer setup
│ ├── producer.py # Ecommerce event generator and MSK producer
│ ├── producer.env.example # Runtime configuration template
│ └── requirements.txt # Producer Python dependencies
├── network/
│ ├── README.md # VPC and security-group documentation
│ └── security-groups.json # Exact project security-group rule manifest
├── architecture/
│ ├── README.md # Architecture visual index
│ ├── pipeline-architecture.svg # End-to-end AWS pipeline
│ ├── pipeline-architecture-dark.svg # Dark-theme pipeline
│ ├── star-schema.svg # Dimensional model
│ └── star-schema-dark.svg # Dark-theme dimensional model
├── glue/
│ ├── bronze-to-silver-orders.py # JSON validation and Parquet conversion
│ ├── silver-to-gold-ecommerce.py # Delta dimensions, SCD2, and facts
│ └── gold-to-redshift-stage.py # Delta snapshot to plain Parquet
├── redshift/
│ ├── 01-create-tables.sql # Star schema and staging DDL
│ ├── 02-copy-to-staging.sql # S3 Parquet loads
│ ├── 03-merge.sql # Idempotent final-table merges
│ └── 04-validation.sql # Row-count, SCD2, and integrity checks
├── iam/
│ ├── README.md # Role-to-policy mapping
│ ├── producer-*.json # EC2, MSK write, and Session Manager
│ ├── msk-*.json # Topic administration and connector role
│ ├── glue-*.json # Glue trust policy
│ ├── ecommerce-glue-policy.json # Glue data and logging permissions
│ ├── redshift-*.json # Redshift trust policy
│ └── ecommerce-redshift-policy.json # Redshift stage-read permissions
├── msk/
│ ├── README.md # Complete MSK creation guide
│ ├── serverless-cluster.json.example # MSK Serverless CLI request
│ ├── client.properties # Kafka IAM client authentication
│ ├── s3-sink-connector.properties # Readable S3 sink properties
│ ├── create-custom-plugin.json.example
│ └── create-connector.json.example # Complete connector CLI request
├── docs/
│ ├── 01-vpc-network.md
│ ├── 02-aws-resource-setup.md
│ ├── 03-schemas.md
│ ├── 04-troubleshooting.md
│ ├── 05-future-enhancements.md
│ └── 06-msk-serverless-and-connect.md
├── .gitignore
└── README.md
MSK Connect separates ingestion from transformation. Raw Kafka events are durably stored in S3 and can be replayed without depending on downstream Glue or Redshift availability.
The Bronze and Silver prefixes grow continuously. Stable DynamicFrame transformation contexts allow Glue bookmarks to process only newly discovered S3 objects after successful runs.
Plain Parquet cannot update a previous customer version in place. Delta Lake provides transactional merge operations required to close old SCD2 records while inserting new versions.
A Delta directory contains transaction logs and Parquet files that may no longer belong to the current table version. The export job reads the authoritative Delta snapshot and produces ordinary Parquet under redshift_stage/ before Redshift COPY.
Staging tables isolate each load and make final-table updates repeatable. MERGE updates existing keys and inserts new ones, preventing duplicates during reruns.
| Challenge | Resolution |
|---|---|
| Delta operations failed because Spark was not Delta-enabled | Added --datalake-formats=delta plus the Delta Spark extension and catalog configuration |
| Facts failed to resolve 22 customer versions through a timestamp-range join | Used the same deterministic customer_id + customer_updated_at key in facts and the customer dimension |
| Redshift COPY returned a Spectrum 403 error | Added s3:GetObject for redshift_stage/* to ecommerce-redshift-role |
Redshift rejected a target alias in MERGE |
Referenced the target table by its complete name and aliased only the source |
| Redshift rejected single-clause merges | Included matched-update and not-matched-insert clauses for all four tables |
| Direct COPY from Delta was unsafe | Added a dedicated Delta-to-Parquet staging export job |
The final validation covers:
- Row counts across all dimensions and the fact table
- Exactly one current record per customer
- Historical SCD2 versions ordered by
effective_from - No facts with unresolved customer surrogate keys
- Idempotent Redshift reruns through staging and merge
Queries are available in redshift/04-validation.sql.
- Cloud and network architecture
- VPC and security-group configuration
- Implementation and AWS resources
- Event and analytical schemas
- Engineering challenges and resolutions
- Production evolution
- MSK Serverless and MSK Connect
- Step-by-step MSK creation README
- EC2 Kafka client and producer setup
- Complete IAM roles and policies
The implemented project deliberately prioritizes core data-engineering concepts over additional orchestration services. A production evolution would add Step Functions and EventBridge, Delta Change Data Feed for changed-row-only exports, immutable load manifests, schema governance, monitoring, and alerting.
Adarsh Damarla
GitHub Profile