Tabular QC for TSV Data | Dataford Interview Questions

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

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

Schema

tsv_import

Column	Type	Description
source_rowPK	INT	Original row number in the TSV file
sample_id	VARCHAR(20)	Imported sample identifier
assay_code	VARCHAR(20)	Imported assay code
collection_date_text	VARCHAR(10)	Collection date as imported text
result_text	VARCHAR(30)	Assay result as imported text
qc_flag	VARCHAR(10)	Imported quality-control flag

valid_assays

Column	Type	Description
assay_codePK	VARCHAR(20)	Approved assay code
assay_name	VARCHAR(80)	Human-readable assay name

Your question is Tabular QC for TSV Data. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem