Administering Roles and Users

Snowflake uses roles for managing access and operations. In other words, you can create custom roles with a set of privileges to control the granularity of the access granted. For instance, say we want to create a role for our marketing team, which will grant the team members access to the data and allow them to run SQL queries using a virtual warehouse. According to the Snowflake model, access to securable objects is managed by privileges assigned to roles. Moreover, roles can be assigned to other roles and users.

Figure 7-1 shows an example of the Marketing role that grants the privileges USAGE, MODIFY, and OPERATE to the securable objects DATABASE and WAREHOUSE for marketing users.

You can create custom roles with the SECURITYADMIN role, or you can grant the CREATE ROLE privilege to any new role. For any custom role, you should think about the role hierarchy in order to assign your new custom role to the one of the high-level administration roles. Figure 7-2 shows an example of this hierarchy. It shows the Marketing role, which has privileges for the marketing database, schema, and warehouse that belong to the SYSADMIN role.

Enforcement Model

When you are connecting your Snowflake account with the web interface or ODBC/JDBC, a session is initiated, and it has a current role that consists of managed permissions for user. It is possible to change the role using the USE ROLE command or to switch roles by using the menu in the top-right corner.

When a user wants to perform any action in Snowflake, Snowflake will compare the user’s role privileges against the required privileges.

Note

You may be familiar with the concept of a super-user or super-role with other database vendors, but you will not find this functionality in Snowflake. All access requires the appropriate access privileges.

Working with Roles and Users

Snowflake allows you to control your data warehouse at a granular level within roles. To create a role, you can execute DDL commands or use the web interface. The following commands are available:

• CREATE ROLE: Creates a new role

• ALTER ROLE: Changes an existing role

• DROP ROLE: Drops an existing role

• SHOW ROLES: Shows a list of available roles

• USE ROLE: Switches a role for the session

Let’s create a new role. Log into your Snowflake account and make sure that you choose the SECURITYADMIN role.

CREATE ROLE MARKETING_TEAM;

This command will create a role. Next, grant permissions for this role and attach users. The following commands are available for user management:

• CREATE USER: Creates a new user

• ALTER USER: Changes an existing user

• DESCRIBE USER: Describes an existing user

• SHOW PARAMETERS: Shows the parameters of the user

In addition, we can specify the following options for users:

• userProperties: Properties such as password, display name, and so on.

• sessionParams: Session options such as default warehouse and namespace

Let’s run a command that will create a new user and assign him to the MARKETING_TEAM role.

CREATE USER marketing_analyst PASSWORD = 'RockYourData' COMMENT = 'Marketing Analyst' LOGIN_NAME = 'marketing_user1' DISPLAY_NAME = 'Marketing_Analyst' DEFAULT_ROLE = "MARKETING_TEAM" DEFAULT_WAREHOUSE = 'SF_TUTS_WH' MUST_CHANGE_PASSWORD = TRUE;

GRANT ROLE "MARKETING_TEAM" TO USER marketing_analyst;

You can achieve the same result using the web interface.

The last part of the code grants privileges for the MARKETING_TEAM role so new users can run SQL queries. We should grant OPERATE and USAGE for the virtual warehouse to our new role, like this:

GRANT USAGE ON WAREHOUSE SF_TUTS_WH TO ROLE MARKETING_TEAM;

GRANT OPERATE ON WAREHOUSE SF_TUTS_WH TO ROLE MARKETING_TEAM;

Note

SF_TUTS_WH is a small virtual warehouse that was created previously, but you can use your own warehouse. For demo purposes, it is always good to use the smallest computing instance.

Again, we can use the web interface to perform the same actions. Then we can log in with a new user, using login marketing_user1, and run this sample query:

SELECT * FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."REGION"

If you want to achieve the same result using the web interface, you should navigate to the Account menu, as shown in Figure 7-4.

By default the Account menu is available for the role ACCOUNTADMIN. This menu is usually accessible for Snowflake administrators. It allows them to manage users and roles, control credit usage, and so on.

Administering Resource Consumption

The next important topic for Snowflake administrators is resource consumption. Keeping track of storage and compute resources is critical for Snowflake customers. Snowflake provides administrative capabilities for monitoring credit and storage usage as well as provides resource monitors that can send alerts on usage spikes and automatically suspend the virtual warehouse.

By default, only the ACCOUNTADMIN role has access to the billing information. But this access can be provided for other users and roles with the monitor usage permissions.

As you know, Snowflake has a unique architecture that splits compute resources (virtual warehouses) and data storage. The cost of Snowflake consists of these two elements and is based on credits. In our case, when we created the Snowflake demo account for this book, we were granted 400 credits, and we are tracking consumption.

Virtual Warehouse Usage

You are already familiar with virtual warehouses (VWs) and its T-shirt sizes. Snowflake will charge credits for using a VW. In other words, the price depends on the number of VWs, their size, and how long they are running queries.

Note

Credits are billed per second, with a 60-second minimum.

You can use the table function WAREHOUSE_METERING_HISTORY that will show us hourly credit usage, or you can use web interface and click Account ➤ Billing & Usage. Let’s run this code to see the usage for the last seven days:

select * from table(information_schema.warehouse_metering_history(dateadd('days',-7,current_date())));

In addition, we can specify the VW name as a parameter. Figure 7-5 shows an example of sample usage.

Data Storage Usage

Another aspect of the price is storage. Snowflake calculates the price of storage monthly based on the average daily storage space. It includes files stored in the Snowflake stage, data stored in databases, and historical data maintained for a fail-safe. Moreover, time traveling and cloned objects are consuming storage. The price is based on a flat rate per terabyte (TB).

Note

The TB price depends on the type of account (capacity or on-demand), region, and cloud provider.

We can review the usage data using the web interface, as shown in Figure 7-6.

Also, you can leverage table functions and a Snowflake view, as shown here:

#Database Storage for last 7 days

select * from table(information_schema.database_storage_usage_history(dateadd('days',-7,current_date()),current_date()));

#Stage Storage for last 7 days

select * from table(information_schema.stage_storage_usage_history(dateadd('days',-7,current_date()),current_date()));

#Table Storage utilization

select * from table_storage_metrics

Note

Make sure that data is in a compressed format in the Snowflake staging area. Another consideration is to use external storage options like Amazon S3 where you can set the data lifecycle policy and archive cold data.

Administering Databases and Warehouses

There are a number of actions we can do with databases and warehouses. As usual, you have a choice to use the web interface or execute SQL commands.

We covered VWs in Chapter 2. In this section, we will review actions that we can do with VWs and databases.

Zero-Copy Cloning

Another unique feature is zero-copy cloning, which creates a snapshot of a database. This snapshot is writable and independent. These types of features are like a “dream come true” for data warehouse DBAs.

There are many situations where people need to copy their database to test or experiment with their data to avoid altering their sensitive production database. However, copying data can be painful and time-consuming because all the data needs to be physically moved from the production database to the database copy. This is extremely expensive because both copies of the data need to be paid for. When a production database gets updates, the database copy becomes stale and requires an update.

Snowflake takes a different approach. It enables us to test and experiment with our data more freely. It allows us to copy databases in seconds. Snowflake doesn’t physically copy data. It continues to reference the original data and will store new records only when you update or change the data; therefore, you will pay for each unique record only once. Finally, we can use zero-copy cloning with the Time Travel feature.

Figure 7-7 shows an option for cloning a database using the web interface.

As usual, we have the option to execute a command. Here are examples of commands with definitions:

--Clone a database and all objects within the database at its current state:

create database mytestdb_clone clone mytestdb;

--Clone a schema and all objects within the schema at its current state:

create schema mytestschema_clone clone testschema;

--Clone a table at its current state:

create table orders_clone clone orders;

--Clone a schema as it existed before the date and time in the specified timestamp:

