Object Tagging in Snowflake —Part 1

The purpose of this blog is to explore one of Snowflake’s key data governance features, Object Tagging, and its concepts and advantages.

What is Object Tagging ?

Object Tagging enables governance operations such as

• Data classification
• Tracking sensitive data
• Resource usage monitoring
• Protecting the data.

Object tagging allows us to assign metadata to Snowflake objects in order to categorize a group of objects or describe the type of data stored in a table or column.

What is a Tag?

A tag is a schema-level object in Snowflake, which is created once and then applied to many different objects as needed. Therefore, tagging a Snowflake object is a two-step process:

Tags are made of key-value pairs. For example, “cost_center = finance” or “protection_level = PII” or “PII_type = email.”

TAG Definition (CREATE TAG):

Defining a tag is creating a tag with a name, an optional comment, and an optional allowed values attribute.

For example:

Tag Name:
    Sports (Values tagged : Cricket, Badminton, Football)
    Security(Values taggged : PHI,PII, PCI)
    Travel (Values tagged : Air, Road, Sea)

In above example Sports is the Key and Cricket, Badminton, FootBall are the values applied to it.

TAG Assignment (APPLY TAG):

Tag assignment is applying a tag to Snowflake objects with an arbitrary string value that can classify Snowflake objects or data.

In the tag assignments example below, Fig.1 Sports and Travel tags are assigned to a Database, Schema, Views and a Warehouse. Fig.2 Security_class tag is assigned to a Table.

Fig. 1

Fig. 2

Further, tags are inherited based on where you apply them — so if you tag a table “Security_level = PHI,” every column in that table will also be tagged as “Security_level= PHI.” This applies the same way at higher levels: if you tag an entire database as “Security_level= PHI,” then every schema, every table, every view and every column in that database will be tagged as “Security_level= PHI”

Other Definition:

Tag Override:

We can also override or add to tags. A specific table in the “cost_center = finance” schema may have its tag overridden to “cost_center = finance_north_america.” All the columns in a table with “Security_level= PHI” will have the same “Security_level= PHI” tag but can also have a specific tag such as “PII_type = email” appended, so both pieces of information are returned when you query information about that column.

Benefits of using Tag:

There are multiple benefits using TAG and few listed below,

• Track and discover sensitive data
• Data/Object classification
• Track resource usage
• Row level security
• Tag based data masking
• Facilitates Automation

How to create TAG in Snowflake ?

A tag must be defined before it can be applied to other Snowflake objects. Tag are created as schema-level objects and live in the schema where they are created. Tags can be assigned to any supported objects in Snowflake, including the database and schema where the tag is created.

CREATE OR REPLACE DATABASE OBJECT_TAGGING_DEMO;
CREATE OR REPLACE SCHEMA OBJECT_TAGGING_SCHEMA;

USE ROLE USERADMIN;
CREATE OR REPLACE ROLE TAG_ADMIN;

USE ROLE ACCOUNTADMIN;
GRANT ROLE ACCOUNTADMIN TO ROLE TAG_ADMIN;
GRANT ROLE TAG_ADMIN TO USER SNOWFLAKEMEDIUM7;

USE ROLE TAG_ADMIN;

CREATE OR REPLACE TAG SPORTS ALLOWED_VALUES 'CRICKET','BADMINTON','FOOTBALL'
 COMMENT='SPORTS TAG';

Fig. 3 Creation of new Tag Sports

Apply TAG:

Once a tag is created, it can be applied to as many different Snowflake objects with the same or different string values. If the allowed values attribute is defined, the tag value must be one of the possible string values listed in the tag allowed values. Tags can be applied to all supported objects while creating the objects or the objects can be altered to apply the tag.

The below example shows how to apply TAG for the Snowflake object (Role):

CREATE OR REPLACE ROLE DATA_OWNER;
GRANT APPLY on TAG OBJECT_TAGGING_DEMO.OBJECT_TAGGING_SCHEMA.SPORTS to ROLE DATA_OWNER;

Fig. 4: Apply tag to a Role

The below example shows how to apply TAG for the Snowflake object (Warehouse):

CREATE OR REPLACE WAREHOUSE CRICKET_WH WITH 
TAG(OBJECT_TAGGING_DEMO.OBJECT_TAGGING_SCHEMA.SPORTS='CRICKET');

Fig. 5: Apply tag to a Warehouse

The below example shows how to apply TAG for the already existing Snowflake object (Warehouse):

ALTER WAREHOUSE BADMINTON_WH SET TAG 
OBJECT_TAGGING_DEMO.OBJECT_TAGGING_SCHEMA.SPORTS='BADMINTON';

Fig. 6: Appy tag to a already existing objects

Note: A maximum of 20 unique tags can be assigned to one Snowflake object and the assigned string value is limited to 256 characters.

How to discover the TAG?

The below statements return a list of all tag definitions within an account, database, and schema, respectively.

SHOW TAGS in ACCOUNT;
SHOW TAGS in DATABASE OBJECT_TAGGING_DEMO;
SHOW TAGS in SCHEMA OBJECT_TAGGING_DEMO.OBJECT_TAGGING_SCHEMA;

Fig. 7: To display all TAGS present in the account

Fig. 8: To display all TAGS present in the Database

Fig. 9: To display all TAGS present in the Schema

The next part (2) of this tutorial will cover more about Tagging Strategy, Tagging Inheritance and different type of TAG usecases.

References:-

• https://www.snowflake.com/

About Me:-

I am Data Engineer and Cloud Architect! and am currently working as a Senior Consultant in EY GDS. I have worked on many legacy data warehouses, big data implementations, cloud platforms and migrations. You can reach out to me in LinkedIn if you need any further help on certification, Data Solutions and Implementations!