Homomorphic encryption on Snowflake

Snowflake does the arithmetic. Only the data owner can read the answer.

Encrypted payroll analytics in ordinary Python UDFs on a standard warehouse.

Kevin Keller · kevinkeller.org

Why it matters

Some columns never need to be seen, only computed on

  • HR wants payroll by department, average pay by region, the cost of a 3% raise.
  • Nobody running those queries needs to read an individual salary.
  • Homomorphic encryption lets the platform do the work and still not read the data.

How it works

Encrypt locally, compute in the cloud, decrypt locally

  1. The data owner encrypts values on their own machine.
  2. Only ciphertexts and a public context go to Snowflake.
  3. Snowflake adds and multiplies the ciphertexts directly.
  4. The encrypted result comes back and only the owner decrypts it.

What Snowflake holds

~100 bytes

of public context. No secret key, no public key, no evaluation key.

Snowflake can add and multiply ciphertexts, but it can neither decrypt nor even encrypt.

Two schemes

Exact integers or approximate decimals

SchemeUsed forArithmetic
BFVSalary, bonus, deductionsExact, within about ±8.6 billion
CKKSRates, hours, scores, flagsApproximate, up to two encrypted multiplications

Further than sums

Statistics, scoring and running totals all work on ciphertext

  • Aggregates: SUM, AVG, GROUP BY, ROLLUP
  • Pay maths: net pay, raises, employer cost
  • Statistics: variance, correlation, regression
  • Encrypted conditions: COUNTIF and SUMIF
  • ML scoring: linear models, a sigmoid approximation
  • Time series: year-to-date, moving sums

Verified

185

results decrypted and checked against plaintext. All pass.

Correlation matched plaintext to about 5·10⁻⁸. BFV payroll totals had zero error.

Packed mode

A whole column in one ciphertext

LayoutCiphertextsSize
Row mode, 100 salaries1008.9 MB
Packed, 100 salaries188 KB

Packed SUM: 2.2 s against 3.0 s in row mode, same exact answer.

The cost, honestly

Slow and bulky, so use it on a few columns

  • About 86 KB per value (BFV), about 300 KB (CKKS).
  • 100 rows × 8 encrypted columns: about 182 MB.
  • Upload about 2 minutes. Typical query 2 to 13 seconds.
  • For a few high-value numeric columns, not whole tables.

The limits

No comparisons, no encrypted division, limited depth

  • No WHERE salary > x, ORDER BY, MIN, MAX or joins on encrypted keys.
  • Keep filter and grouping columns in plaintext or tokenised.
  • Divide after decryption.
  • Integrity is not protected: a server could return a wrong ciphertext.

The idea

Use it where the alternative is not putting the data in the cloud at all.

A handful of high-value columns, computed on by the platform, readable only by the owner.

The takeaway

Homomorphic encryption is not just research: it is a set of Python UDFs.

Read the full article

Kevin Keller · kevinkeller.org · Personal project, a technical demonstration, not an official Snowflake feature