Loading...
Loading...
Browse 12 real-world technical and behavioral interview questions about Data modelling. Review scenarios, edge cases, and architectural best practices.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.