Carton Traceability Platform
End-to-end supply chain traceability tracking 1M+ cartons/day across warehouse, transport, and RFID systems.
Overview
Designed and built a carton traceability platform as the sole engineer over 6 months. The system ingests data from Warehouse Management Systems (WMS), Transport Management Systems (TMS), and RFID scanners to provide end-to-end visibility into carton movement, from warehouse shelf to delivery.
Built on Cloud Composer (Airflow) for orchestration and dbt for transformation, with BigQuery as the analytical warehouse.
Architecture
flowchart LR
WMS --> CC
TMS --> CC
RFID["RFID Scanners"] --> CC
CC["Cloud Composer\n(Airflow)"] --> dbt
dbt --> BQ[("BigQuery")]
BQ --> Out["Dashboards / APIs"]Tech Stack
| Layer | Technology |
|---|---|
| Sources | WMS, TMS, RFID scanners |
| Ingestion | Cloud Composer (Airflow) DAGs |
| Transformation | dbt (SQL models) |
| Storage | BigQuery (partitioned, clustered) |
| Monitoring | Cloud Monitoring + custom alerts |
Key Metrics
| Metric | Value |
|---|---|
| Scale | 1M+ cartons/day |
| Data sources | 3 (WMS, TMS, RFID) |
| Build duration | 6 months |
| Team size | 1 (sole engineer) |
Challenges & Decisions
Reconciling three source systems
- Problem: WMS, TMS, and RFID each had different schemas, event formats, and delivery guarantees.
- Approach: Standardized ingestion layer with source-specific adapters feeding a unified event schema in dbt staging models.
- Outcome: Single source of truth for carton lifecycle, regardless of origin system.
Scale at 1M+ cartons/day
- Problem: Naive BigQuery queries over unpartitioned data were slow and expensive.
- Approach: Partition by event date, cluster by carton_id and source_system. dbt incremental models for efficient updates.
- Outcome: Query performance stayed under 5 seconds for most analytical queries.
Sole engineer, zero incidents
- Problem: No backup engineer meant the system had to be self-healing.
- Approach: Extensive Airflow alerting, dbt tests on every run, idempotent DAGs that safely retry.
- Outcome: Zero production incidents over the operational period.
What I Learned
- Building as a sole engineer forces you to design for operability from day one - if you can’t debug it at 2 AM, don’t ship it.
- dbt incremental models are essential at this scale - full refreshes would blow through compute budgets.
- Cross-system traceability is a data modelling problem, not a pipeline problem. Get the schema right first.
Built at Wayfair India · 2024