Dataset anatomy

What you will be able to do

Before you count, join or clean anything, you must know what one row of each table means. If you get this wrong, every number after it is wrong, and no error tells you.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. Its first cell downloads the practice data and builds the database, so it needs nothing on your computer. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 40 minutes

What you need to know

• What a CSV file is, from Reading and writing files: CSV and JSON in Python for Practical AI Engineering. You do not need to know SQL.
• For the notebook: run the cells in order from a fresh start, as in Notebook discipline: Restart and run all.

What you need on your computer

• Nothing. The SQL boxes in this lesson run in your browser; the first run loads about 1 MB. The notebook in the box above runs in Colab or Kaggle. You build the database on your computer in Set up a practice database, the last lesson of this module.
• The data is made up for this course. There are no real people or companies in it.

The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

The help-desk export

Larkfield is a fictional online shop for home and garden products. When a customer has a problem, they contact the support team, and the help-desk system records a ticket. Larkfield wants to know, when a ticket arrives, if it will need a senior agent within 72 hours. Moving a ticket to a senior agent is an escalation. Then the shop can send those tickets to a senior agent early.

Priya, a data scientist, will train a model for this. She asks Mei, the data engineer, for the data. Mei gets an export: a copy of the help-desk data, written to files at one moment. The export has three CSV files:

File	What it holds
customers.csv	The customers who can contact support
tickets.csv	The tickets: who asked, when, through which channel, and about what
outcomes.csv	What happened to each ticket later: resolved, escalated, reopened

Mei loads the three files into one database: a file that holds several tables and answers questions about them. In this course, the database is SQLite, which keeps the whole database in one file. Python includes SQLite, so you need no server and no account.

Rows, columns and values on four tickets

Here are four tickets from the export, with only five of their columns:

ticket_id	customer_id	created_at	channel	priority
T-10001	C-0228	2026-01-05 07:30:24	email	2
T-10002	C-0207	2026-01-05 09:09:23	email	3
T-10003	C-0119	2026-01-05 13:22:27	Email	2
T-10064	NULL	2026-01-13 11:42:05	email	2

• A table is a set of data in rows and columns, with a name. This one is tickets.
• A row is one line of the table. Here, each row describes one ticket. (Some books say “record” or “tuple”. They mean the same as row.)
• A column has a name and holds one kind of fact for every row, for example channel.
• A value is what one row has in one column: the channel of T-10003 is Email.
• NULL means “no value”. It is not zero and not an empty text. T-10064 has no customer: it came from a guest who bought without an account.

Look at T-10003 again. Its channel is Email with a capital E. For a person, email and Email are the same. For a computer, they are two different values. You meet many quiet problems like this in the course. Here you only notice it.

Types, identifiers and timestamps

Each column has a type: the kind of value that it holds. SQLite uses a few simple types:

Type	Holds	Example column	Example value
TEXT	Letters, digits and symbols	channel	email
INTEGER	Whole numbers	priority	2
REAL	Numbers with a decimal part	order_value	47.25

The description of all tables, their columns and their types is the schema. The schema tells you what the data can hold. It does not tell you what the data actually holds. To know that, you look at the data.

Two kinds of column need special attention:

• An identifier is a value that names one thing, such as T-10001 for a ticket or C-0228 for a customer. You do not calculate with identifiers. You use them to find a thing, and to connect a row in one table to a row in another. customer_id in tickets says which customer opened the ticket.
• A timestamp says when something happened, for example created_at. SQLite has no special date type. The export writes timestamps as text in the form YYYY-MM-DD HH:MM:SS, so 2026-01-05 07:30:24 is 5 January 2026, at 7:30 in the morning. With this form, the order of the text is also the order in time. So you can sort and compare timestamps as text.

In AI work, timestamps matter for a second reason. They tell you when a value was known. A model that predicts at ticket creation can use only what was known at that moment. Module 5 builds on this.

Run your first query

A query is a question that you ask the database, written in SQL (Structured Query Language). The next box asks for all columns (*) of the tickets table, but only the first 5 rows (LIMIT 5).

Run SQL in your browser

Try it: the first five tickets

Press Run. You should see 5 rows and 11 columns. The first row is T-10001. Scroll to the right to see all columns. Then change 5 to 10, and run it again.

SQL query (you can change it)SELECT * FROM tickets LIMIT 5;

The table has 11 columns. Some are clear from their names, such as team and word_count. Others need the person who knows the help desk. Grace, the support lead, explains three of them:

• priority is set when the ticket arrives: 1 is the most urgent, 3 the least.
• priority_now is the priority today. It can change after creation.
• closed_at is when the ticket was closed. It is NULL when the ticket is still open.

Mei writes these meanings down. The next modules use them.

The question that comes first: what does one row mean?

The grain of a table is what one row represents. Ask this question before any count. Look at a small sample of each table.

customers — one row is one customer.

customer_id	segment	region	joined_on
C-0001	home	north	2024-10-18
C-0002	home	west	2023-11-17
C-0003	trade	west	2025-09-27

tickets — one row is one ticket. Customer C-0001 has three tickets, so C-0001 appears in three rows here:

ticket_id	customer_id	created_at	team
T-10270	C-0001	2026-02-08 14:01:04	warranty
T-10530	C-0001	2026-03-12 09:39:06	returns
T-10714	C-0001	2026-04-02 22:48:49	account

outcomes — one row is one event in the life of a ticket. Ticket T-10014 was resolved, then the customer reopened it, then it was resolved again. That is three events, so three rows:

outcome_id	ticket_id	recorded_at	status
O-00019	T-10014	2026-01-08 16:03:30	resolved
O-00028	T-10014	2026-01-09 17:26:31	reopened
O-00036	T-10014	2026-01-10 09:17:41	resolved

The three tables have three different grains. A count of rows in outcomes is a count of events, not of tickets. Mei writes the grain of each table at the top of her notes:

Table	One row means	Identifier of the row
customers	one customer	customer_id
tickets	one ticket	ticket_id
outcomes	one event in a ticket’s life	outcome_id

The snapshot

The help desk changes every minute. New tickets arrive, and open tickets close. The export is a snapshot: the state of the data at one fixed moment. Everything that happened after that moment is not in it. The database has a small table that records the moment.

Run SQL in your browser

Try it: when was the export made?

Run the query. You should see 3 rows. The row exported_at has the value 2026-07-01 06:00:00.

SQL query (you can change it)SELECT * FROM snapshot;

The snapshot explains some values. 12 tickets have NULL in closed_at. They were still open at 06:00 on 1 July. They can close later, but this export cannot know it. If you get a new export next month, the counts change. So write the snapshot time next to every number that you report: “1,417 ticket rows in the export of 2026-07-01 06:00”.

Worked example: how big is each table?

Priya asks Mei: “How much data is there?” Mei counts the rows of each table. COUNT(*) counts rows. Mei runs one query for each table:

SELECT COUNT(*) FROM customers;   -- 240
SELECT COUNT(*) FROM tickets;     -- 1417
SELECT COUNT(*) FROM outcomes;    -- 1874

She does not stop at the numbers. She reads each one against the grain:

• 240 customers. One row is one customer, so Larkfield has 240 customers in the export.
• 1,417 ticket rows. One row should be one ticket. Mei writes “ticket rows”, not “tickets”, until she checks that no ticket appears twice. You do that check in the next lesson.
• 1,874 outcome rows. One row is one event. There are more events than tickets, because one ticket can have several events. So 1,874 is not a number of tickets.

The case studies at the end of the course

The lessons use one dataset, Larkfield’s help-desk export, so that you can focus on each idea. The course ends with three case studies on real public data: a shop’s sales, city repair requests and bike-share trips. Each one takes a messy real export to tables, checks and a result that someone can act on, with every output explained.

Case studies in this course

At the end of the course, you use what you learn on a realistic project, from the data to the recommendation. Complete at least one for the certificate; the others are optional.

• Explain an online shop's salesTurn a real shop export into tables, reconcile revenue and returns, and answer planning questions with window functions and cohorts.
• Prepare a leak-free table for city repair requestsBuild a repeatable pipeline from a messy NYC 311 export to a documented, leak-free modelling table.
• Plan overnight bike rebalancingFind export defects and van moves in real bike-share trips, and build a station target from earlier days only.

You need to complete at least one case study for the certificate. Each one is also a portfolio piece. They use the whole course, so most learners start them after Module 6.

Guided practice: count and look

1. In the box below, run the query. It counts the rows of customers. You should see one row with the value 240.
2. Change customers to tickets. Before you run it, predict the number. Then run it. You should see 1417.
3. Change the table to outcomes, and run it. You should see 1874.
4. Change the query to SELECT * FROM outcomes LIMIT 6;, and run it. Look at the ticket_id column. The rows are in the order the events were recorded, not in ticket order: T-10002 comes first.

Run SQL in your browser

Try it: count the rows

Follow the steps above. Each run starts from a fresh copy of the database, so you cannot break it.

SQL query (you can change it)SELECT COUNT(*) FROM customers;

Your turn: one customer’s tickets

The query in the box shows every event of ticket T-10014. WHERE keeps only the rows where the condition is true. You learn WHERE properly in Module 2.

Change the query so that it shows every column of every ticket of customer C-0001. Predict the number of rows first.

Run SQL in your browser

Your turn: the tickets of C-0001

The check compares your result with ours. Text values need single quotes: 'C-0001'.

SQL query (you can change it)SELECT * FROM outcomes WHERE ticket_id = 'T-10014';

Show the example answer

SELECT *
FROM tickets
WHERE customer_id = 'C-0001';

The result has 3 rows: T-10270, T-10530 and T-10714. Two changes were needed: the table (tickets) and the column in the condition (customer_id). The grain tells you which table to use. “Tickets of a customer” are rows in tickets. “Events of a ticket” are rows in outcomes.

Common mistake

Counting events as tickets

Symptom: Omar, the product manager, asks how many tickets were resolved. Tomás runs this query and answers “1,533”:

SELECT COUNT(*)
FROM outcomes
WHERE status = 'resolved';

The query runs without an error. But 1,533 is more than the number of different tickets in the export (1,403). This number cannot be a count of tickets.

Cause: the grain of outcomes is one event, not one ticket. A ticket that was resolved, reopened and resolved again, like T-10014, has two resolved rows. The query counted events.

Fix: count each ticket once. COUNT(DISTINCT ticket_id) counts the different ticket IDs. You learn it in Module 2.

SELECT COUNT(DISTINCT ticket_id)
FROM outcomes
WHERE status = 'resolved';

The result is 1,396 tickets. Before you count, say the grain of the table aloud: “one row is one event”.

In AI work: the same mistake gives a training table with one row per event instead of one row per ticket. Tickets with many events then count several times, and the model learns more from them than from the others.

Check your understanding

The outcomes table has 1,874 rows. Does Larkfield have 1,874 tickets with an outcome? Explain with the grain.

Show the answer

No. The grain of outcomes is one event in a ticket’s life, not one ticket. A ticket can have several events, for example resolved, reopened and resolved again. So 1,874 is a number of events. The number of tickets with at least one event is smaller. You count it with COUNT(DISTINCT ticket_id).

Check your understanding

12 tickets have NULL in closed_at. Are they missing data that the export forgot? What do you write in a report about them?

Show the answer

They are not an error. NULL in closed_at means that the ticket was still open at the snapshot, 2026-07-01 06:00. The value can exist in a later export. In a report, write the snapshot time with the numbers: “12 tickets were open at the export of 2026-07-01 06:00”.

Check your understanding

A table has the columns order_id, line_number, product and quantity. Each order can contain several products. What is the grain?

Show the answer

One row is one product line in an order, not one order. The column line_number shows this: one order has line 1, line 2 and so on. So COUNT(*) counts order lines. To count orders, count the different values of order_id.

Recap

• A table has rows and columns. Each column has a type: TEXT, INTEGER or REAL in SQLite. NULL means “no value”.
• The schema says what the tables can hold. Only the data says what they actually hold.
• An identifier names one thing. A timestamp says when something happened, and when a value was known.
• The grain is what one row means: one customer, one ticket, or one event in a ticket’s life. Say it before you count.
• The export is a snapshot of 2026-07-01 06:00. Write the snapshot time next to your numbers.

Next, you connect the three tables with keys, and check if the keys in the export can be trusted.

Next: Relational concepts