Three sources, three shapes
Sales arrive as a spreadsheet export, a file from the website and a table from the till system.
Each writes dates and numbers its own way. To report on all three, you must blend them into one final format.
Key words
| Word | What it means |
|---|---|
| Dataset | a collection of related data about one subject |
| Blending | bringing data from different sources into one dataset |
| Format | the way data is stored, such as a sheet, JSON or a table |
| Data type | the kind of value a column holds |
| JSON | a text format of nested records with braces and quotes |
| Cast | change a value from one type to another |
| Final format | the shape the blended data must have at the end |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can blend datasets from different formats into one final format.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Identify and analyse each dataset
List each dataset with its format, its columns and the data type of each. Note the oddities: "N/A" in a number column, dates written day first, names in capitals.
This list is your map for the next step.
The three datasets side by side
| Source | Format | units arrives as | Date arrives as |
|---|---|---|---|
| Spreadsheet export | Text values from a sheet | Text, may say N/A | 3 April 2026, day first |
| Website | JSON file | Number or text | 2026-04-05 |
| Till system | Table | Whole number | Date |
The cells students fill in are left blank in this preview.
Prepare and standardise each one
Check consistency of data types: every units column must be a whole number. Check compatibility of formats: every date must be written the same way.
Use a cast to change type. Where the cast cannot work, such as "N/A", the value becomes missing. Then decide what to do about it.
Try it: standardise the sheet export
select product,
try_cast(units as integer) as units,
strptime(sold_on, '%d/%m/%Y')::date as sold_on
from from_sheet;
-- Try it: which row has a missing units value now?
Try it: read the JSON text
select j->>'product' as product,
try_cast(j->>'units' as integer) as units,
(j->>'sold_on')::date as sold_on
from (select unnest(from_json('[{"product":"helmet","units":3,"sold_on":"2026-04-05"},{"product":"bell","units":"4","sold_on":"2026-04-05"}]', '["JSON"]')) as j);
-- The 4 arrived as text. The cast makes it a number.