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
| Change | Example | SQL | Excel |
|---|---|---|---|
| Values | Trim a name, parse a date | TRIM, CAST | Helper column |
| Rows | Keep booked invoices, append a month | WHERE, UNION ALL | Filter, paste below |
| Columns | Add a region from another table | SELECT, JOIN | XLOOKUP |
| Grain | Total invoices per customer | GROUP BY | PivotTable |
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.
| invoice_id | exported_on | status | amount |
|---|---|---|---|
| INV-1008 | 2026-09-01 | pending | 540 |
| INV-1008 | 2026-10-01 | booked | 540 |
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.
| Step | What it changes |
|---|---|
| Stack the two exports | Rows |
| Parse amounts and dates | Values |
| Keep one row per invoice | Rows and grain |
| Keep booked invoices in Q3 | Rows |
| Add customer and region | Columns |
| Total by region | Rows and grain |
How many rows are left after each step?
| Step | Rows |
|---|---|
| Stack | 18 |
| Parse | 18 |
| One row per invoice | 16 |
| Booked in Q3 | 13 |
| Add customers | 13 |
| Total by region | 2 |
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.