Interview questions

Interview questions (with the counter-questions you'll get)

Q1. SQL or NoSQL: how do you decide?

They're testing: reasoning from access patterns, not fashion.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** I start from the data and how it's read and written. If I need multi-row transactions, strong rules, and flexible queries, I use a relational database; it's also my default when the access patterns aren't clear yet. If the pattern is narrow and extreme, like key lookups at huge scale, self-contained documents of varying shape, a massive write stream, or connection queries, a NoSQL family built for that pattern fits better. Many systems use both.
3
4**Counter-questions:**
5- *"Doesn't NoSQL scale better?"* → Many NoSQL databases are designed to split across machines from the start, which helps at very large scale. But relational databases scale a long way with indexes, caching, replicas and sharding; most products never outgrow one well-run SQL database.
6- *"What would make you switch later?"* → A specific bottleneck, like a write volume one primary can't handle, that the relational design can't fix cheaply.
7
8**Trap:** "NoSQL because it's faster" or "SQL because it's safer", with no access pattern.

Q2. Explain ACID.

They're testing: precise definitions with an example.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Atomicity: a transaction's changes all happen or none do. Consistency: each transaction leaves the data obeying its constraints. Isolation: concurrent transactions don't see each other's partial work, to a degree set by the isolation level. Durability: committed data survives crashes, usually via a write-ahead log. Example: placing an order saves the order and reduces stock atomically, and stock can never go below zero.
3
4**Counter-questions:**
5- *"Is ACID's C the same as CAP's C?"* → No. ACID's C is about the database's rules holding; CAP's C is about every reader seeing the latest write across replicas (Chapter 16).
6- *"How is durability implemented?"* → The change is written to a log on disk before the commit is confirmed, so it can be replayed after a crash.
7
8**Trap:** mixing up consistency and isolation.

Q3. What is normalisation, and when would you denormalise?

They're testing: trade-off thinking.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Normalisation stores each fact once and links with keys, which avoids contradictions and makes updates cheap, at the cost of joins on read. I'd denormalise when a hot read needs data from many tables and the joins are too slow or impossible, for example across shards, or for a precomputed feed or summary. Then I accept that updates must touch every copy and plan how to keep copies in sync, often asynchronously.
3
4**Counter-questions:**
5- *"How do you keep copies in sync?"* → Update them in the same transaction if possible; otherwise publish a change event and let consumers update copies (Chapter 23), accepting brief staleness.
6
7**Trap:** "always normalise" or "joins are slow, so denormalise everything".

Q4. What are the main types of NoSQL databases?

They're testing: breadth and fitting each to a use.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Key-value stores fetch a value by exact key, like Redis or DynamoDB: sessions, carts, caches. Document stores hold nested JSON-like records with queryable fields, like MongoDB: catalogues and profiles. Wide-column stores like Cassandra group rows by partition key and sort within it, built for very high write volumes like events and messages. Graph databases like Neo4j store nodes and edges for multi-hop relationship queries.
3
4**Counter-questions:**
5- *"Where does DynamoDB fit?"* → It's a key-value and document store: items fetched by partition key (and sort key), with document-style attributes.
6
7**Trap:** naming products without saying what each is good at.

Q5. Why do many NoSQL databases not support joins?

They're testing: the link between distribution and data modelling.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** They're designed to spread data across many machines by key. A join may need rows from many machines, which is slow and complicated to do well, so they leave it out and expect you to model data so a common read hits one key or one partition, usually by denormalising.
3
4**Counter-questions:**
5- *"So how do you do a 'join' in a document store?"* → Embed the related data, or fetch the related documents in a second query and combine them in the application.
6
7**Trap:** "because NoSQL is newer" or "joins are a SQL thing".

Q6. What's the difference between schema-on-write and schema-on-read?

They're testing: understanding "schemaless".

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Schema-on-write means the database enforces structure when data is stored, rejecting bad data and requiring migrations to change shape. Schema-on-read means the database stores whatever it's given and the application interprets it when reading, which makes changes easy but moves validation into code. Every system has a schema; the question is who enforces it.
3
4**Counter-questions:**
5- *"How do you change a document's shape safely?"* → Write code that reads both old and new shapes, start writing the new shape, migrate old documents in the background, then remove support for the old shape.
6
7**Trap:** "NoSQL has no schema".

Q7. Would you put payments in a NoSQL database?

They're testing: judgement about correctness.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** I'd default to a relational database with transactions and constraints, because payments need atomic multi-row changes, strong correctness, and auditability. Some NoSQL databases do offer transactions and are used for payments at large scale, but that's a deliberate choice with careful design, not a default.
3
4**Counter-questions:**
5- *"What if the payments volume is enormous?"* → Shard the relational database by account or merchant, or use a database built for distributed transactions, and keep a strict ledger model (Chapter 45).
6
7**Trap:** choosing a store for payments purely on throughput.

Q8. When would you use a graph database?

They're testing: knowing its niche.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** When the important questions are about relationships several hops deep: recommendations, social connections, fraud rings, network dependencies. In SQL each hop is another join, which gets slow and awkward; a graph database follows edges directly. For simple one-hop relationships, a relational database is usually fine.
3
4**Counter-questions:**
5- *"Any downside?"* → They can be harder to shard, and they're a second system to run. Many teams keep the main data in SQL and copy relationship data into a graph store for those queries.
6
7**Trap:** using a graph database for everything that has "relations".

Q9. Can a relational database store JSON? Then why use a document store?

They're testing: knowing the line has blurred.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Yes. PostgreSQL, for example, stores and indexes JSON with its jsonb type, which covers many document-style needs inside a relational database. A dedicated document store makes sense when almost everything is documents of varying shape, you want to scale out by key from the start, or the team and tooling are built around it.
3
4**Counter-questions:**
5- *"What's a risk of putting everything in a JSON column?"* → You lose the database checking structure and types, and queries across JSON fields can be harder to make fast.
6
7**Trap:** treating SQL and NoSQL as strict opposites.

Q10. How would you model the bookstore's orders in a document store?

They're testing: applying denormalisation carefully.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** One document per order, embedding the items with a snapshot of each book's title and the price paid, plus the delivery address at the time, because the order is read as a whole and that history must not change. I'd reference the user and books by ID rather than embedding their current details. For "all orders of a user", I'd index the user ID field.
3
4**Counter-questions:**
5- *"An order can have thousands of items. Still embed?"* → No. Documents have size limits and huge documents are slow; store items in a separate collection keyed by order ID.
6
7**Trap:** embedding everything, including data that changes often.
8
9---
10
11## Practice coverage
12
13Every section in this chapter is tested at least once.
14
15| Section | Tested by |
16|---|---|
17| 8.1 Tables, rows, keys | Level 3, Q1 |
18| 8.2 ACID | Level 3, Level 4, Q2, Q7 |
19| 8.3 Normalisation and joins | Level 1B, Level 3, Q3 |
20| 8.4 NoSQL families | Level 1A, Level 5, Q4, Q8 |
21| 8.5 Schema-on-write vs read | Level 1A.3, Q6, Q9 |
22| 8.6 Denormalisation | Level 1B, Level 2.1, Level 2.2, Q5, Q10 |
23| 8.7 How to choose | Level 1A, Level 5, Q1, Q7 |
24
25---

8. SQL vs NoSQL