Applied Data Modeling: How To Answer Any Modeling Question

In a modeling interview, you get a short request. The request is called a prompt. For example: “Design the tables so the business can see revenue by country.” The person who watches you is the interviewer.

The interviewer does not want a perfect diagram. They want to hear how you think. Many people start to draw at once, and that is the usual mistake.

This chapter gives you a script of six steps. You can use it for any prompt.

It teaches no new modeling. It uses the earlier chapters in the right order.

The examples use the shop from the earlier chapters: customers, orders, order_items and products. One prompt runs through the whole chapter. A shorter second prompt comes near the end.

Do the same six steps in the same order every time. Say each step out loud.

Each section of this chapter is labelled with its step number. Then the interviewer can follow you, even when your design is not finished.

This table shows each step, what you do, and what you might say:

The order matters. The grain comes before the tables, because the grain decides what the tables can hold. The check comes before you say you are done.

The next sections run all six steps on one prompt, and name each step in the title.

Here is the prompt for this chapter:

Do not draw yet. First, ask what the business will do with the tables. A late answer can mean you start again.

These are five questions to ask:

1. Which questions must the tables answer? List them.
2. What is “revenue”? Do refunded and pending orders count?
3. What is a “unit”? One item, or one order line?
4. What is “each day”? The day the customer ordered, or the day it shipped?
5. Does the past change? If a customer moves country, which country does an old order show?

The talk for this prompt might go like this:

• You: “Does revenue include refunded orders? Is a unit one item? Which date do you use?”
• Interviewer: “Paid orders only. A unit is one item sold. Use the order date.”
• You: “If a customer moves country, should old orders keep the old country?”
• Interviewer: “Yes. Old orders keep the country they had.”

Now you have two questions to answer and four rules. The rules are: paid orders only, a unit is one item, use the order date, and old orders keep the old country.

Say them back in one line.

Sometimes the interviewer says, “You decide.” Pick a simple rule and say it out loud. For example: “I will count paid orders only.”

Grain is what one row of a table means. Chapter 4 taught it. Say it as one sentence for each fact table, before you draw any table.

Look at the two questions from the clarify step. Revenue belongs to an order.

Units belong to an item on an order. These are two different grains.

This table shows the rows each question needs. Compare the two:

Try to use one table for both. Put amount on each item row. Orders 101 and 106 have two items each, so their amounts count twice.

The paid total would be 40 + 40 + 25 + 15.50 + 80 + 80 = 280.50. The true total is 160.50.

The SQL chapters called this fan-out: a join repeats a row, so a number is added twice. No error appears. The number is just wrong.

So you say two grain sentences, and you build two fact tables:

• fact_orders: one row is one order.
• fact_order_items: one row is one product on one order.

Now pick the facts and the dimensions. A fact table holds the numbers you add up. A dimension holds the labels you group by, such as country or product name.

Sort the words of the prompt. This table shows where each word goes:

Look at the key marks in the picture. A foreign key (FK) points at a dimension.

fact_orders has FKs to dim_customer and dim_date. fact_order_items has FKs to dim_product and dim_date, and its key is order_id with product_key. This is the star shape from chapter 9:

Notice that fact_order_items has the date and the status too. They are copied down from the order.

The unit query can then filter paid items without a join to fact_orders. The copy is safe, because you never add them up.

Say what you drew: “Two stars share one date table. I keep the dimensions flat, with no snowflake, so each question needs one join.”

Omar has no orders. He is still in dim_customer, and that is fine.

A dimension lists everyone. A fact lists only what happened.

Say the keys out loud. Each dimension gets a surrogate key. That is a new number made by the warehouse, as chapter 3 taught.

In this chapter the new keys use the same numbers as the old ids, to keep the rows easy to read. The date key is the date as a number: 2026-01-03 becomes 20260103.

The key of each fact is its grain. In fact_orders it is order_id. In fact_order_items it is order_id and product_key together.

Now handle the missing country. Dev has no country.

Store the word Unknown in dim_customer. Then the report shows a clear label, not a blank.

History for country

The interviewer said old orders keep the old country. That needs history, as in chapter 11. Use a type 2 change: add a new row, and never overwrite the old one.

Suppose Asha moves to DE on 2026-02-01. This table shows her two rows in dim_customer. A valid_to of NULL means the row is current:

Orders 101 and 102 still point at customer_key 1, so they stay in IN. A new order after 2026-02-01 uses customer_key 6, so it counts in DE.

The cost is real. One customer is now two rows, so COUNT(*) counts Asha twice.

Use COUNT(DISTINCT customer_id) instead. It counts each customer once.

What you say: “Surrogate keys, flat dimensions, and type 2 history on country. Each fact is keyed by its grain.”

Before you say “done”, prove the model works. Run two or three real questions on the sample rows. Say the answer you expect before you run each one.

What you say: “I expect 160.50 for revenue and 9 for units. Now I run both.”

Question 1 is revenue by country by day. This query adds up the paid amounts:

SELECT f.date_key, c.country, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY f.date_key, c.country
ORDER BY f.date_key;

Add the four numbers: 40 + 25 + 15.50 + 80 = 160.50. That equals the paid total in orders. The totals match.

Question 2 is units by product by day. This query adds up the paid quantities:

SELECT i.date_key, p.name AS product, SUM(i.quantity) AS units
FROM fact_order_items AS i
JOIN dim_product AS p ON p.product_key = i.product_key
WHERE i.status = 'paid'
GROUP BY i.date_key, p.name
ORDER BY i.date_key, p.name;

Check the rows. Pen is 2 + 3 = 5 over two days. The other four paid items are 1 each.

In total, 5 + 4 = 9. That is the sum of the paid quantities.

Last, name the edge cases the sample rows contain. Order 103 is refunded. Order 105 is pending with a NULL amount.

Dev has no country. Omar has no orders. Say how the model handles each one.

The last step is to say what your design costs. Every design gives something up.

A trade-off is what your design costs you. Name one choice, its benefit, and its cost.

This table shows three choices from the shop prompt:

Now put the six steps together as one answer. This is what you might say in about a minute:

1. “Two questions: paid revenue by country by day, and units by product by day.”
2. “Revenue is per order. Units are per item. So I use two facts.”
3. “fact_orders has amount. fact_order_items has quantity. Country, product and date are dimensions.”
4. “I use surrogate keys. Country keeps its history, so old orders keep the old country.”
5. “I checked it. Paid revenue is 160.50 and paid units are 9. Both match the source.”
6. “The trade-off: two facts need two queries, but they never count an order twice.”

Try the script on a new prompt: “Track subscription plans and monthly recurring revenue.”

This is the idea of the practice exercise “Subscription warehouse grain”.

You can practise this prompt in the lab below.

MRR means monthly recurring revenue: the money one subscription pays each month. This table runs the six steps with short answers:

Watch out: you can add mrr across customers in one month. Do not add it across months. A customer who pays 20.00 each month still has an MRR of 20.00, not 200.00 after ten months.

Each exercise opens on the ERD canvas. Run the six steps on each one, and say them out loud.