Formula and Lookup Fields

Syncing formula fields or lookup fields can cause problems because formula fields don’t update when the formula changes, and lookup fields don’t update when the target object changes. Initially, when you create a Salesforce connector, all formula fields in objects are excluded from being synced. However, you can review the connector schema later and select the formula fields you want to sync. Lookup fields are selected to be synced by default.

Roll-up summary fields are similar to formula fields and have the same behavior as formula fields.

Formula fields can be derived from both custom and standard fields. If formula fields are enabled we sync the fields along with the objects. This may cause data integrity issues as explained in the example below.

We use system table FIVETRAN_FORMULA to store data related to formula fields. This table contains the object name, formula field name, and formula that is used to compute the resulting data. Salesforce users can change formulas. To keep track of these changes, we sync another system table, FIVETRAN_FORMULA_HISTORY. Using the FIVETRAN_FORMULA_HISTORY table, we can capture a history of formula field changes as they occur, starting from the date you first connected your Salesforce connector to Fivetran.

IMPORTANT: You can use Fivetran’s pre-built, dbt Core*-compatible Salesforce Formula Utils data model to map formula fields to existing tables. Learn more in our Salesforce Formula Utils model documentation.

Suppose our Leads object looks like this:

Suppose we then add a formula field IsGreat = CONTAINS(Name, 'Great'), and a lookup field AccountName = SELECT Name FROM Account WHERE Id = Lead.AccountId:

Suppose we sync this object, including the formula and lookup fields, into our data warehouse. This is a bad idea for two reasons:

1. Formula fields don’t update when the formula changes.
2. Lookup fields don’t update when the target object changes.

Formula fieldslink

Suppose someone changes the formula field in Salesforce to IsGreat = CONTAINS(Name, 'Amazing') and adds a new lead:

Changing the Salesforce formula field definition within Salesforce doesn’t update the object’s SystemModStamp. Therefore, it’s impossible to know that L1 from this example should now be updated to IsGreat = FALSE. Consequently, our data warehouse now contains a mixture of the old definition of IsGreat and the new definition. A better solution is to write your SQL queries to work off the underlying values and keep the formula fields out of your data warehouse, for example:

SELECT name LIKE '%Great%' AS is_great FROM lead.

Salesforce explains this behavior in their documentation:

…a change to a formula field will not trigger a sync. This is because a formula field is a way of arriving at a value, but does not actually store the value and will not update the last modified timestamp on a record when it changes.

Lookup fieldslink

Lookup fields are even more problematic. Suppose we change the account name from FooCorp to Foo Corp Private Limited. This will change Account.LastModified but not Lead.LastModified, so the value in your data warehouse will be wrong:

It’s better to simply do a join in your SQL query, for example:

SELECT *, account.name AS account_name FROM lead JOIN account ON lead.account_id = account.id

In this case, anyone reading the query can see where account_name comes from so there is no confusion.

* dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.