AI Data Importers: How to Prevent Silent Data Loss

What the success signal actually measures

An exit code of 0 usually means the program ended without an unhandled failure. A log line like "complete" is often just that same idea in friendlier language. Neither one guarantees that every input record became an output record.

A loop can exit "cleanly" while still losing data:

for each row:
if row does not match expected shape:
continue
write mapped record

The program didn't crash. It did exactly what it was coded to do: ignore rows that don't match. Unless the skipped rows are counted or logged, the final summary can still claim success.

That's why "no errors" isn't enough to validate an importer. It tells you something about exceptions, not about completeness. A useful status needs to describe the data flow: how many rows were read, accepted, rejected, and written.

Why one missing section can evade ordinary testing

A test that uses the same representative row you gave the agent will confirm the happy path. It proves the code handles that row. It doesn't prove the spreadsheet's other sections have the same headers, optional fields, or value types.

This is as much a sampling problem as a coding problem. The code reflects a narrow view of the input, and the test repeats that same narrow view. Both can be "right" under their assumptions, while the real file contains more shapes than you tested.

Before trusting the importer, identify the distinct row types in the source. If there are three sections with different headers or structures, each one needs its own representative test. One example is evidence that one path works - not proof that the whole file will survive intact.

"Don't crash" is not a complete error-handling strategy

Continuing after a bad row can be a sensible operational decision. The real question is whether the importer tells you what it skipped and why.

There are two different behaviors:

• Silent skip: the row vanishes from the output and the report.
• Explicit rejection: the row is excluded, recorded with a reason, and included in the job totals.

The second approach lets a team recover. You can inspect rejected records, fix the source export, update the mapping, or decide that a particular variation is intentionally unsupported. The first approach leaves you guessing - often after the data has already been used downstream.

A good rule is: tolerate a bad row only when the rejection is observable. If the job can't explain what it rejected, it can't provide a trustworthy account of what happened.

The mismatch between exception checks and data checks

Many validations only ask whether an exception was thrown. That's worth checking, but it isn't the same thing as completeness. A parser can "handle" mismatches without throwing by filtering, defaulting, or skipping. You end up passing the exception check while failing the job's actual purpose.

For an importer, the purpose isn't "run without crashing." It's "process the intended input according to a defined mapping." That means you need assertions about outcomes:

• Did the job read the expected number of rows?
• Did each row become an accepted record or a documented rejection?
• Did accepted records actually reach the destination?
• Did the mapped values match the source for each supported row type?

This is the same separation you'd make in a payment workflow or a deployment workflow: "the process finished" is one signal; "the intended state was reached" is another. Treat data pipelines the same way.

Reconcile input and output counts

Count source rows and count accepted records. Then decide what the relationship should be. If every source row must become a database record, then success requires matching counts. If some rows can be rejected, the accounting still needs to balance:

rows_read = rows_accepted + rows_rejected

If writes can fail after parsing, track that too:

rows_read = rows_written + rows_rejected + rows_failed_to_write

The categories may vary by pipeline, but the principle holds: every input row needs a known outcome. A job that reads 1,200 rows and writes 800 shouldn't report an unqualified success. It should say what happened to the other 400 and return an exit status that matches the contract it chose.

Reconciliation doesn't replace data validation - something can go wrong in transformation and still produce the "right" counts. But reconciliation catches the specific failure mode where rows disappear without explanation, and it's usually cheap to implement.

Parse strictly by default, then allow known variation

A strict parser should reject unknown headers, missing required columns, and unexpected types instead of guessing or quietly discarding data. Strict doesn't mean every optional field must exist. It means the accepted variations are deliberate.

One practical approach is to split the schema into three buckets:

• Required fields: missing makes the row invalid.
• Optional fields: missing is allowed and has a defined meaning (for example, null).
• Known variations: documented differences by section, export version, or customer.

Handle known variations explicitly. If one section uses Customer Name and another uses Account Name, map both to the same internal field - and record which format you saw. Don't treat unfamiliar headers as "irrelevant" just because the parser wasn't told about them.

Strict parsing works best when you expect a stable source contract. If you're ingesting messy third-party data and genuinely expect variation, then yes - you may need some tolerance. But make the rejection rules and reporting explicit so "tolerant" doesn't become "invisible."

Record rejections with enough detail to recover

A rejection report (or structured error output) should include a stable row identifier or source location, the reason for rejection, and enough context to diagnose the issue. For spreadsheets, that often means sheet name, row number, and the unexpected header or value. Avoid dumping sensitive data into logs; use a safe identifier or a restricted output file when needed.

A useful rejection report answers three questions:

1. What input was rejected? Reference the original row reliably.
2. Why was it rejected? Mention the missing column, invalid type, or unsupported shape.
3. What should happen next? Tell you whether the source can be fixed, whether the mapping needs an update, or whether this variation is intentionally out of scope.

This turns a failed parse into an actual queue the team can work through, instead of a quiet loss that gets discovered later.

Test each distinct section and inspect representative outputs

For every distinct source section or row shape, pick a known sample and assert the expected result - not just that "something" was parsed. Check the fields that matter: identifiers, dates, amounts, and relationships. A test that only checks "record exists" can still miss a mapping bug that puts values into the wrong fields.

Also test negative cases: missing required headers, wrong value types, unexpected fields. The expected behavior should be clear. Should the importer reject the row, stop the job, or accept it with a documented default? Any of those can be reasonable depending on the situation - but an undocumented, silent skip is not.

Return a structured result instead of "done"

A completion summary that operators can act on might look like this:

{
"rows_read": 1200,
"rows_accepted": 1187,
"rows_rejected": 13,
"rows_written": 1187,
"status": "completed_with_rejections"
}

The summary should link to the rejection report and clearly separate parsing problems from database write failures. A single success: true throws away the exact information you need to decide what to do next.

Set the process exit code according to the job's contract. If any rejected row means the import is incomplete, return a failing exit code. If partial imports are allowed, return a distinct status - but make sure the caller can detect it. The core idea is simple: partial completion shouldn't be able to masquerade as complete success.

Treat unknown fields as signals, not clutter

Unknown keys aren't automatically "noise." They can be evidence that a server contract evolved, a source export changed, or the client's model is incomplete. Dropping them without trace removes the evidence you'd need to respond.

For an API client, you need an unknown-field policy. Depending on product requirements and compatibility expectations, that policy might be:

• Reject the response when an unknown field could affect correctness.
• Preserve unknown fields in an extension map for later use.
• Accept the response but emit a structured warning or metric.
• Ignore fields that are known to be harmless, while recording the schema version or event.

There isn't one rule that fits every API. Strict rejection can make a client fragile when servers add harmless fields. Blindly ignoring everything can hide a breaking change. Choose based on the contract and what missing data would cost - and make the choice visible to the consumer.

This is where API contract testing and data validation practices help: they move shape changes and expectations into the development workflow instead of letting them become "surprises" after deployment.

Separate partial success from full success

Some pipelines have to keep moving even when some records are invalid. That's reasonable - especially when one bad event shouldn't block unrelated work. But partial success should be represented as a distinct state, not collapsed into a generic "done."

For example, a batch job can finish while reporting that it still has rejected records. A message consumer can only acknowledge messages after routing invalid content to a dead-letter queue. An API client can return parsed data alongside a typed warning. In each case, the pipeline continues, but it doesn't pretend nothing was lost.

A useful operational question is: If this job drops 10% of its input tomorrow, what dashboard, log, or alert tells us? If the answer is "a user will report it," then the pipeline's success criteria are incomplete.