Cassandra & ScyllaDB: Wide-Column Data Modeling Done Right

cassandra data modeling is the discipline that decides whether a query returns in two milliseconds or times out under load — and it is the single skill relational engineers get wrong most often, because every instinct they carry from Postgres and MySQL is actively harmful here. In a wide-column store there are no joins, no ad-hoc WHERE clauses, no foreign keys, and no query planner rescuing a badly-shaped schema at runtime. The data model is the query plan: you decide, at design time, exactly which reads the cluster will serve cheaply, and any read you did not plan for is either impossible or catastrophically slow. Normalizing a schema — the thing you were trained to do for two decades — produces a design that cannot be queried, because the row you want lives on a node the coordinator has no way to find without scanning every partition on every machine.

This guide is the walkthrough you wished existed the first time an interviewer asked "model a messaging inbox for Cassandra" and your carefully-normalized users/messages/threads diagram earned a slow shake of the head. It rebuilds the mental model from the physical layout up: what a wide-column store actually stores on disk (a partitioned, sorted map of maps written to an LSM tree), how the two halves of the primary key — the partition key that routes a row to a node and the clustering key that sorts rows within a partition — determine every access path, why query-first denormalization means duplicating the same fact into one purpose-built table per read, and the three failure modes that end careers in production: tombstones that turn deletes into read-time landmines, hot partitions that funnel a whole cluster's traffic onto one overloaded node, and oversized partitions that blow past the size budget and stall compaction. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the database practice library →, rehearse pipeline shaping on the data-processing practice library →, and sharpen your schema instincts on the dimensional-modeling practice library →.

---

On this page

• The wide-column data model
• Partition keys and clustering keys
• Query-first denormalization
• Pitfalls — tombstones, hot and large partitions
• Cassandra vs ScyllaDB and tuning
• Cheat sheet — wide-column modeling recipes
• Frequently asked questions
• Practice on PipeCode

---

1. The wide-column data model

A wide-column store is a partitioned, sorted map of maps — not a table of rows, and every relational instinct you have is a liability

The one-sentence invariant: a wide-column store like Cassandra or ScyllaDB persists a two-level nested map — an outer map from partition key to partition, and an inner map from clustering key to row — physically sorted on disk within each partition, replicated across a ring of nodes, and written through a log-structured merge tree that never updates data in place, which means the schema you design is a direct encoding of the exact reads you will serve and nothing else. There is no query optimizer that will rescue a normalized design at runtime; the access path is frozen the moment you choose the primary key. If you can name the partition and you can name (or range-scan) the clustering values inside it, the read is O(1) coordinator routing plus a sorted-range disk read; if you cannot, the read is either rejected outright or degenerates into a cluster-wide scatter-gather that pages on-call at 3 AM.

The axes that matter — what "wide-column" actually means.

• Partitioned. Every row belongs to exactly one partition, chosen by hashing the partition key to a token. The token maps to a position on a ring of nodes; the partition (and all its rows) lives on the node(s) that own that token range. Cross-partition queries have no efficient path — the coordinator would have to ask every node.
• Sorted. Within a partition, rows are stored physically sorted by the clustering key. This makes range reads inside a partition (WHERE partition_key = ? AND clustering_col > ?) a contiguous disk scan — the single most important performance property of the model.
• Map of maps. Conceptually partition_key -> (clustering_key -> row). A "wide" partition can hold millions of rows/cells; a "narrow" one holds a handful. The width is a design choice with a hard budget, covered in section 4.
• LSM-tree storage. Writes go to an in-memory memtable plus a commit log, then flush to immutable SSTables. Updates and deletes are appended as new versions (or tombstones), and background compaction merges SSTables. Nothing is mutated in place — a fact that explains tombstones, read amplification, and compaction strategy all at once.

The 2026 reality — why teams still reach for wide-column.

• Linear write scalability. Cassandra and ScyllaDB scale writes linearly by adding nodes, with no single write master. Append-only LSM storage makes writes cheap and predictable — the workload profile behind time-series, event logging, IoT telemetry, messaging, and feature stores.
• No single point of failure. A masterless ring with tunable replication (RF) survives node and even datacenter loss with the right consistency level. This availability story is the reason the pattern outlives every "just use Postgres" objection at scale.
• Predictable latency at volume. Because the access path is fixed by the schema and there is no planner variance, a well-modeled table delivers flat p99 latency into the billions of rows — provided you never issue a query the model was not built for.
• The catch. All of that is conditional on modeling for the query. The same engine that serves 2 ms reads at a million writes/second will fall over on a single ALLOW FILTERING scan or one unbounded partition. The engine gives you a scalpel with no safety guard.

What interviewers listen for.

• Do you say "model for the query, not the entity" in the first sentence? — required answer.
• Do you name the storage engine as an LSM tree and connect it to tombstones and compaction? — senior signal.
• Do you state "there are no joins and no ad-hoc WHERE" and design around it instead of wishing it away? — required answer.
• Do you describe a table as a "partition of sorted rows" rather than as "a table like in SQL"? — senior signal.
• Do you refuse to reach for ALLOW FILTERING and instead add a purpose-built table? — senior signal.

Worked example — reading a wide-column table as a map of maps

Detailed explanation. The fastest way to internalize the model is to stop picturing a spreadsheet grid and start picturing nested dictionaries. A CQL table with a compound primary key is a dictionary keyed by the partition key, whose values are dictionaries keyed by the clustering key, whose values are the non-key columns. Walk through a per-user activity feed.

• Outer map. user_id (partition key) → the user's partition.
• Inner map. activity_ts (clustering key, descending) → one activity row.
• Leaf. the columns action, target_id, metadata for that timestamp.
• Physical consequence. All of a user's activity lives contiguously on one set of replicas, sorted newest-first, so "latest 20 activities for user X" is one node hop and one sorted-range read.

Question. Model a per-user activity feed so that "the most recent N activities for a given user" is a single-partition range read, and show the conceptual map it produces.

Input.

Requirement	Value
Read query	latest N activities for one user
Partition key	user_id
Clustering key	activity_ts DESC
Row payload	action, target_id, metadata

Code.

-- CQL: one partition per user, rows sorted newest-first
CREATE TABLE activity_by_user (
    user_id      uuid,
    activity_ts  timestamp,
    action       text,
    target_id    uuid,
    metadata     map<text, text>,
    PRIMARY KEY ((user_id), activity_ts)
) WITH CLUSTERING ORDER BY (activity_ts DESC);

-- The only read this table is built for:
SELECT activity_ts, action, target_id
FROM   activity_by_user
WHERE  user_id = 8f3b...       -- names the partition
LIMIT  20;                     -- sorted range read, newest first

Step-by-step explanation.

1. PRIMARY KEY ((user_id), activity_ts) declares user_id as the partition key (the double parentheses) and activity_ts as the clustering key. Every row for a user lands in one partition on one set of replicas.
2. WITH CLUSTERING ORDER BY (activity_ts DESC) stores rows physically newest-first on disk. The LIMIT 20 read then reads the first 20 rows off the front of the sorted partition — no sort at query time, no scan of older rows.
3. Conceptually the table is { user_id: { activity_ts: {action, target_id, metadata} } }. The read WHERE user_id = ? selects the inner map; LIMIT 20 takes its head.
4. There is no efficient way to ask "all activity across all users on a given day" against this table — that query names no partition. If the business needs it, you build a second table keyed by day (section 3).
5. The metadata map<text,text> is a collection column stored inline in the row. Collections are convenient but have their own tombstone hazards (section 4); prefer them small and bounded.

Output.

user_id	activity_ts	action	Read cost
8f3b…	2026-09-05 10:04	comment	1 partition, sorted head
8f3b…	2026-09-05 09:58	like	contiguous next row
8f3b…	2026-09-05 09:41	follow	contiguous next row

Rule of thumb. Read every wide-column table as partition_key -> (clustering_key -> row). If your target query can name the partition and take a sorted slice of the clustering values, the table is right; if it cannot, you need a different table, not a cleverer query.

Worked example — modeling a lookup table and a static column

Detailed explanation. Not every table is a wide time-series. The two other canonical shapes are the narrow key-value lookup (one row per partition) and the partition-with-header pattern using a static column (per-partition metadata shared across all clustering rows). Build both for a chat application.

• Lookup. user_by_id — partition key user_id, no clustering key, one row per partition. Pure key-value.
• Static column. messages_by_thread — partition key thread_id, clustering key message_ts, plus a static column thread_name stored once per partition, not once per message.
• Why static. The thread name is the same for every message in the thread; a static column stores it once and updates it in one place.

Question. Model user_by_id as a key-value lookup and messages_by_thread with a per-thread static column, and explain when a static column beats duplicating the value on every row.

Input.

Table	Partition key	Clustering key	Static column
user_by_id	user_id	(none)	(none)
messages_by_thread	thread_id	message_ts	thread_name

Code.

-- Narrow key-value lookup: one row per partition
CREATE TABLE user_by_id (
    user_id  uuid PRIMARY KEY,
    email    text,
    name     text,
    created  timestamp
);

-- Wide table with a per-partition static column
CREATE TABLE messages_by_thread (
    thread_id    uuid,
    message_ts   timestamp,
    thread_name  text STATIC,     -- stored once per partition
    sender_id    uuid,
    body         text,
    PRIMARY KEY ((thread_id), message_ts)
) WITH CLUSTERING ORDER BY (message_ts ASC);

-- Update the thread name once, not per message:
UPDATE messages_by_thread SET thread_name = 'Launch plan'
WHERE  thread_id = 1a2b...;      -- no clustering key needed for static writes

Step-by-step explanation.

1. user_by_id has a single-column primary key, so the partition key is the whole key and there is no clustering key. Each partition holds exactly one row — the classic distributed hash-map. Reads are WHERE user_id = ?, always O(1) routing.
2. messages_by_thread keys by thread_id (partition) and message_ts (clustering), so all messages in a thread live together, sorted oldest-first for natural chat display.
3. thread_name text STATIC is stored once per partition rather than once per message row. A 10,000-message thread stores the name once, not 10,000 times, and renaming the thread is a single write.
4. Static columns can be written without a clustering key value (as in the UPDATE above), because they belong to the partition, not to any one row. Reading any message row also returns the current thread_name.
5. Use a static column when a value is (a) identical across all rows in a partition and (b) mutable — otherwise you would either duplicate it on every row (wasting space and forcing a fan-out update) or split it into a separate lookup table (forcing a second read).

Output.

Pattern	Rows per partition	Best for
Key-value lookup	1	entity-by-id reads
Wide + clustering	many	feeds, time-series, threads
Static column	many + 1 shared	per-partition header/metadata

Rule of thumb. Reach for a static column when a mutable value is constant across a partition; reach for a separate lookup table when the value is queried on its own; never duplicate a mutable value onto every clustering row — that turns one logical update into a partition-wide fan-out.

Data engineering interview question on the wide-column data model

A senior interviewer often opens with: "Design the Cassandra schema for a messaging product. Users have many conversations; each conversation has many messages. The app must render (a) a user's list of conversations most-recent-first, and (b) the messages inside a conversation oldest-first with pagination. Give me the tables, the primary keys, and explain why a normalized users/conversations/messages design fails here."

