Skip to main content

Format-Preserving Encryption

Note

This documentation outlines the in-product format-preserving encryption feature. We continue to support our Snowflake Native App version of format-preserving encryption. Learn more.

ALTR’s format-preserving encryption (FPE) feature is compute-based protection that protects sensitive data at rest while maintaining the usability of the original plaintext. Data can be encrypted directly in your Snowflake account or encrypted upstream (Shift Left) in your source system. FPE ensures that encrypted data has the same format as the original data, so the resulting ciphertext has similar length, alphabet and special characters as the source values, and thus can readily be used as-is in their encrypted state in downstream analytic tools. ALTR manages access to the underlying encryption keys, enabling fine-grained control over decryption while ensuring that your data never leaves your platform.

ALTR’s format preserving encryption supports alphanumeric, alphabetic and numeric data within string columns. ALTR currently requires minimum character length to ensure compatibility with the FF3-1 algorithm. Refer to the following table for details on field alphabet and length support:

Alphabet Type

Characters

Example

Minimum Characters

Alphanumeric

  • a-z

  • A-Z

  • 0-9

  • Plaintext: cza-0019

  • Ciphertext: p05-m2WQ

4

Alphabetic

  • a-z

  • A-Z

  • Plaintext: phone

  • Ciphertext: LpwOf

4

Numeric

0-9

  • Plaintext: 123-12-1234

  • Ciphertext: 812-01-2345

6

Note

With FPE, ALTR manages encryption key access. Encryption and decryption of data occurs in Snowflake. ALTR does not access your plaintext nor ciphertext when encrypting or decrypting data.

Uses

Format-preserving encryption is suited to protect sensitive data such as Personally Identifiable Information (PII) or financial information that meet the minimum value length requirements. The following table outlines examples of data that can be protected using FPE and ciphertext for given alphabets.

Example Data Type

Example Values

Example Ciphertext and Alphabet Type

Social Security Numbers

  • 123 45 6789

  • 111-11-1111

  • 987654321

  • 649 92 1047 | numeric

  • 947-61-2246 | numeric

  • 1hFl9s68D | alphanumeric

Email Addresses

  • steve@aol.com

  • janice1987@google.com

  • robert@hotmail.com

  • kFosU@ghs.lFo | alphabetic

  • kAlDic1987@flsOfL.coa | alphabetic

  • f0Bk62@h9f5Hil.c91 | alphanumeric

Phone Numbers

  • (178)-555-0398

  • 973 555 9401

  • +44 203 555 4377

  • (738)-016-6189 | numeric

  • a81 lj7 96g1 | alphanumeric

  • +71 048 914 1195 | numeric

Features

Encryption and Decryption

  • Use of NIST standard FF3-1 algorithm for encryption and decryption

  • Seamless integration with ALTR's Data Access Controls for access control over data decryption

  • Ability to mask results for users who decrypt data

  • Usability of ciphertext for common SQL operations and downstream systems via special character preservation and determinism

  • Ability to encrypt data in Snowflake using a simple user-defined function (UDF) or encrypting data upstream / further left in your data pipeline using the Snowflake REST API

Key Management

  • Easy key and tweak creation, management and deactivation with ALTR’s UI or REST API

  • Assignment of and tweaks to individual Snowflake columns for fine-grained access control or Snowflake tags for highly scalable data security

  • Key naming for easy reference and differentiation across datasets

  • Key rotation is facilitated without performing an expensive re-encryption of your data

Supported Snowflake Data Types

ALTR’s FPE solution supports string (varchar columns) Snowflake data types with alphanumeric, numeric or alphabetic alphabet types.

Note

Note that numbers within string columns are supported, but integer and other numeric data types are not. This is due to the potential of the FPE algorithm generating leading zeroes that would be dropped by Snowflake. For example, if the number 123456 was encrypted and the ciphertext became 012345, Snowflake would remove the leading zero and the value would become 12345. This would result in the wrong plaintext being generated once the data is decrypted.

How Format-Preserving Encryption Works

FPE leverages the encryption keys and tweaks to encrypt data at rest and decrypt data at query runtime. Customers create and manage encryption keys using the ALTR platform. Encryption is performed using a Snowflake user-defined function and decryption is performed using an integration with Snowflake Dynamic Data Masking Policies.

