History Tables

(Or Slowly Changing Tables)

A very common use-case in analytical database design is the ability to analyze changes to tables over time. For event-like time series data, this is easy – the events are never changed after their initial insertion, and they’re timestamped, thus allowing you to query the changes over time.

But ever-changing table data present a more difficult case. For example, consider a users table that contains the following record:

{id: 15, city: ‘San Francisco’}

This record might be updated in the future, when the user changes their address, for example. For most use-cases, this is a good thing – it allows you to easily analyze the current locations of your users, but in some cases you might be interested in keeping track of these changes, in order to analyze how the data is changing over time.

To achieve this, supports the generation of History Tables by transforming the static table into a new time-series table that contains historic snapshots of every row in the original table. This allows you to query the original table or query the revisions on the table, effectively rewinding back to any point in time. Of course, you can join these two tables together to achieve any complex analysis that comes to mind.

For example, after the user has changed their address to Chicago, the updated users’ table would contain the following updated record for the user:

{id: 15, city: ‘Chicago’}

But, if History Tables are enabled, a new users_history table would be generated by with the following record:

{id: 15, city: ‘San Francisco’, start_time: ‘…’, end_time: ‘…’ }

This History Table contains the exact same fields as the original table, except that it also maintains the timeframe (start and end dates) during which this record has been active in the original table. The records in this table are never modified, but only inserted as changes takes place.