Snowflake query tags are labels that you can attach to each individual query or group of queries to help organize and categorize them for tracking and reporting purposes.
In this article, we'll talk about what Snowflake query tags are, how to use them, and how they can benefit Snowflake query tracking, Snowflake query tuning and optimization.
What is a Snowflake query tag?
Snowflake query tags are Snowflake parameters that can be configured at the account, user, or even session level in Snowflake. These query tags allow users to assign a descriptive string label of up to 2000 characters long (and containing any characters) to any query they execute. Using Snowflake query tags, users can easily name queries, group them, and keep better track of how well queries are performing, which can help with Snowflake query optimization.
Users have a lot of control over how they keep track of or organize their queries because Snowflake query tags can be configured at different levels (account, session, or user level).
Okay, so are the Snowflake query tags and Snowflake object tags the same?
No, they are not the same.
Both Snowflake query and object tags allow for structured monitoring and better visibility within a Snowflake account, but they do so for different reasons. Snowflake query tags are labels that you can attach to each individual query or group of queries to help organize and categorize them for tracking and reporting purposes. Object tags, however, are labels that you can attach to database objects such as tables, views and schemas. Object tags can be used to help manage and organize your data assets, facilitate access control, and apply data retention policies. While both types of tags provide valuable functionality, they are used in different contexts and for different purposes.
How to add a query tag in Snowflake?
To summarize what we've just mentioned above, Snowflake query tags can be used to easily trace back the source of a query or what it was used for, aiding in Snowflake query debugging and Snowflake query optimization. You could, for example, use Snowflake query tags to differentiate between queries used for reporting and those used for analytics.
Let's go over the step-by-step procedure for creating SESSION-level query tags in Snowflake using simple SQL statements.
Step 1: Log in to your Snowflake account and create a worksheet
Step 2: Create a database in Snowflake using the following SQL statement:
CREATE DATABASE awesome_database;
Step 3: Use the newly created database by running the following SQL statement:
USE DATABASE awesome_database;
Step 4: Create a warehouse in Snowflake using the following SQL statement:
CREATE WAREHOUSE awesome_warehouse;
Step 5: Use the newly created warehouse by running the following SQL statement:
CREATE WAREHOUSE awesome_warehouse;
Step 6: Create a table in Snowflake that you will use to demonstrate Snowflake query tags using the following SQL statement:
CREATE TABLE awesome_table (
id INT,
name VARCHAR,
age INT
);
Step 7: Insert some sample data into the table using the following SQL statement:
INSERT INTO awesome_table VALUES (1, 'Johnny', 25), (2, 'Happy', 30), (3, 'Chaos', 40);
Step 8: Set up Snowflake query tags for your session by running the following SQL statement:
ALTER SESSION SET QUERY_TAG = 'Some_query_tag_name';
Step 9: Run a query against the table with the Snowflake query tag attached to it using the following SQL statement:
SELECT * FROM awesome_table;
Step 10: Verify that the Snowflake query tags was applied to the query by running the following SQL statement:
SELECT QUERY_TAG FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_TEXT LIKE 'SELECT * FROM awesome_table%';
This particular query should return a single row with the query tag you set up in Step number 8th.
Note: Snowflake query tags can be extremely useful for tracking the origin or purpose of a query in a large system, but they should be used with caution to avoid cluttering query logs with unnecessary information.
Snowflake Query Tagging by User/Account/Session
You can set Snowflake query tags at three different levels:
- Account
- User
- Session
When Snowflake query tags are set at multiple levels, they are applied in a specific order of precedence. So at the account level, a query tag is set for the entire Snowflake account. If a user-level query tag is set for a specific user in the Snowflake account, it will override the account-level query tag for any queries that the user runs.
In the same way, if a user-level or account-level query tag is already in place and a session-level query tag is set, the session-level query tag will take priority over the user-level, or account-level tag for any queries run during that session.
Hence, Snowflake query tags can be set at multiple levels, but the order of precedence determines which tag is applied for any given query.
Here's an in-depth explanation of each level:
Account-Level Query Tag:
An account-level query tag is a tag that applies to all queries run in the account, no matter who runs them. This means that if an account admin sets an account-level query tag, it will be automatically applied to every query run by any user in that account.
Note: Only an ACCOUNTADMIN can set an account-level query tag.
To assign an account-level query tag in Snowflake, you can use the ACCOUNTADMIN role to set the tag using the ACCOUNT_USAGE view.
Here's an example SQL query:
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET QUERY_TAG = 'Account_level_query_tag';
User-Level Query Tags:
A user-level query tag is a way to apply a tag to the queries run by a specific user. This tag will only be applied to the queries run by that user and not to any other queries. To set a user-level query tag, you must have the "ALTER USER" privilege. This feature can be handy in situations where you need to track queries for specific users or departments.
To assign a user-level query tag in Snowflake, you can use the ALTER USER command with the SET QUERY_TAG parameter.
Here's a sample SQL query:
USE ROLE SYSADMIN;
ALTER USER Pramit SET QUERY_TAG = 'TeamA';
ALTER USER Preeti SET QUERY_TAG = 'TeamB';
ALTER USER Happy SET QUERY_TAG = 'TeamC';
ALTER USER Alice SET QUERY_TAG = 'TeamD';
ALTER USER John SET QUERY_TAG = 'TeamE';
USE ROLE ACCOUNTADMIN;
SELECT user_name, role_name, query_tag
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_tag = 'TeamA'
GROUP BY user_name, role_name, query_tag;
Session-Level Query Tags:
Other than account-level and user-level Snowflake query tags, Snowflake also allows for session-level query tags. These unique Snowflake query tags are extremely useful for categorizing and tracking queries related to specific tasks or issues. You can assign a session-level query tag before running any important query, making it easier to locate them later if you need to use Snowflake time travel.
For example, you could create a session-level query tag named "HighPriority" before initiating object tagging in your account in order to group and reuse associated queries.
To assign a session-level query tag in Snowflake, you can use the ALTER SESSION command with the SET QUERY_TAG parameter.
Here's an example SQL query:
ALTER SESSION SET QUERY_TAG = 'HighPriority';
USE ROLE ACCOUNTADMIN;
SELECT user_name, role_name, query_tag
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_tag = 'HighPriority'
Note: Session-level Snowflake query tags are unique to the current session and replace user-level Snowflake query tags. So, by utilizing session-level Snowflake query tags, you enhance your ability to efficiently identify queries related to specific tasks or issues, which can be incredibly valuable when debugging queries or any issue.
What are the benefits of using Query Tags in Snowflake?
There are numerous benefits to using Snowflake query tags, some of which are listed below:
1) Snowflake Query Monitoring + Management 🕵️
Snowflake query tags make it easy to find and track queries based on criteria like department, user, or purpose of the query. This makes it easier to keep an eye on and manage queries. This info can be really valuable in detecting performance issues or optimizing resource allocation. For instance, if a particular user or department is experiencing slow query times, Snowflake query tags allow account admins to pinpoint the exact queries causing the problem and take appropriate measures to improve overall Snowflake performance.
2) Optimizing Snowflake Query Performance 🚀
Snowflake query tags play a significant role while optimizing query performance. By tagging queries, users can easily find and prioritize specific queries, allocating resources based on their priorities and needs for faster processing and reduced wait times. This effective method for optimizing Snowflake queries improves the whole querying process, making Snowflake queries more efficient and better at what they do.
3) Streamlined Snowflake Query Tracking Process 🔍
Snowflake query tags is a versatile feature that simplifies the process of auditing queries by enabling them to be organized according to specific criteria for categorization and easy retrieval. So by tagging queries with specific labels/criteria, admins can quickly search for and identify relevant information, which can help streamline the query auditing process and improve compliance and security measures as well.
4 best practices for Using Query Tags in Snowflake
1) Establishing a consistent query tagging technique
It's ABSOLUTELY necessary to have a consistent tagging approach that aligns with your business goals and query management processes. Creating a clear naming convention for your tags is a good idea to make analysis and reporting easier and more useful.
2) Limiting the number of Snowflake query tags
When creating Snowflake query tags, it's crucial to strike a perfect balance between comprehensiveness and manageability. Even though it's important to have enough tags to cover the most important parts of query activity, having too many tags can make query data too big, confusing, and hard to analyze. So, it's best to come up with a small set of query tags that cover the most important parts of query activity.
3) Using Snowflake query tags to track essential metrics at all times
Snowflake query tags play an important role in monitoring and optimizing query performance. You can put these descriptive labels on queries to keep track of key metrics like resource usage, run-time, queue time by groups. Hence, by analyzing these trends, you can easily identify query groups that need optimization. For instance, to find queries that use too much CPU or take too long to run you can use tags to find out which users or applications run these queries. With this valuable insight, you can better understand where and how your resources are being utilized and make informed decisions about Snowflake query optimization.
4) Using Snowflake query tags for compliance + auditing purposes
Snowflake query tags make tracking and monitoring queries for compliance and auditing purposes easy and quick. You can use Snowflake query tags to find queries that access sensitive data or do important tasks so that they can be thoroughly audited. These tags make it easier to identify anomalies or potential issues requiring attention.
Conclusion
Query performance tracking can be daunting, particularly when dealing with millions of queries. Snowflake query tags, fortunately, provide a solution to this problem by allowing users to label their workloads. These labels aid in categorizing and aggregating queries for simple monitoring and optimizing platform resources, resulting in better utilization and reduced costs.
In summary, Snowflake query tags enable users to enhance their Snowflake query management workflow while also enhancing query performance and significantly reducing Snowflake costs.
Start tagging those queries and feel like a boss who can easily find a needle in a haystack, or in this case, a query in your Snowflake.
FAQs
How are query tags different from object tags?
Query tags and object tags serve different purposes in Snowflake. Query tags are used for tagging SQL statements to enable better cost and performance monitoring. Object tags, on the other hand, are used for persistent account objects like users, roles, tables, views, and functions.
Why should I use query tags in Snowflake?
Query tags provide enhanced cost attribution and performance monitoring. They allow for fine-grained cost attribution by associating costs with specific queries or sets of related queries.
Can query tags be used with dbt (data build tool)?
Yes, query tags can be used with dbt.
Can query comments be used instead of query tags?
Yes, query comments can be used to add metadata to queries, but they have some limitations. Query tags are simpler to parse and analyze downstream, while query comments may require additional processing. Query comments also have a size limit of 1MB, whereas query tags are limited to 2000 characters.
How do you set a query tag in Snowflake?
To set a query tag in Snowflake, you can use the "ALTER USER" command with the SET QUERY_TAG parameter. This allows you to assign a tag to specific users.
