[Bug] When using full_statement_version=true, i.e. Option 1 in the README, you can't exclude formulas

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

I am building out some models using the sfdc_formula_view macro. When I set full_statement_version = true, I get failing tests because there are some fields coming in (as currency) that are being set to STRINGs and their formulas no longer work. In this specific example, I'm getting an error because you can't coalesce a STRING and INT64. However, this is something we could work around.

The main issue is, If we just wanted to remove this field and continue testing or even operating in production with the current model, we couldn't use full_statement_version = true. I've dug through the code quite a bit and since we are pulling the model straight from the fivetran_formula_model table, you can't cleanly remove the problematic formulas.

Step 5 in the README would leave you to believe that this sfdc_exclude_formulas works regardless of if you are using Option 1, 2, or 3. It would be greatly beneficial if we could have Option 1 (grabbing nested formulas) also be able to exclude formulas from our models.

Relevant error log or model output

Database Error in model stg_sfdc__leads_calculated (models/staging/sfdc/stg_sfdc__leads_calculated.sql)
21:07:02    No matching signature for function COALESCE for argument types: STRING, INT64. Supported signature: COALESCE([ANY, ...]) at [25:6496]
21:07:02    compiled Code at target/run/grafanalabs/models/staging/sfdc/stg_sfdc__leads_calculated.sql

Expected behavior

I would expect the code to execute and ignore the problematic field - in this case it's committed_mrr_c.

dbt Project configurations

vars:
    sfdc_exclude_formulas: ('committed_arr_c', 'committed_mrr_c')

Package versions

packages:

package: dbt-labs/dbt_utils version: 1.0.0
package: dbt-labs/codegen version: 0.9.0
package: brooklyn-data/dbt_artifacts version: 2.2.0
package: fivetran/salesforce_formula_utils version: [">=0.8.0", "<0.9.0"]
package: calogica/dbt_expectations version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.0

Additional Context

I tried rebuilding some of the macros to get this to work, but I end up being limited by the fivetran_formula_model table. I can't change how this is built and therefore can't really be of much use adding a PR to this repo - since the problem lives outside of it.

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.

Hi @duncan771 thanks for opening this issue.

I agree with you that the wording in the README could be more direct in highlighting that the variable does not work for option 1. I can very much work to update that wording to be more clear.

However, for the formula logic itself is something that we have little manipulation on within the package (especially when using option 1) since the package is simply taking the logic from the fivetran_formula_model table and materializing it in your warehouse. I would recommend that the best option forward is to raise this issue with our connector team to let them know this translation is not successful. From there, they will be able to apply a fix directly to the connector and ensure a similar translation error to this does not occur in the future.

You can open a support ticket via our support portal. Let me know if you have any other questions!

fivetran / dbt_salesforce_formula_utils