Skip to main content
Last updated on

Ingest Salesforce formula fields incrementally

Beta

This feature is in Beta.

By default, Lakeflow Connect doesn't ingest Salesforce formula fields incrementally. Instead, it takes a snapshot of formula fields on each pipeline run, then joins them with the rest of the table. However, you can enable incremental formula field ingestion, which often significantly improves performance and reduces costs.

Why formula fields are ingested differently​

Salesforce computes formula fields dynamically at read time rather than storing them as static values. A formula such as one that uses TODAY() or NOW() can produce a different result on every read, even when nothing about the underlying record has changed.

Salesforce cursor columns (like SystemModstamp or LastModifiedDate) track changes to the record itself, not changes to formula outputs. When a formula result changes, Salesforce doesn't advance the cursor columns. Because incremental ingestion relies on those cursor columns to detect changed rows, formula fields can't be ingested incrementally the same way ordinary fields are: a formula output can change without any cursor column advancing, so a standard incremental read would silently miss it.

To handle this, Lakeflow Connect separates the two kinds of fields and uses a different refresh strategy for each, described in the following section.

How incremental formula field ingestion works​

If you use the default snapshot approach, the end result of a Salesforce ingestion pipeline is a series of streaming tables (one per source table), and formula fields are re-snapshotted and joined on each run.

If you use incremental formula field ingestion instead, the connector splits each table into two parts:

  • Non-formula fields are ingested incrementally into an intermediary streaming table. The connector applies only the rows that actually changed in Salesforce, using the cursor columns to detect changes (the standard incremental change-data-capture behavior).
  • Formula fields are recreated as a materialized view that recomputes the formula logic on top of the intermediary streaming table. Because formula outputs can change without a cursor signal, this design keeps formula fields separate from cursor-based incremental ingestion.

The end result is a single materialized view per table that joins the incrementally maintained non-formula fields with the recomputed formula fields. This avoids re-snapshotting the entire table on every run.

note

The intermediary streaming table that holds non-formula fields is named <destination-table>_without_formula_fields. Because non-formula fields use cursor-based change detection, this table records a change for a row only when that row's non-formula data actually changes in Salesforce. The final materialized view can still show updated formula values on a run when the intermediary streaming table recorded no change for that row, because formula fields are recomputed independently of the cursor columns. This is expected: an updated formula value in the materialized view does not mean the row's non-formula data changed.

When a non-formula field is deleted in Salesforce, the corresponding column in the destination is marked as inactive but is not deleted. If it reappears in the source, the pipeline fails with a duplicate column error. In contrast, when a formula field is deleted, it's removed from the final materialized view. If the field later reappears in the source, it's ingested into the materialized view with the latest data.

When you create a formula that returns a number in Salesforce, you can specify the scale (the number of digits allowed after the decimal). Salesforce automatically rounds the formula's result to the specified number of decimal places. To avoid cumulative rounding errors, the Databricks connector doesn't reproduce this behavior.

Limitations​

The limitations in this section apply when you create an ingestion pipeline with incremental formula fields.

General limitations​

  • You cannot enable this feature on an existing pipeline. You must create a new pipeline.
  • This feature is API-only. You cannot configure it using the Databricks UI.
  • The final materialized view does not support SCD type 2. However, you can view the history of non-formula fields in the intermediary streaming table.

Supported global variables​

The following Salesforce global variables are supported:

Global variable

Required tables

$User

User

$Organization

Organization

$Profile

User, Profile

$UserRole

User, UserRole

$System.OriginDateTime

$API

$RecordType

RecordType

$Label

$Setup

Referenced custom setting object

$CustomMetadata

Referenced custom metadata type (__mdt) object

$Permission

Global variable

Required tables

$User

User

$Organization

Organization

$Profile

User, Profile

$UserRole

User, UserRole

$System.OriginDateTime

$API

$RecordType

RecordType

$Label

$Setup

Referenced custom setting object

$CustomMetadata

Referenced custom metadata type (__mdt) object

$Permission

Some of these global variables are dependent on data in other tables for their computation. These dependent tables must also be ingested in the same pipeline.

note

The values for $User, $Profile, $UserRole, $Label, $Permission, and $Setup are dynamically generated using the connecting user's details, because they are user-dependent.

Custom labels and Translation Workbench​

When using the $Label global variable in formulas, the Translation Workbench feature must be enabled in your Salesforce org to access all translated labels. If Translation Workbench is not enabled, only base language labels are ingested.

To enable and configure it:

  1. Open Setup by selecting the gear icon.
  2. Type Translation Language Settings in the Quick Find box.
  3. Select Translation Language Settings and click Enable.

Supported rollup summary fields​

The translation of the following standard roll-up summary fields is supported:

Object

Standard field

Child object

Quote

LineItemCount

QuoteLineItem

Quote

Subtotal

QuoteLineItem

Quote

TotalPrice

QuoteLineItem

WorkOrder

LineItemCount

WorkOrderLineItem

WorkOrder

Subtotal

WorkOrderLineItem

WorkOrder

TotalPrice

WorkOrderLineItem

DuplicateRecordSet

RecordCount

DuplicateRecordItem

ProfileSkill

UserCount

ProfileSkillUser

ProfileSkillUser

EndorsementCount

ProfileSkillEndorsement

Object

Standard field

Child object

Quote

LineItemCount

