Cassandra Migration Best Practices
Migrating to Cassandra — whether from a relational database, another NoSQL system, or a legacy Cassandra version — requires careful planning. The data model, write patterns, and query design all need to align with Cassandra's architecture. Rushing migration without this alignment leads to poor performance, hot partitions, and expensive rework later.
Migration Scenarios
Scenario Key Challenge ────────────────────────────────────────────────────────────── Relational DB → Cassandra Schema redesign (query-first) MySQL / PostgreSQL → Cassandra Remove joins; denormalize MongoDB → Cassandra Rethink document structure Old Cassandra → New Cassandra Schema changes; version upgrade Adding Cassandra alongside SQL DB Dual-write transition pattern
Phase 1: Model Before You Migrate
Never start a Cassandra migration by converting existing tables one-for-one. Instead, follow the query-first modeling approach: list all application queries, then design Cassandra tables to answer each query with a single partition read.
Relational model (DO NOT copy directly):
users(id, name, email)
orders(id, user_id, date, total)
products(id, name, price)
order_items(order_id, product_id, qty, unit_price)
Cassandra model (designed from queries):
Q: Get all orders for user X (newest first)
→ orders_by_user PRIMARY KEY (user_id, order_date DESC)
Q: Get order detail by order_id
→ orders_by_id PRIMARY KEY (order_id)
Q: Get all items in an order
→ order_items PRIMARY KEY (order_id, product_id)
Phase 2: Build and Validate the Schema
-- Validate that every query is served by a table: -- For each query in the application: -- Can it be answered by partition key + clustering columns? -- Does the table avoid ALLOW FILTERING on production queries? -- Is the partition key high-cardinality enough? -- Are partition sizes bounded (< 100 MB)?
Phase 3: Load Historical Data
Use bulk loading tools rather than INSERT statements for large data sets. Bulk loaders write SSTables directly to disk, bypassing the Cassandra write path and achieving 10–100× higher throughput than CQL INSERTs.
Option A: sstableloader
# Generate SSTables from a CSV using CQL: cqlsh -f load_products.cql # Stream pre-built SSTables into the cluster: sstableloader -d 10.0.0.1 /path/to/sstables/ecommerce/products/
Option B: DSBulk (DataStax Bulk Loader)
# Load CSV into a Cassandra table at high speed: dsbulk load \ --contact-point 10.0.0.1 \ --keyspace ecommerce \ --table products \ --url /data/products.csv \ --header true \ --schema.mapping "product_id, name, price, category"
Option C: Spark for Large-Scale ETL
# Read from source database, transform, write to Cassandra:
source_df = spark.read.jdbc(
url="jdbc:postgresql://old-db:5432/ecommerce",
table="orders",
properties={"user": "admin", "password": "secret"}
)
transformed_df = source_df.select(
col("user_id").alias("customer_id"),
col("created_at").alias("order_date"),
col("id").alias("order_id"),
col("status"),
col("total")
)
transformed_df.write \
.format("org.apache.spark.sql.cassandra") \
.options(table="orders_by_customer", keyspace="ecommerce") \
.mode("append") \
.save()
Phase 4: Dual-Write Transition Pattern
Running the old system and Cassandra in parallel lets you validate correctness before cutting over completely. The application writes to both systems and reads from the old system until confidence is high, then gradually shifts reads to Cassandra.
Stage 1: Dual-write
Application → Old DB (primary reads + writes)
→ Cassandra (writes only)
Stage 2: Shadow reads
Application → Old DB (primary reads + writes)
→ Cassandra (writes + shadow reads for comparison)
Compare results; fix discrepancies.
Stage 3: Cassandra primary reads
Application → Old DB (writes only for rollback safety)
→ Cassandra (primary reads + writes)
Stage 4: Full cutover
Application → Cassandra only
Old DB → archived or decommissioned
Phase 5: Schema Version Control
Track all CQL DDL changes in version-controlled migration scripts, just as you would with SQL migrations. Tools like Liquibase (with a Cassandra extension) or custom shell scripts can apply migrations in order.
# Migration script naming convention: V001__create_keyspace.cql V002__create_products_table.cql V003__add_products_by_category_table.cql V004__alter_orders_add_shipped_at.cql # Apply with cqlsh: cqlsh -f V004__alter_orders_add_shipped_at.cql
Phase 6: Schema Changes on a Live Cluster
Cassandra handles many schema changes without downtime, but some require care.
Schema Change Safe? Notes ────────────────────────────────────────────────────────────── Add a column Yes Existing rows return null for it Drop a column Yes Data removed at next compaction Add a table Yes No impact on existing tables Drop a table Yes Permanent; cannot undo Change PK column type No Not supported in Cassandra Rename PK column No Not supported Change non-PK column type Limited Only compatible type widening Add a secondary index Yes Builds in background Drop an index Yes No downtime Change replication factor Yes Follow with nodetool repair
Common Migration Mistakes
Mistake Fix ────────────────────────────────────────────────────────────── Copying relational tables 1-to-1 Redesign using query-first method Using sequential IDs as PKs Use UUID to avoid hotspots Running analytics queries directly Add a dedicated analytics DC on the production cluster or use Spark Using ALLOW FILTERING in production Redesign the table for the query Loading data with CQL INSERT loops Use DSBulk, sstableloader, or Spark Not testing restore from backups Always verify backup recoverability Skipping performance testing Load test with realistic data volumes with real data volumes before going to production
Migration Validation Checklist
✓ All production queries answered without ALLOW FILTERING ✓ No partition exceeds 100 MB or 100,000 rows ✓ Partition keys have high cardinality ✓ Row counts match between source and Cassandra ✓ p99 read and write latency meets SLAs under load ✓ Repair runs successfully across the cluster ✓ Backup and restore tested end-to-end ✓ Monitoring dashboards show green on all key metrics ✓ Runbook documented for rollback if cutover fails
Summary
A successful Cassandra migration starts with query-first data modeling before any data moves. Load historical data with bulk tools like DSBulk or Spark ETL rather than CQL INSERTs. Use the dual-write transition pattern to validate correctness before cutting over. Version-control all schema changes as migration scripts. Test partition sizes, query latency, and restore procedures before the production cutover. The most common failure in Cassandra migrations is applying a relational data model to a non-relational database — always redesign the schema around your access patterns first.