Systems and records
Dataset completeness acceptance test against source system counts
By SourceX Editorial · Updated
9 sections
Short answer
A dataset completeness acceptance test compares the export with control totals taken from the source system at the same cutoff: records per object, per period, per status and child records per parent. Every difference must be explained by a documented rule such as archiving, deletion, permissions or filters. A file that opens proves only readability; the reconciliation proves the workflow is all there.
Checklist
Dataset completeness
Key takeaways
- Readable files prove nothing about completeness; reconcile against source system control totals.
- Take exact counts at a stated cutoff, by an all-access user; caches, estimates and ID gaps mislead.
- Reconcile by object, month, status and child records, not only in total.
- Explain every difference with a cause, a count and evidence until the residual is zero.
- Non-random gaps, such as missing closed cases, fail acceptance even when the total looks close.
- Counts prove presence, not per-case wholeness, rights or privacy.
How do you find missing workflow segments in files that open successfully?
Reconcile the export against control totals taken from the source system itself, at the same cutoff, broken down the way the workflow is broken down: records per object, per period, per status, and child records per parent. Then explain every difference with a written rule. A file that opens and parses proves the file is readable. Only the reconciliation shows that the closed cases, the comment threads and the older years are actually in it.
This matters because the most common completeness failures are silent. A help desk export can contain every open ticket and none of the closed ones from two years ago. A CRM extract can omit records the exporting user was not allowed to see. A paginated API pull can stop early and still write a perfectly valid file. None of these produces an error, a broken row or a parser warning.
The test described here is run by the supplier before a package is offered, and repeated by the buyer at acceptance. It complements, rather than replaces, checks on whether each individual case is internally complete and checks on whether the delivered files match their own manifest.
Where do whole workflow segments disappear between a system and its export?
Segments disappear at a handful of predictable points, and most of them are documented behavior of the source system rather than bugs. Knowing them tells you which control totals to take. The examples below come from the vendors' own documentation.
| Where the loss happens | Documented example | What it removes | Control total that exposes it |
|---|---|---|---|
| Archived records skipped by list endpoints | Zendesk documents that archived tickets are not included in the list tickets endpoint responses; tickets are usually archived about 120 days after they close | Most of the closed history, often the resolved cases a model learns from | Records by final status and by year, from a source that includes archived records |
| Incremental export stopped early | Zendesk's incremental export says to keep paginating until end_of_stream is true | Everything after the last page fetched, usually the most recent period | Records by month across the whole window |
| Permission-scoped counts and exports | Salesforce's Record Count resource counts only records visible to the calling user | Records owned by teams or territories the exporting user cannot see | Counts taken by an administrator, compared with counts taken by the export user |
| Search index lag | Jira Cloud search is eventually consistent by default and may return stale results shortly after writes | Recent changes and late status transitions | Counts repeated after a settling period, or reconciled per issue |
| Line counts mistaken for record counts | RFC 4180 allows line breaks inside quoted fields, so one record can span several lines | Nothing, but a line count overstates or misstates records, hiding a real gap | Records counted with a CSV parser, never with a line count |
| Child objects left out of the export | A parent-only export of tickets, opportunities or work orders without comments, events or attachments | The steps of the workflow, leaving only its start and end | Child records per parent, and parents with zero children, by type |
Which source counts can you trust as control totals?
Use an exact count from the system of record at a stated cutoff time, taken by a user who can see everything in scope. Convenient counts are often estimates, caches or filtered views, and a reconciliation against an estimate proves little.
Read the documentation for the count you plan to use. Salesforce describes the result of its REST Record Count resource as a cached snapshot that may not reflect the number of records at the time of the request, and points to a SOQL count query for an exact figure. Jira Cloud's approximate-count endpoint returns, as its name says, an estimated count for a bounded JQL query. Both are useful for sizing; neither should be the control total that decides acceptance.
Do not infer missing records from ID gaps either. Zendesk explains that ticket IDs are skipped when a ticket is deleted and also when a ticket creation attempt fails in the agent interface, a request form or the API, because the ID is consumed to keep numbering unique. A run of missing IDs is a prompt to check, not proof of loss.
Record when each count was taken. A count captured at 09:00 and an export finished at 17:00 will differ by whatever was created in between. Lineage metadata formats make this explicit: the OpenLineage data quality metrics facet carries a row count together with a capture timestamp, and states that all metrics in the facet reflect the dataset as of that time.
The completeness reconciliation against source system counts
Fill one line per control total. The allowed difference column is set before the test runs, in writing, so nobody can widen it after seeing the result. Any line outside its allowance goes to the exception log with a cause and evidence.
| Reconciliation line | Source control total | Export total | Allowed difference | Evidence if they differ |
|---|---|---|---|---|
| Parent records in scope | Exact count by object at the cutoff, taken by an all-access user | Distinct parent IDs parsed from the export | Zero, after documented exclusions | Exclusion rule and count per rule |
| Parents by created month | Exact count per month over the full window | Distinct parent IDs per month | Zero per month | Pagination or date filter log for the affected months |
| Parents by final status | Count per status including archived and closed | Count per status in the export | Zero per status | Archive, deletion or retention policy that explains the gap |
| Child records per parent type | Count of comments, events or line items per object | Child rows linked to an exported parent | Zero orphans; totals equal | List of orphan children and parents missing children |
| Parents with no child records | Share of parents with zero children in the source | Same share in the export | Within a few points, set in advance | Sample of empty parents opened in the source system |
| Attachments and files | Count of attachment references per parent | Files present and readable per parent | Zero missing references | Missing-file list with reason (size limit, deleted, permission) |
| Outcome or resolution labels | Count per resolution code in the source | Count per resolution code in the export | Zero per code | Mapping table for retired or renamed codes |
| Duplicate parent records | Not applicable; source IDs are unique | Rows sharing a parent ID | Zero duplicates | Duplicate list and the de-duplication rule applied |
| Timestamps inside the window | Window start and end in a stated time zone | Records with timestamps outside the window or blank | Zero outside; blanks listed | Time zone and format notes from the data dictionary |
| Schema versions over time | Dates of field additions, retirements and renames | Records per schema version in the export | Every period maps to one documented version | Schema history with change dates |
How do you turn a count mismatch into an explained difference?
Every difference gets a cause, a count and a piece of evidence, and the counts in the exception log must add up to the gap. If they do not, the gap is not explained yet, however plausible the stated reasons sound.
Automate the comparison so it runs the same way on every refresh. In a dbt project, the dbt-utils package provides an equal_rowcount test that asserts two relations have the same number of rows, a fewer_rows_than test for expected reductions, and a group_by_columns option so the counts must match within each group rather than only in total. Its relationships_where test checks referential integrity with a filter, which suits the orphan-child check while excluding records created in the last few hours.
Keep the exception log next to the package, not in an email thread. A buyer who receives a package with a gap and a clear log can decide whether the gap matters for the task. A buyer who discovers an unexplained gap will usually question everything else in the package.
- Cause: the specific rule or system behavior, such as archived tickets excluded by the export method.
- Count: how many records the cause accounts for, per affected line.
- Evidence: the policy, log, query or vendor documentation that shows the cause.
- Remedy: re-export, document as an exclusion, or withhold the affected segment.
- Residual: whatever the listed causes do not explain, which must be zero before acceptance.
Which missing segment changes the buyer's acceptance decision even when every file opens?
A gap that is not random is the one that changes the decision. Losing a small fraction of records evenly across the window rarely matters. Losing all closed cases, all records from one region, all records from before a system migration, or every case that ended in escalation skews what a model learns, even when the total looks close.
Read the reconciliation by segment before reading it in total. A package can be within a tolerable gap overall while one status or one year is almost empty. If that segment is the one the buyer's task depends on, such as resolved cases for a support agent or completed jobs for a field service model, the package fails acceptance regardless of the headline figure.
State the segments the task depends on before running the test, using the buyer's task description. That turns a debatable judgment into a line in the reconciliation with a pass or fail.
Worked example (illustrative): a help desk export that lost its closed history
All figures here are illustrative. A software company prepares three years of support tickets. The export, built on the list tickets endpoint, produces valid JSON files that load cleanly and pass schema checks.
The reconciliation tells a different story. The source shows about 48,000 tickets created in the window; the export holds about 26,000. By final status, open and pending tickets match exactly, while closed tickets older than roughly four months are almost absent. By month, the gap starts at the archive threshold and runs back to the start of the window. The cause is documented behavior: archived tickets are not returned by the list endpoint.
The supplier re-exports through the cursor-based incremental export, paginating until end_of_stream is true, and the closed history returns. Child counts then reveal a second gap: comments for tickets in the first year are missing because the comment pull ran with a later start date. That is re-run too.
The exception: about 300 tickets that were deleted and later scrubbed cannot be recovered from any endpoint. They go into the exception log as a documented exclusion with their count by month, and the residual reaches zero.
What a count reconciliation cannot prove
Matching counts show that the records are present, not that each one is whole. A ticket can be counted and still have a truncated description, a missing final reply or an outcome field left at a placeholder value. Per-case completeness needs its own checks on the steps inside each case.
Counts also say nothing about rights or privacy. A complete export may include records the supplier cannot license or fields that must be removed, and de-identification can legitimately remove content after the reconciliation. When that happens, reconcile twice: once from source to raw export, and once from raw export to prepared package, with every removal logged.
Bring in a data engineer who knows the source system when the gap cannot be explained from documentation, when the system went through a migration inside the window, or when counts disagree between two exact methods. Those cases usually need direct database or administrator access that a standard export does not provide.
Offering a reconciled package for licensing
A supplier who can show source counts, export counts and an exception log with a zero residual answers the first question a buyer's data team will ask. A reconciled package can then go through SourceX for sourcing, rights review, contracting, preparation, delivery and payment, licensed to AI developers within the scope the company approves.
Nothing has to be uploaded to find out whether a company qualifies; the first step is a short company assessment. Very large packages are not moved to SourceX storage either; a seller keeps them on its own systems or sends them on encrypted drives.
Frequently asked questions
What is a dataset completeness acceptance test?
It is a check that a delivered dataset contains everything in its declared scope, done by comparing export totals with control totals from the source system at the same cutoff. The comparison runs by object, period, status and child record type, and every difference must be explained by a documented rule. It is separate from checks on file format and from checks inside each record.
Why is a row count not enough to prove completeness?
A total can match while one year or one status is missing and another is duplicated. Line counts can also mislead, because CSV fields may legally contain line breaks. Count distinct record IDs with a parser and compare them by segment, not only in total.
Can I use ID gaps to find missing records?
Only as a prompt for investigation. Systems such as Zendesk skip IDs when a record is deleted and when a creation attempt fails, so a gap does not prove a record was lost. Check gaps against an event or audit log before treating them as missing data.
Who should run the reconciliation, the supplier or the buyer?
Both. The supplier runs it before offering the package, because only the supplier can take control totals from the source system. The buyer repeats the export-side counts at acceptance and checks that the supplier's figures and exception log add up.
How often should the reconciliation be repeated?
Run it on every delivery and every refresh, with the same lines and allowances. Automating it, for example with row count and grouped count tests in a dbt project, keeps a later delivery from silently dropping a segment that the first one contained.
Sources
- Zendesk's Tickets API reference states that archived tickets are not included in the list tickets endpoint responses. Source
- Zendesk automatically archives tickets, in most cases 120 days after they are closed, and incremental exports return archived tickets. Source
- Zendesk's incremental export guide says to keep paginating while end_of_stream is false and stop when it is true. Source
- Zendesk skips ticket IDs for deleted tickets and for failed ticket creation attempts. Source
- Salesforce's Record Count resource returns a cached snapshot that counts only records visible to the user; SOQL count gives an exact figure. Source
- Jira Cloud search does not provide read-after-write consistency by default and may return stale data after writes. Source
- Jira Cloud's approximate-count endpoint returns an estimated count of issues for bounded JQL. Source
- RFC 4180 says fields containing line breaks, double quotes and commas should be enclosed in double quotes. Source
- dbt-utils provides equal_rowcount, fewer_rows_than and relationships_where tests, with group_by_columns for per-group counts. Source
- The OpenLineage data quality metrics facet records rowCount with a captureDate that the metrics reflect. Source
See if your company qualifies
A short company assessment. No data uploads are needed.