↓ Skip to main content
  1. Articles/

Computing on Data Snowflake Can't Read: Homomorphic Encryption in Plain Python UDFs

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

Code: github.com/sfc-gh-kkeller/snowflake-homomorphic-encryption (Apache 2.0). Deployment and loading scripts, 13 homomorphic SQL functions, three executed notebooks, 21 offline tests and a patched Pyfhel build.

Personal project. This is a technical demonstration, not an official Snowflake feature. It hasn’t been security-reviewed for production use and isn’t production-grade cryptographic engineering. See the repo’s SECURITY.md for the threat model.


Why compute on ciphertext?
#

In The Data, the Key, or the Switch I walked through customer-held controls for data sovereignty. Most of them control when and for whom plaintext exists inside Snowflake. Only a few keep plaintext out of the platform entirely, and homomorphic encryption is the one that still lets Snowflake do useful work on the data.

The use case is narrow but real. Some columns never need to be seen in the cloud, only computed on. Salaries are the classic example: HR wants payroll totals by department, average pay by region, the cost of a 3% raise. Nobody running those queries needs to read an individual salary, and the data owner may not want the platform able to read it under any circumstances.

Homomorphic encryption makes that possible. You encrypt values on your own machine, upload only ciphertexts, and Snowflake adds and multiplies the ciphertexts directly. The result comes back still encrypted, and only you can decrypt it.

This post is the deep dive behind that one section.


How it works
#

 LAPTOP (data owner)                               SNOWFLAKE (standard warehouse)
┌──────────────────────────────┐   ciphertexts    ┌────────────────────────────────────────────┐
│ keys/  secret keys (local)   │ ───────────────► │ EMPLOYEES_ENC  plaintext dims + BINARY cts  │
│ encrypt salary, bonus, ...   │  public params   │ HE_KEYS        public parameters only       │
│                              │ ───────────────► │ HE_SUM · HE_ADD · HE_SUB · HE_MUL_PLAIN ... │
│ decrypt results + verify     │ ◄─────────────── │ GROUP BY / WHERE on plaintext columns       │
└──────────────────────────────┘ encrypted result └────────────────────────────────────────────┘

Three design choices make this practical:

  • Nothing special on the Snowflake side. The homomorphic operations are ordinary Python UDFs, UDAFs and UDTFs on a standard warehouse, using the pyfhel package from Snowflake’s Anaconda channel. Pyfhel wraps Microsoft SEAL. There’s no Snowpark Container Services, no external function and no extra infrastructure.
  • Snowflake holds almost nothing. For row-level keys, the only thing uploaded besides ciphertexts is the public context: about 100 bytes describing the ring size, moduli and scale. There’s no secret key, no public key and no evaluation key. Snowflake can add and multiply ciphertexts, but it can neither decrypt nor even encrypt.
  • Every result is checked. The notebooks decrypt every answer and compare it with the plaintext ground truth, which never leaves the laptop. All 185 results pass.

Two schemes cover different needs:

SchemeUsed forArithmetic
BFVSalary, bonus, deductionsExact integers, as long as results stay within about ±8.6 billion
CKKSHourly rate, hours, performance score, flagsApproximate real numbers, with up to two encrypted multiplications

What Snowflake actually stores
#

Here’s what an encrypted salary looks like in the table: about 88 KB of ciphertext per value, next to plaintext dimensions you can still filter and group on.

SELECT emp_id, department, region,
       LENGTH(salary_ct)                       AS salary_ct_bytes,
       HEX_ENCODE(SUBSTR(salary_ct, 4097, 12)) AS salary_ct_sample
FROM EMPLOYEES_ENC ORDER BY emp_id LIMIT 3;
EMP_IDDEPARTMENTREGIONSALARY_CT_BYTESSALARY_CT_SAMPLE
1MarketingAMER88,52435C7E405A5CD10E655376AE0
2SalesAPJ88,480B9DDB7B495499FD123C6C3A4
3SupportEMEA88,579ED3FA9A8917DC5C696A64F6C

