Data Sources, Unit of Observation, Grain and the Data Dictionary
4 min read
Lesson objectives
- Identify the data source, unit of observation, grain and key before analysis.
- Explain how one-to-many relationships can distort counts and sums after joins.
- Create a reproducible data-dictionary definition and connect it to simple lineage.
Before joining data: what does one row represent?
A data source tells you where values came from, while the unit of observation tells you what one row represents. The grain may be one order, one order line, or one customer in one month. If different grains are combined without understanding keys and relationships, correct values can be duplicated and the final answer can be wrong even when the formulas themselves are valid.
| Concept | Practical question | Example |
|---|---|---|
| Source | Which system or file produced the data? | Order system |
| Unit of observation | What does one row represent? | One order |
| Grain | What is the finest stored level? | One row per order |
| Key | What identifies the row? | order_id |
| Relationship | Does one record map to one or many records? | One order to many lines |
The data dictionary: meaning before formula
A useful data dictionary is more than a list of column names. It states the field name, business meaning, data type, unit, allowed values or missing-value codes, source and refresh frequency when relevant to the decision. For example, customer_type might mean “new if this is the customer’s first completed order,” not “new if the account was created this month.” That difference changes results, so the definition must be fixed before comparison.
| Field | Meaning | Type/unit | Values or notes |
|---|---|---|---|
| order_id | Order identifier | text/identifier | unique in Orders |
| order_value | Order value before cancellation | numeric/accounting unit | do not sum after a join that duplicates orders |
| completion_status | Completion status | categorical | completed / cancelled / pending |
| customer_type | Customer type based on first completed order | categorical | new / returning |
Add enough lineage for the decision: source → extract → transformation → final table or metric. A foundational diploma does not require a complex lineage platform; you should simply be able to explain where a value came from and which rule changed it. If today’s report uses an extract that is three days old, that timing matters even when the columns are otherwise correct.
Practical rule: do not aggregate or compare until you can state in one sentence what a row represents. If processing changes the grain, record that change. This small habit prevents many common counting, summing and denominator errors after joins.
Lesson summary
Unit of observation, grain and keys determine what a row means and how it can be aggregated or joined. A data dictionary fixes definitions and rules, while lineage explains where a value came from and which transformations affected it.