(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon

How to create age-based purge policies in a Lakehouse: step by step

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

This tutorial shows how to configure and apply age-based purge policies in a Lakehouse to safely remove old records. Age-based purge is useful to meet retention requirements, reduce storage costs and maintain query performance.

Prerequisites

  • Account with access to an environment that supports Delta Lake (for example Azure Databricks or Microsoft Fabric Lakehouse).
  • Permissions to run queries, create/alter Delta tables and schedule jobs.
  • Basic knowledge of SQL and PySpark.
  • A Delta table with a date/time column (for example created_at TIMESTAMP).

Step 1: Define the retention (age) policy

Before touching the data, decide the purge rule: for example, remove records with created_at older than 365 days. Document the rule and validate it with governance/privacy.

Step 2: Create a view or helper table with selection criteria

Create a view to identify records that should be purged. This separates the selection logic from the removal process and makes testing easier.

CREATE OR REPLACE VIEW db.purge_candidates AS
SELECT *
FROM db.my_delta_table
WHERE created_at < current_timestamp() - INTERVAL 365 DAYS;

Step 3: Test the selection without deleting anything (verification)

Run validation queries to count and inspect the records that would be removed. Avoid surprises.

-- Count how many records would be purged
SELECT COUNT(*) AS cnt FROM db.purge_candidates;

-- See 10 examples for inspection
SELECT * FROM db.purge_candidates LIMIT 10;

Step 4: Implement safe purge with MERGE or DELETE

Choose between direct DELETE or using MERGE for more control (for example to keep an audit). Here we show a direct DELETE in Delta. Always take a backup or create a snapshot first.

-- Example of direct DELETE (Delta Lake)
DELETE FROM db.my_delta_table
WHERE created_at < current_timestamp() - INTERVAL 365 DAYS;

Step 5: Create an optional audit copy before purging

If you need to record deleted data for audit, write it to a history table before the DELETE. Keep only the necessary fields to reduce costs.

CREATE TABLE IF NOT EXISTS db.purged_audit AS
SELECT *, current_timestamp() AS purged_at
FROM db.purge_candidates;

-- After the backup, execute the DELETE on the primary table
DELETE FROM db.my_delta_table
WHERE created_at < current_timestamp() - INTERVAL 365 DAYS;

Step 6: Automate with a scheduled job and alerts

Scheduling the purge avoids manual intervention. Create a job (for example in Databricks Job or Fabric pipeline) that executes the steps: generate audit, run DELETE, write log. Configure alerts in case of failure or if the number of purged records exceeds a threshold.

# Pseudocode for job (brief PySpark)
from delta import *
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

# backup
spark.sql("CREATE TABLE IF NOT EXISTS db.purged_audit AS SELECT *, current_timestamp() AS purged_at FROM db.purge_candidates")
# delete
spark.sql("DELETE FROM db.my_delta_table WHERE created_at < current_timestamp() - INTERVAL 365 DAYS")
# simple log
cnt = spark.sql("SELECT COUNT(*) AS c FROM db.purged_audit WHERE purged_at >= current_date() - INTERVAL 1 DAY").collect()[0]['c']
print(f"Purged {cnt} rows today")

Step 7: Maintain and optimize (VACUUM and OPTIMIZE)

After purging, the Delta table may still have old files. Run OPTIMIZE and VACUUM carefully to reclaim space and improve queries. Adjust retentionHours if needed, respecting version retention policies.

-- Optimize and remove old files (Databricks/Delta example)
OPTIMIZE db.my_delta_table;
VACUUM db.my_delta_table RETAIN 168 HOURS; -- 7 days

Verify the result

Confirm that the purge worked: count again, check storage size and inspect the audit table. Review job logs and alerts. Run a sanity query to ensure there are no records with created_at earlier than the cutoff date.

-- Quick verification
SELECT COUNT(*) FROM db.my_delta_table WHERE created_at < current_timestamp() - INTERVAL 365 DAYS;
-- Should return 0

-- Size and files (example using table info)
DESCRIBE HISTORY db.my_delta_table LIMIT 1;

Conclusion

Applying age-based purge policies in a Lakehouse protects compliance and controls costs. Next steps: integrate rules into data governance, create automated tests and adjust the process for retention by tag or by table. Tip: always test in a development environment and maintain a backup strategy before purging in production.