Design and implement a robust data validation and cleaning pipeline using Python and SQL for clinical trial data, including checks for range, consistency, and missing values, and describe how you would handle discrepancies and document the cleaning process for audit trails.

MECE Framework: 1. Design: Define data dictionary, validation rules (range, consistency, uniqueness, missingness) per CRF/protocol. Schema-on-read for Python (Pandas) and schema-on-write for SQL (CREATE TABLE, CHECK constraints). 2. Implement: Develop Python scripts using Pandas for initial data loading, type conversion, and rule-based validation. Utilize SQL stored procedures/functions for cross-table consistency checks and referential integrity. 3. Execute: Automate pipeline via Airflow/CRON. Log all validation failures. 4. Handle Discrepancies: Flag invalid records. Generate discrepancy reports for data queries (DQs) to sites. Implement a 'quarantine' table in SQL for problematic data requiring manual review. 5. Document: Maintain a detailed data cleaning log (Python/SQL comments, version control). Store validation rules, DQ resolutions, and audit trails in a central repository for regulatory compliance.

I would design a robust data validation and cleaning pipeline using a multi-layered approach. First, I'd define a comprehensive data dictionary specifying expected data types, ranges, and categorical values for each variable, derived directly from the clinical trial protocol and CRFs. In Python, I'd use Pandas for initial data loading, type coercion, and implementing custom functions for range checks, consistency checks (e.g., visit dates sequential, lab values within physiological limits), and identifying missing values. SQL would be leveraged for enforcing referential integrity, unique constraints, and cross-table consistency checks via CHECK constraints and stored procedures.

Discrepancies would be handled systematically: invalid records are flagged, not deleted. A 'discrepancy report' is generated, detailing the issue, variable, and record ID, which then triggers a data query (DQ) to the clinical site for clarification or correction. Corrected data is re-validated. For audit trails, every step of the cleaning process—including validation rules, identified discrepancies, DQ issuance, resolution, and any data modifications—is meticulously logged in a version-controlled repository (e.g., Git) and a dedicated SQL audit table, ensuring full traceability and compliance with regulatory requirements.