Handling Formula Fields

Formula fields, also known as derived fields or calculated fields, in Salesforce generate their values dynamically based on other fields, functions, or expressions. For example, the custom field Expected_Revenue__c may be defined as Amount * Probability. Every time this field is accessed, the value is recalculated in real time instead of being stored in the schema.

In Salesforce, whenever a record is updated in an object, a timestamp field, such as SystemModStamp, is also updated. Hevo uses this field to identify Events for incremental ingestion. However, changes to a formula field or any of its referenced fields do not trigger a timestamp update. As a result, objects that contain these fields may not be included in the incremental load. To ensure your Destination always reflects the latest formula values, Hevo identifies and processes these fields differently. During each run, Hevo performs the following actions:

1. Extracts the formula definitions from Salesforce,

2. Translates them into SQL queries compatible with your Destination,

3. Creates views within your Destination that dynamically recalculate these formulas.

This ensures that your data in your Destination stays accurate and consistent with Salesforce.

Note:

• Formula field replication is currently supported only for Snowflake and BigQuery Destinations.

• To enable formula field replication, set your Pipeline’s load mode to Merge. Formula fields are not supported for Pipelines using the Append load mode.

Handling Formula Fields

To support formula field replication, Hevo provides the following set of system tables to store formula definitions, translated SQL logic, and any errors that occur during translation.

You must select these tables during Pipeline creation. Once selected, Hevo automatically handles their creation, population, and periodic refresh in your Destination. After selection, these tables are created in your original schema during the historical load and populated after the first incremental run. Each subsequent Pipeline run replaces the data in these tables to ensure the stored formula definitions and SQL logic always reflect the accurate and latest data in Salesforce.

Formula Views in the Destination

After the system tables are created, Hevo triggers a transformation process that generates the final views, combining the original object data with recalculated formula field values.

To keep original data and recalculated values separate, Hevo creates a schema in your Destination with the suffix _QUICKSTART. For example, if your original schema is SALES, the new schema will be SALES_QUICKSTART. The _QUICKSTART schema contains views for all selected objects, including those without formula fields. This allows you to query all your Salesforce data from a single schema without switching between SALES and SALES_QUICKSTART schemas.

For each Salesforce object, Hevo creates a view in this new schema with the same object name. For example, if your original table is SALES.REVENUE, the corresponding formula view will be SALES_QUICKSTART.REVENUE.

These views are created and refreshed after each incremental run. You can query them just like regular tables. The following query returns all the standard columns from the original Revenue table, along with the recalculated Expected_Revenue__c formula field.

SELECT revenue_id, amount, probability, expected_revenue__c FROM sales_quickstart.revenue;

Note: The formula views are maintained by Hevo. You cannot edit, schedule, or customize the transformations used to generate them.

Supported Formula Fields

Hevo supports the following Salesforce formula field types and functions:

Hevo currently does not support replication for formulas containing the following functions:

• DISTANCE

• GEOLOCATION

• GETRECORDIDS

• GETSESSIONID

• HTMLENCODE

• IMAGE

• IMAGEPROXYURL

• INCLUDE

• ISCHANGED

• ISCLONE

• ISNEW

• JSENCODE

• JSINHTMLENCODE

• JUNCTIONIDLIST

• LINKTO

• PARENTGROUPVAL

• PREVGROUPVAL

• PREDICT

• PRIORVALUE

• REGEX

• REQUIRESCRIPT

• URLENCODE

• URLFOR

• VLOOKUP

If a formula uses unsupported functions, operators, or references data that is not synced, Hevo logs the failure in the hevo_formula_failure_reason table with an appropriate failure code.

Failure Handling

If a formula field cannot be translated, the column appears in the _QUICKSTART schema view with NULL values. Hevo logs the details of the failure in the hevo_formula_failure_reason table.

Note: Pipelines continue running even if one or more formulas fail to translate. All successfully translated formulas and other data remain unaffected.

See Also

• Calculate Field Values with Formulas

Revision History