Skip to main content
Documentation
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: salesforce_formula_utils Support for excluding formulas with Option 1

Planned
Brendan Duncan User

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.

The problem is that this is tied to how Fivetran builds the `fivetran_formula_model` table.

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Ray User
    • Edited

    Thanks for sharing this request. Our goal is to translate all formulas successfully and to skip any impossible formulas. To that end, if you or any customers encounter issues, please report them to our support team. We'll work to get them resolved correctly.

    For now, we'll keep this idea to manage inclusion/exclusion of specific formulas in model translations on the backlog and monitor for demand.

  • Andrew Corcoran User

    This would be a huge addition now that the `fivetran_formula` table has been deprecated. This has caused issues for us because we can ONLY use option 1 now and we have formula fields that are being translated into huge SQL queries in the `fivetran_formula_model` table. We don't get any errors when we try to run the model, they're just so large that dbt and Databricks can't seem to handle them. We only need to exclude like 5 fields, but my only solution to keep our formula fields as current as possible now is to manually update the model each week by retrieving the SQL translation from `fivetran_formula_model`, deleting the problematic fields, and updating the corresponding dbt model.