Understanding and parsing the query history (Snowflake) and joining to dbt artifacts

[jtalmi]

How-to guide on how to parse query comments in the Snowflake query history. Comparison between using query tags and query comments. Get model level performance metrics by joining the query history to the dbt artifacts in the warehouse.

Building on Tristan's comments in the AE roundup this past weekend, I have started writing a post that will introduce the system we use at my company (Snapcommerce) to get compute cost per dbt model run. The gist of it is adding query comments to dbt, parsing the snowflake query history for these comments, and then combining the result with dbt artifacts like run results to get cost + performance per dbt model run.

We've talked about this before here and I finally got around to writing it. I have a WIP version here, I'd be happy to share it with you

[krevitt]

fyi @gwenwindflower i shared you on that draft above - next step as I mentioned @jtalmi would be abstracting out a basic outline in the doc, to tease out the correct order of sections

[jtalmi]

added an outline to the top of the doc and one sentence summary

[gwenwindflower]

awesome - this sounds fantastic! i'll get back to you by EOD tomorrow with rough timelines on next steps - thanks, jonathan!

[jtalmi]

One of the discussion points that came up for query comments vs. query tags. Query tags in Snowflake are set at the session level: https://docs.snowflake.com/en/sql-reference/parameters.html#query-tag

The dbt documentation has this paragraph:

Note: query tags are set at the session level. At the start of each model materialization, if the model has a custom query_tag configured, dbt will run alter session set query_tag to set the new value. At the end of the materialization, dbt will run another alter statement to reset the tag to its default value. As such, build failures midway through a materialization may result in subsequent queries running with an incorrect tag.

This is why I use query comments instead of query tags, since the comments are appended to the queries themselves and therefore there are never data inconsistencies or gaps.

I want to make sure this makes sense. Would you be able to get someone from dbt labs to help clarify this?

[krevitt]

Yeah my sense is query comments is the broadest possible way to solve for this - also, query comments are more likely to work cross-platform yeah / be widely used by other tooling (Looker's query comments make usage there easy to parse as well).