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

  1. Read the .gpg file from the stage
  2. Read the private key from the stage
  3. Unlock the key with the passphrase
  4. Decrypt the PGP message with pgpy
  5. Decompress the gzip layer
  6. Return the plaintext, or write it back to the stage

Two approaches

Pick by file size and purpose

UDFStored procedure
ReturnsContent as a stringFile path on stage
Best forSmall files, ad-hoc checksLarge files, production
Next stepParse in SQLCOPY INTO from stage
Runs asUDF contextEXECUTE 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.

Read the full article

Kevin Keller · kevinkeller.org