The Data Warehouse Toolkit: Kimball's book on keeping dashboards from rewriting past numbers

Picture a Monday morning. A client’s head of sales opens the dashboard and sees that first-quarter revenue for the North is lower than it was last week. Nobody has changed the pipeline and nobody has deleted any orders. A large customer has moved its headquarters to the South, and the system has quietly rewritten the past.

If you were the FDE responsible for the data in that scenario, you would be the one explaining it.

That is why The Data Warehouse Toolkit, 3rd Edition is still worth reading, along with the dimensional modelling technique pages published by the Kimball Group. They name the mechanism that makes old numbers change, and they show how to design your tables so that this happens only when you intend it to.

A book from 2013 still describes today’s stack

Ralph Kimball and Margy Ross wrote the third edition of the classic guide to dimensional modelling, the approach Kimball pioneered. Wiley published it in 2013.

The book is old, yet dbt’s own documentation still describes snapshots as the way to implement SCD Type 2 on source tables that can change. Type 2 is Kimball’s concept. dbt snapshots are one way of putting that idea into code.

Reading Kimball therefore tells you what problem snapshots solve, not just how to configure them. The FDE skill these materials build is turning a vague business question into a data design that can be checked. Three ideas from them are worth taking to any project.

Set the grain before you draw any tables

The Kimball Group’s technique pages define the grain as what a single row of a fact table represents. It is the first decision you have to settle. That sounds simple, but many arguments about numbers start here.

Take “each row is an order” and “each row is a product within an order”. If someone counts rows, the two give different figures for “number of orders”. At the second grain, an order containing three products is counted three times.

So your first task at a client is not to open a SQL editor. Write the grain as a single sentence in business language and have the person responsible on the client side confirm it.

Overwriting is a decision, not a default

Back to the Monday morning problem. With SCD Type 1, the new value replaces the old one in the dimension row, and the Kimball Group’s technique page says plainly that this destroys history. The same page warns that any affected aggregate fact tables and OLAP cubes must be recalculated.

Walk through the query behind that dashboard:

SELECT c.region, SUM(f.amount) AS doanh_so
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE f.order_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY c.region;

(doanh_so means “revenue”; “Miền Bắc” and “Miền Nam” are North and South.)

Suppose that last week the North came to 1,000, of which 300 came from customer KH-07. After a Type 1 statement such as UPDATE dim_customer SET region = 'Miền Nam', the same query returns 700 for the North and moves the 300 to the South. If the regional aggregate table has not been recalculated, it still shows 1,000, and the two dashboards now disagree.

Type 2 works differently. It adds a new row with the updated value to the dimension and assigns it a new surrogate key. That key is then used as the foreign key in the fact tables. The Kimball Group recommends at least three extra columns: an effective date, an expiration date and a current-row flag. The customer dimension in the example would look like this:

customer_key	customer_id	region	effective date	expiration date	current
101	KH-07	Miền Bắc	2024-01-01	2025-06-30	N
245	KH-07	Miền Nam	2025-07-01	9999-12-31	Y

Run the same SQL again. First-quarter orders still join to key 101, so the North stays at 1,000. Orders from July onwards point to key 245.

In a 2008 article, Kimball used himself as the example. Under Type 2, he wrote, a new employee record would be issued for Ralph Kimball, effective 18 July 2008, rather than editing the old record.

Not every column needs Type 2. Overwriting is reasonable when you are correcting a misspelled customer name. Your job is to go through the attributes one by one and ask the client whether past reports should show the value at the time or the value now.

Define once, use everywhere

When sales and finance each keep their own “customer” table, their dashboards rarely agree. The Kimball Group’s answer is the conformed dimension. You define it once, together with the people who govern the company’s data, and reuse it across fact tables. Numbers stay consistent and later development costs less.

The test for whether two dimensions are conformed is specific: their attributes must have the same column names and the same domain of values. If one table says “North” and the other says “N”, they are not conformed.

Read by problem, not cover to cover

One route to try: open the Kimball Group’s Dimensional Modeling Techniques pages and read the sections on Grain, Type 1, Type 2 and Conformed Dimensions, in that order. Then use the book to go deeper on each topic when a real case comes up. Finally, open the dbt snapshots documentation to connect the theory to code.

These materials suit developers who write SQL well but have never been responsible for a number that senior management uses to make decisions.

On a CV, a line such as “moved the customer dimension to SCD Type 2 with dbt snapshots so historical reports stay fixed when customers change region” carries much more weight than “proficient in SQL”.

Clients rarely ask whether you used Type 1 or Type 2. They ask why yesterday’s number is different from today’s. An FDE who can answer that in five minutes will be trusted with every question that follows.