How to automate file cleanup in the Data Lake with Azure Synapse Analytics

João Barros 13 de September de 2026 5 min read

Automating the removal of obsolete files in the Data Lake helps control costs, improve ingestion and indexing performance, and maintain organization. In this tutorial I explain why following a pattern with Azure Synapse Analytics (Synapse Pipelines) orchestrating and an Azure Function executing the cleanup logic is a practical and scalable approach. I will give concrete examples, typical parameters and best practices to avoid deleting data by mistake.

Prerequisites

Azure account with permissions to create resources (Synapse workspace, Storage, Function).
Azure Synapse Analytics workspace with Synapse Pipelines enabled.
ADLS Gen2 (Storage account) with a data container and some prefixes (for example logs/ or staging/).
Azure Function App (Consumption or Premium) with identity or key to delete files.
Basic familiarity with JSON and PowerShell/Python to test locally.

Step 1: Concept and why the architecture

Instead of performing massive operations directly in Synapse (which can increase costs and complexity), the pattern separates orchestration from execution: Synapse Pipelines acts as a scheduler and orchestrator; the Azure Function contains the listing and deletion logic in ADLS Gen2. Advantages: scalability (the Function can scale with load), centralized logging and telemetry, reuse of the Function by other pipelines and a smaller risk surface when removing files.

Concrete example: in a company that accumulates 100k small files per month, deleting files older than 90 days can reduce read/list overhead and operational costs. Additionally, a daily pipeline that checks a prefix with 10k files usually completes in under 5 minutes with the Function, depending on the size and latency of the storage account.

Step 2: Create the Azure Function to delete old files

The Function receives parameters (container, prefix, age in days, mode_preview) and deletes files with lastModified earlier than the cutoff date. Here is a robust example in Python (uses azure-storage-blob), which includes logging, a preview mode to not delete anything during tests and a limited return count to avoid very large payloads.

import os
import logging
from datetime import datetime, timezone, timedelta
from azure.storage.blob import ContainerClient

conn_str = os.environ.get('AZURE_STORAGE_CONNECTION_STRING')

def main(req):
    data = req.get_json()
    container = data.get('container')
    prefix = data.get('prefix','')
    days = int(data.get('days',30))
    preview = bool(data.get('preview',True))

    client = ContainerClient.from_connection_string(conn_str, container)
    cutoff = datetime.now(timezone.utc) - timedelta(days=days)
    deleted = []

    for blob in client.list_blobs(name_starts_with=prefix):
        try:
            if blob.last_modified and blob.last_modified < cutoff:
                logging.info(f"Candidate: {blob.name} last_modified={blob.last_modified}")
                if not preview:
                    client.get_blob_client(blob).delete_blob()
                    deleted.append(blob.name)
                else:
                    # preview mode: apenas registar
                    deleted.append(f"PREVIEW:{blob.name}")
        except Exception as e:
            logging.error(f"Erro a processar {blob.name}: {e}")

    return {
        'status': 200,
        'deleted_count': len([d for d in deleted if not str(d).startswith('PREVIEW')]),
        'preview': preview,
        'sample': deleted[:50]
    }

Step 3: Enable authentication and access to ADLS Gen2

You have two main options: use AZURE_STORAGE_CONNECTION_STRING in the Function Application Settings (quick for dev) or assign a Managed Identity and use RBAC (recommended for production). For critical environments, create a Managed Identity for the Function and assign the "Storage Blob Data Contributor" role at the container or storage account level. This avoids exposing keys and eases credential rotation.

Example: in a security policy, grant the role only to the target container; alternatively, use more restrictive policies such as Azure AD + POSIX ACLs if you need per-file control.

Step 4: Create a Synapse Pipeline that calls the Function

In Synapse Studio create a Pipeline with a Web Activity that does a POST to the Function URL. Pass the JSON parameters (container, prefix, days, preview). This allows scheduling, monitoring runs and integrating conditions before and after cleanup.

{
  "method": "POST",
  "url": "https://.azurewebsites.net/api/",
  "headers": {
    "Content-Type": "application/json"
  },
  "body": {
    "container": "dados",
    "prefix": "logs/",
    "days": 90,
    "preview": true
  }
}

Step 5: Add retry logic and notification

Configure retry properties on the Web Activity (e.g., 3 attempts with 30 s between each) and timeout (for example 5 m). Use an "If Condition" Activity to evaluate the deleted_count field in the output and, if values exceed a threshold (e.g., >1000), route to a Logic App that notifies the team via email or Teams. On errors, log the run and send automatic alerts.

Step 6: Schedule and test the Pipeline

Use a schedule Trigger on the Synapse Pipeline (e.g., daily at 02:00). Run tests with preview=true and days=1 on a development prefix to confirm behavior. Then do a controlled test with preview=false on a staging prefix and a days limit (for example 180) before applying in production. Always check the Azure Function logs and the Synapse run details to confirm deleted_count and sample.

Verify the result

Confirm in Storage Explorer or in the Storage account portal that files with lastModified earlier than the cutoff were removed. In the Synapse Pipeline check the run details of the Web Activity: it should return deleted_count, preview flag and a sample list. Also consult Application Insights (if configured) for latency and error metrics.

Conclusion

This pattern with Synapse Pipelines orchestrating an Azure Function is simple, reusable and allows automating Data Lake cleanup safely. In production, it is recommended to use Managed Identity, instrument with Application Insights, and start with preview mode and progressive limits. Final tip: always keep a dev prefix and a validation process before applying rules to critical data to avoid the risk of accidental loss.