[Bug] Compilation error: get_column_values(): relation fivetran.salesforce.fivetran_formula_model does not exist and no default value was provided

[avaashwill]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

We switched to the fivetran_formula_model table in anticipation of the fivetran_formula table being deprecated in the Salesforce connector. This error started intermittently popping up during fivetran scheduled transformations of our dbt project. The error self-resolves every time and has not happened two runs in a row.

Relevant error log or model output

18:20:45  Compilation Error in model salesforce__ticket_timer (models/sources/salesforce/salesforce__ticket_timer.sql)
18:20:45    In get_column_values(): relation fivetran.salesforce.fivetran_formula_model does not exist and no default value was provided.
18:20:45    
18:20:45    > in macro statement (macros/etc/statement.sql)
18:20:45    > called by macro default__get_column_values (macros/sql/get_column_values.sql)
18:20:45    > called by macro get_column_values (macros/sql/get_column_values.sql)
18:20:45    > called by macro sfdc_formula_view (macros/sfdc_formula_view.sql)
18:20:45    > called by model salesforce__ticket_timer (models/sources/salesforce/salesforce__ticket_timer.sql)

Expected behavior

No error

dbt Project configurations

N/a

Package versions

# Packages can be found on https://hub.getdbt.com/.
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  - package: dbt-labs/codegen
    version: [">=0.10.0", "<0.11.0"]
  - package: fivetran/salesforce_formula_utils
    version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

snowflake

dbt Version

1.5.1

deployment.yml

dbtVersion: 1.5.1

jobs:
# Note that the timezone that these jobs will run on will be the timezone of the Fivetran destination,
# in this case Eastern Standard Time. Note that Daylight Savings Time is not observed with this time setting.
# For help with cron formatting, visit https://crontab.guru/.
  - name: recurring_production_run
    # At 7am, 10am, 1pm, 4pm, and 7pm from Monday through Friday
    schedule: 0 7,10,13,16,19 * * 1-5
    steps:
      - name: run dbt seed
        command: dbt seed --full-refresh
      - name: run models
        # This will run all models
        command: dbt run

Additional Context

I am able to run the compiled code from the ticket_timer model in snowflake with no issues. I saw similar stale issue, but it refers to the fivetran_formula table.

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-jamie]

Hi there @avaashwill! We've seen this error pop up in the past, and in particular this customer saw a similar 50% fail rate once they upgraded to dbt v1.5. We've suspected an issue in the dbt-snowfake v1.5 adapter specifically

Did you happen to recently upgrade your dbt version? Would it be possible to upgrade to v1.6?

[avaashwill]

Hello @fivetran-jamie thanks for the response! I looked over that thread and it does seem like the same issue. We upgraded to 1.5 in late June 2023, so it was recent... ish. When we were using the fivetran_formula table before this and the code looked like this:

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

we got the error one time on 8/21/2023 and then merged a PR on 8/22/2023 that changed the source from fivetran_formula to fivetran_formula_table. Now the code looks like this:

{{ salesforce_formula_utils.sfdc_formula_view(
            source_table='lwccase_timer_session_time_c',
            full_statement_version=true)
        }}

We get the error daily now. Again, the first time we saw it was right before we switched over to the other formula table.

I'm willing to upgrade to dbt 1.6 and see if that helps, but let me know if there are any smaller changes we could try first, I didn't see anything in the thread that pointed to resolution.

[fivetran-jamie]

Your code looks good to me! Just to confirm, have you included fivetran_formula_model in your defined salesforce source as part of Step 3?

Otherwise, the only solution I can come up with is upgrading dbt 😞 @avaashwill

[avaashwill]

@fivetran-jamie, yep our salesforce source looks like the one you linked.

Okay, no problem at all. I'll talk with the team about upgrading. Thank you so much for your help!

[fivetran-jamie]

no problem! please let us know if this doesn't do the trick. for now i'll mark this as won't fix but will keep it open as i'm sure someone else will have this issue

[avaashwill]

@fivetran-jamie Good morning! We upgraded to the latest version of dbt core (1.6.1) and that did not solve the issue.