History table #

History table is used when use wants to work with Slowly Changing Dimension type 4 pattern. What it does mean?

SCD4 works with two table - current (original) and historical. Current table contains the most recent data, historical table contains all historical changes in the data. These changes are represented by records with validity, i.e., each record for the given key has two more attributes - Valid From and Valid To.

I.e., when data for the given key in the current table changes, ETLs should

• overwrite record in the current table by obtained data
• update Valid To attribute of the latest record in the historical table
• create a new record in the historical table with a proper Valid From attribute and open Valid To attribute

MetaCroc supports management of SCD4 by several features:

1. It allows to create a historical table as a copy of a current table (via checkbox Has historical table)
2. It keeps a historical table in sync with the current table
3. It creates an automatic mapping to the historical table

Historical table behaviour #

A historical table is created as a copy of the current table at the time when user checked Has historical table checkbox and its name is [Table Name] History. It has all settings, attributes, columns and links as the current table. User can switch to the historical table by button in table header

and back

User can overwrite some attributes by specific value. E.g., historical table can have different partitioning strategy, retention policy, etc. When user overwrites the attribute, it is not synchronized anymore. Attribute is marked as non-synchronized:

The attribute can be synchronized again by clicking on the cross.

Both original and historical table are sent to template together so template should handle both of them in a single output script.

Automatic historical mapping #

Automatic historical mapping is created together with the historical table. It does not have explicit column mappings because its script is determined only by structure. The structure is enough for template to create the mapping from current to historical table.