fivetran / dbt_zendesk Goto Github PK
View Code? Open in Web Editor NEWFivetran's Zendesk Support dbt package
Home Page: https://fivetran.github.io/dbt_zendesk/#!/overview
License: Apache License 2.0
Fivetran's Zendesk Support dbt package
Home Page: https://fivetran.github.io/dbt_zendesk/#!/overview
License: Apache License 2.0
Null values for either today or tomorrow. Need to dig in here and fix
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
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?
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.
No response
No response
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
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?
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.
n/a
Link to the package.
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
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?
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?
No response
One error from the dry runner is this case statement:
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.
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
N/A
Values in zendesk__ticket_metrics.first_reply_time_business_minutes
should consider our schedules and reduce the minutes to first reply.
# 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
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"]
bigquery
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
I spot-checked the data collected vs the data in the Zendesk reports.
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.
[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
Successful dbt test
, as was happening under v0.5.2.
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
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
bigquery
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
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:
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.
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
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.
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.
Our package matches the metric definition defined by Zendesk for multi-touch ticket resolution.
N/A
packages:
bigquery
v1.1.0
The int_zendesk__field_history_scd
model file contains an interesting pattern at the top of the model:
--
){%- -%}
)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.
No response
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.
n/a
latest:
version: [">=0.10.0", "<0.11.0"]
bigquery
Core:
Your version of dbt-core is out of date!
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation
Plugins:
No response
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
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'.
No response
The filtered status looks for valid statuses in Zendesk, in this case, 'hold' instead of 'on-hold'
N/A
snowflake
1.3
No response
Feature to use this package for multuple Zendesk connectors simultaneously.
This feature is already implemented in dbt-xero packages: unioning-multiple-xero-connectors
No response
No response
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.
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?
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
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?
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.
Updates required in this Issue include:
Remove trailing comma to avoid run failures with user tags disabled.
Address the below Postgres compatible failures in the below models:
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
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?
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!
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.
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.
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
The models successfully compile without any of the data this package generates already being in place.
No special configuration. I can reproduce this with a fresh project generated by dbt init
0.10.0
bigquery
Core:
Plugins:
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.
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.
PR #83 is a great start to addressing this new end model.
No response
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.
zendesk__ticket_metrics.first_reply_time_calendar_minutes
is nullint_zendesk__ticket_reply_times_calendar.first_reply_time_calendar_minutes
is nullint_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
.
No response
I would expect the model to account for this user being an employee at the time of response.
name: 'data_modeling'
version: '1.0.0'
config-version: 2
vars:
zendesk_source:
zendesk_database: production
zendesk_schema: goat_zendesk
packages:
- package: fivetran/zendesk
version: [">=0.9.1", "<=0.9.1"]
snowflake
1.1.0
I am not entirely sure this is a modeling issue ... maybe there's something that can be set upstream in zendesk to help resolve.
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?
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
To be able to create and incrementally refresh those tables.
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
packages:
package: fivetran/zendesk_source
version: [">=0.6.0", "<0.7.0"]
package: fivetran/zendesk
version: [">=0.8.0", "<0.9.0"]
redshift
Core:
Plugins:
No response
Let the dbt package support a Databricks destination
No response
No response
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
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.
Expect business minutes to take into account holiday schedules. They're much longer over christmas time as it assumes people working over holidays!
name: 'qogita_analytics'
version: '1.4.0'
config-version: 2
packages:
- package: fivetran/zendesk
version: [">=0.8.0", "<0.9.0"]
I have checked patch notes for newer versions and the issue seems to persist!
snowflake
Core:
Your version of dbt-core is out of date!
You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation
Plugins:
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 %
No response
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
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
Additional context
Screenshots
Please indicate the level of urgency
low
Are you interested in contributing to this package?
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.
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.
Docs should build on CI.
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
snowflake
1.2.0
No response
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).
No response
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.
n/a
packages:
bigquery
Core:
Plugins:
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.
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?
No response
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.
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]
packages:
bigquery
dbt Version: 1.4
No response
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
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?
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?
No response
I expect the model to give rows for all recently applied SLAs in the transformed table zendesk__sla_policies.
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
packages:
bigquery
version: '1.0.0'
No response
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...)
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
I expected the config variable to limit the date range in int_zendesk__field_history_pivot.
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.
bigquery
dbt cloud does no allow running dbt --version, but it's 1.3.
No response
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
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)
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.
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
dbt run
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
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.
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?
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
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
0.6.0
with dbt 0.20.1
using_organization_tags: False
in dbt_project.yml
dbt run
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
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?
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
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
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.
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?
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
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
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?
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
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
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?
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
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
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?
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.
No response
I would expect the numbers to match to Zendesk explore.
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]
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"]
bigquery
dbt Version: 1.4
No response
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:
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.
No response
Would expect it to only return one row
I don't have this :(
I don't have this :(
snowflake
I don't have this :(
No response
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.
% 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.
I would expect the project to compile without any changes on my end.
My dbt_project.yml
contains:
models:
materialized: ephemeral
zendesk:
enabled: true
zendesk_source:
enabled: true
version 0.8.3, and version 0.6.1 of zendesk_source.
- package: fivetran/zendesk
version: [">=0.8.0", "<0.9.0"]
snowflake
Fails with both 1.1.0
and 1.0.3
. Didn't try <1.
No response
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?
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!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.