Format-preserving encryption
Protected data that still looks and behaves like real data
FF3-1 tokenisation in Snowflake: joinable, sortable, format-correct, with your own key.
Kevin Keller · kevinkeller.org
Why it matters
Compliance says mask it. Analytics says we need to join on it.
Mask (888) 235-8756 to () -8756 and joins produce false matches wherever the last four digits collide. The larger the dataset, the worse it gets.
Masking destroys the information value of the data.
Three levels
From destructive to format-preserving
| Technique | What you keep |
|---|---|
| Masking | Little: values redacted, joins break |
| Tokenisation | Consistency: same input, same token, joins work |
| Format-preserving encryption | Consistency and format: a token phone number still looks like one |
How it works
FF3-1: same length, same format, deterministic
- A NIST-recommended standard based on AES-256.
- Takes a value, a key and a 56-bit tweak.
- Returns ciphertext of the same length and format.
- Same input, same key, same output: that is what makes joins work.
The idea
What you see depends on your role.
Same column, same query: the masking policy checks role and tags to decide the view.
Four views of one email
Plaintext, token, fake or masked
| Role | What they see | Joinable |
|---|---|---|
| Decrypt | kevin@example.com | Yes |
| Data scientist | xkf7q@hj3mn.com | Yes |
| Data scientist, fake tag | A Faker-generated email | No |
| Masked | masked | No |
Tag-driven
Set a tag, get the behaviour
- No custom masking policy per column.
- Tags pick formatted tokens, Faker values or raw join tokens.
- Tags compose per column and use case without touching policy code.
- Strings, integers, decimals, phone numbers and postal codes are supported.
Partial decryption
Enough signal for trends, not enough to identify anyone
For numbers, only the first digit can be decrypted. Data scientists see whether a value is in the tens or the thousands, enough to spot trends and outliers. A fuzzy tag turns it off.
Use cases
Where it fits
- Hold your own key: the AES-256 key stays under your control
- Superusers: even
ACCOUNTADMINcannot read data without the key - Clean rooms: partners share key and tweak, then join on encrypted IDs
- Off-platform: decrypt in PowerBI, Python scripts or PostgreSQL
Scope
An MVP to embrace and extend
- An educational proof of technology, not production-grade tokenisation at scale.
- For that, consider Snowflake's external tokenisation partners.
- It can complement them for less critical datasets, or be a starting point.
The takeaway
Tokenise instead of mask, and keep both compliance and joins.
Kevin Keller · kevinkeller.org · MVP and educational proof of technology