Dedicated settings for snapshot tables

Agreed. We also use a regular history table with the snapshot value included in the primary key, however the performance for a large history table is awful (in general, not just with snapshots).

Your first point should be the case for all history tables. These must never ever be dropped / truncated under any circumstance.

Had the same issue. I made a snapshot project in TX which holds the settings for my snapshots such as snapshot period, delay, ... In the actual projects where the snapshots should be stored I have 1 history table and 1 table that holds the actual values.

Once the snapshot should be taken I push the contents of the actual table to the history table. So for monthly snapshots it only has to run the slow history comparison process once instead of each scheduled execution and it does not have to check for differences since you add a new snapshot period.

Using this method you avoid deletes and updates in the history table and to build the actual table you only use TX's truncate function so no deletes and huge loge files in the end.

Hello everyone,

Doug Wynkoop here with TimeXtender US Support.  One way to deal with snapshots is to do an insert into the valid table of a history table.  This solution can work if:

1) you need to do no lookups or transformations in your snapshot table, 

2) each insert is guaranteed to be unique, and 

3) the data does not need to be validated in the snapshot table 

Those conditions usually apply, because a snapshot is simply trying to capture every record on a given day as they exist with no further modification or validation.  The only reason you need history turned on at all so that the table will not be truncated, so inserting into the valid table bypasses the costly history algorithm altogether.

This is a good workaround in a simple case, but often requires that you use a custom table insert if things more complicated.  Often times, snapshots include aggregation, which won't work with a normal insert.  Table and related records inserts also don't support an explicit time stamp for the insert, although DW_TimeStamp can sometimes work for this.  It's also not uncommon to want to do some kind of left-join or "not exists" insert, which you can do with related records, but not if you want to also use aggregation or something fancier.

To summarize, there is a current workaround that's robust and performant in many cases, but it does require custom code. 

I hope that this helps in the meantime, but you are all right that this is a common requirement.  I personally think an automated solution for this would be really nice to have!

Best regards,

Doug Wynkoop

I think the workarounds here above really stresses the need for a dedicated "one way" feature for this common design scenario. I hope this will be added to the backlog and prioritized high.

It needs to be automated!

Best regards,
Andri Heidberg

Two thumbs up for adding this feature!

Another one here.

Snapshots are relatively common and it would make sense to have some functionality in there.

Please update us whether the snapshot functionality will be added?

Thumbs up for adding this feature!

Can we expect the "PLANNED" tag on this feature any time soon :)

I would also appreciate this functionality.

I agree in the above feature request - a snapshot feature will be very useful as a separate feature. I have received this request from partners/customers several times during the last years ...

Any expected implementation plans?

Any progress on this?

One thing that's lacking is to actually design the specification that a certain type of history is forbidden in a certain table. For example in a snapshot table (also in a type I table) you will want to prevent type 2 fields and set the default for new fields to type 0 or 1. (And in a type 2 table you will want to default new fields to type 2)

Without this, adding new fields will destroy the functionality.

Another thing that's not currently possible with a history table is snapshot updating. Real example from our DW: we have a daily snapshot table that we updated hourly. Data from yesterday is not supplied anymore so it would get TombStone=1. This is data that you still want to report on. Data from today that doesn't exist anymore will also get TombStone=1. This is data that you do not want to report on.

Currently we fix this by running a data cleaning pre-script that deletes the current snapshot from the valid table.