Skip to content 
External connectors like Fivetran and Matillion have become the go-to choice of a growing number of organisations when it comes to transferring data into cloud data platforms, such as Snowflake.
These connectors facilitate seamless data ingestion and, if required, transformation. However, when handling sensitive information, they require deep expertise in security to eliminate risks and ensure compliance.
At Infinite Lambda, we work with clients from various highly-regulated industries where security and compliance are top priorities. The solution we are going to show you in this article comes from a project for a client in the insurance industry. The requirement was clear: they needed to be able to upload encrypted files into Snowflake internal stage via external connectors without exposing the contents of those files during the transfer process. To solve the challenges around secure data handling, we leveraged symmetric and asymmetric encryption in Snowflake.
The challenge with external connectors
In the project at hand, external connectors would operate by picking up encrypted files from a company-managed Demilitarised Zone (DMZ) and pushing them into a Snowflake-managed internal stage. While the connectors would provide great flexibility in terms of automation and scalability, the challenge lay in ensuring that sensitive data would remain secure throughout the process.
Specifically, the connectors themselves should have no access to the decrypted contents of the files at any stage of the process. This meant that the encryption process had to be handled outside of Snowflake within the client’s network and decryption processes needed to be handled entirely within Snowflake, ensuring full security and compliance with data protection policies of this specific client.
Snowflake provides end-to-end encryption for data in transit and at rest. However, our use case required additional measures beyond what Snowflake’s native encryption features offered.
Since our client did not utilise cloud provider services like Key Management Services (KMS) or Secrets Managers, we had to implement a custom solution for managing both encryption and decryption. This involved encrypting files outside of Snowflake and securely decrypting them within Snowflake’s internal stage.
Symmetric vs asymmetric encryption
For our client’s use case, we explored two encryption methods: symmetric encryption and asymmetric encryption, each with its own benefits.
Symmetric encryption, specifically Advanced Encryption Standard (AES) 256, uses the same key for both encrypting and decrypting data, which key must be shared between parties. It is fast, efficient, and ideal for handling large datasets or frequent file transfers. This method offers a straightforward solution for securely storing and processing files within Snowflake.
Asymmetric encryption, on the other hand, uses two keys: a public key for encryption and a private key for decryption. The Pretty Good Privacy (PGP) encryption programme is a common example of this method.
Asymmetric encryption is particularly valuable when files must be securely shared between different parties, as the private key remains confidential. Although it adds more complexity due to key management, it provides enhanced security for external file transfers.
Given the context of our client, we set out to create a robust solution for securely transferring and processing encrypted files within Snowflake, ensuring that sensitive data remained protected throughout the entire workflow.
Symmetric encryption and decryption with AES
Initial research — why we selected AES
When deciding on the symmetric encryption method for our client’s use case, we selected AES 256 for symmetric encryption due to its strong balance of security and efficiency. AES is widely regarded as one of the most secure algorithms, and the 256-bit version offers the highest level of encryption.
The simplicity of symmetric encryption comes from using a single key for both encryption and decryption. This made AES 256 a natural fit for our client’s use case, which involved transferring files with higher frequency.
AES 256 could handle large datasets effectively and with high performance, which was crucial for frequent file transfers in our client’s data pipeline.
What further streamlined this process was the pycryptodome package that was available directly in the Snowflake conda channel. This meant we could utilise a reliable, vetted library without having to manage external dependencies or handle complex package installations within Snowflake.
Using pycryptodome would also allow us to avoid the overhead of maintaining custom Python packages or dealing with regular updates, significantly simplifying the decryption process within Snowflake. This built-in support enabled us to focus more on the implementation and security aspects rather than the operational management of the environment.
Implementation of AES decryption
Once we decided on using AES 256 for symmetric encryption, the next step was to implement a decryption process within Snowflake. Our goal was to securely decrypt any encrypted files stored in Snowflake’s internal stage and write the decrypted content back to the stage for further processing.
To invoke this Snowflake Stored Procedure, we used a simple SQL call. Here is an example of how the procedure was called:
Let’s explore the process step by step:
- The encrypted file is retrieved from Snowflake’s internal stage using SnowflakeFile.open;
- The AES encryption key is provided as a parameter, and so is the initialisation vector;
- The Snowflake Stored Procedure reads the encrypted file from the internal stage, and decrypts the content using AES in CBC mode;
- The decrypted content is written to a temporary file, which is then uploaded back to the Snowflake stage as a new file.
Asymmetric encryption and decryption challenges
The need for asymmetric encryption
While symmetric encryption works well for internal file handling, certain use cases require the enhanced security that asymmetric encryption offers.
In our case, it was necessary to use asymmetric encryption, and Pretty Good Privacy (PGP) in particular, for secure file exchanges between parties where the sender could encrypt the data with a public key, and only the intended recipient could decrypt it using a private key.
This method would be crucial for our client in scenarios where data confidentiality must be maintained during transfers, especially when files would be shared externally, ensuring that only authorised parties could decrypt the sensitive information.
Challenges with Snowflake’s conda channel
One major challenge we encountered during the implementation of asymmetric encryption was that the PGPy library, which is essential for handling PGP encryption and decryption, was not available in Snowflake’s native conda channel.
Unlike symmetric encryption, which was simplified by the availability of the pycryptodome package, asymmetric encryption required additional steps to manually package and upload the necessary libraries to Snowflake.
This added complexity increased the maintenance burden, as we had to manage any external dependencies ourselves. The manual process of packaging and uploading Python libraries would also require careful version control, regular updates, and monitoring to ensure that everything functioned smoothly within Snowflake. These limitations introduced additional operational overhead, making the implementation of asymmetric encryption more challenging compared to symmetric encryption.
Installing and uploading custom Python packages
To implement asymmetric encryption with PGP in Snowflake, we had to manually download, package, and upload the necessary libraries due to the absence of the required PGPy library in Snowflake’s conda channel. We began by downloading the compatible version of the PGPy library locally and creating a ZIP archive that would bundle the library and its dependencies.
The next step was to upload the packaged library to a Snowflake internal stage, which served as the storage location for our custom Python package. Using Snowflake’s snow snowpark package upload command, we pushed the PGPy ZIP file to the internal stage, making it accessible for our Python procedure.
Once uploaded, the package could be referenced in our decryption procedure via the IMPORTS clause, allowing Snowflake to use the custom library for PGP decryption without needing external network access.
This process of manually packaging and uploading external Python packages gave us the flexibility to implement asymmetric encryption within Snowflake.
Detailed below is the set of commands we used in order to package and upload the PGPy library to a Snowflake stage.
Asymmetric decryption with PGP
To decrypt files encrypted with PGP within Snowflake, we developed a custom Python procedure using the PGPy library, which we uploaded to a Snowflake internal stage. This procedure allowed us to securely decrypt files using a private key and passphrase stored within the Snowflake environment.
Here is the procedure we created for PGP decryption:
To invoke the procedure, we used the following SQL call:
Let’s have a look at the process step by step:
- The encrypted file is retrieved from Snowflake’s internal stage using SnowflakeFile.open;
- The private key is read from the internal stage and loaded using PGPy;
- The PGPy library unlocks the private key using the passphrase and decrypts the encrypted file, retrieving the plaintext message;
- The decrypted content is written to a temporary file, which is then uploaded back to the Snowflake stage as a new file.
In this call:
The BUILD_SCOPED_FILE_URL function is used in order to retrieve the URLs for the encrypted file and the private key, both of which are stored in the @pgp_internal_stage.
The procedure then decrypts the file using the private key and passphrase, saving the decrypted content as pgp_decrypted_output.txt in the /decrypted/ directory of the same stage.
Key management and handling private keys
Managing private keys securely is a critical aspect of implementing asymmetric encryption within Snowflake, especially when dealing with PGP decryption. We stored the private keys securely in a Snowflake internal stage, treating the keys as sensitive data.
Access to the stage where the private keys were stored was tightly controlled. Only authorised users and roles with the appropriate Snowflake permissions could access the stage, minimising the risk of the private key being compromised. This approach ensured that only the decryption procedure had access to the private key during the decryption process.
To add another layer of security, the private key was protected by a passphrase. This passphrase was supplied securely when invoking the decryption procedure, ensuring that even if the private key was accessed by an unauthorised party, it could not be used without the correct passphrase.
By securely storing private keys in Snowflake, using tight access controls, and ensuring passphrase protection, we maintained a high level of security throughout the decryption process. This approach allowed us to leverage the power of asymmetric encryption within Snowflake while keeping sensitive cryptographic materials protected.
Comparing symmetric and asymmetric encryption in Snowflake
Symmetric encryption in Snowflake is much easier to implement due to the native availability of the pycryptodome package, allowing for immediate development of the AES decryption procedure without the need for external dependencies. This straightforward process requires minimal setup and no external package uploads, making it efficient and reliable for most use cases.
In contrast, implementing asymmetric encryption with PGP in Snowflake was more complex due to the lack of native support for the necessary libraries in Snowflake’s conda channel, requiring us to manually package, upload, and reference them in the decryption procedure. Additionally, managing asymmetric encryption would involve more intricate key management, including securing private keys and handling passphrase protection, making the overall process more complicated compared to the straightforward implementation of symmetric encryption.
Useful links
If you have any questions about this implementation of symmetric and asymmetric encryption in Snowflake, reach out. We are happy to discuss your challenges as well as the use case you are working on.
Visit the Infinite Lambda Tech Blog for more tips and insights on leveraging the Snowflake AI Data Cloud.
