Cassandra Data Modeling skill for Codex

Done well means: every application query reads one partition (or a small known set) by its full partition key, no partition can grow without bound, writes spread evenly across nodes, deletes and TTLs do not pile up tombstones in read paths, and the consistency level for each query is a deliberate choice.

Work query-first

Relational habit (model entities, then write queries) fails here. Reverse it:

- [ ] 1. Read AGENTS.md, existing CQL (*.cql, migrations) and driver setup
- [ ] 2. List every access pattern with its filters, sort order, limit and frequency
- [ ] 3. Design one table per access pattern (or a shared table if keys match exactly)
- [ ] 4. Size each partition: rows per partition and bytes over its lifetime
- [ ] 5. Decide write path: which tables each business event writes to
- [ ] 6. Pick TTL, compaction strategy and consistency per table
- [ ] 7. Load test with production-like key distribution

Access pattern table (write this down before any CQL):

#	Query	Key inputs	Sort	Volume
Q1	Latest readings for a sensor	sensor_id	time desc	50k reads/s
Q2	Readings for a sensor on a day	sensor_id, day	time asc	200/s
Q3	Sensors in a site	site_id	name	20/s

Keys

CREATE TABLE readings_by_sensor_day (
  sensor_id  uuid,
  day        date,
  ts         timestamp,
  value      double,
  unit       text static,          -- one value per partition
  PRIMARY KEY ((sensor_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC)
  AND default_time_to_live = 7776000          -- 90 days
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_unit': 'DAYS',
                    'compaction_window_size': 3};   -- 30 windows over the TTL

• Partition key (sensor_id, day): decides which nodes hold the data. Must be supplied with equality in every query. Composite partition keys bound growth.
• Clustering columns ts: sort rows within a partition; support range and slice queries in the declared order, left to right, without skipping columns.
• Static columns store per-partition values once.

Partition size limits

Keep partitions comfortably under about 100 MB and around 100k rows; well below is better. Estimate:

rows per partition  = writes per key per second x bucket seconds
partition bytes     = rows x (sum of column value sizes + per-cell overhead)

A sensor writing once a second for a day is 86,400 rows: acceptable with small rows; at 10 Hz, bucket by hour instead. If a few keys are much hotter than the rest (one huge tenant), add a shard component: PRIMARY KEY ((tenant_id, day, shard), ts) with shard = hash(event_id) % 8, and read all shards in parallel.

Hot partitions

All writes for one partition land on the same replicas. A partition key of status or date alone puts all traffic on one node set. Partition keys need high cardinality and even access.

Denormalize per query

One business event often writes to several tables:

BEGIN BATCH
  INSERT INTO orders_by_customer (customer_id, order_ts, order_id, total) VALUES (?, ?, ?, ?);
  INSERT INTO orders_by_id (order_id, customer_id, order_ts, total) VALUES (?, ?, ?, ?);
APPLY BATCH;

• A logged batch guarantees all statements eventually apply (atomic, not isolated). Use it for keeping denormalized copies consistent, not for speed: multi-partition batches are slower than separate async writes.
• Unlogged batches only help when every statement targets the same partition.
• Materialized views are experimental and disabled by default in Cassandra 4.0+ (materialized_views_enabled); maintain denormalized tables from the application.

Secondary access paths

Need	Option
Query by another key at scale	Another table keyed by it (default)
Occasional filter inside a known partition	Clustering-column slice, or filter in the app
Low-frequency lookups by a non-key column	Cassandra 5.0 SAI index (CREATE INDEX ... USING 'sai'); ScyllaDB global or local secondary index
Free-text or arbitrary filters	Export to a search engine, not CQL

ALLOW FILTERING in an application query means a design gap: it scans partitions and fails under load. Accept it only for small admin queries restricted to one partition.

Tombstones and deletes

Deletes, TTL expiry and inserting null all write tombstones that reads must skip until compaction purges them after gc_grace_seconds (default 864000, 10 days).

• Never model a queue or “pending work” list in Cassandra: consumers delete heads and reads scan ever-growing tombstone runs. Use Kafka or a real queue.
• Do not bind null for unset columns in prepared statements; leave them unset (drivers support unset values on protocol v4+).
• For TTL data, use TWCS with a window that makes roughly 20-40 windows over the TTL, set the same default_time_to_live for the whole table, and avoid updating old rows.
• Range deletes (DELETE ... WHERE pk = ? AND ts < ?) write one range tombstone instead of many cell tombstones.
• Reads that hit tombstone_warn_threshold (1,000) log warnings; at tombstone_failure_threshold (100,000) the read fails.

Shortening gc_grace_seconds is only safe when repairs complete more often than the new value; otherwise deleted data can reappear.

Consistency and replication

CREATE KEYSPACE telemetry WITH replication =
  {'class': 'NetworkTopologyStrategy', 'dc1': 3, 'dc2': 3};

• Use NetworkTopologyStrategy with RF 3 per data center in production. SimpleStrategy is for single-node development.
• LOCAL_QUORUM for reads and writes gives read-your-writes within a data center (2 of 3 replicas both ways) and survives one node down per DC. This is the default choice.
• ONE / LOCAL_ONE for data where staleness is fine and latency matters.
• Lightweight transactions (INSERT ... IF NOT EXISTS, UPDATE ... IF col = ?) use Paxos: several round trips and much lower throughput. Use them for uniqueness claims (usernames), never on hot paths. Do not mix LWT and non-LWT writes on the same cells.
• Counters live in counter-only tables and are not idempotent on retry.

ScyllaDB differences

• Same CQL and drivers; prefer Scylla’s shard-aware driver forks for best latency.
• Large partitions are recorded in system.large_partitions, rows and cells in system.large_rows / system.large_cells: check them after load tests.
• Compaction defaults and tablets-based data distribution differ by version; read the Scylla docs for the installed release before copying Cassandra tuning.

Driver usage

• Prepare statements once and reuse; never build CQL by string concatenation.
• Token-aware, DC-aware load balancing so requests go straight to a replica.
• Page large reads (fetch_size), and use async writes with bounded concurrency.
• Set idempotence on statements that are safe to retry (most inserts with fixed keys) so speculative execution and retries can apply.

Verification

nodetool tablestats telemetry.readings_by_sensor_day   # max partition size, tombstones per slice
nodetool tablehistograms telemetry readings_by_sensor_day

• Every access pattern maps to a query with the full partition key; none uses ALLOW FILTERING
• Partition size estimate written down; load test max partition below the limit
• Tombstones per read slice low (tablehistograms / tracing: TRACING ON in cqlsh)
• Write path writes every denormalized table for each event (test that checks all copies)
• Consistency level set explicitly per statement type in driver config

Gotchas

• Primary keys cannot be altered. Changing the key means a new table, dual writes, a backfill and a cutover ($data-backfills).
• IN on the partition key fans out to many nodes through one coordinator; prefer parallel async single-partition queries.
• Collections (list, set, map) are read whole; keep them small. Lists have read-before-write semantics for some operations.
• TRUNCATE and DROP TABLE are irreversible and cluster-wide: confirm with the user before running them anywhere shared, and check snapshot settings first.
• Clock skew between clients affects last-write-wins; keep NTP healthy or use server-side timestamps.

Output template

| # | Query | Table | Partition key | Clustering | CL |
|---|---|---|---|---|---|
| Q1 | Latest readings for sensor | readings_by_sensor_day | (sensor_id, day) | ts DESC | LOCAL_QUORUM |

**Partition sizing:** 1 Hz x 86,400 s = 86k rows x ~40 B = 3.5 MB per partition
**Retention:** TTL 90 d, TWCS 3-day windows (30 windows)
**Write path:** reading event -> 2 tables (logged batch); alert event -> 1 table
**Risks:** tenant acme is 30x average; sharded by event hash into 8 buckets