The Data Platform
Owned the rebuild of customer and order data infrastructure across 200+ merchants on five commerce platforms — replacing a days-late legacy batch pipeline with a tiered-freshness system ranging from sub-5-minute webhooks to 6 hours for legacy integrations, migrated store-by-store over 3 months with a rollback path that was never used.
My Role
Owned architecture and technical direction — ingestion, CDC, and multi-layer serving design — plus CDC strategy, migration strategy, and production rollout across roughly five years of platform ownership. Directed the core implementation (CDC pipeline, landing store, warehouse consolidation) that the platform team, which I grew from 4 to 7 engineers, built and maintained.
System Architecture
The Challenge: Stale Data, Fragmented Sources
Data arrived days late. Commerce webhooks gave near-real-time events, but custom platform integrations required a full data download every 6 hours, and click-tracking and loyalty data were even slower. Downstream products couldn't build real-time features, and the pipeline was load-bearing enough that breaking it during migration risked halting critical business functions.
⚠Failure Points in the Legacy Pipeline
- >6+ hour batch delays: Commerce webhooks provided near-real-time events, but custom platform integrations required a full data download every 6 hours — click-tracking and loyalty data were even slower.
- >Custom integrations per product: Every new downstream product needed to build its own integration instead of tapping a shared stream.
- >Expensive BigQuery reporting: Reporting was expensive because queries had to reprocess data constantly instead of reading from a purpose-built serving layer.
- >No unified data model: Customer data, orders, click-tracking, and loyalty data all flowed through separate systems with no unified schema.
New products needed to build custom integrations instead of tapping a shared stream, and reporting was expensive because queries had to reprocess data constantly.
Architectural Solution: Landing Layer + CDC Fan-Out
Consolidates four source systems (commerce, click-tracking, loyalty, CRM) into a raw MySQL landing store, preserving original schema. Debezium streams changes via Google Pub/Sub. The downstream layer fans out to three specialized stores rather than forcing every workload onto one general-purpose warehouse.
Four source systems (commerce, click-tracking, loyalty, CRM) land in a raw MySQL store, preserving original per-source schema.
Debezium streams changes off the landing store through Google Pub/Sub, giving a replay boundary and per-tenant isolation.
Three specialized stores: MongoDB for point lookups, ClickHouse for analytics, BigQuery for historical/audit data.
New sources plug into the landing layer and new consumers subscribe to Pub/Sub — no custom integrations required. The platform team owns the landing layer and CDC, with a 5-minute latency alert and a 15-minute on-call page threshold.
$_ClickHouse Tuning Notes
clickhouse-merge-tree.conf-- Migrated customer segmentation from BigQuery Views to ClickHouse
-- Cost: 66% reduction | Compute: 64GB -> 16GB RAM (4x) via cityHash64 batched hashing
PARTITION BY (client_id, date) -- isolates per-store backfills, date-range scans
ORDER BY date -- serves segmentation filters + time-series reporting
-- Materialized views: hourly batch refresh, not real-time
-- (stable CPU, predictable cost -- the workload was wrong for the DB model, not a tuning problem)Decisions & Tradeoffs
Landed Raw Instead of Normalizing at Ingest
Costs: five schemas instead of one. Benefit: reversibility — wrong normalization at ingest means a multi-service migration; a wrong view is a redefinition away from correction.
CDC From the Landing Store, Not Sources
Costs: one more moving part. Benefit: CDC from a consolidated landing layer with replay and validation boundaries.
Two Replication Paths: Datastream + Debezium
Started with managed Datastream, gradually earned our way to pure Debezium. Avoided speculative engineering while managing risk.
Table-Per-Client Multiplies Operational Surface
Thousands of tables; migrations run across all of them. Accepted because shared-table contention is worse and less visible than operational overhead.
Rollout: Migration Without Breaking Production
Store-by-store cutover, because the data layer sits under every product — a bad cutover breaks all of them simultaneously. Three safeguards ran throughout the migration:
Dual-wrote to old and new landing layers for 2 weeks. Hourly validation compared row counts, key distributions, timestamp ranges, and checksums before each store's cutover.
Kept the old pipeline running for 30 days after cutover so a broken migration could be reverted. Never had to use it, but it reduced risk both operationally and psychologically.
Tracked freshness (source → landing → serving), lag per store, and checkpoint latency. Alerted on 5-minute latency deviation; paged on-call for delays over 15 minutes.
The production incident that changed the design
Debezium's snapshot query on 50M rows consumed 60% CPU and blocked production writes. We stopped rollout immediately, used Datastream to seed the baseline instead, then transitioned tables to Debezium incrementally.
Read the full incident and recovery decisions →Results & Operational Payoff
Real-time recommendations became possible within seconds of customer actions, conversion attribution could finally link orders to specific blocks, and segmentation worked with fresh data instead of stale exports — while reporting moved off a cost curve that scaled with query volume onto one the team controlled.
| METRIC | LEGACY | NOW | NET GAIN |
|---|---|---|---|
| Data Freshness (webhook-capable platforms) | Days-late (legacy cron) | <5 minutes | Real-time unlocked |
| Data Freshness (full-resync platforms) | Days-late (legacy cron) | ~6 hours | Tiered by capability |
| Segmentation Analytics Cost | $2,470/mo (BigQuery) | $850/mo (ClickHouse) | 66% Cost Cut, 4× Compute |
| Reporting Cost Model | Scaled with query volume | Team-controlled, fixed | Cost Curve Owned |
What I'd change today: I'd go to pure Debezium from day one — Datastream was our transition strategy, and we could have earned off it faster once Debezium proved itself in production. I'd also consider separate CDC per domain (commerce vs. click-tracking) to reduce blast radius, and evaluate Iceberg for the analytics layer, which wasn't production-ready at the time.