Blog · Pipelines

Our checks caught a common data tool quietly changing records

· 3 min read

While loading North Dakota's flood records for Prism Water Risk, our checks caught a widely used data conversion tool changing the data as it loaded it. It rounded long decimal numbers and turned plain dates into timestamps that could fall on a different day. Nothing crashed and nothing warned us. The only reason we know is that we re-counted every record against the original files.

What went wrong

Two things changed on the way in.

Long decimals were cut short. Numbers with more than 15 significant digits were rounded to 15. For most uses that is a rounding error nobody would notice. For a record that is supposed to match its source exactly, it means the stored value is no longer the published value.

Plain dates became timestamps. A date in the source file, such as the day a flood map took effect, is just a day. The tool stored it as a moment in time in a fixed time zone. Read back in North Dakota's time zone, a date like that can show as the evening before. A flood map that took effect on one day would appear to have taken effect on the previous one.

Neither change is dramatic. That is what makes this kind of problem dangerous. A loud failure gets fixed the same afternoon. A quiet one ends up in a report, then in a decision, and is found months later, if ever.

How we caught it

We treat loading data as something to prove, not assume. For every source, a separately written checker reads the original file again, on its own, and compares it with what was stored, record by record. The counts must match, and a sample of records must match exactly, field by field.

Across the sources behind Prism Water Risk, that meant 1,759,122 records re-counted, from FEMA's flood maps and flood insurance claims to building footprints and county boundaries. The counts matched. The exact comparison is where the rounding and the shifted dates showed up.

The same checks found a smaller problem too. One helper column was being copied into a spare table 799,405 times. We confirmed every copy was identical to the original value before removing the duplicates, so no information was lost.

How we fixed it

We replaced that tool with our own loader that stores numbers and dates exactly as published. Then we reloaded the affected tables from the original files, which we keep unchanged precisely so that this is possible. No original file was altered or lost. After the reload the checker passed again, this time with an exact match.

What any team can take from this

You do not need our software to avoid this. A few habits cover most of it.

  • Keep the original files, unchanged. If you only keep the loaded version, you cannot find out later what the source actually said. See data provenance.
  • Check with something you did not use to load. A tool that introduced an error will usually read its own output back without complaint. An independent reader will not.
  • Compare values, not just counts. Matching row counts proves nothing was dropped. It does not prove nothing was changed.
  • Watch dates and long numbers first. Time zones and floating-point rounding are the most common silent changes in data work.
  • Record what you checked. A written check can be repeated after every reload.

Where Prism fits

This is one of the seven steps our engine runs for every field it works in: keep a receipt for every record, and prove the stored data matches the source before anything is built on it. Answers then cite the stored evidence, as described in why AI answers need sources.

Prism Water Risk, where this happened, is in development. If your organisation relies on data that has to be exactly right, join the waitlist.

Sources

Keep reading