Snowflake Integration

Step-by-step instructions for protecting data in your Snowflake application

Ubiq Encryption in Snowflake

The Ubiq Security Snowflake library provides a convenient interaction with the Ubiq Security Platform API from applications written to interact with the Snowflake Data Cloud. Included is a pre-defined set of functions and classes that will provide a simple interface to encrypt and decrypt data.

The snowflake user-defined functions (UDFs) wrap the Ubiq Python library to enable UDF-based encryption and decryption operations within Snowflake data platform. UDFs are defined and deployed using the Snowpark library, a Snowflake client API for interacting with Snowflake and defining/deploying Snowflake objects.

More information in available in the Ubiq GitLab repository here.


Note: The Snowflake library is currently only supported for Azure- and AWS-hosted Snowflake environments.

If you are running Snowflake on GCP, please contact Ubiq Support.


Previous Versions

If older versions of the Ubiq UDFs exist on snowflake, remove them with the following SQL queries:

-- Know your stage name! uses `ubiq_package_stage`.
LIST @ubiq_package_stage PATTERN='.*';
-- Once you are ready, either use this
REMOVE @ubiq_package_stage PATTERN='.*';
-- Or specify individually with the NAME column, eg:
REMOVE @ubiq_package_stage/80661511034a408d96f01ae595b99e6e3251e59feebc8833442c303ba5a9cade/;

Snowflake will keep older versions around and it is hard to tell which version of the it will ultimately be using. Cleaning up old versions can help eliminate this obscurity.



  • Python 3.8


Install the required packages by running the following command from the top-level of the ubiq-udf project directory:

pip install -r requirements.txt

Ensure Anaconda packages have been enabled in Snowflake per



To deploy the Snowflake Ubiq UDFs, run the following command (replace "" with "^" if running on Windows):

python \
    --account="..." \
    --user="..." \
    --password="..." \
    --warehouse="..." \
    --database="..." \

Arguments are defined as follows:

  • account: snowflake account name (excluding https:// prefix)
  • user: snowflake username
  • password: snowflake password
  • warehouse: name of the Snowflake warehouse
  • database: name of the Snowflake database in which to create Ubiq UDFs
  • schema: name of the schema in which to create Ubiq UDFs

Below is an example invocation of the UDF deployment script using dummy values (replace "" with "^" if running on Windows):

python \
    --account=pozvoni-dt53742 \
    --user=testuser \
    --password=testpassword \
    --warehouse=COMPUTE_WH \
    --database=UBIQ_SANDBOX \

If the user has multiple roles, you will need to adjust the to include a role as appropriate.

Snowflake (SQL)

You will need to run all of the statements listed in both initialize_ubiq_external_functions.sql and ubiq_functions.sql.


For Azure-hosted Snowflake environments

A mutual consent between Azure and your Snowflake tenant needs to be granted. Please contact Ubiq Support to provide environmental information to produce the consent link and to grant Azure consent.

Ubiq will provide you with a tenant id, application id, and broker URL to use during set up.


For AWS-hosted Snowflake environments

A mutual consent between AWS and your Snowflake tenant needs to be granted. Please contact Ubiq Support to provide environmental information.

Ubiq will provide you with a api_aws_role_arn and broker URL to use during setup. You will need to provide the API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID given by Snowflake to be authorized to use the external (broker) functions.

Make sure to replace [Ubiq broker base URL] and IDs with information provided by Ubiq before running.

initialize_ubiq_external_functions.sql - Creates the external (broker) functions needed for Snowflake to communicate with Ubiq. These perform the following functions:

  1. Consumes REST request from Snowflake data platform
  2. Converts them to a format expected by the intended Ubiq API and queries the applicable Ubiq API
  3. Parses the Ubiq API response and returns the data in an array format consumable by Snowflake

ubiq_functions.sql - Defines the functions for interacting with the Ubiq encryption service. Ties the user defined functions to aliases that ensure consistent usage of the platform and handle communication with credentials stored in the current session.


The Client Library needs to be configured with your API Key Credentials which are available in the Ubiq Dashboard when you create a Dataset. Credentials will be used when setting up the environment to use the Ubiq functions.

Production and Production-Like Use


In a production deployment, it is critical to maintain the secrecy of Ubiq API Key Credentials (SECRET_CRYPTO_ACCESS_KEY and SECRET_SIGNING_KEY) and API tokens (ACCESS_KEY_ID).

These items SHOULD be stored in a secrets management server or password vault.


All Ubiq functions take as input the Ubiq secret crypto access key and the encrypted private key, along with other attributes returned from the respective Ubiq API endpoint. Encrypt functions expect plain text while decrypt functions expect encrypted data, which is binary data for standard decryption or cipher text for Structured Data decryption. Additionally, Structured Data Encryption functions expect the Dataset names. Examples of each encrypt/decrypt UDF is provided below. They assume that functions were created in the "ubiq" schema and are executing within the context of the database to which the UDFs were deployed.

Structured Data Encryption Setup

Before running encryption/decryption operations, the database session will need to be initialized. This is done by calling the following procedure:

call ubiq_begin_fpe_session(

Arguments are defined as follows:

dataset_names: The datasets to use Structured Data Encryption with. Datasets should be accessible by the API Key. These should be in a single string, separated by commas. eg 'SSN,TELEPHONE_NUMBER,FULL_NAME'

access_key, secret_signing_key, secret_crypto_access_key: Ubiq API Key Credentials available from the Ubiq Dashboard

Structure Data Encryption

The below command performs FPE encryption by calling the Ubiq API to get Dataset metadata corresponding to the given Dataset name (e.g., 'SSN') and an FPE encryption key.

select ubiq_fpe_encrypt_cache(
from table

Unstructured Data Decryption

The below command performs FPE decryption by calling the Ubiq API to get Dataset metadata corresponding to the given Dataset name (e.g., 'SSN') and an FPE encryption key.

select ubiq_fpe_decrypt_cache(
from table

Ending the Ubiq Session

After encrypting/decrypting, you will need to call this function. This will guarantee the environment has been cleaned up and report usage information.

call ubiq_close_fpe_session(

Usage Example for High Volume-Use Structured Data Encryption

-- sample encrypt/decrypt using format-preserving encryption of an SSN for high volume
-- this assumes that you
--      1) have credentials with access to a structured dataset called "SSN"
--      2) that the structured dataset called "SSN" has an input character set of [0-9]
--         and an output character set of [0-9a-zA-Z] and passthrough character of 
--         at least a dash [-]
-- this example pre-caches configuration and keys that will be used in the subsequent
-- encrypt/decrypt events to maximize performance for large datasets

-- warm up configuration and key cache
call ubiq_begin_fpe_session('SSN', access_key, secret_signing_key, secret_crypto_access_key);

select * from sample_ssns

-- update column in table
update sample_ssns set ssn_encrypted = ubiq_fpe_encrypt_cache(ssn_plaintext, 'SSN');
update sample_ssns set ssn_decrypted = ubiq_fpe_decrypt_cache(ssn_encrypted, 'SSN');

-- query data from table
select id, ubiq_fpe_decrypt_cache(ssn_encrypted, 'SSN') from sample_ssns;

call ubiq_close_fpe_session(access_key, secret_signing_key);