SCD2 stands for Slowly Changing Dimension Type 2, which is a way of storing historical values for query or audit purposes. SnapLogic® AutoSync supports SCD2 when loading data for sources that can provide a last updated timestamp. If you enable synchronizaton when creating the pipeline, by default AutoSync does a full load followed by incremental loads (upserts). To use SCD2, edit the data pipeline and change the load type.
To provide SCD2, AutoSync changes the destination schema to add columns that track whether a row contains current or historical value.
Some Salesforce objects are only created, such as
These objects do not have a field that tracks updates and should not be used with the SCD2 load type.
You can change a data pipeline using full or incremental load to SCD2. However, use care in the following situations:
- If you need to change a data pipeline from using SCD2 back to using incremental loads, you should first drop the target tables in the destination. Failing to drop tables synchronized with SCD2 before changing the load type can result in errors.
- We do not recommend setting up multiple data pipelines that load the a particular table or object to the same schema in the target. However, if two pipelines load the same table to the same schema and one is set to use incremental loads and the other to use SCD2, the incremental load will fail.
AutoSync supports the SCD2 load type for the source to destination combinations listed in the following table.
|Source||Amazon Redshift||Google BigQuery||Snowflake|
|Microsoft Dynamics 365 for Sales|
Schema for SCD2
AutoSync tracks changes for SCD2 based on a last updated timestamp. AutoSync updates the values in special columns to indicate whether a row contains the most recent or historical values. For example, when using SCD2 for Salesforce to Snowflake, AutoSync adds following columns to the target Snowflake table.
||Contains the value of the Salesforce object
||The most recent record has a value of
||The start timestamp that records when the record was added to the table.|
||The end timestamp for the record.
Current records have an end timestamp of
||The hashed value of the record. AutoSync uses this to detect changes.|
How AutoSync loads using SCD2
To populate an empty table, AutoSync inserts all rows with a value of
Y in the
Changes to any field in the source, including the
id, cause AutoSync to:
- For the existing record:
- Change the
AUTOSYNC_CURRENTRECORDFLAGfield value to
- Change the
AUTOSYNC_EFFECTIVEENDTIMEfield value to the current timestamp.
- Change the
- For the updated record:
- Insert a new row.
- Add a value of
- Add a value of 4999-12-31 00:00:0 in the