The idea: load the sales, and decide what to do when a customer moves

A data warehouse gets its data from other systems. A job called ETL (extract, transform, load) runs every night or every hour. It reads the new rows from each source, cleans them, and loads them into the warehouse tables. Sales go into a fact table. The things sales are grouped by, such as the customer and the customer's city, sit in a dimension table.

Two sources, shop_db.orders and pos.csv, feed four ETL steps: extract into the staging table, clean the keys, look up customer_sk in dim_customer, load fact_sales. A report joins fact_sales to dim_customer and sums by city.
One ETL batch: extract into staging, clean, look up the surrogate key, load the fact rows. The report reads the city through the dimension.

Sales only get added. Customers change: An lives in Hanoi, then moves to Da Nang. The source database just overwrites the city. The warehouse has to choose: forget Hanoi, or remember it? A dimension whose rows change now and then is a slowly changing dimension (SCD). The three classic answers are called type 1, type 2 and type 3. Each one gives a different report.

On the page, press Run ETL Batch, then Customer Moves, then Run ETL Batch again. Pick the SCD type in the dimension menu. The colour of a row is its city. The report table has a column at sale time: the city the customer really lived in when each sale happened. A number with ✓ agrees with it; a number with ✗ does not.

The four steps of a batch

stepwhat it doeson the page
1 extractcopy the new rows of every source into a staging table, as they areshop_db.orders (a query) and pos.csv (LOAD DATA INFILE) into stg_sales
2 cleanmake the values comparable; set aside rows that cannot be usedUPPER(TRIM(customer_id)): " c1" becomes C1; the row with an empty key is rejected
3 look up keysreplace each source key by the warehouse's own keyC1 → customer_sk 1
4 loadinsert the fact rowsINSERT INTO fact_sales … SELECT … FROM stg_sales

The staging table is the ETL's workbench. Sources with different formats land there first, so the cleaning rules are written once. A rejected row is not thrown away: it goes to a reject file or table, so somebody can fix the source and load it later. In batch 1 the till sent a sale of 30 with no customer. It is extracted, rejected and never reaches fact_sales (Demo 1: one ETL batch: 5 rows extracted, 1 cleaned, 1 rejected, 4 loaded).

Surrogate keys

The source calls the customer C1. That is the natural key (or business key). The warehouse gives every dimension row its own number, customer_sk, an AUTO_INCREMENT column. That is the surrogate key. Fact rows store only the surrogate key.

Why not store C1 in the fact row? Because one customer may need more than one dimension row. With type 2, An in Hanoi and An in Da Nang are two rows with the same customer_id and two different customer_sk. The surrogate key is what lets a fact point to one version. Two sources may also use the same id for different customers; a surrogate key keeps them apart.

SELECT customer_sk FROM dim_customer
WHERE customer_id = 'C1' AND is_current = 1;

The lookup takes the current row. So sales loaded after the move get the new key by themselves.

A customer moves

Batch 1 loads An's sales of 100 and 50 (Hanoi) and Binh's 40 and 60 (Hue). On 2025-02-01 An moves to Da Nang. Batch 2 loads 70 for An and 20 for Binh. What really happened: Hanoi 150, Da Nang 70, Hue 120.

The dim_customer rows of customer C1 after the move, and the report by city, for three types. Type 1: one row, city Da Nang; report Da Nang 220, Hanoi 0, wrong. Type 2: row 1 Hanoi closed on 2025-02-01 and row 3 Da Nang current; report Hanoi 150, Da Nang 70, right. Type 3: one row, city Da Nang, prev_city Hanoi; report by city Da Nang 220, by prev_city Hanoi 220.
The same move, three dimension tables, three reports. Only type 2 keeps each sale in the city where it happened.

Type 1: overwrite

UPDATE dim_customer SET city = 'Da Nang' WHERE customer_id = 'C1';

One row per customer. The old city is gone. Every old fact row of An still points to customer_sk 1, and that row now says Da Nang, so the old sales move too: the report shows Da Nang 220 and no Hanoi at all (Demo 2: type 1 overwrite). On the page the old fact rows change colour. Type 1 is right when the old value was simply wrong (a typo in a name), or when nobody needs the history.

Type 2: a new row

UPDATE dim_customer SET valid_to = '2025-02-01', is_current = 0
WHERE customer_id = 'C1' AND is_current = 1;

INSERT INTO dim_customer (customer_id, name, city, valid_from, valid_to, is_current)
VALUES ('C1', 'An', 'Da Nang', '2025-02-01', '9999-12-31', 1);

