DWH Interview Question 8 - What is data modeling?

Data Warehouse Interview Q&A · Question 8

In this post we are going to cover data modeling: what it is, why we do it, and the three levels interviewers expect you to name.

Data modeling is the process of designing how data is structured: which tables we need, their columns and data types, the keys, and the relationships between the tables. The goal is data that is correct, consistent and easy to use.

It happens at three levels: conceptual, logical and physical. Mention all three with an example, and you will stand out. We will build a tiny customers and orders model through all three levels, and finish with real SQL for the physical model.

Question 8 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Why model data at all?
3. Level 1: The conceptual model
4. Level 2: The logical model
5. Let's create some sample data
6. Step 1: The physical model
7. Step 2: Load rows that follow the rules
8. Step 3: Query across the relationship
9. OLTP vs warehouse modeling
10. Follow-up questions interviewers ask
11. The full script

The interview answer

Say this first

Data modeling is the process of designing how data is structured: which tables we need, their columns and data types, the keys, and the relationships between them, so data is correct, consistent and easy to use. It is done at three levels: conceptual, logical and physical.

Why model data at all?

Imagine TB Shop keeps everything in one big sheet, with no rules at all.

orders_sheet4 rows · 4 columns

order_id	customer	email	order_date
1001	Sara Khan	sara@example.com	2026-01-05
1002	Omar Ali	omar@example.com	2026-01-09
1003	Sara Khan	sara.k@example.com	2026-02-02
1004	Ghost User	???	2026-02-31

Look at what goes wrong:

• Same customer, two different emails - Sara Khan appears as sara@example.com on order 1001 and sara.k@example.com on order 1003. Every order row repeats the customer's details, so when she changes her email and we miss a row, the data disagrees with itself.
• A customer who does not exist - nothing stops someone typing an order for "Ghost User" with an email of ???.
• An impossible date - order 1004 is dated 2026-02-31. There is no 31st of February.

A data model fixes this by splitting the data into well-defined tables, with keys, types and rules. The database can then protect the data for us.

Level 1: The conceptual model

This is the big picture, in business language. Which things matter to the business, and how are they related?

For TB Shop: a Customer places Orders, and an Order contains Products. That's it. No columns, no data types.

The audience is business stakeholders. We use it to agree on the scope and the vocabulary before anyone writes SQL.

Level 2: The logical model

Now each entity gets its attributes, and we mark the keys.

Customer3 rows · 2 columns

attribute	key
customer_id	PK
name
email

A customer has an ID, a name and an email. customer_id uniquely identifies a customer, so it is the primary key.

Order3 rows · 2 columns

attribute	key
order_id	PK
customer_id	FK
order_date

An order has an ID, a customer and a date. The customer_id inside an order points back to the customer, so it is a foreign key.

We also define the relationship with its cardinality: one customer can have many orders, and each order belongs to exactly one customer.

The logical model is still independent of any database product. No VARCHAR, no indexes yet. That comes next.

Let's create some sample data

In this post the tables themselves are the physical model, so we create them in Step 1. The setup script only removes any leftover tables with the same names.

Sample data — run this first

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

Step 1: The physical model

Level three is the physical model: the real thing, for a specific database. Let's write the DDL. We use exact data types like INT, VARCHAR and DATE, NOT NULL where a value is required, a primary key on each table, a unique constraint on email, and a foreign key from orders to customers.

Statement

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50) NOT NULL,
  email       VARCHAR(100) UNIQUE
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL
              REFERENCES customers(customer_id),
  order_date  DATE NOT NULL
);

The rules from the logical model are now enforced by the database itself. An order cannot point to a customer that does not exist, two customers cannot share an email, and order_date must be a real date, so 2026-02-31 would be rejected. The physical model also covers indexes, partitioning and naming rules, everything the database needs to store and find the data efficiently.

Step 2: Load rows that follow the rules

Now let's load two customers and three orders. Every order points to an existing customer, so the foreign key accepts them.

Statement

INSERT INTO customers VALUES
  (1, 'Sara Khan', 'sara@example.com'),
  (2, 'Omar Ali',  'omar@example.com');
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05'),
  (1002, 2, '2026-01-09'),
  (1003, 1, '2026-02-02');

Sara is customer 1 with one email, stored once. Orders 1001 and 1003 both point to her by customer_id, and order 1002 points to Omar.

Step 3: Query across the relationship

Because the relationship is defined by keys, joining the two tables is simple and reliable. Let's count orders per customer.

Query

SELECT c.name, COUNT(*) AS orders
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY c.name;

Result2 rows · 2 columns

name	orders
Omar Ali	1
Sara Khan	2

Omar Ali has 1 order and Sara Khan has 2. Compare that with the spreadsheet at the start, where Sara's two orders carried two different emails and we could not be sure they were the same person.

OLTP vs warehouse modeling

Interviewers also like to hear that there are two main styles of data modeling.

	OLTP modeling	Warehouse modeling
STYLE	Entity-relationship, normalized (3NF)	Dimensional: facts + dimensions
SHAPE	Many small related tables	Star schema
GOAL	No duplicates, fast and safe writes	Simple and fast analysis

For OLTP systems we use normalized, entity-relationship models, usually in third normal form, so there are no duplicates and writes are fast and safe. For data warehouses we use dimensional models: fact tables surrounded by dimension tables in a star schema, so analysis is simple and fast. The next posts build both: normalization first, then dimensional modeling.

Follow-up questions interviewers ask

Logical vs physical model?

Logical: entities, attributes, keys and cardinality, independent of any database. Physical: the real DDL with data types, constraints and indexes for one database.

Who does data modeling?

Data architects and data modelers, but data engineers and analytics engineers do it daily.

Which tools?

ERwin, ER/Studio, draw.io, dbdiagram, plus the DDL scripts in version control.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #8 - What is data modeling?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q08;  SET search_path TO dwh_q08;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;


-- Step 1: Physical model: create the tables (PostgreSQL syntax, standard SQL types)
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50) NOT NULL,
  email       VARCHAR(100) UNIQUE
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL
              REFERENCES customers(customer_id),
  order_date  DATE NOT NULL
);

-- Step 2: Load a few rows that follow the rules
INSERT INTO customers VALUES
  (1, 'Sara Khan', 'sara@example.com'),
  (2, 'Omar Ali',  'omar@example.com');
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05'),
  (1002, 2, '2026-01-09'),
  (1003, 1, '2026-02-02');

-- Step 3: Query across the relationship
SELECT c.name, COUNT(*) AS orders
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY c.name;

Key points

• Designing tables, columns, types, keys and relationships
• Conceptual: entities and relationships, in business words
• Logical: attributes, primary and foreign keys, cardinality
• Physical: real DDL: types, constraints, indexes
• OLTP uses normalized models; warehouses use dimensional models

Next in this series

Question 9 — What is wrong with one flat table? What's wrong with keeping everything in one flat table?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.