Data Modeling Interview Questions (2026)

Original Table (Unnormalized)

orders_raw
+----------+------------+---------+--------+-----------+----------+
| order_id | order_date | cust_id | cust_name | products           |
+----------+------------+---------+-----------+--------------------+
| 1001     | 2025-03-15 | C1      | Alice     | Widget, Gadget     |
| 1002     | 2025-03-16 | C2      | Bob       | Widget             |
| 1003     | 2025-03-16 | C1      | Alice     | Gadget, Thingamajig|
+----------+------------+---------+-----------+--------------------+

Problem: "products" column contains comma-separated values.
This violates 1NF (each cell must hold a single atomic value).

Step 1: First Normal Form (1NF)

Eliminate repeating groups. Each row stores one product per order.

order_items_1nf
+----------+------------+---------+-----------+-------------+
| order_id | order_date | cust_id | cust_name | product     |
+----------+------------+---------+-----------+-------------+
| 1001     | 2025-03-15 | C1      | Alice     | Widget      |
| 1001     | 2025-03-15 | C1      | Alice     | Gadget      |
| 1002     | 2025-03-16 | C2      | Bob       | Widget      |
| 1003     | 2025-03-16 | C1      | Alice     | Gadget      |
| 1003     | 2025-03-16 | C1      | Alice     | Thingamajig |
+----------+------------+---------+-----------+-------------+

PK: (order_id, product)  -- composite key
Now 1NF: every cell is atomic, every row is unique.

Step 2: Second Normal Form (2NF)

Eliminate partial dependencies. order_date and cust_name depend only on order_id, not on the full composite key (order_id, product).

orders_2nf                          order_items_2nf
+----------+------------+---------+-----------+   +----------+-------------+
| order_id | order_date | cust_id | cust_name |   | order_id | product     |
+----------+------------+---------+-----------+   +----------+-------------+
| 1001     | 2025-03-15 | C1      | Alice     |   | 1001     | Widget      |
| 1002     | 2025-03-16 | C2      | Bob       |   | 1001     | Gadget      |
| 1003     | 2025-03-16 | C1      | Alice     |   | 1002     | Widget      |
+----------+------------+---------+-----------+   | 1003     | Gadget      |
  PK: order_id                                     | 1003     | Thingamajig |
                                                   +----------+-------------+
Now 2NF: no non-key column depends on part of the PK.

Step 3: Third Normal Form (3NF)

Eliminate transitive dependencies. cust_name depends on cust_id, not directly on order_id. Extract it into a separate table.

customers_3nf         orders_3nf                   order_items_3nf
+---------+-----------+  +----------+------------+---------+  +----------+-------------+
| cust_id | cust_name |  | order_id | order_date | cust_id |  | order_id | product     |
+---------+-----------+  +----------+------------+---------+  +----------+-------------+
| C1      | Alice     |  | 1001     | 2025-03-15 | C1      |  | 1001     | Widget      |
| C2      | Bob       |  | 1002     | 2025-03-16 | C2      |  | 1001     | Gadget      |
+---------+-----------+  | 1003     | 2025-03-16 | C1      |  | 1002     | Widget      |
  PK: cust_id            +----------+------------+---------+  | 1003     | Gadget      |
                           PK: order_id  FK: cust_id          | 1003     | Thingamajig |
                                                              +----------+-------------+

Now 3NF: every non-key column depends on the key, the whole key,
and nothing but the key.

Three SCD types compared

SCD Type 2: Before and After a Customer Moves

dim_customer (SCD Type 2)
+----------------+-------------+---------+----------+------------+------------+------------+
| customer_sk PK | customer_id | name    | city     | state      | eff_from   | eff_to     | is_current |
+----------------+-------------+---------+----------+------------+------------+------------+
| 1001           | C-42        | Alice   | Chicago  | IL         | 2023-01-15 | 2025-06-30 | false      |
| 1002           | C-42        | Alice   | Austin   | TX         | 2025-07-01 | 9999-12-31 | true       |
| 1003           | C-88        | Bob     | Seattle  | WA         | 2024-03-01 | 9999-12-31 | true       |
+----------------+-------------+---------+----------+------------+------------+------------+

Key design choices:
  1. customer_sk (surrogate key) is the PK. customer_id is the natural/business key.
  2. eff_to = 9999-12-31 marks the current row. is_current flag is redundant but speeds up queries.
  3. When Alice moves, the old row gets eff_to = 2025-06-30 and is_current = false.
     A new row is inserted with eff_from = 2025-07-01.
  4. Fact tables FK to customer_sk, NOT customer_id. This preserves point-in-time accuracy:
     an order placed when Alice lived in Chicago joins to the Chicago row.

Querying with SCD Type 2

-- Current state of all customers
SELECT * FROM dim_customer WHERE is_current = true;

-- Point-in-time: customer state on a specific date
SELECT * FROM dim_customer
WHERE '2024-06-15' BETWEEN eff_from AND eff_to;

-- Join fact to dimension preserving historical accuracy
SELECT f.order_date, f.total_amount, d.city, d.state
FROM fact_orders f
JOIN dim_customer d ON f.customer_sk = d.customer_sk;

The fact table joins on the surrogate key, not the business key. This means each fact row is permanently linked to the correct version of the customer dimension. If you join on customer_id instead, you get the current address for all orders, which destroys historical accuracy.