How to implement a Data Retention Policy in Data Governance: step by step

João Barros 31 de August de 2026 4 min read

This guide shows how to implement a Data Retention Policy in Data Governance to ensure data is retained and deleted according to internal policies and legal requirements. Automated retention reduces regulatory risk, limits exposure of sensitive data and optimizes storage costs — for example, archiving 500 GB of cold data can reduce active storage costs by 60% per year. The approach presented is practical: it describes how to define policies, tag datasets, detect eligible records, apply safe actions and validate results.

Prerequisites

An account with administrator permissions in Azure (or other cloud) and access to a data repository (e.g.: Azure Data Lake, Azure SQL). Ensure appropriate RBAC: an operations user should have limited write permissions and the auditor role should have read-only access to logs.
A cataloging/metadata tool (e.g.: Microsoft Purview or a custom catalog) to tag policies and store metadata about sensitive dates and data ownership.
Ability to run ETL/ELT scripts (PowerShell, Azure Data Factory, Azure Functions or SQL). For production operations, plan processing batches — for example, 10k records per job to avoid locks.
Basic knowledge of SQL and the organization’s compliance policies, including common legal retention periods (e.g.: 5 years for tax data, 2 years for marketing data) and log retention requirements (for example, 7 years for audit).
A documented exceptions/legal hold process: a capability to suspend deletion in legal cases or internal investigations.

Step 1: Define the retention policy (who, what, for how long)

Before coding, take an inventory and describe the policy: which data classes (PII, CustomerData, Transactional), the reason for retention (tax, operational, historical), the period and the action at the end of the period (anonymization, deletion, archiving). Mapping responsibilities is crucial: who approves the policy, who executes the job and who validates the results. For example, a policy for customer PII may be: retention 60 months after created_at, action = delete, approver = Data Protection Officer.

{
  "policyName": "Retencao_PII_5anos",
  "dataClasses": ["PII", "CustomerData"],
  "retentionPeriodMonths": 60,
  "action": "delete", // ou "anonymize" / "archive"
  "startDateField": "created_at"
}

Step 2: Tag datasets in the catalog

Associate datasets in the catalog (e.g.: Microsoft Purview) with the classes used in the policy. This allows applying the same rule to all classified files/tables and managing exceptions centrally. Ideally, maintain a mapping between class tags and owners: for example, 15 datasets tagged as PII, each with an owner responsible for approving the deletion plan. Automation relies on this tag to scale policies to hundreds of tables without manual configuration per table.

// Exemplo conceptual: marcar um dataset via API do catálogo
POST /catalog/datasets/{id}/tags
Body: { "tags": ["PII", "CustomerData"] }

Step 3: Create a job that detects data eligible for action

Create an ETL/ELT process that identifies records whose start field (e.g.: created_at) exceeds the retention period. This can be a pipeline in Azure Data Factory, an Azure Function or a SQL job. For efficiency, process in incremental batches (e.g.: 10k–50k records per run), and use indexes on the date field to reduce scanning. Test in a staging environment with representative samples (1–5% of total volume) and estimate execution time: a table of 10M records with a proper index can be evaluated in a few minutes per batch.

-- Exemplo SQL para identificar registos antigos
SELECT id, created_at
FROM dbo.Customers
WHERE created_at <= DATEADD(month, -60, GETUTCDATE())
  AND classification IN ('PII','CustomerData');

Step 4: Implement action: secure deletion or anonymization

Choose the action defined in the policy. For "delete" implement first a logical deletion (flag "deleted" and move to a quarantine table) followed by physical deletion after a grace period — for example, 30 days for recovery. For "anonymize", replace sensitive fields with irreversible values or hashes and keep an irreversible identifier if needed for aggregated analysis. For "archive", move data to lower-cost storage with long retention policies.

-- Exemplo: anonimização parcial em SQL
UPDATE dbo.Customers
SET email = CONCAT('redacted+', HASHBYTES('SHA2_256', CAST(id AS varchar)), '@example.invalid'),
    ssn = NULL
WHERE created_at <= DATEADD(month, -60, GETUTCDATE())
  AND classification IN ('PII','CustomerData');

-- Exemplo: eliminação física (faça backup antes!)
DELETE FROM dbo.Customers
WHERE created_at <= DATEADD(month, -60, GETUTCDATE())
  AND classification IN ('PII','CustomerData');

Step 5: Automate and schedule safe execution

Put the job on a schedule (Azure Data Factory trigger, Azure Automation, SQL Agent) and implement controls: detailed logging, transactions with rollbacks, tests in staging and manual approval for larger operations. It is recommended to run during low activity hours (e.g.: daily at 02:00) and throttle the rate to avoid impact. Maintain a recovery and backup plan before each mass run; for example, a weekly snapshot before the first run of the month.

// Exemplo conceptual Azure Function trigger (pseudo)
// Trigger diário: verifica e executa acções conforme política JSON
// Regista resultados em table RetentionAudit

Step 6: Audit and proof of compliance

Record who executed the process, which records were affected and the operation performed. Keep immutable logs (for example, stored in Append-Only storage or sent to a SIEM) and summaries in the catalog for audits. Retain evidence for a period longer than legal requirements (e.g.: logs for 7 years). The audit should include before/after counts, sample hashes and identification of exceptions.

-- Exemplo de tabela de auditoria
CREATE TABLE RetentionAudit (
  audit_id UNIQUEIDENTIFIER DEFAULT NEWID(),
  dataset_name NVARCHAR(200),
  action NVARCHAR(50),
  affected_count INT,
  executed_by NVARCHAR(200),
  executed_at DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
);

Verify the result

Validate that the policy was applied: check before/after counts (for example, 120k records identified and 119.8k processed with 0.2k in error), inspect record samples to ensure correct anonymization, confirm entries in RetentionAudit and that the catalog shows the updated state. Test error scenarios (insufficient permissions, job failure) and implement alerts (e-mail/Teams/Syslog) for incidents. Useful metrics: average time per batch, success rate, percentage of records in exception.

Conclusion

Implementing a Data Retention Policy in Data Governance protects the organization, reduces risk and simplifies audits. Start with small, well-documented policies, validate in staging and roll out in phases (for example, 3 pilot datasets) to reduce operational risk. Next steps: integrate with Data Lifecycle Management processes, create automated tests and hook into security alerts. Practical question: which dataset will have the greatest cost or risk impact if not prioritized — start there.