Solution Using query-first wide-column tables keyed for each read

-- Query A: a user's conversations, most-recent-active first
CREATE TABLE conversations_by_user (
    user_id           uuid,
    last_message_ts   timestamp,
    conversation_id   uuid,
    peer_name         text,
    last_snippet      text,
    PRIMARY KEY ((user_id), last_message_ts, conversation_id)
) WITH CLUSTERING ORDER BY (last_message_ts DESC);

-- Query B: messages inside a conversation, oldest-first, paginated
CREATE TABLE messages_by_conversation (
    conversation_id   uuid,
    message_ts        timestamp,
    message_id        timeuuid,
    sender_id         uuid,
    body              text,
    PRIMARY KEY ((conversation_id), message_ts, message_id)
) WITH CLUSTERING ORDER BY (message_ts ASC, message_id ASC);

-- Read A: conversation list for a user (single partition, sorted)
SELECT conversation_id, peer_name, last_snippet, last_message_ts
FROM   conversations_by_user
WHERE  user_id = 8f3b... LIMIT 30;

-- Read B: first page of a conversation, then paginate by message_ts
SELECT message_ts, sender_id, body
FROM   messages_by_conversation
WHERE  conversation_id = 1a2b...
  AND  message_ts > '2026-09-05 00:00:00' LIMIT 50;

Step-by-step trace.

Step	Input	What happens
1	Read A: user_id = 8f3b…	coordinator hashes user_id → token → replicas own the partition
2	LIMIT 30	reads 30 rows off the front of the DESC-sorted partition
3	Read B: conversation_id = 1a2b…	hashes conversation_id → different partition, different owning replicas
4	message_ts > cursor	contiguous sorted-range scan from the cursor forward
5	LIMIT 50	returns one page; next page uses the last message_ts as the new cursor
6	new message arrives	app writes messages_by_conversation AND updates conversations_by_user

Both reads name their partition and take a sorted slice, so each is a single-node hop plus a contiguous disk read. Pagination is cursor-based on the clustering column — no OFFSET, which does not exist and would be an anti-pattern anyway.

Output:

Query	Table	Partition named?	Cost
A — conversation list	conversations_by_user	yes (user_id)	1 partition, sorted head
B — message page	messages_by_conversation	yes (conversation_id)	1 partition, sorted range
(rejected) all msgs by sender	none built	no	would require ALLOW FILTERING

Why this works — concept by concept:

• Query-first modeling — the reads were enumerated before the tables. Each of the two required reads got its own table whose primary key makes that read a single-partition sorted access. The entities (user, conversation, message) never became tables; the queries became tables.
• Partition key = routing — user_id and conversation_id are the partition keys because they are the values each read already knows. Naming the partition is what turns a distributed lookup into a single-node hop.
• Clustering key = order + pagination — last_message_ts DESC and message_ts ASC bake the required sort order into the on-disk layout, and cursor pagination rides the clustering column instead of a nonexistent OFFSET.
• Why the normalized design fails — a normalized messages(conversation_id, sender_id, ...) table cannot answer "conversations for a user" without a join (there are none) or a scan (no partition named). You would be forced into ALLOW FILTERING, a cluster-wide scatter-gather that does not scale.
• Cost — two tables, two writes per new message (one per table), each read O(1) routing + O(page) sorted scan. Storage grows with duplication but storage is cheap; the eliminated cost is the O(N) scan a normalized model would require on every read. Net: bounded write fan-out buys flat read latency at any scale.

Database
Topic — database
Wide-column and NoSQL data-model problems

Practice →

Modeling Topic — dimensional-modeling Data-modeling problems for query-first schemas

Practice →

---

2. Partition keys and clustering keys

The partition key decides which node stores the row; the clustering key decides the order within that node — together they are the entire access path

The mental model in one line: the primary key of a wide-column table is two functionally distinct things fused together — the partition key, which is hashed to a token that routes the row to a set of replica nodes and is the only value that makes a read efficient, and the clustering key, which sorts rows on disk inside the partition and is the only value you can range-scan or paginate over — so choosing the primary key is choosing, permanently, both the physical distribution of your data across the cluster and the exact shape of every read the table can serve. Get the partition key wrong and you get hot spots or unbounded partitions; get the clustering key wrong and the reads you need become impossible; there is no CREATE INDEX that fully undoes either mistake.

The anatomy of a compound primary key.

• PRIMARY KEY ((a, b), c, d). The first parenthesised group (a, b) is a composite partition key — both columns are hashed together to produce one token. c and d are clustering columns that sort rows within the partition, in that priority order.
• PRIMARY KEY (a, b, c). With no inner parentheses, only the first column a is the partition key; b and c are clustering columns. This single-vs-composite distinction is the most common beginner error.
• Query constraint. A read must supply the entire partition key with equality (= or IN). Clustering columns can then be constrained left-to-right with equality and a final range — you cannot skip a clustering column and constrain a later one.
• Sort order. WITH CLUSTERING ORDER BY (c DESC, d ASC) fixes the physical order. Reads in that order are free; reads in the reverse order are supported but read back-to-front; reads in an unrelated order are not supported.

Choosing the partition key — the cardinality-versus-size trade-off.

• High enough cardinality. The partition key must have enough distinct values to spread load across the whole ring. country (≈200 values) is a terrible partition key; user_id (millions) is good.
• Bounded partition size. Each partition must stay under the size budget (≈100 MB / ≈100k cells — section 4). If one partition key value accumulates unbounded rows (e.g. a global event_type), you must add a bucketing component to the partition key.
• Even access distribution. Cardinality is necessary but not sufficient — the access must be even too. A user_id partition key with one celebrity user who gets 40% of reads is a hot partition despite high cardinality (section 4).
• Contains the query's known value. The partition key must be a value every target read already has in hand. You cannot key by order_id if the read only knows customer_id.

Clustering columns — order, range, and the ALLOW FILTERING trap.

• Left-to-right constraint. With clustering (year, month, day), you can query year = ? AND month = ? AND day > ?, but not day = ? alone — the engine cannot seek to a later clustering column without pinning the earlier ones.
• Range on the last constrained column. The final clustering predicate may be a range (>, <, >=, <=); all earlier ones must be equality. This is what makes time-range reads inside a partition efficient.
• ALLOW FILTERING is a red flag. It tells the engine to read rows and discard non-matches — an O(partition) or O(cluster) scan. In an interview, proposing ALLOW FILTERING for a production read is close to disqualifying; the correct answer is almost always "add a table or change the key."
• Secondary indexes are narrow. CREATE INDEX on a non-key column works for low-cardinality, single-partition-scoped filters but fans out across the cluster for global ones; it is not a substitute for modeling the query into the primary key.

Worked example — single vs composite partition key for sensor readings

Detailed explanation. A fleet of IoT sensors emits one reading per second. The naive schema keys by sensor_id alone, but a chatty sensor running for a year accumulates ~31 million rows in one partition — far past the size budget. The fix is a composite partition key that buckets by time, bounding each partition while keeping time-range reads efficient. Walk through both designs.

• Naive. PRIMARY KEY ((sensor_id), reading_ts) — one partition per sensor, grows without bound.
• Bucketed. PRIMARY KEY ((sensor_id, day_bucket), reading_ts) — one partition per sensor per day, each holding ≈86,400 rows.
• Read impact. A single-day read names one partition; a multi-day read issues one query per day bucket (client-side fan-out over a known, bounded set of buckets).

Question. Design a sensor-reading table whose partitions stay under the size budget while "readings for sensor X between two timestamps" remains a single-partition (or bounded multi-partition) sorted read.

Input.

Parameter	Value
Write rate	1 reading/sec/sensor
Naive partition size	~31M rows/year (too big)
Bucket granularity	1 day
Bucketed partition size	~86,400 rows/day

Code.

-- Bucketed composite partition key bounds each partition to one day
CREATE TABLE readings_by_sensor_day (
    sensor_id    uuid,
    day_bucket   date,        -- e.g. '2026-09-05'
    reading_ts   timestamp,
    temperature  double,
    humidity     double,
    PRIMARY KEY ((sensor_id, day_bucket), reading_ts)
) WITH CLUSTERING ORDER BY (reading_ts ASC);

-- Single-day read: names the whole partition key
SELECT reading_ts, temperature
FROM   readings_by_sensor_day
WHERE  sensor_id  = 7c1e...
  AND  day_bucket = '2026-09-05'
  AND  reading_ts >= '2026-09-05 08:00:00'
  AND  reading_ts <  '2026-09-05 12:00:00';

# Multi-day read: fan out over a bounded, known set of day buckets
from datetime import date, timedelta

def day_buckets(start: date, end: date):
    d = start
    while d <= end:
        yield d
        d += timedelta(days=1)

def read_range(session, sensor_id, start, end):
    stmt = session.prepare("""
        SELECT reading_ts, temperature FROM readings_by_sensor_day
        WHERE sensor_id = ? AND day_bucket = ?
          AND reading_ts >= ? AND reading_ts <= ?
    """)
    rows = []
    for bucket in day_buckets(start.date(), end.date()):
        rows.extend(session.execute(stmt, (sensor_id, bucket, start, end)))
    return rows

Step-by-step explanation.

1. The composite partition key (sensor_id, day_bucket) hashes both columns together, so each (sensor, day) pair is its own partition on its own replicas. A year of data for one sensor becomes 365 bounded partitions instead of one giant one.
2. A single-day read supplies both partition-key columns with equality, then range-scans reading_ts — one partition, one sorted disk range. This is the fast path the model exists to serve.
3. A multi-day read cannot name a single partition (the day varies), but the set of day buckets is known and bounded from the query range. The client issues one query per bucket — a controlled fan-out, not an ALLOW FILTERING scan.
4. Bucket granularity is a tuning knob: too coarse (monthly) and partitions grow past the budget again; too fine (hourly) and multi-day reads fan out over too many partitions. Size the bucket so a full partition sits comfortably under ~100 MB.
5. reading_ts as the clustering column gives natural chronological order and cheap range scans; ASC matches the most common "walk forward in time" access.

Output.

Design	Partition size	Single-day read	Multi-day read
((sensor_id), reading_ts)	~31M rows/yr (fails)	one huge partition	one huge partition
((sensor_id, day_bucket), reading_ts)	~86k rows/day	1 partition	N buckets, bounded fan-out

Rule of thumb. When one partition-key value accumulates rows over time, add a time bucket to the partition key. Size the bucket so a full partition stays under ~100 MB, and drive multi-bucket reads from the query's own time range — never from ALLOW FILTERING.

Worked example — clustering order and range reads

Detailed explanation. Clustering order is not cosmetic — it is the physical on-disk layout, and it determines which reads are cheap. A leaderboard that must return "top 10 scores for a game" needs scores stored descending so the top is the head of the partition. Walk through the design and the range-read semantics.

• Requirement. Top-N scores per game, highest first.
• Clustering. (score DESC, player_id ASC) — highest score first, ties broken by player.
• Range read. "scores above a threshold" is score > ? on the descending-sorted partition — a contiguous head scan.

Question. Design a leaderboard table so "top 10 for a game" and "all scores above a threshold" are both single-partition sorted reads, and explain the left-to-right clustering constraint.

Input.

Requirement	Value
Partition key	game_id
Clustering	score DESC, player_id ASC
Read 1	top 10 (LIMIT 10)
Read 2	score > threshold

Code.

CREATE TABLE leaderboard_by_game (
    game_id    uuid,
    score      int,
    player_id  uuid,
    player_name text,
    PRIMARY KEY ((game_id), score, player_id)
) WITH CLUSTERING ORDER BY (score DESC, player_id ASC);

-- Read 1: top 10, highest first — reads the head of the partition
SELECT player_name, score FROM leaderboard_by_game
WHERE  game_id = a91f... LIMIT 10;

-- Read 2: everyone above 9000 — contiguous head range
SELECT player_name, score FROM leaderboard_by_game
WHERE  game_id = a91f... AND score > 9000;

-- REJECTED: cannot constrain player_id without constraining score first
-- SELECT * FROM leaderboard_by_game WHERE game_id = a91f... AND player_id = ...;

Step-by-step explanation.

1. CLUSTERING ORDER BY (score DESC, player_id ASC) stores rows highest-score-first on disk. "Top 10" is literally the first 10 rows of the partition — no sort, no scan of the tail.
2. score > 9000 is a range on the first clustering column, which is allowed and cheap: it reads from the head down to the first row at or below 9000, then stops.
3. The rejected query tries to constrain player_id (the second clustering column) without constraining score (the first). The engine cannot seek to a specific player_id without knowing its score, because the physical order is score-major. This is the left-to-right rule.
4. To support "find a player's rank" you would build a second table keyed differently (e.g. score_by_game_player keyed by (game_id, player_id)) — again, one table per query.
5. Ties on score are ordered by player_id ASC, which makes pagination deterministic: the (score, player_id) pair is a total order, so cursor pagination never skips or repeats a row.

Output.

Query	Constrains	Legal?	Cost
top 10	game_id + LIMIT	yes	head of partition
score > 9000	game_id, score range	yes	contiguous head range
player_id = X	game_id, player_id (skips score)	no	not supported

Rule of thumb. Order clustering columns by how you read them: put the column you range-scan or take the top of first, and remember you can only constrain clustering columns left-to-right with a single trailing range. If a read needs a different order, it needs a different table.

Data engineering interview question on partition and clustering keys

A senior interviewer might ask: "You have a ride-sharing app. You must serve (a) 'all trips for a driver in a given month, newest first' and (b) never let a driver's partition exceed the size budget even for a driver doing 500 trips a day for years. Design the primary key, justify the partition and clustering choices, and show how a cross-month read works."

Solution Using a bucketed composite partition key with a descending clustering column

CREATE TABLE trips_by_driver_month (
    driver_id     uuid,
    month_bucket  text,        -- 'YYYY-MM', bounds the partition
    trip_ts       timestamp,
    trip_id       timeuuid,
    fare_cents    bigint,
    distance_km   double,
    PRIMARY KEY ((driver_id, month_bucket), trip_ts, trip_id)
) WITH CLUSTERING ORDER BY (trip_ts DESC, trip_id DESC);

-- Read A: one month of trips for a driver, newest first
SELECT trip_ts, fare_cents, distance_km
FROM   trips_by_driver_month
WHERE  driver_id = 3d90... AND month_bucket = '2026-09'
LIMIT  50;

# Cross-month read: enumerate the bounded set of month buckets in range
def month_buckets(start, end):
    y, m = start.year, start.month
    out = []
    while (y, m) <= (end.year, end.month):
        out.append(f"{y:04d}-{m:02d}")
        m += 1
        if m > 12:
            m, y = 1, y + 1
    return out

def trips_in_range(session, driver_id, start, end):
    stmt = session.prepare("""
        SELECT trip_ts, fare_cents FROM trips_by_driver_month
        WHERE driver_id = ? AND month_bucket = ?
          AND trip_ts >= ? AND trip_ts <= ?
    """)
    rows = []
    for b in month_buckets(start, end):        # bounded, known fan-out
        rows.extend(session.execute(stmt, (driver_id, b, start, end)))
    return rows

Step-by-step trace.

Step	Input	What happens
1	driver_id=3d90, month='2026-09'	both partition-key cols supplied → single partition
2	500 trips/day × 30 days	~15k rows/partition — well under the budget
3	CLUSTERING trip_ts DESC	newest trips are the head of the partition
4	LIMIT 50	one page off the head, no tail scan
5	cross-month (Jun–Sep)	month_buckets() → ['2026-06','2026-07','2026-08','2026-09']
6	4 queries	bounded fan-out, one partition each, merge client-side

The month bucket caps each partition at roughly a month of one driver's trips (~15k rows) no matter how many years the driver stays active, and the descending clustering column makes "newest first" free. Cross-month reads fan out over a small, query-derived set of buckets rather than scanning.

Output:

Query	Partitions touched	Ordered?	Cost
one month	1	yes (DESC)	sorted head
four months	4	yes per bucket	bounded fan-out
all-time (rare)	N months	yes per bucket	fan-out sized by tenure

Why this works — concept by concept:

• Composite partition key — (driver_id, month_bucket) hashes both columns to one token, so each driver-month is an independent, bounded partition on its own replicas. This is what caps partition size regardless of driver tenure.
• Bucketing bounds size — the month bucket converts "one unbounded partition per driver" into "one bounded partition per driver per month," keeping every partition comfortably under the ~100 MB / ~100k-cell budget.
• Descending clustering key — trip_ts DESC bakes "newest first" into the on-disk order, so the dominant read is a zero-cost head slice, and trip_id DESC makes the order total for safe pagination.
• Query-derived fan-out — cross-month reads compute the exact bucket list from the request's date range, so the fan-out is bounded and predictable — the opposite of an ALLOW FILTERING scan whose cost is the whole table.
• Cost — one write per trip, each read O(buckets-in-range) partition hops × O(page) sorted scan. Storage is linear in trips; there is no growth term that scales with driver tenure inside a single partition. Net: flat per-partition latency and a fan-out you can reason about.

Database
Topic — database
Partition-key and primary-key design problems

Practice →

Data processing Topic — data-processing Bucketing and time-partitioning problems

Practice →

---

3. Query-first denormalization

Enumerate the reads first, then build one purpose-built table per read — duplicating data on write is not a smell here, it is the design

The mental model in one line: query-first modeling inverts the relational workflow — instead of normalizing entities into third normal form and joining at read time, you list every read the application will issue, then create a separate denormalized table for each one whose primary key makes that read a single-partition access, accepting that the same fact is now duplicated across several tables and that keeping those copies consistent is the application's job, because in a wide-column store writes and storage are cheap while reads must be O(1) and joins do not exist. Denormalization is not a performance hack you reach for reluctantly; it is the first-class modeling primitive, and an interviewer who hears you say "I'd normalize and join" has already stopped listening.

The query-first workflow.

• Step 1 — list the reads. Write down every access pattern the app needs, with the values it will have in hand at read time (the future partition keys) and the order it needs results in (the future clustering keys).
• Step 2 — one table per read. Each distinct read becomes a table whose primary key encodes exactly that access. If two reads share a key shape, they can share a table; otherwise they do not.
• Step 3 — pick the write path. Decide how a single logical change (a new order, an email update) propagates to every table that must reflect it: application fan-out, a logged BATCH, or a materialized view.
• Step 4 — accept the duplication. The same fact lives in N tables. That is correct. Storage is cheap; the alternative — a read-time join — does not exist and would not scale if it did.

The cost you are trading.

• Writes get more expensive. A change now writes to every denormalized table. This is fine: writes are the LSM tree's strength, and the fan-out is bounded and known at design time.
• Storage grows. Duplicated data uses more disk. Also fine: storage is the cheapest resource in the system, and you are buying flat read latency with it.
• Consistency becomes your problem. With no foreign keys and no transactions across partitions, the copies can drift if a write path partially fails. You mitigate with logged batches (atomicity across tables sharing a partition-key value), idempotent writes, and periodic reconciliation.
• Reads get trivial. Every read names its partition and takes a sorted slice. This is the whole point — you moved all the complexity to write time, where the engine is fast, and away from read time, where it must be.

Keeping duplicates in sync — three mechanisms.

• Application fan-out. The service writes each table explicitly. Simplest and most common; make writes idempotent (use fixed keys, not append semantics) so retries are safe.
• Logged BATCH. A BEGIN BATCH ... APPLY BATCH groups writes so they all succeed or all get retried by the coordinator. It guarantees eventual atomicity, not isolation, and is cheapest when all statements share the same partition key. Overusing multi-partition batches creates coordinator pressure.
• Materialized views. CREATE MATERIALIZED VIEW asks the engine to maintain a second table keyed differently from a base table automatically. Convenient, but with well-known operational caveats (write amplification, repair complexity); many senior teams prefer explicit application fan-out for critical paths.

Worked example — user-by-id and user-by-email dual tables

Detailed explanation. An auth service must read a user two ways: by user_id (after a session lookup) and by email (at login). Neither read can use the other's key, so query-first modeling produces two tables holding the same user, kept in sync by the application. Build both and the write path.

• Read 1. by user_id → users_by_id.
• Read 2. by email → users_by_email.
• Write path. signup and profile-update write both tables; email change is a delete-plus-insert on users_by_email.

Question. Model the two lookup tables and show the idempotent write path for signup and for an email change.

Input.

Read	Key it has	Table
session → profile	user_id	users_by_id
login	email	users_by_email

Code.

CREATE TABLE users_by_id (
    user_id  uuid PRIMARY KEY,
    email    text,
    name     text,
    pw_hash  text
);

CREATE TABLE users_by_email (
    email    text PRIMARY KEY,
    user_id  uuid,
    name     text,
    pw_hash  text
);

def signup(session, user_id, email, name, pw_hash):
    # Idempotent: both writes use fixed primary keys, safe to retry
    session.execute("""
        INSERT INTO users_by_id (user_id, email, name, pw_hash)
        VALUES (%s, %s, %s, %s)
    """, (user_id, email, name, pw_hash))
    session.execute("""
        INSERT INTO users_by_email (email, user_id, name, pw_hash)
        VALUES (%s, %s, %s, %s)
    """, (email, user_id, name, pw_hash))

def change_email(session, user_id, old_email, new_email):
    # Partition key of users_by_email changes → delete old, insert new
    row = session.execute(
        "SELECT name, pw_hash FROM users_by_id WHERE user_id=%s", (user_id,)
    ).one()
    session.execute(
        "UPDATE users_by_id SET email=%s WHERE user_id=%s", (new_email, user_id))
    session.execute(
        "DELETE FROM users_by_email WHERE email=%s", (old_email,))
    session.execute("""
        INSERT INTO users_by_email (email, user_id, name, pw_hash)
        VALUES (%s, %s, %s, %s)
    """, (new_email, user_id, row.name, row.pw_hash))

Step-by-step explanation.