QuoteLineItem

Quote

Subtotal

QuoteLineItem

Quote

TotalPrice

QuoteLineItem

WorkOrder

LineItemCount

WorkOrderLineItem

WorkOrder

Subtotal

WorkOrderLineItem

WorkOrder

TotalPrice

WorkOrderLineItem

DuplicateRecordSet

RecordCount

DuplicateRecordItem

ProfileSkill

UserCount

ProfileSkillUser

ProfileSkillUser

EndorsementCount

ProfileSkillEndorsement

For the computation of the above standard rollup summary fields, the respective child object must be ingested in the same pipeline.

Beyond these standard fields, custom rollup summary fields on any Salesforce object are also supported. As with standard fields, the summarized child object must be ingested in the same pipeline.

Rollup summary field limitations​

  • Rollup summary fields are computed independently of cursor-based change detection. Unlike non-formula fields, a parent record's rollup values can update without the parent record itself being marked as changed.
  • Rollup summary fields that filter on deleted child records are not supported.
  • Rollup summary fields whose filter includes unsupported formula functions are marked as errors in the error tracking table and not ingested incrementally.

Unsupported formula fields and functions​

important

If a formula field in one table references a column in another table, both tables must be ingested by the same pipeline.

For example, if table_A.column_X is a formula field that references table_B.column_Y, you must configure the pipeline to ingest both table_A and table_B.

  • Fields that reference tables or columns that aren't currently ingested in the pipeline are not ingested incrementally.

  • The following formula functions are not supported:

    • GETSESSIONID
    • RUNASUSER
    • CURRENCYRATE
    • ISCLONE
    • VLOOKUP
    • GETRECORDIDS
    • PARENTGROUPVAL
    • PREVGROUPVAL
    • PRIORVALUE
    • ISCHANGED
    • ISNEW
    • PREDICT
    • MLPREDICT
    • IMAGEPROXYURL
    • JUNCTIONIDLIST
    • LINKTO
    • LINKGROUPVAL
    • REQUIRESCRIPT
    • URLFOR
    • GEOLOCATION
    • DISTANCE
    • HTMLENCODE
    • JSENCODE
    • JSINHTMLENCODE
    • URLENCODE
    • INCLUDE

  • Global entities other than those listed in Supported global variables are not supported.

  • Formulas that use ROUND(arg1, arg2) are not supported unless arg2 is a static integer.

  • Formulas with outputs that exceed the precision decimal(38,18), such as a formula that multiplies two decimals of precision (30,18). These cause the pipeline to fail. To exclude them, use the exclude_columns configuration. See Select columns to ingest.

Functions with ambiguous results​

The following functions might yield different results in Databricks than in Salesforce:

Function

Behavior difference

LEN(TEXT(<decimal_number>)), such as LEN("123.45")

Because Databricks is more precise than Salesforce, this function returns a different value in Databricks than in Salesforce.

CHR(x)

In Salesforce, this function returns the character with the ASCII code specified by x. In Databricks, it returns the character whose ASCII code equals x modulo 256.

INITCAP("_mrSmith is _ok")

In Salesforce, this function capitalizes the first letter of every word, ignoring non-alphabetic characters, yielding "_MrSmith Is Ok". In Databricks, this function capitalizes the first character of every word only if it is a letter, yielding "_mrSmith Is _ok".

FORMAT_DURATION

In Salesforce, when two date-time inputs are exactly 30 minutes apart, the formula outputs 0:00:29:59 instead of the expected 0:00:30:00. This limitation doesn't exist in Databricks, so there might be a one-second gap between the source and destination values.

Function

Behavior difference

LEN(TEXT(<decimal_number>)), such as LEN("123.45")

Because Databricks is more precise than Salesforce, this function returns a different value in Databricks than in Salesforce.

CHR(x)

In Salesforce, this function returns the character with the ASCII code specified by x. In Databricks, it returns the character whose ASCII code equals x modulo 256.

INITCAP("_mrSmith is _ok")

In Salesforce, this function capitalizes the first letter of every word, ignoring non-alphabetic characters, yielding "_MrSmith Is Ok". In Databricks, this function capitalizes the first character of every word only if it is a letter, yielding "_mrSmith Is _ok".

FORMAT_DURATION

In Salesforce, when two date-time inputs are exactly 30 minutes apart, the formula outputs 0:00:29:59 instead of the expected 0:00:30:00. This limitation doesn't exist in Databricks, so there might be a one-second gap between the source and destination values.

Create a pipeline with incremental formula fields​

To enable incremental formula field ingestion, you must create a new pipeline with the configuration flag pipelines.enableSalesforceFormulaFieldsMVComputation: true. You cannot enable this feature on existing pipelines. For example code, see Ingest formula fields incrementally.

Understand the error tracking table​

When you enable incremental formula field ingestion, the connector creates an additional table to help with troubleshooting. This table always appears if the incremental formula fields flag is set, even if there are no errors. The error tracking table (named <pipeline-id>_formula_fields_error_reasons) has the following columns:

  • <source-object>: The name of the source object in Salesforce.
  • <formula-field>: The name of the formula field.
  • error: Error message that explains why the formula wasn't ingested incrementally.

If a formula field shows NULL values in your destination table, query the error tracking table to understand why the field wasn't ingested incrementally. For details, see Formula field values show as NULL (incremental ingestion).

On this page