DBT Package performance issue

[maliro]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Hi ,
we have an issue with the amount of data sync that lately has increased dramatically. and cause snowflake error 300005 even with a very large compute resources.
we discussed it with dbt Support and they would like to have a call with fivetran support engineer about the Fivetran-dbt JIRA package.
they wonder if it is possible that the Fivetran package is not filtering out the data using IS_ACTIVE=FALSE?

see also our previous Fivetran support request #157901.
can we set a meeting and investigate this issue with you?
dbt support engineer who is responsible for this issue is: Rijesh - [email protected]

Thanks in advanced,
Mali Rodan and Tomer Nahum
[email protected]
[email protected]

Relevant error log or model output

11:48:18  Database Error in model jira__daily_issue_field_history (models/jira__daily_issue_field_history.sql)
11:48:18    000603 (XX000): SQL execution internal error:
11:48:18    Processing aborted due to error 300005:4035471279; incident 7371288.
11:48:18    compiled Code at target/run/jira/models/jira__daily_issue_field_history.sql

Expected behavior

complete run successfully

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: 'rnd_project'
version: '1.0.0'
config-version: 2
vars:
  jira_issue_history_buffer: 12
  issue_field_history_columns: ['customfield_10122','assignee','customfield_10135','customfield_10227','customfield_10079','customfield_10099',
                                'customfield_10136','customfield_10094','customfield_10133','customfield_10138','customfield_10126',
                                'customfield_10137','customfield_10106','customfield_10125','customfield_10096','customfield_10127',
                                'customfield_10139','customfield_10288','customfield_10128','customfield_10130','priority','customfield_10294',
                                'customfield_10295','customfield_10298','customfield_10300','customfield_10055','customfield_10124',
                                'customfield_10131','customfield_10132','customfield_10129','customfield_10134','customfield_10121','reporter',
                                'customfield_10045','customfield_10057','customfield_10329','customfield_10038','summary','customfield_10098',
                                'customfield_10291','customfield_10293','customfield_10286','customfield_10090','customfield_10304',
                                'customfield_10068','customfield_10081','customfield_10046','resolution','customfield_10093',
                                'customfield_10215','customfield_10229','customfield_10237','customfield_10220','customfield_11577','customfield_11578',
                                'customfield_10243','customfield_10037','customfield_10051','timespent','duedate','customfield_10250','customfield_10104',
                                'customfield_10113','customfield_10077','customfield_10253','customfield_11583','customfield_11510',
                                'customfield_10041','customfield_11598','customfield_11576', 'customfield_11605',  'customfield_11602']
  
  jira_database: fivetran_cx
  jira_schema: jira_cx
  github_database: fivetran_cx
  github_schema: github_cx 
  tfs_schema: dwh_jira_cloud_cx
  field_option_local: "{{ source('bi_jira', 'field_option') }}"
  tfsdwhdatatotableau: "{{ source('bi_tfs', 'tfsdwhdatatotableau') }}"
  tfs_history_data: "{{ source('bi_tfs', 'tfs_history_data') }}"
  blackduck_database: blackduck
  blackduck_schema: dwh
  cvs_final: "{{ source('bi_blackduck', 'cvs_final') }}"

models:
  +copy_grants: true
  jira:
    +schema: dwh_jira_cloud_cx
    jira__daily_issue_field_history:
      +pre-hook: 
        - "{{ change_warehouse() }}"
  jira_source:    
    +schema: dwh_jira_cloud_cx  
  github:
    +schema: dwh_github
  github_source:    
    +schema: dwh_github     
  
  rnd_project:
    nice_jira:
      tmp:
        materialized: view
      materialized: table
      schema: dwh_jira_cloud_cx
      jira__daily_issue_field_current_new:
        +pre-hook: 
        - "{{ change_warehouse() }}"
      
    blackduck:
      tmp:
        materialized: view
      materialized: table
      schema: dwh_blackduck

    

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

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

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


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

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

Package versions

packages:

• package: fivetran/jira
  version: 0.7.0

• package: fivetran/github
  version: [">=0.5.0", "<0.6.0"]

What database are you using dbt with?

snowflake

dbt Version

dbt Version: 1.5

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.

[fivetran-avinash]

Hi @maliro, thanks for bringing this to our attention. Let's see if we can dig in here and solve this issue! 🔬

First things first, have you tried upgrading your dbt_jira package? 0.7.0 is a very old version of the package--our latest release is at [0.15.0](https://github.com/fivetran/dbt_jira/releases/tag/v0.15.0). It's possible that we've introduced new logic since then that'll make your model run!

Also, we do not recommend using the above filter. This is a historical model and would lose most of its impact if you filtered out these records.

If the package upgrade still causes the same above issue, can you provide us with additional information so we can investigate further:

• Did this model run successfully in the past?
• What is the size of your warehouse?
• What is the {{ change_warehouse() }} prehook? Could this impact the model and cause the error?
• What is the data volume in your issue field history source?
• Could you elaborate on the recent increase in data volume? Did your source increase in data volume or something else?

Let us know if you have any questions!