Employee Data Retention and Purge Automation Script

Role: Lead Database Reliability Engineer specializing in enterprise compliance and data lifecycle automation.

Context

• Target database engine: {{target_database_engine}}
• Retention window limit: {{retention_period_days}} days
• Schema under management: {{target_schema_name}}
• Affected PII entity tables: {{pii_table_list}}
• Cold archive destination: {{archive_storage_path}}
• Safety execution mode: {{dry_run_flag}}

Task

Draft a fully automated, idempotent data archival and purge script that safely exports expired records to cold storage and deletes them from live operational tables in strict adherence to data governance policies.

Method

1. Initialize script parameters, setting execution safeguards based on {{dry_run_flag}}.
2. Open an explicit transaction with strict isolation settings tailored to {{target_database_engine}}.
3. Validate the availability and schema match of {{target_schema_name}} before modification.
4. Calculate the deterministic cutoff timestamp dynamically using {{retention_period_days}}.
5. Implement an export step piping eligible records from {{pii_table_list}} into {{archive_storage_path}}.
6. Structure batched DELETE statements using chunking to prevent table locking and transaction log exhaustion.
7. Insert audit trail records capturing timestamps, row counts deleted, and operator identity.
8. Enforce rollbacks on any unhandled error and emit clear operational stdout logs.

Constraints

• The script MUST execute all write operations within chunked transactions to avoid lock escalation.
• Destructive operations MUST NOT run when {{dry_run_flag}} is set to true.
• All database commands must be native and fully compatible with {{target_database_engine}}.
• Sensitive connection credentials must never be hardcoded into the output.

Output format

Provide the response strictly in three parts:

1. Execution Summary: Brief 2-3 sentence overview of the script's behavior.
2. Complete Executable Script: Single code block containing the end-to-end runnable code with inline comments.
3. Rollback & Verification Commands: 3-5 SQL verification queries to check counts post-execution.

Self-review

• Verify that batched deletion loops include pause intervals or commit boundaries.
• Confirm every table in {{pii_table_list}} is addressed with both archival and deletion steps.
• Ensure zero syntax dialect discrepancies against {{target_database_engine}}.