Most reconciliation logic is hidden in Excel formulas and the analyst's memory. The same mapping, the same cutoff and the same tolerance are recreated every month, and the process changes whenever the analyst changes. A reusable reconciliation rule documents one decision, names its inputs and output, and can be tested, versioned and rerun. This guide shows how to design those rules.
Work through the anatomy of a rule, then apply it to matching, tolerance, timing and exception decisions. The result is a small library of stable rules that make each month's run reproducible and each change deliberate.
Anatomy of a rule
Every rule has the same parts. A name that is stable and unique, such as R_01_Map_Product_Store_Period. An owner who answers for its logic. A set of named inputs, such as the sell-out file, the product master and the store mapping. A body that states the decision in words and, where useful, in a formula. An output that names what the rule produces, such as a mapped dataset or a pass or exception verdict. And effective dates that say when the rule started and when it stops applying.
If a rule cannot be described in a sentence, split it. A rule that maps product codes, applies a tolerance and assigns an owner is three rules, not one.
Inputs and outputs
Write down the exact inputs a rule expects: file, column, key and reference table. A rule that depends on an unnamed file or an assumed column cannot be rerun reliably, because the next person cannot reconstruct what it consumed. Name the version of each reference table too, so a product master change does not silently change rule behaviour.
State the output at the same grain as the decision. A matching rule outputs a joined dataset; a tolerance rule outputs a pass or exception for each matched row; a timing rule outputs a period label. When the output is clear, the next rule can use it as an input.
Rule order matters
Rules run in a defined sequence, and later rules rely on the outputs of earlier ones. Standardize before you map, map before you compare, compare before you apply tolerance, and apply tolerance before you classify exceptions. If the order changes, the same rules can produce different results, so document the sequence alongside the rules.
Record the order explicitly. When a new rule is added, place it in the sequence and state which existing rules consume its output and which it consumes.
Tolerances are decisions, not defaults
A tolerance states how large a matched difference must be before it becomes an exception. It can be absolute, such as five units, relative, such as two per cent of the higher total, or a combination. Name the reference the tolerance applies to, or the rule will be ambiguous when the two sides differ.
Set tolerances from evidence, not habit. A tolerance that is too loose hides real problems; one that is too tight floods the exception queue with noise. Document why the level was chosen and review it when recurring exceptions show it is either too wide or too narrow.
Effective dates make changes deliberate
Every rule carries an effective-from date and an optional effective-to date. When a retailer changes its product code or a market changes its calendar, the rule changes at a defined date rather than in an edited formula with no record. Rules with effective dates also explain historical runs: the version that applied in January may differ from the one that applies now.
Treat an edited formula as a new version with a new effective date. The old version stays available so past results remain reproducible and future changes remain traceable.
Test each rule before it runs
Give each rule a small set of fixed test cases that cover normal and edge behaviour. For a matching rule, test a matched row, an unmapped product and a duplicate key. For a tolerance rule, test a pass, a marginal difference and a clear exception. For a timing rule, test a date on the boundary of the period and a date outside it.
Run the tests whenever the rule changes and before each cycle if the inputs changed. A rule that passes its tests is repeatable by design, not by memory.
Versioning keeps the trail
Store each rule version with its name, version number, change date, author and the reason for the change. Keep every version instead of overwriting it. The version history answers two questions that recur in every reconciliation: which logic produced last month's numbers, and what changed since then.
Keep the version history in the same place as the rule itself, so the documentation and the executable logic cannot drift apart.
Exceptions the rules produce
Rules should produce a predictable exception queue. A well-designed rule set separates three types of output: records that pass, records that become exceptions, and records that need a human decision because the rule cannot classify them. Design each rule to say which type it produces and why.
- Pass within tolerance. The rule states the tolerance and the reference used.
- Exception with a cause-based category, such as timing, mapping, movement or source quality.
- Refer when the rule lacks the evidence to decide, such as an unmapped identifier awaiting the master-data queue.
Do not let a rule silently drop a row that it cannot classify. A refer output keeps the decision visible and the queue complete.
Documentation that travels with the rule
Document each rule in plain language next to its logic: what it does, why it exists, what it depends on and who owns it. The documentation should let someone who has never seen the process apply the rule correctly and explain it in a review. Write it for the next person, because that person is likely to be the reason the rule needs to be explained.
A library of documented rules is the natural precursor to automation. When the rules are stable and tested, automating them is a mechanical step; when they are not, automation only makes the hidden logic faster.
Practical takeaway
Design reconciliation rules as documented, named decisions with explicit inputs, outputs, order, tolerances, effective dates, tests and an owner. Keep every version and write the documentation for the next person. A small library of reusable rules makes each month's run reproducible, each change deliberate and the step toward automation straightforward.
Marksyte's data reconciliation and controls service can design the rule library, and its data mapping and integration service can implement it across source systems.
Frequently asked questions
What is a data reconciliation rule?
A documented decision that states its inputs, logic and output: how products or locations map, which calendar and tolerance apply, and what becomes an exception. It has an owner and an effective date and can be tested and rerun.
How do you make reconciliation rules reusable?
Write each rule once with a stable name, explicit inputs and outputs, a clear order, an owner and effective dates, then version and test it. Reused rules replace formulas and analyst knowledge that are re-derived every month.
What is the difference between a matching rule and a tolerance rule?
A matching rule decides how records are joined, such as by product code, store and period. A tolerance rule decides whether a matched difference passes or becomes an exception, for example within two per cent or five units.