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

TechniqueWhat you keep
MaskingLittle: values redacted, joins break
TokenisationConsistency: same input, same token, joins work
Format-preserving encryptionConsistency 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

RoleWhat they seeJoinable
Decryptkevin@example.comYes
Data scientistxkf7q@hj3mn.comYes
Data scientist, fake tagA Faker-generated emailNo
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 ACCOUNTADMIN cannot 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.

Read the full article

Kevin Keller · kevinkeller.org · MVP and educational proof of technology