fivetran / dbt_zendesk

Fivetran's Zendesk Support dbt package

Zendesk Support Modeling dbt Package (Docs)

📣 What does this dbt package do?

• Produces modeled tables that leverage Zendesk Support data from Fivetran's connector in the format described by this ERD and build off the output of our zendesk source package.
• Enables you to better understand the performance of your Support team. It calculates metrics focused on response times, resolution times, and work times for you to analyze. It performs the following actions:
  • Creates an enriched ticket model with relevant resolution, response time, and other metrics
  • Produces a historical ticket field history model to see velocity of your tickets over time
  • Converts metrics to business hours for Zendesk Support Professional or Enterprise users
  • Calculates SLA policy breaches for Zendesk Support Professional or Enterprise users
• Generates a comprehensive data dictionary of your source and modeled Zendesk Support data through the dbt docs site.

Note: Tickets from the Zendesk Support Chat channel will not populate in this package as the Fivetran connector does not currently support Chat based tickets. This is a feature request that has been flagged.

The following table provides a detailed list of final models materialized within this package by default.

TIP: See more details about these models in the package's dbt docs site.

model	description
zendesk__ticket_metrics	Each record represents a Zendesk Support ticket, enriched with metrics about reply times, resolution times, and work times. Calendar and business hours are supported.
zendesk__ticket_enriched	Each record represents a Zendesk Support ticket, enriched with data about its tags, assignees, requester, submitter, organization, and group.
zendesk__ticket_summary	A single record table containing Zendesk Support ticket and user summary metrics.
zendesk__ticket_backlog	A daily historical view of the ticket field values defined in the ticket_field_history_columns variable for all backlog tickets. Backlog tickets being defined as any ticket not in a 'closed', 'deleted', or 'solved' status.
zendesk__ticket_field_history	A daily historical view of the ticket field values defined in the ticket_field_history_columns variable and the corresponding updater fields defined in the ticket_field_history_updater_columns variable.
zendesk__sla_policies	Each record represents an SLA policy event and additional sla breach and achievement metrics. Calendar and business hour SLA breaches are supported.

Many of the above reports are now configurable for visualization via Streamlit! Check out some sample reports here.

🎯 How do I use the dbt package?

Step 1: Prerequisites

To use this dbt package, you must have the following:

• At least one Fivetran zendesk connector syncing data into your destination.
• A BigQuery, Snowflake, Redshift, PostgreSQL, or Databricks destination.

Databricks Dispatch Configuration

If you are using a Databricks destination with this package you will need to add the below (or a variation of the below) dispatch configuration within your dbt_project.yml. This is required in order for the package to accurately search for macros within the dbt-labs/spark_utils then the dbt-labs/dbt_utils packages respectively.

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['spark_utils', 'dbt_utils']

Step 2: Install the package

Include the following zendesk package version in your packages.yml file:

TIP: Check dbt Hub for the latest installation instructions or read the dbt docs for more information on installing packages.

packages:
  - package: fivetran/zendesk
    version: [">=0.14.0", "<0.15.0"]

Note: Do not include the Zendesk Support source package. The Zendesk Support transform package already has a dependency on the source in its own packages.yml file.

Step 3: Define database and schema variables

By default, this package runs using your destination and the zendesk schema. If this is not where your zendesk data is (for example, if your zendesk schema is named zendesk_fivetran), update the following variables in your root dbt_project.yml file accordingly:

vars:
    zendesk_database: your_destination_name
    zendesk_schema: your_schema_name 

Step 4: Disable models for non-existent sources

This package takes into consideration that not every Zendesk Support account utilizes the schedule, schedule_holiday, ticket_schedule daylight_time, time_zone, domain_name, user_tag, organization_tag, or ticket_form_history features, and allows you to disable the corresponding functionality. By default, all variables' values are assumed to be true. Add variables for only the tables you want to disable:

vars:
    using_schedules:            False         #Disable if you are not using schedules
    using_domain_names:         False         #Disable if you are not using domain names
    using_user_tags:            False         #Disable if you are not using user tags
    using_ticket_form_history:  False         #Disable if you are not using ticket form history
    using_organization_tags:    False         #Disable if you are not using organization tags

(Optional) Step 5: Additional configurations

Add passthrough columns

This package includes all source columns defined in the macros folder. You can add more columns from the TICKET, USER, and ORGANIZATION tables using our pass-through column variables.

These variables allow for the pass-through fields to be aliased (alias) and casted (transform_sql) if desired, but not required. Datatype casting is configured via a sql snippet within the transform_sql key. You may add the desired sql while omitting the as field_name at the end and your custom pass-through fields will be casted accordingly. Use the below format for declaring the respective pass-through variables:

vars:
  zendesk__ticket_passthrough_columns:
    - name: "account_custom_field_1" # required
      alias: "account_1" # optional
      transform_sql: "cast(account_1 as string)" # optional, must reference the alias if an alias is provided (otherwise the original name)
    - name: "account_custom_field_2"
      transform_sql: "cast(account_custom_field_2 as string)"
    - name: "account_custom_field_3"
  zendesk__user_passthrough_columns:
    - name: "internal_app_id_c"
      alias: "app_id"
  zendesk__organization_passthrough_columns:
    - name: "custom_org_field_1"

Note: Earlier versions of this package employed a more rudimentary format for passthrough columns, in which the user provided a list of field names to pass in, rather than a mapping. In the above ticket example, this would be [account_custom_field_1, account_custom_field_2, account_custom_field_3].

This old format will still work, as our passthrough-column macros are all backwards compatible.

Mark Former Internal Users as Agents

If a team member leaves your organization and their internal account is deactivated, their USER.role will switch from agent or admin to end-user. This will skew historical ticket SLA metrics, as we calculate reply times and other metrics based on agent or admin activity only.

To persist the integrity of historical ticket SLAs and mark these former team members as agents, provide the internal_user_criteria variable with a SQL clause to identify them, based on fields in the USER table. This SQL will be wrapped in a case when statement in the stg_zendesk__user model.

Example usage:

# dbt_project.yml
vars:
  zendesk_source:
    internal_user_criteria: "lower(email) like '[e-mail]' or external_id = '12345' or name in ('Garrett', 'Alfredo')" # can reference any non-custom field in USER

Tracking Ticket Field History Columns

The zendesk__ticket_field_history model generates historical data for the columns specified by the ticket_field_history_columns variable. By default, the columns tracked are status, priority, and assignee_id. If you would like to change these columns, add the following configuration to your dbt_project.yml file. Additionally, the zendesk__ticket_field_history model allows for tracking the specified fields updater information through the use of the zendesk_ticket_field_history_updater_columns variable. The values passed through this variable limited to the values shown within the config below. By default, the variable is empty and updater information is not tracked. If you would like to track field history updater information, add any of the below specified values to your dbt_project.yml file. After adding the columns to your root dbt_project.yml file, run the dbt run --full-refresh command to fully refresh any existing models.

vars:
    ticket_field_history_columns: ['the','list','of','column','names']
    ticket_field_history_updater_columns: [
                                            'updater_user_id', 'updater_name', 'updater_role', 'updater_email', 'updater_external_id', 'updater_locale', 
                                            'updater_is_active', 'updater_user_tags', 'updater_last_login_at', 'updater_time_zone', 
                                            'updater_organization_id', 'updater_organization_domain_names' , 'updater_organization_organization_tags'
                                            ]

Note: This package only integrates the above ticket_field_history_updater_columns values. If you'd like to include additional updater fields, please create an issue specifying which ones.

Extending and Limiting the Ticket Field History

This package will create a row in zendesk__ticket_field_history for each day that a ticket is open, starting at its creation date. A Zendesk Support ticket cannot be altered after being closed, so its field values will not change after this date. However, you may want to extend a ticket's history past its closure date for easier reporting and visualizing. To do so, add the following configuration to your root dbt_project.yml file:

# dbt_project.yml

...
config-version: 2

vars:
  zendesk:
    ticket_field_history_extension_months: integer_number_of_months # default = 0 

Conversely, you may want to only track the past X years of ticket field history. This could be for cost reasons, or because you have a BigQuery destination and have over 4,000 days (10-11 years) of data, leading to a too many partitions error in the package's incremental models. To limit the ticket field history to the most recent X years, add the following configuration to your root dbt_project.yml file:

# dbt_project.yml

...
config-version: 2

vars:
  zendesk:
    ticket_field_history_timeframe_years: integer_number_of_years # default = 50 (everything)

Changing the Build Schema

By default this package will build the Zendesk Support staging models within a schema titled (<target_schema> + _zendesk_source), the Zendesk Support intermediate models within a schema titled (<target_schema> + _zendesk_intermediate), and the Zendesk Support final models within a schema titled (<target_schema> + _zendesk) in your target database. If this is not where you would like your modeled Zendesk Support data to be written to, add the following configuration to your root dbt_project.yml file:

models:
  zendesk:
    +schema: my_new_schema_name # leave blank for just the target_schema
    intermediate:
      +schema: my_new_schema_name # leave blank for just the target_schema
    sla_policy:
      +schema: my_new_schema_name # leave blank for just the target_schema
    ticket_history:
      +schema: my_new_schema_name # leave blank for just the target_schema
  zendesk_source:
    +schema: my_new_schema_name # leave blank for just the target_schema

Change the source table references

If an individual source table has a different name than the package expects, add the table name as it appears in your destination to the respective variable:

IMPORTANT: See this project's dbt_project.yml variable declarations to see the expected names.

vars:
    zendesk_<default_source_table_name>_identifier: your_table_name 

(Optional) Step 6: Orchestrate your models with Fivetran Transformations for dbt Core™

Expand for details

Fivetran offers the ability for you to orchestrate your dbt project through Fivetran Transformations for dbt Core™. Learn how to set up your project for orchestration through Fivetran in our Transformations for dbt Core setup guides.

🔍 Does this package have dependencies?

This dbt package is dependent on the following dbt packages. Please be aware that these dependencies are installed by default within this package. For more information on the following packages, refer to the dbt hub site.

IMPORTANT: If you have any of these dependent packages in your own packages.yml file, we highly recommend that you remove them from your root packages.yml to avoid package version conflicts.

packages:
    - package: fivetran/zendesk_source
      version: [">=0.11.0", "<0.12.0"]

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

    - package: dbt-labs/dbt_utils
      version: [">=1.0.0", "<2.0.0"]

    - package: dbt-labs/spark_utils
      version: [">=0.3.0", "<0.4.0"]

    - package: calogica/dbt_date
      version: [">=0.9.0", "<1.0.0"]

🙌 How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions!

