In this blog, we’ll explore phData’s project administration tool (Tram) and how to use it for managing Snowflake object tagging.
What is Tram?
Tram is a self-service provisioning tool for the Snowflake Data Cloud that lets you create and manage different Snowflake objects, including users, roles, access privileges, databases, schemas, warehouses, and object tags.
Tram can fully manage and organize your Snowflake information architecture by defining the objects in simple configuration files using standard YAML syntax; these objects can be provisioned or deleted together in a repeatable way. You can also integrate Tram with IT ticketing tools such as ServiceNow and Jira, allowing you to provision all Snowflake objects and access requests based on the approval workflow.
Below are some common challenges we have addressed for our customers using Tram: phData’s Project Administration tool for Snowflake.
- How do I automate project onboarding in a repeatable, consistent way?
- How do I integrate Snowflake access requests with ITSM tools such as ServiceNow or Jira?
- How can I ensure the Snowflake account stays organized?
- How do I enable self-service for Snowflake?
- How do I keep my Snowflake account secure?
This article will mainly focus on Snowflake’s object tagging implementation using Tram and assumes you are familiar with phData Tram and Snowflake object tagging. In the next sections of this blog, we will achieve the following using Tram:
- Create a database and schema to store the tags.
- Create a custom role to manage the tags.
- Learn how to create and apply tags.
- Learn how to organize the tags effectively.
Create Tag Database and Schema
In this example, we will create a database and schema using Tram, which acts as a central repository for all our tag definitions. The below snippet in a Tram model file creates a database named governance and schema named tag_library.
databases:
- name: governance
schemas:
- name: tag_library
Create Custom Role and Assign Privileges
Snowflake recommends creating a custom role to create and manage tags(Example: tag_admin). Creating the custom role and assigning privileges need the below steps to be performed in order.
Privileges Managed by Tram
Use the below snippet in a Tram model file to create a custom role TAG_ADMIN and assign the required privileges.
# creates role TAG_ADMIN
roles:
- name: TAG_ADMIN
# Grants TAG_ADMIN role to Tram service account TRAM_USER
roleGrants:
- name: TAG_ADMIN
users:
- TRAM_USER
# privileges section grants the required privileges to TAG_ADMIN
#1 usage privileges on governance
#2 usage privileges on governance.tag_schema
#3 create tag privilege on governance.tag_schema
privileges:
- privilege: USAGE
objectType: DATABASE
objectName: governance
roleName: TAG_ADMIN
- privilege: USAGE
objectType: SCHEMA
objectName: "governance.tag_library"
roleName: TAG_ADMIN
- privilege: "CREATE TAG"
objectType: SCHEMA
objectName: "governance.tag_library"
roleName: TAG_ADMIN
Tram translates the above snippet into the below Snowflake statements. Tram is capable of identifying the dependencies and executing the statements in the right order.
# Statements generated by Tram
DATABASE
(+) CREATE DATABASE IF NOT EXISTS governance
SCHEMA
(+) CREATE SCHEMA IF NOT EXISTS governance.tag_library
ROLE
(+) CREATE ROLE IF NOT EXISTS TAG_ADMIN
PRIVILEGE
(+) GRANT USAGE ON DATABASE governance TO TAG_ADMIN
(+) GRANT USAGE ON SCHEMA governance.tag_library TO TAG_ADMIN
(+) GRANT CREATE TAG ON SCHEMA governance.tag_library TO TAG_ADMIN
ROLE GRANT
(+) GRANT ROLE TAG_ADMIN TO USER TRAM_USER
Privileges Managed by Account Admin
In addition to the above privileges, apply tag on account global privilege is required for TAG_ADMIN to apply tags to different Snowflake objects. Since apply tag privilege needs ACCOUNTADMIN, execute the below statements manually in Snowflake.
USE ROLE ACCOUNTADMIN;
GRANT APPLY TAG on ACCOUNT to ROLE TAG_ADMIN;
Configure Tag Admin Role in Tram
Tram supports configuring object owners to manage different Snowflake objects. For example, all databases are managed by SYSADMIN by default. This behavior can be changed by configuring object owners in the Tram application.properties file.
Add the below property in application.properties to manage the tag definitions and assignments using the tag admin custom role created in the previous section. Refer to the Tram user manual to learn more about configuring owner roles.
tram.owners.tags=TAG_ADMIN
Creating and Applying Tags in Tram
A tag must be created before it can be applied to supported objects in Snowflake. The below sections exemplify creating and applying a tag to Snowflake objects using Tram.
Create Tag
Tags are schema-level objects in Snowflake; thus, tag definition is enclosed within the schema definition in Tram. The below snippet creates two tags: department and region under the schema tag_library.
databases:
- name: governance
schemas:
- name: tag_library
tagObjects:
- name: "department" # (required)
comment: "department tag" # (optional)
allowedValues: # (optional)
- sales
- engineering
- name: "region" # (required)
comment: "region tag" # (optional)
allowedValues: # (optional)
- EMEA
- NA
- APAC
Tram generates the below statements based on the above snippet.
# Statements generated by Tram
TAG
(+) CREATE TAG IF NOT EXISTS governance.tag_library.department ALLOWED_VALUES 'sales','engineering' COMMENT = 'department tag'
(+) CREATE TAG IF NOT EXISTS governance.tag_library.region ALLOWED_VALUES 'EMEA','NA','APAC' COMMENT = 'region tag'
Apply Tag
Once a tag is created, it can be applied to other Snowflake objects across the account. In Tram, tag assignment is enclosed within the definition of Snowflake object to which the tag is being applied.
In the example below, department tag is assigned to a user object, and region tag is assigned to a role. While applying the tags in Tram, you must specify the fully qualified name for the tag (<database>.<schema>.<tag_name>).
users:
- name: "USER1"
tags:
governance.tag_library.department: sales
roles:
- name: "EMEA_READ_WRITE"
tags:
governance.tag_library.region: EMEA
Tram generates the below statements for the above example:
# Statements generated by Tram
# Statements for create role and create user are generated only if the objects do not exist already
ROLE
(+) CREATE ROLE IF NOT EXISTS EMEA_READ_WRITE
USER
(+) CREATE USER IF NOT EXISTS USER1
TAG
(+) ALTER USER USER1 SET TAG governance.tag_library.department='sales'
(+) ALTER ROLE EMEA_READ_WRITE SET TAG governance.tag_library.region='EMEA'
Organizing Tag Definitions in Tram
Tram allows you to organize all your tags in a single configuration file (group file). Although tags can be defined in multiple Tram group files, defining all the tags in a central place makes it easier to audit and make changes as needed. The tags created using this centralized approach can be applied to other Snowflake objects within other Tram group files.
In the example below, we define a parameterized pattern in a Tram model file to create a tag. The group file contains different members (tags) with the required parameters. Tram creates three tags (department, environment, and security_class) based on the below pattern.
*Tram group file definition (tags.yml).
model:
name: tag_objects
defaults:
tag_database: governance
tag_schema: tag_library
members:
- tag_name: department
comment: "department tag comment"
allowedValues: "sales,operations"
- tag_name: environment
comment: "environment tag comment"
allowedValues: "dev, int, prod"
- tag_name: security_class
comment: "environment tag comment"
allowedValues: "PII, PCI, PHI"
*Tram model file definition (tag_objects.yml).
databases:
- name: {{ tag_database }}
schemas:
- name: {{tag_schema}}
tagObjects:
- name: {{tag_name}}
{{#comment}}
comment: "{{comment}}"
{{/comment}}
{{#allowedValues}}
allowedValues: [{{allowedValues}}]
{{/allowedValues}}
Closing
Hopefully this blog has helped shine a little light on Tram and its use for managing Snowflake object tagging. Similar to object tagging, Tram can also manage other Snowflake objects by organizing them into workspaces consisting of project resources and the access control around them.
Whether you’re curious about seeing Tram in action or how phData can greatly accelerate your success Snowflake success story via our automation software and services, reach out today!
