Top 14 Data modelling Interview Questions
Browse 14 real-world technical and behavioral interview questions about Data modelling. Review scenarios, edge cases, and architectural best practices.
You are building a user-profile service. Key-value store or document database: what decides it?
It is decided by everything besides fetching a profile by user id. If every read is by id and every write replaces the whole profile, a key-value store is enough. Login by email, unique emails and field-level updates push towards a document database, or towards index items you maintain yourself.
Users in three countries book appointments, and one of those countries has just changed when its clocks move. What do you store, and what do you refuse to store?
For a future appointment, store the local wall-clock time plus an IANA time zone ID such as Europe/Lisbon, and treat any UTC instant as a cache you recompute when time zone rules change. Refuse fixed offsets, abbreviations like IST, and server-local times. Past events are stored as UTC instants.
You are mapping data from a legacy system into a replacement and the two define a customer differently. How do you work that out?
Agree the business meaning and the grain of each entity before touching field names, profile the real data to find the overloaded and sentinel values, then write a mapping where every target field has a source, a rule, a default and a decision for records that cannot be mapped.
How does the reserve figure your systems produce end up changing next year's rates, and where does that loop break?
Reserving estimates what business already written will ultimately cost, and pricing needs that cost attributed back to the characteristics that were rated. The loop breaks over which period a loss belongs to, over recent years whose figures are still moving, and over premium earned at rates nobody charges any more.
What is a loss development triangle, and what does the actuary need from your claims system that reporting does not?
A loss development triangle groups claims by accident or underwriting period and development age so actuaries can estimate ultimate cost, IBNR and reserve movement. It needs immutable claim transactions, evaluation-date reconstruction and clear paid versus incurred measures.
We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.
Give every entity a surrogate primary key but keep a unique constraint on the business identifier, and model a negotiated price as a row with a validity period rather than a mutable column - then copy the agreed price onto the order line so history cannot be rewritten.
How would you model physical and logical network resources, and what do you do when discovery disagrees with the inventory?
Logical network inventory should separate physical equipment, logical resources and services, then reconcile discovery as observed state against intended inventory. Do not overwrite the record blindly; classify gaps as faults, drift or orphaned resources. It also connects resource management to the point an interviewer is testing.
How would you model sex and gender in a patient record?
Not as one field. Administrative sex, recorded sex at birth, gender identity, name and pronouns to use, and the sex parameter a clinical calculation needs are separate elements with separate provenance and separate consumers, and each has to be able to change without rewriting the others.
One celebrity account is ninety per cent of your write traffic on a single partition. What do you do about a key you cannot rebalance?
A hot partition from one account cannot be fixed by ordinary rebalancing because it is one key. Split the key into write-sharded sub-keys or aggregate writes before storage; both choices move cost to reads, ordering, rollups or approximation.
A claim payment goes out. How does your system work out how much of it a reinsurer owes, and what does that require you to have kept?
Cession allocates each payment across the treaties covering the policy, and which treaties those are depends on when the policy was written rather than when the claim happened. So a payment must stay sliceable by treaty, layer, accident date and underwriting year long after the file closed.
Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?
Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.
Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?
Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember. Model the lifecycle explicitly instead.
The actuarial team says they cannot use the claims data your reporting warehouse produces. What are they missing?
Reporting shows the current state of each claim, and actuaries need the state as at each past valuation date, cut by accident period and development period. That is a different shape of data, and it cannot be reconstructed from a table that overwrites a claim's figures as it develops.
Analysts want yesterday's numbers to still match after a late-arriving correction. What does the warehouse have to record?
Two time axes, not one: when the fact was true in the world, and when this warehouse first learned it. Corrections append a new version with a knowledge timestamp instead of updating in place, so a report becomes a query with an as-of predicate and reproduces for ever - and the figure you published should be snapshotted alongside it rather than reconstructed later.