The data model

How detailed should the data model be in a system design interview?

Entities, their keys, where each lives, and one index per query. Three to six entities is right for a round. Column types, constraints and minor fields earn nothing and cost minutes you need for the deep dive. What the interviewer listens for is a key that matches how the data is read and an index that names its query.

SQL or NoSQL for the data model?

Neither as a slogan. Pick the shape per entity: rows when records refer to each other and change together, a document when a record is read whole, a wide column when one owner has a long sorted list written constantly, a log for events, a blob for large bytes. One design often uses three of them, and you say why for each.

What is the difference between metadata and data in system design?

Metadata is the small record that describes a thing and points at it, like a post row with a photo key. Data is the bytes, like the photo. Metadata is read on every request, so it is replicated and cached. Data is large and read whole by key, so it is partitioned across an object store and served through a CDN. Keep them apart.

When should I denormalise, and what does it cost?

Denormalise when a read is far more frequent than the write that changes it, and say the write cost aloud. A news feed inbox turns each feed load into one range read, and costs one write per follower on every post. With 200 followers on average and 20 feed loads per post, the lookups it removes are twenty times the writes it adds.

How do I justify an index in the interview?

Name the query as you draw it: "this index is for a new post finding its author's followers". Every index is a write on every insert and update of that entity, so one with no query behind it is pure cost. Take the queries from the operations you walked, and draw nothing else.