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.

Syncing Salesforce

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™, 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.

We have recently made downloading, orchestrating, and managing the use of this dbt model an all clicks process with the release of our Quickstart data models. This new approach to data transformations lets you turn your Salesforce Formula Fields data into analytics-ready tables with all clicks - no coding, dbt project, or 3rd party tools required. You can now easily transform Opportunities, Accounts and Leads into ARR, NRR, and New Business Bookings reports and calculate any custom KPIs held in your Formula Fields. You can see how easy this is to deploy in our blog here.

For companies who have their own dbt project, you can download and use the free dbt package here.

For a comprehensive view of how to get started - especially for those of you who have your own, or want to create your own, dbt project - you can find the steps here:

Syncing Salesforce

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. 

Cheat sheet: Salesforce connector

DOWNLOAD

When you’re ready, you can deploy our Quickstart data model or download and install our Salesforce formula utils data model. For more information, visit our Salesforce documentation.