Column-level encryption
Encryption is easy. Key management is the hard part.
Four ways to manage AES keys for sensitive Snowflake columns, from demo to defence in depth.
Kevin Keller · kevinkeller.org
Why it matters
Some columns need keys the platform doesn't control
- PCI DSS, HIPAA, GDPR or sovereignty rules can demand it.
- SSNs, card numbers and health records are typical candidates.
ENCRYPT_RAWandDECRYPT_RAWalready exist.
Where does the key live, who can access it, and how does it reach the query?
The four approaches
Same functions, different key delivery
| # | Approach | Key location | Security |
|---|---|---|---|
| 1 | Session variable | SET aes_key | Low |
| 2 | UDF key vault | Secure UDF | Medium |
| 3 | External function + KMS | AWS KMS / Azure Key Vault | High |
| 4 | Key wrapping (HYOK) | KMS + session derivation | Very high |
Portable format
Ciphertext that Postgres can read too
- Random IV per row: the same plaintext encrypts differently every time.
- The IV sits in the first 16 bytes of the ciphertext.
- Compatible with PostgreSQL
pgcryptoin both directions. - Encrypted data flows between Snowflake and Postgres without re-encryption.
Approaches 1 and 2
Good for learning, not for production
- Session variable: zero infrastructure
- But: the key appears in
QUERY_HISTORY - UDF key vault: key hidden by
SECURE, plus a passphrase - But: key hardcoded, rotation needs a redeploy
Approach 3
Fetch the key from KMS at runtime
- An immutable external function calls Lambda or an Azure Function.
- The key never appears in Snowflake SQL or query history.
CONTEXT_HEADERSsend user and account for per-user control and audit.- Variant 3B: crypto runs in the function, the key never leaves the cloud provider.
Approach 4
A stolen KMS key alone cannot decrypt the data
- The external function retrieves the base key from KMS.
- It is combined with session context through SHA-256.
- The derived key encrypts and decrypts.
- Each session gets a unique key: per session for speed, per query for maximum security.
Masking integration
Decryption becomes invisible to the right users
A masking policy decrypts on read: DATA_ANALYST sees SSNs, everyone else sees ENCRYPTED . Tag-based masking applies it to every tagged column at once. No application changes.
Performance
One key fetch per query, not per row
| Approach | Per-query latency |
|---|---|
| Session variable / UDF | ~2 s |
| External function / key wrapping | ~3–7 s |
50 million rows, 7 encrypted columns, L warehouse. 7 columns take ~2.4x one column. Lambda is under 3% of query time.
BI tools
Sessionless tools need per-query keys
| BI pattern | Recommended |
|---|---|
| Session-aware (Snowsight, dbt) | Any approach |
| Sessionless (Sigma, Tableau) | External function, per query |
| Embedded analytics | UDF key vault or external function |
The recommendation
Start production with Approach 3, add Approach 4 for defence in depth.
Approaches 1 and 2 are for understanding the mechanics.
The takeaway
Control the key and you control the column.
Kevin Keller · kevinkeller.org · Educational samples that demonstrate the patterns