Design logic to identify and isolate records with bad schema (type mismatch).
Asked in the technical stage. The records contain values loaded as text, along with the type each field is expected to have. Return only records whose values cannot be interpreted as the declared type. Missing or blank values are not type mismatches.
record_id, field_name, raw_value, expected_type, source_system, ingested_at, and issue.UNKNOWN when a record has no matching batch, and sort by record_id, then field_name.| Column | Type | Description |
|---|---|---|
| record_idPK | INTEGER | Unique validation record identifier |
| batch_id | INTEGER | Referenced ingestion batch |
| field_name | VARCHAR(40) | Name of the validated field |
| raw_value | VARCHAR(100) | Value received from the source system |
| expected_type | VARCHAR(20) | Expected logical type |
| ingested_at | TIMESTAMP | Timestamp when the value was ingested |
| Column | Type | Description |
|---|---|---|
| batch_idPK | INTEGER | Unique ingestion batch identifier |
| source_system | VARCHAR(50) | System that supplied the batch |
| loaded_at | TIMESTAMP | Timestamp when the batch was loaded |