Extract, transform, load (ETL)
The pipeline that feeds a warehouse: the three steps and the staging area, full or incremental loads, dimensions before facts, surrogate keys, slowly changing dimensions, replayable loads and the ELT variant.
- 32 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
- Understand
A few cards from the deck
Data warehouse: what is it?
Show the answer
A database separate from the operational applications, fed at regular intervals from several sources, which keeps integrated, historised data for analysis, added to but not modified.
This is Inmon's definition: subject-oriented, integrated, time-variant, non-volatile. Separating analysis from production keeps heavy queries from slowing the applications down, and keeps the history the sources overwrite.
Customer 1042 moves from Lyon to Nantes. The load runs these two statements. Which dimension policy does it apply?
```sql
UPDATE dim_client SET date_fin = '2024-03-10', courant = 0
WHERE client_id = 1042 AND courant = 1;
INSERT INTO dim_client (cle_sub, client_id, ville, date_debut, date_fin, courant)
VALUES (9001, 1042, 'Nantes', '2024-03-10', NULL, 1);`- Type 2: the old version is closed, a new one is added
- Type 1: the city Lyon is overwritten by Nantes and no trace of the old one is kept
- Type 3: Lyon moves to a previous_city column
- None: the customer is deleted and recreated as a new customer
Show the answer
Type 2: the old version is closed, a new one is added
Closing the current row (end date, flag at 0) then inserting a row with a new surrogate key is the type 2 signature: both cities stay available.
During a load, the … are written first, then the …, because each fact row must carry the surrogate key of a dimension that already exists.
Show the answer
During a load, the dimensions are written first, then the facts, because each fact row must carry the surrogate key of a dimension that already exists.
A surrogate key only exists once its dimension row has been inserted. Loading the facts first would leave references to nothing.
ETL: what is it?
Show the answer
The pipeline that feeds a warehouse in three steps, in this order: take the data from the sources, clean it and put it in the warehouse's format, then write it there.
The initials give the order: Extract, Transform, Load. The transformation happens before the write, on an intermediate server, so the warehouse only receives data that is already clean.
A nightly load fails at 3 am, then is rerun by hand. The next day, the day's revenue has doubled in the warehouse. What needs to change?
- Make the load idempotent: replace the day's slice or merge on the business key
- Add an index on the fact table's date column to speed up the rerun
- Remove the duplicates by hand each morning with a DELETE before the analysts arrive
- Convert the amount column to DECIMAL
Show the answer
Make the load idempotent: replace the day's slice or merge on the business key
The first run had already written some of the rows; the rerun replayed the INSERTs without a uniqueness key. An index speeds things up but does not deduplicate, a manual DELETE treats the symptom, and the amount's type has nothing to do with it.