MegaMart Indonesia — Senior Data Engineer Assessment
A take-home assessment spanning platform consolidation (PySpark/Python), analytical pipelines (SQL + PySpark), and engineering leadership — all set in a realistic Indonesian omnichannel retail scenario.
Q1: Platform Consolidation
Unifying 5 Snowflake silos into a unified Bronze layer
- 13 critical problems identified (vs. 8 minimum)
- Schema harmonization framework with 5 source-specific transformers
- 450-line incremental CDC pipeline with watermark, DLQ, retry, metrics
- Technical design document written for mid-level engineers
Q2: Customer 360 & Inventory Pipelines
Silver/Gold layer with exact business formula compliance
- Customer 360 pipeline: 15 exact spec formulas, handles churned customers
- SCD Type 2 with hash-based change detection, DDL, point-in-time SQL
- Single-pass SQL inventory health with 4 alert flags
- Bottleneck analysis: 4h → 25min optimization roadmap
Q3: Engineering Standards & Team Enablement
Professionalizing a team of 8 with 40% failure rate
- Engineering standards guide: code, pipeline, quality, operational
- Testing strategy with pytest examples, DQ gate design
- Alert reduction from 500/day → <20 with severity routing
- 90-day enablement plan with week-by-week tactics
Technical Stack
Languages
Python, PySpark, SQL
Data Processing
Apache Spark, Snowflake, Delta Lake
Cloud
AWS (S3, DynamoDB, CloudWatch, Secrets Manager)
Formats
Parquet, Avro, JSON
Testing
pytest, PySpark test utils
Methodology
Medallion Architecture, SCD Type 2, CDC
About This Assessment
This is the Level 3 (Senior) Data Engineer take-home assessment from Insignia's AWS Business Unit. The scenario is set at MegaMart Indonesia — the country's largest omnichannel retailer with 850+ stores, 12M loyalty members, and $2.8B annual GMV. Solutions were developed using realistic data (10 CSV files, 150MB) with intentional data quality issues baked in to test production readiness.