Keys and Tweaks

Both encryption keys and tweaks are required to encrypt and decrypt data using the FF3-1 algorithm. ALTR’s platform enables customers to easily create, manage and deactivate keys and tweaks through an easy-to-use user interface and a REST API.

ALTR’s implementation of the FF3-1 algorithm leverages envelope encryption to afford easy key rotation without the overhead of decrypting and re-encrypting all of the values associated with an encryption key. Envelope encryption involves the use of two layered keys in the encryption process:

  1. Data encryption key (DEK)—the key used to encrypt and decrypt data

  2. Key encryption key (KEK)—the key used to encrypt the DEK

ALTR abstracts the DEK and KEK into a single key object managed by users. When a key object is created by an ALTR user, ALTR generates a DEK and KEK, and encrypts the DEK using the KEK. If a key object is rotated in ALTR, the DEK is decrypted, a new KEK is generated and the DEK is re-encrypted using the new KEK.

ALTR abstracts the DEK and KEK into a single key object managed by users. When a key object is created by an ALTR user, ALTR generates a DEK and KEK, and encrypts the DEK using the KEK. If a key object is rotated in ALTR, the DEK is decrypted, a new KEK is generated and the DEK is re-encrypted using the new KEK.

Encrypting Data

Note

Encryption and decryption always happens within Snowflake. ALTR’s platform is used for the management and access of encryption keys. ALTR does not access your plaintext or ciphertext data during the encryption or decryption process.

You can encrypt data using ALTR-managed key objects with a Snowflake user-defined function (UDF). When encrypting data, specify the ALTR key, tweak and alphabet to be used for encryption. There are two different routes to encrypt data:

  1. Use the UDF directly to encrypt plaintext data in Snowflake.

  2. Access the UDF via Snowflake’s REST API to encrypt data in systems outside of Snowflake, such as AWS S3 (this method is referred to as Shift Left as it enables users to move the encryption of data left of Snowflake). Using Snowflake’s REST API enables data to move through the ETL process in an encrypted state, ensuring that it is protected during the entire data pipeline.

Decrypting Data

Data is decrypted using ALTR’s integration with Snowflake Dynamic Data Masking Policies and External Functions. This process enables fine-grained access control over which Snowflake users are able to decrypt data back to its original plaintext and which users may only access the encrypted ciphertext data.

ALTR users can mark data for decryption using the ALTR’s platform or the REST API. Decryption can be applied to individual Snowflake columns or to many columns at once through the use of Snowflake object tags. To mark a column or a tag for decryption, it must be connected to ALTR in the encrypted state. When doing so, a user specifies the key, tweak and alphabet type that is used for decryption. Once connected, ALTR creates the necessary masking policy, external function and supporting objects in Snowflake to facilitate decryption.

When users query columns protected with data decryption, Snowflake communicates with the ALTR platform to determine if the user should be able to decrypt the data. If the user is not allowed access to the data in ALTR’s policy engine, only the encrypted ciphertext is returned. If the user is allowed access to the data but in a masked state, the ciphertext is decrypted and then masked based on the policy. If the user is afforded plaintext access from ALTR’s policy, the data is decrypted and the plaintext is returned to the user.

Workflow Example

The following diagram illustrates how to apply FPE to a sample of data and how the policies dictate what different users can see. In the table on the far left, you can see sensitive data in an external stage that contains plaintext emails and SSNs.

How_ALTR_s_FPE_works_End-to-End.png

This example shows encryption occurring in the ETL pipeline via the Snowflake REST API. You can see the email and SSN columns both have the expected format but do not contain real values. The final two tables show the different views users see depending on their permissions. A high-privilege user can see the decrypted data, but a low-privilege user only sees the encrypted data. These privileges apply not only directly when querying data in Snowflake but also in any downstream applications.

Key Management Recommendations

Although keys can be reused across many different Snowflake columns to enable ciphertext data inter-compatibility, ALTR recommends minimizing the reuse of encryption key and tweak objects. By keeping a 1:1 or minimal mapping between column/tag and keys/tweaks, you can minimize the risk of an unauthorized user discovering unwanted connections between two different datasets or a compromised key being used to decrypt other unrelated data.

