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
- name: TAG_ADMIN
# Grant tag_role to provision tool service account role
- name: TAG_ADMIN
# creates Tags Database
- name: governance
# creates Tags Schema in Tag Database
- database: governance
# Grants TAG_ADMIN role to access to create tags in tag database/schema
- privilege: USAGE
- privilege: USAGE
- privilege: "CREATE TAG"
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
(+) CREATE DATABASE IF NOT EXISTS governance
(+) CREATE SCHEMA IF NOT EXISTS governance.tag_library
(+) CREATE ROLE IF NOT EXISTS TAG_ADMIN
(+) 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.
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
- name: cost_center
comment: example tag
The Provision Tool generates the below statements based on the above snippet.
# Statements generated by The Provision Tool
(+) CREATE TAG IF NOT EXISTS governance.tag_library.cost_center ALLOWED_VALUES 'BU1','BU2' COMMENT = 'example 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
- name: "Database1"
The Provision Tool generates the below statements for the above example:
# Statements generated by the Provision Tool
(+) CREATE DATABASE IF NOT EXISTS Database1 with TAG governance.tag_library.cost_center = 'BU1'
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!