Mock Interview

Data Modeling Interview Questions and Practice Scenarios

A useful data modeling answer makes business rules testable. Work through these original questions and rental examples, then explain why your keys, relationships, and reporting grain fit the problem. State assumptions before defending a schema.

Start with the workload: application OLTP or analytics?

Ask whether the system must confirm a rental safely or compare rental activity over time. For an OLTP application, prioritize valid state transitions, ownership, and concurrent writes. A normalized relational model separates customers, bookings, and payments. For a warehouse, define a measurable business event and preserve the historical context needed for reporting.

These are different workloads, not competing definitions of a good schema. Clarify cancellation rules, reporting dates, money units, and expected access patterns before naming tables. The examples below assume a single currency and rental charges excluding tax.

Eight foundational questions with answer notes

  1. How do conceptual, logical, and physical models differ?

    Conceptual modeling identifies business concepts; logical modeling specifies attributes, keys, and relationships; physical modeling chooses database types, indexes, and storage details. Explain the same booking at each level rather than listing definitions alone.

  2. When would you choose a surrogate key?

    Use a stable internal identifier when business identifiers can change. Preserve a required business identifier with a separate uniqueness constraint; adding an ID does not eliminate business duplicates.

  3. What does normalization solve?

    Separate attributes according to their dependencies: a customer's name belongs with the customer, not every booking. This avoids inconsistent edits. A booking's agreed charge is a historical transaction attribute, not today's catalog price.

  4. How do you represent many-to-many relationships?

    Introduce booking_items between bookings and assets. Its composite key prevents listing the same physical asset twice within one booking; a different business rule might require a separate line identifier.

  5. What does a foreign key guarantee?

    A required reference must identify an existing parent. It does not guarantee that every booking has a child item, so validate completeness when confirming a booking.[2]

  6. How should missing values behave?

    Define whether NULL means unknown or inapplicable. Required charges need NOT NULL as well as a nonnegative CHECK: PostgreSQL accepts a CHECK whose expression evaluates to NULL.[2]

  7. When is denormalization defensible?

    Use it for a demonstrated access need, with an authoritative source and refresh policy. A cached booking total needs reconciliation; duplicating mutable customer details without a purpose creates competing truths.

  8. Which indexes would you investigate first?

    Start from actual filters and joins, such as bookings(customer_id, status). Explain the write and storage costs, then inspect query plans with realistic distributions instead of indexing every column.

Rental scenario: entities, cardinality, and constraints

Assume one booking rents physical assets for one shared interval. Customer and Asset have primary keys customer_id and asset_id. Booking has booking_id, a required customer_id foreign key, status, starts_at, and ends_at. One customer has zero or many bookings; each booking has exactly one customer.

BookingItem uses primary key (booking_id, asset_id), with both columns foreign keys and a required nonnegative charge_cents. Payment has payment_id, a required booking_id foreign key, and positive amount_cents. Bookings and assets each have many items; a booking has zero or many payments. Require at least one item before confirmation.

Store UTC times and require ends_at greater than starts_at. Treat intervals as half-open, allowing adjacent rentals. An ordinary row CHECK cannot prevent overlapping bookings.[2] A production option is an asset-and-interval reservation table with an appropriate PostgreSQL exclusion constraint. The minimal example does not implement availability enforcement. Restrict deletion of referenced assets to preserve history.

Worked SQL: total charges and collections without fan-out

Input: Ada has confirmed booking 100 and cancelled booking 101; Ben has confirmed booking 102; Cy has none. Assets 10 and 11 are a camera and tripod. Each payment is a completed collection, with no refunds in this exercise. Copy this self-contained SQL into your own database environment:

WITH customers (customer_id, name) AS (
  VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')
), bookings (booking_id, customer_id, status) AS (
  VALUES (100, 1, 'confirmed'),
         (101, 1, 'cancelled'),
         (102, 2, 'confirmed')
), booking_items (booking_id, asset_id, charge_cents) AS (
  VALUES (100, 10, 5000), (100, 11, 2000),
         (101, 10, 9000), (102, 10, 4000),
         (102, 11, 4000)
), payments (payment_id, booking_id, amount_cents) AS (
  VALUES (900, 100, 3000), (901, 100, 4000),
         (902, 102, 1000)
), item_totals AS (
  SELECT booking_id, SUM(charge_cents) AS booked_cents
  FROM booking_items
  GROUP BY booking_id
), payment_totals AS (
  SELECT booking_id, SUM(amount_cents) AS paid_cents
  FROM payments
  GROUP BY booking_id
)
SELECT c.customer_id, c.name,
       COALESCE(SUM(i.booked_cents), 0) AS booked_cents,
       COALESCE(SUM(p.paid_cents), 0) AS paid_cents
FROM customers AS c
LEFT JOIN bookings AS b
  ON b.customer_id = c.customer_id
 AND b.status = 'confirmed'
LEFT JOIN item_totals AS i ON i.booking_id = b.booking_id
LEFT JOIN payment_totals AS p ON p.booking_id = b.booking_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Expected output, in cents:

customer_id | name | booked_cents | paid_cents
1           | Ada  | 7000         | 7000
2           | Ben  | 8000         | 1000
3           | Cy   | 0            | 0

Pre-aggregate each child to one row per booking. Keep the status filter in ON so customers without confirmed bookings remain. COALESCE turns missing totals into zero. These are charges and collections, not a claim about recognized revenue.

Valid alternatives and double-counting traps

Joining raw items directly to raw payments creates four rows for booking 100: two items multiplied by two payments. Its charges incorrectly total 14000. SUM(DISTINCT charge_cents) is not a repair: Ben's two legitimate 4000-cent lines would collapse to 4000 instead of 8000.

Correlated aggregate subqueries are a valid alternative; compare their plans with pre-aggregation. If one booking always covers exactly one asset, a required asset_id on Booking is simpler. If assets can repeat across separate intervals within a booking, use line_id and put intervals on lines. Choose the model that matches those rules.

Warehouse case: declare grain before dimensions and facts

For rental-charge analysis, declare the grain first: one confirmed booking item at confirmation, uniquely identified here by (booking_id, asset_id). Kimball defines grain by what one fact record represents, before selecting dimensions and compatible measurements.[1]

Use customer, asset, and confirmation-date dimensions with surrogate keys. The fact contains charge_cents and item_count = 1; booking_id can remain a degenerate identifier. Preserve the confirmation timestamp and source identity for traceability. Do not repeat the whole booking payment on each item. Collections belong in a separate payment-grain fact. Aggregate both facts to compatible dimensions before comparing them; never join their raw rows on customer alone.

SCD Type 2, late arrivals, and corrections

Suppose customer 1 changes segment from Student to Business on April 9. In this proposed Type 2 design, customer key 21 covers times before that boundary; key 34 covers times from it onward. Store effective_from and effective_to as nonoverlapping, half-open intervals. Resolve the fact's customer key using confirmation time, not load time.

A booking confirmed April 8 but received April 10 still uses key 21. If the correct dimension version arrives later, assign a designated unknown key temporarily and rekey affected facts after reconciliation. For a corrected charge, update the existing fact by its source identity rather than inserting a second copy. Preserve an audit trail. A later cancellation needs an explicit policy, such as a separate reversal event, rather than silently erasing confirmation history.

Explain the design aloud, then check its weak points

  1. State the workload and assumptions.
  2. Describe entities, relationships, and row grain.
  3. Explain keys, constraints, and one valid alternative.
  4. Walk through Ada's totals and Cy's zero row.
  5. Challenge the design with concurrent rentals and late corrections.

Check: can every join's cardinality be explained? Are units and cancellation rules explicit? Can retries duplicate facts? Does historical reporting use the right dimension version?

Use the homepage setup to rehearse your explanation by voice or text. These are original practice exercises, not an employer question bank. This guide is not a SQL runner, ER editor, or database auto-grader.

Technical sources

  1. [1] Declaring the Grain — Ralph Kimball
  2. [2] PostgreSQL documentation: Constraints

Practice explaining your data model

Choose a role or paste a valid job description in the homepage setup. Rehearse your design decisions by voice or text, then review feedback on your explanation.

Start interview on the homepage