The Data Platform

Rebuilt how customer and order data flows through the company — from data arriving days late to sub-minute freshness, across 200+ merchant stores on five e-commerce platforms. Every downstream product runs on this.

days → sub-minute200+ stores98%+ uptime

Node.js · MySQL · Debezium · Google Pub/Sub · Datastream · MongoDB · BigQuery · ClickHouse · Redis · GKE

My Role

Architecture owner — designed ingestion, CDC, and multi-layer serving architecture
Migration lead — planned and executed store-by-store cutover across 200+ merchants
Core implementation — built CDC pipeline, landing store, warehouse consolidation
Production ownership — owned monitoring, alerting, incident response for 3+ years
Team of 4 — coordinated architecture decisions, code review, deployment strategy

Context

ConvertCart serves 200+ e-commerce merchants across five platforms (Shopify, BigCommerce, WooCommerce, Magento 1, Magento 2). Customer data, orders, click-tracking, and loyalty data all flow through separate systems with no unified schema. Downstream products (recommendations, segmentation, attribution) need to access this data, but latency and availability are unpredictable.

Problem

Data arrives days late. Commerce webhooks provide near-real-time events, but custom platform integrations require full data downloads every 6 hours. Click-tracking and loyalty data are even slower. Downstream products can't build real-time features. Reporting is expensive because queries have to reprocess data constantly. New products need to build custom integrations instead of tapping a shared stream.

BEFORE
  • 6+ hour batch delays
  • Custom integrations per product
  • Expensive BigQuery reporting
  • No unified data model
WHY IT MATTERED
  • Real-time recommendations impossible
  • Segmentation used stale data
  • Attribution couldn't link orders to blocks
  • Every new feature required engineering

Architecture

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. Downstream layer fans out to three specialized stores: MongoDB for point lookups (serving), ClickHouse for analytics, BigQuery for historical data.

COMMERCE · CLICK-TRACKING · LOYALTY · CRM
                ↓
MYSQL LANDING LAYER (raw, normalized per source)
                ↓
DEBEZIUM CDC → PUB/SUB
                ↓
╔═══════════════╤═══════════════╤═══════════════╗
║ MONGODB │ CLICKHOUSE │ BIGQUERY ║
║ (serving) │ (analytics) │ (historical) ║
║ point lookups │ aggregation │ audit trail ║
╚═══════════════╧═══════════════╧═══════════════╝

New sources plug into the landing layer. New consumers subscribe to Pub/Sub. No custom integrations required.

Key Decisions

  1. Landed raw instead of normalizing at ingest
    Costs: five schemas instead of one. Benefit: reversibility. Wrong normalization at ingest means multi-service migration. Wrong materialized view is a redefinition away from correction.
  2. CDC from landing store, not sources
    Costs: one more moving part. Benefit: single source of truth that can't diverge from actual data.
  3. Two replication paths (Datastream + Debezium)
    Started with managed Datastream, gradually earned our way to pure Debezium. Avoided speculative engineering while managing risk.
  4. Table-per-client multiplies operational surface
    Thousands of tables, migrations run across all. Accepted because shared-table contention is worse and less visible than operational overhead.

Execution

Migration Strategy

Store-by-store cutover because the data layer sits under every product. Bad cutover breaks all of them simultaneously. Slow but safe.

Backfill & Validation

Dual-write to both old and new landing layer for 2 weeks. Ran hourly checksums across all tables. Validated row counts, key distributions, timestamp ranges. Only switched when checksums matched 100%.

Rollback Strategy

Kept old pipeline running for 30 days after cutover. If anything broke, could revert to old data. Never had to use it, but reduced risk psychologically and operationally.

Monitoring & Alerting

Added metrics for data freshness (time from source to landing to serving), lag per store, checkpoint latency. Alerts on 5-minute latency deviation. Page on-call for >15 min delays.

The 50M-Row Snapshot Problem

When Debezium tried to snapshot the landing store: query hit 60% CPU, blocked production writes, unacceptable. Solution: leveraged existing Datastream snapshot, then switched to CDC incrementally per table. This became the dual-replication strategy above.

Impact

DAYS → SUB-MINUTE
From data arriving days late to sub-minute freshness for webhooks, up to 6 hours for custom integrations

Why It Mattered

  • Real-time recommendations became possible. Merchants could see recommendations update within seconds of customer actions.
  • Conversion attribution could link orders to specific blocks and email clicks — what proved to merchants that recommendations were actually working.
  • Segmentation worked with fresh data. Audiences updated in minutes, not hours.
  • Reporting moved off a cost curve that scaled with query volume onto one the team controlled.

Scale

  • 200+ stores · 5 platforms · 98%+ uptime · zero visibility loss during 3-month migration
  • New sources (Klaviyo, Zendesk) plugged in as streaming integrations, not custom pipelines
  • Complete migration off legacy architecture within 3 months, zero rollbacks

What I'd Change Today

What I'd Preserve

  • Raw landing layer (reversibility principle held)
  • CDC at the landing store, not sources (single source of truth)
  • Table-per-tenant for isolation (multi-tenant contention is worse than ops cost)

What I'd Change

  • Datastream → pure Debezium from day one. Datastream was our transition strategy; we could have earned off it faster. Debezium proved itself in production.
  • Consider separate CDC per domain. Commerce CDC vs. click-tracking CDC would reduce blast radius of unplanned events.
  • ​Evaluate Iceberg for analytics layer. Wasn't production-ready at the time; would simplify the architecture today.