April 10, 2023

Dynamically Assign Snowflake Roles and Warehouses with Sigma User Attributes

During my time as a Snowflake Sales Engineer, I was frequently asked two big questions when customers were building a new data environment: (1) How do I govern access to my data, and (2) how do I assign cost to users?

At Sigma, we’ve launched a new way to address these questions easily.

Today, controlling access to data is crucial as organizations strive to adapt to evolving security threats, satisfy regulatory requirements, and build trust with their customers. Snowflake contains built-in features like row-level and column-level security policies that fit squarely under the larger umbrella of role-based access control. This ensures that end users are only able to access the data they need. In addition, Snowflake’s Zero Trust security framework follows the industry standard Principle of Least Privilege, allowing organizations to give users access to the least amount of data they need for their job function.

Attributing spend in Snowflake might not be straightforward to all of its customers. Spend is tied to virtual warehouses (i.e. compute clusters). By providing each user group with a dedicated warehouse, one may attribute spend directly to a specific user group. This method works great for users and groups with direct access to Snowflake, but may become complicated when using shared resources. Most end-user groups access Snowflake through a BI & Analytics platform that typically has a single warehouse and role associated with said platform.

To reduce complexity, Sigma launched User Attributes for Snowflake Connections in January 2023. This allows organizations to dynamically assign a user’s Snowflake role and warehouse when connected to Snowflake from Sigma.

Why One Warehouse Per Connection Doesn't Work

Most BI & Analytics platforms create connections directly from the BI & Analytics platform to the data warehouse with a single Snowflake role and warehouse assigned to represent all users of the platform. This means regardless of how many different teams in an organization are leveraging the BI & Analytics platform to drive compute against Snowflake, Snowflake admins will see all of this usage lumped together (e.g., if sales, marketing, and finance worked through the same platform). For modern organizations, the desire to optimize costs can lead to data platform teams spending hours building complex calculations to try and parse out exactly what percentage of spend can be attributed to different groups. Additionally, very large enterprises could struggle with concurrency if a high number of users are tied to a single warehouse, as Snowflake’s multi-clustering behavior designed to assist with concurrency has a default setting to spin out up to 10 clusters. Lastly, a singular Snowflake role per connection renders role-based control policies incompatible, and may lead to updating security within the BI & Analytics platform itself.

The Sigma Solution

Sigma’s new User Attributes functionality allows Sigma admins to assign users to teams, then designate a Snowflake role and warehouse for each team, rather than lumping all users together into a single Snowflake role and warehouse. Getting started with this functionality is easy. The setup takes minutes and unlocks significant benefits for both Sigma and Snowflake users.

Imagine, for instance, you are in charge of a data platform for a fictitious retailer called Plugs Electronics. Within your sales transactions table there are attributes around products sold, the location where the sale happened, and potentially, even the customer who made the transaction. You want your sales and marketing teams to be able to see this data to improve future marketing campaigns and sales performance, but only want them to be able to see data that applies to them.

Leveraging column and row security policies in Snowflake, you can mask data for the sales team based on the covered sales region, so that those who support the West Region can only see data for the West Region, even though the data may all be co-mingled within a single table. Now that the data is secure in Snowflake, you want your team to be able to explore this data using your organization's analytics tool. You establish a connection to the warehouse and assign a default role, but there is a problem—you have a South Region sales team, an East Region sales team, and a West Region sales team, all of whom need to access the data from their team’s assigned Snowflake role.

If your team uses Sigma for analysis, admins can easily create these groups in Sigma, assign them their needed role and warehouse, then allow their teams to explore the data set. Because Sigma calls the role assigned to these groups in Snowflake, the role-based security placed on the data set will pass through to Sigma and return only the rows and columns each team has access to. Not only can you now be assured the West Region team can only see data for the West, but you can assign a specific warehouse to this team, making it easily visible in Snowflake which team has been driving the most compute, and therefore spend, as their queries pushed down from Sigma will now be neatly tied to their designated compute cluster. Additionally, you’ll no longer have to be concerned about concurrency issues reducing performance for your data users. That’s because you now have multiple warehouses with the ability to spin out additional clusters powering your analytics, rather than relying on a single warehouse’s multi-clustering capabilities.

CONCLUSION

New Sigma functionality, User Attributes, is easy to set up and lets you secure data for both internal user groups and customer-facing applications. It can help you take advantage of Snowflake’s powerful governance functionality for admins. Through Sigma’s User Attributes, Snowflake administrators are able to attribute spend directly to specific user groups, prevent concurrency issues, and ensure access to data is confined to appropriate users in a matter of minutes - a triple win!

The followings are additional resources to learn more about Sigma: