One row, one stated meaning
The grain of a dataset describes what one row represents. It may be one request at creation, one item inside a request, one event in its history, or one snapshot of its state at a particular time. Write that statement before calculating a total.
Consider an illustrative request called R-21. It contains two items and has three recorded status changes. A request table has one row for R-21, an item table has two, and an event table has three. Each table can be correct while producing a different count.
Why a join can multiply a total
If the two item rows are joined to all three events using only the request identifier, the result has six rows. Counting those rows as requests reports six requests where only one exists. Summing a request-level value across the same joined table can inflate it in the same way.
The fix depends on the question. To count requests, use a request-level dataset or count distinct request identifiers under a clearly stated rule. To compare item values with the latest request status, first select the appropriate status record for each request. Do not remove duplicates blindly: repeated rows may represent real, different events.
Test the expected relationships
Describe each relationship as one-to-one, one-to-many, or many-to-many. Then check the keys that should support that description. Are identifiers unique where expected? Are there blank keys? Do some records have no match? Does the relationship change when the reporting window changes?
Check both sides of a join. A source can contain legitimate records that have not yet reached the other system. Excluding them silently may make a report look cleaner while changing its population. Decide how unmatched records are displayed or counted.
Write a short reconciliation note
Use a small sample whose history can be followed. Record row counts before and after the join, distinct identifiers, unmatched records, and the expected result for each example. Explain any aggregation or event-selection step.
A useful note might say: “One row per request; latest status selected by event timestamp; ties retained for review; requests without events shown as status unavailable.” That statement is more informative than simply saying the data was cleaned. It makes the remaining limitations available to the next person.
Take this into the next review
Ask three questions before approving a count: What is the row unit? Can the transformation create more than one row per unit? Which source records are excluded by the join? Answering them often reveals a definition issue before it becomes a chart issue.
All identifiers and quantities in this guide are fictional teaching examples. The technique applies to many operational datasets, but the correct grain and key rules must be established from the actual sources.