DA 1.1: Select, prepare, extract, transform

Raw data is not ready data

A bakery has a sales table. One row says "test row". One row has no quantity. Nobody wants those in the weekly report.

Before analysis, an analyst follows a short routine: choose the data, check it, take it out, and change it. All the time, the rules about data apply.

Key words

Word	What it means	Example
Compliance	Following the law and the security rules that apply to the data.	Obeying data protection law
Select	To pick out which data you need.	Only the sales table
Prepare	To check the quality of the data and fix what you can.	Find rows with no quantity
Extract	To take the data you need out of its source.	Copy the rows into a new file
Transform	To do a calculation or change on the data.	Quantity times price gives the total
Query	A question you ask a database in a special language.	Show all sales of buns
SQL	The language used to write a query for a database.	select item from sales
Database	A store of organised data that a program can search.	The bakery sales table

The cells students fill in are left blank in this preview.

By the end of this lesson

Note

I can put select, prepare, extract and transform in order and do each one with a query.

Starter: what you already know

Have a go

Starter: what you already know

3 questions

Have a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score

Your answers are marked here and are not kept. A college account keeps every student’s marks.

What each step does

Compliance is not one step. It runs beside all of them. Ask: may I use this data, and am I keeping it safe?

Select the data that answers the question.
Prepare it: check the quality.
Extract it: take out what you need.
Transform it: calculate or change it.

Try it: select and prepare

Code: sql

-- Prepare: find the rows that have a problem.
select id, item, qty, note
from sales
where qty is null or note = 'test row';

-- Try it: add or id = 6 to see one more row.

Try it: extract and transform

Code: sql

-- Extract the good rows, then transform: total = qty times price.
select day, item, qty * price as total
from sales
where qty is not null and note <> 'test row'
order by day;

-- Try it: change the order to total desc.

Worked example: one row through the routine

Step	What we do	Row 3 now
Select	Pick the sales table	bun, 6, 2
Prepare	Check for no quantity and test rows	Good row
Extract	Keep the good rows	Row kept
Transform	6 times 2	Total is 12

The cells students fill in are left blank in this preview.

Raw data is not ready data

Key words

By the end of this lesson

Starter: what you already know

Starter: what you already know

What each step does

Try it: select and prepare

Try it: extract and transform

Worked example: one row through the routine

The lesson goes on