How to implement automatic data purge in a Lakehouse: step by step

João Barros 10 de November de 2026 4 min read

This tutorial shows how to implement an automatic data purge mechanism in a Lakehouse to remove old records in a secure and auditable way. It is useful to comply with retention policies, reduce storage costs and maintain performance without losing critical history.

Prerequisites

Account with access to the Lakehouse (e.g.: OneLake/Delta tables) and permissions to read/write and run jobs.
PySpark or SQL environment over the Lakehouse (e.g.: notebook in Fabric or a cluster with PySpark).
Basic knowledge of Delta Lake: Delta tables, MERGE, VACUUM and Time Travel.

Step 1: Define retention policy and purge criteria

Before any code, decide what to remove: for example, records with the event_date earlier than N days, or rows with a specific state. Also define whether you will archive before deleting and who approves the purge.

Step 2: Create a purge control table (audit trail)

Log each purge execution for audit: date, target table, criteria, number of rows affected and user who executed it.

CREATE TABLE IF NOT EXISTS control.expurgo_audit (
  run_id STRING,
  target_table STRING,
  criteria STRING,
  exec_time TIMESTAMP,
  rows_marked BIGINT,
  rows_deleted BIGINT,
  status STRING
) USING DELTA;

Step 3: Mark rows for purge (soft-delete) using a status column

It is recommended to do an intermediate step to avoid accidental loss: mark rows for purge using a column _expurgo_flag and record the version before removal.

# Exemplo PySpark
from pyspark.sql import functions as F
from uuid import uuid4

target = "bronze.vendas"
cutoff_days = 365
run_id = str(uuid4())
cutoff = F.date_sub(F.current_date(), cutoff_days)

# Marca linhas
spark.sql(f"ALTER TABLE {target} ADD COLUMNS (_expurgo_flag BOOLEAN) ")

spark.sql(f"MERGE INTO {target} t USING (
  SELECT *, true as _expurgo_flag FROM {target} WHERE event_date < date_sub(current_date(), {cutoff_days})
) s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t._expurgo_flag = s._expurgo_flag")

Step 4: Archive the marked rows to a historical table

Before deleting, copy the marked rows to an archive table (e.g., archive.vendas_expurgo) with execution metadata.

-- Exemplo SQL
INSERT INTO archive.vendas_expurgo
SELECT *, '$run_id' as run_id, current_timestamp() as archived_at
FROM bronze.vendas
WHERE _expurgo_flag = true;

Step 5: Physically remove the rows with MERGE (hard-delete)

Use MERGE to delete the marked rows in an atomically consistent way.

MERGE INTO bronze.vendas t
USING (SELECT id FROM bronze.vendas WHERE _expurgo_flag = true) s
ON t.id = s.id
WHEN MATCHED THEN DELETE;

Step 6: Update the control table and run VACUUM

Record the result in control.expurgo_audit and run VACUUM carefully to clean up old files (ensure Time Travel is not needed).

-- Registar execução (exemplo SQL simplificado)
INSERT INTO control.expurgo_audit VALUES (
  '$run_id', 'bronze.vendas', 'event_date < date_sub(current_date(), 365)', current_timestamp(), NULL, /*rows_deleted*/(SELECT count(*) FROM bronze.vendas WHERE _expurgo_flag = true), 'completed'
);

-- Executar VACUUM apenas depois de confirmar que Time Travel não é necessário
VACUUM bronze.vendas RETAIN 168 HOURS;  -- retém 7 dias

Step 7: Automate and handle common errors

Create a scheduled job (notebook/job) that runs the steps, with logging and partial rollback in case of failure. Common errors: forgetting to archive before deleting, insufficient permissions, not adding the marker column beforehand (ALTER TABLE) and using VACUUM with too low retention.

Verify the result

Confirm that:

archive.vendas_expurgo contains the archived rows with run_id and archived_at.
bronze.vendas does not contain rows with _expurgo_flag = true.
control.expurgo_audit has a record with run_id and consistent rows_deleted.
Time Travel allows checking previous versions if needed (SELECT * FROM bronze.vendas TIMESTAMP AS OF ...).

Conclusion

Implementing automatic purge in a Lakehouse protects compliance and reduces costs by combining marking, archiving, MERGE and VACUUM. Next steps: include encryption for archived files, add notifications and integrate with the organization’s retention policies. Tip: always test the process in a development environment with simulated data before running in production.