Import validation works by checking whether source records and transformed records satisfy the rules required for a trustworthy load.
A parser can successfully read a file whose records are wrong.
A row can satisfy the correct data type and still belong to the wrong person.
A date can be valid and still fall outside the assessment period.
Validation therefore needs several layers.
This article is the second pillar of How Data Imports Fail.
The Core Idea: Validation Should Catch the Earliest Detectable Wrong State
The earlier an error is detected, the cheaper it is to repair.
A malformed CSV field can be rejected before transformation.
An invalid category can be caught after mapping.
A duplicate identity may require checking the target.
A wrong total may only become visible during reconciliation.
Validation should be placed where the relevant evidence first becomes available.
Layer 1: File-Level Validation
Before processing rows, verify the file itself.
- Expected file type.
- Expected delimiter and quoting rules.
- Character encoding.
- Header presence.
- Expected header names.
- Expected number of columns.
- Source identity and generation time.
- Reasonable file size and row count.
A correct-looking filename is not enough.
Validate Source Identity
Which system created the file?
Which export job?
Which date or period?
Which cohort?
An old file with a perfect schema can be the wrong input.
Source identity belongs in the validation state.
Validate the Header Against the Mapping
If a required column disappears or a new unexpected column appears, stop or flag the batch.
Do not assume the old mapping remains safe.
Header MATCH options in database tools such as PostgreSQL COPY illustrate the value of checking expected column names rather than trusting position alone.
Validate Row Shape
Each record should have the expected number of fields under the actual CSV quoting rules.
Do not split raw lines on commas.
RFC 4180 documents quoted fields that can contain commas and line breaks.
Use a proper parser.
Layer 2: Type Validation
Can each source or transformed value be represented in the required type?
- Integer.
- Decimal.
- Date.
- Timestamp.
- Boolean.
- Identifier string.
- Enumerated category.
- Free text.
Type validation prevents obvious structural corruption but does not establish business correctness.
Reject Ambiguous Conversions
If the value ‘03/10/2026’ has no declared date convention, parsing it is guesswork.
If a percentage field contains ‘0.85’ but the scale is undocumented, conversion is ambiguous.
Validation should sometimes reject uncertainty rather than manufacture confidence.
Layer 3: Range Validation
Some values are impossible or outside the allowed domain.
A mark cannot exceed its maximum score.
An attendance percentage may need to stay between 0 and 100.
A Secondary 1 class code should not contain a Secondary 4-only category if the source scope is fixed.
Range rules should come from the real domain, not arbitrary developer assumptions.
Avoid Overly Narrow Range Rules
Validation itself can be wrong.
A name-length rule may reject legitimate names.
A phone-number rule may assume one country.
A score range may change when assessment format changes.
Every rule should have a documented reason.
Layer 4: Requiredness Validation
Required fields should be tied to the transaction.
A missing Student ID may block result import because identity cannot be established.
A missing optional comment may be acceptable.
Do not make every field mandatory merely because blank values are inconvenient.
Conditional Requiredness
Some fields are required only under certain conditions.
If assessment_type = weighted, max_score may be required.
If country != Singapore, an international phone code may become necessary.
The condition should be explicit and testable.
Layer 5: Uniqueness Validation
A field or combination may need to be unique.
Examples include one external learner ID per student or one result per student-assessment-attempt key.
Uniqueness should be defined at the correct entity level.
A repeated name is not necessarily a duplicate person.
Layer 6: Referential Validation
A row may refer to another entity.
The referenced student, assessment, class or parent record should exist or be created through a controlled sequence.
A syntactically valid foreign identifier can still point nowhere.
Layer 7: Cross-Field Validation
Some rules involve relationships between fields.
- end_date must not precede start_date.
- raw_score must not exceed max_score.
- percentage should agree with raw_score ÷ max_score when both are supplied.
- status = completed may require completion_date.
- country and postcode format may need to agree.
These rules catch records whose individual fields all look valid.
Layer 8: Cross-Record Validation
A file can contain contradictions across rows.
The same external ID may appear with two different names.
One assessment code may appear with two different maximum scores.
A duplicate batch may repeat every row.
Validation should look beyond one row at a time where the domain requires it.
Layer 9: Target-Aware Validation
Some errors only exist relative to the target.
A source record may be valid but conflict with an existing target record.
An external ID may already be attached to another entity.
A category may have been retired.
Target-aware checks should occur before irreversible commitment where possible.
Validation Before or During Load
Some systems validate everything first.
Others validate row by row during loading.
Both can work.
The important part is to define what happens after an error and what state may already have been committed.
Fail the Whole Batch or Reject Individual Rows?
This is a policy decision.
Atomic batch
Any blocking error prevents the batch from committing.
Partial load
Valid rows load; invalid rows go to a reject set.
Neither is universally better.
Use atomic behaviour where partial state would be dangerous.
Use controlled partial loading where independent rows can be safely separated and rejects remain visible.
Partial Loads Need Strong Evidence
If 950 rows load and 50 reject, report both.
Do not say only ‘import complete’.
The operator should know whether the target is intentionally partial.
Reject Records Should Preserve Source Context
A reject record should help the source owner repair the data.
- Batch ID.
- Source row or stable source identifier.
- Field involved.
- Rejected value where safe to record.
- Rule violated.
- Human-readable explanation.
- Suggested repair if unambiguous.
Do not expose sensitive values unnecessarily in logs.
Do Not Turn Reject Handling Into Data Loss
Rejected rows must not vanish.
Keep a controlled reject report or queue so the unresolved work remains visible.
The status system should distinguish accepted, rejected, pending repair and intentionally excluded records.
Error Messages Should Name the Rule
‘Invalid row’ is weak.
‘raw_score 72 exceeds max_score 50’ is useful.
‘Unknown category S3X; allowed values for mapping version 4 are Sec1, Sec2, Sec3, Sec4’ is useful.
Good errors reduce repair time.
Error Messages Should Not Reveal Sensitive Data
Logs can become another uncontrolled copy of personal information.
Where values are sensitive, identify the field and record key rather than dumping the entire row.
Validation evidence should be sufficient for repair and proportionate to privacy requirements.
Warnings Versus Blocking Errors
Not every unusual value should block the load.
A warning may flag a rare but valid value for review.
Blocking errors should be reserved for conditions that make the target state unreliable or violate a real requirement.
This prevents validation fatigue.
Validation Severity
- Blocking — row or batch cannot be trusted.
- High concern — load may proceed only under explicit review.
- Warning — unusual value worth inspection.
- Informational — normal transformation or cleanup event.
Severity should reflect consequence.
Normalize Before or After Validation?
Some cleanup is safe before validation.
Trimming harmless surrounding spaces can be reasonable.
Other transformations can hide errors.
Changing an unknown category to the nearest valid spelling before recording the original may erase evidence.
Keep raw source values available for audit where appropriate.
Validate Both Raw and Transformed Values
A raw source value can be valid while its transformed target value is not.
Example: 85 is valid source percentage; transformation mistakenly multiplies by 100; target becomes 8,500.
Post-transformation validation catches mapping defects.
Validate Defaults
A default value can satisfy requiredness while hiding missing source information.
If the target fills ‘active’ whenever status is blank, validate whether that default is permitted for this import.
Defaults are transformation rules and deserve tests.
Validate Truncation
If a target text field is shorter than the source, do not silently cut the value unless that behaviour is explicitly accepted.
Truncation can change names, codes and meaning.
Prefer rejection or controlled transformation with evidence.
Validate Precision and Rounding
Currency, measurements and calculated marks can change under rounding.
Specify acceptable precision.
A target that stores two decimal places should not silently discard material precision without a rule.
Validate Character Encoding
Test multilingual examples.
A file that passes with English names may still corrupt Chinese characters or symbols.
Encoding validation should include realistic text from the intended population.
Validate Identifier Integrity
Identifiers should preserve exact characters and width where required.
Check leading zeros, case sensitivity, separators and maximum length.
Do not normalize identifiers as if they were ordinary prose.
Validate Duplicates
Duplicate detection can happen at several levels.
- Duplicate rows within the file.
- Repeated file or batch.
- Existing target record.
- Conflicting duplicate where the same key has different values.
Each requires different handling.
Validate Delete Semantics
If a snapshot import can remove target records, test the deletion rule carefully.
Absence from a partial file should not be interpreted as deletion unless the source contract says so.
Deletion is a high-consequence transformation.
Validation in Staging
A staging area lets the team inspect transformed records without treating them as final target state.
This can make error reporting and reconciliation easier.
Staging itself must have appropriate privacy and retention controls.
Validation at Scale
Large files require efficient checks.
Some validation can be vectorised or performed in database constraints.
Do not remove important rules merely because row-by-row code is slow.
Move the rule to a scalable mechanism.
Use Database Constraints as a Last Line of Defence
NOT NULL, UNIQUE, CHECK and foreign-key constraints can protect target integrity.
They should not be the only user-facing validation layer because their raw errors may be hard to interpret.
Pre-validation plus target constraints gives stronger defence.
Do Not Disable Constraints Merely to Finish the Import
Constraints often reveal that the import plan is wrong.
Temporarily disabling them may be legitimate under a designed migration process with later verification.
It should not be an ad hoc response to inconvenient errors.
Worked Example: Student Scores
Source row: Student 0412, raw_score 54, max_score 50.
Type check passes.
Requiredness passes.
Cross-field validation fails.
The row should not become a 108% score merely because the arithmetic is computable.
Worked Example: Duplicate Import
The same source batch is accidentally uploaded twice.
File-level validation identifies the batch checksum or source batch ID as already processed.
The system stops before creating duplicate results.
This is validation of transaction identity, not row content.
Worked Example: Unknown Category
Source level = ‘Sec 1 Express’.
Target uses Full SBB subject-level categories rather than the old stream label.
The mapping has no approved crosswalk.
Do not guess.
Reject or quarantine the row for policy review.
Worked Example: Content Migration
A WordPress import contains status = private.
Target allows only draft and publish under the current migration script.
Validation should stop or map under an explicit rule.
Silently converting private to publish would create a serious state error.
Worked Example: AI Structured Output
A model returns valid JSON with a score field.
Schema validation passes.
The score is unsupported by the source evidence.
Format validation cannot prove factual validity.
Add evidence-level checks before import into an authoritative record.
A Practical Validation Matrix
- Rule ID.
- Scope: file, row, field, cross-field, cross-record or target.
- Condition.
- Severity.
- Error message.
- Repair owner.
- Can load proceed?
- Evidence retained.
- Rule version.
The matrix makes validation policy inspectable.
Validation Test
- Do we know the exact source file and period?
- Does the header match the mapping?
- Can every value be parsed without guessing?
- Do required fields exist?
- Do ranges and categories make sense?
- Are identifiers unique where required?
- Do references resolve?
- Do cross-field relationships hold?
- Can duplicate batches be detected?
- Are rejects visible and repairable?
- Can valid rows be distinguished from partial success?
- Are transformed values validated too?
- Do database constraints agree with the import policy?
Validation Before Reconciliation
Validation reduces the number of bad states that enter the target.
It does not prove that every intended record arrived correctly.
That final evidence comes from How Import Reconciliation Works.
Across the eduKate Ecosystem
This pillar complements How Form Validation Works, which owns interactive user-entry validation, and EMIS Data Quality, Validation & Administrative Data Assurance, which owns education-system administrative assurance. Import validation is the batch-transfer mechanism between source and target states.
Sources and Further Reading
RFC 4180 — Common Format and MIME Type for CSV Files
W3C — CSV on the Web: A Primer
PostgreSQL Documentation — COPY
AWS Database Migration Service — Data Validation
The Deeper Principle: Reject Uncertainty Before It Becomes Authority
Import validation is not about making files look tidy.
It is about protecting the target from states that cannot be justified.
When the importer cannot establish meaning, identity or consistency, the safest answer is often to stop that record and make the uncertainty visible.
Continue the Series
How Data Imports Fail | Why Clean Files Can Still Create Wrong Records
How Import Schema Mapping Works | Match Fields, Types, Units and Identifiers Before Loading Data
How Import Reconciliation Works | Prove the Target Matches the Source After the Load