The deal was voided in the DMS and it is still open in your app
In short
A snapshot export carries the rows that still existed when the job ran, so a voided or purged record leaves no message behind — it just stops appearing. Detect it by diffing the key set inside a bounded window against the keys you hold, but only after proving the file is healthy, because a partial file produces the same disappearance for entirely different reasons.
Key takeaways
- An upsert-only loader can never remove anything, so a cancelled record survives in your system indefinitely.
- Absence is an inference, not a fact. Confirm the file is healthy before letting a disappearance change any row.
- Bound the comparison by a window — 90 days of activity — or a rolling export will retire your whole history.
- Only 2 of the 5 reasons a key vanishes are actual deletions. The other 3 are filters, schema and transfer faults.
- Store the tombstone rather than deleting: disappeared_at, last_seen_file, and the state the row was last in.
- Verify a sample against the source screen before the first automated retirement runs. One check, once, per object.
A deal is unwound at the store on the 12th. Your app still shows it open on the 20th, your follow-up agent is still chasing the customer about it, and the month-end count still includes it. Nothing is broken in the sense anyone can point at: the export simply stopped carrying that row, and a loader that only ever upserts what it sees has no mechanism for noticing that something is gone.
This is structural rather than accidental. A scheduled export is a photograph of the rows that survived to the moment the job ran. Deletion, voiding, merging and purging all produce the same observable — a key that used to be there and is not — and the file itself contains no field, flag or count that distinguishes them. Fixing it means inferring absence, which is doable and needs to be done carefully, because the same observable is also produced by a file that was cut short in transfer.
No field in the file says a row was deleted
Worth stating plainly before designing anything, because a surprising number of integrations are built on the hope that the vendor will add a delete flag. Most will not, and the ones that offer a status column usually populate it only for records the source system soft-deletes, which is a subset of the ones that vanish.
- A soft delete leaves the row present with a status value — voided, cancelled, inactive. These you can read directly, and they are the easy half.
- A hard delete removes the row. Nothing is emitted, no counter changes, and the file is one line shorter than it would have been.
- A merge is a hard delete of the losing record with no pointer to the survivor, so anything referencing the loser now dangles.
- A purge under a retention policy removes rows in bulk by age, which is why a quiet Sunday can retire several thousand keys at once and mean nothing.
- An entitlement change removes an object or a column set from your drop entirely, and every key in it disappears simultaneously.
Building the disappearance list over a bounded window
The comparison is a set difference, and the only genuinely difficult part is choosing the window. Compare the whole file against your whole table and a rolling export — one that only carries the last 90 days of activity — will look like it just deleted 4 years of history.
- Establish what the export actually covers. Ask the vendor or the store, and confirm it against the minimum and maximum dates in the file. All open records, or a rolling window? Which date drives the window — created, last-modified, or posted?
- Take the key set from tonight's file, restricted to the window the export covers.
- Take the key set you hold for the same window and the same object, restricted the same way. A mismatch in how the 2 sets are restricted is the most common false-positive source there is.
- Subtract. What remains is the candidate list: keys you hold that tonight's file does not carry.
- Size-check the candidate list before acting on it. Under about 1% of the window is a normal day; 10% is an incident and must not be applied automatically.
- Pull 10 candidates and look them up on the source system's own screen, with someone at the store. This one manual pass, done once per object, is what turns an inference into a rule you can automate.
- Only then write tombstones, and even then write them rather than deleting rows.
Prove the file is healthy before you believe a disappearance
This is the guard that separates a reconciliation you can automate from one that will eventually delete a quarter of your data overnight. A partial file, a filtered file and a re-shaped file all produce disappearances that are indistinguishable from deletions until you check the file itself.
- Row count within the baseline range for that weekday. A short file is disqualified from producing any retirement at all, whatever it appears to say.
- Column count and header names identical to the last known-good drop. A dropped or renamed key column turns every key into a non-match, which is the failure mode in the export gained a column overnight.
- Date coverage unchanged. If the maximum posting date is 2 days old, the job did not run and you are diffing against a stale copy.
- Distinct key count equal to row count. Duplicate keys mean the file's grain changed, and a grain change invalidates the comparison entirely.
- A cap on the retirement, always. No single run may retire more than a fixed share of the window — 2% is a reasonable starting point — and anything above it raises an alert instead.
Five reasons a key stops appearing, and only two are deletions
| Reason | How it presents | Confirming test | Correct response |
|---|---|---|---|
| Record voided or cancelled at the store | 1 or a few keys, scattered, on an otherwise healthy file | Look it up on the source screen with someone at the store | Tombstone it, and mark anything downstream that referenced it |
| Purged or archived by retention policy | A block of keys disappearing together, all older than a round age boundary | Sort the candidates by age; a hard cut-off is the signature | Tombstone, and stop treating your copy as replaceable from the feed |
| Rolling window moved forward | The oldest keys in your window vanish every night, at a steady rate | Compare the file's minimum date across 7 consecutive drops | Not a deletion. Fix the window, keep the history. |
| Partial or truncated file | Hundreds or thousands of keys at once, byte size well below baseline | Byte size and row count against the weekday baseline | Quarantine the file, retire nothing, replay when it is re-sent |
| Filter or entitlement change at the source | A whole category vanishes — one store, one department, one status | Group the candidates by store, department and status and look for a clean split | Ask the vendor what changed. Retire nothing until they answer. |
Rows 3 to 5 are the reason a naive absence rule is dangerous, and rows 4 and 5 are also why this reconciliation has to be able to say 'I do not know' rather than always producing a verdict. When something moved retroactively rather than vanishing, the sibling diagnosis is yesterday's export does not match today's copy of yesterday, which starts from the same raw-file comparison.
Tombstones: what the row has to carry once it is gone
Do not delete. A deleted row loses the fact that it once existed, which is exactly the information you spent this whole exercise recovering. Keep the row and add 4 fields.
| Field | What it holds | What it lets you answer |
|---|---|---|
| last_seen_file | The identifier of the last drop that carried this key | Which file to re-read when someone disputes the retirement |
| disappeared_at | The timestamp of the first healthy file that did not carry it | How long the record was stale in your system before you noticed |
| disappearance_reason | Inferred class: voided, purged, window, unknown | Whether this row should be excluded from reporting or merely flagged |
| last_known_state | The full row as last received, kept verbatim | What the record said, when a reappearance or an audit question arrives |
Handle reappearance explicitly, because it happens more than people expect: a record voided in error and reinstated, or a key that fell outside a rolling window and came back after new activity. A reappearing key must clear its tombstone and reactivate rather than being inserted as a new record, or you get 2 rows with the same source key and a reporting problem that outlives the incident.
An upsert-only loader is a one-way ratchet. Everything the source system has ever shown you accumulates, and nothing it withdraws is ever taken back.
What reporting does when there is no tombstone
The absence of a tombstone model does not present as missing data. It presents as numbers that are consistently slightly high, and it gets blamed on definitions long before anyone suspects the loader.
- Counts drift upward and never correct. Every cancelled deal and voided order stays in the denominator permanently, so a conversion rate falls a little each quarter for no visible reason.
- Automated outreach acts on records the store no longer has. A follow-up agent chasing a customer about a deal that was unwound is the most visible version of this, and it is why anything built under AI agents and automation needs the retirement path in place before the agent is switched on.
- Reconciliation meetings blame vocabulary. The gap looks exactly like a definition dispute, and gets treated as one — see the CRM says sixty-two and the DMS says fifty-eight for the separation between definition gaps and data gaps.
- Nobody can prove the retirement was right, later, unless the last known state was kept. That is the field people skip and regret.
None of this needs the vendor's cooperation, which is the practical point. Reconciliation by absence is entirely a read-side pattern, and it belongs in the same staged build as everything else you can do without write access — the sequence in the read-only-first playbook. The same reasoning applies to any feed you consume and do not control: a vehicle-data source also reports what is present and stays silent about what stopped, which is one of the criteria in building on the port or on the carmaker's feed.
It is also a fair question to put to anyone proposing to build on a nightly feed: how does your design learn that a record was withdrawn, and what stops a short file from retiring 5,000 rows? Choosing an AI development partner covers the wider version of that conversation. The rest of this silo sits under DMS, CRM and shop-system integration, inside our automotive work.
Frequently asked questions
Short answers to the follow-ups this page tends to raise.
Why are deleted records not in a DMS export?
Because the export is a snapshot of rows that still exist, not a log of changes. When a record is voided, merged or purged at the store it simply stops being included, and no field, flag or count in the file marks its departure. The only way to learn about it is to diff the keys in the file against the keys you hold.
How do I detect deletions in a snapshot feed safely?
Diff the key sets inside the window the export actually covers, but only after the file passes a health check on row count, header shape and date coverage. Cap how much any single run may retire — 2% of the window is a sane start — and verify a sample of candidates against the source system's own screen before you let the rule run unattended.
Should a vanished record be deleted from my database or marked?
Marked, always. Keep the row, add the date it disappeared, the last file that carried it, the inferred reason and the last known state. Deleting destroys the evidence that the record ever existed, which is precisely what you need when the store reinstates it or someone disputes the retirement 3 months later.
What is the difference between a soft delete and a hard delete here?
A soft delete leaves the row in the file with a status such as voided or inactive, so you can read the change directly. A hard delete removes the row entirely and produces only absence. Most dealership systems do both, depending on the object and on how far through its lifecycle the record was, so a build has to handle each.
- dms integration
- data feeds
- reconciliation
- tombstones
The work behind this page
Builds from our portfolio that this page draws on.
Read next
- The scheduled export: a snapshot of survivors, not a record of eventsA scheduled dealership export is a photograph of the rows that survived to the moment the job ran. That one property decides everything you can build on it, and everything you cannot.definition
- Yesterday's export does not match today's copy of yesterdayA figure that was right on Tuesday is different on Wednesday. Four things cause that, three of them at the vendor and one in your loader, and the only thing that separates them is the raw files themselves.diagnostic
- The CRM says sixty-two, the DMS says fifty-eight, and the manager trusts neitherA 4-unit gap between two systems almost never means 4 lost records. Reconcile one month at record level into four buckets and the shape of the result names the cause before anyone has to argue about it.diagnostic
- Data brokers in the dealership stack: who is actually sending you the fileBetween the dealership's system of record and your build sits a party with its own schema, its own cadence and an authorisation only the dealer can sign or revoke.definition
- Four times a repair-order number changes, and what to key on insteadAn RO number identifies a document at one store on one day. Four ordinary events renumber, duplicate or supersede it, and a fifth problem makes it repeat outright.definition
- The DMS and the CRM each believe they own the customerSample twenty customers who both bought and serviced, and count how many link on an identifier both systems actually store. The failure rate tells you whether this is drift or a missing design.diagnostic
Working on something in this space?
Tell us where you are in a sentence or two. We'll tell you honestly whether we're the right team, and what a sensible first slice of the work looks like.
Start the conversation