Mastering Data Sync: From Full Loads to Real‑Time CDC in E‑Commerce

Introduction

A junior developer named "Xiao Aba" is tasked with regularly syncing order data from the operational database to an analytics warehouse. The story illustrates common challenges and step‑by‑step solutions for reliable data synchronization in a fast‑growing e‑commerce environment.

1. Full‑Table Synchronization

The simplest approach copies the entire orders table each run, regardless of changes. Example code:

# Fetch all orders
orders = db.query("SELECT * FROM orders")
# Clear existing data
warehouse.execute("DELETE FROM orders")
# Insert new rows
warehouse.insert(order)

While this works for a small dataset (e.g., 10,000 rows in 3 hours), it becomes impractical as data volume grows.

2. Basic Alert & Error Handling

To avoid silent failures, the process now logs errors, sends email alerts, and rolls back the warehouse to the pre‑sync state when a failure occurs, allowing the team to detect and fix issues before the boss notices.

3. Incremental Synchronization

Instead of copying everything, the job now syncs only rows created after the last successful run (e.g., orders with created_time after midnight). This reduces data volume and execution time dramatically.

However, using only creation time misses updates to existing orders (e.g., refunds). The solution is to use an updated_time column that changes on any modification, ensuring both new and updated rows are captured.

4. Batch Processing

When a promotional event spikes order volume, processing all rows at once leads to OOM errors. The job is refactored to process data in batches (e.g., 100 rows per batch) using pagination:

SELECT * FROM orders
WHERE updated_time >= '2025-09-08' AND updated_time < '2025-09-09'
ORDER BY updated_time
LIMIT 2 OFFSET 0;  -- page 1, 2 rows

Each batch is committed separately, limiting memory usage and allowing partial retries on failure.

5. Cursor Mechanism

Offset‑based pagination can lose rows when new data is inserted during a run. The article demonstrates a cursor‑based approach: after each batch, store the last processed primary‑key (e.g., id) and query the next batch with WHERE id > last_id. This eliminates gaps and avoids deep‑offset performance penalties.

6. Performance Optimizations

Further speed gains are achieved by:

Switching from row‑by‑row inserts to bulk insert statements.

Running multiple batch workers in parallel threads to utilize concurrency.

These changes reduce sync time from hours to minutes, even under million‑row daily loads.

7. Real‑Time Synchronization with CDC & Message Queues

For sub‑hourly freshness, the batch job is replaced by a Change Data Capture (CDC) system that streams every change to a message queue. The consumer reads messages and writes them to the warehouse, achieving sub‑100 ms latency from order placement to dashboard visibility.

Key components:

CDC (e.g., Debezium, Canal) monitors the source database.

A durable message queue (e.g., Kafka) buffers changes.

Consumers apply changes to the analytics store.

Additional safeguards include monitoring queue lag and alerting on back‑pressure.

8. Common Pitfalls & Remedies

During a massive sales event, the system encountered:

Duplicate message processing – solved with idempotent handling (unique message IDs).

Out‑of‑order processing – solved by partitioning messages per order to preserve order.

Queue overload – solved by scaling the consumer cluster and enabling auto‑scaling.

The article also warns against ignoring existing open‑source data‑sync tools (e.g., DataX, Canal, Debezium) and stresses the importance of regular data reconciliation checks.