search

fivetran / dbt_jira

Data models for Fivetran's Jira connector built using dbt.
https://fivetran.github.io/dbt_jira/
Apache License 2.0
8 stars 15 forks source link

BUG - the file:int_jira__issue_join failed on ambiguous column name 'ISSUE_ID' #39

Closed RivkiHofman closed 3 years ago

RivkiHofman commented 3 years ago

Hi

We use with Jira package: fivetran/jira, version: 0.5.0 As of September 17th the DBT job failed on the file: int_jira__issue_join With an error message: ambiguous column name 'ISSUE_ID' . I investigated this file, and I see that in the cte : issue, the field: issue_id appears twice. In the cte: join_issue there are joins to this field, and I think that the failure is because that.

this is the code of cte: issue

with issue as (

-- including issue_id in here because snowflake for some reason ignores issue_id,
-- so we'll just always pull it out and explicitly select it

select
    **issue_id,**  --first
    coalesce(revised_parent_issue_id, parent_issue_id) as parent_issue_id,

    "ORIGINAL_ESTIMATE_SECONDS",

"REMAINING_ESTIMATE_SECONDS", "TIME_SPENT_SECONDS", "ASSIGNEE_USER_ID", "CREATED_AT", "CREATOR_USER_ID", "ISSUE_DESCRIPTION", "DUE_DATE", "ENVIRONMENT",

"ISSUE_ID", --second

"ISSUE_TYPE_ID", "ISSUE_KEY", "PARENT_ISSUE_ID", "PRIORITY_ID", "PROJECT_ID", "REPORTER_USER_ID", "RESOLUTION_ID", "RESOLVED_AT", "STATUS_ID", "STATUS_CHANGED_AT", "ISSUE_NAME", "UPDATED_AT", "WORK_RATIO", "_FIVETRAN_SYNCED", "REVISED_PARENT_ISSUE_ID", "ISSUE_TYPE", "PARENT_ISSUE_TYPE", "PARENT_ISSUE_NAME", "PARENT_ISSUE_KEY", "IS_PARENT_EPIC"

from BIDEVDB.dwh_jira.int_jira__issue_type_parents

)

Did you do something with this code? we run with this version some weeks, and all was okay, what happened suddenly? Can you check it asap and update us? Now our data don't updated, and our users need it...

Thank for your quick check and response.

Rivki

fivetran-joemarkiewicz commented 3 years ago

Hi @RivkiHofman! I am sorry to hear that your dbt_jira runs have failed with this duplicate ambiguous column name error. Our team has actually not made any updates since the previous release v0.5.0 we worked with you on.

As there is no direct change from our end I wonder if your dbt_project.yml may have had any changes since last running successfully? Do you know if any changes may have been applied? It would be incredibly helpful if you could highlight which warehouse you are using (eg. Snowflake, BigQuery, Redshift) and also paste a fresh copy of your dbt_project.yml so our team can investigate further.

Our team will look more into this when we came back from the weekend on Monday, but if you could provide the above details that will be helpful for us to troubleshoot on Monday. Thanks!

RivkiHofman commented 3 years ago

Hi In our side there are no changes also. My warehouse is snowflake. My dbt_project.yml is :

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_10006','priority','summary','customfield_10116','customfield_10206','customfield_10303','customfield_10111','customfield_10407','customfield_11030', 'customfield_10307'] jira_database: FIVETRAN_DATABASE jira_schema: JIRA tfs_schema: DWH_JIRA 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: jira: +schema: dwh_jira jira_source:
+schema: dwh_jira

rnd_project: nice_jira: tmp: materialized: view materialized: table schema: dwh_jira

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!

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

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.

Thank a lot, I appreciate your suuport.

RivkiHofman commented 3 years ago

dbt_project.yml.txt

RivkiHofman commented 3 years ago

dbt_project_yml.txt

RivkiHofman commented 3 years ago

I see that the code of yml file not clear, I tried attach a file, I hope you can see its.

cgkirici commented 3 years ago

Hi @fivetran-joemarkiewicz, we've also started getting the same ambiguous column error in the same model since 2pm GMT on 17th September. We're using Snowflake and there hasn't been any changes to our dbt_project.yml file for the last 3 weeks.

fivetran-joemarkiewicz commented 3 years ago

Hi @RivkiHofman and @cgkirici

I did some investigating and think I found the problem. It looks like exactly three days ago dbt-labs released a patch to their dbt_utils package which updated the dbt_utils.star() macro which this package utilizes.

My suspicion is the change the dbt-labs team made is lowercasing the exception argument, and while Snowflake has uppercase by default it is not excluding them appropriately. I wonder if we could make a quick update to lowercase the exclusion before calling the dbt_utils macro?

Our team will work on a branch today and let you know here once it is ready so you may test and see if it resolves the issue.

fivetran-joemarkiewicz commented 3 years ago

@RivkiHofman and @cgkirici

It looks like our team actually had a fix in the logic of this package to accomodate the bug in dbt-utils previously and the patch they released three days ago fixed that bug, but in turn broke our workaround. That means to fix this, I simply needed to remove our workaround logic. The below branch is working on my end, but would be great if you two could add this in place in your packages.yml before we merge and release this hot fix.

packages:
    - git: https://github.com/fivetran/dbt_jira.git
      revision: hotfix/sf-star-update
      warn-unpinned: false

Let me know if you encounter any other issues.

fivetran-joemarkiewicz commented 3 years ago

@RivkiHofman and @cgkirici

Thanks again for raising this issue to our teams attention! I have been able to merge the above PR which fixes this bug. Additionally, I have cut a new release of the dbt_jira package v0.5.1 that you will be able to use once it is live on the dbt hub at the top of the hour.

Please feel free to reach out or open a new issue if you experience any other issues or if you have any questions.

cgkirici commented 3 years ago

@fivetran-joemarkiewicz thanks so much for addressing this! It is all good now from our end.