How to build ELT pipelines to anonymize PII: step by step

João Barros 15 de August de 2026 3 min read

Learn how to anonymize PII in ELT in a practical and secure way: this tutorial explains why it is important to handle PII (Personally Identifiable Information), which techniques to use and how to integrate those transformations into an ELT pipeline to keep data useful for analysis without exposing sensitive information. I will explain the rationale for each choice and give concrete steps with SQL examples that work on compatible engines (Databricks, Synapse, Snowflake).

Prerequisites

Account or environment with a compatible SQL/Delta engine (e.g.: Databricks, Synapse, Snowflake). Ideally with 4–8 nodes to test performance on datasets of 1–10M records.
Data source with PII (CSV/JSON) accessible to the environment; a test file with ~1000 rows helps validate before rollout.
Permissions to create tables, functions and run ELT jobs; access to a tokenization service if reversal is required.
Basic knowledge of SQL and hashing/cryptography functions. Knowledge of MERGE/UPSERT and partitioning is useful for idempotent pipelines.

Step 1: Identify PII fields and define anonymization requirements

Before coding, inventory the PII fields (e.g.: name, email, NIF, phone). For each field decide the appropriate method: simple masking (keeps part readable), irreversible hashing (SHA256) when you don't need to reverse, tokenization when controlled reversal is required, or generalization when only aggregation is needed (e.g.: store only phone prefix).

Example mapping for 5 fields:

name: masking (first 2 characters) — preserves segmentation by initials in reports;
email: keep domain, hash the user — useful to keep metrics by domain (e.g.: gmail.com) without exposing the user;
NIF: irreversible hashing — required to prevent reconstruction;
phone: generalization to national code (3 digits) — sufficient for region-level analyses;
created_at: keep intact for temporal analyses.

Also define legal requirements: for example, retention of PII may be limited to 2 years — document this in the governance plan.

Step 2: Create staging table non-anonymized

Load the original data into a staging table without changes. This allows auditing, reconciliation and reprocessing. Keep the staging in a secure zone (e.g.: OneLake/secure storage) with access control restricted to the responsible team.

-- Exemplo SQL para Databricks/Synapse/Snowflake
CREATE OR REPLACE TABLE raw_customers_staging (
  id STRING,
  name STRING,
  email STRING,
  nif STRING,
  phone STRING,
  created_at TIMESTAMP
);

-- COPY/LOAD a partir de CSV ou caminho de origem (varia por plataforma)
-- Para grandes volumes recomenda-se particionar por created_at ou por dia.

Step 3: Implement reusable anonymization functions

Create UDFs/SQL functions so the logic is centralized, testable and auditable. This makes updates easier (e.g.: change hashing algorithm) without altering multiple queries. Test the functions with datasets of 1000–10,000 records before using in production.

-- Hash irreversível (SHA256) e truncar para uma coluna identificadora
CREATE OR REPLACE FUNCTION hash_sha256(val STRING) RETURNS STRING AS (
  sha2(val, 256)
);

-- Mascaramento: manter primeiros 2 caracteres e substituir o resto por X
CREATE OR REPLACE FUNCTION mask_name(val STRING) RETURNS STRING AS (
  CASE WHEN val IS NULL THEN NULL
       WHEN length(val) <= 2 THEN repeat('X', length(val))
       ELSE substr(val,1,2) || repeat('X', length(val)-2)
  END
);

-- Generalizar telemóvel: manter só prefixo nacional (ex.: primeiros 3 dígitos)
CREATE OR REPLACE FUNCTION generalize_phone(val STRING) RETURNS STRING AS (
  CASE WHEN val IS NULL THEN NULL
       WHEN length(regexp_replace(val,'\\D','')) <= 3 THEN 'REDACTED'
       ELSE substr(regexp_replace(val,'\\D',''),1,3) || 'XXXXX'
  END
);

Step 4: Anonymize with an ELT query and write to target table

Apply the functions in the transformation and write the result to the final table that will be used by reports. Keep non-sensitive columns intact and clearly document which fields were transformed. If you need to reverse, integrate a managed and audited tokenization service instead of hashing.

CREATE OR REPLACE TABLE customers_anonymized AS
SELECT
  id,
  mask_name(name) AS name_masked,
  -- manter domínio: split email e hash o user
  concat(hash_sha256(split_part(email,'@',1)), '@', split_part(email,'@',2)) AS email_anonymized,
  -- NIF hashed para irreversibilidade
  hash_sha256(nif) AS nif_hash,
  generalize_phone(phone) AS phone_generalized,
  created_at
FROM raw_customers_staging;

Step 5: Automate ELT and validate idempotency

Schedule the job to run periodically (e.g.: hourly or nightly). Ensure idempotency using MERGE/UPSERT and watermarking (for example, process only records with created_at >= last_run). Test with reruns: after recreating the pipeline 3 times in a row the number of records in the target table should remain stable.

-- Exemplo MERGE para atualização incremental
MERGE INTO customers_anonymized tgt
USING (SELECT * FROM raw_customers_staging WHERE created_at >= date_sub(current_date(),1)) src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET
  name_masked = mask_name(src.name),
  email_anonymized = concat(hash_sha256(split_part(src.email,'@',1)), '@', split_part(src.email,'@',2)),
  nif_hash = hash_sha256(src.nif),
  phone_generalized = generalize_phone(src.phone),
  created_at = src.created_at
WHEN NOT MATCHED THEN INSERT VALUES (
  src.id,
  mask_name(src.name),
  concat(hash_sha256(split_part(src.email,'@',1)), '@', split_part(src.email,'@',2)),
  hash_sha256(src.nif),
  generalize_phone(src.phone),
  src.created_at
);

Verify the result

Perform automatic and manual checks: ensure there are no original fields in the anonymized table, that formats are consistent and that the hashing has the expected length (SHA256 → 64 hex chars). Examples of validation queries and metrics:

-- Amostra de 5 linhas para inspeção visual
SELECT id, name_masked, email_anonymized, phone_generalized FROM customers_anonymized LIMIT 5;

-- Verifica que nif_hash tem comprimento 64 (SHA256 hex)
SELECT DISTINCT length(nif_hash) AS len FROM customers_anonymized LIMIT 5;

-- Conta linhas para garantir idempotência após rerun
SELECT count(*) FROM customers_anonymized;

-- Percentagem de NULLs por coluna para validar qualidade
SELECT
  sum(CASE WHEN name_masked IS NULL THEN 1 ELSE 0 END)/count(*) AS pct_null_name,
  sum(CASE WHEN email_anonymized IS NULL THEN 1 ELSE 0 END)/count(*) AS pct_null_email
FROM customers_anonymized;

Conclusion

Anonymizing PII in ELT reduces risk and enables useful analysis. Start small (e.g.: 10k records) to validate correctness and performance before scaling to millions of rows. Practical next steps: implement reversible tokenization if required, add logging and audit (who ran the job, when), and test performance with batch and streaming data. And, of course, confirm legal requirements and internal governance policies before rollout.