CREATE / ALTER / DROP TABLE and Keyspace Objects with Schema Agreement Awareness

Learning outcomes

AtlasMart is about to add its first application-owned CQL schema. The immediate risk is not remembering the syntax of CREATE TABLE; it is assuming that DDL completion means every node, every driver, and every deployed application version is ready for the new schema. This lesson builds a schema-change mental model from keyspace/table definitions through cluster-wide schema agreement.

1. CQL looks familiar, but its schema encodes a distributed access path

Cassandra Query Language (CQL) deliberately resembles SQL, but the similarity stops well before the execution model. A Cassandra keyspace is the namespace where replication policy is defined. A table defines the partition key and optional clustering columns that determine placement and on-partition order. The primary key is therefore not merely a uniqueness constraint: its partition-key component is part of the routing contract.

In a relational system, adding an index or letting the optimizer choose a join order may rescue a query whose access path was not anticipated. Cassandra expects the common query shapes to be designed into tables. CQL has no implicit joins and no subquery mechanism that reconstructs an arbitrary relational graph at request time. Chapter 05 therefore treats DDL as application architecture, not just catalog administration.

2. Establish the disposable cluster and create an application keyspace

Reuse the isolated topology from Chapters 01–04 when it is still running. Otherwise create it below. The chapter uses its own atlasmart_cql keyspace so DDL experiments cannot alter the earlier replication/token labs.

# Disposable Chapter 05 cluster: three nodes, three racks, one DC, 16 vnodes/node# If these objects already exist from Chapters 01–04, reuse them instead of recreating them.docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait for node 1 to accept CQL, then start the two peers.docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 java -version

CREATE KEYSPACE IF NOT EXISTS atlasmart_cqlWITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3}AND durable_writes = true;CONSISTENCY QUORUM;CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_id (    product_id text PRIMARY KEY,    name text,    category text,    price_cents bigint,    status text,    description text,    updated_at timestamp);CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_category (    category text,    bucket tinyint,    product_id text,    name text,    status text,    price_cents bigint,    PRIMARY KEY ((category, bucket), product_id));CREATE TABLE IF NOT EXISTS atlasmart_cql.inventory_by_product (    product_id text,    location_id text,    quantity int,    status text,    promo_note text,    PRIMARY KEY ((product_id), location_id));

Now inspect what Cassandra actually stored instead of trusting the text you typed. DESCRIBE KEYSPACE is a cqlsh convenience. The system_schema tables are queryable schema metadata maintained by Cassandra.

docker exec atlasmart-cass-1 cqlsh -e "DESCRIBE KEYSPACE atlasmart_cql"docker exec atlasmart-cass-1 cqlsh -e "SELECT keyspace_name, durable_writes, replication FROM system_schema.keyspaces WHERE keyspace_name='atlasmart_cql';"docker exec atlasmart-cass-1 cqlsh -e "SELECT table_name FROM system_schema.tables WHERE keyspace_name='atlasmart_cql';"docker exec atlasmart-cass-1 cqlsh -e "SELECT table_name, column_name, kind, position, type FROM system_schema.columns WHERE keyspace_name='atlasmart_cql' AND table_name='products_by_category';"

The expected evidence is the replication map containing NetworkTopologyStrategy and dc1:3, plus column metadata showing category and bucket as partition-key components and product_id as a clustering column. Exact formatting and option ordering are not an API contract.

3. ALTER is a schema mutation, not a free application migration

Use additive DDL first. A new nullable/non-key column can often be introduced before all application instances start writing it. That does not make every alteration safe: changing primary-key shape requires a new table, and Cassandra intentionally restricts type changes that could invalidate existing encoded data.

ALTER TABLE atlasmart_cql.products_by_idADD IF NOT EXISTS manufacturer text;SELECT column_name, kind, typeFROM system_schema.columnsWHERE keyspace_name='atlasmart_cql'  AND table_name='products_by_id'  AND column_name='manufacturer';

A safe rollout separates at least four events: schema mutation accepted, nodes reach schema agreement, deployed readers tolerate the new shape, and deployed writers start using the field. Reversing that order can break old binaries even when Cassandra itself is perfectly healthy.

4. Observe agreement instead of assuming it

nodetool describecluster reports cluster metadata including schema versions. Run it from more than one member when teaching agreement. Also query system_schema directly on individual nodes: a client normally hides which node answered unless you intentionally control the contact point.

docker exec atlasmart-cass-1 nodetool describeclusterdocker exec atlasmart-cass-2 nodetool describeclusterdocker exec atlasmart-cass-3 nodetool describeclusterfor n in 1 2 3; do  docker exec atlasmart-cass-$n cqlsh -e "SELECT column_name,type FROM system_schema.columns WHERE keyspace_name='atlasmart_cql' AND table_name='products_by_id';"done

On PowerShell, run the three docker exec commands separately instead of relying on the Bash loop. Agreement should converge quickly in a healthy local cluster, but “quickly” is not a correctness guarantee. Deployment automation should poll with a bounded timeout and fail visibly rather than sleep for an arbitrary number of seconds and hope.

5. Deliberately wrong DDL: destructive convenience and false confidence

A common migration mistake is to use DROP TABLE as a development reset while connected to an endpoint whose identity was never verified. Another is to see one successful DESCRIBE and declare the rollout complete. Both errors confuse local client success with system-wide intent.

CREATE TABLE atlasmart_cql.ddl_scratch (  id text PRIMARY KEY,  note text);INSERT INTO atlasmart_cql.ddl_scratch (id,note) VALUES ('only-row','disposable');SELECT * FROM atlasmart_cql.ddl_scratch;DROP TABLE atlasmart_cql.ddl_scratch;-- This should now fail because the table no longer exists:SELECT * FROM atlasmart_cql.ddl_scratch;

The failure after DROP is evidence that the catalog object disappeared; it is not a backup test, nor does it tell you whether filesystem snapshots exist. The safe repair is procedural: verify cluster/keyspace identity first, use migration review, prefer additive changes, make destructive DDL explicitly gated, and maintain tested restore paths.

6. Production judgment and lab acceptance

DDL is low-frequency but high-impact. Treat keyspace/table definitions as versioned application contracts. Record exact Cassandra and driver versions, RF, DC/rack layout, table options, schema versions before/after, and the compatibility window for old/new application binaries. A DDL rollback can be harder than the forward change once writers have emitted new data, so the rollback plan must describe data as well as schema.

Verification checklist

• atlasmart_cql uses NetworkTopologyStrategy with RF=3 in dc1.
• products_by_category shows the intended composite partition key in system_schema.columns.
• All three nodes converge to one schema version after the additive ALTER.
• You can explain why agreement is not application compatibility.
• No destructive command targeted any keyspace outside atlasmart_cql.

Summary and next bridge

You now have a schema object model plus an evidence model: CQL DDL changes distributed metadata, nodes must converge, and application compatibility remains a separate responsibility. Lesson 2 moves from schema to mutations and proves why INSERT is normally an upsert and why Cassandra resolves individual cells by timestamps.

# Chapter-only reset or deliberate preservation# Keep atlasmart_cql if you are continuing directly to the next lesson.# Full reset (deletes only the dedicated course containers/volumes/network)docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-datadocker network rm atlasmart-cassandra

Authoritative references

• Apache Cassandra downloads — Current Cassandra and ASF Java-driver release baseline.
• CQL data definition — Official keyspace/table DDL and schema options.
• CQL data manipulation — Official INSERT/UPDATE/DELETE/SELECT, timestamps, TTL, WRITETIME and filtering semantics.
• cqlsh documentation — Official shell commands, tracing, paging, SOURCE/CAPTURE/COPY behavior and compatibility boundary.
• Native protocol — PREPARE/EXECUTE protocol semantics and version framing.
• Apache Cassandra Java Driver prepared statements — Prepared/bound statement behavior, caching and routing metadata.