Data Modelling Questions

Modelling questions sound open-ended and are graded on three specific things: whether you state the grain, whether your keys survive change, and whether you know what a type 2 dimension does to every downstream join.

The question

“Design the warehouse tables for an online bookshop. Analysts need revenue by customer, by country, by book and by month.”

Start with the grain

Before any table, one sentence:

“The fact table is one row per order line — a single book on a single order. That is the lowest grain the questions need, and revenue by order, customer or month all aggregate up from it.”

Getting this wrong is unrecoverable. One row per order cannot answer “revenue by book”; one row per order line per day would multiply revenue by the number of days. Say the grain, then say what it lets you answer.

import duckdb
con = duckdb.connect()

con.execute("""
    create table fct_order_line (
        order_line_sk   bigint,          -- surrogate PK
        order_id        bigint,          -- natural key, degenerate dimension
        line_number     int,
        customer_sk     bigint,          -- FK to dim_customer
        book_sk         bigint,          -- FK to dim_book
        order_date_sk   int,             -- FK to dim_date (YYYYMMDD)
        quantity        int,
        unit_price      decimal(10,2),
        discount_amount decimal(10,2),
        line_revenue    decimal(10,2)    -- additive measure
    )
""")
print(con.execute("describe fct_order_line").df()[["column_name","column_type"]].to_string(index=False))

     column_name   column_type
   order_line_sk        BIGINT
        order_id        BIGINT
     line_number       INTEGER
     customer_sk        BIGINT
         book_sk        BIGINT
   order_date_sk       INTEGER
        quantity       INTEGER
      unit_price DECIMAL(10,2)
 discount_amount DECIMAL(10,2)
     line_revenue DECIMAL(10,2)

Two things to point out while writing it. order_id sits in the fact table without a dimension — a degenerate dimension, and the right choice for an identifier with no attributes of its own. And every measure is additive: summing line_revenue across any combination of dimensions is valid, which is the property that makes a fact table useful.

Name the measure types, because it is a common follow-up:

Storing a ratio in a fact table is a trap: avg(margin_percent) is not the margin percent.

Surrogate keys, and why

“Why not just use customer_id from the source system?”

con.execute("""
    create table dim_customer (
        customer_sk    bigint,           -- surrogate, generated here
        customer_id    varchar,          -- natural key from the source
        full_name      varchar,
        country        varchar,
        valid_from     date,
        valid_to       date,
        is_current     boolean
    )
""")
con.execute("""
    insert into dim_customer values
        (1, 'CUST-001', 'Ada Lovelace', 'GB', date '2025-11-02', date '2026-01-10', false),
        (2, 'CUST-001', 'Ada Lovelace', 'NL', date '2026-01-10', date '9999-12-31', true),
        (3, 'CUST-002', 'Grace Hopper', 'US', date '2025-11-04', date '9999-12-31', true),
        (4, 'CUST-003', 'Alan Turing',  'GB', date '2026-01-03', date '9999-12-31', true)
""")
print(con.execute("select * from dim_customer order by customer_sk").df().to_string(index=False))

 customer_sk customer_id     full_name country  valid_from    valid_to  is_current
           1    CUST-001  Ada Lovelace      GB  2025-11-02  2026-01-10       False
           2    CUST-001  Ada Lovelace      NL  2026-01-10  9999-12-31        True
           3    CUST-002  Grace Hopper      US  2025-11-04  9999-12-31        True
           4    CUST-003   Alan Turing      GB  2026-01-03  9999-12-31        True

Four reasons, in the order they matter:

1. The natural key is no longer unique once you keep history — CUST-001 appears twice. The surrogate is what the fact table can point at unambiguously.
2. Natural keys change. A source system reformats CUST-001 to C-000001 and every foreign key in the warehouse has to change with it.
3. Integers join faster than varchars, and take less space in a large fact table.
4. Late-arriving dimensions need a placeholder row — a surrogate -1 for “unknown” lets a fact land without a matching dimension rather than being dropped.

That last one is worth showing:

