Advanced Data Warehousing
May 24, 2024
Snowflake Projection Policies
Protecting sensitive information within your data warehouse is a critical aspect of data governance. While access controls and masking policies provide essential safeguards, they don't always offer the fine-grained control you need to restrict specific columns from appearing in query results. That's where Snowflake Projection Policies come in.
Understanding Projection Policies
Projection policies are a powerful tool in Snowflake that allows you to define rules determining whether a column can be included in the output of a query. This enables you to:
Prevent Accidental Exposure: Avoid unintended disclosure of sensitive data by restricting certain columns from being displayed.
Comply with Regulations: Implement data minimization principles by showing only necessary columns for specific use cases.
Customize Data Access: Tailor what data users see based on their roles or responsibilities.
How Projection Policies Work
Create a Policy: Define a policy in SQL that specifies the conditions under which a column can be projected (included in query results).
Assign the Policy: Apply the policy to the specific column(s) you want to restrict.
Enforcement: Snowflake automatically enforces the policy, blocking queries that attempt to project restricted columns unless the conditions are met.
A Practical Example: Sales Data Analysis
Let's explore how to use Snowflake Projection Policies in a practical scenario.
⛁ Sample Data Model: salesdb-data-model
Setup the SalesDB sample data model and proceed with the rest of the exercises. This model provides a robust foundation for tracking customers, buyers, clients, and sales opportunities.
>_ SQL
Let's delve into how this Snowflake feature can supercharge your data workflow:
Protecting Customer PII With Projections
Let's use your sales data model (SalesDB.Custs) to illustrate how projection policies work.
Scenario:
You want to restrict access to certain personally identifiable information (PII) such as email addresses (Email) and phone numbers (Phone) in your Customer and Buyer tables. Only users with the SalesManager role should be able to see this information.
1. Creating Projection Policies:
>_ SQL
This will create 2 projection policies to restrict Email and Address visibility. It will only allow those with the SalesManager role to view it.
2. Assigning the Policies:
>_ SQL
3. Testing the Policies:
>_ SQL
Testing now with a role that has privileges for the projections.
>_ SQL
As you can see above, the columns will only show if the role running the query has permission to see it.
Important Note: It's important to note that projection policies limit access also through the snowsight schema and preview views.
Object browser Preview is limited
Schema Preview is limited due to projection
Removing Projection Policies
>_ SQL
Discovering Projection Policies
>_ SQL
Beyond Basic Restrictions
Projection policies can be more sophisticated than just role-based restrictions:
Conditional Logic: You can use complex SQL expressions to define the policy conditions. For example, a policy could allow access to a column only if the user belongs to a specific department and the current date is within a certain range.
Data Sharing: Projection policies are invaluable when sharing data with external parties. You can ensure that only specific columns are shared, protecting sensitive information.
Conclusion
Snowflake Projection Policies provide a powerful and granular way to control data visibility within your Snowflake environment. By defining and assigning appropriate policies, you can protect sensitive information, adhere to data privacy regulations, and empower different users with tailored access to the data they need.
Resource:
For detailed documentation and more advanced use cases, refer to Snowflake's official documentation on projection policies:
https://docs.snowflake.com/en/user-guide/projection-policies