Yesterday's export does not match today's copy of yesterday

Somebody pulls the same report twice, a week apart, and gets 2 different answers for the same day. Before anyone touches the loader, establish which of 4 things happened: the store reopened an accounting period and edited posted rows, the export job ran before the business day closed, the transfer produced a partial file, or your loader ran twice and applied half of the second run. Three are the vendor's or the store's; 1 is yours. From a report they are indistinguishable.

The mistake almost everyone makes first is opening the database. Your tables are the output of the process under suspicion, so they cannot arbitrate. The raw files can — assuming you still hold them, which is the retention argument this page ends on.

Compare the files to each other before you open the database

This takes about 20 minutes for a week of drops and it settles the question in most incidents. Do it on the untouched files as they landed, not on anything a pipeline has rewritten.

1. Pull the last 7 drops for the affected object. Record for each: file name, arrival timestamp, byte size, line count and a SHA-256 of the whole file.
2. Look at byte size across the 7 first. A drop 40% smaller than its neighbours, or a file of 0 bytes, is a transfer problem and you can stop the analysis there.
3. Compute a control total per file — the sum of 1 monetary column and the count of distinct keys. 2 files with the same row count but different sums have had rows edited rather than added or removed.
4. Extract the key set from each file and diff consecutive pairs. Classify every key as added, disappeared, or present in both with a changed row hash. Those 3 counts are the diagnosis.
5. For the keys that changed, print the before-and-after row side by side and look at which columns moved. A status column moving is an edit; every column moving is usually a re-key or a merge.
6. Only now open your database, and check whether your stored copy matches the file you actually received. If it does not, the fault is yours and it is in the loader, not in the feed.

Four things that move a number after the fact

Ordered by how often each turns out to be the answer. The signature column is what the file-pair comparison shows you, and it is the fastest route from symptom to owner.

The last row is the one worth being honest about. If tonight's file and last night's file are byte-identical and your totals still moved, the feed is innocent. That is an idempotency defect, and the row-hash-and-watermark design in building a change stream from a full nightly file exists precisely so re-running the same file is a no-op.

Control totals when the file has no trailer row

Some drops carry a trailer row or a sidecar file with a record count. Assert it whenever it is there. It is absent often enough that you cannot depend on it, so compute your own and store it beside the file.

• Row count, excluding the header and any trailer. The cheapest check there is, and it catches truncation.
• Distinct key count. If it differs from the row count, the file contains duplicate keys, which is a separate and worse problem.
• The sum of 1 signed monetary column, at full precision. Do not round it: rounding hides exactly the small edits you are hunting.
• Minimum and maximum of the posting-date column. A file whose maximum date is 2 days old tells you the job did not run last night, whatever the file name says.
• A per-weekday baseline range, held for at least 8 weeks. Without it, a file 40% short and a genuinely quiet Tuesday look the same, and Monday drops covering a weekend are legitimately 3 times the size.

A reopened period is not a bug, and it is not your loader

Dealership accounting is allowed to reach backwards. A month gets reopened to correct a misposted deal, a repair order is reopened to add an operation performed but never billed, an internal charge is reclassified between departments. Each legitimately rewrites a row you already hold, and the extract restates it with no flag at all.

The design consequence: never treat a posted figure as immutable because it is old. Keep a per-row hash and re-compare on every load, hold the effective date separately from the load date, and make any report that quotes a historical month say which load it was computed from. That last habit is what stops the CRM and the DMS disagreeing on the same month from turning into a meeting rather than a lookup.

The short file that looks exactly like a quiet Tuesday

A partial file is the most dangerous of the 4, because the rows it does contain are perfectly valid. A loader with no size guard accepts it, and every key it omits looks like a record that no longer exists. If you also infer deletions from absence, a truncated file silently retires several hundred live records — the trap set out in the deal was voided and it is still open in your app.

• Quarantine any file more than 40% outside its weekday baseline. A held file can be replayed; a loaded one has to be undone.
• Check the last line ends with a line terminator. Truncation on transfer usually cuts mid-record, and a parser that tolerates a short last row will not tell you.
• Treat a 0-byte file as an incident, not as an empty day. It is nearly always an authentication or disk failure at the far end.
• Confirm the credential is still valid before blaming the network. A transfer running under a named person's login stops the day that person's password changes, which is the whole argument in the integration ran under a person's login.
• Never overwrite a landed file with a re-delivery. Store both, keyed by arrival time — a re-delivery that differs from the original is itself the finding.

You cannot reconstruct what a file said last week from a database that has been loading over itself all month. Either you kept the file, or the question has no answer.

Never delete a source file: the retention rule this all depends on

Every check on this page assumes you still hold the raw drops. Most integrations do not, because somebody set a 7-day lifecycle rule on the landing bucket to keep it tidy, and 7 days is shorter than the interval at which anybody notices a number has moved.

1. Write every file to immutable storage on arrival, before parsing, under a name carrying the object, the source date and the arrival timestamp.
2. Record the SHA-256, byte size, line count and control totals in a manifest table at the same moment. This is what you query 6 weeks later, not the files themselves.
3. Keep at least 13 months of raw files. Anything shorter cannot answer a question about the same month last year, which is the comparison dealer principals actually ask for.
4. Make the load path replayable from the archive with no manual database surgery — the landing-zone design in landing, validating and quarantining a scheduled export drop.
5. Alert on the manifest, not on the parse. A drop that never arrived produces no parse error at all, which is why who is watching the feed at 2am treats absence as the first failure mode.

The archive is also what makes the vendor conversation short. A message saying the file received at 02:14 on the 9th restated 63 rows from the 3rd, with hashes attached, gets a different response from one saying the numbers look wrong.

Building the manifest, the guards and the replay path is a contained piece of work that sits under internal tools and ops, and it is worth doing before the first report is published rather than after the first argument. The rest of this silo sits under DMS, CRM and shop-system integration, inside our automotive work.

• dms integration
• data feeds
• reconciliation
• file loading