General 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:

Example where Entitlements with an EndDate after yesterday are joined onto the already stored data

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