Skip to content

Format-Preserving Encryption on Snowflake

Format-Preserving Encryption (FPE) on Snowflake encrypts data in place, so encrypted values keep the format your queries, joins, and downstream tools expect. You encrypt the data with the ALTR_FPE_ENCRYPT function, using a key and a tweak you create in ALTR. Contact ALTR to enable FPE. ALTR creates the function in the ALTR_DSAAS_DB database when it connects a Snowflake data source for an organization with FPE enabled.

To set up FPE on Snowflake:

  1. Create a key and a tweak.
  2. Grant a Snowflake role access to the encryption functions.
  3. Encrypt the data.
  4. Connect the encrypted column or tag to ALTR with the FPE protection type, and select the Key, Tweak, and Alphabet Type you encrypted it with. If you encrypted with padding, also select Encrypted with padding.
  5. Set up decryption for the roles that should see plaintext.

You create keys and tweaks on the Key Management page in ALTR. Key and tweak names are case sensitive, 256 characters or fewer, and can’t start or end with a space. Each key name and each tweak name must be unique.

To create a key:

  1. Select Data Configuration > Key Management in the navigation menu.
  2. Click Create Key.
  3. Enter a Key Name.
  4. Click Save.

To create a tweak:

  1. Select Data Configuration > Key Management in the navigation menu.
  2. Select Tweaks in the Type list.
  3. Click Create Tweak.
  4. Enter a Tweak Name.
  5. Click Save.

A Snowflake role that encrypts data needs the USAGE privilege on the database, the schema, and each function it calls.

The encryption functions live in the ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID> schema, where <ORG_ID> is your ALTR Organization ID in uppercase with dashes replaced by underscores. Your ALTR Organization ID is on the Organization tab under Settings > Preferences – see Settings and Preferences.

To grant access, run these GRANT statements as a role that can grant privileges on these objects, such as ACCOUNTADMIN, replacing <ROLE_NAME> with the role that encrypts data:

GRANT USAGE ON DATABASE ALTR_DSAAS_DB TO ROLE <ROLE_NAME>;
GRANT USAGE ON SCHEMA ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID> TO ROLE <ROLE_NAME>;
GRANT USAGE ON FUNCTION ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO ROLE <ROLE_NAME>;
GRANT USAGE ON FUNCTION ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT_ARRAY(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO ROLE <ROLE_NAME>;

ALTR_FPE_ENCRYPT takes 4 arguments: the value to encrypt, the key name, the tweak name, and the alphabet – alphabetic, numeric, or alphanumeric. It returns the encrypted value.

To encrypt every value in a column, run:

UPDATE <TABLE>
SET <COLUMN> = ALTR_DSAAS_DB.ALTR_DSAAS_<ORG_ID>.ALTR_FPE_ENCRYPT(
<COLUMN>, '<KEY_NAME>', '<TWEAK_NAME>', '<ALPHABET>'
);

To encrypt only rows loaded since the last run, add a WHERE clause on a column that identifies those rows, such as a load timestamp, so no value is encrypted twice. An encrypted value has the same format as its plaintext, so the column’s own values can’t tell you which rows are already encrypted.

ALTR_FPE_ENCRYPT_ARRAY encrypts several values in one call. It takes a JSON array of values as a string, followed by the key name, the tweak name, and the alphabet, and returns an array of encrypted values.

If padding is enabled for your organization, ALTR_FPE_ENCRYPT and ALTR_FPE_ENCRYPT_ARRAY also accept a fifth argument, deterministic or nondeterministic, that sets the padding type. A role that passes the fifth argument also needs USAGE on the 5-argument version of each function it calls, for example ALTR_FPE_ENCRYPT(VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR).