Blog · Pipelines
Change detection: asking "what changed since last time?
Change detection means comparing the current state of a dataset with an earlier state and reporting what was added, removed or altered. For anyone who monitors a list too long to re-read, "what changed since last time?" is a more useful question than "what does the data say?"
Why the question matters
Most monitoring work is repetitive. A compliance officer checks a register. An analyst checks a list of companies. A programme manager checks on past participants. Each time, nearly everything is the same as before. The value is in the small part that is different, and finding it by eye is slow and unreliable.
A system that stores what it saw last time can do the comparison for you. The reader gets a short list of differences and can spend their attention on judging them.
The three things you need
A stable key. To compare a record with its earlier self, you must know which earlier record that is. A registration number or a publication number is ideal. If the source has no such key, you need to build one, which is an entity resolution task. Without a stable key, a renamed company looks like one removal plus one unrelated addition.
History. You cannot compare with a past you did not keep. Either store a dated snapshot of the data at each run, or store a log of every change. The most common mistake in this whole area is overwriting old data with new.
A comparison rule. You need to decide what counts as different. This sounds obvious and is where most of the work lies.
Kinds of change
| Kind | What it looks like | Note |
|---|---|---|
| Added | A key present now and absent before | May be new in the world, or only new to the source |
| Removed | A key present before and absent now | May be a real removal or a failed fetch |
| Modified | Same key, different values | Report the field, the old value and the new value |
| Re-keyed | The same thing appears under a new key | Needs linking to avoid a false "removed plus added" |
How the comparison is done
The simple method compares each record field by field with its previous version. It is precise and tells you exactly which field moved.
A faster method uses checksums. A checksum, or hash, is a short fingerprint computed from content. Compute one for each record and store it. At the next run, compute it again. If the fingerprints match, the record has not changed and can be skipped. If they differ, look at the fields to find out why. The same trick works on whole files: if the file's checksum is the same as last time, there is nothing new to process. Keeping checksums is also part of good data provenance.
Inside a database you control, there is a third option called change data capture. The database already writes every insert, update and delete to its own log, and tools can read that log to produce a stream of changes. This is reliable and efficient. It is not available for sources you do not run, where all you usually get is the current state each time you look.
Separating noise from real change
A naive comparison produces floods of false alarms. Typical causes:
- Extra spaces, different capital letters or changed punctuation
- A date shown in a different format
- The same list of items in a different order
- A number shown with a different count of decimal places
- A field in the source that records when the file was generated, so that it changes on every run
- The publisher reorganising the whole file, so that everything looks new
The answer is to standardise before comparing. Trim, set the case, sort lists, parse dates and numbers into proper types, and leave out fields that are expected to change every run. Compare the standardised form and keep the raw form for the record.
Some real changes are still not worth a person's time. A figure that moves by a tiny amount may not matter. Set thresholds for what is reported, and keep the full log so that nothing is lost.
Two clocks
Every change has two times. One is when it happened in the real world. The other is when your system first saw it. If you check a source monthly, a change you notice today may have happened four weeks ago.
Store both where you can. Some sources state an effective date. Where they do not, be honest in the report. "First observed on this run" is accurate. "Changed today" may not be. Data modellers call these two clocks valid time and transaction time, and keeping both lets you answer the awkward question "what did we know, and when did we know it?"
The disappearing record problem
A record that vanishes is the hardest case. It may have been properly removed. The source may have been briefly broken. Your request may have timed out halfway. The publisher may have moved it to another file.
Two safeguards help. First, never treat a failed or partial fetch as a valid snapshot. If a run returns far fewer records than usual, stop and flag it, do not report thousands of removals. Second, consider waiting for a record to be absent on two runs in a row before reporting it as removed, when the cost of a false alarm is high.
Turning differences into a report
A raw list of differences is not yet useful. A good change report:
- Groups changes by the entity they affect, not by field.
- Shows the old value, the new value and the source of each.
- Puts the changes that matter most at the top, by rules the reader has agreed.
- States the period covered, and says plainly if any source failed during it.
- Says "no changes" when that is true, so that silence is never ambiguous.
Where a change will trigger action, a person should confirm it first. Human in the loop covers how to design that step so it is not skipped.
Where Prism fits
Change detection is the sixth of seven steps in the engine Prism Labs builds: gather, keep receipts, link, review, answer, watch and deliver. The watch step covers reports, exports and what changed since last time. It rests on the earlier steps, since every record keeps its publisher, request and file checksum. The whole sequence is described in what is a data pipeline?, and there is more on the industries page.