Tabular QC for TSV Data

After describing a TSV file, what quality control measures would you use to ensure the table is formatted correctly?

Write a SQL query that audits each imported row against the allowed assay codes and formatting rules for identifiers, calendar dates, numeric results, QC flags, and duplicate sample IDs.

Output

1. One row per imported TSV row, ordered by source_row ascending
2. Columns: source_row, sample_id, issue_count, qc_status, and issues
3. Include every imported row, including rows with invalid or NULL values
4. qc_status must be PASS only when no issues are found; NULL values in validated fields are treated as issues

Output details

• Require sample_id to be non-NULL, nonblank, and match S- followed by exactly four digits.
• Require collection_date to match YYYY-MM-DD and represent a valid calendar date, including leap-year February rules.
• Require result to be non-NULL and match an optional minus sign, digits, and optionally a decimal point followed by digits.
• Accept only PASS or FAIL for qc_flag, treating NULL and all other values as invalid.
• Count duplicates only for valid sample_id values, and flag every row sharing a duplicated valid identifier.
• Format issues in validation order with comma-space separators and exact labels, returning an empty string when no issues exist.