con.execute("insert into dim_customer values (-1, 'UNKNOWN', 'Unknown customer', 'UNKNOWN', date '1900-01-01', date '9999-12-31', true)")
print(con.execute("select customer_sk, customer_id, full_name from dim_customer where customer_sk = -1").df().to_string(index=False))

 customer_sk customer_id        full_name
          -1     UNKNOWN Unknown customer

Now an order for a customer that has not loaded yet joins to -1 instead of vanishing on an inner join — the same £19.99 problem from lesson 1, solved at the model level.

The type 2 question

This is the part of the round most candidates get half right.

con.execute("""
    create table fct_orders as select * from (values
        (101, 'CUST-001', date '2025-12-15', 100.00),
        (102, 'CUST-001', date '2026-02-01', 150.00),
        (103, 'CUST-002', date '2026-01-20',  80.00)
    ) t(order_id, customer_id, order_date, revenue)
""")

Ada was in GB until 10 January and NL after. Order 101 is a GB order; order 102 is NL. The naive join gets it wrong:

print(con.execute("""
    select o.order_id, o.order_date, d.country, o.revenue
    from fct_orders o
    join dim_customer d on d.customer_id = o.customer_id
    order by o.order_id, d.country
""").df().to_string(index=False))

 order_id order_date country  revenue
      101 2025-12-15      GB    100.0
      101 2025-12-15      NL    100.0
      102 2026-02-01      GB    150.0
      102 2026-02-01      NL    150.0
      103 2026-01-20      US     80.0

Five rows from three orders, and revenue is now £660 instead of £330. Joining to a type 2 dimension without a filter multiplies every fact by the number of versions. This is the single most common data modelling bug in production, and the interviewer is watching for whether you anticipate it.

Two correct joins, for two different questions:

print("current attributes (who are they now):")
print(con.execute("""
    select o.order_id, d.country, o.revenue
    from fct_orders o
    join dim_customer d on d.customer_id = o.customer_id and d.is_current
    order by o.order_id
""").df().to_string(index=False))

print("\npoint-in-time (where were they then):")
print(con.execute("""
    select o.order_id, o.order_date, d.country, o.revenue
    from fct_orders o
    join dim_customer d
      on d.customer_id = o.customer_id
     and o.order_date >= d.valid_from
     and o.order_date <  d.valid_to
    order by o.order_id
""").df().to_string(index=False))

current attributes (who are they now):
 order_id country  revenue
      101      NL    100.0
      102      NL    150.0
      103      US     80.0

point-in-time (where were they then):
 order_id order_date country  revenue
      101 2025-12-15      GB    100.0
      102 2026-02-01      NL    150.0
      103 2026-01-20      US     80.0

Three rows both times, £330 both times, and different answers — deliberately. Order 101 is GB historically and NL by today’s attributes. Which one is correct depends on the question, and saying that is the whole answer:

“Revenue by country restated to today’s customer records uses is_current. Revenue by country as it was reported at the time uses the point-in-time join. Finance almost always wants the second, because otherwise a customer moving country changes a number that was signed off last quarter.”

The best answer to the join problem is to avoid it entirely — resolve the surrogate key at load time:

con.execute("""
    create table fct_orders_resolved as
    select o.order_id, o.order_date, o.revenue,
           coalesce(d.customer_sk, -1) as customer_sk
    from fct_orders o
    left join dim_customer d
      on d.customer_id = o.customer_id
     and o.order_date >= d.valid_from and o.order_date < d.valid_to
""")
print(con.execute("""
    select f.order_id, f.customer_sk, d.country, f.revenue
    from fct_orders_resolved f join dim_customer d using (customer_sk)
    order by f.order_id
""").df().to_string(index=False))

 order_id  customer_sk country  revenue
      101            1      GB    100.0
      102            2      NL    150.0
      103            3      US     80.0

Now every analyst joining on customer_sk gets the historically correct answer without knowing the dimension is type 2. Push the hard join into the pipeline, not into every query — that sentence scores.

The intervals must abut exactly, with no gaps and no overlaps:

