Skip to content

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

This page covers dataset completeness acceptance test against source system counts.Illustrative

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 do whole workflow segments disappear between a system and its export?
Where the loss happensDocumented exampleWhat it removesControl total that exposes it
Archived records skipped by list endpointsZendesk documents that archived tickets are not included in the list tickets endpoint responses; tickets are usually archived about 120 days after they closeMost of the closed history, often the resolved cases a model learns fromRecords by final status and by year, from a source that includes archived records
Incremental export stopped earlyZendesk's incremental export says to keep paginating until end_of_stream is trueEverything after the last page fetched, usually the most recent periodRecords by month across the whole window
Permission-scoped counts and exportsSalesforce's Record Count resource counts only records visible to the calling userRecords owned by teams or territories the exporting user cannot seeCounts taken by an administrator, compared with counts taken by the export user
Search index lagJira Cloud search is eventually consistent by default and may return stale results shortly after writesRecent changes and late status transitionsCounts repeated after a settling period, or reconciled per issue
Line counts mistaken for record countsRFC 4180 allows line breaks inside quoted fields, so one record can span several linesNothing, but a line count overstates or misstates records, hiding a real gapRecords counted with a CSV parser, never with a line count
Child objects left out of the exportA parent-only export of tickets, opportunities or work orders without comments, events or attachmentsThe steps of the workflow, leaving only its start and endChild 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.

The completeness reconciliation against source system counts
Reconciliation lineSource control totalExport totalAllowed differenceEvidence if they differ
Parent records in scopeExact count by object at the cutoff, taken by an all-access userDistinct parent IDs parsed from the exportZero, after documented exclusionsExclusion rule and count per rule
Parents by created monthExact count per month over the full windowDistinct parent IDs per monthZero per monthPagination or date filter log for the affected months
Parents by final statusCount per status including archived and closedCount per status in the exportZero per statusArchive, deletion or retention policy that explains the gap
Child records per parent typeCount of comments, events or line items per objectChild rows linked to an exported parentZero orphans; totals equalList of orphan children and parents missing children
Parents with no child recordsShare of parents with zero children in the sourceSame share in the exportWithin a few points, set in advanceSample of empty parents opened in the source system
Attachments and filesCount of attachment references per parentFiles present and readable per parentZero missing referencesMissing-file list with reason (size limit, deleted, permission)
Outcome or resolution labelsCount per resolution code in the sourceCount per resolution code in the exportZero per codeMapping table for retired or renamed codes
Duplicate parent recordsNot applicable; source IDs are uniqueRows sharing a parent IDZero duplicatesDuplicate list and the de-duplication rule applied
Timestamps inside the windowWindow start and end in a stated time zoneRecords with timestamps outside the window or blankZero outside; blanks listedTime zone and format notes from the data dictionary
Schema versions over timeDates of field additions, retirements and renamesRecords per schema version in the exportEvery period maps to one documented versionSchema 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.

See if your company qualifies