Longitudinal Research Data Scrubbing and Partitioning Generator

Role: Principal Research Data Engineer specializing in longitudinal clinical and educational research data governance.

Context

• Target database engine: {{dataset_engine}}
• Raw ingestion table name: {{raw_table_name}}
• Sensitive identifier columns to mask or drop: {{pii_fields_list}}
• Primary partition dimension: {{partition_dimension}}
• Mandatory retention threshold in years: {{retention_policy_years}}
• Target analytical schema: {{target_schema}}

Task

Generate an executable, production-grade database transformation script that reads raw participant logs from {{raw_table_name}}, strips and pseudonyms all identifiers specified in {{pii_fields_list}}, applies partitioning on {{partition_dimension}}, and stages clean analytical tables in {{target_schema}} conforming to a {{retention_policy_years}}-year retention boundary.

Method

1. Inspect the source schema structure of {{raw_table_name}} for {{dataset_engine}} data type mapping compatibility.
2. Design a deterministic salted hash or surrogate key generation routine for participant identifiers contained in {{pii_fields_list}}.
3. Formulate table definition statements inside {{target_schema}} that establish native partitioning using {{partition_dimension}}.
4. Write the transformation query executing column-level anonymization, nullification of direct identifiers, and noise addition for sensitive continuous measures.
5. Implement automated data validation assertions to verify no unhashed values from {{pii_fields_list}} leak into {{target_schema}}.
6. Add partition management and archival logic enforcing the {{retention_policy_years}}-year cutoff filter.
7. Inject transaction control blocks, rollbacks on error, and index creation for the partitioned target dataset.

Constraints

• MUST produce fully executable, syntactically correct code tailored exclusively to {{dataset_engine}}.
• MUST NOT retain any cleartext values listed in {{pii_fields_list}} in intermediate temporary views or destination schemas.
• Dynamic partition pruning must be enabled where supported by {{dataset_engine}}.
• Include inline commentary explaining every pseudonymization function used.

Output format

• Section 1: Pre-execution Prerequisites and Environment Setup (1 paragraph).
• Section 2: Complete Executable Script (SQL or native procedure block, fully annotated).
• Section 3: Data Quality & Anonymization Verification Queries (3-5 validation statements).
• Section 4: Rollback & Maintenance Runbook (150-250 words).

Self-review

• Confirm that every field in {{pii_fields_list}} is explicitly masked, hashed, or dropped in the script body.
• Verify that partitioning on {{partition_dimension}} matches {{dataset_engine}} best practices.
• Check that transaction isolation and error handling prevent partial unmasked data writes.