We highly encourage and welcome contributions to this package. Check out this dbt Discourse article on the best workflow for contributing to a package!

Opinionated Modelling Decisions

This dbt package takes an opinionated stance on how business time metrics are calculated. The dbt package takes all schedules into account when calculating the business time duration. Whereas, the Zendesk Support UI logic takes into account only the latest schedule assigned to the ticket. If you would like a deeper explanation of the logic used by default in the dbt package you may reference the DECISIONLOG.

🏪 Are there any resources available?

• If you have questions or want to reach out for help, please refer to the GitHub Issue section to find the right avenue of support for you.
• If you would like to provide feedback to the dbt package team at Fivetran or would like to request a new dbt package, fill out our Feedback Form.
• Have questions or want to be part of the community discourse? Create a post in the Fivetran community and our team along with the community can join in on the discussion!

dbt_zendesk's Issues

BUG - large first_reply_time_business_minutes on tickets with no replies

Are you a current Fivetran customer?
Maria Riolo, Data Analyst, Zearn

Describe the bug
In tickets with no replies, first_reply_time_business_minutes defaults to "business minutes from ticket creation to current time + 1000 hrs."

Steps to reproduce

1. Run normally on data including tickets with no replies
2. See errors in the first_reply_time_business_minutes column of zendesk__ticket_metrics table

Expected behavior
I would expect first_reply_time_business_minutes to be null in tickets with no replies.

Project variables configuration

# 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: 'zearn_redshift'
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: 'zearn-redshift'

# 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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]

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

on-run-start:
  # create schemas so we don't run into errors with the on-run-end hooks
  # if only doing a partial build or e.g. just running dbt seed
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_marts\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_staging\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_exports\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_zendesk_staging\""

on-run-end:
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_marts\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_exports\" to jitterbit_sfdc_export, group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_staging\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_zendesk_staging\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_marts\" TO group looker, group zearn_analytics, group zearn_product"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_exports\" TO jitterbit_sfdc_export, group zearn_analytics"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_staging\" TO group zearn_analytics, group looker"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_zendesk_staging\" TO group zearn_analytics, group looker"

# You can define configurations for models in the `source-paths` directory here.
# Using these configurations, you can enable or disable models, change how they
# are materialized, and more!

# In this example config, we tell dbt to build all models in the example/ directory
# as views (the default). These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  +bind: false

  zearn_redshift:
    +materialized: view
    staging:
      +materialized: view
      +schema: staging
      classification:
        +materialized: table
        support_tables:
          +materialized: table
      rostering:
        +materialized: table
      usage:
        +materialized: table
        +dist: user_id
        +sort: event_type, event_type_clean, created_at
    marts:
      +materialized: view
      +schema: marts
      summaries:
        +materialized: table
        +dist: user_id
        +sort: daydate
      exports:
        +materialized: table
        +schema: exports
  zendesk:
    +schema: staging
    intermediate:
      +schema: staging
    sla_policy:
      +schema: staging
    ticket_history:
      +schema: staging
vars:
  school_years: ['2012-2013', '2013-2014', '2014-2015', '2015-2016', '2016-2017', '2017-2018', '2018-2019', '2019-2020', '2020-2021']
  rostering_school_years: ['2016-2017', '2017-2018', '2018-2019', '2019-2020', '2020-2021']
  random_seed: 3655122572989899184
  burbio_table_date: "2020-01-01" #default value needed to generate docs
  burbio_file_name: fake_file.csv #default value needed to generate docs
  district_student_info_district_id: 0 #default value needed to generate docs
  zendesk_source:
    zendesk_database: production
    zendesk_schema: ft_zendesk

Package Version

packages:
  - package: fishtown-analytics/dbt_utils
    version: [">=0.3.0"]
  - package: fivetran/zendesk
    version: 0.4.1

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

I suspect I know where this is coming from.

In int_zendesk__ticket_first_reply_time_business.sql:

 greatest(0,
      (
        {{ fivetran_utils.timestamp_diff(
          'ticket_schedules.schedule_created_at',
          'least(ticket_schedules.schedule_invalidated_at, min(first_reply_time.agent_responded_at))',
          'second') }}/60
        )) as raw_delta_in_minutes

and in int_zendesk__ticket_schedules.sql:

coalesce(lead(schedule_created_at) over (partition by ticket_id order by schedule_created_at) , {{ fivetran_utils.timestamp_add("hour", 1000, "" ~ dbt_utils.current_timestamp() ~ "") }} ) as schedule_invalidated_at

So it looks to me like if there's no reply and lead(schedule_created_at) over (partition by ticket_id order by schedule_created_at) is null, then we'd end up with current time + 1000 hrs treated as first response time.

Screenshots

Please indicate the level of urgency

We're just now working on getting our Zendesk data into Looker, and we ended up looking into this because we were seeing anomalously large average resolution and reply times - especially reply times - and normal looking medians.

This is something we can probably work around if needed, by filtering on reply counts (for reply times) and ticket status (for resolution times), but it would be really nice not to have to.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Feature] New Zendesk Reply Metrics End Model

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Currently, the models produced from this package are typically at the ticket grain. However, there are various instances where the analysts require a lower level grain to abstract actionable insights from the Zendesk data.

Therefore, this Feature Request is to create a new end model (using some logic from the intermediate models) to create a report at the reply grain. This will allow analysts to see each reply for a certain ticket with relevant business and calendar metrics.

Describe alternatives you've considered

PR #83 is a great start to addressing this new end model.

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

[Bug] Agent Work Time in Business Minutes (zendesk__ticket_metrics)

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We are evaluating the inclusion of schedules with v0.11.0 and noticed that agent_work_time_in_business_minutes for some tickets is showing higher than the full_resolution_business_minutes on the zendesk__ticket_metrics model. In these situations, the number is also higher than our existing production values using the v0.10.2 of the package.

We dug in and it looks like the issue is within the int_zendesk__ticket_work_time_business model.

The intercepted_periods cte which joins across the weekly_periods and schedule ctes seems to be where the issue takes place.

It is joining the status the ticket was in, going into the weekend, to both the week before (a holiday week) and the week after (non-holiday) and then counting that time twice (or multiple times if ticket was in a status over multiple schedules changes).

Relevant error log or model output

No response

Expected behavior

If a ticket status spans a weekend, in which the schedule changes, I would expect the ticket's status time to be properly aligned/split to each of those schedules.

dbt Project configurations

n/a

Package versions

packages:

• package: dbt-labs/dbt_utils
  version: 1.0.0
• package: fivetran/fivetran_log
  version: 0.7.4
• package: fivetran/jira
  version: 0.12.2
• package: fivetran/github
  version: 0.7.0
• package: dbt-labs/codegen
  version: 0.9.0
• package: fivetran/zendesk
  version: 0.11.0

What database are you using dbt with?

bigquery

dbt Version

Core:

• installed: 1.3.0

Plugins:

• bigquery: 1.3.0

Additional Context

This is a simple example ticket, that was opened at the end of a holiday week on Friday, after our business hours, then worked/solved on the following Monday.

The time it was open on Monday, seems to be applied twice, once to the prior schedule and then the current.

Ticket 110116-example.xlsx

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

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

[Improvement] too many partitions in int_zendesk__field_history_pivot on bigquery

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I am getting partly the same error as reported in issue #39, which is closed and merged in #47

I think the problem was fixed for the int_zendesk__field_calendar_spine model but not for int_zendesk__field_history_pivot. The fix introduced a config variable, ticket_field_history_timeframe_years to limit the number of partitions, but that config variable is not used in int_zendesk__field_history_pivot.
I believe all other partitioned models use the date spine and get their date range limited by that.

I hacked the model and added the config variable to the where condition in the first cte, it seems to do the trick. however I am not 100% sure what side effects this might have in the data.

with field_history as (

    select
        ticket_id,
        field_name,
        valid_ending_at,
        valid_starting_at

        --Only runs if the user passes updater fields through the final ticket field history model
        {% if var('ticket_field_history_updater_columns') %}
        ,
        {{ var('ticket_field_history_updater_columns') | join (", ")}}

        {% endif %}

        -- doing this to figure out what values are actually null and what needs to be backfilled in zendesk__ticket_field_history
        ,case when value is null then 'is_null' else value end as value

    from {{ ref('int_zendesk__field_history_enriched') }}
    where
        cast( {{ dbt.date_trunc('day', 'valid_starting_at') }} as date) >=
            {{ dbt.dateadd('year', - var('ticket_field_history_timeframe_years', 50), dbt.current_timestamp_backcompat() ) }}
            
    {% if is_incremental() %}
        and cast( {{ dbt.date_trunc('day', 'valid_starting_at') }} as date) >= (select max(date_day) from {{ this }})
    {% endif %}
)

where I added this bit, patched together from your code in the date spine.

cast( {{ dbt.date_trunc('day', 'valid_starting_at') }} as date) >=
            {{ dbt.dateadd('year', - var('ticket_field_history_timeframe_years', 50), dbt.current_timestamp_backcompat() ) }}

this limits the amount of data we can analyse, to fix this properly I think the tables should be partitioned by month. (but 10 years is good enough for us...)

Relevant error log or model output

03:30:56  Completed with 1 error and 0 warnings:
03:30:56  
03:30:56  Database Error in model int_zendesk__field_history_pivot (models/ticket_history/int_zendesk__field_history_pivot.sql)
03:30:56    Resources exceeded during query execution: Table datawarehouse-328110:dbt_dwh_zendesk_intermediate.int_zendesk__field_history_pivot will have 4001 partitions when the job finishes, exceeding limit 4000. If partitions were recently expired, it may take some time to be reflected unless explicitly deleted.
03:30:56    compiled Code at target/run/zendesk/models/ticket_history/int_zendesk__field_history_pivot.sql

Expected behavior

I expected the config variable to limit the date range in int_zendesk__field_history_pivot.

dbt Project configurations

at the time of running:

vars:
  zendesk:
    ticket_field_history_timeframe_years: 1

models:
  zendesk:
    +schema: zendesk
    intermediate:
      +schema: zendesk_intermediate
    sla_policy:
      +schema: zendesk_intermediate
    ticket_history:
      +schema: zendesk_intermediate
    utils:
      materialized: table

materializing utils as table was added during my hacking, bigquery fails on too much computing on too little data unless you do this. Same thing happened in the original issue.

Package versions

