Match app PII encryption

Overview

The Match app supports end-to-end PII encryption using a hybrid asymmetric scheme. When encryption is enabled by your provider, all PII fields in your input and output tables are encrypted at rest — unreadable by any consumer-side role, including ACCOUNTADMIN.

Encryption mode is set by the provider during onboarding and cannot be changed by the consumer. There are three modes:

Prerequisites

• Your provider has set your encryption mode to PII or Full
• The app has been installed or upgraded to version 1.3 or later (match keys are generated automatically on install/upgrade)
• You have the APP_USER_ROLE application role

Step 1: Check Encryption Status and Retrieve the App's Public Key

SELECT
  *
FROM <application_name>.app_public.get_encryption_status;

If the mode is NONE, encryption is not enabled and you can use the app without any encryption steps. Stop here.

If encryption is enabled, retrieve the app's public key (you will need this to encrypt your input):

SELECT
  match_pub
FROM <application_name>.app_public.get_match_public_key;

This returns the app's RSA-3072 public key in PEM format. Store it securely — you will need it every time you submit a job.

Step 2: Generate your consumer keypair

Generate an RSA keypair using your preferred cryptographic library. The private key never leaves your environment.

Recommended: RSA-3072. It's the best choice for both security and performance on the consumer side — the same key size the Match App itself uses for its own keypair. The other sizes below are supported for compatibility but not recommended.

• RSA-3072 — Recommended. 3072-bit key, 128-bit NIST security strength; matches the app's key size; fastest RSA operation that still clears the recommended security floor.
• RSA-4096 — Supported, not recommended. 4096-bit key. Falls between the RSA-3072 and RSA-7680 NIST security-strength tiers (stronger than 128-bit but short of the next official 192-bit tier). No security benefit over RSA-3072 for this use case, and RSA operations are slower.
• RSA-2048 and below — Supported, not recommended. 2048-bit key, only 112-bit NIST security strength — below current best-practice guidance. Not currently enforced, but should not be chosen for new keypairs.

The key must be in PEM format using PKCS#8 (private) and X.509/SubjectPublicKeyInfo (public) encoding.

For concrete examples of keypair generation using OpenSSL and Python, see Appendix A: Reference implementation below.

Step 3: Register your public key

CALL <application_name>.app_public.register_consumer_public_key('<your_public_key_pem>');

The app uses your public key to encrypt output data. Only you can decrypt it with your private key.

Verify registration:

SELECT
  *
FROM <application_name>.app_public.get_encryption_status;

-- CONSUMER_FINGERPRINT should now be populated

Step 4: Encrypt input data

Before inserting into app_public.input, encrypt the designated columns using the app's public key (from Step 1).

Which columns to encrypt: The encrypted_columns field from get_encryption_status (Step 1) returns a JSON object keyed by table name. The value under the input key is an array of column names that must be encrypted before INSERT. Only encrypt those columns — all others (including request_id, record_id, and country_code which are always plaintext) should be inserted as-is.

Cipher specification

Wire format

Each encrypted field value is a Base64-encoded binary blob with the following layout:

┌─────────────────────────────────────┬──────────────┬─────────────────────────┐
│ RSA-wrapped DEK                     │ GCM nonce    │ Ciphertext + GCM tag    │
│ (key_size_bytes: 384 for RSA-3072,  │ (12 bytes)   │ (variable length)       │
│  512 for RSA-4096)                  │              │                         │
└─────────────────────────────────────┴──────────────┴─────────────────────────┘

Encryption steps:

1. Generate a random 256-bit AES key (the DEK). Reuse one DEK across all records in a batch — this is highly recommended for performance — each record must still have a unique nonce.
2. Wrap the DEK using RSA-OAEP (SHA-256, MGF1-SHA-256) with the app's public key.
3. Encrypt the plaintext field value (UTF-8 encoded) using AES-256-GCM with a unique 12-byte nonce. The cipher produces ciphertext + a 128-bit authentication tag.
4. Concatenate: wrapped_DEK || nonce || ciphertext_with_tag
5. Base64-encode the result. This is the value you INSERT into the encrypted column.

Important notes

• Each encrypted column value is independently encrypted — they do not share nonces.
• NULL values should remain NULL (do not encrypt them).
• Empty strings ('') are not supported in encrypted columns — use NULL for missing values. An empty string will cause the decryption step to fail with Ciphertext too short.
• Non-encrypted columns (request_id, record_id, and country_code) remain plaintext.

For a complete working encryption example, see Appendix A: Reference Implementation below.

Step 5: Insert and run job

Insert and submit exactly as described in the base guide's Populate the INPUT table and Populate the REQUESTS table sections — encryption changes nothing about this step except that the designated columns now contain Base64 ciphertext instead of plaintext.