And the key table holds only public parameters: 96 bytes for the BFV context and 122 bytes for CKKS. Rotation keys appear only for the packed mode described below.


Encrypted SQL that reads like SQL
#

Every function takes the public context as its first argument, so one set of functions serves every key and both schemes. Payroll by department, with a rollup, looks like ordinary SQL:

SELECT COALESCE(e.department, '<ALL>') AS department,
       COUNT(*)                       AS headcount,
       HE_SUM(k.context, e.salary_ct) AS payroll        -- encrypted result
FROM EMPLOYEES_ENC e
JOIN HE_KEYS k ON k.key_id = e.bfv_key_id
GROUP BY ROLLUP (e.department)
ORDER BY department;

Snowflake sums the ciphertexts and returns one encrypted total per department. The client decrypts and checks them. With BFV the error is exactly zero:

Payroll per department with ROLLUP: Snowflake sums the encrypted salaries, the data owner decrypts the totals and checks each one against plaintext, with zero error on every row. The query took 4.9 seconds.

Encrypted values combine with each other too. Net pay is salary plus bonus minus deductions, all three encrypted:

SELECT e.department,
       HE_SUM(k.context,
              HE_SUB(k.context,
                     HE_ADD(k.context, e.salary_ct, e.bonus_ct),
                     e.deductions_ct)) AS net
FROM EMPLOYEES_ENC e JOIN HE_KEYS k ON k.key_id = e.bfv_key_id
GROUP BY e.department;

And they combine with plaintext from other tables. Currency conversion multiplies an encrypted hourly rate by a plaintext FX rate from a join:

SELECT e.region,
       HE_SUM(k.context, HE_MUL_PLAIN(k.context, e.hourly_rate_ct, f.usd_to_local)) AS local_sum
FROM EMPLOYEES_ENC e
JOIN HE_KEYS  k ON k.key_id = e.ckks_key_id
JOIN FX_RATES f ON f.region = e.region
GROUP BY e.region;

WHERE clauses work as usual on the plaintext columns: total salary for EMEA hires since 2020 returned 2,376,556 across 16 people, exactly matching plaintext.


Further than sums
#

The surprising part is how much fits inside those few operations. Every row in this table is a working, verified query in the notebooks:

CategoryExampleScheme
AggregatesSUM, COUNT, AVG, GROUP BY, ROLLUP, share of totalBFV
Encrypted ± encryptedGross pay, net payBFV
Constant arithmetic3% raise plus $1,000, employer cost 1.08·salary + bonus + 1,200BFV
Weighted averageFTE-weighted salary with plaintext weightsBFV
DecimalsAverage rate, 5% uplift, currency conversion via an FX joinCKKS
Encrypted × encryptedLabor cost = rate × hours, hours-weighted average rateCKKS
StatisticsMean, variance, standard deviation, covariance, Pearson correlation, regression slope and interceptCKKS
Encrypted conditionsCOUNTIF and SUMIF where the 0/1 condition is itself encryptedCKKS
ML scoringA linear model on encrypted features; a degree-3 sigmoid approximation for attrition riskCKKS
Time seriesYear-to-date running totals, month-over-month deltas, 3-month moving sumsBFV

A few results from the executed notebooks:

  • Correlation between hourly rate and performance score: 0.895640, matching plaintext to about 5·10⁻⁸.
  • COUNTIF of remote workers per department, where the remote flag is encrypted: 9, 8, 8, 6 and 3, each within 2·10⁻⁷ of the exact count.
  • Linear scoring of all 100 employees on encrypted features: maximum error 7.5·10⁻⁸, and the top 5 by decrypted score is identical to the top 5 by plaintext score.
  • Attrition risk with HE_POLY (a degree-3 approximation of the logistic function): per-department averages within 4·10⁻⁶ of the same polynomial on plaintext.
  • Year-to-date revenue with a table function and OVER (PARTITION BY region ORDER BY month): exact, every month.