print(con.execute("""
    select customer_id, count(*) as versions,
           bool_and(valid_to > valid_from) as intervals_valid,
           max(valid_to) = date '9999-12-31' as has_open_row
    from dim_customer where customer_sk > 0 group by 1 order by 1
""").df().to_string(index=False))

 customer_id  versions  intervals_valid  has_open_row
    CUST-001         2             True          True
    CUST-002         1             True          True
    CUST-003         1             True          True

Note 9999-12-31 rather than NULL for the open row. Both are used; the far-future date makes the point-in-time predicate a plain < with no coalesce, and forgetting the coalesce on a NULL upper bound is a very common bug.

Which SCD type

The interview answer is to distinguish corrections from changes: a misspelled name is a type 1 overwrite, because there was never a time the wrong spelling was true. A customer moving country is type 2, because both were true at different times.

Star, snowflake, or one big table

con.execute("""
    create table obt_order_lines as
    select f.order_id, f.customer_sk, d.customer_id, d.full_name, d.country,
           f.order_date, f.revenue
    from fct_orders_resolved f join dim_customer d using (customer_sk)
""")
print(con.execute("select * from obt_order_lines order by order_id").df().to_string(index=False))

 order_id  customer_sk customer_id     full_name country order_date  revenue
      101            1    CUST-001  Ada Lovelace      GB 2025-12-15    100.0
      102            2    CUST-001  Ada Lovelace      NL 2026-02-01    150.0
      103            3    CUST-002  Grace Hopper      US 2026-01-20     80.0

The senior answer names the consumer: “Star for the BI layer, because Looker and Power BI expect it and the metric definitions live in one place. A wide denormalised table alongside it for the feature pipeline, generated from the star — a columnar warehouse makes the storage cheap and the data scientists stop writing their own joins.” Both, generated from one source.

Fact table types

print(con.execute("""
    select 'transaction' as fact_type, 'one row per event, insert-only' as grain,
           'order lines, clicks, payments' as example
    union all select 'periodic snapshot', 'one row per entity per period',
           'daily account balance, monthly inventory'
    union all select 'accumulating snapshot', 'one row per process, updated in place',
           'order lifecycle with placed/picked/shipped/delivered dates'
    union all select 'factless', 'one row per relationship, no measures',
           'student enrolled in course, promotion applied to product'
""").df().to_string(index=False))

            fact_type                                grain                                                   example
          transaction       one row per event, insert-only                             order lines, clicks, payments
    periodic snapshot        one row per entity per period                  daily account balance, monthly inventory
accumulating snapshot one row per process, updated in place order lifecycle with placed/picked/shipped/delivered dates
             factless   one row per relationship, no measures  student enrolled in course, promotion applied to product

The accumulating snapshot is the one worth mentioning unprompted for an orders question: it gives you “average days from placed to delivered” without a self-join, at the cost of being updated rather than append-only.

Many-to-many

“A book can have several authors. How do you model it?”

con.execute("""
    create table bridge_book_author as select * from (values
        (1, 10, 0.5), (1, 11, 0.5), (2, 10, 1.0)
    ) t(book_sk, author_sk, weight)
""")
print(con.execute("""
    select book_sk, count(*) as authors, sum(weight) as total_weight
    from bridge_book_author group by 1 order by 1
""").df().to_string(index=False))

 book_sk  authors  total_weight
       1        2           1.0
       2        1           1.0

A bridge table with an allocation weight. Without the weight, joining a fact through the bridge double-counts revenue for co-authored books; with it, revenue allocates and the totals still reconcile. Mention that the un-weighted “revenue by author” and the weighted version answer different questions, and that both are legitimate as long as the choice is documented.

The scoring

Practice

 5 rows from 3 orders — revenue £660 instead of £330

current:        101 → NL
point-in-time:  101 → GB

 order_id  customer_sk country  revenue
      101            1      GB    100.0

 customer_sk customer_id        full_name
          -1     UNKNOWN Unknown customer

Next: Spark and distributed systems questions — shuffles, skew, and the joins that fall over.