INSERT INTO <application_name>.app_public.input (
  request_id,
  record_id,
  country_code,
  first_name,
  last_name,
  address_line_1,
  ...
)
VALUES (
  'TESTJOB01',
  '1',
  'US',
  '<base64_encrypted_first_name>',
  '<base64_encrypted_last_name>',
  '<base64_encrypted_address>',
  ...
);

INSERT INTO <application_name>.app_public.requests (
  request_id,
  request_options,
  date_created
)
SELECT
  'TESTJOB01' AS request_id,
  NULL AS request_options,
  CURRENT_TIMESTAMP() AS date_created;

The app decrypts the input internally, processes it, and re-encrypts the output with your consumer public key.

Step 6: Decrypt output

Monitor request_status until your job reaches COMPLETED, exactly as described in the base guide's Monitor the request_status view section — encryption does not change job monitoring. Once complete, query the output tables as usual:

SELECT
  *
FROM <application_name>.app_public.match_output
WHERE request_id = 'TESTJOB01';

SELECT
  *
FROM <application_name>.app_public.hygiene_output
WHERE request_id = 'TESTJOB01';

PII columns in the output are encrypted with your consumer public key. The wire format is the same as described above, but the wrapped DEK size corresponds to your key size (384 bytes for RSA-3072, 512 bytes for RSA-4096).

Decryption steps:

1. Base64-decode the field value.
2. Split the blob at the known offsets:
   • Bytes [0 .. key_size_bytes) → RSA-wrapped DEK
   • Bytes [key_size_bytes .. key_size_bytes + 12) → GCM nonce
   • Bytes [key_size_bytes + 12 .. end) → ciphertext + GCM tag
3. Unwrap the DEK using RSA-OAEP (SHA-256, MGF1-SHA-256) with your private key.
4. Decrypt using AES-256-GCM with the unwrapped DEK and the nonce. The cipher verifies the authentication tag automatically — any tampering will cause decryption to fail.

Decryption performance note

The app encrypts output using a per-batch DEK — the same wrapped DEK appears across all values in a job's output. To optimize decrypt performance on large result sets, cache the unwrapped DEK keyed by the wrapped DEK bytes. This reduces RSA private key operations from one-per-value to one-per-batch:

• Without DEK cache: Each value requires an RSA unwrap (~1-5ms) + AES decrypt. At 400K rows × multiple columns, this can take 20+ minutes.
• With DEK cache: One RSA unwrap per batch, then pure AES-GCM for all subsequent values. Same workload completes in under a minute.

For a complete working decryption example with DEK caching, see Appendix A: Reference Implementation below.

Key rotation

Rotating your consumer key

1. Wait for all in-progress jobs to complete
2. Generate a new keypair
3. Register the new public key via register_consumer_public_key
4. Future output will be encrypted with your new key
5. Previously encrypted output remains readable only with your old private key — retain it as needed

Rotating the App's Match Keys

CALL <application_name>.app_public.rotate_match_keys();

This will: block until all in-progress jobs complete; generate a new Match keypair; update the fingerprint.

After rotation, retrieve the new public key (Step 1) and use it for subsequent input encryption.

Troubleshooting

Security notes

• Never share your private key. The app never needs it and will never ask for it.
• The app's private key is inaccessible to all consumer-side roles, including ACCOUNTADMIN. This is enforced by Snowflake's native app schema isolation.
• Use SECONDARY ROLES NONE in sessions that interact with the app to prevent privilege escalation.
• Key fingerprints (visible in get_encryption_status) can be used to verify key identity without exposing key material.

Appendix A: Reference implementation

These examples use OpenSSL (for key generation) and Python with the cryptography library (for encryption/decryption). Any language or library that produces the same wire format is equally valid.

Keypair generation (OpenSSL)

# Generate RSA-3072 private key in PKCS#8 PEM format
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:3072 -out consumer_private.pem

# Extract the public key in X.509/SubjectPublicKeyInfo PEM format
openssl pkey -in consumer_private.pem -pubout -out consumer_public.pem

