See our topic on encryption in the data exports for background information on how individual columns in CSV files can be encrypted.

We recommend importing the data in its encrypted form, and only decrypting it when necessary. The encryption key you will have received from Beat separately.

In this example, we use the imported users snapshot as the basis.

SELECT 
  user_id,
  TO_VARCHAR(
    DECRYPT_RAW(
      BASE64_DECODE_BINARY(encrypted_mail), -- decode Base64 to binary ciphertext
      BASE64_DECODE_BINARY('lc3YvP7CMjZbhfFNWAoEdojLl2pegzpZLv/n/vLaHzw='),  -- 256-bit key
      BASE64_DECODE_BINARY(iv), -- 128-bit IV
      NULL, 
      'AES-CBC/pad:PKCS' -- specify AES-CBC with PKCS padding
    ), 
    'UTF-8' 
  ) AS email
FROM users;

You should add a function to more easily decrypt any encrypted field this. Here is an example:

CREATE OR REPLACE SECURE FUNCTION decrypt_value(
    ciphertext BINARY,
    iv BINARY(16)
)
    RETURNS STRING
    LANGUAGE SQL
AS
$$
    TO_VARCHAR(
            DECRYPT_RAW(
                    ciphertext,
                    -- provide your encryption key here:
                    BASE64_DECODE_BINARY('lc3YvP7CMjZbhfFNWAoEdojLl2pegzpZLv/n/vLaHzw='),
                    iv,
                    NULL,
                    'AES-CBC/pad:PKCS'
            ),
            'UTF-8'
    )
$$;

You should then be able to decrypt fields more easily:

SELECT 
  user_id,
	decrypt_value(encrypted_mail, iv) AS email,
	decrypt_value(encrypted_first_name, iv) AS first_name
FROM users;

<aside> <img src="/icons/warning_red.svg" alt="/icons/warning_red.svg" width="40px" />

Important note on the SECURE function above: Secure functions in Snowflake can be protected, so that only specific users are able to view their contents. In the function declared above, the encryption key is included. It should therefore be shielded from access by other parties. Please read this documentation in Snowflake and apply any protective measures you deem appropriate.

Beat considers it your responsibility to properly secure the encryption key.

</aside>

<aside> <img src="/icons/drafts_blue.svg" alt="/icons/drafts_blue.svg" width="40px" />

Note that Beat strongly recommends that you keep data encrypted until it is necessary to use, and to not create views (especially materialized views) that use the decrypt_value function. Instead the decrypt_value function itself should be limited to anyone with a specific need to access this data. This approach protects user data for as long as possible.

</aside>