[BUG] sfdc_formula_view returns invalid identifier 'MODEL' error on quoted table

[RBABI-Team]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

sfdc_formula_view returns an invalid identifier 'MODEL' error when running on quoted tables, even with using_quoted_identifiers=true. This appears to be the statement throwing the error:

Script

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

Error

On sql_operation.<project_name>.inline_query: select
                "MODEL" as value

            from "SALESFORCE"."salesforce"."fivetran_formula_model"

            where "OBJECT" = 'Case'

            group by "MODEL"
            order by count(*) desc
Snowflake adapter: Snowflake query id: <snowflake_query_id>
Snowflake adapter: Snowflake error: 000904 (42000): SQL compilation error: error line 2 at position 16
invalid identifier 'MODEL'

However, when I check the table definitions of fivetran_formula_model in Snowflake it shows the MODEL field is actually lowercase:

Our Salesforce Connector does have a feature flag turned on to preserve the case/object names during replication due to some naming colisions, I'm wondering if that is what is causing the case discrepancy between Snowflake and what the macro is expecting?

Relevant error log or model output

See codeblock above

Expected behavior

Model should generate as usual in dbt

dbt Project configurations

Not project.yml file, but the Salesforce source is set with the following quoting configuration:

version: 2

sources:
  - name: salesforce
    database: SALESFORCE
    schema: salesforce
    quoting:
      database: true  
      schema: true
      identifier: true
    tables:
      - name: fivetran_formula_model
      - name: fivetran_formula
      - name: Case

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<1.2.0"]

  - package: dbt-labs/codegen
    version: [">=0.9.0", "<1.0.0"]

  - package: fivetran/salesforce_formula_utils
    version: 0.9.2

What database are you using dbt with?

snowflake

dbt Version

Core:

• installed: 1.6.2
• latest: 1.6.2 - Up to date!

Plugins:

• snowflake: 1.6.2 - Up to date!

Additional Context

Feature Flags in Fivetran turned on:

• Hardcoding Formula Values
• Preserve field names

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

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

[fivetran-joemarkiewicz]

Hi @RBABI-Team thanks for opening this issue. Would you be able to try and remove the following from your src.yml and see if that resolves the issue?

    quoting:
      database: true  
      schema: true
      identifier: true

[fivetran-joemarkiewicz]

Marking this as stale since there has not been a response to the above suggestion. Let me know if you tried the above and it did or did not work. Thanks!

[fivetran-joemarkiewicz]

I am going to close this issue and mark as won't fix as there has not bee activity for the last few weeks and I believe the above suggestion should resolve the issue.

If you come across this issue and the above suggestion does not resolve your error, please feel free to comment or reopen the issue.

[RBABI-Team]

Appologies for the delay in circling back to this. Removing the quoting block above results in a SQL compilation error stating that the schema doesn't exist. I assume that's because the name of the schema is coerced to lower case in Snowflake, and without quoting turned on Snowflake is interpreting the object names as uppercase.

Adding the quoting block as we had above is the recommended way to handle case-sensitive object names per dbt's documentation: https://docs.getdbt.com/reference/project-configs/quoting

[fivetran-joemarkiewicz]

Thanks for the response @RBABI-Team. Out of curiousity, what error do you see if you remove the following config in the macro?

using_quoted_identifiers=true

[kharigardner]

also occuring for me as well, wheter using_quoted_identifiers or not, the same error occurs

[kharigardner]

we have a ticket open with Fivetran support as well - quickstart failed to run exactly because of this issue too