Data Retention and Archival Strategy Prompt

This prompt is for data platform engineers and architects who need to design a defensible, tiered data retention and archival strategy for a specific table or dataset. It takes a schema, access patterns, and regulatory requirements as input and outputs a structured plan covering hot/warm/cold storage tiers, retention periods, archival triggers, retrieval SLAs, and compliance alignment. Use this prompt before implementing lifecycle policies in your database, data warehouse, or object store. It is not a replacement for legal review of regulatory requirements, but it ensures the technical plan accounts for the regulations you specify.

The ideal user brings a concrete dataset definition—table schema, current volume, growth rate, and known access patterns—along with the specific regulatory frameworks that apply (GDPR, HIPAA, SOC 2, PCI DSS, or internal data governance policies). The prompt works best when you can articulate who queries this data, how fresh it must be, and what the cost of retrieval latency actually is. Do not use this prompt for abstract 'best practices' generation without a real table; it will produce generic advice that fails under production constraints. Do not use it as a substitute for legal or compliance review—the prompt will map technical controls to regulations you name, but it cannot interpret novel regulatory obligations or assess your organization's specific legal exposure.

Before running this prompt, gather the table DDL, a representative sample of query patterns (with frequencies and latency requirements), and the exact regulatory clauses or internal policy documents that govern retention. If you lack any of these inputs, the output will contain placeholders and assumptions that must be validated before implementation. After receiving the strategy, treat it as an engineering proposal: review the tier assignments against actual query logs, validate the archival trigger conditions with a dry run on non-production data, and have your compliance team confirm that the retention periods satisfy all applicable obligations. The next section provides the copy-ready prompt template you can adapt with your specific inputs.

This prompt template is the core engine for generating a data retention and archival strategy. It is designed to be copied directly into your AI harness, where you will replace the square-bracket placeholders with your specific dataset context. The prompt instructs the model to act as a data platform architect and produce a strictly structured JSON output. This output is not just a text document; it's a machine-readable specification that can be automatically validated against your compliance requirements and ingested by a data lifecycle management tool.

textCopy
You are a data platform architect. Your task is to design a tiered data retention and archival strategy for the dataset described below. You must produce a single, valid JSON object as your entire output, conforming to the [OUTPUT_SCHEMA] provided. Do not include any explanatory text outside the JSON object.

**Dataset Context:**
[CONTEXT]

**Output Schema:**
[OUTPUT_SCHEMA]

**Constraints:**
[CONSTRAINTS]

**Examples for Reference:**
[EXAMPLES]

**Risk Level and Review Requirements:**
[RISK_LEVEL]

To adapt this template, start by populating the [CONTEXT] placeholder with a detailed description of the dataset. This should include the table or dataset name, a description of its contents, its current storage technology (e.g., PostgreSQL, S3), its approximate size, and its primary access patterns. Next, define the [OUTPUT_SCHEMA] as a strict JSON schema. This schema is your contract; it should specify the exact structure for each storage tier (e.g., hot, warm, cold, archive), including fields for tier_name, storage_medium, retention_period, archival_trigger, retrieval_sla, and compliance_rationale. The [CONSTRAINTS] placeholder is where you inject non-negotiable business rules, such as "All PII must be anonymized after 365 days" or "Financial records must be retained for 7 years per regulation XYZ." Use the [EXAMPLES] section to provide one or two correct input-output pairs, which is the most effective way to teach the model the desired level of detail and tone. Finally, set the [RISK_LEVEL] to high if the data is subject to legal hold or strict compliance, which will trigger a note in the prompt for mandatory human review of the final plan.

Before integrating this prompt into a production pipeline, you must test it with several known datasets and constraints. Validate the output JSON against your schema to catch structural errors. More importantly, conduct an evaluation where a domain expert checks if the generated compliance_rationale correctly interprets the regulatory constraints you provided. A common failure mode is the model inventing plausible-sounding but incorrect legal justifications. Always treat the generated plan as a sophisticated first draft that requires human approval, especially for high-risk datasets. The next step is to wire this prompt into an application harness that can automatically validate the JSON output and flag plans for review before any changes are applied to a production database.

This prompt is designed to be integrated into a data platform governance pipeline, not used as a one-off chat interaction. The harness should treat the prompt as a deterministic policy engine that consumes structured metadata about a dataset and returns a machine-readable strategy. The primary integration points are a data catalog (for table metadata), a compliance registry (for regulatory requirements), and a storage tier configuration service (for target tier definitions). The harness must validate that all required inputs are present before invocation, because missing data volume statistics or undefined compliance obligations will produce a strategy that looks plausible but is operationally unsafe.

The implementation should wrap the prompt in a service function that first assembles the context from upstream systems: query the data catalog for table size, row counts, partition columns, and access patterns; query the compliance registry for applicable regulations and their retention minimums/maximums; and load the organization's defined storage tiers with their cost, latency, and durability properties. The prompt is then called with these structured inputs. The output must be parsed as JSON and validated against a strict schema that includes required fields for each tier assignment, retention period, archival trigger condition, and retrieval SLA. Any output that fails schema validation should trigger a retry with the validation errors injected into the [CONSTRAINTS] block, up to a maximum of two retries before escalating for human review.

For high-risk datasets containing PII, financial records, or healthcare data, the harness must enforce a human-in-the-loop approval gate. After successful schema validation, the generated strategy should be written to a pending-review state in the data catalog and flagged for approval by the data governance team. The approval UI should display the prompt's inputs, the generated strategy, and a diff against any existing retention policy. Only after approval should the strategy be applied to the storage tier configuration and the archival job scheduler. Log every invocation with the input context hash, output strategy, validation results, retry count, and reviewer identity to create an audit trail that satisfies SOC 2 and GDPR accountability requirements.

Model choice matters for consistency. Use a model with strong JSON mode and low temperature (0.1-0.2) to reduce variance in retention period calculations. Avoid models that tend to hallucinate regulatory citations; the prompt already provides the applicable regulations in the [COMPLIANCE_REQUIREMENTS] input, so the model should not be asked to recall legal requirements from training data. If the prompt is used in a RAG configuration, ground the compliance requirements in a vector store of internal policy documents rather than relying on the model's parametric knowledge. The harness should also include a periodic reconciliation job that compares applied retention policies against the latest compliance registry and flags any drift for re-evaluation.