fivetran formula field is returning float point error using LN() function

[lucasespinos]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Hello,

I am using fivetran quickstart transformations in order to bring salesforce formula fields to bigquery. But one the views that it got created for a specific salesforce object is not working. I found the root cause and its because of one field formula translation. So we have a field called voc_expected_c and the original formula inside salesforce is the following:

(   PanelCount__c * 10.9 * (1 - 0.0026 * ((AmbientTemperatureInF__c -32) * 5/9 + MPIrradiance__c *(0.95*0.8/15) -25) ) + 16 *   PanelCount__c * 0.0000861733*(273.15 + (AmbientTemperatureInF__c -32) * 5/9 + MPIrradiance__c *(0.95*0.8/15)) *     LN( MPIrradiance__c /1000))* (1 -  Shaded__c) +        (PanelCount__c * 10.9 *(1-0.0026 * ((AmbientTemperatureInF__c -32) *5/9  +  MPIrradiance__c/ 10 *(0.95*0.8/15)-25) ) + 16 *      PanelCount__c  * 0.0000861733*(273.15 + (AmbientTemperatureInF__c -32) *5/9  +  MPIrradiance__c/ 10 *(0.95*0.8/15)) *   LN(MPIrradiance__c/ 10000)) * Shaded__c

and the fivetran SQL translation is the following:

(COALESCE((COALESCE(((COALESCE((COALESCE((COALESCE(mt.panel_count_c,0)*COALESCE(10.9,0)),0)*COALESCE(((COALESCE(1,0)-COALESCE((COALESCE(0.0026,0)*COALESCE(((COALESCE((COALESCE(CASE WHEN COALESCE(9,0)=0 THEN NULL ELSE COALESCE((COALESCE(((COALESCE(mt.ambient_temperature_in_f_c,0)-COALESCE(32,0))),0)*COALESCE(5,0)),0)/9 END,0)+COALESCE((COALESCE(mt.mpirradiance_c,0)*COALESCE((CASE WHEN COALESCE(15,0)=0 THEN NULL ELSE COALESCE((COALESCE(0.95,0)*COALESCE(0.8,0)),0)/15 END),0)),0)),0)-COALESCE(25,0))),0)),0))),0)),0)+COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(16,0)*COALESCE(mt.panel_count_c,0)),0)*COALESCE(0.0000861733,0)),0)*COALESCE(((COALESCE((COALESCE(273.15,0)+COALESCE(CASE WHEN COALESCE(9,0)=0 THEN NULL ELSE COALESCE((COALESCE(((COALESCE(mt.ambient_temperature_in_f_c,0)-COALESCE(32,0))),0)*COALESCE(5,0)),0)/9 END,0)),0)+COALESCE((COALESCE(mt.mpirradiance_c,0)*COALESCE((CASE WHEN COALESCE(15,0)=0 THEN NULL ELSE COALESCE((COALESCE(0.95,0)*COALESCE(0.8,0)),0)/15 END),0)),0))),0)),0)*COALESCE(LN(CASE WHEN COALESCE(1000,0)=0 THEN NULL ELSE COALESCE(mt.mpirradiance_c,0)/1000 END),0)),0))),0)*COALESCE(((COALESCE(1,0)-COALESCE((mt.shaded_c/100.0),0))),0)),0)+COALESCE((COALESCE(((COALESCE((COALESCE((COALESCE(mt.panel_count_c,0)*COALESCE(10.9,0)),0)*COALESCE(((COALESCE(1,0)-COALESCE((COALESCE(0.0026,0)*COALESCE(((COALESCE((COALESCE(CASE WHEN COALESCE(9,0)=0 THEN NULL ELSE COALESCE((COALESCE(((COALESCE(mt.ambient_temperature_in_f_c,0)-COALESCE(32,0))),0)*COALESCE(5,0)),0)/9 END,0)+COALESCE((COALESCE(CASE WHEN COALESCE(10,0)=0 THEN NULL ELSE COALESCE(mt.mpirradiance_c,0)/10 END,0)*COALESCE((CASE WHEN COALESCE(15,0)=0 THEN NULL ELSE COALESCE((COALESCE(0.95,0)*COALESCE(0.8,0)),0)/15 END),0)),0)),0)-COALESCE(25,0))),0)),0))),0)),0)+COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(16,0)*COALESCE(mt.panel_count_c,0)),0)*COALESCE(0.0000861733,0)),0)*COALESCE(((COALESCE((COALESCE(273.15,0)+COALESCE(CASE WHEN COALESCE(9,0)=0 THEN NULL ELSE COALESCE((COALESCE(((COALESCE(mt.ambient_temperature_in_f_c,0)-COALESCE(32,0))),0)*COALESCE(5,0)),0)/9 END,0)),0)+COALESCE((COALESCE(CASE WHEN COALESCE(10,0)=0 THEN NULL ELSE COALESCE(mt.mpirradiance_c,0)/10 END,0)*COALESCE((CASE WHEN COALESCE(15,0)=0 THEN NULL ELSE COALESCE((COALESCE(0.95,0)*COALESCE(0.8,0)),0)/15 END),0)),0))),0)),0)*COALESCE(LN(CASE WHEN COALESCE(10000,0)=0 THEN NULL ELSE COALESCE(mt.mpirradiance_c,0)/10000 END),0)),0))),0)*COALESCE((mt.shaded_c/100.0),0)),0))

The heart of the problem is the natural logarithm function LN()

LN(
  CASE
    WHEN COALESCE(10000,0)=0 THEN NULL 
    ELSE COALESCE(mt.mpirradiance_c,0)/10000 
  END
)

Since first when statement will always be false we went to the second one COALESCE(mt.mpirradiance_c,0)/10000 and here for mt.mpirradiance_c column we might have null values and in that case we will have LN(0) issues.

In theory I think this part of the formula need to be something like the following:

LN(
  CASE
    WHEN COALESCE(mt.mpirradiance_c,0)=0 THEN NULL 
    ELSE COALESCE(mt.mpirradiance_c,0)/10000 
  END
)

This will solve the issue. But since we are not using dbt custom project only enable it from quickstart models there is nothing that we can do right now...

Can someone help me with this?

Relevant error log or model output

Floating point error in function: LN(0)

Expected behavior

Bigquery view working without any issues.

dbt Project configurations

-

Package versions

-

What database are you using dbt with?

bigquery

dbt Version

1.7.14

Additional Context

No response

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

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

[fivetran-joemarkiewicz]

Hi @lucasespinos thanks for opening this issue and sorry to hear you are running into this formula translation error.

Unfortunately, this dbt package only materializes the sql code provided in the fivetran_formula_model table. Therefore, this package has no control over the translations themselves. Fortunately, the connector team does! While we won't be able to address this issue via the dbt package, this can be address directly in the connector.

I recommend opening a Fivetran Support Ticket and highlighting the same information you have here. The Fivetran Support team will then be able to share this with the connector team and look to updating the logic used to translate the Salesforce Formula Field.

Let me know if you have any questions. Thanks!

[fivetran-joemarkiewicz]

Since this is not an issue we will be able to address directly within the dbt package, I will mark this as wontfix. @lucasespinos let me know if you have any concerns. Otherwise, I will plan to close this issue in the near future.