skip to content

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%

answer

  1. equality fields first
  2. the range field last
  3. order written into the key
  4. fields become key parts

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.

solid answer

~40 s

Take the read apart. **Equality inputs** ("this customer") become the leading part of the key: the partition key, or the row-key prefix. The **range or ordering field** ("newest first by order time") comes next: a clustering column declared in descending order, or a time component in the row key encoded so the wanted order is the stored order. Anything that must uniquely identify a row (an order id) goes last. This is often called promoting fields into the key: a field stored only as a column value cannot narrow a read, but as a key component it selects a contiguous slice. Encode key parts so byte order matches the intended order (fixed widths, zero padding), and keep low-cardinality, coarse parts before fine ones so related rows sit together.

code

pseudocode · 7 lines
pseudocode
function orderRowKey(customerId, orderTime, orderId):
    # newer orders must sort first, so store a value that falls as time rises
    descTime = MAX_TIME - orderTime
    return pad(customerId, 12) + "#" + pad(descTime, 20) + "#" + orderId

# read: the 20 latest orders for customer 42
scan(prefix = pad(42, 12) + "#", limit = 20)

go deeper

for a junior

Know that a field must be part of the key for a read on it to be efficient.

for a middle

Decompose a read into equality, range, uniqueness and limit, and place each in the key in that order for both key shapes.

for a senior

Spot keys that cannot serve a neighbouring read, and handle encoding for byte order and descending time correctly.

for a principal

Be ready to judge how many read paths a single key can serve before a new layout is cheaper than contorting the key.

## Reads narrow by key, never by column value A wide-column store can locate data only through its key: the partition key or row-key prefix picks where to look, and the rest of the key orders what is there. A field that lives only in a column value cannot narrow a read. So the fields a read depends on have to **become parts of the key** — a move sometimes called **field promotion**. ## Decomposing a read Write the read as a sentence and label its parts. For *"the 20 latest orders for customer C"*: | part of the read | role | goes where | |---|---|---| | customer C | equality match | front of the key: partition key or row-key prefix | | latest first | order by time, descending | next: a clustering column or key component | | order id | uniqueness | last, to separate orders placed in the same instant | | 20 | limit | the read stops after 20 rows of the slice | ## Building it in each key shape **Hashed partition key plus clustering columns:** - partition key: `customer_id` - clustering columns: `order_time` (descending), `order_id` - the read names the partition and takes the first 20 rows. **One sorted row key:** - row key: `customerId#<time encoded for descending order>#orderId` - the read scans the prefix `customerId#` and stops after 20 rows. In the second shape the time component must be encoded so that newer sorts first, for example by storing a value that decreases as time increases. That is a different purpose from rearranging a key to spread load, which is a partitioning technique. ## Rules that keep keys useful 1. **Equality before range.** A range can only be taken on the last key component a read uses; everything before it must be fixed. 2. **Coarse before fine.** `region#country#city` keeps related rows adjacent and allows reads at every level of the hierarchy. 3. **Encode for byte order.** Zero-pad or use fixed-width binary numbers so `9` does not sort after `10`. 4. **Make it unique.** Add an id so two events with the same time do not overwrite each other. 5. **Keep it short.** The key is stored with every cell, so long keys cost storage and memory. ## What promotion cannot do A key serves reads that **match its prefix**. The layout above cannot answer "all orders on a given day across customers": the day is not at the front. That read needs its own layout, whose leading part is the day, with care for how many rows one day holds. ## Interview angle Strong answers decompose the read into equality, range, uniqueness and limit, place each in the key in that order, mention encoding for byte order, and say which neighbouring read the key cannot serve.

  • Why must the range field be the last key component a read uses?
    Keys are sorted component by component. Once you range over one component, rows with every value of the components after it are interleaved, so only the components before the range can be fixed for a contiguous slice.
  • What happens if two orders for the same customer share a timestamp and the id is not in the key?
    They map to the same key, so the second write overwrites the first cell by cell. Adding a unique id as the last component keeps both.

saying these in an interview costs you the question

  • Leaving a filtering field as a column value and expecting reads on it to be cheap
  • Putting the range field before an equality field in the key
  • Encoding numbers as unpadded text inside a sorted key
  • Omitting a unique id so events with equal times overwrite each other