Data Modeling and CQL Schema Design
This domain tests query-first schema design in CQL: choosing partition keys that spread data, clustering columns that support required sort and range filters, and avoiding patterns Cassandra cannot serve efficiently.
Core rules
- Start from the query. Each table is designed for one access pattern; denormalize into additional tables rather than joining.
- Primary key equals partition key plus optional clustering columns. PRIMARY KEY ((a,b), c, d) has composite partition key (a,b) and clustering c,d. The partition key determines the replica set and cannot be updated in place.
- Keep partitions bounded. Large partitions increase heap pressure, repair cost, and read latency. A practical target is under 100 MB and well under 100k rows, though limits depend on workload.
- Clustering order controls on-disk order and supports range predicates on clustering columns. Use WITH CLUSTERING ORDER BY for descending time series.
- Secondary indexes suit low-cardinality local lookups; they are not a replacement for a query table. Materialized views add write cost and operational risk. Batches are for atomicity within a partition, not bulk loading.
Worked example
For latest events for a user, define PRIMARY KEY ((user_id), event_time, event_id) with CLUSTERING ORDER BY (event_time DESC). Query WHERE user_id=? AND event_time > ? is efficient: one partition, ordered scan. Querying by event_time alone would require ALLOW FILTERING and scan all partitions; add a table keyed by ((event_date), event_time, user_id) if that query is required.
Traps
Choosing a low-cardinality partition key such as status or country, causing hot partitions; using ALLOW FILTERING in production; assuming collections are indexed; updating a primary key; and treating USING TTL as a schema-wide retention policy without planning tombstone compaction.