Formula Fields

Formula fields are a pragmatic solution for the common problem of keeping calculated values in sync in Snowflake.

Omnata has created a comprehensive library of tests cases to check that our Snowflake expressions match the value of the formula in Salesforce, however it's not possible for us to guarantee that it will always be correct.

For this reason, we strongly recommend checking that important formulas do match your expected values.

Background

Many Salesforce customers define Custom Fields that are Formula fields. These are expressions that can include:

• References to other fields, both from the immediate object and from other objects via relationships

• Various operators and functions to modify data

• Functions that are temporal in nature (e.g. TIMENOW(), TODAY())

Since these formula fields often contain business logic, it can be very useful to have them available in Snowflake.

The challenge for ETL products is that:

1. These are evaluated at a point in time, which means their values quickly become outdated

2. A change to the evaluated value of a formula does not result in the system modified timestamp of the record, which means they cannot be maintained via incremental export

3. They often reference other objects, which means they are not simply scalar functions

4. The formula itself can be edited by Salesforce users, which must be reflected in Snowflake as soon as possible.

As you may already know, Omnata provides two objects for querying data from each inbound stream:

• A raw table which contains all field values in a single object column

• A normalized view which dynamically extracts values from the raw table into their own column and performs conversion to native Snowflake types

Omnata's Approach

Support for Salesforce functions is enabled via a sync parameter.

When enabled, Omnata will inspect the formula field definitions and transpile them into an equivalent Snowflake expression. This expression will be used in the normalized view to calculate the current value of the formula.

This solves challenges 1 and 2, bearing in mind that the calculated values are only as fresh as the data in the fields they reference.

If a formula field references another Salesforce object, then that object must also be included in the sync in order for the formula field to be available. The normalized view will automatically join to the corresponding local table in order to calculate the value, solving challenge 3.

When you enable Salesforce function support, all streams will automatically become dependent on the CustomField stream. The CustomField stream syncs all custom field metadata into Snowflake, so that you can see a full list of all custom fields that have been created in Salesforce.

Because the CustomField stream syncs incrementally, the plugin is able to see which objects in Salesforce have had formula field changes, and notify the sync engine of the need to recalculate the normalized view accordingly. This avoids bulk re-creation of

Example

Imagine the Contact object has two custom formula fields:

Name	Formula Value	Description	How to resolve
AccountCreatorEmail__c	Account.CreatedBy.Email	The email address of the user who created the account that this contact belongs to	Joins to an Account object via the "Account" relationship, then joins to a User object via the "CreatedBy" relationship, then returns the email address of that user
Account_Calc__c	`NumericField__c + Account.NumericField2__c	Adds together NumericField__c and NumericField2__c, which both belong to the Account that this contact belongs to	Joins to an Account object via the "Account" relationship, then adds together the values NumericField__c and Account.NumericField2__c

To implement this, in the normalized view for Contact, we have to join the Account table as well as the User table (via Account).

The final query for the normalized view looks something like this:=

with "Contact" as ( 
    select APP_IDENTIFIER as "OMNATA_APP_IDENTIFIER", 
        RETRIEVE_DATE as "OMNATA_RETRIEVE_DATE", 
        RECORD_DATA as "OMNATA_RAW_RECORD", 
        IS_DELETED as "OMNATA_IS_DELETED", 
        RUN_ID as "OMNATA_RUN_ID", 
        RECORD_DATA:"AccountId"::VARCHAR as "AccountId", 
        RECORD_DATA:"CreatedById"::VARCHAR as "CreatedById", -- other columns...
    from "OMNATA_SYNC_ENGINE"."INBOUND_RAW"."formula-field-testing-to-snowflake_Contact"
), 
"Account" as (
    select APP_IDENTIFIER as "OMNATA_APP_IDENTIFIER",
        RETRIEVE_DATE as "OMNATA_RETRIEVE_DATE",
        RECORD_DATA as "OMNATA_RAW_RECORD",
        IS_DELETED as "OMNATA_IS_DELETED",
        RUN_ID as "OMNATA_RUN_ID",
        RECORD_DATA:"AccountNumber"::VARCHAR as "AccountNumber",
        IFF(RECORD_DATA:"NumericField__c"='',null, RECORD_DATA:"NumericField__c")::NUMERIC(38,19) as "NumericField__c",
        IFF(RECORD_DATA:"NumericField2__c"='', null,RECORD_DATA:"NumericField2__c")::NUMERIC(38,19) as "NumericField2__c",
        RECORD_DATA:"OwnerId"::VARCHAR as "OwnerId", -- other columns...
    from "OMNATA_SYNC_ENGINE"."INBOUND_RAW"."formula-field-testing-to-snowflake_Account"
),
"User" as (
    select APP_IDENTIFIER as "OMNATA_APP_IDENTIFIER",
        RETRIEVE_DATE as "OMNATA_RETRIEVE_DATE",
        RECORD_DATA as "OMNATA_RAW_RECORD",
        IS_DELETED as "OMNATA_IS_DELETED",
        RUN_ID as "OMNATA_RUN_ID",
        RECORD_DATA:"Email"::VARCHAR as "Email",
        RECORD_DATA:"Username"::VARCHAR as "Username", -- other columns...
from "OMNATA_SYNC_ENGINE"."INBOUND_RAW"."formula-field-testing-to-snowflake_User" )
select "Contact".*, 
    "Account.CreatedBy"."Email" as "AccountCreatorEmail__c",
    ("Account"."NumericField__c" + "Account"."NumericField2__c") as "Account_Calc__c"
from "Contact"
JOIN "Account" as "Account" ON "Contact"."AccountId" = "Account"."Id"
JOIN "User" as "Account.CreatedBy" ON "Contact"."CreatedById" = "Account.CreatedBy"."Id"

As you can see, we use CTEs to bring in any of the referenced objects, and join to them from the main object by following their Salesforce relationships. Note that an object may be referenced multiple times via different relationships that join on different fields.

In the final query, we use various Snowflake expressions to implement the equivalent formula logic.

Requirements

In order to retrieve formula field definitions in bulk, we must use the Metadata API. This means that the Salesforce profile for the configured user must have this privilege enabled.

Other Considerations

Derived fields

There are some fields within Salesforce known as Derived Fields that are neither regular fields nor formula fields. In other words:

1. The modified date of the record doesn't change when the value changes, therefore incremental syncs won't detect the change

2. There is no defined formula, so we cannot reconstruct the calculation in Snowflake

Examples:

• 'Status' field on the Entitlement object

• 'isViolated' field on the Milestone object

Sometimes the change in value will propagate to Snowflake, but this only occurs if some other field was edited in order to update the modified date of the record, at which point the new derived field value is fetched at the same time.

In order to have updated versions of these values, you must either:

1. Perform a full refresh of the object

2. Create your own formula field which implements the same logic as the derived field, and enable formula field translation

3. Implement the field value yourself in Snowflake as an express, downstream of the inbound table

4. Implement a hybrid approach, which is useful if you know which records will have changed, but you aren't sure of the complete logic of the field. To do this, run a live SOQL Query and join the results to your existing table in Snowflake:\

select stored_values."Id","Name","Status", "CurrentStatus" 
from OMNATA_SYNC_ENGINE.INBOUND_NORMALIZED."my-salesforce-connection-to-snowflake_main_Entitlement" as stored_values
left outer join (
    select RECORD:"Id" as "Id", RECORD:"Status"::varchar as "CurrentStatus"
    from table(OMNATA_SALESFORCE_PLUGIN.UDFS.SOQL_QUERY(
    CONNECTION_SLUG=>'my-salesforce-connection',
    QUERY=>$$select Id,Status from Entitlement where EndDate > $$||(current_date()-1),
    USE_BULK_API=>false))
) live_values on stored_values."Id" = live_values."Id";

The hybrid approach takes longer to query and consumes API quota on your Salesforce environment, so it's not recommended to provide a view like this to a general audience of Snowflake users