create schema mytestschema_clone_restore clone testschema before (timestamp => to_timestamp(40*365*86400));

--Clone a table as it existed exactly at the date and time of the specified timestamp:

create table orders_clone_restore clone orders at (timestamp => to_timestamp_tz('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));

Administering Account Parameters

Parameters control the behavior of our Snowflake account, individual user sessions, and objects. All parameters are available on the Snowflake documentation page.

Moreover, we can look the parameters for a specific database or warehouse.

Administering Database Objects

One of the most common administration tasks within Snowflake is to manage database objects such as tables, views, schemas, stages, file formats, and so on.

Moreover, Snowflake Administartor may leverage Snowflake unique capabilities like UNDROP and zero-copy cloning.

As a Snowflake administrator, you may need to manage these objects.

Administering Data Shares

Secure data shares are another unique feature of Snowflake and will be covered in Chapter 10. This feature allows you to become a data provider by creating a data share using the CREATE SHARE command . By default, this is available only for the ACCOUNTADMIN role.

In some cases, if you don’t need to share anymore and want to drop it, you should consider the downstream impact for all consumers. As an option, you may revoke grants on some objects and see the result.

Administering Clustered Tables

As you know, Snowflake is a data warehouse as a service. The idea here is simple: you just use the data warehouse, and you don’t need to think about data distribution, sorting, and table statistics.

One aspect of Snowflake performance is micro-partitioning. When we are loading data into Snowflake, it is automatically divided into micro-partitions with 50 MB to 500 MB of compressed data. These micro-partitions are organized in a columnar fashion. In addition, Snowflake collects and stores metadata in micro-partitions. This helps to optimize query plans and improve query performance by avoiding unnecessary scanning of micro-partitions through an operation known as partition pruning .

Snowflake also stores data in tables and tries to sort it along natural dimensions such as date and/or geographic regions. This is called data clustering , and it is a key factor for query performance. It is important, especially for large tables. By default, Snowflake uses automatic clustering. However, in some cases we may define the clustering key within the CREATE TABLE statement to change the default behavior. This should be an exception rather than a rule. In most cases, admins will not need to cluster. Best practice is to avoid clustering unless there is a specific query pattern that does not meet the SLA. In general, you should not need to cluster unless the table is at least 1 TB.

As a Snowflake administrator, you may need to review table clustering and run reclustering processes to identify all the problem tables and provide the best possible performance.

If you need to improve the clustering of data, you should create a new table with a new clustering key and insert data into the new table, or you can use materialized views (MVs) to create a version of the table with the new cluster key. Then the MV function will automatically keep the MV data in sync with the new data added to the base table.

Snowflake Materialized Views

When working with Teradata, we worked with materialized views (MVs) a lot. Basically, we had a complex SQL query that could produce metrics and dimensions for a business intelligence solution. Because of complex SQL logic, joins, and derived columns, we used MVs for improving query performance. However, traditional MVs had their downsides. For example, it was important to keep data in the VM up-to-date and refresh it daily with an ETL process. In addition, we experienced slowdowns in performance while updating the MVs using Data Manipulation Language commands.

Snowflake engineers didn’t abandon the MV concept and added this functionality to Enterprise Edition. According to Snowflake, a materialized view is a precomputed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is precomputed, querying a materialized view is faster than executing the original query. This performance difference can be significant when a query is run frequently or is sufficiently complex.

The main benefit of Snowflake MVs is that they solve the issues of traditional MVs. MVs are views that are automatically maintained by Snowflake. There is a background service that updates the materialized view after changes are made to the base table. This is more efficient and less error-prone than manually maintaining the equivalent of a materialized view at the application level.

Summary

In this chapter, we covered the main Snowflake administrative duties (e.g., user and role administration), and you learned about key Snowflake objects (e.g., warehouses and schema-level objects). In addition, we reviewed billing and usage information. Finally, we covered data shares and data clustering concepts as well as materialized views.

In the next chapter, you will learn about one of the key elements of cloud analytics: security.