SQL Can't Handel Null

[jmcdonald-moveworks]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

For fields involving numeric values the translation from Salesforce formula to SQL isn't exactly correct.

I have a salesforce field named [Growth ARR] with the following salesforce formula: \ ARR__c - Renewal_Baseline_ARR__c

This package translates this simple formula to: arr_c - renewal_baseline_arr_c

Salesforce formulas have some sort of if null handling that allows 100k - null to be equal to 100k. In SQL 100k - null = null, which doesn't match salesforce. It would be better for the formula translation to include a coalesce. Here is my "correct" translation: coalesce(arr_c, 0) - coalesce(renewal_baseline_arr_c, 0)

Relevant error log or model output

No response

Expected behavior

numeric value - null = numeric value

dbt Project configurations

name: "gtm_analytics"

config-version: 2 version: 1.1.0

Package versions

packages:

• package: dbt-labs/dbt_utils version: 0.8.4
• package: fivetran/salesforce_formula_utils version: 0.7.0

What database are you using dbt with?

snowflake

dbt Version

1.1.1

Additional Context

No response

Are you willing to open a PR to help address this issue?

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-joemarkiewicz]

Hi @jmcdonald-moveworks thanks so much for opening this issue. This is definitely an interesting scenario. So if I am understanding this correctly, Salesforce interprets a certain number of null records as non null?

[jmcdonald-moveworks]

@fivetran-joemarkiewicz I believe that's correct. Behind the scenes it seems to have some sort of null handling that replaces null with 0:

[fivetran-joemarkiewicz]

Hi @jmcdonald-moveworks my apologies for the delayed response. I have been able to do some digging on this issue but was not able to find a workaround that could be used to address this within the dbt package itself.

The Salesforce connector actually handles the translations from Salesforce SOQL to SQL and we may be able to address this issue directly within the connector. Unfortunately, I am not part of the team working on the translations, so I would suggest opening a Support Ticket and our engineering team can work with you to make sure this behavior is reflected in the translated SQL within the fivetran_formula solution.

[fivetran-joemarkiewicz]

As opening a support ticket (linked in my previous response) is most likely the path forward to resolution, I will close this bug report.

Please feel free to reopen if you believe there is more to discuss.