Format-Preserving Encryption Example

Let's say you have a column containing social security numbers such as 111-11-1111 and you want to encrypt it using FPE.

Create key and tweak objects named the following:

  1. Key: mySecretKey

  2. Tweak: myTweak123

Call the encryption UDF using mySecretKey, myTweak123 and the numeric alphabet type to encrypt the social security number column, resulting in ciphertext like 123-45-6789.

You want to conditionally decrypt the data using ALTR. Connect the column in the encrypted state, specifying the key (mySecretKey), tweak (myTweak123) and alphabet type (numeric) used to encrypt the data. You create an ALTR access control policy specifying that your Snowflake user can access that column without masking applied. When you query the data, you are returned the original decrypted social security numbers such as 111-11-1111.

Set Up Format-Preserving Encryption

To set up format-preserving encryption:

  1. Enable FPE for your organization by contacting ALTR Support.

  2. Connect a Snowflake data source to ALTR. If your data source was connected before January 2025, either connect a new data source within the same Snowflake account or disconnect and reconnect your data source before proceeding or contact ALTR Support for assistance. Learn more.

  3. Create a key and a tweak.

  4. Use the ALTR_DSAAS_DB.$<DATABASE_NAME>.ALTR_FPE_ENCRYPT(...) user-defined function, key, tweak and alphabet type to encrypt your data in Snowflake. Learn more.

    Note

    To encrypt data in your source system before it enters the ETL pipeline (i.e., shifting left), contact ALTR Support for assistance.

  5. Connect the encrypted column or tag in ALTR. Specify the key, tweak and alphabet type used for encryption.

  6. Define access control policies on the column or tag.

Enable FPE

Format-preserving encryption is an add-on feature for ALTR. To purchase this add-on feature and to enable it on your organization, contact ALTR Support.

Manage Keys and Tweaks

Before connecting encrypted columns to ALTR, create a key and a tweak. A key is used to encrypt and decrypt data and ensures different encryption results for the same data. A tweak is an additional value that modifies the encryption process by adding variability and security. ALTR’s FPE feature uses envelope encryption, which is the process of encrypting data with a key and then encrypting the key with another key. Learn more.

Create Keys

To strengthen the security of the encryption, we recommend using separate keys and tweaks for different datasets and minimizing key reuse. For example, have separate keys and tweaks for city and state. Using a different key (and tweak) for each type of data ensures that all of your PII isn’t exposed if your key is compromised.

To create a key:

  1. Select Key Management in the Navigation menu.

  2. Click Create Key.

  3. Enter a Key Name. Key names are unique and case sensitive. Ensure the key name

    1. has 256 or fewer characters

    2. does not have leading or trailing spaces

  4. Click Save.

Rotate Keys

Rotate a key as a preventative steps to reduce the threat of the key being compromised. We recommended rotating the key every 3-6 months and after a suspected incident. Keys can be rotated one at a time and every 30 minutes.

If a key has been compromised, rotating it is not enough of a security measure. We recommend that you deactivate the key and create a new one. Learn more.

This process rotates the key encryption key (KEK) in order to enhance security and maintain the integrity of the data encryption process, meaning, it changes the key that is used to encrypt the data encryption key (DEK) via envelop encryption. Learn more.

To rotate a key:

  1. Select Key Management in the Navigation menu.

  2. Click the key you wish to rotate.

  3. Click Rotate Key; the Rotate key? modal displays.

  4. Click Rotate Key to confirm.

Deactivate Keys

Deactivate a key if

  • it is no longer being used.

  • you suspect the key has been compromised.

Deactivating a key prevents users from accessing data; you will be unable to encrypt or decrypt data once the key is deactivated. You will be locked out of your data, so be sure to update the key on the column or the tag before deactivating the key.

Once a key is deactivated, it cannot be reactivated using the ALTR platform; you must contact ALTR Support to reactivate the key.

Note

If an encrypted column or tag is connected to ALTR with a deactivated key, you won’t be able to decrypt the data.

