Import reconciliation works by comparing the source, the transformation evidence and the target after a load, so that ‘import finished’ becomes a claim that can be checked.
A row count can match while values are wrong.
A total can match while records are swapped between people.
Every individual record can be correct while the wrong month was imported.
Reconciliation therefore needs several independent checks.
This article is the third pillar of How Data Imports Fail.
The Core Idea: Reconciliation Asks Whether the Intended State Arrived
Validation asks whether proposed input is acceptable.
Reconciliation asks whether the target now agrees with what the source and mapping said should happen.
This is a post-load evidence problem.
AWS Database Migration Service describes data validation as comparing source and target rows and reporting mismatches.
That source-to-target comparison captures the heart of reconciliation.
Begin With the Batch Boundary
Which import are we checking?
Record the batch ID, source file, source period, mapping version and load time.
Without a batch boundary, target data from earlier loads can make counts and totals difficult to interpret.
Check Source Count
Count the source records that are inside scope.
Do not count headers as data.
Do not include intentionally excluded records unless the reconciliation explicitly tracks them.
The source count is the first control total.
Check Accepted Count
How many source records passed validation and were intended to load?
This can be lower than the source count.
The difference should be explained by rejects, exclusions or deduplication.
Check Rejected Count
Rejected records are part of reconciliation.
If 1,000 source rows produce 980 target rows and 20 documented rejects, the arithmetic can be coherent.
If 20 rows simply disappear, reconciliation has failed.
Check Inserted Count
How many new target records were created?
This matters when the import mixes inserts and updates.
Inserted count should not be assumed equal to accepted source count.
Check Updated Count
How many existing target records were changed?
An import expected to update 400 people and insert 600 should not report only a final total of 1,000.
The action split helps detect duplicate creation and failed matching.
Check Unchanged Count
Some accepted rows may match the target exactly.
If the importer intentionally skips no-op updates, record that state.
Otherwise an apparently low update count may be misread as missing work.
The Fundamental Count Equation
For a simple independent-row import, a useful reconciliation identity is: source in scope = rejected + excluded + accepted.
Accepted may then decompose into inserted + updated + unchanged, depending on workflow.
The exact equation changes by design.
Write the equation before checking the numbers.
Counts Can Pass While Identity Fails
Two students swap scores.
There are still two source rows and two target rows.
Counts match.
The import is wrong.
This is why identifier-level reconciliation matters.
Reconcile Stable Identifiers
For every accepted source entity, confirm that the expected target identity exists.
Where source and target use different keys, use the mapping crosswalk.
Unmatched keys and unexpected target keys should be investigated.
Reconcile Key Sets
A useful check compares the set of intended source keys with the set of affected target keys.
Set differences reveal missing and extra records even when total counts happen to match.
This is stronger than row count alone.
Reconcile Duplicates
Check whether one source key produced multiple target records.
Also check whether multiple source keys were collapsed into one target unintentionally.
Identity multiplicity is a separate reconciliation dimension.
Reconcile Critical Field Values
Compare important fields at record level.
- Identifiers.
- Dates.
- Amounts.
- Scores.
- Statuses.
- Categories.
- Relationship keys.
Not every low-risk text note needs full automated equality.
Prioritise decision-critical fields.
Reconcile Transformed Values
If the mapping changes representation, the comparison should reproduce the transformation.
Source percentage 85 may correctly correspond to target 0.85.
Literal equality would produce a false mismatch.
Reconciliation must understand the mapping.
Reconcile Derived Values
If target percentage is derived from raw score and max score, recompute independently where possible.
A check that simply reads the importer’s already-computed value can repeat the same bug.
Independent calculation is stronger evidence.
Reconcile Aggregates
Control totals can detect errors efficiently.
- Sum of transaction amounts.
- Sum of raw scores where meaningful.
- Count per class.
- Count per category.
- Minimum and maximum dates.
- Distinct identifier count.
Choose aggregates that are expected to remain invariant under the mapping.
Do Not Use Aggregates That Should Change
If currency conversion is part of the mapping, source and target amount totals may not be equal.
Instead, reconcile under the conversion rule or compare totals within each currency.
The check must reflect the transformation.
Reconcile Categories
Compare distribution by category before and after the mapping.
If 300 Sec 1 records suddenly become 30 after import, a category mapping may have failed even when total rows match.
Distribution checks are good at exposing systematic mapping errors.
Reconcile Null and Blank Patterns
Count missing values by important field.
A sudden drop in nulls can mean defaults filled them.
A sudden rise can mean a source column failed to map.
Missingness is a signal.
Reconcile Date Ranges
Compare earliest and latest dates.
A wrong date format can produce plausible records in the wrong months.
Range and distribution checks can reveal that the imported period shifted unexpectedly.
Reconcile Units and Scale
Compare known reference values.
If percentages should live between 0 and 1 in the target, a maximum of 98 suggests a scale failure.
If durations jump one hundredfold, unit conversion may be wrong.
Reconcile Relationship Counts
For relational data, check parent–child relationships.
How many result records lack a learner?
How many guardian links point to missing students?
How many attachments are orphaned?
Rows can exist and relationships can still fail.
Reconcile Deletes Separately
If the import deletes target records, treat deletion as a distinct control set.
Count intended deletions.
List keys.
Verify that deleted records truly belong to the import scope.
Deletion deserves higher scrutiny because reversal may be difficult.
Reconcile Partial Loads
A partial load should produce an explicit equation.
Example: 1,000 source rows = 960 accepted + 40 rejected; 960 accepted = 600 inserted + 350 updated + 10 unchanged.
Then reconcile the 40 rejects separately.
The target should not look ‘complete’ merely because the job ended.
Reconcile Retries
If the same batch is retried, compare batch identity and target effects.
A safe retry should not create a second set of records unless that is explicitly intended.
Unexpected growth after retry is a strong duplicate signal.
Reconcile Multiple Files
When an import consists of several files, reconcile each file and the combined state.
A missing file may be invisible if another file contains enough rows to make the overall total look plausible.
Reconcile Multiple Tables
Table-level counts are useful.
Cross-table relationships are essential.
A migration can preserve Student and Result row counts while losing the foreign-key relationship between them.
Reconcile Before Releasing Downstream Reports
Do not let dashboards, invoices, parent reports or automated actions consume newly imported data before the critical reconciliation checks are complete.
Release state should follow evidence.
Reconciliation and Staging
A staging layer can make source-to-target comparison easier because the transformed pre-commit state remains inspectable.
After final load, preserve enough batch metadata to trace target records back to their source.
Reconciliation and Provenance
A target record should ideally be traceable to source batch and source identity where consequence requires it.
This does not mean exposing sensitive source files broadly.
It means authorised investigators can reconstruct the transformation.
Reconciliation and Checksums
Checksums or hashes can help prove that files or selected representations have not changed.
They do not prove semantic correctness.
A source file containing wrong values can hash perfectly.
Use integrity checks alongside semantic reconciliation.
Sampling
Full record-level comparison may be expensive.
Sampling can provide useful evidence for lower-risk fields or very large datasets.
A sample should be selected deliberately and the conclusion limited to its scope.
Do not call a ten-row sample proof that ten million rows are perfect.
Risk-Based Reconciliation
Allocate stronger checks to fields and transformations with higher consequence.
Identity, money, eligibility, grades, publication state and deletion usually deserve stronger assurance than cosmetic notes.
Risk-based checking protects attention without pretending every field is equally important.
Independent Checks
The best reconciliation includes checks capable of disagreeing with the import logic.
Recompute totals from source independently.
Compare key sets using a separate query.
Ask a downstream report to reproduce a known control total.
Independent evidence reduces the chance of repeating the same defect.
Worked Example: Student Results
Source: 500 rows.
Validation: 490 accepted, 10 rejected.
Target action: 300 inserted, 180 updated, 10 unchanged.
The counts reconcile.
Next, compare the 490 source student-assessment keys with affected target keys.
Then independently recalculate percentages for a sample and compare category counts by class.
Each layer tests a different failure mode.
Worked Example: Swapped Scores
Two learner IDs are mismapped.
Source scores are 70 and 90.
Target contains 90 and 70.
Row count = 2.
Total score = 160.
Both aggregate checks pass.
Identifier-to-value reconciliation fails.
This demonstrates why several checks are needed.
Worked Example: Duplicate Retry
A 200-row import times out after committing.
The operator retries.
Target row count rises by 200.
Batch reconciliation reveals that the same source batch affected the target twice.
The repair belongs in safe retry or deduplication logic, not in manual deletion alone.
Worked Example: Wrong Month
August attendance is imported into the September table.
Every student record and percentage matches the source.
Record-level comparison passes against the wrong file.
Source-period reconciliation fails.
The batch identity must include business period.
Worked Example: Content Migration
A migration reports 2,000 posts imported.
Source also contains 2,000 posts.
Counts match.
Reconciliation then checks status distribution: 1,500 publish, 300 draft, 200 private in source; target has 2,000 publish.
The critical failure appears immediately.
Worked Example: Multilingual Names
Counts and identifiers match.
A sample reveals corrupted Chinese characters.
Encoding reconciliation identifies a field-quality failure that aggregate counts cannot see.
A Practical Reconciliation Pack
- Batch ID and source identity.
- Mapping version.
- Source count in scope.
- Rejected and excluded counts.
- Accepted count.
- Inserted, updated and unchanged counts.
- Unexpected target rows.
- Missing target rows.
- Key-set differences.
- Critical field mismatches.
- Control totals and category distributions.
- Relationship/orphan checks.
- Delete checks.
- Sample results.
- Known limitations.
- Final release decision.
The pack can be lightweight for small imports.
The important part is that the final decision is supported by visible evidence.
Reconciliation Status
Passed
Defined critical checks agree within accepted tolerances.
Passed with known exceptions
Differences are explained, owned and accepted under the correct authority.
Failed
A critical mismatch remains.
Incomplete
Required checks have not yet been performed.
Do not collapse incomplete into passed simply because no mismatch has been discovered yet.
Reconciliation Tolerances
Some comparisons permit rounding or expected transformation differences.
Define tolerance before seeing the result where possible.
Do not widen tolerance after a mismatch merely to make the check pass.
Reconciliation After Repair
Fixing one target record manually does not close the import defect.
Correct the source, mapping or validation process as appropriate.
Then rerun the relevant reconciliation.
The goal is a repeatable correct process.
Reconciliation Over Time
Recurring imports should trend their mismatch rate.
Repeated unknown categories or duplicate keys indicate an upstream process problem.
Reconciliation evidence can improve the source system, not only police the target.
The Reconciliation Test
- Can we identify the exact batch?
- Do source, reject, accepted and action counts balance?
- Do source and target key sets agree?
- Do critical values agree under the mapping?
- Do independent totals agree?
- Do category and null distributions look plausible?
- Do relationships remain intact?
- Were deletes and retries checked separately?
- Does the imported period match the intended period?
- Are remaining differences explained and owned?
- Is downstream release gated on the critical checks?
Across the eduKate Ecosystem
This pillar connects with How Checklist Verification Works, which owns the principle that checking must confirm reality, and How Review Closure Works, which owns turning findings into verified closure. How Status Change Reporting Works helps communicate partial loads and unresolved mismatches without hiding them.
Sources and Further Reading
AWS Database Migration Service — Data Validation
PostgreSQL Documentation — COPY
W3C — Model for Tabular Data and Metadata on the Web
The Deeper Principle: Trust the Target Only After It Can Disagree With the Importer
Reconciliation is independent evidence that the transformation produced the intended result.
Counts test completeness.
Keys test identity.
Values test meaning.
Totals test aggregates.
Relationships test structure.
No single check is enough.
Together they turn import completion into an auditable claim.
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 Validation Works | Reject Bad Rows Without Hiding Good Data or Real Errors
