Update with History Retention Mode

Pipelines in this mode operate the same way as Update mode pipelines, with the exception that all versions of the records, starting from when the pipeline was created, are retained in the destination table. This is useful when it is desirable to know the state of a record at a certain point in the past.

Etleap appends a column called end_date to destination tables to track record versions. The current version of the record is the one with a null value in the end_date column. The history of deleted records is also preserved, and all versions of a deleted record will have a non-null end_date value.

Example: Salesforce Opportunity History

Assume we are using Salesforce as our CRM and we are looking at the Opportunity table’s stage column, which reflects the stage of a company in our sales pipeline. The destination table of an update-mode pipeline will show us the current stage of the Opportunity.

However, we are interested in how long an Opportunity spent in each of these stages before getting to the final stage (won/lost). Creating a pipeline in Update with History Retention mode makes this information available to us because the destination table will contain a record for each update to the Opportunity record in the source.

Missed Updates

A missed update is a record version that is not present in a history-retaining destination table. This happens for sources where Etleap polls the source for record changes, and a record is modified twice (or more) in the source between two successive batches. In this case, only the last of the two (or more) updates will be reflected in the destination table.

Sources that expose the complete record of changes, such as CDC-enabled databases do not have this issue of missed updates.

Script Changes

Since the data retained for the pipeline may not be compatible with certain transformations, many kinds of changes in the Wrangler are not currently supported. Only changes that rename or drop columns are currently permitted.

To work around this limitation, we suggest using a model that uses SQL to transform the data that is in your destination.

Schema Changes

Etleap will apply column additions or drops that are detected in the source.