Cassandra Data Model by Example: A Comprehensive Guide

cassandra, data model, data modeling

Building a performance optimized data model in Cassandra may seem overwhelming. However, following a few simple best practices when initially designing your schema will help reduce refactoring nightmares down the road.

Let’s jump right in…

Cassandra Data Modeling Best Practices

1. Create a table for each query pattern

Designing a table for each query pattern is fundamental in Cassandra because of its write-friendly nature. This approach optimizes read performance by reducing the need for joins, which can be expensive in a distributed database. Data is denormalized, meaning that it’s stored redundantly to cater to each specific query, thereby minimizing read latency. Each table is structured in a way that it efficiently supports a particular type of query, reducing the time taken for data retrieval. This unique approach also alleviates the need for complex indexing and helps in delivering high-performance read operations.


Let’s use a simple example related to an online book store.

We have two major types of queries we want to support:

  1. Retrieve all the books written by a specific author.
  2. Retrieve all the books purchased by a specific customer.

Instead of trying to make one table fit both queries, we make a table for each query.

1. Books By Author

CREATE TABLE books_by_author (
    author_name text,
    book_id UUID,
    book_title text,
    publication_year int,
    PRIMARY KEY (author_name, publication_year, book_id)

With this table structure, we can easily fetch all books by a specific author, sorted by the year of publication.

2. Books By Customer

CREATE TABLE books_by_customer (
    customer_id UUID,
    book_id UUID,
    purchase_date timestamp,
    book_title text,
    PRIMARY KEY (customer_id, purchase_date, book_id)

In this table, we can fetch all books purchased by a specific customer, ordered by the purchase date in descending order.

As you can see, each table is modeled to suit the query it needs to support, providing an efficient means of data retrieval. This practice of query-driven data modeling is a central tenet of designing performant applications in Cassandra.

2. Use Cassandra Primary Key Design

Primary keys play a crucial role in Apache Cassandra as they help in both data identification and distribution across nodes in the cluster. In Cassandra, a primary key is made up of one or more columns of a table.

A primary key in Cassandra consists of two parts:

  1. Partition key: The first part of the primary key, responsible for data distribution across nodes.
  2. Clustering columns: The rest of the primary key, used to sort data within the partition.


Consider an online store and a table to track customer orders…

CREATE TABLE customer_orders (
    customer_id int,
    order_id int,
    order_date date,
    product_id int,
    quantity int,
    PRIMARY KEY ((customer_id), order_date, order_id)

In this table:

  • customer_id is the partition key, which is used to distribute data across nodes.
  • order_date and order_id are clustering columns that sort the data within the partition.

This design allows us to query all orders placed by a specific customer, sorted in descending order by the order date. The choice of partition key and clustering columns should be guided by the queries your application needs to support, along with considerations for data distribution and performance.

3. Use Partition Key Optimization

The partition key’s role is to distribute data across the nodes of the Cassandra cluster. The partition key forms an integral part of Cassandra’s partitioning strategy.

Choosing a good partition key in Cassandra is essential for effective data distribution and retrieval. Here are some considerations to guide your selection:

  1. Data Distribution: Ideally, a partition key should distribute data evenly across all nodes in the cluster to avoid any single point of failure or performance bottleneck. A good partition key is typically a column with high cardinality (many unique values).
  2. Query Performance: Partition keys play a crucial role in query performance as Cassandra only allows queries that include the partition key. Therefore, you should choose a partition key that is often included in your queries.
  3. Data Volume: Each partition in Cassandra has an upper limit on how much data it can store. If your use-case involves writing a lot of data for a particular partition key, you might need to consider composite partition keys or other design alternatives to ensure you don’t exceed this limit.
  4. Data Locality: Cassandra stores all the rows with the same partition key on the same node (or replica nodes, depending on your replication factor). Therefore, if your use-case frequently requires accessing multiple rows that are related or used together, it may be beneficial to use a partition key that ensures these rows are stored together.

4. Use Data Denormalization Techniques

One of the Cassandra denormalization techniques is to store redundant copies of data. Denormalization can often be a good trade-off for improved read performance in a distributed database like Cassandra. Redundant data helps in reducing the need for join operations, a costly operation in distributed databases. However, it’s crucial to balance denormalization with the increased storage requirements and complexity in maintaining consistency.


Let’s look at a practical example using a social media application where users can have friends, and we want to support two types of queries:

  1. Fetching a list of all friends of a specific user.
  2. Fetching a list of all users who are friends with a specific user.

We could create two tables to support these queries:

1. friends_by_user Table

CREATE TABLE friends_by_user (
    user_id UUID,
    friend_id UUID,
    friend_name text,
    timestamp timestamp,
    PRIMARY KEY (user_id, timestamp, friend_id)

This table is designed to efficiently answer the query “Who are all the friends of this user?”.

2. users_who_are_friends Table

CREATE TABLE users_who_are_friends (
    user_id UUID,
    friend_id UUID,
    user_name text,
    timestamp timestamp,
    PRIMARY KEY (friend_id, timestamp, user_id)

This table is designed to efficiently answer the query “Who are all the users who are friends with this user?”.

In this case, the same “friendship” data is stored twice: once from the perspective of the user_id and once from the perspective of the friend_id. This is an example of denormalization, which increases storage usage but improves read performance by eliminating the need for costly join operations.

See also  Install Cassandra on Windows 10 in 4 Easy Steps

5. Use Appropriate Data Types

Cassandra supports a variety of data types, including text, numeric, date/time, and boolean, among others. It also supports complex types like lists, maps, and sets. Understanding and using the appropriate data type is crucial for efficient data modeling. Moreover, the correct use of data types can optimize data storage and retrieval in Cassandra.

6. Use Wide Rows

Cassandra allows you to design tables to have a large number of columns, resulting in wide rows. Wide rows are beneficial as they allow storing and accessing related data in a single disk seek operation, thereby improving read performance.

This is achieved through proper data modeling using compound primary keys, which consist of a partition key and one or more clustering columns.


Here’s an example of how to configure wide rows for a music streaming application, where we want to store user’s song plays:

CREATE TABLE user_song_plays (
    user_id UUID,
    song_id UUID,
    play_timestamp timestamp,
    artist_name text,
    song_title text,
    PRIMARY KEY (user_id, play_timestamp, song_id)

In this case, user_id is the partition key, and play_timestamp and song_id are clustering columns. Each unique combination of partition key and clustering columns forms a separate cell in the row. Given that play_timestamp and song_id can have many unique combinations for each user, we end up with a wide row per user. This row can store the data of all songs played by a user, sorted in descending order by play time.

It’s worth noting that while wide rows can be beneficial in certain scenarios, they should be used judiciously as extremely wide rows can lead to operational issues in Cassandra. For example, it can lead to hotspots in your cluster if a wide row is read or written to more frequently than other data, or it can lead to issues with compaction and JVM garbage collection.

Another Example

apache cassandra data model example

Lets consider an example of a data model in Apache Cassandra for a blogging platform. This model would need to account for users, blogs, and the comments on the blogs.

Firstly, we need to remember that Cassandra is a wide-column store, and data modeling is query-based. So, you design your data model based on the queries you want to support.

Here are some simplified table structures for our blogging platform:

1. User Table

    user_id UUID PRIMARY KEY,
    username text,
    email text,
    password text

The user_id is a UUID and is the primary key, which uniquely identifies each user in the system.

2. Blog Table

    blog_id UUID,
    author_id UUID,
    title text,
    content text,
    timestamp timestamp,
    PRIMARY KEY (blog_id, author_id)

In this table, blog_id is the partition key, and author_id is the clustering column. This combination allows us to retrieve all blogs by an author efficiently.

3. Comments Table

CREATE TABLE comments (
    comment_id UUID,
    blog_id UUID,
    user_id UUID,
    content text,
    timestamp timestamp,
    PRIMARY KEY (blog_id, comment_id)

In the comments table, blog_id is the partition key, and comment_id is the clustering column. This design allows us to retrieve all comments for a specific blog efficiently.

These tables are relatively simple and serve as a starting point. Additional features, such as tags for blogs or likes for comments, would require more tables or adjustments to these existing ones. The key is to design your data model based on the queries you’ll need to support.

Mastering data modeling in Cassandra is essential for leveraging its full potential. By following the best practices and understanding the unique features of Cassandra, such as primary and partition keys, wide rows, compaction strategies, and more, we can create a robust and performance-optimized database solution. The journey to mastering Cassandra starts with understanding the fundamentals and extends to continuous learning and adapting to new insights and techniques.

Frequently Asked Questions (FAQ)

What are Cassandra compaction strategies?

Cassandra merges SSTables and discards old data via a process known as compaction. It supports different compaction strategies like Size Tiered, Leveled, and Time Windowed, each suited for specific use cases. Choosing the right compaction strategy is essential for efficient disk usage and read performance. Your choice of strategy depends on your write and read patterns, as well as your hardware configuration.

How does data modeling affect query performance?

Query performance is a key aspect of any database system. In Cassandra, query performance can be improved by designing data models to suit your read and write patterns, effectively using primary and partition keys, denormalizing data where necessary, and optimizing your cluster configuration. Additionally, Cassandra query optimization involves understanding the Cassandra write path analysis and optimizing the write operations.

The key to optimizing query performance lies in the cardinal rule of Cassandra data modeling: model your data according to your queries. Cassandra’s unique approach to data modeling and query optimization can be leveraged to create a highly efficient, scalable, and performance-optimized database solution.

How to perform time series data modeling?

Time series data modeling is a common use case for Cassandra, given its ability to handle large amounts of data across many commodity servers and its support for wide rows.

Here’s an example of how you might structure your data for a weather application recording temperature data every minute:

CREATE TABLE temperature_data ( location text, date date, time time, temperature decimal, PRIMARY KEY ((location, date), time) ) WITH CLUSTERING ORDER BY (time DESC);

In this data model:

location and date are the partition key. These choices allow the data to be distributed across nodes based on the location and the date.
time is the clustering column, which sorts the temperature data within each partition.

With this model, you can efficiently query for temperature data for a specific location on a specific date, with results sorted by time in descending order.

A few considerations while modeling time series data:

Bucketing: You should usually bucket your time series data to prevent creating very wide rows. In the example above, we’ve bucketed data by day.
Time Interval: Choose the time granularity based on your use case. Here we’re recording data every minute, but it could be every second, every hour, etc., based on the needs of your application.
TTL (Time To Live): Cassandra allows you to set a TTL on data, after which the data will automatically be deleted. This can be very handy for time series data where old data may not be relevant after a certain period.

Leave a Comment