Object Tagging

This topic provides concepts and instructions on how to use tags in Snowflake.

In this Topic:

What is a Tag?

Tags enable data stewards to track sensitive data for compliance, discovery, protection, and resource usage use cases through either a centralized or decentralized data governance management approach.

A tag is a schema-level object that can be assigned to another Snowflake object. A tag can be assigned an arbitrary string value upon assigning the tag to a Snowflake object. Snowflake stores the tag and its string value as a key-value pair. The tag must be unique for your schema and the tag value is always a string. The maximum number of characters for the tag value is 256. The maximum number of unique tag keys that can be set on a single object is 20.

Note

For a table or view and its columns, the total number of unique tag keys that can be set is 20.

For example, if a single column in a table has 10 unique tag keys set on the column, Snowflake allows 10 additional unique tag keys to be set on either that column, other columns in the table, the table itself, or some combination of the table and its columns. Once the limit of 20 unique tag keys is met, no additional tag keys can be set on the table or its columns.

You create a tag using a CREATE TAG statement, and you specify the tag string value when assigning the tag to an object. The tag can be assigned to an object while creating the object, using a CREATE <object> statement, assuming that the tag already exists. Alternatively, you can assign the tag to an existing object using an ALTER <object> statement.

A single tag can be assigned to different object types at the same time (e.g. warehouse and table simultaneously). At the time of assignment, the tag string value can be duplicated or remain unique. For example, multiple tables can be assigned the cost_center tag and the tag can always have the string value be sales. Alternatively, the string value could be different (e.g. engineering, marketing, finance). After defining the tags and assigning the tags to Snowflake objects, tags can be queried to track usage on the objects to facilitate data governance operations, such as tracking, auditing, and reporting.

Because tags can be assigned to tables, views, and columns, setting a tag and then querying the tag enables the discovery of a multitude of database objects and columns that contain sensitive information. Upon discovery, data stewards can determine how best to make that data available, such as selective filtering using row access policies, or using masking policies to determine whether the data is tokenized, fully masked, partially masked, or unmasked.

Assigning tags to warehouses enables accurate resource usage tracking. Querying tags on resources allows for easy resource grouping by cost center or other organization units. Additionally, the tag can facilitate analyzing relatively short-term business activities, such as projects, to provide a more granular insight into what, when, and how resources were used.

Tag Lineage

A tag is inherited based on the Snowflake securable object hierarchy. Snowflake recommends defining the tag keys as closely as possible to the securable object hierarchy in your Snowflake environment.

The tag administrator can apply masking policies to tables and views.

Tag inheritance means that if a tag is applied to a table, the tag also applies to the columns in that table. This behavior is referred to as tag lineage.

It is possible to override an inherited tag on a given object. For example, if a table column inherits the tag named cost_center with a string value called sales, the tag can be updated with a more specific tag string value such as sales_na, to specify the North America sales cost center. Additionally, a new tag can be applied to the table column. Use an ALTER TABLE … ALTER COLUMN statement to update the tag string value on the column and to set one or more additional tags on a column.

After defining the tag keys and assigning tags to Snowflake objects, track the tags, tag references, and tag lineage using the specified table functions or query the views as shown in Using Tags (in this topic).

Note

Tag lineage does not include propagation to nested objects. For example:

table_1 » view_1 » materialized_view_1

If nested objects already exist relative to an underlying table or view, a tag set on underlying object does not automatically result in a tag being set on the nested object. In this example, a tag set on table_1 does not result in the same tag being set on view_1 and materialized_view_1. This behavior is also true for columns.

If it is necessary to have tags on underlying objects or columns carry over to nested objects, execute a CREATE OR REPLACE statement on the nested object and make sure the SQL statement specifies the tag on the nested object or column.

Tag Benefits

Ease of Use

Define a tag once and apply it to as many different objects as desirable.

Tag Lineage

Since tags are inherited, applying the tag to objects higher in the securable objects hierarchy results in the tag being applied to all child objects. For example, if a tag is set on a table, the tag will be inherited by all columns in that table.

Consistent Assignment with Replication

Snowflake replicates tags and their assignments within the primary database to the secondary database.

For more information, see Replication (in this topic).

Sensitive Data Tracking and Resource Usage

Tags simplify identifying sensitive data (e.g. PII, Secret) and bring visibility to Snowflake resource usage. With data and metadata in the same system, analysts can quickly determine which resources consume the most Snowflake credits based on the tag definition (e.g. cost_center, department).

Centralized or Decentralized Management

Tags supports different management approaches to facilitate compliance with internal and external regulatory requirements.

In a centralized approach, the tag_admin custom role creates and applies tags to Snowflake objects.

In a decentralized approach, individual teams apply tags to Snowflake objects and the tag_admin custom role creates tags to ensure consistent tag naming.

Using Tags with Snowflake Objects and Features

The following describes how tags affect objects and features in Snowflake.

Supported Objects

The following table lists the supported objects for tags, including columns, based on the Snowflake securable object hierarchy.

A tag can be set on an object with a CREATE <object> statement or an ALTER <object> statement unless specified otherwise in the table below.

