Snowflake Query Tagging Best Practices

Hundreds, maybe even thousands, of queries are running in your Snowflake AI Data Cloud account daily. With all these queries running, it’s difficult to determine who’s using them and what they’re for. This is an especially difficult scenario for companies that use a chargeback model for their departments. To alleviate this issue, Snowflake has developed query tags.

In this blog, we’ll discuss query tags, when and how to use them, and some best practices surrounding them.

What are Query Tags?

Query tags are an optional parameter that allows users to tag any SQL statement within Snowflake with a string at a session level. This can be extremely useful when used with the query_history view in Snowflake. This view contains a query_tag column that can be used to group or filter the query tags to monitor groups of queries together.

For instance, this query is looking for any query with the tag:

PHDATA_TRANSFORMATION_PIPELINE

				
					SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
   START_TIME > CURRENT_DATE - 1
AND
   QUERY_TAG = 'PHDATA_TRANSFORMATION_PIPELINE';

When to Use Query Tags

Tracing and grouping your queries can help monitor your Snowflake account more granularly than simply by warehouse or user.

Cost Monitoring

Cost monitoring in a shared cloud environment across business units (BU) can be extremely challenging. It’s especially painful for large corporations that utilize a chargeback model. Deciding who should pay for which computing costs can be a contention between the business units. Without knowing accurately which business unit ran a query, credit costs can be charged to the wrong group, and before you know it, people will start to point fingers at each other when budget talks commence.

This is where query tagging comes into play. By making it a requirement for every query to be tagged with the business unit running the query, companies can accurately chargeback costs, create a company-level dashboard that portrays which units use the most credits, and business units can pinpoint queries that consume disproportionate resources and target optimization efforts.

Query tagging by business unit can also provide a history of queries to track costs and identify usage trends. Each business unit can quickly and efficiently examine its own usage to spot inefficient queries or usage patterns and use that information to drive best practices for its analysts or optimize warehouses.

Tagging by BU has the added benefit of being used in audit trails. For instance, if a query was run that viewed unmasked PII data that was restricted, the investigation into the lapse would be much easier to narrow down, knowing which BU ran the query.

Finally, having queries tracked by BU allows not only monitoring but also alerting within a BU. For instance, if a business unit has a certain credit threshold for the month, an alert could be created to let the BU stakeholders know when they’ve reached 50, 75, or 100% of the credit threshold, and they can act accordingly.

Performance Monitoring

In much the same way, you can use query tags for cost monitoring; they can also be used for monitoring performance for groups as queries. Use cases include:

For queries that utilize multiple warehouse sizes in Snowflake, tag them based on the warehouse they utilize. Analyze query performance data by tag to compare execution times across different warehouse configurations to help determine the most cost-effective warehouse size for specific query types.
As in cost monitoring, use the queries tagged based on the query’s function to analyze performance and determine where slowdowns may be.

Other Best Practices

Establish a clear and consistent approach for tagging queries.
Develop a naming convention for your tags to ensure consistency and clarity. Use keywords or abbreviations that everyone in your organization easily understands.
Develop reports and dashboards that visualize data by query tag to track trends, identify cost drivers and performance bottlenecks, and make informed decisions about resource allocation and query optimization.
Begin with a basic tagging strategy and gradually add complexity as your needs evolve directly in Snowflake's UI.
Granulating too many overly specific tags can become cumbersome.

How to Use Query Tags

Snowflake UI

Query tags can be applied with a simple SQL command directly in Snowflake’s UI, at three different levels at which they can be applied:

Account Level

Query tags can be set at an account level so that they are automatically applied to every query run within the account. Only the account admin can do this.

				
					ALTER ACCOUNT SET QUERY_TAG = 'phdata_aws_us_east_1_account';

User Level

It is also possible to set a query tag at the user level and apply it to every user query.

				
					ALTER USER JUSTIN SET QUERY_TAG = '{"team": "phdata_engineering", "user": "justin"}';

Session Level

Finally, the tags can be set at each session’s most granular level. This will apply the tag to all queries run within the session.

				
					ALTER SESSION SET QUERY_TAG = 'high_priority';

Python

If you are using the Python Connector for Snowflake, there are two ways to set a query tag:

Connection Parameter

The connector allows you to pass in session parameters, one of which can be a query tag that will apply for the entire session for which I am connected to Snowflake.

				
					con = snowflake.connector.connect(
   user='JUSTIN',
   password='super_difficult_password1',
   account='phdata.snowflake_computing.com',
   session_parameters={
       'QUERY_TAG': 'PHDATA_TRANSFORMATION_PIPELINE',
   }
)

SQL Execution

Additionally, executing the ALTER SESSION command is also possible. You can also run the ALTER USER and ALTER ACCOUNT commands this way.

				
					con.cursor().execute("ALTER SESSION SET QUERY_TAG = 'high_priority';")

dbt

dbt supports setting a query tag for your model in three ways:

Setting a default query tag for all your queries in Snowflake in your profile.yml
Setting a query tag for all queries in a model through your model config:

				
					models:
    <resource-path>:
        +query_tag: phdata_query

Or lastly, by overriding the default set_query_tag macro:

				
					{{ config(
    query_tag = 'phdata_query'
) }}

select ...

Coalesce

Coalesce offers two ways to set query tags.

You can simply place the command in the pre-SQL input box to add a query tag before a node is executed:

Or you can create a custom node type that includes the SQL to set the query tag in pre-sql:

Closing

Snowflake accounts can process a massive volume of queries daily. Without proper organization, this can lead to confusing data sprawl, making it difficult to understand who’s running what queries and for what purpose. By implementing query tags, you can organize your Snowflake environment, help control costs, and optimize performance.

Need help implementing best practices?

The experts at phData are here to help you get the most out of your Snowflake investment.