In this blog, we’ll explore phData’s Provision Tool and how to use it for managing Snowflake object tagging.
What is The Provision Tool?
The Provision Tool 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.
The Provision Tool 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 The Provision Tool 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 phData’s Provision Tool:
- 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 The Provision Tool and assumes you are familiar with the Provision Tool and Snowflake object tagging. In the next sections of this blog, we will achieve the following using the Provision Tool:
- 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 Resources
In this example, we will create a database and schema along with the required privileges using the Provision Tool, which acts as a central repository for all our tag definitions. The below snippet in The Provision Tool template file creates a database named governance and a schema named tag_library and the required privileges.
# creates ROLE to manage tags
roles:
- name: TAG_ADMIN
# Grant tag_role to provision tool service account role
roleGrants:
- name: TAG_ADMIN
toRoles:
- PROVISION_ADMIN
# creates Tags Database
databases:
- name: governance
# creates Tags Schema in Tag Database
schemas:
- database: governance
name: tag_library
# Grants TAG_ADMIN role to access to create tags in tag database/schema
privilegeGrants:
- 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
The Provision Tool translates the above snippet into the below Snowflake statements. The Provision Tool is capable of identifying the dependencies and executing the statements in the right order.
# Statements generated by Provision Tool
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
(+) GRANT USAGE ON DATABASE governance TO ROLE TAG_ADMIN
(+) GRANT USAGE ON SCHEMA governance.tag_library TO ROLE TAG_ADMIN
(+) GRANT CREATE TAG ON SCHEMA governance.tag_library TO ROLE TAG_ADMIN
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;
Creating and Applying Tags in the Provision Tool
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 the Provision Tool.
Create Tag
Tags are schema-level objects in Snowflake; thus, tag definition is enclosed within the schema definition in The Provision Tool. The below snippet creates cost_center tag under the database governance and the schema tag_library.
tags:
- name: cost_center
database: governance
schema: tag_library
allowedValues:
- BU1
- BU2
properties:
comment: example tag
The Provision Tool generates the below statements based on the above snippet.
# Statements generated by The Provision Tool
TAG
(+) CREATE TAG IF NOT EXISTS governance.tag_library.cost_center ALLOWED_VALUES 'BU1','BU2' COMMENT = 'example tag'
Apply Tag
Once a tag is created, it can be applied to other Snowflake objects across the account. In the Provision Tool, tag assignment is enclosed within the definition of a Snowflake object to which the tag is being applied.
In the example below, the cost_center tag is assigned to a database object. While applying the tags in the Provision Tool, you must specify the fully qualified name for the tag (<database>.<schema>.<tag_name>).
database:
- name: "Database1"
tags:
governance.tag_library.cost_center: BU1
The Provision Tool generates the below statements for the above example:
# Statements generated by the Provision Tool
DATABASE
(+) CREATE DATABASE IF NOT EXISTS Database1 with TAG governance.tag_library.cost_center = 'BU1'
Closing
Hopefully this blog has helped shine a little light on the Provision Tool and its use for managing Snowflake object tagging. Similar to object tagging, the Provision Tool 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 the Provision Tool in action or how phData can greatly accelerate your success Snowflake success story via our automation software and services, reach out today!
