New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Snowflake Query_id #383
Snowflake Query_id #383
Comments
Thank you for surfacing @kkprab, sounds like a great addition! Are you open to contributing this feature? |
Hey @kkprab thanks for this idea! In addition, can you please share with us the need this feature helps you with? |
Hi
Yes I am open to contributing for this feature..
I understand that query_id would be only relavant to snowflake... let me try to explain use case
In dbt tables are create or replace so if I want to time travel on a table
Only way is to create table using before (date time or query id)
Reason why we need time travel could be
1) Issue with logic (ie changes passed on to PROD with out proper validation) I know in our company it happens some time
2) Data comparison
3) Say dbt run took long time for certain model with query id it will be faster to get to and view the query profile to find out root casue
If we have query_id there is no better way to time travel as well analysing issue in query profile
Thanks
Prabahar Kamatchirajan
From: IDoneShaveIt ***@***.***>
Sent: 06 November 2022 21:04
To: elementary-data/elementary ***@***.***>
Cc: Kamatchirajan, Prabahar ***@***.***>; Mention ***@***.***>
Subject: Re: [elementary-data/elementary] Snowflake Query_id (Issue #383)
[EXTERNAL E-MAIL] This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Hey @kkprab<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkkprab&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7C12b5605d97f041a78bef08dac03a6c9a%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638033654414942712%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=a9otcKBamIpiBZaas6696yVMWkRNIhBXH8fmvGInScs%3D&reserved=0> thanks for this idea!
I tested it, and it seems to be a specific information that snowflake adapter is returning on dbt run.
As @Maayan-s<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FMaayan-s&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7C12b5605d97f041a78bef08dac03a6c9a%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638033654414942712%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=QcD6eQ1piRKIKXbU01SR7bKp6UNqmG19XtX5UWpI8lQ%3D&reserved=0> said, if you are open to contributing this feature we would love to provide guidance!
In addition, can you please share with us the need this feature helps you with?
And can you please tell us if (or why) the compiled code that we save on the dbt_run_results table and shows in our alerts & report isn't enough?
We would love to learn from your use case an improve our product!
-
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Felementary-data%2Felementary%2Fissues%2F383%23issuecomment-1304893197&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7C12b5605d97f041a78bef08dac03a6c9a%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638033654414942712%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=oRU01b%2BpG8PM9zcaH7ynC5HDZigerk3nhiuyWaNP2QY%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAXHOSKYGKCG72XAUGKFPRM3WHAML5ANCNFSM6AAAAAARX3U7MI&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7C12b5605d97f041a78bef08dac03a6c9a%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638033654414942712%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2Bif%2FXMp57B0RIFezt%2FBjG9pxYPQuQy0jNWCP0rQwgto%3D&reserved=0>.
You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>
|
Thanks @kkprab for willing to contribute this feature and sharing all this information with us! Here's a guidance for this feature:
|
@kkprab of course! I think that the easiest way for you to do so is to install you local elementary package in your dbt project (guide). packages:
- local: <dbt-data-reliability cloned repo path> Now when you'll run Another option is to run our integration tests. Thanks again for the contribution, and feel free to reach out for any other question that you have! P.S I am assigning the issue to you 🙂 |
Hi @kkprab , chiming in here. Please correct me if I'm wrong, but I believe you are trying to add an "{% if" statement within the context of "{% set flatten_run_result_dict" in I believe this should work though:
Or actually, a shorter version without duplicating the logic that gets the query id:
|
Thanks this works..
Now I have tested and all works now..
Now I can see new column query_id is created in dbt_run_results and on dbt run snowflake query_id is populating..
***@***.***
Can you guide me how to send my github fork back to you..
Thanks
Prabahar Kamatchirajan
From: Itamar Hartstein ***@***.***>
Sent: 10 November 2022 13:14
To: elementary-data/elementary ***@***.***>
Cc: Kamatchirajan, Prabahar ***@***.***>; Mention ***@***.***>
Subject: Re: [elementary-data/elementary] Snowflake Query_id (Issue #383)
[EXTERNAL E-MAIL] This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Hi @kkprab<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkkprab&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7Cd5c0a3990c1142845eeb08dac31d7e11%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638036828697162546%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=yAtjDVqojHkZ32%2BAWqmEL9NPpCySjQdGiSf1HiKIEEQ%3D&reserved=0> , chiming in here.
Can you please send the full file with this change (or a github link)?
It can help with understanding the issue.
Please correct me if I'm wrong, but I believe you are trying to add an "{% if" statement within the context of "{% set flatten_run_result_dict" in upload_run_results.sql.
That won't work as you can't nest Jinja statements.
I believe this should work though:
'query_id': run_result_dict.get('adapter_response', {}).get('query_id') if run_result_dict.get('adapter_response', {}).get('query_id') else None
Or actually, a shorter version without duplicating the logic that gets the query id:
'query_id': run_result_dict.get('adapter_response', {}).get('query_id') or None
-
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Felementary-data%2Felementary%2Fissues%2F383%23issuecomment-1310265234&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7Cd5c0a3990c1142845eeb08dac31d7e11%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638036828697162546%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=6t4f7QobRDEQq%2Fur3Cm2Mklg9jsYEyJNXOWe%2BLQhXrQ%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAXHOSK7RUGOJCTQ3N37WANTWHTYLDANCNFSM6AAAAAARX3U7MI&data=05%7C01%7Cprabahar.kamatchirajan%40irco.com%7Cd5c0a3990c1142845eeb08dac31d7e11%7Ccbcd573c126846b899ebfe1150873655%7C0%7C0%7C638036828697162546%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=ouGsKf%2BGSGndXOZTYzADnbM33zcE7fRgFgrf2GxRS2s%3D&reserved=0>.
You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>
|
Hi @kkprab - You should open a pull request. |
Is your feature request related to a problem? Please describe.
When we get a dbt run failure, I often look through the debug logs to find the exact query which was run..
Also in case of data issues if we want to use snowflake time travel query id make it easier to go back
Describe the solution you'd like
dbt run_results.json has snowflake query_id.. add this as part of elementary table dbt_run_results
Describe alternatives you've considered
Right now, I look through the snowflake query history to find the sfqid, which works but is a bit tedious.
Additional context
Add any other context or screenshots about the feature request here.
The text was updated successfully, but these errors were encountered: