←BaldanAll skillsLet's talk

Data Analytics & BI · Data models

From raw data to tables people trust.

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.

Raw to ready

What happens to raw data before anyone sees a chart.

Scroll through the full journey of the real Online Retail file, from the export to a trusted table.

online_retail.xlsx · 541,909 rows, stored untouched (bronze)

InvoiceNoStockCodeDescriptionQtyPriceCustomer
53636585123AWHITE HANGING HEART T-LIGHT HOLDER62.55known
53640921866UNION JACK FLAG LUGGAGE TAG11.25known
53640921866UNION JACK FLAG LUGGAGE TAG11.25knownexact duplicate
C536379DDiscount-127.5knowncancellation
53694122734amazon200nullzero price, odd name
53641422139null560nullno description

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

Flat (1NF)order_linesinvoicedatecountrycodedescriptionqtypriceone row per order line; names and countries repeat

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

extracttest rawcleanproductsfct_salesEach task waits for the ones it depends on; failures retry or stop what depends on them.
online_retail.xlsxstg_salesstg_productsfct_salesdim_productSales dashboardIf a number on the dashboard looks wrong, lineage shows every step it came through.

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.

online_retail.xlsxTABLEInvoiceNotextStockCodetextDescriptiontextQuantityintInvoiceDatedatetimeUnitPricedecimalCustomerIDintCountrytext

UCI Online Retail (CC BY 4.0): rows copied from the file, customer IDs left out

541,909 rows like these

InvoiceNoStockCodeDescriptionQtyInvoiceDatePriceCountry
53636585123AWhite Hanging Heart T-Light Holder62010-12-01 08:262.55United Kingdom
53636571053White Metal Lantern62010-12-01 08:263.39United Kingdom
53636584406BCream Cupid Hearts Coat Hanger82010-12-01 08:262.75United Kingdom
53636584029GKnitted Union Flag Hot Water Bottle62010-12-01 08:263.39United Kingdom
C536379DDiscount-12010-12-01 09:4127.5United Kingdom
536370POSTPostage32010-12-01 08:4518France

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.

invoicecodeqty
53636585123A6
536365710536
  • 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.

invoicedateqty
5363652010-12-01 08:266
5363652010-12-01 08:266
  • 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.

codedescriptionprice
85123AWhite hanging heart2.55
71053White metal lantern3.39
22999Old product1
  • 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).

Batchcollected, then moved every night0%DBStreamingeach event moves within secondsDB
  • 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.

1101001k10k100kfence 23.580,995 unitsMiddle half of orders: 1–10 units. 56,635 rows sit past the fence:most are real bulk orders, so the fence flags, a person decides.
  • 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.

invoicecodedescriptionqtyprice
53636585123AWHITE HANGING HEART T…62.55
53640921866UNION JACK FLAG LUGGA…11.25
53640921866UNION JACK FLAG LUGGA…11.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.

invoicecustomercountry
536365knownUnited Kingdom
536414nullUnited Kingdom
536941nullUnited Kingdom
536409knownUnited 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.

codedescriptionrows
17107DFLOWER FAIRY,5 SUMMER B'DRAW LINERS25
17107DFLOWER FAIRY 5 DRAWER LINERS21
17107DFLOWER FAIRY 5 SUMMER DRAW LINERS1
  • 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.

invoicecodedescriptionqtyprice
53636585123AWHITE HANGING HEART T…62.55
C536379DDiscount-127.5
53694122734amazon200
536370POSTPOSTAGE318
  • 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.

InvoiceNoStockCodeUnitPrice
53636585123A2.55
536365710533.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.

linesproducts
codeqtyname
85123A6White heart T-light
710536White metal lantern
99999X2null

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.

invoicecodeqtyprice
53636585123A62.55
5363657105363.39
53636584406B82.75
53636584029G63.39
53636584029E63.39
5363652275227.65
5363652173064.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.

monthordersrunning totalvs last (LAG)rank
2011-06
1,525
1,525null4
2011-07
1,452
2,977-735
2011-08
1,339
4,316-1136
2011-09
1,818
6,134+4793
2011-10
2,005
8,139+1872
2011-11
2,751
10,890+7461

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).

countrymonthorders
France2011-0945
France2011-1033
France2011-1164
Germany2011-0941
Germany2011-1056
Germany2011-1164
  • 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.

dateqtyprice
2010-12-01 08:2662.55
2010-12-01 08:2663.39
2010-12-01 08:2682.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.

customercityupdated_at
C-17Leeds2011-02-03
C-17York2011-09-14
C-42Lyon2011-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.

idadditionalProperties
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.

invoicedateqty
5363652010-12-01 08:266
5363652010-12-01 08:266
  • 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.

Flat (1NF)order_linesinvoicedatecountrycodedescriptionqtypriceone row per order line; names and countries repeat
  • 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_codedescription
85123AWHITE HANGING HEART T-LIG…
85099BJUMBO BAG RED RETROSPOT
22423REGENCY CAKESTAND 3 TIER
47566PARTY 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.

customercity
C-17Leeds
C-42Lyon
  • 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.

keycustomercityvalid_fromvalid_tocurrent
1C-17Leeds2010-12-019999-12-31yes
  • 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.

customercity
C-17Leeds
  • 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_keyqtyrevenue
1615.3
2620.34
3822
  • 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).

Row storeinvoicecodeqtypricecountrySUM(qty) reads every columnColumn storeinvoicecodeqtypricecountrySUM(qty) reads 1 of 5 columns
  • Apps: row stores (PostgreSQL)
  • Analytics: column stores (BigQuery, DuckDB, Parquet)
  • Columns of similar values compress well
Store & speedreal data

Indexes

A sorted lookup structure (B-tree) that jumps to matching rows instead of scanning everything.

WHERE invoice_no = '581483'root< 500000500000+50…58…Index: about 3 page reads. Without it: scan all 541,909 rows.
  • 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.

fact_sales522,568 rowsmonthly_orders13 rows, storedrefreshed nightlydashboardscans 522,568 rows every time
  • 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).

ETLExtractTransformLoadtransform on a separate server, then load clean dataELTExtractLoadTransformload raw first, transform inside the warehouse with SQL
  • 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.

Bronzeraw, as received541,909 rowsSilvercleaned & typed522,568 rowsGoldbusiness tables13 rowsReal counts: the raw file, after the cleaning rules, and one gold table (orders per month).
  • 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.

extracttest rawcleanproductsfct_salesEach task waits for the ones it depends on; failures retry or stop what depends on them.
  • 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.

online_retail.xlsxstg_salesstg_productsfct_salesdim_productSales dashboardIf a number on the dashboard looks wrong, lineage shows every step it came through.
  • 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.

columnmeaningempty
InvoiceNoOrder number; starts with C when cancelled0
StockCodeProduct code; a few codes are fees0
DescriptionProduct name as typed in the shop system0.3%
QuantityUnits; negative for returns0
InvoiceDateWhen the order was placed (UK time)0
UnitPricePrice per unit in pounds0
CustomerIDCustomer number; empty for guests24.9%
CountryCustomer's country0
  • 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.

customeremailphonecountry
Customer Aana@example.com+44 7700 900123United Kingdom
Customer Bluc@example.org+33 6 12 34 56 78France
  • 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 Kingdom2,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.

Agreed contract

invoice_no: text, required

quantity: integer

unit_price: decimal ≥ 0

Today's delivery

invoice_no: text

quantity: integer

unit_price: decimal

Schema matches: load continues

  • Breaking changes are caught before they load
  • Producers know who depends on them
  • Versioned like an API