Data grain and the data dictionary

Data grain is the precise meaning of one row in a table. A row might represent a complete order, one order line, a payment movement or a daily snapshot of an order whose status changes. Similar column names do not make these tables equivalent. Treating every line as a separate order inflates order counts. Summing the full order value repeated on each line inflates revenue. On receiving a table, ask which event or entity a row represents and which fields distinguish it from another row.

A key is a set of fields identifying a row at the chosen grain. Order number may suffice in an orders table, but an order-lines table may require both order number and line number. Test uniqueness rather than inferring it from a column name. A field labeled “identifier” is not proof of uniqueness. Two rows for the same customer are not necessarily duplicates if each represents a different transaction. Define grain before deleting duplicates: removing a legitimate row can understate revenue just as retaining a duplicate can overstate it.

Put a concrete grain statement in the data dictionary: “one row per sales line within an order, before product aggregation.” Record the key, each field's type, unit, meaning, permitted values and missing-value interpretation. The value 0012 may be an identifier whose leading zeros must be preserved, not a quantity for arithmetic. A date may refer to the local business day or a timestamp in a global time convention. Distinguish event time from warehouse arrival time. An older transaction arriving today can revise a historical report without representing new activity today.

Separate measures from descriptive attributes. Line quantity and line value are measures; product category and customer region help group them. Some attributes change over time. If a customer moves, joining all earlier transactions to the current address reallocates history. That may be intentional for a question about the present customer portfolio, but it is unsuitable for a question about where sales occurred at the time. Choose the intended meaning and document the time-based assignment rule before joining tables.

A system may contain both event and snapshot tables. The event table records changes such as opening, closing and reopening a ticket. The snapshot table records status at a specified moment, such as each ticket's state at the end of the day. Counting event rows does not give the number of tickets, and summing daily snapshots does not give distinct tickets over a month. Count events to examine change-processing workload, distinct identifiers to measure cases, or the latest snapshot to measure a closing balance. The question determines the appropriate choice.

Different aggregation levels require explicit treatment. Suppose targets are recorded by month and branch, while actual sales are recorded by day, product and branch. Joining the target to every actual-sales row and then summing it repeats the target many times. For a monthly comparison, first aggregate actual sales to month and branch, then join tables at that common level. Allocating a target across products requires a separate, agreed allocation rule, not an arbitrary division hidden inside a query.

Review the dictionary using at least three illustrative records: an ordinary record, one at a time boundary, and one containing a missing value or changing attribute. This does not mean three records are enough to test the entire table; they help expose ambiguous meanings. Follow with comprehensive uniqueness, range and coverage checks. If customer identifiers are missing on some orders, establish whether these are guest purchases or recording failures. Do not merge every missing identifier into one imaginary customer when counting purchasers.

Define currency and quantity units at field level. Quantity may represent packs in one source and individual items in another. Do not add them without a documented conversion factor. A value may include tax in one table and exclude it in another. An analyst cannot establish an accounting definition of revenue from a column label. Obtain the source definition and consult the responsible specialist. A limited operational comparison can use a consistently recorded value, provided its label explains what was measured and which meaning remains unresolved.

The dictionary is a working document, not merely a list of column names. Include the definition owner, verification date and changes to grain or meaning. When two files disagree, compare definitions before searching for arithmetic errors. A field whose meaning changes while its name remains constant constitutes a change to the data contract and requires review of affected analyses. Preserving a label does not automatically preserve its meaning.