The private key file will start with -----BEGIN PRIVATE KEY----- (PKCS#8).
The public key file will start with -----BEGIN PUBLIC KEY----- (SubjectPublicKeyInfo).

Common mistake: Do not use openssl rsa -RSAPublicKey_out — this produces PKCS#1 format (BEGIN RSA PUBLIC KEY) which the app will reject with algid parse error, not a sequence.

To add passphrase protection to the private key:

openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:3072 -aes-256-cbc -out consumer_private.pem

Encrypting input (Python)

This example encrypts field values using the hybrid envelope scheme with a per-batch DEK.

import os
import struct
import base64
from cryptography.hazmat.primitives import hashes, serialization
from cryptography.hazmat.primitives.asymmetric.padding import MGF1, OAEP
from cryptography.hazmat.primitives.ciphers.aead import AESGCM


class BatchEncryptor:
    """Encrypts multiple values using a single DEK (per-batch).

    Each value gets a unique nonce via a 4-byte random prefix + 8-byte counter.
    """

    def __init__(self, match_public_key_pem: str):
        pub_key = serialization.load_pem_public_key(match_public_key_pem.encode())

        # Generate and wrap a single DEK for the batch
        self.dek = os.urandom(32)
        self.aesgcm = AESGCM(self.dek)
        self.wrapped_dek = pub_key.encrypt(
            self.dek,
            OAEP(mgf=MGF1(algorithm=hashes.SHA256()), algorithm=hashes.SHA256(), label=None)
        )
        self.nonce_prefix = os.urandom(4)
        self.counter = 0

    def encrypt(self, plaintext: str) -> str:
        """Encrypt a single field value. Returns Base64-encoded ciphertext."""
        if plaintext is None:
            return None

        # Unique nonce: 4-byte random prefix + 8-byte monotonic counter = 12 bytes
        nonce = self.nonce_prefix + struct.pack('>Q', self.counter)
        self.counter += 1

        ct_and_tag = self.aesgcm.encrypt(nonce, plaintext.encode('utf-8'), None)
        blob = self.wrapped_dek + nonce + ct_and_tag
        return base64.b64encode(blob).decode('ascii')


# Usage:
# match_pub_pem = <output from GET_MATCH_PUBLIC_KEY>
# encryptor = BatchEncryptor(match_pub_pem)
# encrypted_first_name = encryptor.encrypt("John")
# encrypted_last_name = encryptor.encrypt("Smith")
# encrypted_address = encryptor.encrypt("123 Main St")
# ... use the same encryptor instance for all records in the batch

Decrypting output (Python)

This example decrypts field values with DEK caching for batch performance.

import base64
from cryptography.hazmat.primitives import hashes, serialization
from cryptography.hazmat.primitives.asymmetric.padding import MGF1, OAEP
from cryptography.hazmat.primitives.ciphers.aead import AESGCM


class BatchDecryptor:
    """Decrypts values encrypted by the Match App, with DEK caching.

    The app uses a per-batch DEK, so the same wrapped DEK appears across
    all values in a job's output. Caching avoids redundant RSA unwraps.
    """

    def __init__(self, consumer_private_key_pem: str):
        self.priv_key = serialization.load_pem_private_key(
            consumer_private_key_pem.encode(), password=None
        )
        self.key_size_bytes = self.priv_key.key_size // 8  # 384 for RSA-3072
        self.dek_cache = {}

    def decrypt(self, ciphertext_b64: str) -> str:
        """Decrypt a single field value. Returns plaintext string."""
        if ciphertext_b64 is None:
            return None

        blob = base64.b64decode(ciphertext_b64)

        wrapped_dek = blob[:self.key_size_bytes]
        nonce = blob[self.key_size_bytes:self.key_size_bytes + 12]
        ct_and_tag = blob[self.key_size_bytes + 12:]

        # Cache the unwrapped DEK — one RSA operation per batch
        cache_key = wrapped_dek
        if cache_key not in self.dek_cache:
            self.dek_cache[cache_key] = self.priv_key.decrypt(
                wrapped_dek,
                OAEP(mgf=MGF1(algorithm=hashes.SHA256()), algorithm=hashes.SHA256(), label=None)
            )

        dek = self.dek_cache[cache_key]
        aesgcm = AESGCM(dek)
        plaintext = aesgcm.decrypt(nonce, ct_and_tag, None)
        return plaintext.decode('utf-8')


# Usage:
# with open('consumer_private.pem', 'r') as f:
#     priv_pem = f.read()
# decryptor = BatchDecryptor(priv_pem)
# first_name = decryptor.decrypt(row['FIRST_NAME'])
# last_name = decryptor.decrypt(row['LAST_NAME'])
# ... use the same decryptor instance for all rows in the result set

Verifying your setup

Quick end-to-end test to confirm your keypair and encryption work correctly before submitting a real job:

# 1. Encrypt a test value with the Match public key
encryptor = BatchEncryptor(match_pub_pem)
encrypted = encryptor.encrypt("test value")
print(f"Encrypted: {encrypted[:40]}...")

# 2. Verify the blob structure
blob = base64.b64decode(encrypted)
print(f"Blob length: {len(blob)} bytes")
print(f"  Wrapped DEK: {len(blob[:384])} bytes (expect 384 for RSA-3072)")
print(f"  Nonce: {len(blob[384:396])} bytes (expect 12)")
print(f"  Ciphertext+tag: {len(blob[396:])} bytes")

# 3. Decrypt with your consumer private key (to test the round-trip locally,
#    you'd need the Match private key — which you don't have. This step is
#    only possible for output decryption after the app re-encrypts with YOUR key.)