Snapshot Table

A Snapshot Table is an additional table that supports the Historical Table. Its structure is closely related to the Current Value Table, with one significant difference. Whereas the Current Value Table has the most recent values, the Snapshot Table is going to have a series of snapshots that are created at specific time intervals.

As an example, we might have a batch job that calculates the beginning values of each day, and store those calculated values in this table. Now, if we want to calculate the values of a given point in time, we only have to start as far back as the beginning of the day in question, and then apply the transactions that occurred between that beginning of day values and the target time we are interested in.

Another feature that the snapshot table gives us is day to day auditing. If necessary, we can replay a given day, starting from one snapshot, apply all transactions from that day, and verify that it matches the next day's snapshot. Thus, if an errant or malicious record is introduced, we can quickly narrow down which day was impacted.

In the event that a historical table is edited, then it is possible that multiple snapshots may need to be updated as well. Much like the Historical table, the rows should not be deleted, but instead should be kept with new replacement rows inserted intot the table.

Table Columns

The columns in a Snapshot Table are essentially the same as the Current Value Table, with the addition of a date/time stamp to indicate when the snapshot was made, as well as the of the audit columns from the Historical Table.

SnapshotTime

Indicates the point in time the snapshot represents.

AccountID

This is a globally unique ID that associates the data with a specific account.

TransactionID

TransactionID serves the same purposes as listed in Current Value Table, plus additional purposes here. If the last transaction occurred well before the snapshot was made, this will lets us be aware of the fact.

InsertionTime

Represents when the record was actually placed into the table. This may differ from the SnapshotTime since the batch processing is not actually required to be run right at the time the Snapshot is supposed to represent. Furthermore, if the Snapshots have to be updated due to edits in the Historial Table, then this time will reflect when the updates were made to the Snapshot Table.

UpdateID

This field will contain the ID of who or what actually updated the data. If the customer was the person updating the info, then we would use the AccountID in this field. If an administrator or customer representative was the person updating the data, then that person's user ID would be used to indicate they were the one to make the changes. If an automated system updated the row, then an identifier specifying the exact system needs to be used. If a batch process updated the row, then a unique identifier specifying the exact batch process should be used.

UpdateReason

UpdateReason tells us why the row was updated. This information is extremely important since it will tell auditors why something was changed./p>

Status

Indicated the status of the transaction. Be default it would be a value indicating that the transaction was "good" or "valid". If the transaction was edited, then the status may be changed to something like "replaced", and the replacement transaction will have a status of "good".

Data

This is not a single field, but instead are all the fields that represent the results of all the transactions applied to an account. For example, if a Historical Table contained transactions that would be used to calculate three different monetary values, then this table should have field for each of those monetary values. If the data is such that multiple rows are needed, then only the minimum number of rows per account should be kept in the table.

Next: Daily Table