How to do data masking in ETL: step by step

This tutorial shows how to apply data masking in ETL to protect PII (Personally Identifiable Information) during processing. You will learn a simple and secure method to replace or obfuscate sensitive fields before loading into consumer systems.

Prerequisites

• Basic knowledge of Python and handling CSV files.
• Python 3.8+ installed with pandas and faker (pip install pandas faker).
• An example CSV file with PII columns (e.g.: name, email, ssn).

Step 1: Why mask data and common strategies

Data masking protects PII for development, testing and analysis, reducing exposure. Common strategies: deterministic substitution (consistent mapping), random substitution (non-reversible), tokenization and encryption. We will apply two techniques: deterministic substitution for contacts and random substitution for SSN.

Step 2: Prepare the environment and load data

Load the CSV into a DataFrame with pandas. Keep an original copy and work on another to ensure auditability.

import pandas as pd
from faker import Faker

fake = Faker()
# Exemplo: ficheiro input.csv com colunas: id,name,email,ssn,phone
df = pd.read_csv('input.csv')
df_orig = df.copy()

Step 3: Implement deterministic substitution

Deterministic substitution ensures the same input value always generates the same masked value — useful to preserve relationships between tables. Use a simple hash and a persistent mapping (here a JSON file as an example).

import hashlib, json
from pathlib import Path

map_file = Path('mask_map.json')
if map_file.exists():
    mask_map = json.loads(map_file.read_text())
else:
    mask_map = {}

def deterministic_mask(value, prefix='U'):
    if pd.isna(value):
        return value
    key = str(value)
    if key in mask_map:
        return mask_map[key]
    h = hashlib.sha256(key.encode('utf-8')).hexdigest()[:10]
    masked = f"{prefix}_{h}"
    mask_map[key] = masked
    return masked

# Exemplo para email e phone
df['email_masked'] = df['email'].apply(lambda v: deterministic_mask(v, prefix='EM'))
df['phone_masked'] = df['phone'].apply(lambda v: deterministic_mask(v, prefix='PH'))

# Persistir o mapa para reutilização
map_file.write_text(json.dumps(mask_map))

Step 4: Implement random substitution (irreversible)

For fields that do not need to be correlated (e.g.: SSN in test data), generate random non-reversible values. The Faker module is useful to create plausible values.

def random_ssn(_):
    # Gera um número de identificação plausível (exemplo US SSN-format)
    return fake.ssn()

# Aplica substituição aleatória; não é determinística
df['ssn_masked'] = df['ssn'].apply(random_ssn)

Step 5: Keep audit logs and anonymization rules

Store metadata about what was masked: columns, method (deterministic/random), date and user. This helps governance and reproducibility without exposing PII.

from datetime import datetime
metadata = {
    'masked_columns': ['email','phone','ssn'],
    'methods': {'email':'deterministic','phone':'deterministic','ssn':'random'},
    'timestamp': datetime.utcnow().isoformat() + 'Z'
}
pd.Series(metadata).to_json('mask_metadata.json')

Step 6: Validate and export the masked data

Check that there are no original values in the masked columns and export to the destination (CSV, data lake or database). Keep separate stores for originals and masked data when necessary.

# Verificações simples
assert df['email_masked'].isna().sum() == df['email'].isna().sum()
assert not df['ssn_masked'].isin(df['ssn']).any()

# Escolhe colunas para exportar (substitui originais)
df_out = df.copy()
df_out['email'] = df_out['email_masked']
df_out['phone'] = df_out['phone_masked']
df_out['ssn'] = df_out['ssn_masked']

# Exporta
df_out.drop(columns=['email_masked','phone_masked','ssn_masked']).to_csv('output_masked.csv', index=False)

Verify the result

Open output_masked.csv and confirm that the sensitive columns have been replaced and that there are no original values. Also check mask_map.json to ensure deterministic consistency and mask_metadata.json for auditing. Test re-running with some added records to ensure persistent mapping.

Conclusion

Applying data masking in ETL protects PII and allows sharing data for testing and analysis without exposure. Next steps: integrate this code into a pipeline (e.g.: Airflow or Azure Data Factory), add encryption for map_file and control access. Tip: choose deterministic for keys that need linkage between systems and random when correlation is not required.