fivetran / fivetran/dbt_salesforce_formula_utils

do not null out fields that dbt cannot calculate

Open
#56 3 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

priority:p4 status:stale
Dominant language
Shell
Stars
18
Forks
21
Avg merge
15d 20h
Merged PRs (30d)
2

Description

### Is there an existing feature request for this?

- [X] 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](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-office-hours) for guidance.
- [X] No.

### Anything else?

_No response_

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by locating the dbt macro that builds Salesforce formula fields and the formula history table used for freshness checks. Confirm how a null fivetran_formulas.SQL value currently affects the generated relation; done means dbt-calculable fields are rebuilt, other fields retain Fivetran values, and stale formula updates cause a failing dbt test.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.