Systems and records
Workflow data source reconciliation: tie rebuilt cases to system totals
By SourceX Editorial · Updated
10 sections
Short answer
Workflow data source reconciliation proves that cases rebuilt from event logs account for every in-scope record in the source system. Anchor on an exported list of source record IDs for the agreed scope and period, then bridge it line by line: extracted, keyed, linked, cases built, and each exclusion under a coded reason. The bridge should end at zero unexplained difference, traced record by record.
System export
Workflow data source
Key takeaways
- Reconcile cases, the business objects whose lifecycle is the task, not event rows or export rows.
- Anchor on an exported list of source record IDs with a timestamp; approximate API counts are only sanity checks.
- Fix the period rule first, because start, end and overlap rules give different case counts from the same log.
- Every difference between source and dataset goes to a coded exclusion or to the unexplained residual, which should be zero.
- The line where a difference appears points to its cause: clocks, keys or missing transitions.
What does workflow data source reconciliation prove?
It proves that the cases you rebuilt from event data account for every in-scope record in the system they came from, with nothing missing, nothing extra and every difference explained. Without it, a dataset of reconstructed workflows can look clean while silently dropping the cases that were hard to rebuild, which are usually the exceptions a buyer most wants.
Reconciliation is a different check from validating events. The IEEE Task Force on Process Mining's manifesto asks that event logs be trustworthy, meaning recorded events really happened, and complete, meaning no events are missing for a given scope. Event validation tests the first property. Source reconciliation tests the second, at the level that matters for a training set: the case.
The unit is the business object whose lifecycle is the task, such as a ticket, an order line or a claim, not the event row and not the export row. A single ticket can produce dozens of events and several export rows, so row counts that tie can still hide missing cases, and case counts can tie while event totals differ for legitimate reasons.
Which source total should anchor the bridge?
Anchor on a count you can reproduce exactly: a list of record IDs pulled from the source system for the agreed scope, with the time the list was taken. Every later line of the worksheet is then an ID-level comparison against that list, not a comparison of two numbers.
Avoid dashboard and API counts as the anchor unless the documentation says they are exact. Zendesk documents its Count Tickets endpoint as returning an approximate count, refreshed every 24 hours once the total exceeds 100,000. Jira Cloud's approximate-count search endpoint likewise returns an estimate and warns that recent updates may not show up. Both are fine for a sanity check and wrong as the reference a dataset is held to.
Write the scope definition next to the anchor in words a workflow owner would sign: which object type, which business units or queues, which date field defines the period, which statuses count, and whether records deleted or archived in the source are in or out. Most reconciliation arguments turn out to be scope arguments that nobody wrote down.
Why does the period rule change the case count before anything else?
Because a case has a start and an end, and a period boundary can cut through it. The same event data yields different case counts for the same quarter depending on whether a case belongs to the period it started in, the one it finished in, or any period it touched.
The open-source process mining library pm4py makes the choice explicit. Its time-range filter can keep only events inside the window, keep cases fully contained in it, keep cases that intersect it, or keep cases that start or complete in it. Run on one log, those modes return different case sets, and all of them are defensible. What is not defensible is leaving the mode implicit and comparing the result with a source count that used another rule.
Pick one rule, usually the date field the source total was counted on, and apply it to both sides. Then decide what happens to cases still open at the cutoff: exclude them under a coded reason, or keep them flagged as incomplete. Either works if the worksheet says which.
The case count and exclusion reconciliation worksheet
Fill one worksheet per scope and period. Each line is computed from ID lists, not from summary numbers, so any difference can be listed record by record. Lines B through H must each tie to the line above minus a coded difference; line I is whatever remains.
| Line | What it counts | How to compute it | Typical cause of a difference |
|---|---|---|---|
| A. Source total | In-scope record IDs in the source at the anchor time | ID list exported from the source with the scope filter and timestamp | Scope defined differently from the reconstruction |
| B. Extracted | IDs present in the raw extract | A minus IDs missing from the extract, each coded | Records deleted or archived after the anchor, API paging gaps, permission limits |
| C. Keyed | Extracted records whose case key resolves | B minus records with a null or unmatched key | Key held in a custom field, renumbering after migration |
| D. Linked | Keyed records with at least one event in the event extract | C minus records with no events | Audit retention shorter than the period, logging switched off for a queue |
| E. Cases built | Distinct cases after merge and split rules | D adjusted by merges and splits, each listed | Duplicate records merged, one record split into several cases |
| F. Rule exclusions | Cases removed by stated rules | Count per exclusion code | Open at cutoff, test records, below a minimum number of events |
| G. Rights and privacy exclusions | Cases removed for permission or privacy reasons | Count per exclusion code, reviewed separately | Restricted customer, contract carve-out, sensitive category |
| H. Expected dataset | Cases that should be in the dataset | E minus F minus G | Arithmetic only |
| I. Unexplained residual | Difference between H and the cases actually delivered | Anti-join of both ID lists in each direction | Clock or time zone handling, a filter applied twice, a code path that dropped cases |
Which exclusion codes keep the bridge honest?
A short, closed list, each with a definition and an owner, so that exclusions cannot quietly become a place to put whatever did not reconcile. Report every code's count even when it is zero; a zero tells the reader the rule was applied and found nothing.
At a glance
Which exclusion codes keep the bridge honest
Minimum-size rules deserve particular care. Libraries make them easy: pm4py's case-size filter keeps only cases whose number of events falls between a minimum and a maximum. That is a legitimate cleaning step, but the cases it drops are often the short, abandoned or immediately escalated ones, which are real business outcomes. Count them under their own code and look at a sample before accepting the rule.
- OUT_OF_PERIOD: the case falls outside the period under the stated period rule.
- OPEN_AT_CUTOFF: the case had not reached a terminal state at the cutoff.
- TEST_RECORD: sandbox, training or test data identified by a documented marker.
- MERGED_DUPLICATE: the record was merged into another case in the source, with the surviving ID recorded.
- DELETED_AFTER_ANCHOR: the record existed at the anchor time and was gone at extraction.
- NO_EVENTS: the record exists but no event history was available for it.
- BELOW_MIN_EVENTS: the case had fewer events than the stated minimum.
- RIGHTS_EXCLUDED and PRIVACY_EXCLUDED: removed after rights or privacy review, kept as separate codes so neither absorbs the other.
Where do clock offsets, key mismatches and missing transitions show up in the bridge?
Each of these defects moves cases to a predictable line, which is the practical value of the worksheet: the line where a difference appears tells you which defect to look for.
Clock and time zone problems appear in line I and in OUT_OF_PERIOD. A system that stores local time while the source count used UTC will shift cases created in the first or last hours of the period across the boundary. The signal is a small residual concentrated in records created near midnight on the first or last day.
Identifier mismatches appear in lines C and E. A key that changed format in a migration leaves records unkeyed; a key reused across companies or instances merges unrelated records into one case, which shows up as fewer cases than records with no matching merge in the source.
Missing transitions appear in OPEN_AT_CUTOFF. When a state change was made by a path that did not log, such as a bulk update or a direct database fix, the reconstructed case never reaches a terminal state, so it is excluded as open although the source shows it closed. Compare the reconstructed end state with the current state field in the source for every case in that code.
Object-centric data adds one more effect. The OCEL 2.0 specification models events that relate to several objects of different types. When such data is flattened to a single case notion, one event can be counted under several cases or under none, so event totals should be reconciled per object type rather than across the whole log.
Should you reconcile more than the case count?
Yes. Matching counts can hide offsetting errors: ten cases missing and ten cases that should not be there. Run the ID-level anti-join in both directions, then tie at least one breakdown and one measure.
The breakdown is the count per period and per terminal state, because errors cluster there. The measure is a sum that the source can also produce, such as total quantity on order lines or the number of reopen events, compared within a stated tolerance. Data build tools support this directly; dbt-utils, for example, provides an equal_rowcount test that can be grouped by columns such as period or state, and an equality test that compares two relations on a chosen subset of columns.
Keep the comparison queries with the worksheet. A buyer or reviewer should be able to rerun them on the delivered data and reach the same lines.
Worked scenario (illustrative): one quarter of service tickets
All figures here are invented to show the mechanics. A support team reconstructs one quarter of help desk tickets into cases, one ticket per case, with events from the ticket audit history. The scope is tickets created in the quarter in three queues, with creation time in UTC as the period field.
The source ID list taken at the anchor time holds 12,480 tickets. The extract holds 12,452; the 28 missing tickets were deleted in the source after the anchor and carry DELETED_AFTER_ANCHOR. All 12,452 have a ticket ID as key. 21 have no audit events because a queue's history was purged under an old retention setting, coded NO_EVENTS, leaving 12,431. 37 were merged into other tickets in the source, leaving 12,394 cases built. Rule exclusions remove 214 tickets still open at the cutoff and 61 test tickets from a sandbox queue, for an expected dataset of 12,119.
The delivered dataset holds 12,110 cases, an unexplained residual of 9. The anti-join shows all 9 were created in the first five hours of the quarter's first day. The reconstruction converted timestamps to the team's local time before applying the period rule, moving them into the previous quarter. After the conversion was fixed, the residual was zero and the worksheet was signed off with every line explained.
The scenario also shows what the worksheet does not settle. The 214 open tickets are excluded by rule, but they over-represent hard cases. Whether to deliver them later as a separate, flagged set is a decision for the workflow owner and the buyer, not a reconciliation question.
How much unexplained difference is acceptable, and how should uncertainty be shown?
Set the tolerance before you reconcile and make it zero for ID-level lines wherever the source allows an exact ID list. Tolerances belong on measures, such as quantity sums affected by rounding, and on sources that only offer approximate counts, where the tolerance should be stated next to the method.
Show uncertainty at two levels. At the dataset level, publish the worksheet with each line, each exclusion code count and the residual. At the case level, give every case a reconstruction confidence grade, for example whether all of its events came from one logged source, whether any transitions were inferred, and whether its key was matched exactly or by rule.
Stop and bring in the workflow owner when the residual is not zero and cannot be traced to a single cause, when one exclusion code grows sharply from one period to the next, or when the source total itself is disputed. Those are signs the scope or the process changed, and no amount of reconstruction logic will fix that.
What the worksheet means for a licensing conversation
A signed reconciliation worksheet answers the first question a buyer asks about workflow data: does this represent the business, or the part that was easy to extract? It also documents exactly which records left the scope and why, which is what a rights and privacy review needs to see.
When a business licenses reconciled workflow records to AI developers through SourceX, SourceX handles sourcing, rights review, contracting, data preparation, delivery and payment. Qualification is checked through a short assessment with no data upload, and the business approves the scope and signs before any case leaves its systems.
Frequently asked questions
Why not just compare row counts between the source and the export?
Row counts measure the wrong unit. One ticket or order can produce many export rows and many events, so equal row counts can hide missing cases, and unequal row counts can be perfectly correct. Reconcile distinct case IDs against an ID list from the source, then compare a breakdown and a measure.
Should cases still open at the cutoff be excluded?
Either choice is defensible if it is written down and coded. Excluding them keeps every delivered case complete but removes long-running and difficult cases; keeping them requires a flag so nobody treats an open case as a finished outcome. Many teams exclude them from one release and deliver them once they close.
What if the source system only gives an approximate count?
Use the approximate figure as a sanity check, not as the anchor. Build the anchor from an exported list of record IDs with a timestamp. If no ID list is possible, state the count method and its documented precision next to the tolerance you applied.
Who should sign off the reconciliation?
The person who built the reconstruction prepares it, and the workflow or system owner signs it, because they can confirm the scope definition, the exclusion rules and whether an unexplained difference reflects a real process change.
How often does reconciliation need to be rerun?
Every time the scope, the extract logic or the source configuration changes, and for every new period delivered. A worksheet that tied last quarter says nothing about this quarter if a queue, a field or a retention setting changed in between.
Sources
- The IEEE Task Force on Process Mining's manifesto sets quality criteria for event logs, including that they be trustworthy and complete for a given scope. Source
- Zendesk's Count Tickets endpoint returns an approximate count that is updated every 24 hours once it exceeds 100,000. Source
- Jira Cloud's approximate-count search endpoint returns an estimated count of issues matching a bounded JQL query, and recent updates might not be immediately visible. Source
- pm4py's time-range filter supports modes that keep events in the window, cases contained in it, cases intersecting it, and cases starting or completing in it; its case-size filter keeps cases with a number of events between a minimum and a maximum. Source
- The OCEL 2.0 specification defines object-centric event logs in which events relate to objects of several types, with qualified relationships. Source
- dbt-utils provides an equal_rowcount test that supports group_by_columns and an equality test that can compare a chosen subset of columns. Source
See if your company qualifies
A short company assessment. No data uploads are needed.