To deactivate a key:

  1. Ensure the key is not in use on an encrypted tag or column. If the key is in use,

    1. Decrypt the data in Snowflake using the existing key and tweak.

    2. Create a new key in ALTR.

    3. Re-encrypt the data in Snowflake using the new key and existing tweak.

    4. Update the relevant column or tag connected in ALTR using the new key and existing tweak.

      Note

      A key can be changed on an existing column. However, you are unable to update a connected tag in ALTR; disconnect the tag from ALTR and reconnected it with the new key.

  2. Select Key Management in the Navigation menu.

  3. Click the key you wish to deactivate.

  4. Click Deactivate Key; the Deactivate key? modal displays.

  5. Click Deactivate Key to confirm. The key still exists, but cannot be used to encrypt a column or a tag.

Reactivate Keys

Reactivate a key if you

  • wish to use it again.

  • no longer suspect the key has been compromised.

To reactivate a key, contact ALTR Support.

Create Tweaks

To strengthen the security of the encryption, we recommend using separate keys and tweaks for different datasets and minimizing key reuse. For example, have separate keys and tweaks for city and state. Using a different key (and tweak) for each type of data ensures that all of your PII isn’t exposed if your key is compromised.

To create a tweak:

  1. Select Key Management in the Navigation menu.

  2. Click the Tweaks tab.

  3. Click Create Tweak.

  4. Enter a Tweak Name. Tweak names are case sensitive. Ensure the tweak name

    1. has 256 or fewer characters

    2. does not have leading or trailing spaces

  5. Click Save.

Deactivate Tweaks

Deactivate the tweak if you suspect it has been compromised. You will be unable to encrypt or decrypt data with this tweak once it is deactivated.

Before deactivating a tweak, ensure it is not in use by a column or a tag for encryption. You won't be able to decrypt a tag or column if it is associated with a deactivated tweak.

To deactivate a tweak:

  1. Ensure the tweak is not in use on an encrypted tag or column. If the tweak is in use,

    1. Decrypt the data in Snowflake using the existing key and tweak.

    2. Create a new tweak in ALTR.

    3. Re-encrypt the data in Snowflake using the new tweak and existing key.

    4. Update the relevant column or tag connected in ALTR using the new tweak and existing key.

    Note

    A tweak can be changed on an existing column. However, you are unable to update a connected tag in ALTR; disconnect the tag from ALTR and reconnect it.

  2. Select Key Management in the Navigation menu.

  3. Click the tweak you wish to deactivate.

  4. Click Deactivate Tweak; the Deactivate tweak? modal displays.

  5. Click Deactivate Tweak to confirm. The tweak still exists, but cannot be used to encrypt a column or a tag.

Reactivate Tweaks

Reactivate a tweak if you

  • wish to use it again.

  • no longer suspect the tweak has been compromised.

To reactivate a tweak, contact ALTR Support.

Encrypt Data in Snowflake

Your data can be encrypted directly in Snowflake via the user-defined function (UDF) or in the ETL pipeline before it enters Snowflake via the REST API, which calls the same UDF. Once the data is encrypted, you can then apply policy. Contact ALTR Supportfor assistance in encrypting columns in Snowflake.

Decryption is maintained in Snowflake. Once you set up policy on the encrypted data, as you query data, ALTR is used to decrypt it depending on your role, the key and tweak. Learn more.

Before encrypting columns,

  1. Verify that all variables are correctly set before running the following script.

  2. Test a small subset of data to confirm your configuration is correct.

To encrypt data in Snowflake using our UDF:

  1. Define the key and tweak names for encryption:

    SET KEY_NAME = '<Insert Key Name Here>'; -- Replace with the name of your encryption key 
SET TWEAK_NAME = '<Insert Tweak Name Here>'; -- Replace with the name of your tweak key

  2. Specify the alphabet for encryption. Options are: alphabetic, numeric or alphanumeric.

    SET ALPHABET = 'alphabetic' | 'numeric' | 'alphanumeric';

  3. Encrypt the specified column in the target table using FPE.

    1. Replace $<TABLE> and $<COLUMN> with the table and column you are encrypting.

    2. Replace $<DATABASE_NAME> with the database name where the table resides.

    UPDATE $<TABLE> SET $<COLUMN> = ALTR_DSAAS_DB.$<DATABASE_NAME>.ALTR_FPE_ENCRYPT( $<COLUMN>, -- Column to encrypt 
$KEY_NAME, -- Name of the encryption key. $TWEAK_NAME, -- Name of the tweak. $ALPHABET -- Character set for FPE );

