PGP in Snowflake
Decrypt partner files where the data lands
PGP decryption inside Snowflake: no middleware, no external compute, no key in transit.
Kevin Keller · kevinkeller.org
Why it matters
The traditional MFT pipeline is a server to babysit
- Partners encrypt files with your public key
- A middleware server receives, decrypts, decompresses and stages them
- The private key lives on that server
- It needs maintaining: network rules, patching, IAM
The idea
Move the decryption into Snowflake's Python sandbox
Encrypted files arrive on a stage, a Python function decrypts them, and COPY INTO loads the result.
How it works
Six steps inside one function
- Read the
.gpgfile from the stage - Read the private key from the stage
- Unlock the key with the passphrase
- Decrypt the PGP message with pgpy
- Decompress the gzip layer
- Return the plaintext, or write it back to the stage
Two approaches
Pick by file size and purpose
| UDF | Stored procedure | |
|---|---|---|
| Returns | Content as a string | File path on stage |
| Best for | Small files, ad-hoc checks | Large files, production |
| Next step | Parse in SQL | COPY INTO from stage |
| Runs as | UDF context | EXECUTE AS CALLER |
The small fix that made it work
pgpy needed two surgical patches
- Stock pgpy reads files with Python's
open() - Stage files must be read through
SnowflakeFile - Two methods patched; file size found by seeking, not
os.path.getsize() - The modified bundle is uploaded to the stage and imported by the function
Scaling up
Three ways to go from one file to many
- Batch the procedure: the loop pointers are already in the code
- Wrap it: a thin procedure loops over files on the stage
- Direct to table: turn the UDF into a UDTF inside
COPY INTO - Schedule it: a Snowflake Task makes it a self-contained MFT endpoint
Security
The key never leaves the boundary
- Private key on an encrypted internal stage, readable only by a service role
- Passphrase in a Snowflake secret in production, not a literal
- No outbound network from the UDF without an External Access Integration
- Every decryption logged in
QUERY_HISTORY
One gotcha
Stage encryption decides whether you can download
With SNOWFLAKE_FULL, internal stage files can only be read by Snowflake.
Use SNOWFLAKE_SSE if you need to GET decrypted files for local testing. For production inside Snowflake, either works.
Takeaway
Files arrive encrypted, are decrypted on schedule and loaded, without leaving the platform.
Kevin Keller · kevinkeller.org