search

fivetran / dbt_intercom

Data models for Fivetran's Intercom connector built using dbt.
https://fivetran.github.io/dbt_intercom/
Apache License 2.0
4 stars 6 forks source link

Failure on intercom__conversation_metrics model build #30

Closed scott-infogrid closed 1 year ago

scott-infogrid commented 1 year ago

Is there an existing issue for this?

Describe the issue

Running dbt run on --select +intercom+ returns an error for the intercom__conversation_metrics model. We are using a Redshift database.

As per the error below, the issue occurs within the date_diff functions. The model runs if these five lines are removed from the code.

Relevant error log or model output

Postgres adapter: Postgres error: function pg_catalog.date_diff("unknown", timestamp with time zone, timestamp with time zone) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Expected behavior

Models should build without error.

dbt Project configurations

# Name your project! Project 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: 'infogrid_bi'
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 `model-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"

  # Materialize all views as late-binding to reduce Looker downtime. See https://docs.getdbt.com/reference/resource-configs/redshift-configs#late-binding-views 
  +bind: false 

# Tables output as a result of package runs go into separate schemas.
# These outputs are then transferred to the analytics schema as needed. This helps reduce model clutter.
  hubspot:
    +schema: dbt_packages_output
  hubspot_source:
    +schema: dbt_packages_source
  xero:
    +schema: dbt_packages_output
  xero_source:
    +schema: dbt_packages_source
  lever:
    +schema: dbt_packages_output
  lever_source:
    +schema: dbt_packages_source
  intercom:
    +schema: dbt_packages_output
  intercom_source:
    +schema: dbt_packages_source

# Tables that we've built for final analysis go in the analytics schema as tables
# Staging models should only contain light transformation so these are materialized as views
# Intermediate models often contain heavy transformations. These are materialized as tables for query performance reasons. They are stored in a separate schema to avoid clutter.
  infogrid_bi:
    athena_sensor_events:
      01_staging:
        schema: 01_staging
        materialized: view
      02_intermediate:
        schema: 02_intermediate
        materialized: table
      03_core:
        materialized: table

    aws_cost_report:
      01_staging:
        schema: 01_staging
        materialized: view

    reporting:
      materialized: table

    hubspot:
      01_staging:
        schema: 01_staging
        materialized: view
      02_intermediate:
        schema: 02_intermediate
        materialized: table
      03_core:
        materialized: table

    lever:
      01_staging:
        schema: 01_staging
        materialized: view
      02_intermediate:
        schema: 02_intermediate
        materialized: table
      03_core:
        materialized: table

    hibob:
      01_staging:
        materialized: view
      02_intermediate:
        schema: 02_intermediate
        materialized: table
      03_core:
        materialized: table

    xero:
      01_staging:
        schema: 01_staging
        materialized: view
      # 02_intermediate:
      #   schema: 02_intermediate
      #   materialized: table
      03_core:
        materialized: table

    company_targets:
      01_staging:
        materialized: view
      # 02_intermediate:
      #   schema: 02_intermediate
      #   materialized: table
      03_core:
        materialized: table

    webapp_rds:
      01_staging:
        schema: 01_staging
        materialized: table
      02_intermediate:
        schema: 02_intermediate
        materialized: table
      03_core:
        materialized: table

seeds:
  infogrid_bi:
    hibob_people_daily_audit_historic_seed:
      +column_types:
        employee_id: varchar
        manager_s_id: varchar

vars:
  segment:
    segment_page_views_table: "{{ source('segment', 'pages') }}"

# Setting custom options for Fivetran Hubspot dbt package
  hubspot__pass_through_all_columns: true 
  hubspot_email_event_spam_report_enabled: false
  hubspot_email_event_print_enabled: false
  hubspot_email_event_forward_enabled: false
  hubspot_contact_merge_audit_enabled: true 

# Options for Fivetran's Intercom dbt package
  intercom__using_company_tags: false
  intercom__using_conversation_tags: false

# Setting the source data location for Fivetran dbt packages
  hubspot_database: bi_database
  hubspot_schema: hubspot_fivetran 
  lever_database: bi_database
  lever_schema: lever_fivetran 
  xero_database: bi_database
  xero_schema: xero_fivetran 
  segment_database: bi_database
  segment_schema: webapp
  intercom_database: bi_database
  intercom_schema: intercom

Package versions

packages:
  - package: emilyriederer/dbtplyr
    version: [">=0.3.1"]
  - package: fivetran/hubspot
    version: ["=0.5.1"]
  - package: fivetran/lever
    version: [">=0.4.0"]
  - git: "https://github.com/scott-infogrid/dbt_xero.git" # Temporary fork of the dbt_xero package to remove testing error.
    revision: "v1.0.0"
  - package: dbt-labs/redshift
    version: [">=0.8.0"]
  - package: fivetran/intercom
    version: [">=0.4.0", "<0.5.0"]

What database are you using dbt with?

redshift

dbt Version

1.2 (latest)

Additional Context

No response

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

fivetran-joemarkiewicz commented 1 year ago

HI @scott-infogrid thanks so much for opening this issue and I am sorry to see you are experiencing an error.

It looks like you may be using an outdated version of the dbt_intercom package. I noticed in your package versions you are using the following:

packages:
  - package: fivetran/intercom
    version: [">=0.4.0", "<0.5.0"]

However, our v0.5.0 release of the intercom package is the one with Postgres support and addresses some timestamp issues. I would recommend upgrading your package dependency to be the following:

packages:
  - package: fivetran/intercom
    version: [">=0.5.0", "<0.6.0"]

This should do the trick and remove the error you are experiencing. Let me know if this works!

scott-infogrid commented 1 year ago

Thanks @fivetran-joemarkiewicz - I hadn't noticed we were behind on releases. That's resolved it now!

I'll close out the ticket.