Connect Encrypted Data to ALTR

Connect Encrypted Columns to ALTR

Once columns have been encrypted in Snowflake, connect the encrypted columns to ALTR in order to apply masking policies to define who is able to view the decrypted data. Encrypted columns are connected to ALTR individually.

Note

If your encrypted column is already connected in ALTR, update the column to mark it as encrypted.

To connect an encrypted column to ALTR:

  1. Ensure the column has been encrypted in Snowflake. Learn more.

  2. Select Data Configuration → Data Management in the Navigation menu.

  3. Click the Columns tab.

  4. Click Connect Column.

  5. Select the Data Source in which the column resides.

  6. Select the Schema and Table or View in which the column resides.

  7. Select the Column.

    Note

    If your schema information doesn't display in the dropdowns, ensure your service user account has privileges to access the data.

  8. Select Yes for Do you have encryption or tokenization applied to this column?

  9. Select Format-preserving encryption for What kind of advanced protection is applied to this column?

  10. Select a Key. This is the main key to encrypt and decrypt the column and is added on the Key Management page. Learn more.

  11. Select a Tweak. This is the additional value that adds variability and security to encrypt and decrypt the column. Tweaks are added on the Key Management page. Learn more.

  12. Select an Alphabet Type that pertains to the data you have encrypted. Options are:

    1. Alphabetic—encrypted data is only letters: a-z and A-Z

    2. Alphanumeric—encrypted data is letters and numbers: a-z, A-Z and 0-9

    3. Numeric—encrypted data is only numbers: 0-9

  13. Click Connect Column.

Connect Encrypted Tags to ALTR

Once tags have been encrypted in Snowflake, connect the encrypted tags to ALTR in order to apply masking policies to define who is able to view the decrypted data. Encrypted tags are connected to ALTR individually.

Note

Connected tags cannot be edited in ALTR. If your encrypted tag is already connected to ALTR and you need to mark it as encrypted:

  1. Disconnect the tag from ALTR.

  2. Reconnect the tag to ALTR. Mark the tag as containing encryption and assign a key and a tweak.

To connect an encrypted tag to ALTR:

  1. Ensure the tag has been encrypted in Snowflake. Learn more.

  2. Select Data Configuration → Data Management in the Navigation menu.

  3. Click the Tags tab.

  4. Click Connect Tag.

  5. Select a ALTR Data Source Connection. This can be any data source associated with your Snowflake account where the Service user has privileges to access the tag’s database.

  6. Select a Tag Database. This is the database where the tag is located and can be different from the data source connection.

  7. Select a Tag Schema. This is the schema within the Tag Database where the tag is located.

  8. Select a Tag Name. This is the name of the tag in Snowflake.

    Note

    If your data doesn't display in the dropdowns, ensure your service user account has privileges to access the data.

  9. Select Yes for Do you have encryption or tokenization applied to this tag?

  10. Select Format-preserving encryption for What kind of advanced protection is applied to this tag?

  11. Select a Key. This is the main key to encrypt and decrypt the tag and is added on the Key Management page. Learn more.

  12. Select a Tweak. This is the additional value that adds variability and security to encrypt and decrypt the tag. Tweaks are added on the Key Management page. Learn more.

  13. Select an Alphabet Type. Options are:

    1. Alphabetic—encrypted data is only letters: a-z and A-Z

    2. Alphanumeric—encrypted data is letters and numbers: a-z, A-Z and 0-9

    3. Numeric—encrypted data is only numbers: 0-9

  14. Click Connect Tag.

Set Up Policy on Encrypted Data

Once FPE is set up for a column or a tag, set up policy to restrict access to the decrypted data. Learn more.

For example, if only ACCOUNTADMIN is allowed access to the data with No Mask, then

  1. when an ACCCOUNTADMIN queries the data, they will see decrypted data (i.e., data in plaintext).

  2. when a user with any role other than ACCOUNTADMIN queries the data, they will see encrypted data (i.e., data in ciphertext).

In this section: