Concept 1 of 71 min
Modelling the warehouse: facts, dimensions and history
A slowly changing dimension
| Lyon until | 31 March 2027 |
|---|---|
| Marseille | after |
| Sale | 15 March 2027 |
- The customer has 2 rows
- The sale joins the Lyon row
Each row carries its validity dates, so history stays correct.
AVG and NULL
| Discount 1 | 8 |
|---|---|
| Discount 2 | NULL |
| Discount 3 | 16 |
| Rows | 3 |
- AVG = (8 + 16) ÷ 2 = 12
- With 0 for NULL: (8 + 0 + 16) ÷ 3 = 8
AVG skips NULLs, which is right only if a missing discount is unknown, not 0.
- 15 March 2027A sale, joined to the Lyon row
- 31 March 2027Last day of the Lyon row
- 1 April 2027The Marseille row starts
The dimensions describe the event. A type 2 slowly changing dimension adds a new row with validity dates so past facts keep their old attributes.
Analytics tables are denormalised to avoid costly joins, and aggregates such as AVG skip NULL. Products, stores and dates are described once in dimensions; facts only point to them.
Overwriting a customer's city in place, type 1, rewrites the past: old sales would move to the new city. Analytics reads a lot and rarely updates, so wide tables pay off, while normalisation protects consistency where data changes often.
Treating a missing discount as 0 needs COALESCE, which changes the average. A customer living in Lyon until 31 March 2027 and in Marseille from 1 April has 2 rows, and a sale on 15 March joins the Lyon row.
- Putting product attributes in the fact table.
- Overwriting a dimension when history matters.
- Forgetting that AVG ignores NULL.
Sketch a warehouse model for a bank's card payments so that analysts can report by merchant, country and day.
A complete answer covers
- A fact table with one row per payment: amount, fees and keys.
- Dimensions for merchant, card, customer, country and date.
- Type 2 history on dimensions whose attributes change, such as a merchant's category.
- Partition the fact table by date for cost and speed.
Also in our technical interviews, on camera, with a score.
Concept 2 of 71 min
Lakes, warehouses, columnar files and change capture
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 3 of 71 min
Spark: lazy plans, skew and broadcast joins
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 4 of 71 min
Partitions, idempotent loads, small files and backfills
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 5 of 71 min
Streams: latency, consumers, delivery and late events
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 6 of 71 min
Data quality, scheduling, throughput, time zones and deletion
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inConcept 7 of 71 min
AdvancedChoosing a watermark: completeness against delay
The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.
See the plansSign inRecap
- A fact row is a measured event with keys to dimensions; type 2 dimensions keep history with validity dates.
- Raw data in a cheap lake, curated tables in a warehouse; columnar files read only the columns asked for.
- Partitions prune only on their key; overwrite a partition to make reloads idempotent; compact small files; backfill after a fix.
- Spark is lazy until an action; one hot key slows one task; broadcast the small side of a join.
- Streams: one consumer per partition, at-least-once means duplicates, watermarks wait for late events, UTC (Coordinated Universal Time) everywhere.
25 questions on Data engineering
Timed, like the real one, drawn from a bank of 33. Every answer explained.