Systems and records
How to reconcile a dataset delivery against its approved scope
By SourceX Editorial · Updated
9 sections
Short answer
To reconcile a dataset delivery, take each line of the approved scope, count matching records in the source, subtract every documented exclusion and compare the result with what the delivered files contain. The delivery reconciles when every line ties or sits within an agreed tolerance, every difference has a written reason and nothing outside scope is present. Checksums prove files arrived intact, not that the scope is right.
Provenance trail
Dataset delivery
Key takeaways
- Decision rule: in-scope source count minus documented exclusions must equal the delivered count, per scope line.
- File checksums and byte counts prove integrity, not scope; reconcile records and fields separately.
- Count from the same source snapshot you extracted, and reconcile each record type against its own count.
- Unexplained variance, out-of-scope records and unapproved fields block the delivery, whatever their size.
- Keep the counting queries with the worksheet so any number can be reproduced on request.
How do you reconcile a dataset delivery against its approved scope?
You reconcile a dataset delivery by taking each line of the approved scope, counting the matching records in the source system, subtracting every documented exclusion with its reason, and comparing the result to what the delivered files actually contain. A delivery reconciles when every scope line ties exactly or within an agreed tolerance, every difference has a written reason, and nothing outside the approved scope is present.
The decision rule is short: expected delivered count equals in-scope source count minus documented exclusions, per scope line, and any variance that is not explained blocks sign-off. Everything else in this guide is about making the three terms in that equation trustworthy.
Reconciliation is a supplier's job before it is a buyer's. The supplier has the source system, knows why records were dropped and can fix a gap in hours. A buyer who finds the same gap during acceptance can only reject the delivery and wait.
Why is checking that the files arrived not enough?
File integrity checks prove the bytes you sent are the bytes that arrived; they say nothing about whether those bytes are the records the agreement covers. A delivery can pass every checksum and still contain the wrong years, an unapproved field or half the comments it should have. Reconciliation works on three layers, and each needs its own evidence.
The first layer is well served by existing standards. BagIt, the packaging format in RFC 8493, lists every payload file with a checksum and adds Payload-Oxum, an octet count and file count meant for quickly spotting an incomplete bag before full checksum validation. The Library of Congress bagit-python tool exposes that quick check through a fast validation flag. Croissant recommends a sha256 checksum on every file in a versioned dataset, and the Frictionless Data Resource specification has bytes and hash properties for the same purpose. GNU sha256sum with --check reads a checksum list and reports whether each named file matches.
The second and third layers, record counts and scope, are where most deliveries go wrong, and no packaging format does them for you.
Run the layers in order. There is no point counting records in a file that failed its checksum, and no point checking scope boundaries until the counts tie. Each layer that passes narrows where the next problem can hide, which keeps investigation time short when something does not match.
| Layer | Question it answers | Evidence | Typical tool |
|---|---|---|---|
| File integrity | Did every file arrive complete and unchanged? | File list, byte counts, sha256 per file | BagIt manifest, sha256sum --check, Croissant or Frictionless metadata |
| Record counts | Does each file hold the number of records the source says it should? | Source count queries, delivered row counts, exclusion log | SQL counts at source, Parquet footer row counts, lineage output statistics |
| Scope | Is every record and field inside the approved scope, and is everything approved present? | Approved scope table, min and max dates, distinct values, delivered column list | Profiling queries against the delivered files |
| Explanation | Is every difference written down with a reason? | Variance column with reasons and owners | The reconciliation worksheet below |
What should the scope to delivery reconciliation worksheet include?
The worksheet has one row per scope line, meaning each record type, system, period or business unit the agreement names, and a fixed set of input columns. Keep it as a spreadsheet or CSV in the delivery, next to the manifest. The input dictionary below defines each column so two people filling it in get the same numbers.
At a glance
What should the scope to delivery reconciliation worksheet include
Exclusions deserve the most care. Agree the allowed exclusion reasons before preparation starts, typically test records, spam, duplicates by a stated key, records outside scope by a stated filter, and records removed during de-identification. Then log every removal against one of those reasons as it happens. An exclusion reason invented after the counts fail to tie is a red flag to any reviewer, and it should be to you.
Use the same scope line identifiers in the worksheet, the exclusion log, the manifest and the delivery documentation. When identifiers match across documents, a buyer can trace any number back to its source in one step.
- Add a second, shorter table for fields: approved field list against delivered column list, one row per column, with the result approved, missing or not approved.
- Add a third check for boundaries: minimum and maximum of the scope date field in the delivered files, compared with the approved range.
- Sign the worksheet as a whole. The person signing should not be the person who ran the extraction.
| Input | Definition | Where it comes from | Rule |
|---|---|---|---|
| scope_line_id | Identifier for one line of the approved scope | The signed scope schedule | One row per line; never merge lines to make totals tie |
| approved_definition | The exact filter: system, record type, date field, date range, units | The signed scope schedule, copied verbatim | Name the date field and time zone used for the range |
| source_count | Records in the source matching the definition | A saved query against the source or a frozen extract | Record the query and the snapshot time |
| documented_exclusions | Records removed, one sub-line per reason | The exclusion log kept during preparation | Each reason must be one the agreement allows |
| expected_delivered | source_count minus documented_exclusions | Calculated | Never typed by hand |
| delivered_count | Records actually present in the delivered files | Counts read from the files as delivered | Count the final files, not the staging tables |
| variance | delivered_count minus expected_delivered | Calculated | Zero, or within the agreed tolerance |
| explanation | Why any variance exists | Investigation notes | A cause, not a guess; unknown means blocked |
| status | Reconciled, explained, or blocked | Reviewer | Only reconciled and explained rows may ship |
Which counting assumptions make a correct delivery look wrong?
The assumption that most often changes the decision is the snapshot time of the source count. A live system keeps changing while you extract, so a count taken on Monday and an extract run on Wednesday will not match even if nothing is wrong. Freeze the source, count from the same snapshot you extract from, or record both times and treat the drift as a separate, explained line.
The second is the unit of counting. One help desk ticket can become one row in a tickets file, many rows in a comments file and several files in an attachments folder. Reconcile each record type against its own source count, and never compare a row count in one file with a case count in another.
The third is the date boundary. If the scope says calendar year 2025 and the source stores timestamps in UTC while the agreement meant local business time, records near midnight on the first and last days move in or out of scope. State the date field and time zone in approved_definition and test the boundary explicitly.
Show uncertainty as its own number, never folded into a total. If a source count can only be estimated, for example because an archive is partially restored, record the known count, the estimated range for the rest and the method, and mark the row explained only if the buyer has agreed to receive an estimate.
- De-identification that drops whole records, such as tickets whose only content was personal data, is an exclusion and needs its own reason line.
- Deduplication changes counts; record the key and the rule used.
- Compression and format conversion change byte counts but must not change record counts; if they do, something was lost.
Worked scenario: reconciling a help desk delivery
This scenario is illustrative and every number in it is invented for the example. A company delivers six years of support tickets, their public comments and their attachments. The approved scope excludes internal notes and test tickets. Three scope lines reconcile or are explained; two block the release.
Read the table from left to right as the equation in the decision rule. Each documented exclusion is a reason the agreement allows, the expected figure is calculated rather than typed, and the status column applies the rule that unexplained means blocked.
- The comments line shows the most common pattern: a real, harmless exclusion that nobody wrote down. It becomes explained once the reason is in the exclusion log and the manifest is reissued.
- The attachments line blocks because 18 is small but unexplained. Small unexplained gaps often point to a systematic fault, such as a file name character the export tool could not handle.
- The fields line blocks for a different reason: the counts tie, but the delivery contains a field the agreement never covered. Remove the column, regenerate the checksums and rerun the worksheet.
- The date boundary line shows why an apparent overrun is not always a defect: once the time zone rule is written into the approved definition, the boundary tickets are in scope and the row is explained rather than blocked.
| Scope line | Source count | Documented exclusions | Expected vs delivered | Status |
|---|---|---|---|---|
| Tickets, created 2020 to 2025, US desk | 412,300 | Test tickets 1,850; spam 4,120; merge duplicates 2,330 | 404,000 expected, 404,000 delivered | Reconciled |
| Public comments on delivered tickets | 2,914,600 | Internal notes 611,200; comments on excluded tickets 21,400 | 2,282,000 expected, 2,281,640 delivered | Explained after review: 360 comments empty after redaction, added as an exclusion |
| Attachments on delivered tickets | 188,000 referenced | On excluded tickets 900; purged under retention 12,100 | 175,000 expected, 174,982 delivered | Blocked: 18 files unaccounted for |
| Approved fields for tickets | 24 approved fields | None | 24 expected, 25 delivered columns | Blocked: requester email column not approved |
| Date boundary, created_at | Range 2020-01-01 to 2025-12-31 local time | None | Max delivered value falls on 2026-01-01 in UTC | Explained: UTC storage; boundary tickets checked and in scope by local time |
Which out-of-scope or missing content opens cleanly in every tool?
The defects that change acceptance are scope defects that pass format checks: an unapproved column, records from a business unit that was carved out, a period that runs one day long, or a record type that is present but quietly incomplete. Every one of them opens cleanly in any tool, and every one of them means the buyer received something other than what was licensed.
Out-of-scope content is the most serious because it is a rights problem as well as a quality problem. Data outside the approved scope was never licensed, so it has to be removed before delivery, and the worksheet should show that it was looked for, not merely that it was not noticed.
Missing in-scope content is the next most serious. A buyer who agreed to six years and receives five and a half has a dataset with a silent gap that will distort any trend a model learns.
A third, subtler defect is a population that ties in total but differs in composition. If a support desk was reorganized midway through the period and one queue moved to another system, the ticket count can match the source while one product line disappears from the later years. Profile the distribution of key categorical fields by year as part of the scope layer, not only the totals.
How do record counts get read from the delivered files?
Read delivered counts from the final files, using the cheapest reliable method for each format. Parquet stores the number of rows in each file and each row group in the file footer metadata, so a count can be read without scanning the data. For CSV and JSON Lines, count records with a parser rather than counting lines, because quoted fields can contain line breaks.
If your pipeline emits lineage events, the OpenLineage output statistics facet records rowCount, size in bytes and fileCount for each dataset written. Those numbers are a useful cross-check, but they describe what the job wrote, which may be a staging table, so the authoritative delivered_count still comes from the files that ship.
Keep the counting queries with the worksheet. When a buyer questions a number, re-running a saved query settles it in minutes; reconstructing how someone counted six weeks ago does not.
Regenerate checksums last. Any change to a file after counting, such as removing a column, re-redacting a field or recompressing, changes its checksum and can change its counts. The order that avoids surprises is: finalize files, count records, fill the worksheet, then generate the manifest and checksums from exactly those files.
Which reconciliation failures should hold the delivery or change the scope?
Stop when a variance is unexplained after a reasonable investigation, when out-of-scope records or fields are found, or when the source count cannot be produced at all. None of those can be fixed by a note in the delivery.
Narrow the scope when one line cannot be reconciled but the rest can, for example an attachments archive that was partly lost in a migration. Delivering the lines that reconcile and amending the scope for the one that does not is better than shipping a line with a known hole. The scope change is approved by the data owner before delivery, not discovered by the buyer afterward.
Ask for review from privacy or legal colleagues when the variance involves personal data, records that may belong to a customer under contract, or records from a business that was sold or acquired during the covered period.
Escalate early rather than late. A scope amendment agreed a week before delivery is routine; the same amendment negotiated after a buyer rejects the delivery costs time on both sides and puts the rest of the relationship under strain.
Repeat the reconciliation for every recurring delivery. Incremental deliveries add a further check: records delivered in earlier increments must not reappear unless the agreement covers updates, and the running total across increments must reconcile to the source for the whole covered period.
Why the signed scope is the reference point in a SourceX deal
Reconciliation needs a fixed reference, and in a SourceX deal that reference exists before any files move: the business signs the agreement, including its scope, ahead of delivery. SourceX handles preparation and delivery as part of the licensing work, and very large datasets are not uploaded to it; they remain in the seller's storage or travel on encrypted drives, so the worksheet is reconciled against files wherever they sit. Companies can check whether they are a fit through a brief assessment that asks for no data.
Frequently asked questions
What is dataset delivery reconciliation?
It is the check that the delivered files contain exactly the records and fields in the approved scope. For each scope line, the in-scope source count minus documented exclusions must equal the delivered count, and anything outside scope must be absent.
Are checksums enough to reconcile a delivery?
No. Checksums and byte counts prove files arrived intact and unchanged. They do not show that the files hold the right records, the right period or only the approved fields, which is what scope reconciliation checks.
What variance is acceptable?
Zero unexplained variance is the safe default. Some agreements set a small tolerance for live systems, but every difference should still have a written reason, and a tolerance never covers out-of-scope records.
Who should sign the reconciliation worksheet?
Someone on the supplier side who did not run the extraction, so the counts get a second look. The buyer's acceptance team then reviews the same worksheet during acceptance testing.
What happens if records are lost in a migration before delivery?
Count what was lost if you can, record it as a documented gap, and ask the data owner to approve a narrower scope or a disclosed gap before delivery. Do not let the buyer discover it.
Sources
- RFC 8493 defines BagIt payload manifests with checksums and Payload-Oxum, an octet count and stream count for detecting incomplete bags before checksum validation. Source
- The Library of Congress bagit-python tool can quickly validate a bag by comparing its payload-oxum, the byte count and number of files, with a fast flag. Source
- Croissant 1.1 lets each FileObject carry a sha256 checksum and strongly recommends checksums for all files in versioned datasets. Source
- The Frictionless Data Resource specification defines bytes for file size and hash for the file's checksum. Source
- GNU sha256sum --check reads file names and checksums from a list and reports whether the checksums match the named files. Source
- Parquet file metadata and row group metadata each store num_rows, the number of rows. Source
- The OpenLineage output statistics facet records rowCount, size in bytes and fileCount written to a dataset. Source
See if your company qualifies
A short company assessment. No data uploads are needed.