Everything that happens between a messy export and a dashboard: how data is loaded, checked, cleaned, reshaped, modelled, stored and governed. Animated, mostly on a real file.
Real rows. Customer numbers are never shown here, only whether there is one.
Profile: how healthy is each column?
InvoiceNo
25,900 distinct
StockCode
4,070 distinct
Description
0.3% null
Quantity
722 distinct
InvoiceDate
23,260 distinct
UnitPrice
1,630 distinct
CustomerID
24.9% null
Country
38 distinct
Green = filled, red = empty. Quantity runs from -80,995 to 80,995; prices from -11,062.06 to 38,970.
Test: rules the data must pass
not_null(InvoiceNo)✓ pass
not_null(Description)✗ 1,454 rows
unique(all columns)✗ 5,268 rows
Quantity > 0✗ 10,624 rows
UnitPrice > 0✗ 2,517 rows
Country is not empty✓ pass
Run on the raw file. Failing tests stop the pipeline or send rows to a quarantine table.
Clean: one written rule at a time (silver)
Raw rows541,909
Exact duplicates removed536,641 −5,268
Cancellations removed527,390 −9,251
Zero or negative quantity / price removed524,878 −2,512
Missing descriptions removed524,878 ±0
Postage, fees and adjustments removed522,568 −2,310
The missing-description rule removed nothing more: every remaining row without a description also had a zero price or quantity, so the rule before it had already caught it.
Standardise: one name per product
FLOWER FAIRY,5 SUMMER B'DRAW LINERS25 rows
FLOWER FAIRY 5 DRAWER LINERS21 rows
FLOWER FAIRY 5 SUMMER DRAW LINERS1 rows
↓
17107D → one description, kept in a mapping table
The raw file has 4,223 different descriptions for 4,070 product codes.
Model: split into facts and dimensions
Serve: small, fast tables for dashboards (gold)
WHERE month = '2011-11' → 1 of 13 partitions read
10-12
11-01
11-02
11-03
11-04
11-05
11-06
11-07
11-08
11-09
11-10
11-11
11-12
The table is stored month by month, so a one-month query skips the other folders entirely.
Automate and document
Shapes of data
The same business, stored in different shapes.
Transactional
Analytical
Specialised
Flat file (raw export)
real data
One wide table, one row per line of every order, exactly as the shop's system exported it. Easy to open, painful to analyse: descriptions repeat on every row, and cancellations and postage sit next to real products.
UCI Online Retail (CC BY 4.0): rows copied from the file, customer IDs left out
541,909 rows like these
InvoiceNo
StockCode
Description
Qty
InvoiceDate
Price
Country
536365
85123A
White Hanging Heart T-Light Holder
6
2010-12-01 08:26
2.55
United Kingdom
536365
71053
White Metal Lantern
6
2010-12-01 08:26
3.39
United Kingdom
536365
84406B
Cream Cupid Hearts Coat Hanger
8
2010-12-01 08:26
2.75
United Kingdom
536365
84029G
Knitted Union Flag Hot Water Bottle
6
2010-12-01 08:26
3.39
United Kingdom
C536379
D
Discount
-1
2010-12-01 09:41
27.5
United Kingdom
536370
POST
Postage
3
2010-12-01 08:45
18
France
Best for
Moving data between systems
A first look in a spreadsheet
Watch out
Repeated text wastes space and drifts (same product, two spellings)
No rules: a cancellation (C536379) and postage look like sales
SQL · Orders per month
SELECT strftime(InvoiceDate, '%Y-%m') AS month,
COUNT(DISTINCT InvoiceNo) AS orders
FROM online_retail
WHERE InvoiceNo NOT LIKE 'C%' -- cleaning rules live in every query
AND Quantity > 0 AND UnitPrice > 0
AND StockCode NOT IN ('POST', 'D', 'M')
GROUP BY month ORDER BY month;
Same question, different model
“Revenue per product per month”, four ways.
The shape of the data decides how much work every single query has to do.
Flat file (raw export)
0 joins · cleaning in the query
SELECT strftime(InvoiceDate, '%Y-%m') AS month,
COUNT(DISTINCT InvoiceNo) AS orders
FROM online_retail
WHERE InvoiceNo NOT LIKE 'C%' -- cleaning rules live in every query
AND Quantity > 0 AND UnitPrice > 0
AND StockCode NOT IN ('POST', 'D', 'M')
GROUP BY month ORDER BY month;
Normalised (OLTP, 3NF)
2 joins · cleaning in the query
SELECT date_trunc('month', i.invoice_date) AS month,
p.description,
SUM(l.quantity * l.unit_price) AS revenue
FROM invoice_lines l
JOIN invoices i USING (invoice_no)
JOIN products p USING (stock_code)
WHERE NOT i.is_cancelled
GROUP BY month, p.description;
Star schema
2 joins · pre-cleaned
SELECT d.month, p.description, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key)
GROUP BY d.month, p.description; -- cleaning already done when the fact table was built
One big table (wide)
0 joins · pre-cleaned
SELECT month, product, SUM(revenue) AS revenue
FROM sales_wide
GROUP BY month, product; -- no joins at all
Method library
Every technique between raw data and a trusted table.
43 methods used in data teams and databases, each animated. Most run on the real Online Retail rows; the others are marked as examples.
Ingestreal data
Full load
Copy the whole source table every time, replacing the old copy.
invoice
code
qty
536365
85123A
6
536365
71053
6
Simple and always complete
Slow and costly once tables are big
Fine for small lookup tables
▸ SQL
TRUNCATE stg_products;
INSERT INTO stg_products SELECT * FROM source.products;
Ingestreal data
Incremental load
Only fetch rows newer than the last load, using a watermark such as the latest timestamp.
invoice
date
qty
536365
2010-12-01 08:26
6
536365
2010-12-01 08:26
6
Remember the high-water mark after each run
Fetch WHERE updated_at > watermark
Misses deletes unless the source flags them
▸ SQL
INSERT INTO stg_sales
SELECT * FROM source.sales
WHERE invoice_ts > (SELECT MAX(invoice_ts) FROM stg_sales);
Ingestexample
Change data capture
Read the source database's own change log (inserts, updates, deletes) and replay it.
code
description
price
85123A
White hanging heart
2.55
71053
White metal lantern
3.39
22999
Old product
1
Captures updates and deletes, not just new rows
Near real-time, light on the source
Needs log access (e.g. Debezium on PostgreSQL)
▸ SQL
MERGE INTO products t USING changes c ON t.code = c.code
WHEN MATCHED AND c.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET price = c.price
WHEN NOT MATCHED THEN INSERT VALUES (c.code, c.description, c.price);
Ingestexample
Batch vs streaming
Move data on a schedule (batch) or event by event as it happens (streaming).
Batch: simpler, cheaper, minutes-to-hours old
Streaming: seconds old, more moving parts
Most reporting is happy with batch
Profile & testreal data
Data profiling
Measure every column before trusting it: empties, distinct values, ranges.
InvoiceNo
25,900 distinct
StockCode
4,070 distinct
Description
0.3% null
Quantity
722 distinct
InvoiceDate
23,260 distinct
UnitPrice
1,630 distinct
CustomerID
24.9% null
Country
38 distinct
Green = filled, red = empty. Quantity runs from -80,995 to 80,995; prices from -11,062.06 to 38,970.
24.9% of rows have no customer ID
1,454 rows have no description
Ranges reveal negatives and extreme values
▸ SQL
SELECT COUNT(*) AS rows,
COUNT(*) - COUNT(CustomerID) AS no_customer,
COUNT(DISTINCT StockCode) AS products,
MIN(Quantity), MAX(Quantity)
FROM raw_sales;
Profile & testreal data
Data tests
Rules the data must pass every run: not null, unique, accepted ranges, valid references.
not_null(InvoiceNo)✓ pass
not_null(Description)✗ 1,454 rows
unique(all columns)✗ 5,268 rows
Quantity > 0✗ 10,624 rows
UnitPrice > 0✗ 2,517 rows
Country is not empty✓ pass
Run on the raw file. Failing tests stop the pipeline or send rows to a quarantine table.
Written once, run automatically
Fail loudly instead of silently
Common in dbt: not_null, unique, relationships
▸ SQL
-- a test passes when this returns no rows
SELECT * FROM raw_sales WHERE Quantity <= 0;
Profile & testreal data
Outlier detection
Find values far from the rest with a rule (IQR fence, z-score) and decide what they are.
Biggest line: 80,995 units, cancelled 12 minutes later
Fences flag candidates, people judge them
Never delete outliers just for being big
▸ SQL
WITH q AS (SELECT quantile_cont(Quantity, 0.25) q1, quantile_cont(Quantity, 0.75) q3 FROM raw_sales)
SELECT * FROM raw_sales, q WHERE Quantity > q3 + 1.5 * (q3 - q1);
Cleanreal data
Remove duplicates
Rows that are identical in every column are the same event recorded twice.
invoice
code
description
qty
price
536365
85123A
WHITE HANGING HEART T…
6
2.55
536409
21866
UNION JACK FLAG LUGGA…
1
1.25
536409
21866
UNION JACK FLAG LUGGA…
1
1.25
5,268 exact duplicate rows in the raw file
Decide what “duplicate” means: every column, or a business key
Keep one, count what was removed
▸ SQL
SELECT DISTINCT * FROM raw_sales;
-- removes 5,268 rows here
Cleanreal data
Handle missing values
Choose per column: drop the row, fill a value (impute), or keep it and flag it.
invoice
customer
country
536365
known
United Kingdom
536414
null
United Kingdom
536941
null
United Kingdom
536409
known
United Kingdom
135,080 orders have no customer: real guest checkouts, so flag them
Imputing invents data: only where it can't mislead
Never fill with 0 when 0 means something
▸ SQL
SELECT *, CustomerID IS NULL AS is_guest,
COALESCE(CAST(CustomerID AS TEXT), 'guest') AS customer
FROM raw_sales;
Cleanreal data
Fix data types
Turn text into real dates, integers and decimals so sorting and maths work.
InvoiceDate (text)
Quantity (text)
UnitPrice (text)
01/12/2010 08:26
6
£2.55
01/12/2010 08:26
6
£3.39
01/12/2010 08:26
8
£2.75
Dates in one format and time zone
Strip currency signs and spaces before casting
Rows that won't cast go to quarantine
▸ SQL
SELECT strptime(InvoiceDate, '%d/%m/%Y %H:%M') AS invoice_ts,
CAST(trim(Quantity) AS INTEGER) AS quantity,
CAST(replace(UnitPrice, '£', '') AS DECIMAL(10,2)) AS unit_price
FROM raw_sales_text;
Cleanreal data
Standardise values
One spelling per thing: trim spaces, fix case, merge variants of the same name.
code
description
rows
17107D
FLOWER FAIRY,5 SUMMER B'DRAW LINERS
25
17107D
FLOWER FAIRY 5 DRAWER LINERS
21
17107D
FLOWER FAIRY 5 SUMMER DRAW LINERS
1
Code 17107D appears under 3 different descriptions
Pick one canonical name per code
Keep a mapping table so it's repeatable
▸ SQL
SELECT StockCode,
FIRST(Description ORDER BY n DESC) AS description -- most common spelling
FROM (SELECT StockCode, Description, COUNT(*) n FROM raw_sales GROUP BY 1, 2)
GROUP BY StockCode;
Cleanreal data
Filter invalid records
Remove rows that aren't what the analysis is about: cancellations, zero prices, fees.
invoice
code
description
qty
price
536365
85123A
WHITE HANGING HEART T…
6
2.55
C536379
D
Discount
-1
27.5
536941
22734
amazon
20
0
536370
POST
POSTAGE
3
18
9,288 cancellations, 2,517 zero-price rows
2,916 postage, fee and adjustment rows
Write each rule down and count what it removes
▸ SQL
SELECT * FROM raw_sales
WHERE InvoiceNo NOT LIKE 'C%'
AND Quantity > 0 AND UnitPrice > 0
AND StockCode NOT IN ('POST', 'D', 'M', 'DOT', 'BANK CHARGES');
Cleanreal data
Rename & conform
Consistent names and units across every source: snake_case, currency in the name.
InvoiceNo
StockCode
UnitPrice
536365
85123A
2.55
536365
71053
3.39
Every table speaks the same language
Units in names avoid mistakes (price_gbp)
Shared columns get identical names (conformed)
▸ SQL
SELECT InvoiceNo AS invoice_no,
StockCode AS product_code,
UnitPrice AS unit_price_gbp
FROM raw_sales;
Transformexample
Joins
Combine tables on a shared key. The join type decides what happens to rows without a match.
code
qty
name
85123A
6
White heart T-light
71053
6
White metal lantern
99999X
2
null
Every line; unknown product 99999X gets nulls.
INNER: only matches
LEFT: keep every left row, nulls where no match
ANTI: rows with no match, great for checks
▸ SQL
SELECT l.*, p.description
FROM order_lines l
LEFT JOIN products p ON p.code = l.code;
Transformreal data
Aggregation
Collapse many rows into one per group with SUM, COUNT, AVG.
invoice
code
qty
price
536365
85123A
6
2.55
536365
71053
6
3.39
536365
84406B
8
2.75
536365
84029G
6
3.39
536365
84029E
6
3.39
536365
22752
2
7.65
536365
21730
6
4.25
The GROUP BY columns become the new grain
Count distinct carefully (orders vs lines)
Filter groups with HAVING
▸ SQL
SELECT invoice_no, COUNT(*) AS lines, SUM(quantity) AS units,
SUM(quantity * unit_price) AS revenue
FROM order_lines GROUP BY invoice_no;
Transformreal data
Window functions
Calculations across neighbouring rows without collapsing them: running totals, ranks, previous values.
month
orders
running total
vs last (LAG)
rank
2011-06
1,525
1,525
null
4
2011-07
1,452
2,977
-73
5
2011-08
1,339
4,316
-113
6
2011-09
1,818
6,134
+479
3
2011-10
2,005
8,139
+187
2
2011-11
2,751
10,890
+746
1
Real cleaned orders. Each row keeps its detail and gains a value computed across neighbouring rows.
OVER (ORDER BY …) defines the window
LAG / LEAD look at the previous / next row
PARTITION BY restarts per group
▸ SQL
SELECT month, orders,
SUM(orders) OVER (ORDER BY month) AS running_total,
orders - LAG(orders) OVER (ORDER BY month) AS change,
RANK() OVER (ORDER BY orders DESC) AS rank
FROM monthly_orders;
Transformreal data
Pivot & unpivot
Reshape between long (one row per value) and wide (one column per category).
country
month
orders
France
2011-09
45
France
2011-10
33
France
2011-11
64
Germany
2011-09
41
Germany
2011-10
56
Germany
2011-11
64
Long is best for storage and SQL
Wide is best for people and charts
UNPIVOT goes back the other way
▸ SQL
PIVOT monthly_country_orders
ON month USING SUM(orders)
GROUP BY country;
Transformreal data
Derived columns
New columns computed from existing ones: revenue, weekday, flags, buckets.
date
qty
price
2010-12-01 08:26
6
2.55
2010-12-01 08:26
6
3.39
2010-12-01 08:26
8
2.75
Compute once in the model, not in every report
Name them clearly
Keep the formula documented
▸ SQL
SELECT *, quantity * unit_price AS revenue,
dayname(invoice_ts) AS weekday
FROM order_lines;
Transformexample
Keep the latest version
When a record appears several times over time, number the versions and keep the newest.
customer
city
updated_at
C-17
Leeds
2011-02-03
C-17
York
2011-09-14
C-42
Lyon
2011-05-01
ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC)
Keep row number 1
Common after incremental loads
▸ SQL
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY updated_at DESC) AS rn
FROM customer_versions)
WHERE rn = 1;
Transformexample
Flatten nested data
Turn JSON objects and lists into rows and columns that SQL can join and count.
id
additionalProperties
JamCams_00001.07450
[{available:true}, {videoUrl:…mp4}]
UNNEST / explode a list into rows
Pull fields out of objects into columns
Watch for row multiplication
▸ SQL
SELECT id, p.key, p.value
FROM places, UNNEST(additionalProperties) AS t(p);
Transformreal data
Union / append
Stack tables with the same columns, e.g. one file per year, into one.
invoice
date
qty
536365
2010-12-01 08:26
6
536365
2010-12-01 08:26
6
Columns must match in order and type
UNION removes duplicates, UNION ALL keeps all (faster)
Add a source column to know where rows came from
▸ SQL
SELECT * FROM sales_2010
UNION ALL
SELECT * FROM sales_2011;
Modelexample
Normalisation (1NF → 3NF)
Split a repetitive table so every fact is stored once, in the table it depends on.
1NF: one value per cell, one row per thing
2NF: no facts that depend on part of the key
3NF: no facts that depend on other non-key columns
Modelreal data
Fact grain
Declare what one row of the fact table means before adding any column.
GRAIN: ONE ROW PER INVOICE LINE
invoice 536365
WHITE HANGING HEART T-LIGHT HOLDER6 × 2.55
WHITE METAL LANTERN6 × 3.39
CREAM CUPID HEARTS COAT HANGER8 × 2.75
KNITTED UNION FLAG HOT WATER BOTTLE6 × 3.39
Decide the grain first. A shipping fee stored once per invoice on every line would be counted 4 times.
Here: one row per invoice line
Measures must match the grain
Mixed grains double-count
Modelreal data
Surrogate keys
Give each dimension row its own meaningless integer key instead of the source's code.
product_code
description
85123A
WHITE HANGING HEART T-LIG…
85099B
JUMBO BAG RED RETROSPOT
22423
REGENCY CAKESTAND 3 TIER
47566
PARTY BUNTING
Stable even if the source code changes or is reused
Small integers join fast
Needed for history (SCD type 2)
▸ SQL
SELECT ROW_NUMBER() OVER (ORDER BY product_code) AS product_key, *
FROM stg_products;
Modelexample
SCD type 1: overwrite
When an attribute changes, overwrite it. Simple, but the history is gone.
customer
city
C-17
Leeds
C-42
Lyon
Use for corrections (typos)
Reports always show today's value
Old reports change when re-run
▸ SQL
UPDATE dim_customer SET city = 'York' WHERE customer = 'C-17';
Modelexample
SCD type 2: new row
Close the old row with an end date and add a new row, so the past stays true.
key
customer
city
valid_from
valid_to
current
1
C-17
Leeds
2010-12-01
9999-12-31
yes
valid_from / valid_to / is_current columns
Facts point to the version valid at the time
The standard for things that really change
▸ SQL
UPDATE dim_customer SET valid_to = DATE '2011-09-13', is_current = false
WHERE customer = 'C-17' AND is_current;
INSERT INTO dim_customer VALUES (2, 'C-17', 'York', DATE '2011-09-14', DATE '9999-12-31', true);
Modelexample
SCD type 3: previous column
Keep the current and the previous value side by side in two columns.
customer
city
C-17
Leeds
Only one step of history
Handy for “before and after a reorganisation”
Rarely the right long-term choice
Modelreal data
Denormalise for reading
Join descriptive columns back onto the facts so readers don't have to.
product_key
qty
revenue
1
6
15.3
2
6
20.34
3
8
22
The opposite of normalising, on purpose
Faster, simpler dashboards
Rebuild when dimensions change
▸ SQL
CREATE TABLE sales_wide AS
SELECT f.*, p.description FROM fact_sales f JOIN dim_product p USING (product_key);
Store & speedexample
Row vs column storage
Row stores keep each row together (fast for one record); column stores keep each column together (fast for totals).
A sorted lookup structure (B-tree) that jumps to matching rows instead of scanning everything.
Great for selective lookups (one invoice)
Every index slows down writes a little
Column stores rely more on partitions
▸ SQL
CREATE INDEX idx_sales_invoice ON order_lines (invoice_no);
Store & speedreal data
Partitioning
Store a big table in chunks (by month, by country) so queries skip chunks they don't need.
WHERE month = '2011-11' → 1 of 13 partitions read
10-12
11-01
11-02
11-03
11-04
11-05
11-06
11-07
11-08
11-09
11-10
11-11
11-12
The table is stored month by month, so a one-month query skips the other folders entirely.
Partition by the most common filter, usually date
Pruning: only matching partitions are read
Too many tiny partitions hurt
▸ SQL
CREATE TABLE order_lines (...) PARTITION BY RANGE (invoice_month);
Store & speedreal data
Materialised views
Store the result of an expensive query and refresh it on a schedule.
Dashboards read the small stored result
Refresh after each load
Stale between refreshes
▸ SQL
CREATE MATERIALIZED VIEW monthly_orders AS
SELECT date_trunc('month', invoice_ts) month, COUNT(DISTINCT invoice_no) orders
FROM order_lines GROUP BY 1;
Architectureexample
ETL vs ELT
Transform before loading (ETL), or load raw data and transform inside the warehouse (ELT).
ELT is the modern default with cheap warehouses
Raw data stays available for re-processing
dbt is an ELT tool
Architecturereal data
Bronze, silver, gold
Layers of increasing quality: raw as received, cleaned, then business-ready tables.
Bronze: never edited, can always replay
Silver: cleaned, typed, deduplicated
Gold: modelled for one purpose (dashboards, ML)
Architectureexample
OLTP vs OLAP
Systems that run the business (transactions) versus systems that analyse it.
OLTP (the app)
INSERT one order line
many tiny reads & writes
normalised tables
row storage, indexes
must never lose a write
OLAP (analytics)
SUM revenue by month
few huge reads
star schema / wide tables
column storage, partitions
minutes-old data is fine
Don't run heavy reports on the app's database
Copy data to an analytical store
Model each for its job
Architectureexample
Orchestration (DAG)
A scheduler runs each step in dependency order, retries failures and alerts when something breaks.
Tasks and dependencies form a graph
Tools: Airflow, Dagster, dbt Cloud, cron
Parallel where there are no dependencies
Architecturereal data
Idempotent jobs
Running a job twice gives the same result as running it once.
INSERT every run
7
rows for invoice 536365 after 1 run
MERGE / delete-then-insert
7
rows for invoice 536365 after 1 run
Re-running a job (after a failure, or to backfill) must give the same result, not double it.
Delete-then-insert or MERGE, never blind INSERT
Makes retries and backfills safe
Partition by date to re-run one day
▸ SQL
DELETE FROM fact_sales WHERE invoice_date = DATE '2010-12-01';
INSERT INTO fact_sales SELECT * FROM stg_sales WHERE invoice_date = DATE '2010-12-01';
Architectureexample
ACID transactions
A group of changes either all happen or none do, even if the system crashes half-way.
Warehouse stock
100
Order lines
0
⇄
BEGIN: take 6 from stock and record the sale
Atomic, Consistent, Isolated, Durable
BEGIN … COMMIT / ROLLBACK
Why databases don't lose half an order
▸ SQL
BEGIN;
UPDATE stock SET units = units - 6 WHERE code = '85123A';
INSERT INTO order_lines VALUES ('536365', '85123A', 6, 2.55);
COMMIT;
Governanceexample
Data lineage
A map of where every table and dashboard number comes from.
Trace a wrong number back to its source
See what breaks before changing a table
dbt draws it automatically
Governancereal data
Data catalogue
Every table and column documented: meaning, owner, quality.
column
meaning
empty
InvoiceNo
Order number; starts with C when cancelled
0
StockCode
Product code; a few codes are fees
0
Description
Product name as typed in the shop system
0.3%
Quantity
Units; negative for returns
0
InvoiceDate
When the order was placed (UK time)
0
UnitPrice
Price per unit in pounds
0
CustomerID
Customer number; empty for guests
24.9%
Country
Customer's country
0
People find data without asking around
Definitions agreed once (what is an “order”?)
Kept next to the code so it stays current
Governanceexample
Masking personal data
Hide or hash personal details so analysts can work without seeing who people are.
customer
email
phone
country
Customer A
ana@example.com
+44 7700 900123
United Kingdom
Customer B
luc@example.org
+33 6 12 34 56 78
France
Hash IDs so joins still work
Partially mask emails and phones
Required by privacy laws such as GDPR and Indonesia's PDP law
Governancereal data
Row-level security
One table, many viewers: each user sees only the rows they're allowed to.
████████
███
████████
███
United Kingdom
2,481
Same table, same report: each viewer only sees their own rows (orders, Nov 2011).
Rules filter rows per user or role
Built into Power BI, BigQuery and PostgreSQL
One report instead of a copy per region
▸ SQL
CREATE POLICY by_country ON orders
USING (country = current_setting('app.country'));
Governanceexample
Data contracts
An agreement on a dataset's schema and quality between the team that produces it and the teams that use it.