Build an n8n Workflow That Reconciles Two APIs Without Duplicating Records

byHossein Hezami •
0

The first reconciliation workflow I ever built "worked" for two weeks. Then the upstream API timed out on page 7 of a paginated response, the workflow retried from the beginning, and I spent an afternoon explaining to finance why the accounting system now contained a customer named "Acme Corp" four times.

That's the thing nobody warns you about: duplicate records in an n8n integration are almost never caused by the reconciliation logic. They're caused by retries, re-executions, overlapping schedule windows, and state that quietly disappears when you redeploy. The join logic was fine. The workflow just wasn't idempotent, and nobody notices until the first network blip.

This article walks through a production-grade pattern for reconciling two APIs in n8n — normalizing their payloads, matching records, deciding what to create, update, or skip, and making the whole thing safe to run twice, ten times, or concurrently. It's written for people who've already built a few workflows and are now staring at the "what happens when this fails halfway through" problem.

TL;DR

  • Normalize both APIs into a single canonical shape with a Code node before you try to match anything.
  • Pick a stable natural key (or a hash of composite fields) — never a database-generated ID that only exists on one side.
  • Do the diff in memory with the Merge node or a Code node; do the bookkeeping (state) outside the workflow execution.
  • Make every write idempotent — upserts, external IDs, or a dedup ledger — because n8n will re-run your workflow eventually.
  • Store reconciliation state in an external datastore, not in workflow static data, if you care about surviving restarts.
  • End with a report node so you can answer "what did last night's run actually do?" without opening the execution log.

📋 Table of Contents

1. The Duplicate That Isn't a Bug

Scenario: You sync customers from a CRM (say, HubSpot) into an invoicing system (say, Xero). The workflow runs every 15 minutes: fetch contacts from HubSpot, fetch contacts from Xero, insert the ones that don't exist. It runs green for days. Then HubSpot has a slow afternoon, the HTTP Request node times out on the fetch step, n8n retries the whole execution, and you get duplicates — or worse, the workflow fails after inserting half the batch, and the next scheduled run re-inserts the same half.

Why it matters: The obvious workflow structure — trigger → fetch A → fetch B → filter → insert — has no concept of "this run already happened." n8n executes your workflow as a fresh, stateless pass every time. Every duplicate-prevention mechanism you build has to accept that premise.

The mental shift that fixes this: stop thinking of the workflow as a sync job and start thinking of it as a reconciliation function:

diff(state, world) → actions

The workflow's job isn't "copy A into B." Its job is "compute the difference between A and B, expressed as idempotent actions, and execute only those actions." The rest of this article is about making each part of that sentence real in n8n.

🧠 The important part: a reconciliation workflow that's correct but not idempotent is not correct. Retries, manual re-runs, and overlapping schedules are guaranteed events in production, not edge cases.

2. Two APIs, One Shape: Normalize Before You Match

Scenario: The CRM returns { "properties": { "firstname": "Ravi", "lastname": "Sharma", "email": "ravi@acme.com" } }. The invoicing API returns { "ContactName": "Ravi Sharma", "EmailAddress": "ravi@acme.com" }. You write merge conditions that compare email on one side and EmailAddress on the other, and six months later someone changes the CRM field mapping and the workflow silently stops matching.

Why it matters: When matching logic is spread across node options, expressions, and field names, the workflow has no single place that says "these two records refer to the same real-world entity." Every comparison is a new chance for drift.

Solution: Insert a Code node after each fetch that maps raw payloads into one canonical shape you control. From that point on, the rest of the workflow only ever sees the canonical shape.

// Code node: "Normalize CRM contact" — Run Once for All Items
return $input.all().map(item => {
  const p = item.json.properties ?? {};
  return {
    json: {
      key: (p.email ?? '').trim().toLowerCase(),
      name: [p.firstname, p.lastname].filter(Boolean).join(' ') || '(unnamed)',
      email: (p.email ?? '').trim().toLowerCase(),
      // updatedAt gives us the diff signal later; null means "unknown"
      updatedAt: p.lastmodifieddate ?? null,
      source: 'crm',
      rawId: item.json.id,
    },
  };
});

// Code node: "Normalize invoice contact" — Run Once for All Items
return $input.all().map(item => ({
  json: {
    key: (item.json.EmailAddress ?? '').trim().toLowerCase(),
    name: item.json.ContactName ?? '(unnamed)',
    email: (item.json.EmailAddress ?? '').trim().toLowerCase(),
    updatedAt: item.json.UpdatedDateUTC ?? null,
    source: 'billing',
    rawId: item.json.ContactID,
  },
}));

Why this works: All the API-specific noise is quarantined in one node per source. When an API changes a field name, you fix one mapping function, and the diff logic — which now compares key against key — doesn't change at all. It also gives you a place to do the boring-but-critical cleanup: trimming whitespace, lowercasing emails, coalescing nulls. You'd be surprised how many "duplicates" are just "Ravi@Acme.com" vs "ravi@acme.com ".

Practical note: I keep a source field on every canonical record. When something goes wrong, the report (Section 10) can tell you which side the orphaned record came from, and that's often the difference between a five-minute fix and an hour of guessing.

Also decide explicitly what "unknown" means. The ?? null above isn't laziness — if the source doesn't provide a last-modified timestamp, your diff logic needs to know that, because "null vs timestamp" and "timestamp vs timestamp" are completely different decisions.

3. The Idempotency Key Decision That Everything Else Depends On

This is the decision that quietly determines whether the whole system works, and it's the one people get wrong most often.

The wrong answer: using the record's ID from one side. HubSpot's internal contact ID means nothing to Xero, and Xero's UUID means nothing to HubSpot. If your join key only exists on one side, you don't have a join — you have a lookup table you still need to store somewhere (which is a valid pattern, and we'll get to it in Section 8, but it's a different architecture with different failure modes).

The better default: a natural key — a field that exists on both sides because it describes the entity itself, not the row. Emails for contacts, order numbers for orders, SKU + warehouse for inventory, invoice number for invoices.

The pragmatic answer when no clean natural key exists: a deterministic composite hash. Say you're reconciling invoices and the only reliable identity is the tuple of (vendor ID, invoice number):

// Inside the normalize Code node, when there's no single natural key
const crypto = await import('crypto');

return $input.all().map(item => ({
  json: {
    ...item.json,
    key: crypto
      .createHash('sha256')
      .update(`${item.json.vendorId}|${item.json.invoiceNumber}`)
      .digest('hex')
      .slice(0, 16),
  },
}));

Wait — can you import crypto in an8n Code node? Yes: the Code node runs in Node.js and supports dynamic import() of built-in modules (the require-style module list is curated, but await import('crypto') works on self-hosted). On n8n Cloud, module access is more restricted, so if you're targeting Cloud, compute the key with plain string concatenation instead — ${vendorId}|${invoiceNumber} is a perfectly good key as long as you pick a delimiter that can't appear inside the values.

⚠️ Gotcha: pick your delimiter carefully. I once saw a composite key built as name + city, and a customer named "Carlton Rhodes" in "Leeds" collided with "Carl" in "Ton Rhodes Leeds." Use a delimiter that's structurally impossible in the data (a |, a ::, a tab), or quote-escape the components, or hash.

One more subtlety: the key must be immutable from the record's perspective. If your key is the email address and a user changes their email in the CRM, the next run sees an "new" record on one side and an "orphan" on the other — and now your reconciliation workflow is a duplicate factory. If entities can legitimately change their natural key in either system, you need a cross-reference table (Section 8) rather than pure natural-key matching. There's no way around this; it's a fundamental trade-off, not an implementation detail.

4. Fetching 50k Records Without Blowing Up the Execution

Scenario: Your workflow runs fine with the 200 test records from the trial account. On real data, the first fetch returns 50,000 records, n8n holds every item in memory for the whole execution, the Merge node chokes, the execution history grows to hundreds of megabytes, and the scheduled run takes 40 minutes — during which the next scheduled run starts.

Why it matters: Reconciliation workloads are unbounded in a way that most automation isn't. A "send a notification" workflow processes one item per trigger. A reconciliation workflow processes the entire database every run. Everything about the design changes at that scale.

Solution — three moves:

First, use the HTTP Request node's built-in pagination. n8n's HTTP Request node has first-class pagination support (under Options → Pagination): you define a pagination-complete condition, and the node loops through pages internally and emits all items from one node execution. This is far more efficient and far more debuggable than manually chaining page-fetches through a Loop Over Items node — one node, one retry policy, one place to configure auth.

Second, shrink the fetch window instead of the fetch volume. The best performance optimization for reconciliation is not fetching what you don't need. If the API supports updated-since filters, your scheduled run should ask for records modified since the last successful run, not the whole table:

GET /contacts?modified_since={{ $('Get Last Run').item.json.lastSuccessAt }}

Store that timestamp in your external ledger (Section 8) and pass it through the workflow. Now your run is proportional to the change rate, not the table size — a few dozen records instead of 50,000, even on a large account.

Third, batch the writes, not the reads. The Loop Over Items node ("Split In Batches" in older versions) with a batch size of, say, 25–50 lets you rate-limit the write side so you don't hammer the target API's rate limiter. Keep the reads unbatched if you can: the diff needs the full picture of both sides to be meaningful.

🚨 Production warning: execution data retention matters here. A reconciliation workflow holding 50k items per execution, running every 15 minutes, will fill up n8n's execution history (SQLite by default on self-hosted) embarrassingly fast. Move execution storage to Postgres and set a sane pruning window (env: EXECUTIONS_DATA_PRUNE=true, EXECUTIONS_DATA_MAX_AGE), or your disk will let you know about the problem first.

5. Three Ways to Diff the Two Sides — and When to Use Each

Now the core question: given canonical records from side A and canonical records from side B, how do you compute "exists on both," "exists only in A," "exists only in B," and "exists on both but differs"?

You have three real options in n8n, and they're not interchangeable.

Option A: The Remove Duplicates node (the 80% answer)

The Merge node's sibling for simple cases is the dedicated Remove Duplicates node, but read its modes carefully — one of them is exactly what reconciliation needs, and the others will mislead you:

  • Remove Items Processed During Current Execution — dedupes within one run. Not what you want here.
  • Remove Items Processed in Previous Executions — this is the interesting one. It remembers items from earlier runs using workflow static data and lets new items through. This is really a state tool (Section 8), not a diff tool.
  • Field-based dedup — collapse each side to unique keys.

For the diff itself, most people reach for the Merge node in Combine → Matching Fields mode: feed side A into input 1, side B into input 2, match on key. Its output modes are the whole game:

Merge output modeWhat you getUse for
Merge matched dataOne item per key present on both sides, with combined containing both payloadsRecords needing update comparison
Keep unmatchedOnly keys present on one sideNew records to create / orphans to flag

So a practical layout: two Merge nodes off the same normalized streams — one configured to keep matches (compare updatedAt in a downstream Code node to decide update vs skip), one configured to keep non-matches (split into "create in B" and "investigate" by the source field).

Option B: The Code node (the full-control answer)

When the matching has any nuance — tolerance for missing emails, fallback to name+zip matching, business rules like "billing system is authoritative for status" — stop fighting node options and do the diff in a single Code node:

// Code node: "Diff" — Run Once for All Items
// Input: two items, one per source, each carrying an array of canonical records.
// Feed it via an Aggregate node or by fetching each side in a Code node.

const byKey = (records) => {
  const map = new Map();
  for (const r of records) {
    if (!r.key) continue; // never match on an empty key
    if (!map.has(r.key)) map.set(r.key, []);
    map.get(r.key).push(r);
  }
  return map;
};

const a = byKey($input.all().find(i => i.json.source === 'crm').json.records);
const b = byKey($input.all().find(i => i.json.source === 'billing').json.records);

const actions = [];

for (const [key, crmRecs] of a) {
  const billingRecs = b.get(key);
  if (!billingRecs) {
    actions.push({ action: 'create', side: 'billing', record: crmRecs[0] });
    continue;
  }
  const crm = crmRecs[0];
  const billing = billingRecs[0];
  const stale =
    crm.updatedAt && billing.updatedAt && crm.updatedAt > billing.updatedAt;
  actions.push({ action: stale ? 'update' : 'skip', side: 'billing', record: crm, existing: billing });
}

for (const [key, billingRecs] of b) {
  if (!a.has(key)) actions.push({ action: 'orphan', side: 'billing', record: billingRecs[0] });
}

return actions.map(action => ({ json: action }));

Why this works: The diff is explicit, testable, and readable in one place. Every record gets exactly one action — create, update, skip, or orphan — which becomes the routing signal for everything downstream. You can unit-test this logic in isolation by pasting fixtures into the node, which is something you simply can't do with a Merge node's configuration UI.

Also note the guard clauses: empty keys are skipped rather than matched (two records with missing emails would otherwise "match" on undefined), and duplicates within one side are collapsed to the first rather than crashing the Map. Reconciliation code that assumes clean input is reconciliation code that hasn't met production yet.

Option C: Let the database do it (the scale answer)

If both datasets can land in the same SQL database — even as scratch tables written at the start of the run — the entire diff becomes one query:

SELECT crm.key
FROM crm_contacts crm
LEFT JOIN billing_contacts bc ON bc.key = crm.key
WHERE bc.key IS NULL;   -- records in CRM missing from billing

A FULL OUTER JOIN with null checks gives you all four categories in one shot, and Postgres will diff a million rows faster than any in-memory JavaScript you're willing to run inside a workflow execution.

Which one, when

ApproachComplexitySweet spotBreaking point
Merge / Remove Duplicates nodesLow< ~10k items, simple key matchNuanced match rules, large volumes
Code node diffMedium1k–100k items, business rules in the matchMemory + execution-history bloat
SQL diffHigher (needs a DB)100k+ items, or diff rules that are inherently set logicExtra infra to operate

💡 Practical note: I'd start with the Code node diff even for small datasets, because the action record it produces is what powers your report, your dead-letter queue, and your tests. Node-configured merges are fine until you need any of those, and then you end up rewriting into a Code node anyway.

6. Writing Back: Why "Just Insert" Is the Last Thing You Do

The diff tells you what to do. The write-back is where duplicates are actually born, so this is where idempotency stops being a buzzword.

Scenario: Your diff says "create 40 records in billing." The workflow inserts 40 records. The workflow then crashes on an unrelated reporting node. Someone clicks "Execute Workflow" again from the editor. The diff runs fresh — and now those 40 records exist on both sides, so they're "matched" and skipped. No duplicates, actually. But suppose the crash happened mid-insert, after 23 of 40: the re-run's diff sees 23 matched and 17 new. Also fine! That's the beauty of re-deriving the diff from the world each time.

The failure mode is different: the write itself being non-idempotent while the workflow's state says it already did the work. That happens when people "optimize" by storing the diff results and resuming — or when the target API's "create" endpoint silently creates a second record for the same logical entity instead of rejecting it.

The fix, in order of preference:

1. Use the target's upsert semantics if they exist. Many APIs have a PUT-style upsert or support a lookup field on create (Xero's contacts accept an AccountNumber and dedupe on it; HubSpot contacts upsert on email by design). Pointing your write at an upsert endpoint makes the whole duplicate question mostly vanish — the API itself is your dedup ledger.

2. Send an idempotency key if the API supports one. Stripe-style Idempotency-Key headers are the gold standard: the API remembers the key and replays the original response on retry. If your target API has this, put the natural key (or its hash) in the header and use n8n's built-in retry on the node:

// HTTP Request node → Header Parameters
Name: Idempotency-Key
Value: {{ $json.key }}

3. Check-then-write with the key, if that's all you have. Before each insert, query the target for the key; skip if present. This is the weakest option — it has a TOCTOU window under concurrency (Section 9) — but it's honest, and for 15-minute scheduled reconciliation runs it's usually adequate.

4. Never blind-insert. A bare POST with no dedup on either side isn't a reconciliation workflow; it's a duplicate generator with extra steps.

Why this works: the ordering matters. Idempotent endpoint semantics (options 1 and 2) are safe no matter how many times the workflow runs, including concurrently. Check-then-write (option 3) is only safe against sequential re-execution. When you know which category you're in, you also know whether you need to worry about Section 9 — or whether you can schedule generously and move on.

7. Partial Failures and the Dead-Letter Ledger

Scenario: Your write phase processes 40 actions. Records 1–18 succeed. Record 19 fails — the target API rejects its payload (a 20-character name where the API allows 15). The workflow errors. Records 20–40 never run. The 18 created records exist; the failure is invisible except in a red dot in the executions list that nobody is watching at 2 a.m.

Why it matters: A reconciliation run that stops at the first bad record doesn't just lose throughput — it loses visibility. And on the next run, record 19 fails again, forever, blocking everything behind it. One malformed record now degrades your entire sync.

Solution: make the write loop resilient and make the failures queryable data.

First, the node settings. On the write node(s), enable Retry On Fail (a few attempts with a backoff handles transient 5xx and rate limits), and set On Error → Continue (in older versions, "Continue On Fail") so a single bad record doesn't kill the batch. Route errors forward — set the error output to continue to your ledger step.

Second, capture the outcome of every action into a dead-letter ledger — a table in Postgres, a sheet, a Notion database, whatever your team actually looks at. A Code node after the write phase can stamp each action:

// Code node: "Stamp outcomes" — Run Once for All Items
return $input.all().map(item => ({
  json: {
    key: item.json.key,
    action: item.json.action,
    status: item.json.error ? 'failed' : 'ok',
    error: item.json.error ? String(item.json.error.message).slice(0, 500) : null,
    runId: $execution.id,
    ranAt: new Date().toISOString(),
  },
}));

Then a simple Switch node routes ok to the report and failed to the ledger insert.

Why this works: the poison-record problem (that 15-character name) is now decoupled from the batch problem (the other 22 records that never got written). Records 20–40 proceed, record 19 lands in the ledger with its actual API error message, and your next run — which re-derives the diff from the world — automatically retries record 19 and skips the 18 successes. No bookkeeping needed for what succeeded; the world is the state.

⚠️ Gotcha: "Continue On Fail" is node-granular, and it's easy to accidentally make your whole workflow swallow failures by setting it on the wrong node and never checking the error branch. Pair it with the report step in Section 10 — if failed > 0 and nobody's looking at the ledger, you've built a silent failure machine, which is worse than a loud one.

One more thing about error workflows: configure an Error Trigger workflow that sends you (Slack, email) a message when an execution fails. It's cheap to set up, and it's the difference between "the sync has been broken for nine days" and "the sync broke nine minutes ago."

8. State That Survives: Preventing Duplicates Across Executions

Here's the subtle version of the duplicate problem. Sections 5 and 6 make a single run safe. But some duplicate patterns only appear across runs, and some only appear after a restart. This is where n8n-specific knowledge pays off.

Workflow static data: convenient, and a trap

n8n gives workflows static data — a small object persisted between executions of a workflow, accessible in Code nodes via $getWorkflowStaticData('global'). The Remove Duplicates node's "Remove Items Processed in Previous Executions" mode is built on it: it remembers seen keys between runs and only passes genuinely new items through.

This is a real, useful feature — and I'd treat it as a development convenience, not production state, for three reasons:

  • On n8n Cloud, static data has historically had tighter size limits than people assume; on self-hosted, it lives in the n8n database.
  • It's tied to the workflow's identity. Clone the workflow, or export/import it into a new instance during a migration, and your "already processed" memory is gone — and now every historical record looks new again.
  • It doesn't survive the "wipe and redeploy" flows that self-hosted setups sometimes do.

The failure mode is nasty precisely because it's silent: static data works perfectly for months, then a restore-from-backup duplicates your entire dataset in one glorious run.

🚨 Production warning: if you use the Remove Duplicates node's previous-executions mode, understand that its memory is only as durable as your workflow static data. For anything that creates records in a system of record, I'd back it with an external check (Section 6) as the real guarantee.

The external ledger: where state belongs

For durable reconciliation state, put it in a system that's designed to be state: a Postgres table, a Redis set, a bucket in S3, even a dedicated Airtable base if that's your team's comfort zone. The shape is simple:

CREATE TABLE reconciliation_state (
  key TEXT PRIMARY KEY,          -- the natural key from Section 3
  source_record_id TEXT,          -- rawId on the CRM side
  target_record_id TEXT,          -- rawId on the billing side
  last_seen_run TEXT,             -- which execution last touched it
  last_success_at TIMESTAMPTZ,    -- your incremental-fetch watermark
  status TEXT NOT NULL DEFAULT 'ok'
);

This table does three jobs at once:

  1. Cross-reference — once a record is created on both sides, store the ID pair. Now you can join on internal IDs forever, and a user changing their email (the Section 3 trap) no longer orphans the pair.
  2. Watermark — last_success_at drives the incremental fetch from Section 4.
  3. Dedup guarantee — before creating, check the ledger; after creating, insert the pair. If the check-then-write race worries you, a PRIMARY KEY on key means the ledger itself rejects the double-insert, and you can catch that error instead of a duplicate record downstream.

Why not just query the target API every time instead of keeping a ledger? You can, and for small datasets you should — deriving truth from the world is more robust than any cache. The ledger earns its keep at scale (API rate limits make per-record existence checks expensive) and when the target API's own dedup semantics are weak.

9. Concurrency: The Overlap Window Nobody Plans For

Scenario: Your reconciliation runs every 15 minutes via Schedule Trigger. One evening, a run takes 17 minutes. The next scheduled run starts while the first is still writing. Two executions are now computing the same diff against a slightly stale picture of the world. Both decide to create the same record. Depending on the write semantics from Section 6, you get one duplicate or two.

Why it matters: schedule overlap isn't exotic — it's what happens the first week your dataset grows 30%. And n8n's default behavior won't save you: scheduled executions run concurrently; the platform doesn't queue them for you.

Three defenses, in escalating order of robustness:

1. Idempotent writes make overlap mostly harmless. If your create path is a true upsert or carries an idempotency key (Section 6, options 1–2), two concurrent runs racing to create the same record collapse into one. This is the cheapest defense and the best one. Prefer it whenever the target API allows.

2. Set an execution timeout and make it shorter than your schedule. In the workflow settings, configure the timeout so a wedged run dies before the next one starts. Combined with Retry On Fail on nodes, this gives you retry-without-overlap semantics for free.

3. Use a lock. If the write semantics can't be made idempotent, take a lock at the start of the run — the cleanest n8n-native version is a tiny Redis SET key value NX EX 900 (or a Postgres advisory lock) at the top of the workflow. If the lock isn't acquired, the run exits immediately. Release it at the end, and let the TTL clean up after crashes.

On self-hosted instances with queue mode, there's also the environment-level lever: N8N_CONCURRENCY_PRODUCTION_LIMIT caps how many productions executions run at once across workers. That's a global throttle, not per-workflow serialization — useful to know, but don't mistake it for overlap protection on this one workflow.

🔍 Why this matters: I've seen teams "fix" overlap by making the schedule less frequent — every 15 minutes becomes every hour. That doesn't fix anything; it just moves the failure to the day the run takes 61 minutes. Fix the idempotency or fix the lock. Interval-widening is a napkin over a check-engine light.

10. Build a Reconciliation Report Into the Workflow Itself

This is the least glamorous section and the one that determines whether anyone trusts the system.

Scenario: Finance asks, "did yesterday's invoices sync?" The honest answer is a shrug, because "did it run" and "did it reconcile correctly" are different questions, and your only evidence is a wall of execution logs in the n8n UI.

