Database XP / Over time

The reporting copy that becomes a second system

It starts as a convenience and acquires dependents.

A copy of production data for reporting is created to keep analytical load off the operational system, which is sound. What follows is that reports are built on it, then integrations, then a process that writes something back, and the copy becomes a system with its own consumers and no ownership.

The specific hazard is divergence. Transformations applied during the copy encode business rules, and those rules drift from the ones in the application, so the two systems produce different answers to the same question and both are defended by their users.

The controls are ordinary: the copy has an owner, the transformations are in version control, and there is a defined position on whether anything may write to it. Establishing those early is far easier than retrofitting them once a dozen reports depend on it.

It is also worth periodically checking that a small number of key figures reconcile between the two. Where they do not, the discovery should be made deliberately rather than in a meeting where two people have brought different numbers.

The other reconciliation worth automating is a nightly comparison of a handful of key counts between the operational system and the reporting copy. It takes minutes to build and it converts a class of silent divergence into an alert, which is considerably better than discovering it in a board paper.