2026 · Citi coding workshop — solution engineering
Teams Data Platform
A medallion pipeline that answers seven business questions — and refuses to answer where the data can't support one.
Built for Citi's coding workshop, against a provided brief and repository scaffold. The pipeline, serving layer, API and interface are my work — 43 commits.
- Source rows
- 703,448 · 7 feeds
- Tests
- 390 · 5 gates
- Scale
- 10× rows → 1.9× time
the problem
The brief's scenario company, ACME Inc., had team data spread across seven feeds and six systems, and nobody could answer basic questions about how the organisation was actually structured: who is on each team, where teams sit, which leaders are not co-located, which teams run above a 20% non-direct-staff ratio. The naive answers are easy to produce and quietly wrong — 28,595 email addresses map to more than one person, so a plain join inflates 244k memberships to roughly 650k rows while every downstream figure still looks plausible.
the approach
A Bronze/Silver/Gold pipeline in PySpark. Bronze is a faithful string-typed copy that records the source of every row; Silver types, deduplicates and identity-resolves, asserting that Silver plus quarantine equals Bronze per entity and halting the run when it doesn't; Gold produces 15 business marts mirrored into Aurora PostgreSQL with indexes for serving. An identity bridge collapses each ambiguous email to one party before any fact join, and every row carries its resolution status so a deterministic pick can never be mistaken for a fact. A Lambda-backed read-only API serves the Gold layer to a React interface, and a Jupyter notebook runs the same queries against live cloud data.
the result
The finding that matters is a negative one. Co-location looked like '20,240 teams are not co-located' until two problems surfaced: one location code maps to two different cities, and 95% of teams contain an ambiguous identity. Only 200 of 25,000 teams can honestly be decided, so the platform publishes both readings — the observed number for continuity, the confirmed one for decisions — instead of picking whichever looked better. Verified by 390 tests with five coverage gates, reconciliation invariants that raise rather than log, and an independent recomputation of every headline figure in pandas by a different route.
stack
- PySpark
- Aurora PostgreSQL
- S3 Parquet
- AWS EKS
- AWS Lambda
- Terraform
- React
- Jupyter