Lesson 2 of 15 · Concept

What is data grain? How each step changes a table

Every transformation changes a table's values, its rows, its columns or its grain. Grain means what one row stands for, such as one invoice or one customer per month. Knowing which of the four a step changes tells you what to check afterward.

Four kinds of change

What each change does, with its usual tools
ChangeExampleSQLExcel
ValuesTrim a name, parse a dateTRIM, CASTHelper column
RowsKeep booked invoices, append a monthWHERE, UNION ALLFilter, paste below
ColumnsAdd a region from another tableSELECT, JOINXLOOKUP
GrainTotal invoices per customerGROUP BYPivotTable

Grain is the one that can't be undone. Three invoices of 120, 80 and 160, where customer A has the first and third, group into A 280 and B 80. You can now compare customers, but nothing in that 280 can give you back the two invoices. Keep the source table, and group as late as you can.

Sorting is the quietest change to rows. It moves them but changes no values and no count, though their order matters to rolling calculations.

What does one row stand for?

This course follows fictional Vandelay Coffee Supply, whose two reports disagree by €5,682. Its target is one row per booked Q3 invoice.

Vandelay's staff describe their exports as one row per invoice, but INV-1008 appears in both exports with different statuses.

The same invoice in two exports
invoice_idexported_onstatusamount
INV-10082026-09-01pending540
INV-10082026-10-01booked540

Neither row is a mistake, since each was true on the day of its export. Once the files are stacked, the real grain is one row per invoice per export, and anything that adds up that table counts this invoice twice. A sum is safe only when each row is one thing you mean to count.

Predict the row count at each step

Here is the path Vandelay's files take through this course. The first export has 10 rows and the second has 8, and two invoices are in both. One invoice is out of period, one is pending with no date, and one is canceled.

StepWhat it changes
Stack the two exportsRows
Parse amounts and datesValues
Keep one row per invoiceRows and grain
Keep booked invoices in Q3Rows
Add customer and regionColumns
Total by regionRows and grain
How many rows are left after each step?
StepRows
Stack18
Parse18
One row per invoice16
Booked in Q313
Add customers13
Total by region2

Stacking changes the grain's meaning without saying so. Only the third step restores one row per invoice.

Check row counts and totals

Most steps have a predictable count, which makes the count an easy test. A step that should keep rows steady but adds two has multiplied something. A filter that drops more than you can name has a rule wider than you thought.

Totals are a weaker test. The two INV-1008 rows carry the same amount, so a total can't tell you which one you kept, and two errors can cancel out.

So write three numbers beside any flow, where a person can read them: rows in, rows out and rows set aside. In SQL, that's SELECT COUNT(*) after each step. In Excel, it's =ROWS(Table1) on each table, or the count in the status bar. Keep the rows you set aside. They're your evidence.

Try it in Strata, Oplema's browser app for data prep, with no account needed.