[ADAP-625] [Feature] Support for Multiple Query Tags from different keys

[joris-swapfiets]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Description
The current implementation of the +query_tag parameter in dbt-snowflake only supports a single query tag at a time. This feature request aims to enhance the functionality by allowing the merging of multiple query tags defined in the dbt_project.yml configuration file.

Proposed Solution
The proposed solution involves modifying the +query_tag behavior to merge multiple query tags together into a JSON string. Each query tag specified in the model-configs section of dbt_project.yml will contribute to the final merged query tag for each model.

Example
Consider the following dbt_project.yml configuration:

models:
  dbt_reporting:
    +schema: reporting
    +query_tag: >
      {"repository": "dbt_reporting"}
    marts:
      consumer_direct:
        +query_tag: >
          {"department": "consumer_direct"}
        marketing:
          +query_tag: >
            {"domain": "marketing"}
        rating:
          +query_tag: >
            {"domain": "rating"}

• If a model in the rating folder is executed, the resulting query tag should be: {"repository": "dbt_reporting", "department": "consumer_direct", "domain": "rating"}.
• If a model in the marketing folder is executed, the resulting query tag should be: {"repository": "dbt_reporting", "department": "consumer_direct", "domain": "marketing"}.

Describe alternatives you've considered

No response

Who will this benefit?

Benefits

• Enhanced flexibility: Users can define multiple query tags in the dbt_project.yml configuration, allowing for more detailed categorization and organization of Snowflake queries.
• Improved query tagging: The merged query tags provide richer metadata, enabling better tracking and management of queries within Snowflake.
• Simplified configuration: Instead of specifying individual query tags for each model, users can define a set of query tags at the mart or project level, reducing duplication and improving maintainability.

Are you interested in contributing this feature?

No response

Anything else?

Additional Considerations

• It might be a possibility to add an option inherit_parent_tags to either include or overwrite the parent-level query tags.
• The order of merging the query tags can be based on the hierarchical structure defined in the dbt_project.yml configuration, where the parent-level tags are merged first, followed by child-level tags.
• The merged query tags can be transformed into a string format compatible with Snowflake query tagging requirements.

[dataders]

hey @joris-swapfiets great suggestion and it is entirely feasible. Query tagging was originally added in dbt-labs/dbt-core#2555 (back when the snowflake code still lived within dbt-core).

afaict, the mechanics for generating the query tag lives below. That said, I have to do some double-checking on the mechanics of config.get() to see if it's possible to be additive as you suggest. My hunch right now is that it's behavior is use the model's config to override the rest, which would make your ask harder. Can you think of an example where this is possible already?

dbt-snowflake/dbt/include/snowflake/macros/adapters.sql

Lines 288 to 297 in c613723

	{% macro snowflake__set_query_tag() -%}
	{% set new_query_tag = config.get('query_tag') %}
	{% if new_query_tag %}
	{% set original_query_tag = get_current_query_tag() %}
	{{ log("Setting query_tag to '" ~ new_query_tag ~ "'. Will reset to '" ~ original_query_tag ~ "' after materialization.") }}
	{% do run_query("alter session set query_tag = '{}'".format(new_query_tag)) %}
	{{ return(original_query_tag)}}
	{% endif %}
	{{ return(none)}}
	{% endmacro %}

You can play around with this macro easily by adding the snowflake__set_query_tag macro to the macros/ dir of whatever project you're currently using. I'd then recommend adding some log statements to the macro body to print out some info to the console about what's going on.

{{ log('JORIS: the query_tag is: ' ~ query_tag, info=True) }} 

[joris-swapfiets]

@dataders I've played around quite a bit with this macro but indeed it seems the config.get('query_tag') variable is being overwritten. I did however see that the +meta config is merged when it is set at multiple levels in the dbt_project.yml.

    top_level:
      +meta:
        key1: "value1"

      child:
        +meta:
          key2: "value2"

The config.get('meta') command returns the value {'key1': 'value1', 'key2': 'value2'} for a model in the child folder. I could not figure out how to translate this to the +query_tag configuration however. If you can maybe give me some pointers on how to do this I can further investigate.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.