The current row is closed and a new row is added with a new surrogate key (3). Old facts keep key 1 = Hanoi. New facts get key 3 = Da Nang. The report shows Hanoi 150, Da Nang 70, Hue 120: the truth (Demo 3: type 2 new row). Each further move adds one more row (Demo 5: type 2, two moves: keys 1, 3 and 4). The price is a bigger dimension, and queries that want "customers today" must filter on is_current = 1. The open end date 9999-12-31 stands for "still valid"; it keeps BETWEEN filters simple.

Type 3: an extra column

UPDATE dim_customer SET prev_city = city, city = 'Da Nang'
WHERE customer_id = 'C1';

One row, two columns: the current city and the one before. (In MariaDB the assignments of a single-table UPDATE run left to right, so prev_city must come first.) The report can group by either column. By city: Da Nang 220. By prev_city: Hanoi 220. Each view puts all of An's sales in one city; neither splits them 150 and 70 (Demo 4: type 3 extra column). And there is room for only one old value: when An moves again, to Can Tho, prev_city becomes Da Nang and Hanoi is lost (Demo 6: type 3, two moves). Type 3 fits a planned, one-time change, such as a new sales region map, where people want to compare "old map" and "new map" for a while.

The three types side by side

type 1type 2type 3
change madeoverwrite the columnclose the row, insert a new onecopy to prev_city, then overwrite
rows per customer11 + number of changes1
history keptnoneall of itone step back
old facts are shownin the new city ✗in the city at sale time ✓all in the new city, or all in the old one
report after the move and batch 2Da Nang 220, Hue 120Hanoi 150, Da Nang 70, Hue 120by city: Da Nang 220, Hue 120; by prev_city: Hanoi 220, Hue 120

The type is chosen per column, not per table. A real customer dimension often overwrites the name (type 1) and versions the city (type 2).

See also Change Data Capture for a way to extract changes from the database log instead of querying the tables, Row Store vs Column Store for how a warehouse stores its fact table, Normalization for why the source keeps the city in one place, and MVCC for row versions inside the database engine, which are a different thing from type 2 versions.

What the page leaves out

The star schema itself, more dimensions (date, product) and cube operations: see Star Schema and OLAP Cube. New customers that appear in a batch before they are in the dimension, and late facts that must be matched to the dimension row valid on the sale date, not the current one. Removing duplicate rows that both sources send. Incremental extraction by timestamp or by log. Hashing a row to detect changes. Types 0, 4 and 6. Loading in one transaction and restarting a failed batch. MariaDB has no MERGE statement; an upsert is written with INSERT … ON DUPLICATE KEY UPDATE. MariaDB's system-versioned tables (WITH SYSTEM VERSIONING) keep old row versions by themselves, but by the time of the change in the warehouse, and without a new surrogate key.

On the mind map

Open the Distributed Systems mind mapDistributedSystemsReplication: copies of data · what reads seeReplicationPartitioning: split data over nodesPartitioningTime and membership: order events, know who is aliveTime and membershipConsensus: agree on one value or logConsensusTransactions: one change across many servicesTransactionsMessaging: send events between systemsMessagingKafka internals: a replicated append-only logKafka internalsRedis internals: one thread, data in memoryRedis internalsKubernetes: reconcile desired stateKubernetesResilience and speed: stay up and fast under loadResilience and speedDelivery semanticsDelivery semanticscommit before or after processingMQTT QoS levelsMQTT QoS levelsQoS 2: PUBREC/PUBREL drop resent copiesPush vs pullPush vs pullbroker pushes with prefetch vs pollChange data captureChange data capturetail the WAL, publish each row changeETL and SCD typesType 2 = new row, new key, history keptRedis StreamsRedis Streamsconsumer group, PEL until XACKLog vs queueLog vs queuequeue deletes on ack; log keeps offsetsOpen the Distributed Systems mind mapDistributed Systemssend events between systemsMessagingDelivery semanticsDelivery semanticscommit before or after processingMQTT QoS levelsMQTT QoS levelsQoS 2: PUBREC/PUBREL drop resent copiesPush vs pullPush vs pullbroker pushes with prefetch vs pollChange data captureChange data capturetail the WAL, publish each row changeETL and SCD typesType 2 = new row, new key, history keptRedis StreamsRedis Streamsconsumer group, PEL until XACKLog vs queueLog vs queuequeue deletes on ack; log keeps offsets
This page on the Distributed Systems mind map · 82 pages in 10 branches · open it to see the whole map and test yourself in recall mode.