Denormalization Without Lying to Yourself

Copying data is not automatically bad. Copying it without naming the owner is.

Every duplicated field needs one source of truth and one reason to exist. The copy is usually one of two things:

1. An immutable historical snapshot, such as the delivery address used for an old order.
2. A disposable cache, such as a customer name copied into a search document for faster reads.

Those two copies need opposite update rules. A snapshot must not change when the source changes. A cache must be repairable from the source.

Build the source map

For each repeated field, record:

• the authoritative table and column;
• every place the value is copied;
• whether each copy is a snapshot or cache;
• the event that creates or refreshes it;
• acceptable lag;
• the repair owner and reconciliation query.

If nobody can answer those points, the system has several unofficial sources of truth.

Propagation patterns

For a cache, update the source first. Record an outbox event in the same transaction, then let a worker refresh the copies. Consumers need idempotent handlers because events can be delivered twice. Track the source version on the copy so the worker cannot overwrite newer data with an older event.

For a snapshot, copy the value at the business moment it becomes history. Name it accordingly, such as billing_email_at_purchase. That makes the intent visible and prevents a well-meaning repair job from rewriting old records.

Avoid dual writes where one request updates two systems with no transaction or durable event between them. They work until one side times out.

Verification checklist

1. Change an authoritative field and prove every cache copy catches up within the allowed lag.
2. Change that same field and prove historical snapshots remain unchanged.
3. Deliver one propagation event twice. The result must be identical.
4. Deliver an older event after a newer one. The copy must not move backwards.
5. Corrupt one cache record and prove reconciliation finds and repairs it.
6. Stop the worker, create updates, restart it, and prove the backlog catches up.

The starter accepts source records, copied-field rules, read paths, mutation events, and the lag threshold. It returns a source map, snapshot/cache classification, propagation plan, repair job, and reconciliation tests. Dry-run produces a reviewable plan and changes no data.

Download the runnable pack

• Complete workflow pack ZIP
• Importable n8n workflow
• GitHub validation workflow
• GitHub manual run workflow
• Docker Compose file
• Fictional sample payload
• Expected safe dry-run result

Comment DENORM for this pack. The rule to keep is simpler: one fact has one owner, even when it has several copies.