A tag can be set on a column using a either a CREATE TABLE, CREATE VIEW, ALTER TABLE … MODIFY COLUMN, or ALTER VIEW statement.

Object hierarchy

Supported objects

Notes

Organization

Account

A tag can be set on your current account by a role with the global APPLY TAG privilege.

Account

Integration

Share

Warehouse

User

Role

Database

All integration types are supported. Use an ALTER INTEGRATION command to set a tag on the integration.

Tags are set on the share by the data sharing provider. These tags are not visible to the data sharing consumer. Use an ALTER SHARE command to set a tag on the share.

Database

Schema

Schema

Stream

Task

Table

External table

View

Materialized view

Stage

Use an ALTER STREAM command to set a tag on the stage.

Use an ALTER TASK command to set a tag on the task.

Table or View

Column

Specifying Tag Values

The ALLOWED_VALUES tag property enables specifying the possible string values that can be assigned to the tag when the tag is set on an object.

You can specify these values when creating or replacing a tag with a CREATE TAG statement, or while modifying an existing tag key with an ALTER TAG statement. Note that the ALTER TAG statement supports adding allowed values for a tag and dropping existing values for a tag.

For example:

Create a tag named cost_center with 'finance' and 'engineering' as the only two allowed string values:

create tag cost_center
    allowed_values 'finance', 'engineering';

Modify the tag named cost_center to add 'marketing' as an allowed string value:

alter tag cost_center
    add allowed_values 'marketing';

Modify the tag named cost_center to drop 'engineering' as an allowed string value:

alter tag cost_center
    drop allowed_values 'engineering';

To obtain the list of allowed string values for a given tag, call the SYSTEM$GET_TAG_ALLOWED_VALUES function. For example, assuming that the tag cost_center is stored in a database named governance and a schema named tags:

select system$get_tag_allowed_values('governance.tags.cost_center');

+--------------------------------------------------------------+
| SYSTEM$GET_TAG_ALLOWED_VALUES('GOVERNANCE.TAGS.COST_CENTER') |
|--------------------------------------------------------------|
| ["finance","marketing"]                                      |
+--------------------------------------------------------------+

CREATE TABLE Statements

With CREATE TABLE … LIKE, tags assigned to the source table are assigned to the target table.

Replication

Tags can be replicated.

The replication operation fails if either of the following conditions is true:

  • The primary database is in an Enterprise (or higher) account and contains a tag but one or more of the accounts approved for replication are on lower editions.

  • An object contained in the primary database has a reference to a tag in another database.

    Note

    If using failover or failback actions, the Snowflake account must be Business Critical Edition or higher.

    For more information, see Database Replication and Failover/Failback.

Cloning

Snowflake supports the following tag behavior during clone operations:

  • Tag associations in the source object are maintained in the cloned objects.

  • When a clone operation is run on a database or schema, the tags stored in that database or schema are also cloned.

  • After cloning, the objects and their tag associations in the source database or schema remain the same. In the cloned database or schema, the objects and their tag associations are specific to the cloned database and schema.

Using Tags

Using tags in Snowflake is a four-step process:

  1. Define a custom role and assign privileges.

  2. Create a tag using a CREATE TAG statement.

  3. Assign a tag to an existing Snowflake object using the ALTER <object> command.

  4. Track tags through table functions and views.

Note that you can alternatively assign a tag to a new object using a CREATE <object> command.

For simplicity, these steps assume a centralized management approach to tags, where the tag_admin custom role has both the CREATE and APPLY privileges for the tag.

Step 1: Create a Custom Role and Assign Privileges

In a centralized management approach, the tag_admin custom role is responsible for creating and assigning tags to Snowflake objects.

Note that this example uses the ACCOUNTADMIN system role. If using this higher-privileged role in a production environment is not desirable, verify that the role assigning privileges to the tag_admin custom role has the necessary privileges to qualify the tag_admin custom role. For more information, see Managing Tags (in this topic).

use role useradmin;
create role tag_admin;
use role accountadmin;
grant create tag on schema <schema_name> to role tag_admin;
grant apply tag on account to role tag_admin;

Step 2: Create a Tag

Execute a CREATE TAG statement to create a tag.

use role tag_admin;
use schema my_db.my_schema;
create tag cost_center;

Step 3: Assign a Tag to a Snowflake Object

Assign a tag to a new Snowflake object using an CREATE <object> statement.

For example, to set a tag on a new warehouse use the CREATE WAREHOUSE command.

use role tag_admin;
create warehouse mywarehouse with tag (cost_center = 'sales');

Assign a tag to an existing Snowflake object using an ALTER <object> statement.

For example, to set a tag on an existing warehouse, use the ALTER WAREHOUSE command.

use role tag_admin;
alter warehouse wh1 set tag cost_center = 'sales';

To set or unset a tag on an existing column, use the ALTER TABLE … MODIFY COLUMN command for a table column or the ALTER VIEW … MODIFY COLUMN command for a view column. Note that more than one tag can be set or unset in a single statement.

-- For a table or external table column

