Our data warehouse is a record of data over time.   It allows us to answer two types of question about the past:

• What do we know now about a time in the past?
• What did we know then?

It’s the second of these that is a bit tricky.  As with the study of history, what we know about the past can change over time as new facts come to light.  For our IT systems, this means the data they hold is sometimes updated with new information about the past.  For example, if a mistake is found in an employee’s service record, the HR team will update that record so that our data is up to date – something that may be very important for the person concerned.

In this example, our data warehouse holds information that lets us ask not only what we know now about that employee’s service record, but also what we knew at a time before the record was corrected.

Every night, the data warehouse receives a list of all the changes that have been made to the HR system (and other systems) during the day.  New additions and current changes are easily added to the integrated data store that lies at the heart of the warehouse, from where they are provisioned to the strategic reporting team and other analysts.

When the list of changes includes a correction to past data, this process of integration becomes more complicated.  What may seem like a simple change to the operator can often lead to complex changes in the underlying data.  The data warehouse has to compare the whole data table in the source system with its own equivalent table, interpreting the records to understand what was known when.

This has proven quite challenging for the team.  They have to understand the different possible behaviours of the source system, and to design and implement processes to maintain an accurate history.  This is important data, as key strategic decisions rely on the accuracy of this information.

In the case of the service record, each time the team deployed a solution to resolve a set of problematic data, the result would reveal some further cases that needed to be handled.  Fortunately, the number of such problematic records reduced with each iteration and now we believe we have a solution that handles all the known cases.

No-one promised that implementing time travel would be easy.

Implementing time travel / Enterprise Architecture by blogadmin is licensed under a Creative Commons Attribution CC BY 3.0