Data silos are the enemy of automation. Building robust synchronization pipelines ensures that your information is consistent, accessible, and actionable across your entire technical ecosystem.
1CDC vs. ETL
Traditional database synchronization uses ETL (Extract, Transform, Load): a scheduled batch job that runs at night, extracts all changed rows, transforms them, and dumps them into the target. It's simple to set up, but the data is always stale. By morning, you're looking at yesterday's reality.
Change Data Capture (CDC) fixes this by listening to the database's internal transaction log ā PostgreSQL's WAL (Write-Ahead Log), MySQL's binlog, or MongoDB's oplog. Every INSERT, UPDATE, and DELETE is captured the moment it's committed and streamed to your downstream system. No polling, no full-table scans, no overnight batch windows.
The trade-off: CDC requires infrastructure investment (Debezium, Kafka, or specialized connectors) and careful schema management. But for any business where data freshness matters ā inventory, customer status, financial records ā the switch from ETL to CDC is non-negotiable.
-- ETL approach (batch, runs every night)
SELECT * FROM orders
WHERE updated_at > NOW() - INTERVAL '24 hours';
-- Problem: data is 0-24 hours stale
-- CDC approach (event-driven, real-time)
-- PostgreSQL WAL listener captures:
{
"op": "UPDATE",
"table": "orders",
"before": { "status": "pending" },
"after": { "status": "shipped" },
"ts_ms": 1710000000000
}2The Idempotency Standard
In distributed systems, the network will fail. A sync task will time out mid-execution, the retry will fire, and now you have the same record being written twice. If your pipeline uses a naive INSERT, you get a duplicate. If it uses a naive UPDATE, you might overwrite a record that was already updated by something else in the gap.
Idempotency solves this. An idempotent operation produces the same result no matter how many times it runs. The primary tool is Upsert (also called INSERT ... ON CONFLICT DO UPDATE in PostgreSQL). Instead of blindly inserting, you provide a unique key. If a record with that key already exists, update it. If not, insert it. Run it once or ten times ā same result.
In n8n, this means avoiding the 'Create' operation on database nodes when retries are possible. Always prefer 'Upsert' with a stable natural key (order ID, user email, external system ID). This single habit eliminates an entire class of data corruption bugs.
-- Naive INSERT (DANGEROUS on retry)
INSERT INTO contacts (email, name)
VALUES ('[email protected]', 'Alex');
-- On retry: ERROR: duplicate key value
-- violates unique constraint
-- Idempotent UPSERT (safe to retry)
INSERT INTO contacts (email, name)
VALUES ('[email protected]', 'Alex')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
updated_at = NOW();3Conflict Resolution & Monitoring
When two systems can write to the same record simultaneously, you get write conflicts. The simplest resolution strategy is Last Write Wins (LWW): compare timestamps and keep the most recent. It's deterministic and easy to implement, but it can silently discard valid updates if clocks aren't synchronized (use updated_at fields with UTC timestamps, not local time).
More complex scenarios require custom merge logic ā field-level merging where system A owns certain fields and system B owns others, or vector clocks for distributed systems that need causal ordering. For most automation use cases, LWW + upsert is sufficient.
Finally, monitor your sync lag. The delay between a change in the source database and its appearance in the target is your key health metric. Set up an alert: if lag exceeds 5 minutes, something is broken ā a dead consumer, a failed connector, or a full queue. Don't wait for users to report stale data.
// LWW conflict resolution in n8n Code node
const incoming = $input.item.json;
const existing = await db.findOne(incoming.id);
if (!existing || incoming.updatedAt > existing.updatedAt) {
await db.upsert({
id: incoming.id,
data: incoming,
key: 'id'
});
return { status: 'updated' };
} else {
return { status: 'skipped (stale)' };
}