Handling Missing Data During Ingestion | Dataford Interview Questions

Tell me how you handle missing or malformed data during ingestion.

Inspect the provided raw records and allowed event types. Return one validation result for every raw record. Treat trimmed blank values as missing, accept only calendar-valid dates in YYYY-MM-DD form, accept nonnegative decimal measurements, and require a recognized event type. Do not allow malformed values to abort the query.

Output

Columns: source_line, record_id, ingestion_status, rejection_reason, normalized_visit_date, normalized_measurement
One row per raw record, ordered by source_line
Include accepted and rejected records; rejected records must identify each applicable reason

Schema

raw_ingestion_records

Column	Type	Description
source_linePK	INTEGER	Source row number from the ingestion file
record_id	VARCHAR(30)	Raw record identifier
raw_visit_date	VARCHAR(30)	Raw visit date text
raw_measurement	VARCHAR(30)	Raw numeric measurement text
event_type	VARCHAR(30)	Raw event type code
subject_code	VARCHAR(30)	Subject identifier

allowed_event_types

Column	Type	Description
event_typePK	VARCHAR(30)	Recognized event type code
event_description	VARCHAR(100)	Description of the recognized event type

Your question is Handling Missing Data During Ingestion. 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