skip to content

Query-First Key Design

4 roadmaps6 questions

Designing keys from the queries you will run rather than from the entities: a layout per read path, keys that bound partition growth, and why a filtering scan is the wrong answer.

on this pageshow

explore

questions

6

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
open as a page

Why must a wide-column design bound how large one partition or row can grow, and how does adding a time bucket to the key bound it?

level: middleimportance: must knowfreq 54%

basics

~20 s

A partition or row is never split across servers, so one that grows forever becomes slow to read, compact and repair and can hit size limits. Adding a time bucket to the grouping part of the key starts a fresh partition each period.

open as a page

Why does a wide-column store make a query that filters on a non-key column expensive, and what should you do instead?

level: middleimportance: must knowfreq 58%

basics

~20 s

The key is the only structure that narrows a read, so a filter on another column makes the store read and discard data across partitions. Serve that read with a layout keyed by the field instead.

open as a page

How do you turn a read such as "the latest orders for a customer" into a wide-column key, and why do the query's fields move into the key?

level: middleimportance: should knowfreq 56%

basics

~20 s

Put the fields the read matches exactly, such as the customer id, first in the key and the field it ranges over, such as order time, next, in the order it is read. A field only filters cheaply as part of the key.

open as a page

In a wide-column design with a layout per read path, what must happen to the duplicated copies when a field that forms part of one layout's key changes?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A key cannot be updated, so every layout keyed by the changed field must delete the row at its old key and write it at the new one. That needs the old value, read from the source of truth or carried in the change event.

open as a page

For time-series data in a wide-column store, when would you store one row per event rather than one row per entity and period holding many cells?

level: seniorimportance: nice to knowfreq 30%

basics

~20 s

One row per event is simpler, copes with uneven or unbounded streams and slices any time range. One row per entity and period packs events compactly and reads a period in one fetch, but must stay bounded in size.

open as a page