• package: fivetran/zendesk
  version: [">=0.9.0", "<0.11.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt cloud does no allow running dbt --version, but it's 1.3.

Additional Context

No response

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

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

BUG - runs fail with error in int_zendesk__field_history_pivot

Are you a current Fivetran customer?
Yes. Maria Riolo, Data Analyst at Zearn

Describe the bug

Upon running, we get the error:

Database Error in model int_zendesk__field_history_pivot (models/ticket_history/int_zendesk__field_history_pivot.sql)
  syntax error at or near "pivot"
  LINE 123: ), pivot as (
               ^

Our daily runs started failing Dec 10, 2021, but at the time we hadn't made any changes to our own code since Dec 3.

Steps to reproduce

dbt run -m int_zendesk__field_history_pivot

(or just dbt run)

Project variables configuration

# 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: 'zearn_redshift'
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: 'zearn-redshift'

# 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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]

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

on-run-start:
  # create schemas so we don't run into errors with the on-run-end hooks
  # if only doing a partial build or e.g. just running dbt seed
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_marts\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_staging\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_exports\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_zendesk_staging\""
  - "CREATE SCHEMA IF NOT EXISTS \"{{ target.schema }}_ad_hoc\""

on-run-end:
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_marts\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_exports\" to jitterbit_sfdc_export, group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_staging\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT USAGE ON SCHEMA \"{{ target.schema }}_zendesk_staging\" to group looker, group zearn_analytics, group zearn_product"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_marts\" TO group looker, group zearn_analytics, group zearn_product"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_exports\" TO jitterbit_sfdc_export, group zearn_analytics"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_staging\" TO group zearn_analytics, group looker"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_ad_hoc\" TO group zearn_analytics, group looker"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA \"{{ target.schema }}_zendesk_staging\" TO group zearn_analytics, group looker"

# You can define configurations for models in the `source-paths` directory here.
# Using these configurations, you can enable or disable models, change how they
# are materialized, and more!

# In this example config, we tell dbt to build all models in the example/ directory
# as views (the default). These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  +bind: false

  zearn_redshift:
    +materialized: view
    staging:
      +materialized: view
      +schema: staging
      classification:
        +materialized: table
        support_tables:
          +materialized: table
      rostering:
        +materialized: table
      usage:
        +materialized: table
        +dist: user_id
        +sort: event_type, event_type_clean, created_at
    marts:
      +materialized: view
      +schema: marts
      summaries:
        +materialized: table
        +dist: user_id
        +sort: daydate
      exports:
        +materialized: table
        +schema: exports
    ad_hoc:
      +materialized: table
      +schema: ad_hoc
  zendesk:
    +schema: staging
    intermediate:
      +schema: staging
    sla_policy:
      +schema: staging
    ticket_history:
      +schema: staging
vars:
  school_years: ['2012-2013', '2013-2014', '2014-2015', '2015-2016', '2016-2017', '2017-2018', '2018-2019', '2019-2020', '2020-2021', '2021-2022']
  rostering_school_years: ['2016-2017', '2017-2018', '2018-2019', '2019-2020', '2020-2021', '2021-2022']
  random_seed: 3655122572989899184
  burbio_table_date: "2020-01-01" #default value needed to generate docs
  burbio_file_name: fake_file.csv #default value needed to generate docs
  district_student_info_district_id: 0 #default value needed to generate docs
  zendesk_source:
    zendesk_database: production
    zendesk_schema: ft_zendesk

Package Version

packages:
  - package: dbt-labs/dbt_utils
    version: [">=0.3.0"]
  - package: fivetran/zendesk
    version: [">=0.7.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Please indicate the level of urgency
We do use our zendesk data. It's not an emergency right now, but it would be a big help to get it resolved before the end of the week so that our nightly syncs will be up and running over our winter break.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Merged tickets don't follow same logic as Zendesk explore

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi,

I noticed an issue with tickets merged to another one. When we merge a ticket in Zendesk to another one, it closes it and put the solved date as the merge date.
But if I check in the data base using Fivetran dbt model, I get some different numbers:


The solved date is different and the resolution minutes too.
Also, it happens sometimes that my solved date and/or my resolution minutes are null when they are not in Zendesk explore.

Relevant error log or model output

No response

Expected behavior

I would expect the numbers to match to Zendesk explore.

dbt Project configurations

vars:
surrogate_key_treat_nulls_as_empty_strings: true
using_domain_names: False #Disable if you are not using domain names
using_organization_tags: False #Disable if you are not using organization tags
zendesk__ticket_passthrough_columns: [custom_contact_reason,custom_coupon_code,custom_order_number,custom_carrier,custom_client_contact_reason_no_order,custom_client_contact_reason_order]

Package versions

packages:

package: dbt-labs/codegen
version: ['>=0.7.0', <0.10.0]
package: dbt-labs/dbt_utils
version: ['>=0.8.0', '<2.0.0']
package: fivetran/zendesk
version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt Version: 1.4

Additional Context

No response

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

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

Integer overflow error with zendesk__ticket_backlog model on Redshift

Hi all. We are trying to set up the Zendesk package but running into an error. When running the zendesk__ticket_backlog model, we get an Integer Overflow error message. It looks like our Zendesk user ID is a bigint, but there is logic in the package casting the field to Integer during a join.

We are on Redshift. Full error message is below:

Completed with 1 error and 0 warnings:
Database Error in model zendesk__ticket_backlog (models/zendesk__ticket_backlog.sql)
  Overflow (Integer valid range -2147483648 to 2147483647)
  DETAIL:  
    -----------------------------------------------
    error:  Overflow (Integer valid range -2147483648 to 2147483647)
    code:      1207
    context:   Value: '12415410808', Character: '8'
    query:     21488955
    location:  :0
    process:   query6_84_21488955 [pid=0]
    -----------------------------------------------
  compiled SQL at target/run/zendesk/models/zendesk__ticket_backlog.sql

I can fix locally by removing the cast(... as {{ dbt_utils.type_int() }}) statements in dbt_modules/zendesk/models/zendesk__ticket_backlog.sql (starting on line 75), but I'm not sure if the casts in there for a reason.

Thanks in advance for your help! @annichia fyi.

[Feature] Improve first schedule identification

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Current model (/fivetran-dbt/dbt_zendesk/models/intermediate/int_zendesk__ticket_schedules.sql) does the following to determine which schedule is first after the creation of the ticket

 select
    ticket.ticket_id,
    ticket.created_at as schedule_created_at,
    '{{default_schedule_id}}' as schedule_id
  from ticket
  left join ticket_schedule as first_schedule
    on first_schedule.ticket_id = ticket.ticket_id
    and {{ fivetran_utils.timestamp_add('second', -5, 'first_schedule.created_at') }} <= ticket.created_at
    and first_schedule.created_at >= ticket.created_at    
  where first_schedule.ticket_id is null

But this particular join clause uses a somewhat complicated logic to ensure we are getting the first schedule created at the time the ticket is created.

and {{ fivetran_utils.timestamp_add('second', -5, 'first_schedule.created_at') }} <= ticket.created_at and first_schedule.created_at >= ticket.created_at`

Some users have noted that this logic limits it to an = operation on the where clause as DATEADD(SECOND, -5, first_schedule.created_at)>= ticket.created_at and first_schedule.created_at <= ticket.created_at becomes = ticket.created_at by simple math.

Request to explore alternate approaches to this logic, e.g., maybe a window function might be more appropriate in identifying the first ticket schedule.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

Compilation fails for snowflake destinations

When I attempt to run the zendesk_ticket_metrics model and all upstream models
dbt run -m +zendesk_ticket_metrics

The job fails with the following error

Database Error in model stg_zendesk_group (models/stg_zendesk_group.sql)
001003 (42000): SQL compilation error:
syntax error line 5 at position 33 unexpected 'group'.
syntax error line 7 at position 0 unexpected ')'.
compiled SQL at target/run/zendesk_source/models/stg_zendesk_group.sql

This is because the keyword group isn't escaped with quotes causing issues in snowflake.
Locally I was able to get it to run by modifying the src_zendesk.yml file
to include an identifier which allowed the variable lookup to produce properly formatted snowflake sql.

    - name: group
    **identifier: '"GROUP"'**
    description: >
      When support requests arrive in Zendesk Support, they can be assigned to a Group. Groups serve as the core
      element of ticket workflow; support agents are organized into Groups and tickets can be assigned to a Group
      only, or to an assigned agent within a Group. A ticket can never be assigned to an agent without also being 
      assigned to a Group.
    freshness: null
    columns:
      - name: id
        description: Automatically assigned when creating groups
        tests:
            - unique
            - not_null 
      - name: name
        description: The name of the group

I am unable to test if this will cause issues with other warehouse destinations (ie Redshift, etc)

[Bug] < int_zendesk__schedule_spine > may be missing rows after DT ends

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I have spent a good deal of time trying to figure out why the table sla_policy only has rows as recent as Oct 30 2022.

After digging, I found the intermediate table called int_zendesk__schedule_spine, which only has rows with values for "valid until" up to Oct 30 2022 as well. All other tables have seemingly valid data such as "sla policty applied" and "time zones".

Anyone else experiencing this issue?

Relevant error log or model output

No response

Expected behavior

I expect the model to give rows for all recently applied SLAs in the transformed table zendesk__sla_policies.

dbt Project configurations

vars:
    #Shopify Vars
    shopify_union_schemas: ['shopify_tbv','shopify_tel','shopify_tna']

    #Zendesk Vars
    #using_schedules:            False         #Disable if you are not using schedules
    #using_domain_names:         False         #Disable if you are not using domain names
    using_user_tags:            False         #Disable if you are not using user tags
    #using_ticket_form_history:  False         #Disable if you are not using ticket form history
    #using_organization_tags:    False         #Disable if you are not using organization tags

Package versions

packages:

• package: fivetran/shopify
  version: [">=0.6.0", "<0.7.0"]
• package: fivetran/netsuite
  version: [">=0.6.0", "<0.7.0"]
• package: fivetran/zendesk
  version: [">=0.9.0", "<0.10.0"]

What database are you using dbt with?

bigquery

dbt Version

version: '1.0.0'

Additional Context

No response

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

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

FEATURE - Add number of ticket handoffs to zendesk__ticket_metrics

Are you a Fivetran customer?
I work at Fivetran :)

Is your feature request related to a problem? Please describe.
This is a request to add a new metric to zendesk__ticket_metrics, which is the number of ticket handoffs. This is defined as number of distinct agents who touched (commented) on the ticket.

Describe the solution you'd like
I believe this could be calculated within the int_zendesk__comment_metrics model and then surfaced into zendesk__ticket_metrics.

Describe alternatives you've considered
I would compute this myself in our own zendesk data model, but would prefer if I could get it from a package instead for consistency.

Additional context

Please indicate the level of urgency and business impact of this request

At Fivetran, tracking the efficiency of the support team is important to us. This would allow us to understand how many different touches it takes to close tickets, whether that is a pattern among certain types of tickets, and organize our Support teams accordingly.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this work implemented.
• No, I'd prefer if someone else did this. I don't have the time and/or don't know how to incorporate the changes necessary.

If this is quicker to do for the solutions team, that would be great, otherwise I would be happy to open a PR with some help!

[Feature] support for multiple zendesk connectors

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Feature to use this package for multuple Zendesk connectors simultaneously.

This feature is already implemented in dbt-xero packages: unioning-multiple-xero-connectors

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

BUG - Too many partitions produced by query, allowed 4000, query produces at least 4272 partitions

Are you a current Fivetran customer?
Yes; Chaskin Saroff, Data Engineer at Bstock

Describe the bug
Too many partitions produced by int_zendesk__field_calendar_spine and int_zendesk__field_history_pivot.

Database Error in model int_zendesk__field_calendar_spine (models/ticket_history/int_zendesk__field_calendar_spine.sql)
  Too many partitions produced by query, allowed 4000, query produces at least 4272 partitions
  compiled SQL at target/run/zendesk/models/ticket_history/int_zendesk__field_calendar_spine.sql

Database Error in model int_zendesk__field_history_pivot (models/ticket_history/int_zendesk__field_history_pivot.sql)
  Too many partitions produced by query, allowed 4000, query produces at least 4022 partitions
  compiled SQL at target/run/zendesk/models/ticket_history/int_zendesk__field_history_pivot.sql

Steps to reproduce
Have more than 4000 days worth of field history data in zendesk while using bigquery as your data warehouse.

Expected behavior
No error should occur.

Project variables configuration

# 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: 'bstock'
version: '0.0.1'
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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
models:
  +persist_docs:
    relation: true
    columns: true
target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"
# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the marketplace/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  marketplace:
      # Applies to all files under models/marketplace/
      marketplace:
          materialized: table


vars:
    'dbt_date:time_zone': 'America/Los_Angeles'

Package Version

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.4
  - package: fivetran/zendesk
    version: 0.4.0
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fishtown-analytics/spark_utils
    version: 0.1.0
  - package: calogica/dbt_date
    version: 0.2.5

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Please indicate the level of urgency
Not urgent, but the bug is frustrating because my build fails constantly if I leave this package installed.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Zendesk Package Prevents Docs from Building in Slim CI in dbt Cloud

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

An error in the zendesk package prevents the docs from building if the models in the package aren't run by the PR.

@fivetran-joemarkiewicz suggested a fix in the dbt slack: https://getdbt.slack.com/archives/C01D1R2JLLA/p1659536144595479?thread_ts=1659463574.617639&cid=C01D1R2JLLA

It looks like this fix was implemented: 0933f9e

and released:
https://github.com/fivetran/dbt_zendesk/releases/tag/v0.9.1

I've upgraded to the new package version but I'm still seeing the error.

Relevant error log or model output

16:35:58  Running with dbt=1.2.3
16:35:59  Found 297 models, 323 tests, 8 snapshots, 1 analysis, 914 macros, 1 operation, 11 seed files, 221 sources, 0 exposures, 2 metrics
16:35:59  
16:36:01  Concurrency: 4 threads (target='default')
16:36:01  
Runtime Error
  Database Error in model int_zendesk__calendar_spine (models/utils/int_zendesk__calendar_spine.sql)
    002003 (42S02): SQL compilation error:
    Object 'ANALYTICS.DBT_CLOUD_PR_44565_386_STG.STG_ZENDESK__TICKET' does not exist or not authorized.

Expected behavior

Docs should build on CI.

dbt Project configurations

 zendesk:
    +schema: 
    +materialized: table
    agent_work_time:
      +schema: stg
    intermediate:
      +schema: stg
    reply_times:
      +schema: stg
    resolution_times:
      +schema: stg 
    sla_policy:
      +schema: stg
    ticket_history:
      +schema: stg
  zendesk_source:
    +schema: stg

Package versions

• package: fivetran/zendesk
  version: 0.9.1

What database are you using dbt with?

snowflake

dbt Version

1.2.0

Additional Context

No response

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

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

BUG - Backlog counts are incorrect

Are you a current Fivetran customer?
Yes. My name is Garegin -- I am employed at Fivetran :)

Describe the bug
zendesk__ticket_field_history and zendesk__ticket_backlog both contain incorrect values for the backlog count. It doesn't seem right that the backlog value is the same across the board for all days.

Expected behavior
The backlog values should differ for each date.

Project variables configuration

Package Version

Warehouse

• BigQuery

[Compatibility] Whitespace Errors while compiling `int_zendesk__field_history_scd`

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The int_zendesk__field_history_scd model file contains an interesting pattern at the top of the model:

1. First, a standard SQL comment (using --)
2. Next, the standard jinja config.
3. Then, a jinja set command with whitespace controls ({%- -%})
4. Finally, the SQL begins with the usual with statement.

Jinja whitespace parsing can cause unexpected issues when compiling to SQL. While this pattern appears to work as expected with dbt's current parsing engine, I've been testing an alternative parsing tool and am finding that the whitespace parsing is leading to the surprising result where the last word of the comment from (1) is being concatenated with the with from (4) resulting in valueswith which obviously causes the sql to fail.

Relevant error log or model output

No response

Expected behavior

While I understand that the team's objective is to build for today's dbt and not other parsing engines, I would still expect to avoid problematic jinja patterns, especially those that result from cosmetic choices like whitespace management. Simply flipping the set command to use {% %} instead should allow all jinja parsers to handle the script successfully.

dbt Project configurations

n/a

Package versions

latest:

version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:

• installed: 1.5.3
• latest: 1.6.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:

• bigquery: 1.5.3 - Up to date!

Additional Context

No response

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

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

BUG - Requester Wait Time metrics have mismatches

Are you a current Fivetran customer?
Yes. My name is Alexei, Analytics Engineer working at Colvin.

Describe the bug
The metric Requester Wait Time is having different numbers compared to the Fivetran UI for the same ticket ids.

Steps to reproduce

1. Go to Zendesk queries editor. Select ticket_id as row, select Requester Wait Time - Business Hours and Requester Wait Time - Calendar hours as metric.
2. Go to the dbt output table zendesk__ticket_metrics and query for the same metrics and the same ticket_ids.
3. The numbers for Requester Wait Time don't match.

Expected behavior
The numbers of the table zendesk__ticket_metrics should match with the Zendesk UI numbers.

Project variables configuration

name: 'dbt_customer_experience'
version: '1.0.0'
config-version: 2

profile: 'customer_experience'

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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"

vars:
  zendesk:
    using_organization_tags: False
  zendesk_source:
    zendesk_database: thecolvin-data
    zendesk_schema: fivetran_zendesk


models:
  dbt_customer_experience:
    sources:
        materialized: ephemeral
    staging:
        schema: staging

  zendesk:
    +schema: staging    
    intermediate:
      +schema: staging
    sla_policy:
      +schema: staging
      materialized: view
    ticket_history:
      +schema: staging
      materialized: view
  zendesk_source:
    +schema: staging
    materialized: view

Package Version

packages:
  - package: fivetran/zendesk
    version: 0.7.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
See the screenshots

Screenshots

Please indicate the level of urgency
The metrics are not matching with the Zendesk UI. The stakeholders are being affected in analysis and decision making.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] failures on dbt run on big zendesk schema

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The table zendesk__ticket_field_history and zendesk__ticket_metrics are not able to incrementally refresh due to size.

When I run dbt run on zendesk package from fivetran I get the log below. it fails on those two tables as they are too big and consume too much resources until refshift can't hold it anymore.

Is there any way I can tune it?

Relevant error log or model output

13:24:06  Completed with 2 errors and 0 warnings:
13:24:06  
13:24:06  Database Error in model zendesk__ticket_field_history (models/zendesk__ticket_field_history.sql)
13:24:06    Query (3528505) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06    DETAIL:  
13:24:06      -----------------------------------------------
13:24:06      error:  Query (3528505) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06      code:      1078
13:24:06      context:   Query (3528505) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06      query:     0
13:24:06      location:  wlm_query_action.cpp:156
13:24:06      process:   wlm [pid=25731]
13:24:06      -----------------------------------------------
13:24:06    compiled SQL at target/run/zendesk/models/zendesk__ticket_field_history.sql
13:24:06  
13:24:06  Database Error in model zendesk__ticket_metrics (models/zendesk__ticket_metrics.sql)
13:24:06    Query (3529173) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06    DETAIL:  
13:24:06      -----------------------------------------------
13:24:06      error:  Query (3529173) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06      code:      1078
13:24:06      context:   Query (3529173) cancelled by WLM abort action of Query Monitoring Rule "Max_Query_Mem_On_Disk".
13:24:06      query:     0
13:24:06      location:  wlm_query_action.cpp:156
13:24:06      process:   wlm [pid=25731]
13:24:06      -----------------------------------------------
13:24:06    compiled SQL at target/run/zendesk/models/zendesk__ticket_metrics.sql

Expected behavior

To be able to create and incrementally refresh those tables.

dbt Project configurations

models:
fivetran_dbt:
zendesk:
+schema:
intermediate:
+schema:
sla_policy:
+schema:
ticket_history:
+schema:
zendesk_source:
+schema:

vars:
using_schedules: False
zendesk_source:
zendesk_database: dkall
zendesk_schema: zendesk
zendesk:
ticket_field_history_extension_months: 6

Package versions

packages:

• package: fivetran/zendesk_source
  version: [">=0.6.0", "<0.7.0"]

• package: fivetran/zendesk
  version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

redshift

dbt Version

Core:

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

Plugins:

• postgres: 1.1.0 - Up to date!
• redshift: 1.1.0 - Up to date!

Additional Context

No response

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

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

[Open investigation] Understanding Zendesk agent replies metric edge cases

We are looking for input on the Zendesk agent replies metric. Currently we believe that our model accounts for agent replies in most situations (see our latest PR).

But we did find discrepancies between the Zendesk Explore numbers and ticket reply numbers on our data occasionally. Here were two edge cases we encountered:

• If an agent creates a ticket with an internal comment, and then replies to that ticket, is that internal comment the ticket being created and the external reply logged as a legitimate reply? We were seeing an undercount of replies by 1 in our own modelling compared to the number of replies in a ticket in the Zendesk Explore.

• How replies are logged to tickets that are follow up to tickets. Does a new ticket log a reply from an old ticket as a reply, or the first comment created? We were seeing an overcount of replies by 1 in our own modelling in the Zendesk explore, because our model can bring in those agent comments from merged tickets.

Have you encountered these issues as well? If you have, or have seen any differences between the Zendesk replies on your internal data vs our own, please let us know in the comments!

[Feature] ADD Databricks support to package

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Let the dbt package support a Databricks destination

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

BUG - First Reply Time metrics have mismatches

Are you a current Fivetran customer?
Yes. My name is Alexei, Analytics Engineer working at Colvin.

Describe the bug
The metrics First Reply Time for both, business hours and calendar hours, are having different numbers compared to the Fivetran UI for the same ticket ids.

Steps to reproduce

1. Go to Zendesk queries editor. Select ticket_id as row, select First Reply Time - Business Hours and First Reply Time - Calendar hours as metric.
2. Go to the dbt output table zendesk__ticket_metrics and query for the same metrics and the same ticket_ids.
3. The numbers for First Reply Time don't match.

Expected behavior
The numbers of the table zendesk__ticket_metrics should match with the Zendesk UI numbers.

Project variables configuration

name: 'dbt_customer_experience'
version: '1.0.0'
config-version: 2

profile: 'customer_experience'

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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"

vars:
  zendesk:
    using_organization_tags: False
  zendesk_source:
    zendesk_database: thecolvin-data
    zendesk_schema: fivetran_zendesk


models:
  dbt_customer_experience:
    sources:
        materialized: ephemeral
    staging:
        schema: staging

  zendesk:
    +schema: staging    
    intermediate:
      +schema: staging
    sla_policy:
      +schema: staging
      materialized: view
    ticket_history:
      +schema: staging
      materialized: view
  zendesk_source:
    +schema: staging
    materialized: view

Package Version

packages:
  - package: fivetran/zendesk
    version: 0.7.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
In the screenshots you can see the mismatches and the workaround to fix the problem in calendar hours. But we need also a solution for Business hours.

Screenshots

Please indicate the level of urgency
The metrics are not matching with the Zendesk UI. The stakeholders are being affected in analysis and decision making.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Disabling Zendesk Schedules doesn't work

We don't use Schedules in Zendesk, so I set the variable using_schedules to False.

However, this package still doesn't compile, with the following error

Database Error in model stg_zendesk_ticket_schedule (models/stg_zendesk_ticket_schedule.sql)
  002003 (42S02): SQL compilation error:
  Object 'FIVETRAN_DATABASE.ZENDESK.TICKET_SCHEDULE' does not exist or not authorized.
  compiled SQL at target/run/zendesk_source/models/stg_zendesk_ticket_schedule.sql

The table TICKET_SCHEDULE in fact does not exist. Shouldn't setting the using_schedules make up for this?

Zendesk Ticket field history null value for tomorrow

Null values for either today or tomorrow. Need to dig in here and fix

[Bug] Fivetran Zendesk package references an old status for on hold tickets

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

See slack thread here for reference: https://getdbt.slack.com/archives/C01D1R2JLLA/p1695159048651189

The model int_zendesk__requester_wait_time_filtered_statuses looks for a status value = 'on-hold' . It turns out that this value doesn't exist in Zendesk. It is 'hold'.

https://github.com/fivetran/dbt_zendesk/blob/v0.11.2/models/sla_policy/requester_wait_time/int_zendesk__requester_wait_time_filtered_statuses.sql#L30

Relevant error log or model output

No response

Expected behavior

The filtered status looks for valid statuses in Zendesk, in this case, 'hold' instead of 'on-hold'

dbt Project configurations

N/A

Package versions

• package: fivetran/zendesk
  version: 0.10.0

What database are you using dbt with?

snowflake

dbt Version

1.3

Additional Context

No response

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

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

Reply/resolution times in business hours not matching up with Zendesk

When comparing reply/resolution times in business hours from the zendesk_ticket_metrics model to those in Zendesk Explore or the Zendesk Support API, the metrics do not match for the majority of tickets.

A couple of issues:

1. Time in business hours is calculated in both dbt and Zendesk, but does not match
2. Time in calendar and business hours is calculated in Zendesk, but time in business hours is null in dbt

Is fivetran planning to add the ticket_metrics table from the Zendesk Support API? A direct connection would be great to have, as Zendesk provides these business hour metrics.

BUG - UTC Timezones for schedules need to be reworked

Are you a current Fivetran customer?

Fivetran created PR

Describe the bug

It was found that the start_time_utc and end_time_utc fields within the ticket_schedules table are no longer accurate. We will want to add the daylight_savings and timezone tables to the package and leverage the start_time and end_time fields for the schedules.

We will then convert these schedules to UTC using the two new tables appropriatley.

Expected behavior

UTC values are accurate.

[Bug] Multi-touch ticket resolution logic could be adjusted

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Initially brought to our attention by @tonytusharjr

The current logic within the zendesk__ticket_metrics model that calculates the multi-touch resolution could to be updated to ensure a ticket doesn't have both double and multi touch results. Below is the logic in question.

dbt_zendesk/models/zendesk__ticket_metrics.sql

Lines 125 to 128 in ed7118b

	case when lower(ticket_enriched.status) in ('solved','closed') and not ticket_comments.is_one_touch_resolution
	then true
	else false
	end as is_multi_touch_resolution

My one thought to not making this update is that multi-touch tickets are in fact also double touch tickets. We should confirm with the Zendesk metrics that these two types of tickets are inherently different before addressing this within the package.

Relevant error log or model output

SELECT * 
FROM `dbt_zendesk.zendesk__ticket_metrics` 
where is_multi_touch_resolution and is_two_touch_resolution

The above result may end with multiple records due to the current logic.

Expected behavior

Our package matches the metric definition defined by Zendesk for multi-touch ticket resolution.

dbt Project configurations

N/A

Package versions

packages:

• package: fivetran/zendesk
  version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

bigquery

dbt Version

v1.1.0

Additional Context

• See PR #74 for the work already done to address this issue.
• See the Zendesk Metrics community doc for details on how these metrics should be calculated.

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

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

BUG - SLA_policies model doesn't have correct is_sla_breach

Are you a current Fivetran customer?
I work at Fivetran :)

Describe the bug
zendesk__sla_policies doesn't always correctly identify SLA breaches (missed SLAs).

Steps to reproduce
For example ticket_id = 18717

select *
from `digital-arbor-400.dbt_pkg_zendesk.zendesk__sla_policies`
where metric = 'first_reply_time' and sla_policy_name in ('Enterprise SLA - Support', 'Standard/Starter SLA - Support') and is_sla_breach and ticket_id = 18717

is breached but in Zendesk, it is achieved

Other ticket_id's where is_sla_breach is true when it should be false (these are all "first_reply_time" metrics)
19505
19539
19544

Expected behavior

Project variables configuration

name: 'prj_production'
version: '1.0.0'
config-version: 2

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

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

on-run-start:
    - '{{create_udfs()}}' # Hook that executes macro to create UDFs on run start (look inside the macros folder)

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
  +persist_docs:
      relation: true
      columns: true
  prj_production:
      materialized: view
  zendesk:
    +schema: 'dbt_pkg_zendesk'
  zendesk_source:
    +schema: 'dbt_pkg_zendesk'

# This section is used for variables for dbt packages
vars: 
  zendesk_source:
    zendesk_schema: zendesk_new
    using_schedules: false        # Disable if you do not have the schedule and ticket_schedule tables, or if you do not want metrics reported in business hours
  zendesk:
    using_schedules: false        # Disable if you do not have the schedule and ticket_schedule tables, or if you do not want metrics reported in business hours

Package Version

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.6
  - git: https://github.com/fivetran/dbt_zendesk.git
    # TODO once the production version zendesk package incremental strategy is verified, we need to swap back to that version
    revision: fix/no-incremental-field-history
    warn-unpinned: false
  - package: fivetran/github
    version: [">=0.2.0", "<0.3.0"]

Warehouse

• [ x] BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency
We're currently recalculating SLA policy achievement metrics ourselves, but we'd like to transition to use the package if possible. We'll transition to use it as soon as it is accurate.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [ x] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Ephemeral models fail when used with run_query

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The ephemeral materialization of some staging/tmp tables cause failures during the compilation stage. Specifically, int_zendesk__calendar_spine depends on the model stg_zendesk__ticket. However it's not explicit: the referenced table is used for a run_query macro. When that macro runs, it tries to run it on the CTE (__DBT__CTE__STG_ZENDESK__TICKET), and that CTE is not included in the run_query macro's query. Since the table doesn't exist, the DBT compilation fails.

Relevant error log or model output

% dbt compile -m +int_zendesk__calendar_spine
17:50:37  Running with dbt=1.1.0
17:50:37  Unable to do partial parsing because a project config has change
17:50:54  Found 414 models, 579 tests, 0 snapshots, 0 analyses, 651 macros, 4 operations, 3 seed files, 253 sources, 0 exposures, 0 metrics
17:50:54
17:50:55  Concurrency: 4 threads (target='dev')
17:50:55
17:50:59  Encountered an error:
Runtime Error
  Database Error in model int_zendesk__calendar_spine (models/utils/int_zendesk__calendar_spine.sql)
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__STG_ZENDESK__TICKET' does not exist or not authorized.

Expected behavior

I would expect the project to compile without any changes on my end.

dbt Project configurations

My dbt_project.yml contains:

models:
  materialized: ephemeral
  zendesk:
    enabled: true
  zendesk_source:
    enabled: true

Package versions

version 0.8.3, and version 0.6.1 of zendesk_source.

  - package: fivetran/zendesk
    version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

snowflake

dbt Version

Fails with both 1.1.0 and 1.0.3. Didn't try <1.

Additional Context

No response

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

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

"Business Minute" metrics don't take into account daylight savings

Hi there,

I'm hope I'm missing something, but it appears this package doesn't include Fivetran's DAYLIGHT_TIME and TIME_ZONE tables, when converting SCHEDULES from UTC. I've noticed after some QA on tickets created before the most recent daylight savings time, that historical business minute calculations appear to be based off the current date's time zone offset.

I'm referring to these tables from the Fivetran release notes, which I didn't see in this repo:

Am I mistaken? Any help here is really appreciated.

[Bug] Numbers not matching between Agent Replies and count_agent_comments

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi,

While checking the numbers between count_agent_comments using the dbt model in zendesk__ticket_metrics table and the number of agent replies in Zendesk explore, I can see the numbers are not matching.

Do you know why there is such a big discrepancy in numbers?

Relevant error log or model output

No response

Expected behavior

I would expect the numbers from count_agent_commands, count_public_agent_comments or total_agent_replies to match the numbers I get for agent replies in Zendesk explore but none of them are matching.

dbt Project configurations

vars:

Needed to keep the surrogate_key function treating null as empty strings following upgrade of db-utils

surrogate_key_treat_nulls_as_empty_strings: true
using_domain_names: False #Disable if you are not using domain names
using_organization_tags: False #Disable if you are not using organization tags
zendesk__ticket_passthrough_columns: [custom_contact_reason,custom_coupon_code,custom_order_number,custom_carrier,custom_client_contact_reason_no_order,custom_client_contact_reason_order]

Package versions

packages:

• package: dbt-labs/codegen
  version: ['>=0.7.0', <0.10.0]
• package: dbt-labs/dbt_utils
  version: ['>=0.8.0', '<2.0.0']
• package: fivetran/zendesk
  version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt Version: 1.4

Additional Context

No response

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

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

BUG - Package compilation does not respect the `using_organization_tags` field

Are you a current Fivetran customer?
Consultant working with Designs for Health

Describe the bug
When setting the using_organization_tags variable to False I get the following compilation error:

Running with dbt=0.20.1
Encountered an error:
Compilation Error in model int_zendesk__organization_aggregates (models/intermediate/int_zendesk__organization_aggregates.sql)
  Model 'model.zendesk.int_zendesk__organization_aggregates' (models/intermediate/int_zendesk__organization_aggregates.sql) depends on a node named 'stg_zendesk__organization_tag' which is disabled

Looking at your source code, this may be a dbt issue because it looks like the reference is contained inside of an if statement.

Steps to reproduce

1. Download this package and dependencies version 0.6.0 with dbt 0.20.1
2. Set using_organization_tags: False in dbt_project.yml
3. Run dbt run
4. See error

Expected behavior
I expect you'll see this error

Running with dbt=0.20.1
Encountered an error:
Compilation Error in model int_zendesk__organization_aggregates (models/intermediate/int_zendesk__organization_aggregates.sql)
  Model 'model.zendesk.int_zendesk__organization_aggregates' (models/intermediate/int_zendesk__organization_aggregates.sql) depends on a node named 'stg_zendesk__organization_tag' which is disabled

Project variables configuration

name: 'XXXXXXX'
version: '1.0.0'
config-version: 2

profile: 'XXXXXXX'

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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"


vars:
  zendesk_source:
    zendesk_database: designs_for_health
    zendesk_schema: dfhzendesk
    using_organization_tags: False

Package Version

packages:
  - package: fivetran/zendesk
    version: [">=0.6.0", "<0.7.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency
If this doesn't work we'll use something else for Zendesk transformation.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] All 'business minutes' fields don't take into account the schedule_holiday table

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi, I previously had an open ticket with Prasadu (#129459) but he suggested I make another one as he does not deal with the DBT package side of support - hopefully this can reach the right person!

Our issue is that we use the Zendesk Fivetran DBT package shown here: https://hub.getdbt.com/fivetran/zendesk/latest/

It calculates the logic to provide business minutes (e.g. First Resolution Time in Business Minutes) - and I can see the logic given in the package in the interim tables (e.g. int_zendesk__ticket_first_resolution-time_business.sql). Our problem is that this calculation does not take into account holidays.

The holiday logic is available in the schedule_holiday table from Zendesk, and we update this for our own holidays, but it is not taken into account when calculating these columns.

Please can you update the package to correct this logic and use the holiday schedule to calculate time to resolution

Thanks,

Harry

Relevant error log or model output

Mentioned above, but much longer 'Time to First Resolution Business Minutes' is much longer around public holidays like Christmas, which we factor into schedule_holiday but does not feature in the code that calculates these metrics.

Expected behavior

Expect business minutes to take into account holiday schedules. They're much longer over christmas time as it assumes people working over holidays!

dbt Project configurations

name: 'qogita_analytics'
version: '1.4.0'
config-version: 2

Package versions

packages:
- package: fivetran/zendesk
version: [">=0.8.0", "<0.9.0"]

• package: dbt-labs/segment
  version: 0.8.1
• package: fivetran/lever
  version: 0.4.0
• package: fivetran/google_ads
  version: [">=0.8.0", "<0.9.0"]

I have checked patch notes for newer versions and the issue seems to persist!

What database are you using dbt with?

snowflake

dbt Version

Core:

• installed: 1.3.0
• latest: 1.4.5 - 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.3.0 - Update available!

At least one plugin is out of date or incompatible with dbt-core.
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation
harry@Harrys-MacBook-Air dbt-analytics %

Additional Context

No response

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

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

BUG - Issues with timestamptz data type

Are you a current Fivetran customer?
Juan Francisco Briones, Data Engineer, Hopin.

Describe the bug
Apparently some fields from the original Zendesk data are in timestamp with timezone (timestamptz) and that makes the dbt_utils.datediff and dbt_utils.dateadd functions break.

Steps to reproduce
Just run the dbt_zendesk package with date fields in timestamptz.

Expected behavior
You will get the following error in multiple models:

  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.

Project variables configuration

name: 'zendesk_streamyard'
version: '1.0'
config-version: 2
vars:
  using_schedules:            False         #Disable if you are not using schedules
  using_domain_names:         False         #Disable if you are not using domain names
  using_user_tags:            False         #Disable if you are not using user tags
  using_ticket_form_history:  False         #Disable if you are not using ticket form history
  using_organization_tags:    False         #Disable if you are not using organization tags
  zendesk_source:
    zendesk_database: data
    zendesk_schema: zendesk_streamyard

# This setting configures which "profile" dbt uses for this project.
profile: 'hopin-redshift-staging'

# 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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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"

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# We tell dbt to build all the models in the zendesk_streamyard/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  zendesk_streamyard:
    zendesk:
      +schema: zendesk_streamyard_modeled
      +materialized: table
    zendesk_source:
      +schema: zendesk_streamyard_modeled
      +materialized: table

Package Version

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

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
I've already had a conversation with Joseph Markiewicz, Renee Li and Sheri Nguyen from Fivetran. They are aware of the issue and we solved it together casting the fields to timestamp without time zone.

Please indicate the level of urgency
Medium priority. We need this information to create a dashboard for Customer Support team.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
  Joseph from Fivetran can help us as well.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] zendesk__ticket_metrics.first_reply_time_business_minutes ignore schedule

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Comparing the data collect via Fivetran vs the Zendesk report UI; it appears our business schedule is not taken into consideration when calculating the value.

Seems related to: #53

Relevant error log or model output

N/A

Expected behavior

Values in zendesk__ticket_metrics.first_reply_time_business_minutes should consider our schedules and reduce the minutes to first reply.

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: "binti_data_platform"
version: "1.3.2"
config-version: 2

# This setting configures which "profile" dbt uses for this project.
# We default to using local, in the CI and Fivetran, this will be overidden by passing the --profile flag,
# I.E. dbt run --profile
profile: "local" # pass in the --profile flag to overide this when running dbt run

# 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_packages"

vars:
  # Configurations for the dbt fivetran github package
  github_source:
    # This is the project Fivetran dumps data into
    github_database: my-gcp-project
  # GCP project where Fivetran Jira connector loads data
  jira_database: my-gcp-project

  zendesk:
    using_organization_tags: False

  zendesk_source:
    # This is the project Fivetran dumps data into
    zendesk_database: my-gcp-project
    using_organization_tags: False

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
  +database: |
    {%- if target.name == "fivetran" -%}
      my-gcp-project
    {%- else -%}
      {{ target.database }}
    {%- endif -%}
  github:
    github__pull_requests:
      +schema: marts
  zendesk:
    zendesk__ticket_metrics:
      +schema: marts
  binti_data_platform:
    marts:
      +schema: marts
      jira:
        +materialized: table
      github:
        +materialized: table

Package versions

packages:
  # Source Github Models
  # https://github.com/fivetran/dbt_github_source
  - package: fivetran/github_source
    version: [">=0.6.0", "<0.7.0"]
  # Transformational Github Models
  # https://github.com/fivetran/dbt_github
  - package: fivetran/github
    version: [">=0.6.0", "<0.7.0"]
    # Source Jira Models from Fivetran
  - package: fivetran/jira_source
    version: [">=0.6.0", "<0.7.0"]

  # Transformational Zendesk Models
  # https://hub.getdbt.com/fivetran/zendesk/latest/
  - package: fivetran/zendesk
    version: [">=0.10.0", "<0.11.0"]
  - package: fivetran/zendesk_source
    version: [">=0.8.0", "<0.9.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.1

# Choose the version of dbt that will run the project.
# Supported versions are listed in https://fivetran.com/docs/transformations/dbt/setup-guide#prerequisites.
# If the version is not specified, dbt 0.18.0 will be used.
dbtVersion: "1.3.1"

jobs:
  # Give each job a unique name. The name will be displayed in the Fivetran
  # dashboard once your jobs are imported.  Job names must be unique.
  - name: Weekly Github Issue Metrics
    # The name of the target that will be used when running the job. If it's not
    # specified, the target will be named 'prod'.
    targetName: fivetran
    # Define when this job should run, using cron format. This example will run
    # every day at 12:00pm (according to your warehouse timezone). For help with
    # cron formatting, visit https://crontab.guru/.
    schedule: 0 0 * * 0
    steps:
      - name: Install Deps # Give each step in your job a name. This will enable you to track the steps in the logs.
        command: dbt deps # Enter the dbt command that should run in this step. This example will run all your models. For a list of available commands visit https://docs.getdbt.com/reference/model-selection-syntax/.
      - name: Update Github Models
        command: dbt run --select +github__pull_requests +github__pull_request_reviews
      - name: Run Tests
        command: dbt test --select +github__pull_requests +github__pull_request_reviews
  - name: Daily Jira Metrics
    targetName: fivetran
    schedule: 0 0 * * *
    steps:
      - name: Install Deps
        command: dbt deps
      - name: Update Jira Models
        command: dbt run --select +jira__field_change_history +jira__issues +jira__sprints
      - name: Run Tests
        command: dbt test --select +jira__field_change_history +jira__issues +jira__sprints
  - name: Daily Zendesk Metrics
    targetName: fivetran
    schedule: 0 0 * * *
    steps:
      - name: Install Deps
        command: dbt deps
      - name: Update Zendesk Models
        command: dbt run --select +zendesk__ticket_metrics
      - name: Run Tests
        command: dbt test --select +zendesk__ticket_metrics

Additional Context

I spot-checked the data collected vs the data in the Zendesk reports.

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

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

[Question] Duplicate SLA Event IDs

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hey all,

First all thanks so much for a great package. I unfortunately couldn't use it in my environment out of the box but was able to get far enough in converting most of the code to Snowflake SQL to suffice! However, I am running into one unexpected issue.

In zendesk_sla_policies I am getting duplicate sla_events_ids. Here is an example:

• Ticket's schedule is 24/5 M-F
• Ticket Has Business Hour SLA Applied At 01:29 AM UTC
• Next SLA Schedule Start At occurs at 04:00 AM UTC (12 AM ET)
• Agent Replies at 1:33 AM UTC

Because the filtered_reply_times CTE evaluates if the agent_reply_at date is the same as both the sla_schedule_start_at dates both rows are retained and are passed through to the zendesk_sla_policies table.

, filtered_reply_times as (
  select * 
  from lagging_time_block
  where {{ dbt.date_trunc("day", "cast(agent_reply_at as date)") }} = {{ dbt.date_trunc("day", "cast(sla_schedule_start_at as date)") }}
    or ({{ dbt.date_trunc("day", "cast(agent_reply_at as date)") }} < {{ dbt.date_trunc("day", "cast(sla_schedule_start_at as date)") }} and sum_lapsed_business_minutes_new = 0 and sla_breach_at = first_sla_breach_at)

)

While I know I'm not using the out of the box version of the code and thus this may be user error. I was wondering if somebody may be able to point how the code is supposed to solve for this. type of scenario.

The only thing I can think of is perhaps is_breached_during_schedule was supposed to be used to indicate which row to retain. Feels odd that work goes into defining that but is never used.

Relevant error log or model output

No response

Expected behavior

Would expect it to only return one row

dbt Project configurations

I don't have this :(

Package versions

I don't have this :(

What database are you using dbt with?

snowflake

dbt Version

I don't have this :(

Additional Context

No response

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

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

BUG - reply_time fields in ticket_metrics not populating

Are you a current Fivetran customer?
<Yes, Emilie DeCherney, Director of Analytics, Pendo>

Describe the bug

Steps to reproduce
Run example query:
select ticket_id, total_agent_replies, first_reply_time_business_minutes, first_reply_time_calendar_minutes
from pendo-reporting.pendo_zendesk.zendesk__ticket_metrics
where ticket_id = 75162

Expected behavior
Reply times to be populated

Project variables configuration

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!
source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-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"


# Model Configuration
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
    +persist_docs: # Create BigQuery descriptions for objects with resource descriptions.
        relation: True
        columns: True
    analytics:
     # Applies to all Files under models/temp
        materialized: table
        intermediate:
            dataset: staging
        final:
            dataset: final
        staging:
            schema: staging
        views:
            schema: views
        temp:
            schema: temp
    jira_source:
        +schema: staging
    jira:
        +schema: fivetran_jira
        intermediate:
            +schema: staging
    zendesk_source:
        +schema: staging
    zendesk:
        +schema: pendo_zendesk
        agent_work_time:
            +schema: staging
        intermediate:
            +schema: staging
        reply_times:
            +schema: staging
        sla_policy:
            +schema: staging
        ticket_history:
            +schema: staging
        utils:
            +schema: staging
    salesforce_source:
        +schema: staging
    salesforce:
        +schema: fivetran_salesforce
        intermediate:
            +schema: staging

vars:
    jira_schema: fivetran_jira
    jira:
        issue_field_history_columns: [
            "timespent",
            "assignee",
            "lastViewed",
            "summary",
            "resolutiondate",
            "creator",
            "resolution",
            "updated",
            "description",
            "timeoriginalestimate",
            "issuekey",
            "timeestimate",
            "security",
            "parent",
            "priority",
            "created",
            "workratio",
            "statuscategorychangedate",
            "statuscategory",
            "environment",
            "project",
            "issuetype",
            "reporter",
            "components",
            "labels",
            "versions",
            "fixVersions",
            "customfield_12602",  # The custom field for zendesk ticket count
            "customfield_12735", # one of the 'severity' fields
            "customfield_12100", #another 'severity' field
            "customfield_10114", # 'Team'
            "customfield_12801", # 'Team name'
            "customfield_11201", # 'Dev team'
            "customfield_12720", # 'Team'
            "customfield_12760", # 'Value proposition'
            "customfield_12508", # 'Buisness value'
            "customfield_12836" # 'Dev SOC Trust values'
        ]
    zendesk_schema: pendo_zendesk
    salesforce_schema: fivetran_salesforce
    fivetran_log:
        fivetran_log_using_transformations: false # this will disable all transformation + trigger_table logic
        fivetran_log_using_triggers: false # this will disable only trigger_table logic

Package Version

  - package: fivetran/jira_source
    version: 0.2.0
  - package: fivetran/jira
    version: 0.3.0
  - package: fivetran/zendesk_source
    version: 0.3.0
  - package: fivetran/zendesk
    version: 0.4.0
  - package: fivetran/salesforce_source
    version: 0.2.1
  - package: fivetran/salesforce
    version: 0.3.1
  - package: fivetran/fivetran_log
    version: 0.3.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Query results from zendesk__ticket_metrics for example ticket_id (75162) show null reply times, when there is a reply:

For the same ticket, reply is shown in Zendesk UI:

Please indicate the level of urgency
Urgent, this is blocking ability to report accurately through several dashboards for an extended period of time.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

BUG - int_zendesk__calendar_spine doesn't use `zendesk_intermediate` schema

Are you a current Fivetran customer?
Jon Wu, Architect, Paper Culture

Describe the bug
int_zendesk__calendar_spine model is not created in the zendesk_intermediate schema like the other intermediate models.

Steps to reproduce

1. dbt run
2. Check schema

Expected behavior
int_zendesk__calendar_spine uses zendesk_intermediate

Project variables configuration
N/A

Package Version

packages:
  - package: dbt-labs/dbt_utils
    version: 0.7.3
  - package: fivetran/ad_reporting
    version: 0.4.0
  - package: fivetran/facebook_ads
    version: 0.3.0
  - package: fivetran/google_ads
    version: 0.3.1
  - package: fivetran/microsoft_ads
    version: 0.3.0
  - package: fivetran/pinterest
    version: 0.4.0
  - package: fivetran/zendesk
    version: 0.6.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

I believe this happens because this model is in the utils folder and thus doesn't pick up the intermediate config from dbt_project.yml. I'd fix but wasn't sure the best way to handle given how you set things up, and am assuming it's just a quick config change in the file.

Screenshots

Please indicate the level of urgency

Nice to have. We share final model datasets with users, and hide intermediate and staging models from them so they can get straight to the action.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Feature] Support dbt-dry-run

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

We use dbt-dry-run when deploying our dbt code to check for any errors. On a dbt project using this package, dbt-dry-run will fail if you try to run it before the models have been run. Once the models have been run once and generated some data in BigQuery, the dry run will succeed.

Please can you support dbt-dry-run without having to run the models prior to the dry run?

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

One error from the dry runner is this case statement:

dbt_zendesk/models/ticket_history/int_zendesk__field_calendar_spine.sql

Line 24 in 3af26a7

cast( {{ dbt.date_trunc('day', "case when status != 'closed' then " ~ dbt.current_timestamp_backcompat() ~ " else updated_at end") }} as date) as open_until

It complains the two return values are of different types.
No matching signature for operator CASE; all THEN/ELSE arguments must be coercible to a common type but found: STRING, TIMESTAMP; actual argument types (WHEN THEN) ELSE: (BOOL TIMESTAMP) STRING at [162:14]

I believe the fix for this issue is to wrap updated_at with a cast to timestamp, but I've not tested this.

Another set of errors it raises is it can't find any of the fields from ticket_field_history_columns in the schema of upstream models when trying to dry dun model.zendesk.zendesk__ticket_backlog. I imagine the root cause of this is that these fields are not physical columns in the source data. This will require further investigation from someone more familiar with this package.

BUG - SLA policies model failling uniqueness test

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I recently upgraded from v0.5.2 to v0.7.1 of the Zendesk package, and have successfully done a full refresh dbt run of all zendesk models since. I now receive a dbt test failure, saying that the sla_event_id column in the sla_policies model is not unique.

I have debugged this as best I can myself. The issue seems to be caused by a change introduced in v0.7.1 (absent from v0.7.0, still present in v0.8.0).

v0.7.0 has this join condition in the int_zendesk__sla_policy_applied model:
and sla_policy_applied.sla_applied_at >= sla_policy_name.valid_starting_at

Since v0.7.1, it has been changed to this:
and sla_policy_applied.sla_applied_at >= {{ fivetran_utils.timestamp_add(datepart='second', interval='-5', from_timestamp='sla_policy_name.valid_starting_at') }}

Relaxing the join condition in this way causes duplication in the join, and ultimately in the final model output, as indicated by the unque test fail.

Relevant error log or model output

[31mCompleted with 1 error and 0 warnings:[0m

[31mFailure in test unique_zendesk__sla_policies_sla_event_id (models/zendesk.yml)[0m
  Got 1406 results, configured to fail if != 0

  compiled SQL at target/compiled/zendesk/models/zendesk.yml/schema_test/unique_zendesk__sla_policies_sla_event_id.sql

Done. PASS=75 WARN=0 ERROR=1 SKIP=0 TOTAL=76

Expected behavior

Successful dbt test, as was happening under v0.5.2.

dbt Project configurations

  zendesk:
    +schema: zendesk
    +tags:
      - daily_04_utc
      - eu
      - prod_only
    intermediate:
      +schema: zendesk_staging
    sla_policy:
      +schema: zendesk_staging
    ticket_history:
      +schema: zendesk_staging

  zendesk_source:
    +schema: zendesk_staging
    +tags:
      - hourly
      - eu
      - prod_only

Package versions

packages:

  - package: dbt-labs/dbt_utils
    version: 0.7.6

  # will also import fivetran/zendesk_source as a dependency
  - package: fivetran/zendesk
    version: 0.7.1

  - package: fivetran/ad_reporting
    version: [">=0.2.2", "<0.6.0"]

  - package: calogica/dbt_expectations
    version: 0.4.7

  - package: dbt-labs/segment
    version: 0.6.0

What database are you using dbt with?

bigquery

dbt Version

installed version: 0.21.1
   latest version: 1.0.0

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

Plugins:
  - bigquery: 0.21.1

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

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

QUESTION - Zendesk__ticket_backlog not working because of invalid identifier ASSIGNEE_ID

Are you a Fivetran customer?
Yes. Jordan Stewart. Sr Data Engineer. Workrise

Your Question
We have been using the Zendesk and Zendesk Source dbt hub packages since the first version was released. We now are trying to upgrade to the newest Zendesk package version, as two new tables are available and we would like to be up to date in general with these packages.

Our issue is with building the new Zendesk__ticket_backlog table/model. It is failing for this:

Database Error in model zendesk__ticket_backlog (models/zendesk__ticket_backlog.sql)
  000904 (42000): SQL compilation error: error line 644 at position 35
  invalid identifier 'TICKET_FIELD_HISTORY.ASSIGNEE_ID'
  compiled SQL at target/run/zendesk/models/zendesk__ticket_backlog.sql

I see that the data in the field_name column from the stg_zendesk_ticket_field_history is pivoted when the value is 'priority', 'assignee_id' or 'status' which is how the assignee_id column is created. This CTE here, __dbt__CTE__int_zendesk__field_history_pivot, returns the assignee_id column and looks great.

However, in this CTE, '__dbt__CTE__int_zendesk__field_history_scd' only 3 columns are returned ,
date_day as valid_from,
ticket_id,
ticket_day_id.

This CTE is then used going forward to further build out the ticket_field_history CTE and alias that is used in the final join where our code is failing:

--Join not needed if fields is not located in variable, otherwise it is included.
left join users as assignee
on assignee.user_id = cast(ticket_field_history.assignee_id as
bigint
)

We have no variables set in our dbt_project.yml, so we are using all default values as defined in the dbt hub Zendesk documentation.

I am confused how this package is intended to work out of the box with no manual code changes or variables set, as the assigne_id column is clearly dropped out after the __dbt__CTE__int_zendesk__field_history_scd CTE is created and referenced forward. I see no bugs or issues about this, and this code clearly works for other people who are using it or else there would be open issues. But at a loss for how to get this working without changing the code.

Additional context
We use Snowflake as our data target

Please indicate the level of urgency and business impact of this request
This is medium urgency and impact. Tracking our ticket backlog is something we don't currently have in place in the data warehouse, and this would be a great insight for our ops team.

BUG - resources exceeded error caused by an order by

Are you a current Fivetran customer?
Mark MacArdle, Bought by Many

Describe the bug
An order by in the stg_zendesk__ticket_field_history model is causing a resources exceed error

Steps to reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior

Project variables configuration

copy dbt_project.yml here

Package Version

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.4  # dbt recommend pinning all packages, `*` doesn't work.

  # will also import fivetran/zendesk_source as a dependency
  - package: fivetran/zendesk
    version: 0.4.1

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency

low

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Config variables values can't be found

Good day

The package execution fails when it tries to execute models that have the following line (one such model is models/sla_policy/agent_work_time_breach/agent_work_time_business_hours_breached.sql):

{{ config(enabled=enabled_vars(['using_sla_policy','using_schedules'])) }}

I've tried to manually set these variables to 'true' in my project's dbt_project.yml file but the package still fails to run. I've also tried the below to no success still

vars:
zendesk_source:
using_schedules: true
using_sla_policy: true
zendesk:
using_schedules: true
using_sla_policy: true

Please kindly assist

BUG - Dependency resolution issue with dbt 0.20

Are you a current Fivetran customer?
Yes; Mike Sluyter, Software Engineer at GetUpside

Describe the bug
Dependency resolution fails with fivetran/zendesk == 0.5.2 and fivetran/zendesk_source >= 0.4.0 with the following error:

❯ ./dbt deps
Running with dbt=0.20.0
Encountered an error:
Version error for package fivetran/zendesk_source: Could not find a satisfactory version from options: ['>=0.4.0', '<0.5.0', '>=0.3.0', '<0.4.0']

It looks like the problem is that this package depends on an older version of zendesk_source causing a conflict. I drew a picture:

         ┌────────┐                                  
         │  Dbt   ├─────────────────────┐            
         │ 0.20.0 │                     │            
         └───┬────┘                     │            
             │                          │            
             │                          │            
             │                          │            
   ┌─────────▼─────────┐   ┌────────────▼───────────┐
   │ fivetran/zendesk  │   │fivetran/zendesk_source │
   │        5.2        │   │         >= 4.0         │
   └─────────┬─────────┘   └────────────────────────┘
             │                                       
             │                                       
             │                                       
             │                                       
┌────────────▼────────────┐                          
│ fivetran/zendesk_source │                          
│  [">=0.3.0","<0.4.0"]   │                          
└─────────────────────────┘                          

I tried just removing the explicit dependency on zendesk_source, but that resulted in other conflicts with fivetran_utils:

❯ ./dbt deps
Running with dbt=0.20.0
Encountered an error:
Version error for package fivetran/fivetran_utils: Could not find a satisfactory version from options: ['>=0.2.0', '<0.3.0', '>=0.2.0', '<0.3.0', '>=0.1.0', '<0.2.0']

Steps to reproduce

1. run dbt deps with packages illustrated below.

Expected behavior
Dependencies successfully resolved.

Project variables configuration

Non relevant variables omitted:

vars:
    zendesk_source:
        zendesk_database: raw
        zendesk_schema: fivetran__zendesk
    hubspot_source:
        hubspot_database: raw
        hubspot_schema: fivetran__hubspot
    # disable a few Hubspot models that cause errors
    hubspot_email_event_forward_enabled: false
    hubspot_email_event_print_enabled: false
    hubspot_engagement_call_enabled: false

Package Version

  - package: fivetran/hubspot
    version: 0.4.0
  - package: fivetran/hubspot_source
    version: 0.4.0
  - package: fivetran/zendesk
    version: 0.5.2
  - package: fivetran/zendesk_source
    version: [">=0.4.0", "<0.5.0"]

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
I'm guessing that this PR resolves the issue: #37 If that's the case, can you us know and when you think that'll get merged and a new version released?

Screenshots

Please indicate the level of urgency
Medium High -- we're trying to update dbt to fix an unrelated issue we're having that's causing our CI/CD runs to fail.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Additional fields required in the ticket_metrics model

Hi

Will you kindly include the below fields in the ticket_metrics model of the dbt package?

• assigned_at
• assignee_stations
• assignee_updated_at
• group_stations
• initially_assigned_at
• last_comment_added_at
• replies
• reopens
• requester_updated_at
• status_updated_at
• agent_wait_time_in_minutes__business
• agent_wait_time_in_minutes__calendar
• reply_time_in_minutes__business
• reply_time_in_minutes__calendar

Thank you

Postgres compatibility and minor bug fixes

Updates required in this Issue include:

Remove trailing comma to avoid run failures with user tags disabled.

dbt_zendesk/models/zendesk__ticket_enriched.sql

Line 116 in 11357f4

organization.name as organization_name,

Address the below Postgres compatible failures in the below models:

• int_zendesk__calendar_spine
• int_zendesk__sla_applied
• int_zendesk__ticket_historical_assignee
• int_zendesk__field_history_scd

BUG - Touch Resolution Logic

Are you a current Fivetran customer?

Matthew Skinner, Data Analytics Architect, Pluralsight, Inc

Describe the bug

When calculating "is_one_touch_resolution" and "is_two_touch_resolution" in model int_zendesk__comment_metrics the cte "final" is selecting these two columns as count_internal_comments where it should be count_agent_comments.

This produces an undesired count for what is considered to be one touch/two touch by including internal only comments in the count. This fix also handles erroneous multi-touch count.

Steps to reproduce

1. run the model as normal
2. See error in data

Expected behavior
see description

Project variables configuration
irrelevant

Package Version
version: 0.4.0

Warehouse
Snowflake

Additional context

Screenshots

Please indicate the level of urgency

Are you interested in contributing to this package?

• [x ] Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[Bug] Comments from former employees are classified as an "external_comment", throwing off downstream metrics

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

This issue is manifesting in zendesk__ticket_metrics.first_reply_time_calendar_minutes being null for tickets where the agent replied.

The underlying issue is stg_zendesk__user.role is end-user for former employees.

Tracing this issue from start to finish.

1. zendesk__ticket_metrics.first_reply_time_calendar_minutes is null
2. int_zendesk__ticket_reply_times_calendar.first_reply_time_calendar_minutes is null
3. int_zendesk__ticket_reply_times has a left join where this happens:

left join ticket_public_comments as agent_comments
    on agent_comments.ticket_id = end_user_comments.ticket_id
    and agent_comments.commenter_role = 'internal_comment'
    and agent_comments.valid_starting_at > end_user_comments.end_user_comment_created_at

In this join, in order for the query to return a value for agent_responded_at, the agent_comments.commenter_role = 'internal_comment'.
4. This is set on int_zendesk__comments_enriched.commentor_role
5. This traces back to STG_ZENDESK__USER, where the user_id in question is listed as a end-user.

Relevant error log or model output

No response

Expected behavior

I would expect the model to account for this user being an employee at the time of response.

dbt Project configurations

name: 'data_modeling'
version: '1.0.0'
config-version: 2

vars:
  zendesk_source:
    zendesk_database: production
    zendesk_schema: goat_zendesk

Package versions

packages:
  - package: fivetran/zendesk
    version: [">=0.9.1", "<=0.9.1"]

What database are you using dbt with?

snowflake

dbt Version

1.1.0

Additional Context

I am not entirely sure this is a modeling issue ... maybe there's something that can be set upstream in zendesk to help resolve.

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

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

[Bug] Cannot compile models until they have been run

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When trying to compile a project using this package, in int_zendesk__ticket_schedules it tries to fetch a default_schedule_id from the materialised data. If you have not already run the models then the table it tries to fetch this from does not exist, and you get an error.

Relevant error log or model output

Runtime Error
  Runtime Error in model int_zendesk__ticket_schedules (models/intermediate/int_zendesk__ticket_schedules.sql)
    404 Not found: Table <gcp_project>:_zendesk_source.stg_zendesk__schedule was not found in location EU

Expected behavior

The models successfully compile without any of the data this package generates already being in place.

dbt Project configurations

No special configuration. I can reproduce this with a fresh project generated by dbt init

Package versions

0.10.0

What database are you using dbt with?

bigquery

dbt Version

Core:

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

Plugins:

• bigquery: 1.4.1 - Up to date!

Additional Context

The code which creates the error is here
https://github.com/fivetran/dbt_zendesk/blob/v0.10.0/models/intermediate/int_zendesk__ticket_schedules.sql#L26-L44

It is wrapped in an {% if execute %}, which I assumed would prevent this from running during a dbt compile, but this does not appear to be working.

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

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

[Feature] Update dbt-date version range

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

The version range for calogica/dbt-date in this package is out of sync with the current version range, [">=0.9.0", "<0.10.0"]. The version dependency prevents others from updating to the latest version.

Describe alternatives you've considered

n/a

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

Link to the package.