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 - ): SQL compilation error: ambiguous column name '_FIVETRAN_SYNCED' compiled SQL at target/run/jira/models/jira__issue_enhanced.sql #35

Closed NatalieBabich closed 3 years ago

NatalieBabich commented 3 years ago

Are you a current Fivetran customer?

My name is Nataliya Babich, I am BI Developer at Nice, Nice is Fivetran customer.

Describe the bug

I updated our version JIRA fivertran dbt package to version 0.3.0. After run dbt run --full-refresh I get error in model jira__issue_enhanced.

ERROR creating table model jira.jiraissue_enhanced........ [ERROR in 1.65s] 2021-08-01 14:05:35.133777Z: Finished running node model.jira.jira__issue_enhanced Database Error in model jiraissue_enhanced (models/jira__issue_enhanced.sql) 002028 (42601): SQL compilation error: ambiguous column name '_FIVETRAN_SYNCED' compiled SQL at target/run/jira/models/jira__issue_enhanced.sql

Steps to reproduce I updated our version JIRA fivertran dbt package to version 0.3.0 (https://hub.getdbt.com/fivetran/jira/latest/), then I ran dbt deps. I added var jira_issue_history_buffer: 12 to dbt_project.yml. I run dbt run --full-refresh. In jira__issue_enhanced I get error

ERROR creating table model jira.jiraissue_enhanced........ [ERROR in 1.65s] 2021-08-01 14:05:35.133777Z: Finished running node model.jira.jira__issue_enhanced Database Error in model jiraissue_enhanced (models/jira__issue_enhanced.sql) 002028 (42601): SQL compilation error: ambiguous column name '_FIVETRAN_SYNCED' compiled SQL at target/run/jira/models/jira__issue_enhanced.sql

Expected behavior

Project variables configuration

name: 'my_new_project' version: '1.0.0' config-version: 2 vars: jira_issue_history_buffer: 12 issue_field_history_columns: ['Story Points','priority','summary','Team','Closed','Capitalize','Regression','Epic Story Points','Requirement Category','_FIVETRAN_SYNCED', 'Found By'] jira_database: FIVETRAN_DATABASE jira_schema: JIRA tfs_schema: DWH_JIRA field_option_local: "{{ source('bi_jira', 'field_option') }}" version: "{{ source('bi_jira', 'version') }}" tfsdwhdatatotableau: "{{ source('bi_tfs', 'tfsdwhdatatotableau') }}" tfs_history_data: "{{ source('bi_tfs', 'tfs_history_data') }}"

Package Version

packages:

Warehouse

- [ ] BigQuery - [ ] Redshift [x] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency

Are you interested in contributing to this package?

fivetran-joemarkiewicz commented 3 years ago

Hi @NatalieBabich thanks for opening this issue. I believe I may have found the root cause of this here https://github.com/fivetran/dbt_jira/blob/e86f414a4fb91403451f935238d6470668878996/models/jira__issue_enhanced.sql#L47 In the above code snippet we ensure to only bring through custom fields if they are not already being brought into the issue table natively. For example, _FIVETRAN_SYNCED is already included within the jira__issue_enhanced model and the above code would exclude the field. However, I noticed for Snowflake the |lower filter is not accurately filtering out those fields since Snowflake natively upper cases the fields.

We are planning to release a new version of the jira dbt package today with compatibility for dbt 0.20.0 and can include a conditional on this line for Snowflake users to ensure this error does not happen. In the meantime, if you remove _FIVETRAN_SYNCED from your variable you should be able to run the package without error.

fivetran-joemarkiewicz commented 3 years ago

@fivetran-jamie we can chat more about this live, but would you be able to include the above update in the jira__issue_enhanced model before release today?

fivetran-jamie commented 3 years ago

yes i actually think today's release will already cover this issue! can talk more during standup @fivetran-joemarkiewicz

fivetran-jamie commented 3 years ago

@NatalieBabich v0.4.0 of the jira package should fix this! this version does require you to be on dbt 0.20.0 however FYI. if you're still seeing the issue feel free to re-open this issue!

NatalieBabich commented 3 years ago

Hi, Thank you all. I changed dbt version to 0.20.0, jira package version to 0.4.0. After run dbt debs, I trying dbt run --full-refresh. I get error in int_jira__issue_join.

Database Error in model int_jiraissue_join (models/intermediate/int_jiraissue_join.sql) 000904 (42000): SQL compilation error: error line 332 at position 49 invalid identifier 'VERSION.VERSION_ID' compiled SQL at target/run/jira/models/intermediate/int_jira__issue_join.sql

I attached error log . I guess the problem is here in code : with version as (

select *
from FIVETRAN_DATABASE.JIRA.version

), You use FIVETRAN_DATABASE.JIRA.version instead of stg_jira__version.

Thanks, Natalie

log.txt

NatalieBabich commented 3 years ago

Hi, Thank you . I changed dbt version to 0.20.0, jira package version to 0.4.0. After run dbt debs, I trying dbt run --full-refresh. I get error in int_jira__issue_join.

Database Error in model int_jiraissue_join (models/intermediate/int_jiraissue_join.sql) 000904 (42000): SQL compilation error: error line 332 at position 49 invalid identifier 'VERSION.VERSION_ID' compiled SQL at target/run/jira/models/intermediate/int_jira__issue_join.sql

I attached error log . I guess the problem is here in code : with version as (

select * from FIVETRAN_DATABASE.JIRA.version

), You use FIVETRAN_DATABASE.JIRA.version instead of stg_jira__version.

Thanks, Natalie

вт, 3 авг. 2021 г. в 00:13, Jamie Rodriguez @.***>:

@NatalieBabich https://github.com/NatalieBabich v0.4.0 of the jira package should fix this! this version does require you to be on dbt 0.20.0 however FYI. if you're still seeing the issue feel free to re-open this issue!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_jira/issues/35#issuecomment-891337383, or unsubscribe https://github.com/notifications/unsubscribe-auth/ATD3EYWCS2QJDXPGDZHGNSTT24DBFANCNFSM5BMERE5Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email .

fivetran-joemarkiewicz commented 3 years ago

Hi @NatalieBabich I am sorry you are seeing that error.

After looking into this and seeing the variables you posted within the original issue description it seems the variable we set within this package for version is being overridden by the version variable within your root project. In the below snippet you can see we set the transform variable for version to reference stg_jira__version https://github.com/fivetran/dbt_jira/blob/df506504f9019e563ca3b0ade1529aa499a338ba/dbt_project.yml#L25 However it looks like the override within the root project uses the "{{ source('bi_jira', 'version') }}" variable instead which is why you are seeing the error with the model pointing to the source instead of the staging model.

I believe if you adjust your variables to be scoped to the specific packages rather than globally you should be able to execute the Jira models successfully. Let us know if you continue to run into any issues and I hope this helps!