Snowflake for SQL Server Users - Part 20 - Encryption within Snowflake

Snowflake for SQL Server Users - Part 20 - Encryption within Snowflake

As with most products today, Snowflake has substantial handling of encryption to protect client data.

All editions claim to provide “Always-on enterprise grade encryption in transit and at rest”.

In Transit

First, for connections, HTTPS and TLS 1.2 are used throughout the system.

If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake. To do that, you create an “encrypted stage” where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

create stage webtraffic_stage
url='s3://corporate-bucket/data/'
credentials=(aws _key_id='SDWESS22' aws_secret_key='72828382')
encryption=(master_key='fSyY1jzYfIntsdfsKOEOxq80Au6NbSgPH5r4BDDwhHfd=');

This is useful in allowing you to create an end-to-end encryption while ingesting data into Snowflake.

At Rest

Regarding “at rest”, I’ve previously described how micropartitions are used to store data in data files. These data files are then encrypted (using AES-256) before being stored.  But how it’s handled changes with different editions of Snowflake.

There are four levels of keys used:

  • The root key
  • Account master keys
  • Table master keys
  • File keys

With Standard and Premier editions, a different file key is used each month (aka key rotation). So a single “active” key is used to encrypt your first month’s data, then a different key is used to encrypt the next month’s data, and so on. Previous keys that are no longer “active” (aka retired) are only used for decryption.

Annual Rekey

Enterprise edition and above offer “Annual rekey of all encrypted data”. I was a bit puzzled about how that worked at first. I imagined that once a year, all the existing data would get decrypted and rekeyed. I was thinking that would be quite an expensive operation on a large data warehouse.

What happens instead, is that when any key has been retired for a year, any data encrypted by it is decrypted and re-encrypted using a new key. So if you have those editions, if you store data this month, it will have a single key. In a year and one month’s time, it will get rekeyed. And again in two years and one month’s time, and so on.

Business Critical

The Business Critical edition takes things further. As well as providing  HIPAA and PCI compliance, it offers “Tri-Secret Secure using customer-managed keys”. In this case, Snowflake combines the key that you provide with a Snowflake-maintained key to create a composite master key.

As well as having the obvious benefits in letting you manage access to the data more closely, you can immediately remove all access if needed (i.e. closing an account, or recovering from a breach).

For an index to all posts in this series, see the first post here.

2019-12-20