[Bug] Compiled SQL does not handle quoting and identifier from dbt source

jberkowitz89 commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

Our source order is configured in our dbt source having a specific identifier ORDER, and quoting set to true. When referencing this source in regular staging models, the SQL compiles to database.salesforce."ORDER". When referencing this source in the salesforce_formula_utils.sfdc_formula_view macro, the identifier and quoting are not included, and the SQL fails to reference the correct source table.

When reviewing the compiled SQL, it appears that the identifier and quoting compile correctly for single table formulas, but don't compile correctly in the view_sql.

Relevant error log or model output

Here is a snippet of the view_sql that is causing the error: 

 ( select main__table.id, ( CASE main__table.effective_date = contract__alias.start_date WHEN true THEN False ELSE True END ) as renewal_order_c from database.salesforce.order as main__table left join database.salesforce.contract as contract__alias on main__table.contract_id = contract__alias.id ) as view_sql_1

Here is the relevant log output:
17:06:53  Completed with 1 error and 0 warnings:
17:06:53  
17:06:53  Database Error in model base_salesforce_order_formula (models/staging/database/salesforce/base_salesforce_order_formula.sql)
17:06:53    001003 (42000): SQL compilation error:
17:06:53    syntax error line 104 at position 189 unexpected 'order'.
17:06:53    syntax error line 104 at position 195 unexpected 'as'.
17:06:53    syntax error line 104 at position 324 unexpected 'as'.
17:06:53    compiled SQL at target/run/healthjoy/models/staging/database/salesforce/base_salesforce_order_formula.sql

Expected behavior

I am expecting the SQL to compile correctly and include the quoting and identifier specified in our source.

SQL should compile to:

( select main__table.id, ( CASE main__table.effective_date = contract__alias.start_date WHEN true THEN False ELSE True END ) as renewal_order_c from database.salesforce."ORDER" as main__table left join database.salesforce.contract as contract__alias on main__table.contract_id = contract__alias.id ) as view_sql_1

dbt Project configurations

 # Name your package! Package names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'healthjoy'
version: '1.0'
require-dbt-version: ">=1.0.0"

config-version: 2
# This setting configures which "profile" dbt uses for this project. Profiles contain
# database connection information, and should be configured in the  ~/.dbt/profiles.yml file
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that source models can be found
# in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analysis"] 
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_packages"

...

models:
  sql_header: "alter session set timezone = 'UTC';"
  transient: false
  healthjoy:
      constants:
          materialized: incremental
      objects: 
          materialized: table
      events: 
          materialized: table
      analytics:
          materialized: table
      feedback:
          materialized: table
      staging:
          materialized: view
      intermediate:
          materialized: table
      marts:
          materialized: table

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: 0.8.5
  - package: calogica/dbt_expectations
    version: 0.5.6
  - package: brooklyn-data/dbt_artifacts
    version: 1.2.0
  - package: fivetran/salesforce_formula_utils
    version: 0.7.1
  - package: dbt-labs/codegen
    version: 0.8.1

What database are you using dbt with?

snowflake

dbt Version

Core:

installed: 1.1.0
latest: 1.3.0 - Update available!

Your version of dbt-core is out of date! You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

Plugins:

snowflake: 1.1.0 - Update available!

Additional Context

No response

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 commented 1 year ago

Hi @jberkowitz89 I just wanted to follow up here as we were able to chat during our office hours session last week. Our resolution for this issue was to open a Support Ticket for our engineering team to explore applying a fix within the connector itself.

Were you able to open the support ticket? If so, would you like to keep this issue open as well? If not, we could close it out as the conversation may have been moved to another ticket.

Let me know!

jberkowitz89 commented 1 year ago

Hey Joe, I was able to pen the support ticket: https://support.fivetran.com/hc/en-us/community/posts/4617836454551-Connector-Improvement-dbt-salesforce-formula-utils-Support-for-Reserved-Keywords?input_string=Salesforce%20Formula%20Connector%20-

Feel free to close this one out, I'll keep an eye on the issue for any future fixes.

fivetran-joemarkiewicz commented 1 year ago

Thanks @jberkowitz89 appreciate the follow up and I will keep my eye on that support ticket you created.

fivetran / dbt_salesforce_formula_utils