Solution: end every run with a small aggregation step and a single message. A Count items after the diff, one Count after the writes, one Code node to assemble, one Slack/email node to send:

// Code node: "Build report" — Run Once for All Items
const diff = $('Count After Diff').all()[0].json;
const writes = $('Count After Writes').all()[0].json;
const failures = $('Count Failures').all()[0].json;

return [{
  json: {
    text: [
      `🧮 Reconciliation run ${$execution.id}`,
      `CRM: ${diff.crmRecords} | Billing: ${diff.billingRecords}`,
      `Matched: ${diff.matched} | Created: ${writes.created} | Updated: ${writes.updated}`,
      `Orphans: ${diff.orphans} | Failures: ${failures.count}`,
      failures.count > 0 ? '⚠️ Failures logged to dead-letter ledger.' : '✅ All clear.',
    ].join('\n'),
  },
}];

The numbers should be derived, not asserted — count the actual items that flowed through each branch, and sanity-check them against each other. created + matched + orphans should equal the input count, and if it doesn't, the report is where you want to see that discrepancy, not find out from a customer.

Beyond the daily message, two observability habits pay for themselves:

  • Alert on drift, not just errors. If orphans jump from 3 per run to 300, something upstream changed (a field mapping, a new API version) and the workflow is green while silently syncing nothing. A threshold check in the report node turns that into an alert.
  • Use n8n's execution history for forensics, not monitoring. Filter executions by status and error, dig into a specific failing run's item data — the UI is genuinely good at this. But nobody should have to poll it to know the system is healthy.

11. When You Shouldn't Build This in n8n at All

After all this, the honest caveat: reconciliation is a data engineering problem, and n8n is an orchestration tool. There's a scale and complexity line past which the right answer is a different architecture entirely.

Signaln8n workflowLook elsewhere
VolumeUp to ~100k items per run comfortablyMillions of rows, or high-churn streaming data
Diff logicKey matching + field-level update rulesFuzzy matching, probabilistic dedup (use-cases built for record-linkage tooling)
StateA ledger table + upsert semanticsTrue distributed transactions / exactly-once requirements
CadenceScheduled batches (minutes+)Sub-second or continuous streaming reconciliation
TeamOps/integration engineers own itData engineers with dbt/Airflow already in place

The "look elsewhere" column has real candidates: a nightly SQL job if both systems can land in one warehouse; Airbyte or Fivetran with CDC if you just need tables in sync; a small custom service if the matching logic is the product. Don't build a reconciliation engine inside a workflow tool because the workflow tool was already on the screen.

But within its envelope — and most two-API business integrations live well inside it — the n8n version has genuine advantages: the execution history gives you replayable, item-level forensics for free; non-engineers can read the workflow; and the failure modes we've covered are all visible rather than buried in a cron log on a box nobody remembers exists.

The Checklist I'd Actually Ship With

Before I'd call any n8n reconciliation workflow production-ready, I want yes answers to all of these:

  1. Both sides normalized to one canonical shape, with a key field that's a stable natural key or a delimiter-safe composite.
  2. Every write is idempotent — upsert endpoint, idempotency key, or check-then-write backed by a ledger with a unique constraint.
  3. The diff is re-derived from the world every run — no "resume from saved diff" shortcuts.
  4. Writes continue past poison records, and failures land in a dead-letter ledger with the real error message.
  5. Durable state (cross-reference IDs, watermark) lives in an external datastore, not static data, if duplicates are unacceptable.
  6. The workflow can't overlap with itself — timeout + idempotent writes, or a lock.
  7. Every run ends in a report with derived counts, and a threshold alert for drift.
  8. The Error Trigger workflow exists and goes somewhere a human actually reads.

That's the whole philosophy, compressed: derive the diff from reality, make the writes idempotent, keep the state somewhere durable, and make every run narrate what it did. Do those four things and the scary parts of API reconciliation — retries, restarts, overlap, drift — stop being incidents and become just another line in the morning report.

Tags:

Post a Comment

Previous Post Next Post