Automatically sync formula field values to Salesforce

Learn how to use Fivetran’s transformation package for dbt Core* to automatically generate views in your destination of fresh, reliable Formula Field values from Salesforce.
March 30, 2022

Customer relationships are the heart of every sales organization, and Salesforce is the world’s leading customer relationship management (CRM) platform, with 150,000 companies relying on it to keep track of sales and customer information. One of the reasons Salesforce suits the needs of so many different organizations is its flexibility — but with that flexibility comes challenges.

Salesforce formula fields: useful but hard to sync

Salesforce comes equipped to organize and store thousands of objects for sales teams. Quite often, users have data needs beyond the standard objects and fields in Salesforce. In these cases, the Salesforce administrator will create custom fields. When additional fields are defined by custom logic, they are referred to as formula fields, lookup fields or calculated fields.

Say you have an Opportunity object with a native monthly recurring revenue (MRR) field. For convenience, you might want an annual recurring revenue (ARR) field as well. Instead of adding an ARR field to be updated independently, your Salesforce administrator will create a calculated field that automatically populates based on underlying data, in this case from the MRR field. This ARR field might be defined by MRR * 12.

Formula fields are an excellent solution for Salesforce users who need to regularly customize their interface. For data analysts, however, the raw data coming from Salesforce is most important, as they rely on their ability to sync the raw data to their warehouse so they can analyze it alongside data from their other sources. This is where custom fields can cause issues for data professionals.

Secondary values, inaccurate timestamps

Technically, formula fields represent derived data. The Salesforce API doesn’t treat these values the same way as it treats primary data. The main difference is when the value of a formula field changes, Salesforce does not update the object’s primary modified_at timestamp. Due to this silent update, Fivetran cannot reliably detect updates and changes to formula field values in a timely manner.

Fivetran incremental syncs rely on our ability to check whether each record’s modified_at value has been updated, in order to decide whether to sync new data for that record. By default, Fivetran does not include formula field data in Salesforce syncs due to API limitations and the inability to guarantee the integrity of the data.

Formula fields as views: accurate values, minimal maintenance

Fivetran has developed a solution that solves the issue of missing Formula Field data without the need to coordinate with your Salesforce administrator and manually translate your formulas from Salesforce SQL (SOQL) to Standard SQL.

Our new feature, formula fields as views, provides accurate representations of formula field values with minimal ongoing maintenance. The solution works in two parts: the Fivetran_formula table and and our new transformation solution, Salesforce formula utils.

The Fivetran_formula table

Fivetran has developed a new system table within the Salesforce schema called Fivetran_formula. This new table automatically pulls custom formulas that have been enabled by the Salesforce administrator and displays the relevant object name, field name, SOQL formula, and the equivalent SQL formula. The translation from SOQL to SQL is handled by Fivetran with no need to coordinate with your Salesforce administrator. And, this table is completely free and won’t accrue any MAR. 

Fivetran automatically maintains your Salesforce formulas for you! Now, when your Salesforce administrator adds or edits a custom field formula, the new formula and its translated SQL formula will automatically be populated in your Fivetran_formula table. The SQL translations generated by Fivetran are written using a combination of Standard SQL and destination-specific SQL common to Snowflake, BigQuery and Redshift.

The Salesforce formula Utils transformations

Working in tandem with the Fivetran_formula table is our new transformation package: Salesforce Formula Utils. This package uses the Standard SQL formulas provided by the Fivetran_formula table to create and display your custom field values as views in your warehouse. Analysts can then access, utilize and trust that the Formula Field data in their warehouse is accurate and up to date.

How to get started

Getting started requires a bit of configuration, but everything you need to get going is right here.

Syncing Salesforce

If you’re currently using the Fivetran Salesforce connector, you should already have the Fivetran_formula table available to you via your Salesforce schema. The table is completely free and doesn’t incur any additional MAR. 

To begin syncing your Salesforce data with Fivetran, visit our Salesforce connector page and start your free 14-day trial.

Generating views in your destination

dbt (data build tool) is an open source tool used for data modeling. If you have not used dbt before, we recommend spending some time learning about the tool

When you’re ready, you can download and install the Fivetran transformation package to get started. See the solution in action by watching our demo!

For more information, visit our Salesforce documentation.

*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.

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.