[Feature] Add option to remove hard-coded where clause from sfdc_formula_view

[paperlinguist]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

Currently, fivetran is populating a where clause in fivetran_formula_model.model which is hard-coding the removal of deleted records. WHERE NOT mt."_FIVETRAN_DELETED"

Our company needs to remove this criteria from all of our Salesforce models so we ingest deleted items into our DBT snapshots. I propose adding that functionality directly to the sfdc_formula_view macro:

{% if remove_where_clause %}
    {{ table_results[0]|replace("WHERE NOT mt.\"_FIVETRAN_DELETED\"", "") }}
{% else %}
    {{ table_results[0] }}
{% endif %}

Describe alternatives you've considered

There's a few ways to solve this problem including building a view on top of the existing fivetran_formula_model table that makes this replacement. It's easy enough to update the schema name in the salesforce source yml (which is what we've gone with for the time being). However, I feel like this is functionality that is useful for not just our team and the solution that we've implemented is a bit clunky.

Are you interested in contributing this feature?

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

Anything else?

I have this built out in a branch already and it functions as I would expect it in testing: https://github.com/paperlinguist/dbt_salesforce_formula_utils/blob/paperlinguist-add-where-clause-removal/macros/sfdc_formula_view.sql

[fivetran-catfritz]

Thanks @paperlinguist! We'll take a look at your branch and feature request in the coming days and let you know if we have any questions.

[fivetran-catfritz]

Hi @paperlinguist thanks again for opening the issue and PR. We discussed this internally, and the consensus is that we would not be able to verify the integrity of the results with the _fivetran_deleted condition removed. So unfortunately we won't be able to merge in your PR at the moment. For now we recommend you to fork the repo and use your adjusted version.

However, we would like to keep this feature request open to see if others are wanting the same functionality. If others in the community let us know they want this, then we can reconsider incorporating it into the official version. Please let us know if you have any additional comments or questions!

[paperlinguist]

Thanks for the review internally. I had a feeling that would be the case and we've already deployed a work-around for our needs.