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_RAW and DECRYPT_RAW already 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

#ApproachKey locationSecurity
1Session variableSET aes_keyLow
2UDF key vaultSecure UDFMedium
3External function + KMSAWS KMS / Azure Key VaultHigh
4Key wrapping (HYOK)KMS + session derivationVery 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 pgcrypto in 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_HEADERS send 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

  1. The external function retrieves the base key from KMS.
  2. It is combined with session context through SHA-256.
  3. The derived key encrypts and decrypts.
  4. 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

ApproachPer-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 patternRecommended
Session-aware (Snowsight, dbt)Any approach
Sessionless (Sigma, Tableau)External function, per query
Embedded analyticsUDF 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.

Read the full article

Kevin Keller · kevinkeller.org · Educational samples that demonstrate the patterns