How to encrypt sensitive fields in ETL: step by step

Protecting sensitive fields (e.g.: NIF, email, card) during an ETL pipeline prevents exposure and meets legal requirements. This tutorial shows how to encrypt sensitive fields in ETL with a practical example in Python, so that data is secure before being loaded into a database.

Prerequisites

• Python 3.8+ installed
• cryptography library installed (pip install cryptography)
• A sample CSV file with sensitive fields
• Basic knowledge of Python and ETL (read, transform, write)

Step 1: Why encrypt in ETL

Encrypting in ETL protects data at rest and reduces the risk of leakage if the database or storage is compromised. It differs from masking (where the value loses utility): encryption allows recovering the value with the correct key, so it is useful for scenarios that require controlled reversibility.

Step 2: Choose the encryption method

We will use symmetric encryption with Fernet from the cryptography library — it is simple, secure for many cases, and provides encryption + signing (integrity). Avoid inventing algorithms; follow the standard.

Step 3: Prepare key and utilities

Create a secure key, store it securely (e.g.: Azure Key Vault, Secrets Manager). For this example we generate it locally and show minimal functions to encrypt/decrypt.

from cryptography.fernet import Fernet

# Gerar chave (fazer uma vez e guardar com segurança)
key = Fernet.generate_key()
fernet = Fernet(key)
print(key.decode())  # guarda esta chave com segurança

# Funções utilitárias
def encrypt_value(value: str) -> str:
    if value is None:
        return None
    return fernet.encrypt(value.encode()).decode()

def decrypt_value(token: str) -> str:
    if token is None:
        return None
    return fernet.decrypt(token.encode()).decode()

Step 4: Read the data and identify fields to encrypt

Read the CSV file and define which columns are sensitive. In this example we encrypt 'email' and 'nif'.

import csv

input_file = 'clientes.csv'
output_file = 'clientes_encriptado.csv'
sensitive_cols = ['email', 'nif']

with open(input_file, newline='', encoding='utf-8') as infile:
    reader = csv.DictReader(infile)
    rows = list(reader)

Step 5: Apply encryption during transformation

Iterate the rows and replace sensitive values with the encrypted token. Keep other columns intact. Avoid encrypting empty values.

transformed = []
for row in rows:
    new_row = row.copy()
    for col in sensitive_cols:
        val = row.get(col)
        if val and val.strip() != '':
            new_row[col] = encrypt_value(val)
        else:
            new_row[col] = ''
    transformed.append(new_row)

Step 6: Write the encrypted data

Write the output CSV with the encrypted values. This file can be loaded into a database; only those with the key can decrypt.

with open(output_file, 'w', newline='', encoding='utf-8') as outfile:
    writer = csv.DictWriter(outfile, fieldnames=transformed[0].keys())
    writer.writeheader()
    for r in transformed:
        writer.writerow(r)

print('Ficheiro escrito:', output_file)

Step 7: How to decrypt when necessary

For legitimate operations that need the original value (e.g.: authorized email sending), use the decryption function with access control and logging. Never embed the key in production code.

# Exemplo de desencriptação pontual
sample_encrypted = transformed[0]['email']
original = decrypt_value(sample_encrypted)
print('Original:', original)

Verify the result

Confirm that in the output file the sensitive fields are recognizably encrypted (long strings with / and base64 characters). Also verify that the decryption process recovers exactly the original value and that other fields remain the same.

Conclusion

Encrypting sensitive fields in ETL is a practical measure to reduce risk and comply with data protection policies. Next step: integrate key management with a secure service (e.g.: Azure Key Vault) and apply encryption to incremental flows. Tip: have you already validated where you will store the key and who has access?