Tech

Data engineering

Design pipelines that load once, scan little and never lie: warehouse models, columnar files, partitions, Spark joins, late events in streams, and tests that catch a half-empty table.

Read first: SQL and data analysis

7 concepts8 min read25-question test, from a bank of 33Updated

Concept 1 of 71 min

Modelling the warehouse: facts, dimensions and history

The rule
In a star schema each fact row is a measured event, such as a sale line, holding keys to its dimensions and its amounts.
Worked examples

A slowly changing dimension

Given
Lyon until31 March 2027
Marseilleafter
Sale15 March 2027
  1. The customer has 2 rows
  2. The sale joins the Lyon row

Each row carries its validity dates, so history stays correct.

AVG and NULL

Given
Discount 18
Discount 2NULL
Discount 316
Rows3
  1. AVG = (8 + 16) ÷ 2 = 12
  2. With 0 for NULL: (8 + 0 + 16) ÷ 3 = 8

AVG skips NULLs, which is right only if a missing discount is unknown, not 0.

The picture
A type 2 dimension keeps the past
  1. 15 March 2027A sale, joined to the Lyon row
  2. 31 March 2027Last day of the Lyon row
  3. 1 April 2027The Marseille row starts
Formula
Fact: date_key, product_key, store_key, quantity, price
type 2 row: customer_key, city, valid_from, valid_to, is_current
Why it holds

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.

Watch out
  • Putting product attributes in the fact table.
  • Overwriting a dimension when history matters.
  • Forgetting that AVG ignores NULL.
In the interview
  1. 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 rule
Raw data sits cheaply in a lake on object storage, while curated tables live in a warehouse for fast SQL (Structured Query Language).

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 3 of 71 min

Spark: lazy plans, skew and broadcast joins

The rule
Spark transformations only build a plan until an action such as count, write or collect runs it.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 4 of 71 min

Partitions, idempotent loads, small files and backfills

The rule
A partitioned table prunes only on its partition key, so a filter on user_id alone scans every date partition.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 5 of 71 min

Streams: latency, consumers, delivery and late events

The rule
A Kafka partition goes to one consumer of a group, so consumers beyond the partition count sit idle.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 6 of 71 min

Data quality, scheduling, throughput, time zones and deletion

The rule
Volume and anomaly tests catch silent partial loads, and uniqueness tests catch rows doubled by a join.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 7 of 71 min

AdvancedChoosing a watermark: completeness against delay

The rule
A watermark closes an event-time window: waiting longer counts more late events but delays every result.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Recap

  • 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.
Now test it

25 questions on Data engineering

Timed, like the real one, drawn from a bank of 33. Every answer explained.

Test yourself →