Keep the Evidence, Not the Flood: Retention for a Failed Import

A failed import can write one error line per bad row. If the file has thousands of bad rows, the log becomes large, slow to read and a place where row content lingers longer than anyone intended. This article proposes bounding that output with sampling and a purge you can check. It is an engineering design, not a retention policy for any legal purpose, and every number below is a hypothetical placeholder.

The primary artifact: a sampling and purge verification log

The design has three tiers with different lifetimes.

Counters per error code. Tiny, no row content, kept longest.
A capped sample per error code, for example the first 5 occurrences. Content in samples should already be reduced to row number, column and error code.
Raw error lines. Held only while the incident is being worked, with a short hard limit, for example 48 hours.

Add a cap on distinct error codes, for example 10. Any further codes are counted under OTHER, so a new bug cannot create unbounded tiers.

Worked example (hypothetical)

An import of 12,000 rows fails. Errors by code:

DATE_FORMAT: 7,200
MISSING_REFERENCE: 1,500
BAD_NUMBER: 300

Total error lines: 7,200 + 1,500 + 300 = 9,000. The remaining 3,000 rows had no recorded error. Counters therefore sum to 9,000, which is the first consistency check.

Sample tier: 5 per code across 3 codes gives 15 samples. Every code has at least 5 occurrences, so all three are full. If BAD_NUMBER had only 3 occurrences, the sample count would be 13, and the log should say so rather than pad.

Purge schedule, counting from the failure time at day 0:

Raw lines (9,000): purge at 48 hours.
Samples (15): purge at day 7.
Counters (3 rows): keep with the import record until that record itself is retired.

The log you fill in

For each purge, record: incident id, tier, expected item count, count found before purge, count found after purge, who or what ran it, time, and result. For the raw tier the entries would be: expected 9,000, before 9,000, after 0, result verified. A before count that differs from the expected count is not a failure of the purge but is a finding. Investigate it.

Branches

If the raw tier is still needed after 48 hours, a person extends it once, with a reason and a new end time recorded. No silent extension.
If the purge reports success but the after count is above 0, mark it unverified.
If the purge call itself timed out and you do not know whether it executed, do not automatically fire it again. Read the current count first, then decide. A repeated destructive call against an uncertain result can remove more than intended.
If logs are copied elsewhere, such as to a search index, the verification must cover each copy. A purge of the primary store alone proves nothing about the copy.

Stop conditions

Stop the import's logging and keep only counters if the error rate exceeds a cap you set, for example 50 percent of rows after the first 1,000. Further lines add cost without adding information. Stop the purge and escalate if the after count cannot be read.

Acceptance tests (proposed, not performed)

Run a synthetic import with the three code counts above. Expect counters to sum to 9,000 and samples to equal 15.
Add an eleventh distinct code. Expect it under OTHER and the sample tier to stay bounded.
Run the raw purge. Expect after count 0 and a log entry with both counts.
Simulate a purge that returns a timeout. Expect no automatic second purge, and a log entry reading unverified.
Confirm that nothing in the sample tier contains cell values, only row, column and code.
Advance the clock to day 7 and confirm samples are removed and the entry written.

The point of the log is that deletion becomes something you can show happened, not something you assume.