Statistics work because everything they need is a sum. Snowflake computes encrypted Σx, Σx², Σy, Σy² and Σxy with HE_MUL and HE_SQUARE; the client decrypts those five numbers and does the final division locally.


Inside a homomorphic aggregate
#

HE_SUM is a Python UDAF. Each warehouse worker keeps one running ciphertext, ships it as bytes, and the partial sums are merged. Homomorphic addition is associative, so any partitioning gives the same answer, which the offline tests check with random partitions:

class HeSum:
    """Homomorphic SUM. State = (context bytes, serialized partial sum)."""

    def __init__(self):
        self._ctx = None
        self._acc = None                     # running ciphertext

    @property
    def aggregate_state(self):               # what Snowflake ships between workers
        return (self._ctx, None if self._acc is None else self._acc.to_bytes())

    def _add(self, ctx, ct_bytes):
        ctx = bytes(ctx)
        if self._ctx is None:
            self._ctx = ctx
        elif ctx != self._ctx:
            raise ValueError("HE_SUM: ciphertexts under different keys/contexts cannot be added")
        c = _ct(_he(self._ctx), ct_bytes)
        self._acc = c if self._acc is None else self._acc + c   # ciphertext + ciphertext

    def accumulate(self, ctx, ct):
        if ctx is not None and ct is not None:
            self._add(ctx, ct)

    def merge(self, other_state):            # combine partial sums from other workers
        other_ctx, other_acc = other_state
        if other_acc is not None:
            self._add(other_ctx, other_acc)

    def finish(self):
        return None if self._acc is None else _out(self._acc)   # still encrypted

Deployment embeds a single handler file verbatim into every function, and the offline tests import the same file. So the code that runs in Snowflake is exactly the code that was tested:

CREATE OR REPLACE AGGREGATE FUNCTION HE_SUM(ctx BINARY, ct BINARY)
RETURNS BINARY
LANGUAGE PYTHON RUNTIME_VERSION = '3.11'
PACKAGES = ('pyfhel==3.4.2', 'numpy==1.26.4')
HANDLER = 'HeSum'
AS $$ ... udf_handlers.py ... $$;

Two engineering details deserve a mention:

  • No evaluation keys at all in row mode. Normally, multiplying two ciphertexts requires a relinearization key on the server. The demo skips that: products are left un-relinearized (three or four polynomials instead of two), which SEAL can still add, rescale and decrypt. That’s why Snowflake needs nothing but the 100-byte public context.
  • Exact CKKS scale management. Pyfhel loses a ciphertext’s modulus level when it’s deserialized. The handlers recover it from the serialized size, encode plaintext constants at the ciphertext’s exact scale, and align polynomial terms to identical scales instead of rounding.

Packed mode: a whole column in one ciphertext
#

Row mode is simple but heavy: 100 salaries take 8.9 MB. Packed (SIMD) mode puts up to 4,096 values into one ciphertext, and operates on all of them at once.

LayoutCiphertextsSize
Row mode: salary column1008.9 MB
Packed: salary column188 KB
Public rotation keys (BFV / CKKS)11.9 MB / 9.2 MB

A raise, an addition, or rate × hours then happens elementwise on every row in a single operation. Sums and dot products use rotations, which shift values between slots and need public rotation keys uploaded to Snowflake. Those keys still don’t allow decryption. A packed SUM over 100 salaries took 2.2 seconds, against 3.0 seconds for row mode, and returned the exact same 13,996,222.


The cost, honestly
#

Homomorphic encryption is not free, and the numbers make that clear. Measured with 100 employees on an X-Small warehouse:

Ciphertext size~86 KB per value (BFV), ~300 KB (CKKS)
100 rows × 8 encrypted columns~182 MB of ciphertext
Local encryption~3–10 s
Upload~2 min
Typical encrypted query2–13 s

So this is for a few high-value numeric columns, not whole tables. Use it where the alternative is not putting the data in the cloud at all.

The limits matter just as much:

  • No comparisons. WHERE salary > x, ORDER BY, MIN, MAX, DISTINCT and joins on encrypted keys aren’t possible with these schemes. Keep filter and grouping columns in plaintext (or tokenized).
  • No division by an encrypted value. Divide after decryption, as the statistics above do.
  • Limited depth. BFV here does additions and plaintext multiplications only; CKKS allows two encrypted multiplications.
  • BFV wraps around. Results outside about ±8.6 billion wrap silently. Rescale units (dollars instead of cents) or use CKKS.
  • CKKS is approximate, around 10⁻⁸ relative error at low depth and 10⁻⁵ at depth two.

What it does and doesn’t protect
#

ProtectedConfidentiality of the encrypted values from the platform. Secret keys never leave the client.
Not protectedIntegrity. A buggy or malicious server could return a wrong but well-formed ciphertext.
Visible to the serverPlaintext columns, row counts, query shapes, ciphertext sizes and which operations were applied.
Key lossLosing the local keys makes the encrypted data unrecoverable. Rotating keys means re-encrypting.
CKKS caveatRaw CKKS decryptions can leak information about the secret key if shared with untrusted parties. Don’t publish them in an adversarial setting.

The parameters target 128-bit security: BFV with ring size 4,096 and CKKS with ring size 8,192, within the homomorphic encryption standard’s bounds.


One gotcha worth knowing: SEAL versions
#

If you try this with Pyfhel from PyPI, it fails inside Snowflake with RuntimeError: incompatible version. PyPI’s Pyfhel 3.4.2 source bundles Microsoft SEAL 4.0, while Snowflake’s Anaconda build of the same Pyfhel version links SEAL 4.1. SEAL writes its version into every serialized object and refuses to load objects from another minor version.

The repo vendors Pyfhel 3.4.2 with SEAL 4.1.2 swapped in, so ciphertexts created locally load in Snowflake. It also disables SEAL’s bundled zlib, which doesn’t compile on recent macOS SDKs; Zstandard compression keeps working, so serialized objects stay compatible. Both changes are documented, and a script reproduces the vendored tree from upstream with checksum verification. Pixi provides the whole compiler toolchain, so there’s nothing to install system-wide.


Try it
#

You need Pixi, network access to GitHub for the first build, and a Snowflake role that can create a database and use a warehouse.

git clone https://github.com/sfc-gh-kkeller/snowflake-homomorphic-encryption.git
cd snowflake-homomorphic-encryption

pixi install                          # builds Pyfhel 3.4.2 + SEAL 4.1.2 (a few minutes the first time)
pixi run test                         # 21 offline tests of every UDF handler

cp config.example.toml config.toml    # set connection, role, warehouse
pixi run deploy                       # database, tables, 13 homomorphic functions
pixi run load                         # generate keys, encrypt locally, upload ciphertexts
pixi run demo                         # execute the three notebooks

pixi run lab opens the notebooks in JupyterLab, and pixi run clean-snowflake drops the demo database after a typed confirmation.


The takeaway
#

Homomorphic encryption has a reputation for being a research topic. On Snowflake it’s a set of Python UDFs on a standard warehouse, and it goes well beyond sums: weighted averages, currency conversion, correlation, regression, conditional counts, model scoring and running totals all work on ciphertext. It’s slow and bulky, so it belongs on a handful of high-value columns. For those columns, it means the platform does the work and still can’t read the data.


References
#

Behaviour reflects Snowflake as of November 2026. Views are my own.

Kevin Keller
Author
Kevin Keller
Personal blog about AI, Observability & Data Sovereignty. Snowflake-related articles explore the art of the possible and are not official Snowflake solutions or endorsed by Snowflake unless explicitly stated. Opinions are my own. Content is meant as educational inspiration, not production guidance.
Share this article

Related

The Data, the Key, or the Switch: Customer-Held Controls for Data Sovereignty in Snowflake

Runnable patterns that keep the most sensitive data under the data owner’s control while Snowflake stays the analytics platform. Each one puts a different thing in the customer’s hands: the data, the key, or the switch.