How to Validate Data Grain Before Aggregation

A perfectly valid formula can still produce the wrong metric when it is applied at the wrong data grain. Revenue may be counted twice, conversion rates may use mismatched populations, and customer totals may change depending on which table was joined first. Before trusting any aggregation, confirm what one row represents and whether the data still follows that definition.

What data grain means in practice

The grain of a dataset is the real-world entity or event represented by one row. Common examples include:

• One row per customer
• One row per order
• One row per order line
• One row per employee per month
• One row per account per day
• One row per support ticket status change

A table name rarely proves its grain. A file called orders.csv might contain one row per order, one row per product within each order, or multiple snapshots of every order. Those structures require different aggregation logic.

Suppose an analyst receives this table:

| order_id | product | quantity | order_total | |---|---|---:|---:| | A101 | Desk | 1 | 500 | | A101 | Chair | 2 | 500 | | A102 | Lamp | 1 | 80 |

The table is at order-line grain because order A101 appears once for each product. However, order_total is stored at order grain and repeated across the lines. Summing that column directly produces 1,080 rather than the correct order-level total of 580.

Nothing is wrong with the addition. The problem is that the measure and the rows have different grains.

Write the expected grain as a testable statement

Avoid descriptions such as “sales data” or “customer records.” Write a precise sentence:

Each row represents one product line within one order, uniquely identified by order_id and product.

That statement identifies three things:

1. The entity represented by a row
2. The columns expected to identify a row
3. The level at which measures can safely be aggregated

Next, translate the statement into a candidate business key. For an order-line table, the key might be (order_id, line_number). For a monthly employee snapshot, it might be (employee_id, reporting_month). A transaction identifier alone may be enough for a payment-events table.

Do not assume the available key is reliable merely because its column name includes “ID.” Test whether the proposed key is populated and unique. If repeated keys are allowed, document why. A ticket can legitimately appear many times in a status-history table, for example, but the combination of ticket ID and event timestamp may need to be unique.

Profiling the candidate key and its missing values is a useful first step. The profiling documentation can help analysts structure that initial examination.

Check grain before and after joins

Joins are a common point at which a known grain silently changes. Imagine an account table with one row per account and a contact table with several contacts per account. Joining them on account_id changes the result from one row per account to one row per account-contact combination.

That expansion may be intentional, but account-level measures will now repeat. If annual_contract_value comes from the account table, summing it after the join will overstate the total for accounts with multiple contacts.

Before a join, record:

• The expected grain of the left table
• The expected grain of the right table
• Whether the join key is unique on either side
• The relationship type: one-to-one, one-to-many or many-to-many
• The expected grain of the result

After the join, compare more than the total row count. Check the number of distinct business keys, the distribution of rows per key and whether nulls appeared in required fields. A stable row count does not prove a correct join: some rows may have multiplied while others failed to match.

Many-to-many joins deserve particular scrutiny. They can be valid, but they should be deliberate. If both tables contain several rows for the same key, joining them creates every matching combination. Aggregate one or both sides to the required grain first when those combinations have no analytical meaning.

Match every measure to its native grain

A dataset can contain columns defined at several levels. In the order-line example, quantity belongs to the line grain, while order_total belongs to the order grain. Customer region belongs to customer grain and may repeat across every order.

Classify important measures before calculating a report:

• Additive measures can be summed across the relevant rows, such as line quantity.
• Repeated higher-grain measures must be deduplicated or selected once per parent entity before aggregation.
• Rates and percentages usually need their components recomputed rather than averaged blindly.
• Snapshot measures require a defined date or period selection rule.
• Distinct counts require an explicit entity key and population.

For example, if store-level conversion rate is orders / visits, the overall conversion rate should normally be calculated as total orders divided by total visits. Averaging store percentages gives every store equal weight regardless of traffic.

Similarly, a daily account-balance table should not be summed across dates unless the question truly concerns balance-days. To report month-end balance, select the applicable snapshot for each account and month.

Diagnose duplicate keys instead of deleting them immediately

A failed uniqueness check tells you that the proposed grain does not hold. It does not tell you that the extra rows are disposable duplicates.

Group repeated keys and compare their non-key fields. The pattern usually points to one of several causes:

• Exact duplicate records introduced during export or concatenation
• Valid child records hidden behind an incomplete key
• Multiple versions or status changes for the same entity
• Overlapping snapshots from different extraction dates
• A join that multiplied rows
• Conflicting source records requiring business review

Consider two rows with the same employee ID but different effective dates. Deleting one as a duplicate would erase history. The actual grain may be one row per employee per effective period rather than one row per employee.

Keep exact duplication, key duplication and business duplication separate. Exact rows can sometimes be removed mechanically. Repeated keys need contextual investigation. Business duplicates—such as two customer IDs believed to refer to the same organization—usually require evidence beyond column equality.

The assessment documentation provides a useful reference when turning observations into explicit quality findings rather than making immediate destructive changes.

Build a grain validation checklist

Run the following checks before publishing an aggregation:

1. State what one source row represents.
2. Name the candidate key for that grain.
3. Count missing values in every key component.
4. Test the candidate key for uniqueness.
5. Inspect repeated keys and classify their cause.
6. Record the grain of every joined table.
7. Confirm expected relationship cardinality before each join.
8. Recheck row counts and distinct keys after joins.
9. Label measures that repeat from a higher grain.
10. Recompute ratios from compatible numerators and denominators.
11. Reconcile a small sample manually against source records.
12. Document any deduplication or pre-aggregation rule.

A useful reconciliation sample includes simple and difficult cases: an entity with one child row, one with several child rows, one unmatched key and one known repeated record. The goal is not merely to reproduce the final total. It is to prove that the calculation behaves correctly under different relationships.

You can adapt worked patterns from the EazyDataFix examples, but the grain statement and business key must come from the meaning of your own data.

Make grain part of the metric definition

Do not leave grain knowledge inside a notebook cell or an analyst’s memory. Include it in the metric specification, data contract or review notes. Record the source grain, aggregation grain, grouping columns, join assumptions, snapshot rule and treatment of repeated measures.

Before your next aggregation, pause and write one sentence describing a row. Test its candidate key, inspect exceptions and verify that each measure belongs at the level where it is being calculated. That short review prevents mathematically correct code from producing logically incorrect results.