Grain describes what one row represents in a dataset. A JOIN should be designed with a clear understanding of the grain of every table or result.
Many reporting errors occur because datasets with different levels of detail are JOINed without considering their grain.
Orders may have one row per order while OrderItems have multiple rows per order.
SELECT
o.OrderID,
SUM(oi.Quantity * oi.UnitPrice) AS OrderValue
FROM Orders o
INNER JOIN OrderItems oi
ON o.OrderID = oi.OrderID
GROUP BY
o.OrderID;Used in data warehouses, Power BI models, financial reporting, and advanced SQL analysis.
- Every table has an underlying grain.
- JOINs can change the grain of the result.
- Aggregations should match the intended grain.
- Different grains can cause duplicate totals.
- Always ask what one row represents.
What does grain mean in SQL analytics?
Grain describes the business meaning of one row in a dataset, such as one order or one order item.
Why is grain important before JOINing?
Different grains can cause row multiplication and incorrect aggregations if they are combined without careful design.