alter table <table_name> modify column <column_name> set tag <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ];
alter table <table_name> modify column <column_name> unset tag <tag_name> [ , <tag_name> , ... ];

-- For a view or materialized view column

alter view <view_name> modify column <column_name> set tag <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ];
alter view <view_name> modify column <column_name> unset tag <tag_name> [ , <tag_name> , ... ];

Step 4: Track the Tags

You can track tag usage through two different Account Usage views, two Information Schema table functions, an Account Usage table function, and a system function.

It can be helpful to think of two general approaches to determine how to track tag usage:

  • Discover or list tags.

  • Identify assignments (i.e. references) between a tag and an object.

Discover tags

Snowflake supports the following options to list tags and to identify the tag string value for a given tag key.

  • Identify tags in your account:

    Use the TAGS view in the Account Usage schema of the shared SNOWFLAKE database. This view can be thought of as a catalog for all tags in your Snowflake account that provides information on current and deleted tags. For example:

    select * from snowflake.account_usage.tags
    order by tag_name;
    
  • Identify a value for a given tag:

    Use the SYSTEM$GET_TAG system function to return the tag value assigned to the specified tag, and the Snowflake object or column.

    select system$get_tag('cost_center', 'my_table', 'table');
    
Identify assignments

Snowflake supports different options to identify tag assignments, depending on whether the query needs to target the account or a specific database, and whether tag lineage is necessary.

  • Account-level query with lineage:

    Use the Account Usage table function TAG_REFERENCES_WITH_LINEAGE to determine all of the objects that have a given tag key and tag value that also includes the tag lineage:

    select *
    from table(snowflake.account_usage.tag_references_with_lineage('my_db.my_schema.cost_center'));
    
  • Account-level query without lineage:

    Use the Account Usage TAG_REFERENCES view to determine all of the objects that have a given tag key and tag value, but does not include the tag lineage:

    select * from snowflake.account_usage.tag_references
    order by tag_name, domain, object_id;
    
  • Database-level query, with lineage:

    Every Snowflake database includes an Information Schema. Use the Information Schema table function TAG_REFERENCES to determine all of the objects that have a given tag that also includes the tag lineage in a given database:

    select *
    from table(my_db.information_schema.tag_references('my_table', 'table'));
    
  • Database-level query for all of the tags on every column in a table or view, with lineage:

    Use the Information Schema table function TAG_REFERENCES_ALL_COLUMNS to obtain all of the tags that are set on every column in a given table or view.

    Note that the domain TABLE must be used for all objects that contain columns, even if the object name is a view (i.e. view, materialized view).

    select *
    from table(information_schema.tag_references_all_columns('my_table', 'table'));
    

Managing Tags

Tag Privileges

Snowflake supports the following privileges to determine whether users can create, set, and own tags.

Privilege

Usage

CREATE

Enables creating a new tag in a schema.

APPLY

Enables the set and unset operations for the tag on a Snowflake object. For syntax examples, see: Summary of DDL Commands, Operations, and Privileges.

OWNERSHIP

Transfers ownership of the tag, which grants full control over the tag. Required to alter most properties of a tag.

Note

Tags are stored at the schema level.

Operating on a tag requires the USAGE privilege on the parent database and schema.

Tag DDL Reference

Snowflake supports the following DDL to create and manage tags.

Note that Snowflake does not support the describe operation for the tag object.

Summary of DDL Commands, Operations, and Privileges

The following table summarizes the relationship between tag privileges and DDL operations.

Operation

Privilege required

Create tag.

A role with the USAGE privilege on the parent database and schema with the CREATE TAG privilege in the same schema.

Alter tag.

The role with the OWNERSHIP privilege on the tag.

Drop & Undrop tag.

A role with the OWNERSHIP privilege on the tag and the USAGE privilege on the database and schema in which the tag exists.

Show tags.

One of the following: . A role with the USAGE privilege on the schema in which the tags exist, or . A role with the APPLY TAG on ACCOUNT permission.

Set or unset a tag on an object.

For individual objects, a role with the APPLY TAG privilege on the account, or the APPLY TAG privilege on the tag and the OWNERSHIP privilege on the object on which the tag is set. See Supported Objects.

Set or unset a tag on a column.

A role with the APPLY privilege on the tag and the OWNERSHIP privilege on the table or view.

Get tags on an object.

See SYSTEM$GET_TAG, TAG_REFERENCES, and TAG_REFERENCES_WITH_LINEAGE.

Snowflake supports different permissions to create and set a tag on an object.

  1. For a centralized tag management approach, in which the tag_admin custom role creates and sets tags on all objects, the following permissions are necessary:

    use role securityadmin;
    grant create tag on account to role tag_admin;
    grant apply tag on account to role tag_admin;
    
  2. In a hybrid management approach, a single role has the CREATE TAG privilege to ensure tag keys are named consistently and individual teams or roles have the APPLY privilege for a specific tag.

    For example, the custom role finance_role role can be granted the permission to set the tag key cost_center on tables and views the role owns:

    use role securityadmin;
    grant create tag on account to role tag_admin;
    grant apply on tag cost_center to role finance_role;