1. users_by_id and users_by_email store the same user redundantly, each keyed by the value one read already has. Neither read could use the other's key, so two tables are mandatory — this is denormalization by necessity, not by choice.
2. Signup writes both tables with INSERT using fixed primary keys. Because the keys are fixed (not append-generated), re-running the insert on a retry overwrites with identical data — the writes are idempotent, so partial failure plus retry converges.
3. An email change is subtle: email is the partition key of users_by_email, and partition keys are immutable. Changing the email means deleting the old-email row and inserting a new-email row — you cannot UPDATE a partition key.
4. The users_by_id side is a simple UPDATE because email there is a regular column, not part of the key.
5. If the process crashes between the delete and the insert, users_by_email temporarily lacks the user; a retry (idempotent) or a reconciliation job restores it. This is the consistency cost you accept for join-free reads.

Output.

Operation	users_by_id	users_by_email
signup	INSERT	INSERT
profile name update	UPDATE	UPDATE
email change	UPDATE email col	DELETE old + INSERT new

Rule of thumb. When two reads need two different keys for the same entity, build two tables and make every write idempotent with fixed keys. Remember partition keys are immutable — "changing" one is always delete-plus-insert, never update.

Worked example — a logged BATCH for atomic multi-table writes

Detailed explanation. When a single logical event must land in several tables and you want the coordinator to guarantee they all eventually apply, wrap them in a logged BATCH. It is not a relational transaction — there is no isolation and no rollback — but the batch log ensures that if the coordinator accepts the batch, every statement will eventually be applied even across node failures. Build an order-placement write that updates three query tables.

• Tables. orders_by_id, orders_by_customer, orders_by_status.
• Guarantee. logged batch = atomicity (all-or-eventually-all), not isolation.
• Caveat. cheapest when statements share a partition key; multi-partition batches add coordinator overhead — use sparingly.

Question. Write the logged batch that records a new order into three query tables, and state precisely what the batch does and does not guarantee.

Input.

Table	Keyed by	Serves read
orders_by_id	order_id	order detail
orders_by_customer	customer_id, order_ts	a customer's orders
orders_by_status	status, order_ts	ops queue by status

Code.

CREATE TABLE orders_by_id (
    order_id    uuid PRIMARY KEY,
    customer_id uuid, status text, total_cents bigint, order_ts timestamp);

CREATE TABLE orders_by_customer (
    customer_id uuid, order_ts timestamp, order_id uuid,
    status text, total_cents bigint,
    PRIMARY KEY ((customer_id), order_ts, order_id))
    WITH CLUSTERING ORDER BY (order_ts DESC, order_id DESC);

CREATE TABLE orders_by_status (
    status text, order_ts timestamp, order_id uuid,
    customer_id uuid, total_cents bigint,
    PRIMARY KEY ((status), order_ts, order_id))
    WITH CLUSTERING ORDER BY (order_ts DESC, order_id DESC);

-- Logged batch: all three apply, or the coordinator retries until they do
BEGIN BATCH
  INSERT INTO orders_by_id (order_id, customer_id, status, total_cents, order_ts)
    VALUES (11a2..., 77c1..., 'pending', 4200, '2026-09-05 10:00:00');
  INSERT INTO orders_by_customer (customer_id, order_ts, order_id, status, total_cents)
    VALUES (77c1..., '2026-09-05 10:00:00', 11a2..., 'pending', 4200);
  INSERT INTO orders_by_status (status, order_ts, order_id, customer_id, total_cents)
    VALUES ('pending', '2026-09-05 10:00:00', 11a2..., 77c1..., 4200);
APPLY BATCH;

Step-by-step explanation.

1. The three tables denormalize the same order for three reads: by id, by customer, by status. A new order must appear in all three, so the write fans out three ways.
2. BEGIN BATCH ... APPLY BATCH (logged, the default) writes a batch record to the batchlog on two replicas first. If the coordinator dies mid-apply, another node replays the batchlog, guaranteeing every statement eventually applies.
3. The guarantee is atomicity over time, not isolation: a concurrent reader may see orders_by_id updated before orders_by_status. There is no snapshot and no rollback. Design reads to tolerate this brief skew.
4. This batch spans three different partition keys (order_id, customer_id, status), so it is a multi-partition batch — heavier on the coordinator than a single-partition batch. It is justified here because the atomicity matters and the fan-out is only three; do not batch dozens of partitions casually.
5. If you did not need the atomicity guarantee, plain idempotent application fan-out (three separate inserts) is lighter and equally correct under retries — reserve logged batches for when partial visibility is genuinely unacceptable.

Output.

Guarantee	Logged BATCH	Plain fan-out
all statements eventually apply	yes	only with retries
isolation (no partial reads)	no	no
rollback	no	no
coordinator cost	higher (multi-partition)	lower

Rule of thumb. Use a logged BATCH only when several denormalized tables must not diverge even under coordinator failure, and keep the number of partitions small. For everything else, idempotent application fan-out is cheaper and just as safe.

Worked example — a materialized view for an auto-maintained second key

Detailed explanation. When a denormalized table is a strict re-keying of a base table (same rows, different partition key), a materialized view can maintain it automatically. The engine intercepts base-table writes and updates the view. It removes fan-out code but adds write amplification and repair complexity, so it is a convenience with caveats. Build a view that re-keys orders by status.

• Base. orders_by_id.
• View. orders_by_status_mv — same data, keyed by status.
• Trade-off. automatic maintenance vs write amplification and operational caveats.

Question. Create a materialized view that lets you read orders by status from a base table keyed by id, and state when to prefer it over manual fan-out.

Input.

Object	Keyed by	Maintained by
orders_by_id (base)	order_id	application
orders_by_status_mv (view)	status, order_id	engine

Code.

-- Base table
CREATE TABLE orders_by_id (
    order_id    uuid PRIMARY KEY,
    customer_id uuid,
    status      text,
    total_cents bigint,
    order_ts    timestamp
);

-- Materialized view re-keys by status; engine keeps it in sync
CREATE MATERIALIZED VIEW orders_by_status_mv AS
    SELECT status, order_id, customer_id, total_cents, order_ts
    FROM   orders_by_id
    WHERE  status IS NOT NULL AND order_id IS NOT NULL
    PRIMARY KEY ((status), order_id);

-- Read by status without any application fan-out
SELECT order_id, customer_id, total_cents
FROM   orders_by_status_mv
WHERE  status = 'pending';

Step-by-step explanation.

1. The view's PRIMARY KEY ((status), order_id) re-keys the base rows by status. Every base-table column used in the view key must be NOT NULL in the WHERE, because a null key column cannot be placed in a partition.
2. When the application writes orders_by_id, the engine automatically applies the corresponding change to orders_by_status_mv — no fan-out code in the service. A status change moves the row from one view partition to another for you.
3. The cost is write amplification: each base write triggers a view write, and the engine must read the old value to delete the stale view row (a read-before-write on updates). At high write rates this is a measurable tax.
4. Views also complicate repair and can drift under certain failure modes, which is why many senior teams restrict them to non-critical read paths and hand-roll fan-out for the hot ones.
5. Prefer a materialized view when the second table is a pure re-key of one base table and the write rate is moderate; prefer manual idempotent fan-out when you need multiple derived tables, high write throughput, or full operational control.

Output.

Aspect	Materialized view	Manual fan-out
maintenance code	none	explicit writes
write amplification	engine-driven (incl. read-before-write)	one write per table
operational risk	repair/drift caveats	app owns correctness
best for	single re-key, moderate writes	many tables, hot paths

Rule of thumb. Reach for a materialized view when a table is exactly one re-keying of a base table and the write volume is modest; hand-roll idempotent fan-out when you have several derived tables or a hot write path where you need explicit control over amplification.

Data engineering interview question on query-first denormalization

A senior interviewer might ask: "Model an e-commerce order store for Cassandra. Product needs (a) order detail by order id, (b) a customer's orders newest-first, and (c) an operations queue of orders by status newest-first. There are no other reads. Give the tables, the single write path for placing an order, and explain how you keep the three copies consistent when a status changes."

Solution Using three query tables with idempotent fan-out and status re-keying

CREATE TABLE orders_by_id (
    order_id uuid PRIMARY KEY,
    customer_id uuid, status text, total_cents bigint, order_ts timestamp);

CREATE TABLE orders_by_customer (
    customer_id uuid, order_ts timestamp, order_id uuid,
    status text, total_cents bigint,
    PRIMARY KEY ((customer_id), order_ts, order_id))
    WITH CLUSTERING ORDER BY (order_ts DESC, order_id DESC);

CREATE TABLE orders_by_status (
    status text, order_ts timestamp, order_id uuid,
    customer_id uuid, total_cents bigint,
    PRIMARY KEY ((status), order_ts, order_id))
    WITH CLUSTERING ORDER BY (order_ts DESC, order_id DESC);

def place_order(session, order_id, customer_id, total_cents, order_ts):
    # Idempotent fan-out: three fixed-key inserts, safe to retry
    session.execute("INSERT INTO orders_by_id (order_id,customer_id,status,total_cents,order_ts) VALUES (%s,%s,'pending',%s,%s)",
                    (order_id, customer_id, total_cents, order_ts))
    session.execute("INSERT INTO orders_by_customer (customer_id,order_ts,order_id,status,total_cents) VALUES (%s,%s,%s,'pending',%s)",
                    (customer_id, order_ts, order_id, total_cents))
    session.execute("INSERT INTO orders_by_status (status,order_ts,order_id,customer_id,total_cents) VALUES ('pending',%s,%s,%s,%s)",
                    (order_ts, order_id, customer_id, total_cents))

def change_status(session, order_id, order_ts, customer_id, new_status):
    old = session.execute("SELECT status,total_cents FROM orders_by_id WHERE order_id=%s",(order_id,)).one()
    session.execute("UPDATE orders_by_id SET status=%s WHERE order_id=%s",(new_status, order_id))
    session.execute("UPDATE orders_by_customer SET status=%s WHERE customer_id=%s AND order_ts=%s AND order_id=%s",
                    (new_status, customer_id, order_ts, order_id))
    # status is the PARTITION KEY of orders_by_status → delete old, insert new
    session.execute("DELETE FROM orders_by_status WHERE status=%s AND order_ts=%s AND order_id=%s",
                    (old.status, order_ts, order_id))
    session.execute("INSERT INTO orders_by_status (status,order_ts,order_id,customer_id,total_cents) VALUES (%s,%s,%s,%s,%s)",
                    (new_status, order_ts, order_id, customer_id, old.total_cents))

Step-by-step trace.

Step	Operation	Effect
1	place_order	3 idempotent inserts, one per query table
2	read order detail	orders_by_id WHERE order_id — 1 partition
3	read customer orders	orders_by_customer WHERE customer_id — sorted head
4	read ops queue	orders_by_status WHERE status='pending' — sorted head
5	change_status pending→shipped	UPDATE by_id + by_customer (status is a plain col)
6	orders_by_status re-key	DELETE ('pending',...) + INSERT ('shipped',...)

Placing an order fans out to three tables; a status change updates the two tables where status is a normal column and re-keys the one table where status is the partition key (delete old partition row, insert new). Every read names its partition and takes a sorted slice.

Output:

Read	Table	Partition named	Ordered
order detail	orders_by_id	order_id	n/a
customer orders	orders_by_customer	customer_id	order_ts DESC
ops queue	orders_by_status	status	order_ts DESC

Why this works — concept by concept:

• One table per query — three reads produced exactly three tables, each keyed so its read is a single-partition sorted access. No read joins, filters, or scans; the modeling absorbed all the complexity.
• Idempotent fan-out — place_order uses fixed-key inserts, so a retry after partial failure overwrites identical data and converges. Idempotency is what makes fan-out safe without transactions.
• Partition-key immutability — because status is the partition key of orders_by_status, a status change is delete-plus-insert there, while the other two tables (where status is a value) take a plain UPDATE. Knowing which is which is the crux of the answer.
• Consistency by convergence — with no cross-partition transactions, correctness comes from idempotent writes plus a periodic reconciliation job that re-derives the query tables from orders_by_id, the source of truth. Brief inter-table skew is tolerated by design.
• Cost — three writes per order and four writes per status change, each read O(1) routing + O(page) scan. Storage triples the order data — cheap — in exchange for three flat-latency reads that a normalized model could not serve at all without cluster-wide scans.

Modeling
Topic — dimensional-modeling
Denormalization and query-first modeling problems

Practice →

Database Topic — database Multi-table write-path and consistency problems

Practice →

---

4. Pitfalls — tombstones, hot and large partitions

The three failure modes that turn a well-shaped schema into a 3 AM incident — and every one is a modeling decision, not a config knob

The mental model in one line: the three ways a wide-column table fails in production are tombstones (deletes and TTL expirations that pile up as invisible markers the read path must still scan), hot partitions (a partition key whose access is so skewed that one node absorbs a disproportionate share of traffic while the rest of the ring idles), and large partitions (a partition key whose values grow past the ~100 MB / ~100k-cell budget until compaction stalls and reads slow) — and all three are consequences of the schema you chose, so the fix is almost always a modeling change (bucket the key, spread the key, avoid the delete pattern), not a tuning parameter. Interviewers probe these because they separate people who have only read the docs from people who have been paged.

Tombstones — deletes are writes, and they haunt the read path.

• What a delete really does. Because SSTables are immutable, a DELETE (or a TTL expiry, or writing null) does not remove data — it writes a tombstone, a marker that shadows older values. The real data is purged only when compaction merges the SSTables and the tombstone is older than gc_grace_seconds (default 10 days, needed for anti-entropy repair).
• Why they hurt reads. A read must scan and merge all tombstones in the requested range to know what is actually deleted. A partition with tens of thousands of tombstones makes a read do enormous work to return few live rows — and past a threshold the coordinator aborts with TombstoneOverwhelmingException.
• The classic anti-pattern. Using a partition as a queue: insert rows, process them, delete them. The deleted head of the partition becomes a wall of tombstones every read must scan before reaching live rows. Never model a queue this way.
• Hygiene. Prefer TTL with a compaction strategy that drops whole expired SSTables (TWCS); avoid deleting individual collection elements; design so ranges are read forward past live data, not through graveyards; tune gc_grace_seconds only with repair cadence in mind.

Hot partitions — high cardinality is not enough if access is skewed.

• What it is. One partition receives a hugely disproportionate share of reads and/or writes, so its owning replicas saturate while the rest of the cluster is idle. The whole cluster's throughput collapses to what one node can do.
• How it happens. A celebrity user in a by_user table; a global or default sentinel value used as a partition key; a monotonically increasing key (like a raw timestamp bucket that is "now") that funnels all current writes to one partition.
• Detection. Per-node/per-partition metrics: one replica set with far higher read/write rate, higher local latency, or larger partition than peers. Cassandra's nodetool toppartitions and ScyllaDB's per-shard metrics surface the offender.
• The fix. Increase effective cardinality by sharding the hot key — append a small bucket component ((user_id, shard) with shard in 0..N) and fan reads across the N shards, or split write and read paths so the monotonic "now" is spread across buckets.

Large partitions — the size budget is real.

• The budget. Aim to keep partitions under ~100 MB and ~100k cells (rows × columns). Beyond that, compaction slows, memory pressure rises, repair streams huge partitions, and reads that touch the partition degrade.
• How it happens. A partition key that accumulates unbounded rows over time (per-sensor forever, per-user forever) with no bucketing.
• The fix. Bucket the partition key by time or by a modulo shard so each partition is bounded — the same technique from section 2, applied as a corrective.
• Detection. nodetool tablehistograms / tablestats report max partition size and cell count; alert when the max approaches the budget, well before it becomes an incident.

Worked example — diagnosing a tombstone-overwhelmed read

Detailed explanation. A team modeled a task queue as a Cassandra partition: enqueue inserts a row, a worker processes it and DELETEs it. After a week, reads of the "next tasks" start timing out with TombstoneOverwhelmingException. The partition is mostly graves. Walk through the diagnosis and the re-model.

• Symptom. SELECT ... LIMIT 10 times out; logs show "Scanned over 100000 tombstones."
• Root cause. the delete-after-process pattern leaves a tombstone per processed task at the head of the partition; each read scans them all to reach live tasks.
• Fix. stop deleting; use TTL + TWCS, or re-model so processed tasks live in a different partition (time-bucketed) that ages out whole SSTables.

Question. Diagnose why the queue-partition read times out and re-model it so reads never scan through tombstones.

Input.

Observation	Value
pattern	insert, process, DELETE per task
tombstones scanned per read	> 100,000
gc_grace_seconds	864000 (10 days)
error	TombstoneOverwhelmingException

Code.

-- ANTI-PATTERN: queue in one partition, delete after processing
-- CREATE TABLE task_queue (bucket text, task_id timeuuid, payload text,
--   PRIMARY KEY ((bucket), task_id));
-- DELETE FROM task_queue WHERE bucket='q' AND task_id=...;   -- leaves a grave

-- FIX: time-bucketed partitions + TTL + TWCS; never DELETE, let it expire
CREATE TABLE tasks_by_minute (
    minute_bucket text,        -- 'YYYY-MM-DD HH:MM'
    task_id       timeuuid,
    payload       text,
    done          boolean,
    PRIMARY KEY ((minute_bucket), task_id)
) WITH CLUSTERING ORDER BY (task_id ASC)
  AND default_time_to_live = 86400          -- rows self-expire after 1 day
  AND compaction = {'class':'TimeWindowCompactionStrategy',
                    'compaction_window_unit':'HOURS',
                    'compaction_window_size':1};

-- Mark done with an UPDATE (a normal write), not a DELETE
UPDATE tasks_by_minute SET done = true
WHERE  minute_bucket='2026-09-05 10:04' AND task_id = 5c...;

Step-by-step explanation.

1. The anti-pattern keeps one partition and deletes each processed task. Every delete writes a tombstone that lives for gc_grace_seconds; the "next tasks" read scans all of them at the head before reaching live rows, eventually tripping the tombstone limit.
2. The fix time-buckets the partition by minute, so old buckets naturally stop being read as time moves forward — reads target the current bucket, not a partition full of processed history.
3. default_time_to_live = 86400 makes rows self-expire after a day. Crucially, combined with TimeWindowCompactionStrategy, an entire SSTable whose rows have all expired is dropped wholesale — no per-row tombstone scanning on the read path.
4. Marking a task done is an UPDATE (a normal cell write), not a DELETE, so it creates no tombstone. Processed tasks simply age out with their bucket.
5. TWCS groups data by time window so each SSTable covers a contiguous time range; expired windows are dropped as units. This is the correct compaction strategy for any TTL-driven time-series or queue-like table.

Output.

Design	Tombstones on read path	Read outcome
one partition + DELETE	grows unbounded	TombstoneOverwhelmingException
time-bucket + TTL + TWCS	~0 (whole SSTables dropped)	fast, bounded

Rule of thumb. Never model a queue or churny dataset with delete-after-process in a single partition. Time-bucket the partition, mark state with updates, and let TTL + TWCS drop whole expired SSTables so reads never wade through tombstones.

Worked example — spreading a hot partition by sharding the key

Detailed explanation. A likes_by_post table keys by post_id. A viral post gets millions of likes and reads per second, all hitting one partition on one replica set — a hot partition that caps throughput at one node's capacity. The fix adds a shard component to the partition key and scatters writes across N sub-partitions, then fans reads across them. Walk through it.

• Symptom. one replica set at 100% CPU while the rest of the ring idles; latency spikes on that post.
• Root cause. all traffic for one post_id lands on one partition → one owning replica set.
• Fix. partition key (post_id, shard) with shard = hash(user_id) % N; reads fan out over 0..N-1 and aggregate.

Question. Re-model likes_by_post so a viral post's traffic spreads across N nodes, and show the write and the fan-out read.

Input.

Parameter	Value
hot key	post_id of a viral post
shard count N	16
shard function	hash(user_id) % 16
read	count / list likes for a post

Code.

-- Sharded partition key spreads one post across N partitions
CREATE TABLE likes_by_post (
    post_id   uuid,
    shard     int,             -- 0..N-1
    user_id   uuid,
    liked_ts  timestamp,
    PRIMARY KEY ((post_id, shard), user_id)
);

N = 16

def add_like(session, post_id, user_id, liked_ts):
    shard = hash(user_id) % N          # deterministic per user
    session.execute("""
        INSERT INTO likes_by_post (post_id, shard, user_id, liked_ts)
        VALUES (%s, %s, %s, %s)
    """, (post_id, shard, user_id, liked_ts))

def count_likes(session, post_id):
    stmt = session.prepare(
        "SELECT COUNT(*) FROM likes_by_post WHERE post_id=? AND shard=?")
    total = 0
    for shard in range(N):             # bounded fan-out over N shards
        total += session.execute(stmt, (post_id, shard)).one()[0]
    return total

Step-by-step explanation.

1. The partition key becomes (post_id, shard), so a single post now spans N distinct partitions that hash to N different token ranges — and therefore N different replica sets. One viral post's load is spread N-fold across the ring.
2. shard = hash(user_id) % N is deterministic per user, so a user's like always lands in the same shard (idempotent writes, no double-counting) while the population of users spreads evenly across shards.
3. Reads fan out over all N shards and aggregate. The fan-out is bounded and known (exactly N), the opposite of an unbounded scan. For counts, each shard sub-count is cheap; for listing, merge the N sorted sub-results.
4. N trades write/read spread against fan-out cost: larger N spreads a hotter key further but makes every read touch more partitions. Choose N to match the hottest realistic key, not the average.
5. This is the same cardinality lever as time-bucketing, applied to access skew rather than growth: both increase the effective number of partitions so no single one dominates.

Output.

Design	Partitions per post	Node load	Read cost
((post_id), user_id)	1	one replica set saturates	1 partition
((post_id, shard), user_id)	N=16	spread across ring	N-shard fan-out

Rule of thumb. When a single partition key value is a traffic magnet, add a shard component (% N) to the partition key to multiply its effective cardinality, and fan reads across the N shards. Size N to the hottest key you expect, not the median.

Worked example — bounding a large partition before it stalls compaction

Detailed explanation. A messages_by_room table keys by room_id. A busy support room accumulates years of messages in one partition, which grows past 100 MB. Compaction of that partition slows, repair streams it whole, and reads touching it get sluggish. The fix time-buckets the room partition. Walk through detecting and correcting it.

• Detection. nodetool tablehistograms shows max partition approaching/exceeding ~100 MB.
• Root cause. unbounded rows per room_id over time.
• Fix. partition key (room_id, month_bucket) to bound each partition to a month of the room's messages.

Question. Detect the large partition and re-model messages_by_room so no partition exceeds the size budget while in-room reads stay single-partition.

Input.

Parameter	Value
current max partition	~180 MB (over budget)
growth	unbounded per room over time
bucket	month
target	< 100 MB per partition

Code.

-- Detect (shell): nodetool tablehistograms keyspace messages_by_room
--   → "Partition Size" max column shows ~180 MB → over budget

-- FIX: bound each partition to one month of a room
CREATE TABLE messages_by_room_month (
    room_id       uuid,
    month_bucket  text,        -- 'YYYY-MM'
    message_ts    timestamp,
    message_id    timeuuid,
    sender_id     uuid,
    body          text,
    PRIMARY KEY ((room_id, month_bucket), message_ts, message_id)
) WITH CLUSTERING ORDER BY (message_ts ASC, message_id ASC);

-- In-room read for the current month: one bounded partition
SELECT message_ts, sender_id, body
FROM   messages_by_room_month
WHERE  room_id = 42ab... AND month_bucket = '2026-09'
  AND  message_ts >= '2026-09-01 00:00:00';

Step-by-step explanation.

