Formula Fields

This feature is currently in private preview

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 the following privilege enabled: