Cassandra

Overview

Apache Cassandra is a distributed, wide-column database designed to stay available and to scale writes across many machines. Every node in a cluster has the same role, so there is no single master and no single point of failure. You add nodes to add capacity, and the data spreads out on its own.

It is built for large, write-heavy workloads such as telemetry, sensor streams, messaging, and activity logs. In return for that scale, it asks you to design tables around your queries, and it gives up general joins and ad hoc analytics. This page explains how Cassandra works, how it compares with its neighbors, and how it relates to tabular foundation models, which learn from raw tables. Cassandra often holds the raw, high-volume rows that later become training or scoring tables. For the model side, see /model/research.

What it is

Cassandra is an open-source database that stores data in tables with rows and columns, queried with CQL, the Cassandra Query Language, which looks like a small subset of SQL. Under the surface, it is a partitioned row store: rows are grouped into partitions, partitions are spread over nodes with consistent hashing, and each partition is kept sorted by clustering columns.

Main traits:

• Peer-to-peer architecture. All nodes are equal, and any node can take any request.
• Linear scale-out. Doubling nodes roughly doubles capacity.
• Tunable consistency. You choose per query how many replicas must answer.
• Multi-datacenter replication. Data can live in several regions with local reads and writes.
• Fast writes. Writes append to a log and memory first, so they are cheap.
• Query-first modeling. You build a table for each query shape.
• Time-to-live on data. Cells can expire automatically.
• No joins, limited aggregation, and restricted filtering by design.

Everyday analogy

Imagine a large city post office with many sorting stations. A letter's address decides which station keeps it. Any clerk who receives a letter can look at the address and pass it to the right station, and each letter is copied to two more stations in case one burns down. There is no head office that everything must go through. That is Cassandra: stations are nodes, the address is the partition key, and the copies are replicas.

Where it came from

Cassandra was created at a large social network company around 2008 to power an inbox search feature, and was then open sourced and later became a top-level project of the Apache Software Foundation. Its design combined two earlier ideas from research and industry papers: the data model and storage design of Google's Bigtable, and the distribution and availability ideas of Amazon's Dynamo. Those two papers, from the mid 2000s, are widely cited as its roots.

Since then it has gained a query language, secondary indexes, materialized views, lightweight transactions, better compaction, and many operational improvements. Large companies in streaming, retail, finance, and telecommunications run big Cassandra clusters. Managed services and compatible databases also exist. Always check the current release notes for exact feature status.

How it works

Step 1: Data is split by partition key

Each table has a primary key made of a partition key and optional clustering columns. The partition key is hashed to a token, and the token decides which nodes own the row. All rows with the same partition key sit together, so a query that names the partition key goes straight to the right nodes.

CREATE TABLE sensor_readings (
    device_id   text,
    day         date,
    ts          timestamp,
    temperature double,
    humidity    double,
    PRIMARY KEY ((device_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Here (device_id, day) is the partition key and ts is the clustering column. All readings of one device on one day live in one partition, sorted newest first.

Step 2: The token ring

Think of a clock face. The range of possible hash values is wrapped into a circle, and each node owns one or more arcs. A row's token falls on the circle, and the owner is the node at that spot. Virtual nodes give each machine many small arcs, so load is spread smoothly and adding a node moves only a little data.

Step 3: Replication

A replication factor, for example 3, says how many nodes keep a copy of each row. The next nodes around the ring, or nodes in distinct racks and datacenters according to the strategy, hold the copies. If one node dies, others still have the data.

Step 4: The write path

A write goes to a coordinator node, which forwards it to the replicas. Each replica does two cheap things:

1. Append the write to a commit log on disk for durability.
2. Insert it into an in-memory table called a memtable.

When a memtable is full, it is flushed to disk as an immutable sorted file called an SSTable. Writes never update files in place. This is why writes are so fast.

Step 5: The read path

A read may need to look in the memtable and in several SSTables, then merge the results by timestamp. Bloom filters tell the node quickly which files cannot contain a key, and partition indexes find the spot inside the files. Caches help with hot data.

Step 6: Compaction

Over time there are many SSTables. A background process called compaction merges them, drops overwritten values and expired data, and keeps reads fast. Different compaction strategies suit different patterns, for example time-windowed compaction for time series data.

Step 7: Tombstones

A delete does not remove data right away. It writes a marker called a tombstone, which hides older values until compaction can safely drop both. Many tombstones in a partition slow reads, so avoid patterns that delete or overwrite huge numbers of cells.

Step 8: Tunable consistency

For each query you pick a consistency level. With a replication factor of 3, a level of ONE needs one replica to answer, QUORUM needs two, and ALL needs all three. A common rule is that if read replicas plus write replicas is greater than the replication factor, reads see the latest writes. This lets you trade strict consistency for speed and availability per query.

Step 9: Repair and gossip

Nodes tell each other about cluster state with a gossip protocol. Hinted handoff stores writes for a node that is briefly down, and repair processes compare replicas and fix differences. Regular repair is part of healthy operation.

Key concepts and terms

A worked example

We will model device telemetry, write to it, read from it, and produce a table for a tabular model.

Design from the queries

Cassandra modeling starts with questions, not entities. Suppose the application needs:

1. Show the latest readings of one device for a given day.
2. Show all devices that reported in a given hour.

These are two queries, so we create two tables. The first is the one defined above. The second is:

CREATE TABLE devices_by_hour (
    hour_bucket timestamp,
    device_id   text,
    last_ts     timestamp,
    PRIMARY KEY (hour_bucket, device_id)
);

The application writes to both tables when a reading arrives. This duplication is normal in Cassandra and is the price of fast reads.

Write and read

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement
import datetime

cluster = Cluster(["example.internal"])
session = cluster.connect("telemetry")

insert = session.prepare(
    "INSERT INTO sensor_readings (device_id, day, ts, temperature, humidity) "
    "VALUES (?, ?, ?, ?, ?) USING TTL 2592000"
)
now = datetime.datetime.utcnow()
session.execute(insert, ("dev-1", now.date(), now, 21.4, 0.55))

q = SimpleStatement(
    "SELECT ts, temperature FROM sensor_readings "
    "WHERE device_id = %s AND day = %s LIMIT 100",
    consistency_level=ConsistencyLevel.LOCAL_QUORUM,
)
for row in session.execute(q, ("dev-1", now.date())):
    print(row.ts, row.temperature)

The TTL of 2,592,000 seconds is 30 days, so readings expire by themselves. The query names the full partition key, so it hits only the right nodes.

Bounding the partition

If a device reports every second, one day holds 86,400 rows, which is fine. If you used only device_id as the partition key, the partition would grow forever. Adding the day to the key bounds its size. This is called bucketing and it is one of the most important habits in Cassandra design.

Producing a table for a model

Cassandra is poor at scanning and aggregating everything. For a tabular model, you usually export or stream the raw rows to an analytics tool, then aggregate there. A Spark job or a connector reads the table in token ranges, computes features, and writes a flat table.

import pandas as pd

rows = session.execute(
    "SELECT device_id, day, ts, temperature, humidity "
    "FROM sensor_readings WHERE device_id = 'dev-1' AND day = '2026-03-04'"
)
df = pd.DataFrame(list(rows))
features = df.agg({"temperature": ["mean", "min", "max", "std"],
                   "humidity": ["mean", "std"]})
print(features)

For many devices, run this at scale in a batch tool, not by looping in a script. The goal is one row per device per day with summary columns, which a tabular model can read directly.

What to check in the result

1. Did you read a complete partition, or did a limit cut it off?
2. Are there gaps in time where devices went silent? Gaps are informative missingness.
3. Are units consistent across devices and firmware versions?
4. Are there duplicate readings from retries? Writes in Cassandra are idempotent by key, so a retry with the same key overwrites, but a retry with a new timestamp key may duplicate.
5. Are there impossible values, such as a temperature of -999 used as an error code?

Estimating partition size

A quick calculation helps you avoid oversized partitions.

• Suppose a device writes one reading every 10 seconds. That is 8,640 rows per day.
• Suppose each row takes about 100 bytes with overhead. One day-partition is then under 1 MB.
• If you used only device_id as the key, one year would hold about 3.15 million rows, or roughly 300 MB in one partition.
• Large partitions slow reads, repairs, and compaction, so the day bucket is a good choice here.

As a loose guide, keep partitions in the range of tens of megabytes and well under a few hundred thousand rows when you can. The right number depends on hardware and workload, so test it.

Consistency by example

Take a replication factor of 3, so each row has three copies.

This is the heart of tunable consistency. You pick the row that matches the cost of stale data versus the cost of failed requests.

Handling updates and last-write-wins

Each cell carries a write timestamp. When two replicas disagree, the cell with the newest timestamp wins. This means that clocks must be kept in sync across clients and nodes, and that concurrent updates to the same cell are resolved by time, not by merging. If you need to count or append safely, model it as separate rows, not as a read-modify-write on one cell.

Strengths and limitations

Strengths

• High availability with no single point of failure.
• Very fast writes and good write scale-out.
• Predictable low-latency reads when you query by partition key.
• Multi-datacenter replication built in.
• Tunable consistency per request.
• Automatic data expiry with TTL.
• Proven at very large scale.
• Good fit for time series and event data when modeled with buckets.

Limitations

• No joins, and limited filtering, grouping, and aggregation.
• Queries must follow the table design. New query shapes often need new tables.
• Data duplication across tables is normal, and the application must keep copies in step.
• Large partitions hurt performance, as do many tombstones.
• Eventual consistency is the default model, so applications must be designed for it.
• Operations need skill: repair, compaction tuning, capacity planning.
• Secondary indexes and materialized views have caveats and are best used with care.
• It is not meant for ad hoc analytics or full table scans.

How it compares with alternatives

A frequent pattern is to use Cassandra for the live, high-volume layer and an analytical store for history and reporting. A stream of changes, handled by a tool such as Kafka Connect, keeps the two in step.

Role in tabular foundation models

A tabular foundation model learns from tables: rows are records, columns are typed features, and the model must cope with mixed types, missing values, columns whose names carry little meaning, and relations that differ per dataset. Cassandra is not where a model runs. It is where very large, fast-moving raw tables often originate. Its role is to hold and serve that raw data, and to constrain how it can be read. See /model/architecture for how a model reads a table.

Row and column structure

A Cassandra table already looks like a table, with typed columns, but its rows are organized by partition. A tabular model wants a set of rows with the same columns, often one row per entity. Because Cassandra is optimized for reading one partition at a time, a clean way to build a model table is to read partition by partition, summarize each, and emit one row per partition.

Mixed types

CQL has rich types: integers, decimals, text, timestamps, UUIDs, collections such as lists, sets, and maps, and user-defined types. A model usually needs numbers and categories, so collections must be flattened into counts or encoded features, and UUIDs should be treated as identifiers, not as features. Timestamps become derived columns such as hour of day or time since last event.

Missing values

Cassandra distinguishes a missing cell from a deleted one, and sparse rows are common because the model is "wide". When a column was never written, you see a null on read. In telemetry, nulls can mean a sensor was off, a message was lost, or a field did not exist in an older firmware. A model sees all of these as missing. Add indicator columns for informative gaps, and avoid filling with zero, which looks like a real reading.

No column names

Wide tables sometimes use short or coded column names to save space. After export, these become the only labels a model or a person sees. A tabular model that does not rely on names can still learn from the values, but humans need a dictionary. Keep column comments or a catalog that maps each code to its unit and meaning.

Pre-training data

Models that learn from many tables often use synthetic tables for pre-training, plus real ones where allowed. Cassandra clusters hold real event streams that can be sampled for evaluation sets after anonymization. Sampling by token range gives a fair spread over the data without a full scan, and a fixed range makes the sample repeatable.

In-context learning

In in-context learning, a model reads labeled rows in its prompt and predicts for new rows, without gradient updates. Cassandra can serve the context quickly when the context is defined by a key, for example the latest labeled events for one device or customer. This fits per-entity prediction: fetch recent rows by partition key, form a small table, and give it to the model together with the new row. For choosing context by similarity, pair it with a vector or search tool.

Continual learning

Streams never stop, and models need to be refreshed or checked for drift. Time-bucketed partitions make it easy to read exactly one day or one hour of data. A scheduled job can read the newest buckets, compute feature statistics, and compare them with a reference window. TTL rules also remind you that old raw data disappears, so keep derived features or exports for any history you may need to retrain on.

Imputation

Gaps in sensor data are common. Imputation can be as simple as carrying the last value forward within a limit, or as advanced as a model that predicts the missing cell from its neighbors in the row and in time. Do the work in the analytics layer, and store the results in a separate table with a flag for imputed cells. Do not overwrite raw readings, since the original gap pattern is itself information.

Anomaly detection

Telemetry is a classic field for anomaly detection: spikes, flat lines, stuck sensors, sudden level shifts. Cassandra can serve the raw stream, and an external process can score it. Scores can be written back to a table keyed by device and time, so an alerting service can read them with a single partition query. Keep windows short and partitions bounded so reads stay fast.

Sector and semantic classification

Deciding what a column means, for example temperature versus pressure, or which industry a device belongs to, is a semantic task. For Cassandra data, useful evidence includes the table and column names, the units, the value ranges, and the time pattern. Profiles per column, such as min, max, spread, and update frequency, give a classifier compact signals without moving all raw data. For benchmark context on how models are judged, see /model/benchmarks.

Practical guidance and best practices

Data modeling

• Write the list of queries first, then design one table per query.
• Choose a partition key that spreads data evenly and that every query supplies.
• Keep partitions bounded in size, for example by adding a time bucket.
• Use clustering columns to sort rows in the order the query needs.
• Avoid unbounded collections inside a single cell.
• Denormalize on purpose, and keep the write path responsible for copies.
• Use prepared statements for speed and safety.

Consistency and availability

• Use LOCAL_QUORUM for both reads and writes as a common balanced default in multi-datacenter setups.
• Use a replication factor of 3 in each datacenter for production data.
• Lower the consistency level only where stale reads are acceptable.
• Avoid lightweight transactions on hot paths, since they cost several round trips.

Operations

• Run repair on a regular schedule.
• Choose a compaction strategy that matches the workload.
• Monitor pending compactions, tombstone counts, partition sizes, and latencies.
• Add nodes before disks are nearly full.
• Keep an eye on garbage collection and heap settings.
• Back up with snapshots and test restores.
• Secure the cluster with authentication, authorization, and encrypted traffic.

Getting data out for models

• Do not run full scans through the application path. Use a batch engine or a connector that reads in token ranges.
• Export to columnar files with explicit types.
• Aggregate per entity and time window, so each row is a meaningful unit.
• Mark gaps explicitly instead of dropping them silently.
• Remove or hash identifiers before data leaves the secure zone.
• Record the query, the time range, and the consistency level used for each export, for reproducibility.

A short modeling checklist

1. List each query with its filters and sort order.
2. For each, define the partition key and clustering order.
3. Estimate partition size in rows and bytes.
4. Add a bucket to the key if it can grow without bound.
5. Decide TTL for each table.
6. Decide which consistency level each query needs.
7. Plan how copies in different tables are written.
8. Load test with realistic sizes.

Capacity planning notes

• Plan for storage to be used at well under full capacity, since compaction needs temporary space.
• Count the replication factor in your storage estimate. Three copies means three times the raw size.
• Estimate write rate per node and keep headroom for repair and node failures.
• Use local solid state drives for the data directory when latency matters.
• Spread nodes across racks or zones so a single failure does not remove two replicas.

Testing before launch

1. Create the real tables on a small cluster.
2. Load realistic volumes with the expected key distribution.
3. Run the real queries at the expected rate and record latency percentiles.
4. Kill a node during the test and confirm that requests still succeed at your chosen consistency level.
5. Run repair and compaction under load and watch for slowdowns.
6. Check that TTL rules remove data as expected.
7. Record the results as a baseline for later comparison.

Feeding analytics and models safely

• Prefer reading from a dedicated analytics datacenter or replica group, so batch scans do not hurt live traffic.
• Use a low consistency level for exports only if small inconsistencies are acceptable for the model use.
• Export incrementally by time bucket, and record the last bucket processed.
• Keep a copy of the exported feature tables, since raw rows expire with TTL.
• Compare feature distributions between exports to catch changes in firmware or units early.

Example: a second look at the model table

Suppose the daily summary job produces this table for a tabular model, one row per device per day.

Notice what each column adds.

• n_readings and gap_minutes tell the model how complete the day was, so that a missing mean is not mistaken for a normal value.
• firmware is a categorical column that may explain differences in scale or units.
• The all-null row for dev-3 is kept, not dropped, because a silent device is itself an event.
• device_id is an identifier. Keep it for joins and for grouping splits, but think twice before feeding it as a feature, since it can let a model memorize individual devices.

Splitting data for evaluation

Random row splits are risky for telemetry, because neighboring days of the same device look alike. Two safer options exist.

1. Split by time: train on earlier days, test on later days.
2. Split by device: hold out whole devices to test how well the model works on new ones.

Pick the one that matches how the model will be used. A model that scores new devices needs the second test. A model that forecasts for known devices needs the first.

Naming and documentation habits

• Use clear, lowercase names with units, such as temperature_c.
• Add a comment to each table that states its query purpose.
• Keep the schema files in version control.

Observability for the pipeline itself

• Count rows read, rows emitted, and rows dropped at each stage.
• Log the time range and the partition count for each export.
• Alert when a day produces far fewer rows than the trailing average.
• Keep a small sample of raw rows with each export for spot checks.

Common mistakes

• Designing tables like a relational schema and expecting joins.
• Using a low-cardinality partition key, creating hot partitions.
• Letting partitions grow without bound.
• Running queries with ALLOW FILTERING in production.
• Using secondary indexes on high-cardinality or very low-cardinality columns without testing.
• Deleting or overwriting many cells and then suffering from tombstones.
• Ignoring repair until inconsistencies pile up.
• Treating the database as a queue, which creates many deletes.
• Reading the whole table to train a model, which can disturb live traffic.
• Filling missing telemetry with zeros before modeling.
• Mixing time zones in timestamp columns.

Frequently asked questions

What is Cassandra used for?

Cassandra is used for write-heavy, always-on workloads at large scale, such as telemetry, time series, messaging, activity feeds, and personalization data. It is chosen when downtime is costly and when data must be replicated across data centers. It works best when you can name the queries up front.

What does wide-column mean?

It means a table can have a very large number of columns and rows are grouped into partitions where each row can hold different cells. In practice, it is a partitioned row store with sorted rows inside each partition. It is not the same as a column-oriented analytics database, which stores each column in its own file.

How is Cassandra different from a column database like ClickHouse?

Cassandra is built for fast writes and key-based reads with high availability, while a column-oriented analytics database is built for scanning and aggregating many rows. They often work together: the first serves the live application and the second answers analytical questions. See ClickHouse for that side.

Does Cassandra support SQL?

It supports CQL, which looks like SQL but is more limited. You can create tables, insert, update, delete, and select with conditions on keys. You cannot do general joins, and aggregation and filtering are restricted to keep queries fast and predictable.

What is tunable consistency?

It means you decide per request how many replicas must confirm a read or write. A low level is faster and more available but may return stale data. A higher level gives stronger guarantees at the cost of latency. A quorum level is a common middle ground.

Why do I need to design a table per query?

Because Cassandra can only read efficiently by partition key, each query shape needs a table whose key matches it. Duplicating data across such tables gives fast reads without joins. The cost is extra writes and storage, which Cassandra handles well.

How do I get Cassandra data into a tabular model?

Read it with a batch tool or connector in token ranges, aggregate per entity and time window, handle missing values explicitly, and write a typed flat table. Then give that table to your model or training pipeline. Avoid scanning live tables through the same path that serves applications.

Can a tabular foundation model work on time series from Cassandra?

Yes, once the stream is turned into rows with meaningful columns, such as window statistics, counts, and lags. Many tabular models read such tables in context without retraining. See /model/research and /model/architecture for how they treat rows, columns, and missing values.

What are tombstones and why do they matter?

A tombstone is a marker that records a delete or an expired value. Reads must skip over tombstones, so partitions with many of them become slow. Good designs avoid patterns that create huge numbers of deletes, and use TTL and time-window compaction for expiring data.

Is Cassandra a good choice for caching?

Usually not. For caching and sub-millisecond lookups, an in-memory store such as Redis is a better fit. Cassandra is better when data must be durable, large, and replicated across regions.

Glossary

• Bucketing: Adding a time or hash piece to the partition key to bound partition size.
• Clustering column: A key column that orders rows within a partition.
• Commit log: A durable append-only log of writes.
• Compaction: Merging SSTables and dropping obsolete data.
• Consistency level: The number of replicas that must answer a request.
• CQL: The Cassandra Query Language.
• Gossip: The protocol by which nodes exchange state.
• Hinted handoff: Temporary storage of writes for an unavailable node.
• Keyspace: A namespace holding tables and replication settings.
• Memtable: The in-memory write buffer.
• Partition key: The key part that decides which nodes store a row.
• Quorum: A majority of replicas.
• Repair: A process that makes replicas agree.
• Replication factor: The number of copies of each row.
• SSTable: An immutable sorted data file.
• Token: The hash value that places a row on the ring.
• Tombstone: A delete marker.
• TTL: A time limit after which data expires.
• Virtual node: One of many small token ranges owned by a node.

Summary

Cassandra is a peer-to-peer, wide-column database that scales writes by adding nodes and stays available when machines fail. It spreads data by partition key, replicates it, writes fast through a commit log and memtables, and reads by merging sorted files. In exchange, you model data around queries and accept limits on joins and analytics.

For tabular foundation models, Cassandra is a source of very large raw tables, especially telemetry and event data. The practical work is to export in token ranges, aggregate into one row per entity and time window, handle gaps and mixed types explicitly, keep imputed values separate, and slice by time for continual learning and anomaly checks. With that preparation, models that read rows and columns in context can work on top of data that Cassandra collects reliably at scale. For more on those models, see /model/research, /model/architecture, and /model/benchmarks.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: