Echo in the Backroom

The Power of Reconciliation

Rune Antonsen -

Reconciliation is the process of checking that a target table consists of the same values as the source.

The purpose is to ensure that data has been transferred and transformed correctly from the source. The side effect is raising trust in the data. To be able to do so we need to impose the same business rules as when loading the data when reconciling. We might even create a datamart for the very purpose of reconciling the different business processes that exists, for instance for a data warehouse. When reconciling we often aggregate important numbers on a daily basis per company or department. When numbers are derived from different sources we would take one number from each source to make sure each one is loaded and calculated according to set rules.

Some people question the reason for reconciling data that has been loaded from a given source. This is sound if we think we have a correct process of both loading data incrementally and that the defined business rules always applies. However, scrutinizing incomplete data more closely often reveals holes in the logic; that the business rules don’t cover all aspects or the loading process is incomplete.

I think we should turn the development process around and start with creating reconciliation routines. This approach has similarties to test-driven development and we could achieve a lot more by doing so:

  • Unveiling the business rules before getting started
  • Uncovering how to build the reconciliation will work as a detailed specification sheet for the implementation
  • The reconciliation routines would work as automated test cases
  • As it needs to load larger sets of data from the source - instead of the typical small we load when running unit test – we can early pinpoint slow response from the source and talk to the DBA to make specific indexes or extraction
  • When running the reconciliation alongside the implementation both flaws in the logic of loading and business rules will appear and this will be discovered at an early stage.

So, instead of thinking of reconciliation as a tedious and unnecessary exercise, it could serve as a really good starting point to remedy a lot of unpleasant surprises in the development stage.