[Bug] missing Redshift datediff function when executing `dbt run`

[gtang31-te]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

I am trying to use the salesforce_the_formulas_util dbt package to ingest formula fields for my SFDC objects. These are the current files and their contents in my models/ directory:

models/fivetran_salesforce.opportunity_view.sql

{{ salesforce_formula_utils.sfdc_formula_view(
    source_table='opportunity',
    source_name='fivetran_salesforce',
    full_statement_version=true)
}}

models/src_salesforce.yml

version: 2

sources:
  - name: fivetran_salesforce #It would be best to keep this named salesforce
    schema: 'fivetran_salesforce' #Modify this to be where your Salesforce data resides
    tables:
      - name: fivetran_formula_model
        description: Used for the recommended Option 1 version of the formula solution.
      - name: fivetran_formula
        description: Used for options 2 and 3 of the original individual formula solution.

      ## Any other source tables you are creating models for should be defined here as well.
      - name: opportunity

Relevant error log or model output

Database Error in model fivetran_salesforce.opportunity_view (models/fivetran_salesforce.opportunity_view.sql)
function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp without time zone) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
compiled Code at target/run/PROD_US_East_1/models/fivetran_salesforce.opportunity_view.sql

Expected behavior

datediff is a valid redshift function so I am unsure why this is happening

dbt Project configurations

version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project 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_modules"

### Package versions

packages:
  - package: fivetran/salesforce_formula_utils
    version: [">=0.7.0", "<0.8.0"]

### What database are you using dbt with?

redshift

### dbt Version

Core:
  - installed: 1.3.1
  - latest:    1.3.1 - Up to date!

Plugins:
  - redshift: 1.3.0 - Up to date!
  - postgres: 1.3.1 - Up to date!

### Additional Context

Found a similar issue here: https://github.com/fivetran/dbt_jira_source/issues/23

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

- [X] Yes.
- [X] Yes, but I will need assistance and will schedule time during our [office hours](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-office-hours) for guidance
- [ ] No.

[fivetran-joemarkiewicz]

Hi @gtang31-te thanks for opening this issue!

It looks like the error you are experiencing is a result of a translation issue within the solution. Unfortunately, the dbt package only captures the sql within the fivetran_formula_model table within your source and materializes it within your warehouse. As such, there is nothing the package can do to address the translation error.

However, our engineering team will be able to help! I would create a support ticket and share the same information. Our engineering team will then be able to triage the issue and help.

[gtang31-te]

thanks for the quick response @fivetran-joemarkiewicz , i will open a support ticket

[fivetran-joemarkiewicz]

Sounds great! I will mark this as wontfix and will close the ticket for the time being. However, feel free to reopen or comment in this thread if anything else on your end comes up 😄