[Bug] Model runs into an error when no record exists in fivetran_formula_model

[fivetran-juliengoulley]

Is there an existing issue for this?

• [x] I have searched the existing issues

Describe the issue

When an object has no formula then no record exists under fivetran_formula_model and that causes the model to fail.

For instance, in my environment, there is no formulas for case_opportunity. If I include the below statement in my SQL model

{% set sf_object_name = 'case_milestone' %} {{ config({ 'alias': sf_object_name}) }} {{ salesforce_formula_utils.sfdc_formula_view(source_table=sf_object_name, materialization='table',  full_statement_version=true) }}

Then I'm getting the below error

16:25:15 1 of 1 START sql table model julien_dbt_fivetranlog.case_milestone ............. [RUN] 16:25:18 BigQuery adapter: https://console.cloud.google.com/bigquery?project=fivetran-wild-west&j=bq:US:afdb7919-2106-4f9d-831a-5dab7d7b66e2&page=queryresults 16:25:18 1 of 1 ERROR creating sql table model julien_dbt_fivetranlog.case_milestone .... [ERROR in 3.22s] 16:25:18 16:25:18 Finished running 1 table model in 0 hours 0 minutes and 6.26 seconds (6.26s). 16:25:18 16:25:18 Completed with 1 error and 0 warnings: 16:25:18 16:25:18 Database Error in model salesforce_test (models/salesforce_test.sql) 16:25:18 Syntax error: Expected "(" or keyword SELECT or keyword WITH but got ")" at [28:5] 16:25:18 compiled Code at target/run/dbt_wildwest_fivetranlog/models/salesforce_test.sql

When I check the compiled model, I can see that the statement is indeed empty

create or replace table fivetran-wild-west.julien_dbt_fivetranlog.case_milestone OPTIONS() as ( -- Best practice for this model is to be materialized as view. That is why we have set that here.

/ The below sets the old_formula_fields variable to the results of the get_column_values results which queries the field column from the fivetran_formula table. The logic here is that the variable will be a list of all current salesforce formula field names. This list is then used within the dbt_utils.star operation to exclude them. This allows users with the Fivetran legacy Salesforce fields to ignore them and be replaced by the new fields. / );

A solution could be to remove that model but the customer would like to make sure any new formula is picked up by this DBT transformation so this is not an acceptable solution

Relevant error log or model output

See above

Expected behavior

Create an empty table or other approach. At least, remove the error

dbt Project configurations

name: 'dbt_wildwest_fivetranlog'
version: '1.0.0'
config-version: 2

profile: 'dbt_wildwest_fivetranlog'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  
clean-targets: 
  - "target"
  - "dbt_packages"

vars:
    fivetran_log_database: fivetran-wild-west
    fivetran_log_schema: julien_fivetran_log_live

    salesforce_schema: julien_salesforce_sandbox

Package versions

packages:
  - package: fivetran/salesforce_formula_utils
    version: 0.7.2

What database are you using dbt with?

snowflake, bigquery

dbt Version

1.3.0

Additional Context

This issue has been raised by LeasePlan. They have raised a few issues while moving from V1 to V2 and although many seem to be related to Salesforce Data Quality issue, it would really help them get passed this issue.

I haven't done so yet, but in the meantime, we will look into adding a IF statement around the salesforce_formula_utils.sfdc_formula_view function to check the existence of a model first

Are you willing to open a PR to help address this issue?

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

[fivetran-joemarkiewicz]

HI @fivetran-juliengoulley thanks for opening this issue. I actually just chatted with our product team and discovered that this is something which will be addressed within the connector. As such, this is likely something we won't reflect within the dbt package.

Therefore, I will mark this as won't do and will close out this issue once the update is rolled out to the connector.

[ym-onprem]

has this been resolved? was it addressed within the connector?

[yuna-tang]

I have experienced the same issue, can you please advise when this issue will be fixed by the connector update? I have logged a support ticket: 144364 I have to update to below to make sure the code works```

`{{ salesforce_formula_utils.sfdc_formula_view(
    source_name = 'salesforce',
    source_table='account_history',
    full_statement_version=false
    )
}}`

[babaMar]

HI @fivetran-juliengoulley thanks for opening this issue. I actually just chatted with our product team and discovered that this is something which will be addressed within the connector. As such, this is likely something we won't reflect within the dbt package.

Therefore, I will mark this as won't do and will close out this issue once the update is rolled out to the connector.

Hi @fivetran-joemarkiewicz we are experiencing the same with the latest versions of dbt and this package.

In which way will be addressed? Wouldn't the macro still try to fetch the formula from fivetran_formula_model, are you going to have a "dummy" formula there?

This issue has been open in February, any updates?

[babaMar]

It would probably be better to just raise an error if there's no formula model, wouldn't it?