do not null out fields that dbt cannot calculate

[jeff-skoldberg]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

In some cases fivetran_formulas table has a null value in the SQL field, so dbt is not able to rebuild the formula. Therefore, to get ALL formula fields from salesforce, we need a mixed approach: Let dbt calculate fields that it can, then get the remaining fields from fivetran (with the risk that those formulas can go stale).

Simply put, the behavior should be: if fivetran_formulas.SQL is null, the dbt macro skip that field. Currently it will present null instead of the values that Fivetran brought in.

We stated this approach brings on risk of stale formulas. There should be a dbt test that checks the formula history table for updates to fields that dbt cannot re-calculate. The test should fail if any fivetran sync time for the data is less than the most recent formula update time. This way we can alert users of stale formulas via email and the user can run a full re-sync in fivetran.

Describe alternatives you've considered

Joe suggested to I can create a macro that dynamically builds the sfdc_exclude_formulas variable. The problem with this approach is that field will be excluded from the final output all together. Let's use an example to clarify this confusing statement.

Fivetran is syncing Salesforce table CONTACT and Fivetran Customer Support has enabled the formula fields to sync. Then I have a field called region_c, which fivetran is syncing but dbt cannot re-calculate. If I exclude region_c in sfdc_exclude_formulas, the resulting dbt view would not have the region_c column at all. So now if I want a view that has region_c from fivetran and all other fields from the macro, I need to do something like this:
select a.*, b.region_c from {{ref(fivetran_dbt_macro_contact_table)}} a join {{ref(original_salesforce_contact_table)}} b on a.id = b.id .

Hopefully that is clear why I do not have a work around that I can move forward with.

Are you interested in contributing this feature?

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

Anything else?

No response

[fivetran-joemarkiewicz]

Hi @jeff-skoldberg thanks for opening this feature request and I apologize for the delayed response.

I am very much in the corner of not wanting to secretly swap in the feature flagged formula in this solution as it opposes the intention of this package where the formula fields generated are accurate and up to date. If we brought in the formula which is synced via the connector, we could be passing through the wrong formula and you would be unaware that this is happening. I would prefer we take the route of addressing these fields on an ad hoc basis and open a support ticket for our engineering team to take a closer look. This would allow us to determine if there is something that may be done to resolve the formula translation.

Your second point is quite interesting to me as I thought an excluded formula field would be passed through if it still existed in the source. However, I noticed that we explicitly request the macro to ignore formula these formula fields.

dbt_salesforce_formula_utils/macros/sfdc_formula_view.sql

Line 25 in 40cdfd4

{{ salesforce_formula_utils.sfdc_star_exact(source(source_name,source_table), relation_alias=(source_table + "__table"), except=current_formula_fields) }} --Querying the source table and excluding the old formula fields if they are present.

I found that this may be possible to adjust. However, I want to make sure this is the right approach for all users. Similar to the above statement, I want to make sure this solution is providing the accurate formulas and not sneakily including others without the user being aware. For example, I could see it being confusing if a customer asks to ignore custom_field_c, but then because the Fivetran feature flag is turned on, that field still is pulled through in the end.

I hope this makes sense. I am going to collaborate internally some more to determine the best approach and will share what we discuss. Please feel free to add more context if you would like!

[jeff-skoldberg]

Hi @fivetran-joemarkiewicz , thank you for the reply. It makes sense.

One suggestion is to leave the default behavior of the macro as-is and add a new parameter to the macro: include_fivetran_synced_formulas default = False... but For my scenario I can swap it to true. This would seem like a win-win. Existing behavior stays in place unless you set include_fivetran_synced_formulas = True.

I look forward to your next update.

[raymondgh]

Hi Jeff, PM for Fivetran Salesforce connector here. Just wanted to pop in to thank you for your suggestion & use case context here!

While I agree with Joe that mixing transformed & synced formula fields could cause confusion & distrust, you have a point that in the end, sometimes something is better than nothing when it comes to getting these column values. A custom-written transformation would be the highest integrity but also the highest maintenance, while filling in the nulls with synced values would be at the opposite end of the spectrum.

As we continue to improve our translations, we'll be covering more and more formulas natively. That's our focus in the short & medium term. However, once that effort plateaus, I think it's likely we'll begin exploring mixed approaches to bridge the remaining gap between what can be created post-sync and what exists in the source.