Skip to main content

πŸ”’ Data Encryption in Snowflake: Ensuring Privacy & Granular Access

Encryption is a fundamental building block of data privacy and security. In Snowflake, data encryption ensures that sensitive data is protected at rest and in transit, while enabling granular access controls to balance security with usability.


πŸš€ What is Encryption?​

Encryption is the process of converting data into an unreadable format (ciphertext) using algorithms and keys. Only users with the right keys can decrypt and read the original data (plaintext). This ensures that even if data is accessed by unauthorized parties, it remains unreadable.


πŸ”‘ Key Encryption Concepts in Snowflake​

  • βœ… At-Rest Encryption
    Snowflake automatically encrypts all data at rest (data stored in tables, databases, or stage files).
  • Uses AES-256 strong encryption.
  • Transparent to the user – no extra configuration needed.
  • βœ… In-Transit Encryption
    All data moving in and out of Snowflake (queries, data loads/unloads) is encrypted using TLS (Transport Layer Security).
  • Prevents eavesdropping or tampering during transit.
  • βœ… Key Hierarchy
    Snowflake uses a hierarchical key management system:
  • Root keys
  • Account-level keys
  • Table-level keys
    Keys are automatically rotated for enhanced security.

βš™οΈ Granular Access Control & Encryption​

While encryption ensures data is secure at the storage layer, granular access control ensures only the right users can access decrypted data.

πŸ”Ή Role-Based Access Control (RBAC) – Roles determine who can see decrypted data.
πŸ”Ή Column-level security & masking – Works alongside encryption for even more granular protection.
πŸ”Ή Network policies & MFA – Add extra layers of security.


πŸ’‘ Encryption Use Cases in Snowflake​

  • βœ… Regulatory compliance – e.g., GDPR, HIPAA, PCI DSS.
  • βœ… Securing sensitive workloads – like financial data, healthcare data, or PII.
  • βœ… Cross-cloud data sharing – Snowflake’s secure sharing works with encrypted data.

🌟 Best Practices for Encryption in Snowflake​

  • βœ… Use built-in Snowflake encryption – No extra setup needed for AES-256 encryption at rest.
  • βœ… Layer security controls – Combine encryption with access controls, masking, and MFA.
  • βœ… Leverage customer-managed keys (if needed) – For even more control, Snowflake offers Tri-Secret Secure (where you bring your own key to work alongside Snowflake’s).
  • βœ… Monitor and audit – Use Snowflake’s Account Usage views to track key usage and access.

πŸ› οΈ Example Use Case: Protecting Financial Data with Encryption & Access Controls​

Let’s say you have a financial transactions table that stores sensitive data, like credit card numbers.

1️⃣ Create a Table to Store Sensitive Data​

CREATE TABLE financial_transactions (
transaction_id INT,
customer_name STRING,
credit_card_number STRING,
transaction_amount FLOAT
);
  • βœ… Note: Snowflake automatically encrypts this data at rest with AES-256 encryption.

2️⃣ Insert Some Sample Data​

INSERT INTO financial_transactions VALUES
(1, 'Ananya', '4111111111111111', 250.00),
(2, 'Rohan', '5500000000000004', 400.50);

3️⃣ Create a Masking Policy for the Credit Card Column​

Even though encryption at rest is already active, we want to add an extra layer:

  • βœ… Dynamic masking to restrict who can see the full card number.
CREATE MASKING POLICY mask_credit_card AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('FINANCE_ADMIN') THEN val
ELSE 'XXXX-XXXX-XXXX-' || RIGHT(val, 4)
END;

4️⃣ Apply the Masking Policy to the Column​

ALTER TABLE financial_transactions
MODIFY COLUMN credit_card_number
SET MASKING POLICY mask_credit_card;

5️⃣ Test Queries with Different Roles​

πŸ” As a Regular User (no finance admin privileges):​

-- Assume CURRENT_ROLE() is 'ANALYST'
SELECT transaction_id, customer_name, credit_card_number
FROM financial_transactions;

Output:

transaction_idcustomer_namecredit_card_number
1AnanyaXXXX-XXXX-XXXX-1111
2RohanXXXX-XXXX-XXXX-0004

πŸ” As a Finance Admin User (has FINANCE_ADMIN role):​

-- Assume CURRENT_ROLE() is 'FINANCE_ADMIN'
SELECT transaction_id, customer_name, credit_card_number
FROM financial_transactions;

Output:

transaction_idcustomer_namecredit_card_number
1Ananya4111111111111111
2Rohan5500000000000004

πŸ” Putting it All Together​

  • βœ… Encryption at rest: Snowflake encrypts all stored data automatically.
  • βœ… Granular access: Masking policies hide sensitive parts of the data dynamically based on roles.
  • βœ… No changes to queries: Users write the same SELECT statement, but see only what they’re allowed to see!

πŸ› οΈ Manual Column-Level AES-256 Encryption (Client-Side)​

Snowflake’s built-in AES-256 encryption cannot be manually enabled/disabled at column-levelβ€”it’s always on. However, you can encrypt data before inserting it into Snowflake (client-side encryption).

πŸ”Ή Encryption Example with Python​

from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
import os
from cryptography.hazmat.primitives import padding

plaintext = b'4111111111111111' # Example credit card number
key = os.urandom(32) # Securely store this key
iv = os.urandom(16)

cipher = Cipher(algorithms.AES(key), modes.CBC(iv))
encryptor = cipher.encryptor()

padder = padding.PKCS7(128).padder()
padded_data = padder.update(plaintext) + padder.finalize()

ciphertext = encryptor.update(padded_data) + encryptor.finalize()
print(ciphertext.hex()) # Store in Snowflake

πŸ”Ή Storing in Snowflake​

CREATE TABLE encrypted_data (
id INT,
encrypted_card_number STRING, -- ciphertext (hex)
iv STRING -- IV (hex)
);

INSERT INTO encrypted_data VALUES
(1, 'f2c33c4e2a64c3f...', 'ecff1a334a332bc5...');

πŸ”Ή Decryption (Client-Side)​

cipher = Cipher(algorithms.AES(key), modes.CBC(iv))
decryptor = cipher.decryptor()

padded_plaintext = decryptor.update(ciphertext) + decryptor.finalize()
unpadder = padding.PKCS7(128).unpadder()
plaintext = unpadder.update(padded_plaintext) + unpadder.finalize()
print(plaintext.decode())

πŸ’‘ Key Takeaways​

βœ”οΈ Automatic AES-256 encryption in Snowflake – always on, no user config needed. βœ”οΈ Client-side encryption can add column-level protection if needed (no built-in SQL decryption). βœ”οΈ Masking policies + RBAC roles = granular, dynamic access to decrypted data. βœ”οΈ Perfect for PCI DSS, GDPR, and secure data-sharing scenarios.