You no longer need to coordinate with your administrator and manually translate formulas from Salesforce SQL to Standard SQL.
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 come challenges.
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.
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. Historically, Fivetran has intentionally not synced Formula Fields values, because we were unable to guarantee the integrity of the data.
By default, Fivetran has not included Formula Field data in Salesforce syncs — until now. Fivetran has developed a solution that solves this issue 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 an all new dbt transformation package.
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, and from this initial release will gradually increase coverage toward 100% of Salesforce Formulas.
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 Standard SQL functions that are common to Snowflake, BigQuery and Redshift.
Working in tandem with the Fivetran_formula table is our new dbt package: Formula Utils. This package uses the Standard SQL formulas provided by the Fivetran_formula table to create and display your custom field values in your warehouse. Analysts can then access, utilize and trust the Formula Field data in their warehouse without having to coordinate with their Salesforce administrator.
Getting started requires a bit of configuration, but 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 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.
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 dbt package to get started. See the dbt package in action by watching our step-by-step video demo, and follow along in our dbt quick-start guide.
For more information, visit our Salesforce documentation.