skip to content

Why do you design a wide-column schema from the queries you will run rather than from the entities and their relationships?

level: juniorimportance: must knowfreq 66%

answer

  1. no joins to fall back on
  2. the key fixes the order
  3. list the reads first
  4. one layout per read path

basics

~20 s

A wide-column store answers efficiently only reads that follow a key and its stored order, and it cannot join. So you list the application's reads first and give each one a key layout that serves it in one contiguous read.

solid answer

~50 s

In a relational database you model entities, normalise, and let the query planner combine tables at read time. A wide-column store has **no joins** and serves efficiently only reads that **start from a key and follow its stored order**; anything else becomes a scan. So the design runs backwards: first **list the access paths** (for example "orders for a customer, newest first", "readings for a device between two times"), then give **each path a key layout** whose partition or prefix selects exactly the data it needs and whose order matches the order it wants. Data needed by several paths is **duplicated**, one copy per layout, and the application writes all copies. The result is fast, predictable reads at the cost of more writes, more storage and a schema that must change when the queries do.

go deeper

for a junior

Be able to explain that wide-column schemas start from the list of queries because the store cannot join and reads must follow the key.

for a middle

Walk through turning a read path into a grouping, an order and a layout, and show how one piece of data lands in several layouts.

for a senior

Estimate the write, storage and consistency cost of the layouts you propose and identify which copy is the source of truth.

for a principal

Be ready to judge when query-first rigidity is acceptable for a product whose access paths may still change, and how to stage new layouts safely.

## Two opposite starting points **Entity-first** (relational) modelling asks *what are the things and how do they relate?* It normalises data so each fact is stored once and relies on joins and indexes to answer any question later. **Query-first** modelling asks *what will the application read, how often, in what order?* and shapes storage around the answers. Wide-column stores push you there because of three properties: - **No joins.** Data read together must already be stored together. - **Key-ordered storage.** A read is efficient only if it names a key, or a key prefix or partition, and reads along the stored order. - **Filtering is a scan.** A predicate on a non-key column means reading and discarding data. ## The method, step by step 1. **Enumerate access paths.** Write each read as a sentence with its inputs and ordering: "given a customer id, the 20 most recent orders". 2. **Choose the grouping.** What is always read together? That becomes the partition, or the row-key prefix. 3. **Choose the order.** How is it read inside the group? That becomes the clustering columns, or the rest of the row key. 4. **Estimate size.** How large can one group grow? If it is unbounded, add a component, such as a time bucket, that caps it. 5. **Assign a layout per path.** Paths that need different groupings get different tables, or different row-key prefixes in one table. 6. **List the writes.** Each piece of data now lands in every layout that needs it; note which layout is the source of truth. ## An example | access path | grouping | order | layout | |---|---|---|---| | orders of a customer, newest first | customer | order time, descending | `orders_by_customer` | | an order's line items | order id | line number | `items_by_order` | | orders shipped from a warehouse per day | warehouse + day | ship time | `orders_by_warehouse_day` | Every order is written into three layouts. Each read is one contiguous slice. ## Costs you accept - **Write amplification**: one logical change becomes several physical writes. - **Storage**: duplicated data. - **Consistency work**: the copies must be kept in step, and a changed key field means moving a row between positions. - **Rigidity**: a new read path usually means a new layout and a backfill. ## One table or many Stores built around a hashed partition key and clustering columns usually express each path as its **own table**. Stores built around one sorted row key often keep related datasets in **one table** with distinct row-key prefixes, because a prefix scan already isolates each dataset. The principle is the same: **a layout per read path**. ## Interview angle Interviewers want to hear the order of operations — reads first, then keys, then duplication — and an honest account of what it costs. Starting from an entity diagram and adding keys afterwards is the classic mistake.

  • What do you do when a new read path appears after launch?
    Add a layout for it, write new data into it alongside the existing ones, and backfill history from the source-of-truth layout. Trying to serve it from an existing layout with filters usually means scans that get slower as data grows.
  • Is normalisation ever useful in a wide-column design?
    For data that changes often and is read rarely with the parent, a reference to a separate row can beat copying it everywhere. The trade is one extra point read against updating many copies on every change.

saying these in an interview costs you the question

  • Starting from an entity-relationship diagram and adding keys afterwards
  • Planning to join tables at read time in the application for common paths
  • Assuming one normalised table can serve every access path with filters
  • Ignoring the write cost of keeping several layouts in step