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?

We are working on turning this into a backlog item. It very easily becomes quite complicated if we need to support all use cases. I expect that we can build a feature that supports the basic snapshot scenario with either fixed schedule (specific dates) or recurring snapshots (eg. daily, weekly, monthly, yearly).

Can anybody explain what does the current history table logic lack so that it could be used as a snap-shot table?

The back-end logic of history tables have been rewritten recently. Performance of history tables (since version 19.2) is no longer an issue since history tables with default settings (all fields as Type I) will be as fast in both data transfer and data cleansing as the deprecated target-based incremental load, and faster than full load.

But if you set all fields as Type 0, it will be the fastest table available, because only rows with new primary key values will be allowed to the valid table. Any other change will simply be ignored. No comparison of existing rows will be performed. 

Do you think we can assume that history tables with all fields as Type 0 is good enough as a snap-shot table?

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.

Thanks for the explanation, Steven.

The main requirement for snapshot tables is that the table should never be truncated by TimeXtender (as is the case for a History table).  But you don’t need all the overhead of History tables (SCD Hash columns, IsCurrent, TombStone) - which is bad for performance.
So, just add an extra option which stops TX from truncating the table (even when deploying it).  The snashot functionality can be handled by a few pre / post scripts.

While there are many variants to snapshotting, an 80/20 implementation would really only need a periodicity parameter, a field storing the period id, and an option whether the current period's records should be updated on execution within the period.

I.e. a daily snapshot will start a new period after 23:59:59.999999 and any reloads within that day would update (or truncate + reload) that period's record if the update option is selected. 

There is actually functionality available natively in SQL Server starting with SQL 2016 that covers this use case: Temporal Tables apart from some indexes that need to be added to deal with larger snapshot history (see: here ) , this seems a pretty promising possibility.

We set up snapshot tables using the History function (just to make sure the tables are never deleted during deploy).  But indeed, history is very slow because of the extra functionality - not needed for snapshot tables.

It would be great if TX implements a ‘snapshot’ function which covers :

• the table never being truncated by TimeXtender (deploy) (similar to history but without all the overhead to improve performance)
• add more options for table partitioning (day, week, month, ...) - which make it easier to truncate the period that needs to be replaced. 

   

The remaining tasks for setting up a snapshot table can be handled by TimeXtender functionality which is already in place (e.g. Table inserts, non-physical Valid Table, scripts to remove ‘old’ periods,...)