Cassandra Data Modeling: From CQL to Production

Imagine you're building a real-time analytics platform that tracks millions of user events per second. You need a database that can handle massive write throughput, scale horizontally, and remain available even if some servers fail. Traditional relational databases with their joins, transactions, and normalized schemas would buckle under this load. Enter Apache Cassandra: a distributed NoSQL database designed for high availability and linear scalability. But Cassandra's power comes with a price: you must abandon decades of relational data modeling intuition. In Cassandra, you don't model data based on entities and relationships; you model based on queries. This tutorial will take you from CQL basics to production-ready data modeling patterns. You'll learn how to design primary keys, use clustering for sorting, denormalize for performance, and avoid common pitfalls like hot partitions and tombstones. By the end, you'll be able to design schemas that scale to petabytes without breaking a sweat.

Understanding CQL and Cassandra's Data Model

Cassandra Query Language (CQL) looks like SQL, but its data model is fundamentally different. In Cassandra, a table is a logical grouping of rows, but rows are distributed across nodes based on the partition key. Each row is uniquely identified by its primary key, which consists of a partition key (one or more columns) and optional clustering columns. The partition key determines which node stores the data, while clustering columns define the sort order within a partition. Unlike SQL, you cannot join tables; you must denormalize data into a single table per query pattern. CQL supports SELECT, INSERT, UPDATE, DELETE, and batch operations, but with restrictions: WHERE clauses can only include primary key columns (unless using ALLOW FILTERING, which is discouraged). Understanding these constraints is the first step to effective data modeling.

CREATE TABLE user_events (
  user_id UUID,
  event_time TIMESTAMP,
  event_type TEXT,
  payload TEXT,
  PRIMARY KEY ((user_id), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

Designing Primary Keys for Query Patterns

In Cassandra, you design tables to serve specific queries. For example, if you need to fetch recent events for a user, you'd use a table with partition key user_id and clustering column event_time sorted descending. If you need to query events by type across users, you'd need a different table with partition key event_type and clustering column event_time. This is called 'query-first design.' You may end up with multiple tables storing the same data in different orders. This duplication is intentional and necessary for performance. When designing, consider the cardinality of your partition key: too low (e.g., boolean) leads to large partitions; too high (e.g., UUID) leads to many small partitions. Aim for a partition size between 10 MB and 100 MB. Use compound partition keys to achieve this balance.

-- Table for fetching recent events by user
CREATE TABLE user_recent_events (
  user_id UUID,
  event_time TIMESTAMP,
  event_type TEXT,
  payload TEXT,
  PRIMARY KEY ((user_id), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

-- Table for fetching events by type across users (with time bucket)
CREATE TABLE events_by_type (
  event_type TEXT,
  month_bucket TEXT,
  event_time TIMESTAMP,
  user_id UUID,
  payload TEXT,
  PRIMARY KEY ((event_type, month_bucket), event_time, user_id)
) WITH CLUSTERING ORDER BY (event_time DESC, user_id ASC);

Clustering Columns and Sort Order

Clustering columns control the physical order of rows within a partition. You can specify ascending or descending order per column. This is crucial for queries that need the most recent data first. For example, CLUSTERING ORDER BY (event_time DESC) ensures that the latest event is at the top of the partition. You can also use multiple clustering columns for hierarchical sorting, like (event_time DESC, user_id ASC). However, you cannot change the clustering order after table creation; you must drop and recreate the table. Also, note that clustering columns are part of the primary key, so they enforce uniqueness. If you need multiple rows with the same clustering values, you must add a distinguishing column (e.g., a UUID) to the clustering key.

CREATE TABLE sensor_data (
  sensor_id UUID,
  date DATE,
  hour INT,
  reading DOUBLE,
  PRIMARY KEY ((sensor_id, date), hour)
) WITH CLUSTERING ORDER BY (hour ASC);

-- Insert data
INSERT INTO sensor_data (sensor_id, date, hour, reading) VALUES (uuid(), '2025-03-01', 10, 23.5);
INSERT INTO sensor_data (sensor_id, date, hour, reading) VALUES (uuid(), '2025-03-01', 11, 24.0);

-- Query: get readings for a sensor on a specific date, sorted by hour
SELECT * FROM sensor_data WHERE sensor_id = ? AND date = '2025-03-01';

Denormalization and Data Duplication

Cassandra does not support joins. To retrieve related data in a single query, you must denormalize: store all the data you need in one table. This means duplicating data across multiple tables. For example, in an e-commerce system, you might have an orders_by_user table and an orders_by_status table, both containing the same order details but organized differently. Denormalization improves read performance but increases write complexity and storage. You must ensure consistency across duplicates, often by using batch writes or application-level logic. Also, consider using user-defined types (UDTs) to group related fields, but be cautious: UDTs cannot be indexed and can complicate schema evolution.

-- Table for querying orders by user
CREATE TABLE orders_by_user (
  user_id UUID,
  order_id UUID,
  order_date TIMESTAMP,
  status TEXT,
  total DECIMAL,
  PRIMARY KEY ((user_id), order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC);

-- Table for querying orders by status (with time bucket)
CREATE TABLE orders_by_status (
  status TEXT,
  month_bucket TEXT,
  order_date TIMESTAMP,
  user_id UUID,
  order_id UUID,
  total DECIMAL,
  PRIMARY KEY ((status, month_bucket), order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC);

-- Batch insert to keep both tables consistent
BEGIN BATCH
  INSERT INTO orders_by_user (user_id, order_id, order_date, status, total) VALUES (?, ?, ?, ?, ?);
  INSERT INTO orders_by_status (status, month_bucket, order_date, user_id, order_id, total) VALUES (?, ?, ?, ?, ?, ?);
APPLY BATCH;

Secondary Indexes and Materialized Views

Cassandra offers secondary indexes and materialized views as alternatives to denormalization, but they come with caveats. Secondary indexes are best for low-cardinality columns (e.g., status) and are not recommended for high-cardinality columns (e.g., user_id). They can cause performance issues because they involve querying all nodes. Materialized views automatically maintain a denormalized copy of data based on a new primary key. However, they have limitations: you cannot use them with certain data types (e.g., collections), and they can impact write performance. In production, many teams prefer explicit denormalization over materialized views for better control and predictability.

-- Secondary index on status (low cardinality)
CREATE INDEX ON orders_by_user (status);

-- Materialized view to query by status
CREATE MATERIALIZED VIEW orders_by_status_mv AS
  SELECT * FROM orders_by_user
  WHERE status IS NOT NULL AND user_id IS NOT NULL AND order_date IS NOT NULL AND order_id IS NOT NULL
  PRIMARY KEY ((status), order_date, user_id, order_id)
  WITH CLUSTERING ORDER BY (order_date DESC);

Handling Time Series Data

Time series data is a common use case for Cassandra. The key challenge is to avoid unbounded partition growth. Use time bucketing: include a date or month in the partition key. For example, PRIMARY KEY ((sensor_id, date), hour). This limits each partition to one day's data. You can also use a sliding window of buckets (e.g., last 30 days) and delete old partitions. Another pattern is to use a separate table for each time granularity (e.g., raw events, hourly aggregates). For high-frequency data, consider using a wider row with multiple columns per time slot (e.g., one column per minute) to reduce partition count.

-- Time series with daily partition
CREATE TABLE sensor_readings (
  sensor_id UUID,
  date DATE,
  hour INT,
  minute INT,
  reading DOUBLE,
  PRIMARY KEY ((sensor_id, date), hour, minute)
) WITH CLUSTERING ORDER BY (hour ASC, minute ASC);

-- Insert a reading
INSERT INTO sensor_readings (sensor_id, date, hour, minute, reading) VALUES (uuid(), '2025-03-01', 14, 30, 22.5);

-- Query all readings for a sensor on a specific date
SELECT * FROM sensor_readings WHERE sensor_id = ? AND date = '2025-03-01';

Common Pitfalls and Anti-Patterns

Several anti-patterns plague Cassandra data modeling. 1) Large partitions: Partitions over 100 MB cause garbage collection pauses and slow repairs. 2) Hot spots: Uneven data distribution due to poor partition key choice (e.g., using a single partition key for a popular entity). 3) Too many tombstones: Frequent deletes or updates with TTL can create tombstones that slow reads. 4) ALLOW FILTERING: Using this in production queries indicates a missing index or table design. 5) Overusing batches: Large batches (more than 10 statements) can cause coordinator overload. 6) Ignoring compaction: Default compaction strategies may not suit your workload; use SizeTieredCompactionStrategy for write-heavy, LeveledCompactionStrategy for read-heavy.

-- Anti-pattern: Using ALLOW FILTERING for non-primary key column
SELECT * FROM user_events WHERE event_type = 'click' ALLOW FILTERING;

-- Better: Create a table with event_type as partition key
CREATE TABLE events_by_type (
  event_type TEXT,
  event_time TIMESTAMP,
  user_id UUID,
  PRIMARY KEY ((event_type), event_time)
);

Production Best Practices

In production, follow these best practices: 1) Test with realistic data volumes using tools like cassandra-stress. 2) Monitor partition size with nodetool tablehistograms. 3) Use consistency level LOCAL_QUORUM for most operations to balance availability and consistency. 4) Set appropriate compaction strategy: LeveledCompactionStrategy for read-heavy, SizeTieredCompactionStrategy for write-heavy. 5) Use TTL for time-series data to avoid manual deletes. 6) Avoid cross-datacenter writes if possible; use local consistency. 7) Plan for schema evolution: Adding columns is cheap, but changing primary keys requires creating new tables. 8) Use lightweight transactions (LWT) sparingly; they are expensive. 9) Backup regularly using nodetool snapshot. 10) Design for failure: Assume nodes will fail and ensure your application can handle retries.

-- Example: Using TTL and compaction settings
CREATE TABLE events (
  event_id UUID,
  event_time TIMESTAMP,
  data TEXT,
  PRIMARY KEY ((event_id))
) WITH default_time_to_live = 2592000  -- 30 days
  AND compaction = { 'class': 'LeveledCompactionStrategy' };