Skip to content

MODULE 1 · LESSON 3 · Data foundations

Rows, columns, records and fields

Identify the unit represented by a row and detect when a table mixes incompatible levels.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 2; revisit its worked examples if needed.

Definition: A record is one observation represented as a row. A field is an attribute represented as a column. The grain of a table is the precise level of detail represented by one row. A key is a field or combination that identifies a record.

Name the grain explicitly

“One row per customer” differs from “one row per customer purchase.” A customer can make several purchases, so counting purchase rows does not count unique customers. Before aggregation, complete the sentence: each row represents one ____. Add the relevant time and grouping boundaries. A website table may contain one date-channel pair, not one individual visitor.

Keep columns consistent

A well-formed field has the same meaning across rows. Do not place a monthly total in a column otherwise containing order-level values without marking it separately. If the table has a total row, including it in a sum double-counts the underlying observations. Similarly, a field called amount is ambiguous without a unit and definition. Revenue, quantity and duration are not interchangeable because they are all numeric.

Choose keys that match the records

A key may be a single invoice ID or a combination such as date and channel. A repeated date is expected when several channels are recorded each day. Repetition becomes a potential duplicate only relative to the intended grain and key. Examine the full record before deleting anything. A table’s physical layout can look tidy while its logical structure still permits counting mistakes.

Two worked examples

Website channels

Suppose 1 June has Search: 100 sessions and Email: 40 sessions. Two rows represent two date-channel observations. Their total is 140 sessions if the channel definitions are mutually exclusive for each session. Counting the rows gives two observations, not two sessions and not necessarily two people.

Orders and customers

Customer A makes orders R1 and R2; customer B makes order R3. There are three order records and two distinct customer identifiers. The order key is unique in this example while the customer field intentionally repeats. Deleting the second A record would remove a real order rather than clean a duplicate.

Where this becomes useful

A project lead receives a task table and a weekly team summary. Combining the rows directly would mix task-level observations with aggregates. Establish the grain of both sources before selecting a join or comparison.

Make an attempt

Open Table Explorer with Website traffic. Inspect the first six rows. Count records, distinct dates and channels separately. State the composite key you expect to be unique, then explain why dates alone repeat.

Open the accompanying interactive lab →

Misconception check

Claim: Repeated values mean duplicate records.

Inspect the correction

Values in non-key columns often repeat legitimately. Duplicate decisions depend on the grain, key and other fields.

Independent application

Design a six-row table for two fictional customers making several purchases. Include separate customer and order IDs. Compute the number of orders and unique customers, then explain which field identifies each type of entity.

Completion records practice and self-review. It is not certification of statistical competence.