10. FAQ > 10.10. Working with Databases > 10.10.5. Saving Historical Data

It happens very often that you need to see the content of a database “as if” you were at a specific date in the past. This type of request happens mostly…

•…when you are creating predictive models: i.e. You need to create a learning dataset “as if” you were at specific observation date in the past.

•…for compliance reasons.

This is very easy to do when your table (inside your database) contains the two columns named “RECORD_VALIDITY_FROM” and “RECORD_VALIDITY_TO”. Then, if you want to “see” a table “as-if” you were at a specific “Observation_Date”, you’d simply write:

SELECT * FROM <My_Table>
WHERE (RECORD_VALIDITY_FROM < Observation_Date) AND (Observation_Date< RECORD_VALIDITY_TO)

Unfortunately, most tables/databases do not possess such columns. In such (common) situation, to still be able to safeguard all the “history” of your database (to be able to “go back” in time), you’ll need to run everyday an Anatella graph that looks at the current content of your database and saves (inside different .gel_anatella files) all the changes made over the last day. More precisely, this Anatella graph needs to: For each Table T inside your database:

•Find inside the table T all the rows that changed.

•Add only the changed rows inside a <TableT.gel_anatella> file.

•Create (and thereafter, update) the columns “RECORD_VALIDITY_FROM” and “RECORD_VALIDITY_TO” inside the <TableT.gel_anatella> file.

•(optional): Create (and thereafter, update) the column “RECORD_CURRENT_FLAG” inside the <TableT.gel_anatella> file.

If you are lucky and you have a column named “LAST_UPDATE_TIME” inside your database (that contains the last time a row has been updated), then go to the next section 10.10.5.1. Otherwise, go to the section 10.10.5.2.