[Bug] sfdc_formula_view no longer viable solution

[duncan771]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Since the changes Fivetran made to it's SFDC connector effective Nov 1st and the resulting PR #101, many of our models won't build correctly using sfdc_formula_view. The reason behind this is because with Option 2 or 3, we were able to have sfdc_exclude_formulas which accounted for erroneous fields.

As of now, the models that previously contained those erroneous formulas aren't building at all. Which means we have to continue to use full_statement_version=false which will become stale very quickly.

I'd like for there to be a way to ignore specific formulas like we could with Options 2 and 3. I can't imagine ours is the only workflow that was broken because of this.

Relevant error log or model output

These error logs are incredibly unhelpful. For example:
Invalid NUMERIC value: 
  compiled Code at target/run/XXX/sfdc/base/df_base_sfdc__sbqq_quote_c_calculated.sql

Expected behavior

I would expect these models to continue to build correctly after there was a PR deprecating the current option for how we were building.

dbt Project configurations

The only relevant ones are the formulas under sfdc_exclude_formulas which are specific to our team.

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.5"]

  - package: calogica/dbt_expectations
    version: [">=0.8.0", "<0.9.0"]

  - package: grafana/leaner_query
    version: 0.1.5

What database are you using dbt with?

bigquery

dbt Version

1.6.3

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 @duncan771 thank you for opening this issue and I definitely understand your desire to filter out problematic formula fields with the full_statement_version=true version of the package. However, this likely won't be possible for us to achieve within the package. As this version pulls from the fivetran_formula_model table, it essentially just materializes the model field in your warehouse. Therefore, if we were to try and remove fields we would have to do some intense string matching which likely would not be successful in the end.

I understand that it would be preferred to remove these formulas directly from the package like you were able to previously. However, this request would now needs to be addressed via a Support Ticket so our connector and support teams may either help address the problem code in the formula translation, or offer flexibility for removing columns in the model field that the connector syncs.

I apologize we are not able to help within the package, but our support and connector teams should be able to help going forward!

[fivetran-joemarkiewicz]

As a result of the above response, I will close this ticket. For anyone else who comes across a similar inquiry, I encourage you to open a support ticket to work directly with our connector and support teams to raise any concerns. Thank you!