1. nodetool tablehistograms (or ScyllaDB's equivalent per-table metrics) reports the max partition size; when it approaches ~100 MB you re-model before it becomes an incident, not after.
2. Adding month_bucket to the partition key caps each partition at one month of a room's traffic. Even a busy room now produces bounded ~monthly partitions instead of one ever-growing one.
3. In-room reads for the current month name both partition-key columns and range-scan message_ts — a single bounded partition, fast. Older months are separate partitions read only when the user scrolls back.
4. The trade-off is that "all messages in a room" now fans out over the room's active months — a bounded, query-derived set (same technique as section 2), acceptable because the common read is "recent messages," which is one bucket.
5. Migration is a backfill: read the old oversized partition in ranges and rewrite into month buckets, then cut reads over. Because writes are idempotent (fixed keys), the backfill can run alongside live traffic.

Output.

Design	Max partition size	Recent-month read	Full-history read
((room_id), ...)	~180 MB (over budget)	one huge partition	one huge partition
((room_id, month_bucket), ...)	< 100 MB	1 partition	bounded month fan-out

Rule of thumb. Watch max partition size with tablehistograms and re-model before it crosses ~100 MB. The cure for a growing partition is always a bucketing component on the partition key — the same lever that prevents hot partitions, applied to size instead of access.

Data engineering interview question on wide-column pitfalls

A senior interviewer might ask: "A team's events_by_type table is timing out on reads with TombstoneOverwhelmingException, and nodetool shows one partition at 300 MB while one node runs hot. Walk me through diagnosing all three problems, and give me the re-modeled schema and delete strategy that fixes tombstones, hot partitions, and large partitions at once."

Solution Using time-bucketing plus sharding plus TTL-with-TWCS

-- BEFORE (all three pitfalls): one partition per event_type, DELETEs, unbounded
-- CREATE TABLE events_by_type (event_type text, event_ts timestamp, ...,
--   PRIMARY KEY ((event_type), event_ts));

-- AFTER: bucket by time (bounds size), shard (spreads hot key),
--        TTL + TWCS (kills tombstones)
CREATE TABLE events_by_type_bucketed (
    event_type   text,
    hour_bucket  text,          -- 'YYYY-MM-DD HH' : bounds partition size
    shard        int,           -- 0..N-1          : spreads a hot event_type
    event_ts     timestamp,
    event_id     timeuuid,
    payload      text,
    PRIMARY KEY ((event_type, hour_bucket, shard), event_ts, event_id)
) WITH CLUSTERING ORDER BY (event_ts DESC, event_id DESC)
  AND default_time_to_live = 604800        -- 7-day TTL, no manual DELETEs
  AND compaction = {'class':'TimeWindowCompactionStrategy',
                    'compaction_window_unit':'HOURS',
                    'compaction_window_size':6}
  AND gc_grace_seconds = 43200;            -- lower grace: TWCS drops whole windows

N = 8

def write_event(session, event_type, event_ts, event_id, payload):
    hour_bucket = event_ts.strftime('%Y-%m-%d %H')
    shard = event_id.int % N                      # spread within the hour
    session.execute("""
        INSERT INTO events_by_type_bucketed
          (event_type, hour_bucket, shard, event_ts, event_id, payload)
        VALUES (%s,%s,%s,%s,%s,%s)
    """, (event_type, hour_bucket, shard, event_ts, event_id, payload))

def read_recent(session, event_type, hour_bucket, limit=100):
    stmt = session.prepare("""
        SELECT event_ts, payload FROM events_by_type_bucketed
        WHERE event_type=? AND hour_bucket=? AND shard=? LIMIT ?
    """)
    rows = []
    for shard in range(N):                         # bounded N-shard fan-out
        rows.extend(session.execute(stmt, (event_type, hour_bucket, shard, limit)))
    rows.sort(key=lambda r: r.event_ts, reverse=True)
    return rows[:limit]

Step-by-step trace.

Step	Mechanism	Fixes
1	hour_bucket in partition key	large partitions (bounds each to 1 hour)
2	shard % N in partition key	hot partitions (spreads a hot event_type N-fold)
3	default_time_to_live = 7d	replaces manual DELETEs (no tombstone spam)
4	TimeWindowCompactionStrategy	drops whole expired SSTables → tombstone-free reads
5	gc_grace_seconds = 43200	shorter grace safe because TWCS drops windows wholesale
6	read fan-out over N shards	bounded, sorted-merge, no ALLOW FILTERING

The single re-model attacks all three failure modes with three orthogonal levers: time-bucketing bounds partition size, sharding spreads access, and TTL-with-TWCS eliminates tombstones by expiring whole time-windowed SSTables instead of deleting rows. Reads fan out over the known N shards of the current hour and merge — bounded and fast.

Output:

Failure mode	Before	After
tombstones	DELETE spam → timeout	TTL + TWCS → whole-SSTable drop
hot partition	one node saturates	spread over N shards
large partition	300 MB single partition	< 100 MB per (type, hour, shard)
read cost	ALLOW FILTERING / timeout	N-shard bounded fan-out

Why this works — concept by concept:

• Time-bucketing — hour_bucket in the partition key caps how many rows any one partition can accumulate, holding every partition under the size budget regardless of how long the workload runs.
• Sharding — shard % N multiplies the effective cardinality of a hot event_type, scattering its traffic across N replica sets so no single node becomes the bottleneck.
• TTL with TWCS — a table-level TTL replaces manual DELETEs entirely, and TimeWindowCompactionStrategy drops whole expired SSTables as units, so the read path never scans tombstones — the root cause of the timeout.
• Lower gc_grace_seconds — safe here precisely because TWCS reclaims data by dropping windows rather than by merging away per-row tombstones, so the long grace period needed for row-level tombstone repair is less critical (still coordinate with repair cadence).
• Cost — one write per event, reads fan out over N shards of the target hour and sort-merge — O(N × page). Storage is bounded by the 7-day TTL. The eliminated cost is the unbounded tombstone scan and the single-node saturation; all three failure modes become bounded, monitorable quantities.

Database
Topic — database
Tombstone, hot-partition and large-partition problems

Practice →

Data processing Topic — data-processing Time-bucketing and TTL retention problems

Practice →

---

5. Cassandra vs ScyllaDB and tuning

Same data model, same CQL, different engine — ScyllaDB rewrites Cassandra in C++ with a shard-per-core architecture, so the modeling is identical but the tuning and operations diverge

The mental model in one line: ScyllaDB is a drop-in-compatible reimplementation of Cassandra's data model, CQL, and wire protocol on a shared-nothing, shard-per-core C++ runtime (Seastar) that pins one thread and a slice of RAM to each CPU core and eliminates JVM garbage-collection pauses — so everything you learned about partition keys, clustering keys, denormalization, and pitfalls transfers unchanged, while the operational surface (thread/GC tuning, compaction throughput, shard-aware routing, and cost-per-throughput) shifts because the engine underneath is different. In an interview, the correct framing is "the model is the same, the engine is different"; candidates who think ScyllaDB needs a different schema have misunderstood both.

What is the same — the entire data model.

• CQL and schema. Tables, primary keys, partition and clustering keys, static columns, collections, materialized views, secondary indexes, TTL, and compaction strategies are all present with the same semantics.
• The modeling rules. Query-first design, one-table-per-read denormalization, partition sizing, and the tombstone/hot/large-partition pitfalls apply identically. A schema that is well-modeled for Cassandra is well-modeled for ScyllaDB.
• The distribution model. A token ring, replication factor, tunable consistency, hinted handoff, and repair all carry over. ScyllaDB even speaks the Cassandra wire protocol so most drivers work unchanged.
• The failure modes. Tombstones, hot partitions, and oversized partitions bite ScyllaDB too — the physics of an LSM tree and a token ring do not change with the implementation language.

What is different — the engine and its operations.

• Shard-per-core (Seastar). ScyllaDB runs one shard per physical core, each owning a slice of the data and its own memory, with no locks between shards. This extracts far more throughput per node and gives more predictable latency than Cassandra's thread-pool + shared-heap model.
• No JVM, no GC pauses. ScyllaDB is C++ with its own memory management, so it avoids the stop-the-world GC pauses that cause Cassandra p99 latency spikes and require careful JVM/heap tuning.
• Shard-aware drivers. ScyllaDB-aware drivers route a request not just to the right node but to the right shard (core) on that node, skipping an internal hop. Using a shard-aware driver is a key ScyllaDB tuning step with no Cassandra analogue.
• Self-tuning and cost. ScyllaDB auto-tunes many parameters Cassandra exposes manually (compaction throughput, memory) and typically serves the same workload on fewer nodes — the usual business case for migrating.

Tuning levers that matter for both.

• Compaction strategy. SizeTieredCompactionStrategy (STCS) for write-heavy/general, LeveledCompactionStrategy (LCS) for read-heavy with frequent updates (bounds read amplification at higher write cost), TimeWindowCompactionStrategy (TWCS) for time-series/TTL data. Matching strategy to access pattern is a top-tier tuning decision.
• Replication factor + consistency level. RF sets how many copies exist; the per-query consistency level (ONE, QUORUM, LOCAL_QUORUM, ALL) sets how many replicas must respond. LOCAL_QUORUM on RF=3 is the standard multi-DC production default: strong-enough consistency, DC-local latency.
• Read/write path knobs. Bloom filters, key/row caches, and compression tune the read path; commitlog settings and memtable thresholds tune the write path. ScyllaDB auto-manages more of these than Cassandra.
• Shard-aware routing + prepared statements. Always use prepared statements and a shard-aware (ScyllaDB) or token-aware (Cassandra) driver so requests skip coordinator hops and go straight to an owning replica/shard.

Worked example — choosing a compaction strategy per access pattern

Detailed explanation. Compaction merges SSTables; the strategy determines read amplification, write amplification, and space amplification. Picking the wrong one is a common cause of bad latency on a correctly-modeled table. Walk through matching STCS, LCS, and TWCS to three workloads.

• Write-heavy, whole-row reads. STCS — low write amplification, acceptable read amplification.
• Read-heavy with in-place updates. LCS — bounds SSTables touched per read, at higher write/space cost.
• Time-series with TTL. TWCS — groups by time window, drops whole expired windows.

Question. Assign a compaction strategy to each of three tables and justify the choice from the access pattern.

Input.

Table	Access pattern	Strategy
audit_log (append, TTL 30d)	time-series + TTL	TWCS
user_profile (read-heavy, updates)	reads dominate, mutable	LCS
event_ingest (write-heavy)	writes dominate	STCS

Code.

-- Time-series with TTL → TWCS (drops whole expired windows)
ALTER TABLE audit_log WITH compaction = {
    'class':'TimeWindowCompactionStrategy',
    'compaction_window_unit':'DAYS','compaction_window_size':1};

-- Read-heavy with updates → LCS (bounds read amplification)
ALTER TABLE user_profile WITH compaction = {
    'class':'LeveledCompactionStrategy','sstable_size_in_mb':160};

-- Write-heavy ingest → STCS (low write amplification)
ALTER TABLE event_ingest WITH compaction = {
    'class':'SizeTieredCompactionStrategy','min_threshold':4};

Step-by-step explanation.

1. audit_log is append-only with a 30-day TTL, the textbook TWCS case: each SSTable holds one day's window, and once every row in a window has expired the whole SSTable is dropped — no per-row tombstone compaction, cheap reclamation.
2. user_profile is read-heavy with frequent updates. LCS organizes SSTables into levels so a read touches at most a few SSTables per level, bounding read amplification — the read latency win is worth LCS's higher write and space amplification.
3. event_ingest is write-dominated with rare reads. STCS merges similarly-sized SSTables and imposes the least write amplification, so ingest stays cheap; the higher read amplification is acceptable because reads are rare.
4. Choosing LCS for a write-heavy table would burn write and I/O budget on constant re-leveling; choosing STCS for a read-heavy updated table would let SSTables pile up and inflate read amplification. Strategy must match the dominant operation.
5. On ScyllaDB the same strategies exist and are auto-tuned more aggressively, but the choice of which strategy fits the access pattern is still the modeler's call.

Output.

Strategy	Optimizes	Costs	Fits
STCS	write amplification	read amplification	write-heavy ingest
LCS	read amplification	write + space	read-heavy, updated
TWCS	TTL reclamation	non-time queries	time-series + TTL

Rule of thumb. Match compaction to the dominant operation: STCS for write-heavy, LCS for read-heavy-with-updates, TWCS for anything time-series or TTL-driven. The wrong strategy shows up as latency on a schema that is otherwise correctly modeled.

Worked example — choosing replication factor and consistency level

Detailed explanation. Consistency in a wide-column store is tunable per query via the interplay of replication factor and consistency level. The rule R + W > RF gives read-your-writes strong consistency. Walk through the standard production choices for a single-DC and a multi-DC deployment.

• RF. number of replicas per partition (3 is standard).
• Write CL + Read CL. if writes and reads together cover more than RF replicas, a read always sees the latest write.
• Multi-DC. LOCAL_QUORUM keeps quorum within the local datacenter for latency while replicating across DCs for durability.

Question. Choose RF and consistency levels for (a) a single-DC app needing strong consistency and (b) a multi-DC app needing low local latency, and prove the strong-consistency case with R + W > RF.

Input.

Deployment	RF	Write CL	Read CL
single-DC strong	3	QUORUM (2)	QUORUM (2)
multi-DC low-latency	3 per DC	LOCAL_QUORUM	LOCAL_QUORUM

Code.

from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement

# (a) Single-DC strong consistency: QUORUM reads + QUORUM writes on RF=3
#     W=2, R=2, RF=3 → R + W = 4 > 3 → read always sees latest write
write = SimpleStatement(
    "INSERT INTO account (id, balance) VALUES (%s, %s)",
    consistency_level=ConsistencyLevel.QUORUM)
read = SimpleStatement(
    "SELECT balance FROM account WHERE id = %s",
    consistency_level=ConsistencyLevel.QUORUM)

# (b) Multi-DC low latency: LOCAL_QUORUM stays within the caller's DC
write_local = SimpleStatement(
    "INSERT INTO account (id, balance) VALUES (%s, %s)",
    consistency_level=ConsistencyLevel.LOCAL_QUORUM)
read_local = SimpleStatement(
    "SELECT balance FROM account WHERE id = %s",
    consistency_level=ConsistencyLevel.LOCAL_QUORUM)

Step-by-step explanation.

1. With RF=3, QUORUM means 2 replicas. A QUORUM write reaches 2 replicas and a QUORUM read consults 2; since any two 2-subsets of 3 overlap in at least one replica, the read is guaranteed to see the latest write. That is R + W > RF → 2 + 2 > 3.
2. LOCAL_QUORUM computes quorum within the local datacenter's replicas, so a read/write does not wait on cross-DC network latency while still achieving quorum locally — the standard multi-DC default.
3. ONE (write or read) is faster but gives only eventual consistency: a read at ONE may hit a replica that has not yet received the latest write. Use it for latency-tolerant, high-volume, non-critical data.
4. ALL requires every replica and gives the strongest consistency but zero availability tolerance — one down replica fails the query. Almost never the right production default.
5. The consistency level is a per-query lever, so you can mix: LOCAL_QUORUM for critical account writes, ONE for analytics counters — on the same cluster and even the same table.

Output.

Setting	Consistency	Availability	Latency
RF3 + QUORUM/QUORUM	strong (R+W>RF)	tolerates 1 down	medium
RF3 + LOCAL_QUORUM	strong per DC	tolerates 1 local down	low (DC-local)
RF3 + ONE/ONE	eventual	highest	lowest
RF3 + ALL	strongest	none (any down fails)	highest

Rule of thumb. Default to RF=3 with LOCAL_QUORUM reads and writes for production; it satisfies R + W > RF for strong consistency while keeping latency datacenter-local. Drop to ONE only for data that tolerates staleness, and avoid ALL outside rare admin paths.

Worked example — shard-aware routing on a Cassandra→ScyllaDB migration

Detailed explanation. Migrating a correctly-modeled Cassandra keyspace to ScyllaDB is mostly operational: keep the schema, switch the engine, and adopt shard-aware drivers and self-tuned compaction. The throughput win comes from shard-per-core plus the driver routing to the exact shard. Walk through the migration checklist and the driver change.

• Schema. unchanged — same CQL, same keys.
• Data. stream via nodetool/SSTable load or dual-write + backfill.
• Driver. switch to a shard-aware driver and always use prepared statements.
• Compaction. let ScyllaDB auto-tune; keep TWCS for time-series.

Question. Give the migration checklist and show the driver-level change that unlocks shard-aware routing.

Input.

Item	Cassandra	ScyllaDB
schema / CQL	same	same
routing	token-aware	shard-aware
GC tuning	JVM heap	none (C++)
compaction throughput	manual	auto-tuned

Code.

# Shard-aware routing: prepared statements let the driver compute the
# partition token AND the target shard, skipping a coordinator hop.
from cassandra.cluster import Cluster

cluster = Cluster(["scylla-node-1", "scylla-node-2"])  # shard-aware driver
session = cluster.connect("app_keyspace")

# Prepared statement → driver knows the partition-key columns →
# routes straight to the owning replica AND the owning core (shard).
insert = session.prepare(
    "INSERT INTO readings_by_sensor_day "
    "(sensor_id, day_bucket, reading_ts, temperature) VALUES (?, ?, ?, ?)")

session.execute(insert, (sensor_id, day_bucket, reading_ts, temp))
# No ALLOW FILTERING, no unbounded scans — same schema as Cassandra,
# now landing on the exact shard that owns the token.

Step-by-step explanation.

1. The schema and every table definition carry over verbatim — ScyllaDB speaks the same CQL and stores the same partitioned, sorted data. No re-modeling is required, which is the whole appeal.
2. Prepared statements are the key: because the driver knows which bind parameters are partition-key columns, it computes the token client-side and routes the request to an owning replica (token-aware) and, on ScyllaDB, to the specific core that owns that token (shard-aware), skipping an internal cross-core hop.
3. Removing the JVM removes GC-pause tuning entirely; the p99 latency spikes that Cassandra operators fight with heap and GC flags simply do not occur, so that whole tuning surface disappears.
4. Compaction throughput and memory that Cassandra exposes as manual knobs are auto-tuned by ScyllaDB against the shard model, though the strategy choice (TWCS for time-series, etc.) remains the modeler's decision.
5. The net operational result is typically the same workload on fewer, better-utilized nodes with more predictable latency — the standard business case for the migration, achieved without touching the data model.

Output.

Migration step	Change	Effect
schema	none	model transfers unchanged
driver	shard-aware + prepared	request hits owning core directly
GC tuning	removed	no stop-the-world p99 spikes
node count	usually fewer	shard-per-core throughput

Rule of thumb. Treat a Cassandra→ScyllaDB migration as an engine swap, not a re-model: keep the schema, adopt a shard-aware driver with prepared statements, drop the JVM/GC tuning, and let ScyllaDB auto-tune compaction throughput while you keep owning the strategy choice.

Data engineering interview question on Cassandra vs ScyllaDB and tuning

A senior interviewer might ask: "You run a time-series telemetry workload on Cassandra with painful GC-driven p99 spikes and rising node costs. You are asked to migrate to ScyllaDB and tune it. Walk me through what stays the same, what you change, the compaction and consistency choices for time-series, and how you validate the migration without a re-model."

Solution Using an engine swap with TWCS, LOCAL_QUORUM, and shard-aware routing

-- Schema is IDENTICAL on ScyllaDB — time-series, bucketed, TTL + TWCS
CREATE TABLE telemetry_by_device_hour (
    device_id    uuid,
    hour_bucket  text,          -- 'YYYY-MM-DD HH'
    reading_ts   timestamp,
    metric       text,
    value        double,
    PRIMARY KEY ((device_id, hour_bucket), reading_ts, metric)
) WITH CLUSTERING ORDER BY (reading_ts DESC, metric ASC)
  AND default_time_to_live = 2592000        -- 30-day retention
  AND compaction = {'class':'TimeWindowCompactionStrategy',
                    'compaction_window_unit':'HOURS',
                    'compaction_window_size':1};

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel

cluster = Cluster(["scylla-1", "scylla-2", "scylla-3"])  # shard-aware
session = cluster.connect("telemetry")

write = session.prepare("""
    INSERT INTO telemetry_by_device_hour
      (device_id, hour_bucket, reading_ts, metric, value) VALUES (?,?,?,?,?)""")
write.consistency_level = ConsistencyLevel.LOCAL_QUORUM      # strong, DC-local

read = session.prepare("""
    SELECT reading_ts, value FROM telemetry_by_device_hour
    WHERE device_id=? AND hour_bucket=? AND reading_ts>=?""")
read.consistency_level = ConsistencyLevel.LOCAL_QUORUM

def validate(session_c, session_s, device_id, hour_bucket, since):
    # Dual-read validation: same query on both engines must match
    c = list(session_c.execute(read, (device_id, hour_bucket, since)))
    s = list(session_s.execute(read, (device_id, hour_bucket, since)))
    assert [(r.reading_ts, r.value) for r in c] == [(r.reading_ts, r.value) for r in s]

Step-by-step trace.

Step	Action	Rationale
1	keep schema verbatim	model is engine-agnostic; no re-model
2	TWCS, 1-hour windows	time-series + 30d TTL → whole-window drops
3	shard-aware driver + prepared	route to owning core; skip coordinator hop
4	LOCAL_QUORUM read + write	RF3 → R+W>RF strong, DC-local latency
5	dual-write, then backfill	migrate data with live traffic
6	dual-read validation	assert Cassandra and ScyllaDB agree before cutover

The migration keeps the schema, swaps STCS/GC concerns for TWCS + shard-per-core, routes with a shard-aware driver, and holds strong consistency with LOCAL_QUORUM on RF=3. Validation dual-reads the same partition from both engines and asserts equality before the read cutover, so the migration is provably lossless without any modeling change.

Output:

Dimension	Cassandra (before)	ScyllaDB (after)
p99 latency	GC-driven spikes	flat (no GC)
compaction	manual throughput	auto-tuned, TWCS
routing	token-aware	shard-aware (per core)
consistency	LOCAL_QUORUM	LOCAL_QUORUM (unchanged)
node count	baseline	typically fewer

Why this works — concept by concept:

• Engine swap, not re-model — ScyllaDB implements the same CQL, keys, and distribution, so the bucketed, TTL+TWCS time-series schema transfers unchanged; the migration risk is operational, not architectural.
• TWCS for time-series — one-hour compaction windows plus a 30-day TTL let whole expired SSTables drop as units, keeping reads tombstone-free — the correct strategy on both engines, and what fixes the reclamation cost.
• Shard-per-core + shard-aware routing — Seastar pins a shard to each core with no GC, and the shard-aware driver plus prepared statements route each request straight to the owning core, which is where the throughput and flat-p99 win comes from.
• LOCAL_QUORUM on RF=3 — preserves strong consistency (R + W > RF) at datacenter-local latency across the migration, so consistency semantics do not change under the users.
• Cost — the model's write/read costs are unchanged (one write per reading; bounded per-partition sorted reads); the migration trades JVM/GC tuning and node count for C++ shard-per-core efficiency, validated by a dual-read equality check. Net: same model, lower latency variance, fewer nodes.

Data processing
Topic — data-processing
Compaction, consistency and tuning problems

Practice →

Database
Topic — database
Cassandra and ScyllaDB engine and replication problems

Practice →

---

Cheat sheet — wide-column modeling recipes

• Query-first workflow. List every read with the values it will have and the order it needs; make one table per read whose primary key encodes that access; accept duplication; pick a write path (idempotent fan-out, logged batch, or materialized view). Never start from entities; start from queries.
• Primary-key anatomy. PRIMARY KEY ((partition_cols), clustering_cols). Double parens = composite partition key (hashed together to one token). No inner parens = only the first column is the partition key. Partition key routes to a node; clustering columns sort within the partition and are constrainable left-to-right with one trailing range.
• Partition-size budget. Keep partitions under ~100 MB and ~100k cells. When one partition-key value grows unbounded over time, add a time bucket ((id, day_bucket)); when access is skewed, add a shard ((id, shard) with % N). Both increase effective cardinality.
• Clustering order = on-disk order. WITH CLUSTERING ORDER BY (col DESC) bakes the sort in; the dominant read becomes a zero-cost head slice. Make the clustering key a total order (add a tiebreaker like timeuuid) so cursor pagination never skips or repeats. Never use OFFSET — it does not exist.
• Denormalization sync. Idempotent application fan-out (fixed keys, safe retries) for most paths; logged BATCH when several tables must not diverge under coordinator failure (keep partition count small); materialized view for a single pure re-key at moderate write volume. Partition keys are immutable — "changing" one is delete-plus-insert.
• Tombstone hygiene. Deletes/TTL/null writes create tombstones that the read path must scan until compaction purges them past gc_grace_seconds. Never model a queue as insert-process-delete in one partition. Prefer TTL + TWCS so whole expired SSTables drop; mark state with UPDATE, not DELETE.
• Hot-partition fix. High cardinality is not enough — access must be even. A celebrity key, a global/default sentinel, or a monotonic "now" bucket saturates one replica set. Shard the key (% N) and fan reads across the N shards; size N to the hottest key, not the median. Detect with nodetool toppartitions / per-shard metrics.
• Bucketing formula. Choose bucket granularity so a full partition sits under ~100 MB: rows_per_bucket × row_size < 100 MB. Too coarse → large partitions; too fine → wide multi-bucket fan-out. Drive multi-bucket reads from the query's own range, never ALLOW FILTERING.
• Compaction-strategy picker. STCS for write-heavy ingest (low write amplification); LCS for read-heavy tables with updates (bounds read amplification); TWCS for time-series/TTL (drops whole expired windows). The wrong strategy shows up as latency on a correctly-modeled table.
• Consistency picker. RF=3 + LOCAL_QUORUM read/write is the production default: R + W > RF gives strong consistency at DC-local latency. ONE for staleness-tolerant high-volume data; ALL almost never (any down replica fails the query). Consistency is a per-query lever — mix as needed.
• Cassandra vs ScyllaDB. Same data model, CQL, keys, distribution, and pitfalls. ScyllaDB differs in the engine: C++ shard-per-core (Seastar), no JVM/GC pauses, shard-aware drivers, auto-tuned compaction, usually fewer nodes. Migrate by swapping the engine, not re-modeling; adopt a shard-aware driver with prepared statements.
• ALLOW FILTERING ban. If a read needs ALLOW FILTERING, the model is wrong — build another table or change the key. ALLOW FILTERING is an O(partition) or O(cluster) scan and is close to disqualifying as a proposed production read in an interview.

Frequently asked questions

What is wide-column data modeling in one sentence?

Wide-column data modeling is the practice of designing tables around the exact read queries an application will issue rather than around normalized entities, because a wide-column store like Cassandra or ScyllaDB has no joins and no ad-hoc filtering and can only serve reads that name a partition key and take a sorted slice of clustering keys. You enumerate the reads first, then create one denormalized table per read whose primary key makes that read a single-partition access, duplicating data across tables on write so every read stays O(1) routing plus a contiguous disk scan. The mantra is "model for the query, not the entity" — the schema is effectively a hand-written query plan frozen at design time.

Partition key vs clustering key — what's the difference?

The partition key is the part of the primary key that is hashed to a token to decide which node(s) store the row, and it is the only value that makes a read efficient — every read must supply the full partition key with equality. The clustering key sorts rows within a partition on disk and determines the order you can range-scan and paginate over — clustering columns can be constrained left-to-right with a single trailing range. In PRIMARY KEY ((sensor_id, day), reading_ts), (sensor_id, day) is a composite partition key hashed together, and reading_ts is the clustering key that orders readings inside each partition. Getting the partition key wrong causes hot or oversized partitions; getting the clustering key wrong makes the reads you need impossible.

Why does Cassandra force denormalization?

Because there are no joins and no efficient cross-partition queries, the only way to serve a read is to have a table whose primary key already matches it — so if you read the same data two ways (by id and by email, by customer and by status), you need two tables holding that data, each keyed differently. Denormalization is therefore not an optimization you reach for reluctantly; it is the fundamental modeling primitive. Writes and storage are cheap in an LSM-tree architecture, so duplicating a fact across several tables and paying a bounded write fan-out is the correct trade for flat, single-partition read latency. Consistency across the copies becomes the application's responsibility, handled with idempotent writes, logged batches where atomicity matters, and periodic reconciliation from a source-of-truth table.

What are tombstones and why do they cause read timeouts?

A tombstone is the marker Cassandra/ScyllaDB writes when you delete a row, let a TTL expire, or write a null — because SSTables are immutable, data is never removed in place; instead a tombstone shadows the old value until compaction purges it after gc_grace_seconds (default 10 days, needed for repair). Tombstones cause read timeouts because a read must scan and merge every tombstone in the requested range to determine what is actually live, so a partition with tens of thousands of tombstones makes reads do enormous work — and past a threshold the coordinator aborts with TombstoneOverwhelmingException. The classic trigger is modeling a queue as insert-process-delete in one partition, which builds a wall of tombstones at the partition head. The fix is to avoid deletes: use TTL with TimeWindowCompactionStrategy so whole expired SSTables drop, and mark state changes with UPDATE instead of DELETE.

How big can a Cassandra partition be?

Technically a partition can hold up to about two billion cells, but the practical budget is far lower: keep partitions under roughly 100 MB and 100,000 cells (rows times columns per row). Beyond that, compaction slows, memory pressure rises, repair has to stream huge partitions, and reads that touch the partition degrade. When a partition-key value would accumulate unbounded rows over time, you bound it by adding a bucketing component to the partition key — a time bucket like (device_id, day) for time-series, or a modulo shard like (post_id, shard) for a hot key. Monitor the maximum partition size with nodetool tablehistograms (or ScyllaDB's per-table metrics) and re-model before the max approaches the budget rather than after it becomes an incident.

Cassandra vs ScyllaDB — which should I pick?

They share the same data model, CQL, primary-key semantics, distribution model, and failure modes, so anything you model correctly for one is correct for the other — the choice is about the engine, not the schema. ScyllaDB is a C++ reimplementation on a shard-per-core runtime (Seastar) with no JVM garbage-collection pauses, shard-aware drivers that route requests to the exact owning core, and heavy auto-tuning, which typically delivers more predictable p99 latency and higher throughput per node, so the same workload often runs on fewer machines. Cassandra has the larger, older ecosystem and is the safe default if you already run the JVM stack and its tooling. Pick ScyllaDB when latency predictability, throughput density, or node cost are the pain points and you can adopt shard-aware drivers; pick Cassandra when ecosystem maturity and existing operational familiarity outweigh the efficiency gains — and remember migrating between them is an engine swap, not a re-model.

Practice on PipeCode

• Drill the database practice library → for the wide-column, partition-key, clustering-key, tombstone, and hot-partition problems senior interviewers love.
• Rehearse on the data-processing practice library → for the bucketing, TTL retention, compaction, and time-series pipeline patterns.
• Sharpen your schema instincts on the dimensional-modeling practice library → for query-first denormalization and one-table-per-read design.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the wide-column modeling decisions against real graded inputs.

Lock in wide-column modeling muscle memory

Docs explain syntax. PipeCode drills explain the decision — when a partition key hot-spots, when a partition grows past the budget, when denormalization needs a logged batch, when tombstones turn a delete into a read-time landmine, and when to reach for ScyllaDB's shard-per-core engine. Pipecode.ai is Leetcode for Data Engineering — query-first practice tuned for the production trade-offs senior data engineers actually face.

Practice database problems →
Practice data-modeling problems →