• What Are Temporal Tables?
• Formula Fields with Large Tables/Result Sets
• Best Practices When Using Temporal Tables
  • Syntax
  • Example

What Are Temporal Tables?

Temporal tables are system-versioned tables designed to keep a full history of data changes and allow easy point in time analysis. DBAmp provides functionality to make local copies of Salesforce objects in SQL Server. You can use SF_Mirror to make temporal tables of Salesforce objects or with a subset of columns (equivalent to SF_BulkSOQL).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

Before using DBAmp to make local copies of Salesforce objects as temporal tables, consider the following:

• SQL Server 2016 or later is required.

• The local copy made on a Salesforce object contains two local tables: the current table and temporal table. The current table name in SQL Server is also the name of the Salesforce object (e.g., Account). The temporal table name in SQL Server is the name of the Salesforce object with _Temporal appended (e.g., Account_Temporal).

• Once a local copy of a Salesforce object is made as a temporal table, you must use the SF_Mirror temporal option on that object moving forward. There must be no mixing of temporal, IAD, or regular options/stored procedures on an object.

• We recommend creating the local copies as temporal tables in a separate database as copies being made using the IAD or regular options or stored procedures.

• Do not make schema changes directly to the current or temporal tables locally. Schema changes made on Salesforce are allowed.

• Any deleted fields on Salesforce are not removed from the local copy. The field remains in the current and temporal table locally.

• The Salesforce object must contain a SystemModstamp field.

• Query the current table for the current reflected data on Salesforce.com. For example:

     SELECT ID, Name, AnnualRevenue FROM Account
  

• Query the temporal table for a full history of data changes made to the object on Salesforce.com. For example:

     SELECT * FROM Account FOR SYSTEM\TIME Between '2018-01-31 17:44:04' and '2019-01-31 17:44:04'
  

• You must include the ID and SystemModstamp columns in the SOQL statement.

For more information about SF_Mirror when using the temporal options, see SF_Mirror.

Formula Fields with Large Tables/Result Sets

Whether formula fields exist in the object or result set being replicated locally, using SF_Mirror with the temporal option determines what DBAmp does with the current and temporal table:

• If a formula field exists in the object or the result set being replicated locally, DBAmp will add all rows in the current table to the temporal table. This means, if the local object or result set contains 5,000 records, if a formula field is found DBAmp adds all 5,000 records to the temporal table every time the stored procedure is ran. NOTE that this can cause the temporal table to grow at a very high rate.

• If a formula field does not exist in the object or result set, DBAmp only adds the rows that were updated/changed to the temporal table. For example, if five records were changed on Salesforce, only those five records are added to the temporal table during the next run.

Best Practices When Using Temporal Tables

• Temporal tables take up large amounts of storage. Make sure there is enough storage space in the database to handle this.

• Use SF_Mirror with a subset of columns to only pull the fields or data needed locally instead of specifying a Salesforce object, which pulls all the fields and data in an object.

Syntax

 EXEC SF_Mirror '𝘚𝘈𝘓𝘌𝘚𝘍𝘖𝘙𝘊𝘌_𝘓𝘚','𝘰𝘣𝘫𝘦𝘤𝘵_𝘯𝘢𝘮𝘦','options'

where SALESFORCE_LS is your linked server name and object_name is the object name. There are several options you can include, but temporal is required.

Example

The following example creates a mirrored local Account table with the current data on Salesforce.com using the SALESFORCE linked server.

 EXEC SF_Mirror '𝘚𝘈𝘓𝘌𝘚𝘍𝘖𝘙𝘊𝘌_𝘓𝘚', 'Account', 'temporal'