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 replicate
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.
Derivative 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 incremental syncs.
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. Fivetran handles this Salesforce API limitation automatically, by detecting which fields are formula fields and excluding them from the sync 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_model table and our new transformation solution, Salesforce formula utils.
The Fivetran_formula_model table
Fivetran has developed a new system table within the Salesforce schema called Fivetran_formula_model. This new table automatically pulls custom formulas that have been enabled by the Salesforce administrator and displays the relevant object name, alongside a full SELECT statement for each object synced. The detection of custom fields and the generation of the SELECT statement 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 will automatically be translated to SQL and populated in your Fivetran_formula_model 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 data models
Working in tandem with the Fivetran_formula_model table is our transformation solution: Salesforce formula utils. This package uses the SELECT SQL formulas provided by the Fivetran_formula_model 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 just a bit of configuration, and everything you need to get going is right here.
If you’re currently using the Fivetran Salesforce connector, you should already have the Fivetran_formula_model 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
Fivetran utilizes dbt Core, an industry-leading, open-source transformation tool, to bring you completely free, Fivetran-managed data models that enrich the data synced from Fivetran connectors to jumpstart your analysis. Fivetran's formula models are ready for use to create views in your destination. By using a tool like dbt Core, you can easily organize and automate the creation of these views as well as ensure that you're always using the latest model definitions synced by Fivetran. 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 our Salesforce formula utils data models to get started. 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.