CQL Fundamentals: CREATE, INSERT, SELECT, UPDATE, DELETE

Get comfortable with the basic syntax of the Cassandra Query Language for everyday CRUD operations.

CQL looks like SQL on purpose, so that developers can start quickly. The syntax is familiar; several of the semantics are not.

CQL looks and feels like ordering from a menu you recognize (SQL-like syntax), but the kitchen behind it (the storage engine) works completely differently — you can only efficiently ask for dishes by their exact table location (partition key), not browse the whole restaurant.

Key Concepts

Knowing exactly where the resemblance stops is the interview question.

INSERT and UPDATE are the same operation. Both are upserts. Cassandra does not check whether a row exists before writing:

    INSERT INTO users (id, email) VALUES (7, 'a@x.com');
    UPDATE users SET email = 'a@x.com' WHERE id = 7;

Identical effect. An INSERT on an existing row overwrites the columns it mentions and leaves the rest alone; an UPDATE on a missing row creates it. There is no "already exists" error, because finding out would mean a read before every write.

INSERTUPDATE

SELECT can only filter on the primary key. With PRIMARY KEY ((customer_id), order_date):

PRIMARY KEY ((customer_id), order_date)

    WHERE customer_id = 7                          ok, one partition
    WHERE customer_id = 7 AND order_date > '...'   ok, a slice of that partition
    WHERE order_date > '...'                       rejected
    WHERE status = 'PAID'                          rejected

The rejections are not arbitrary. Without the partition key, Cassandra has no way to know which node holds the answer, so it would have to ask every node in the cluster.

ALLOW FILTERING lets you do it anyway, and you should not. It tells Cassandra to contact every node and read every partition. It works instantly on the twenty rows in your test cluster and takes the production cluster down at ten million.

ALLOW FILTERING

Treat it as a signal that you need a different table, not a fix.

DELETE writes data rather than removing it. Because SSTables are immutable, a delete appends a tombstone — a marker saying "this is gone as of this timestamp". The real bytes disappear later, during compaction.

Deleting in Cassandra is a write, and heavy deleting has a cost that arrives later.

The extras worth knowing by name.USING TTL 86400 — the row expires by itself after a day, becoming a tombstone. Sessions and caches use this constantly.

USING TTL 86400

IF NOT EXISTS and IF status = 'OPEN' — lightweight transactions. These genuinely do check first, using a consensus round between replicas, and cost roughly four times a normal write. Use them where you must and nowhere else.

IF NOT EXISTSIF status = 'OPEN'

BEGIN BATCH — groups statements. Not a relational transaction: it gives atomicity, not isolation, and across partitions it can be slower than separate writes.

BEGIN BATCH

No joins, no subqueries, no aggregates worth relying on. GROUP BY and COUNT(*) exist and scan, which on a large table is exactly the operation the design is meant to avoid. Data that must be aggregated is aggregated on write, or in Spark, not in a CQL query.

GROUP BYCOUNT(*)

What the interviewer is probing.1. "How does an INSERT differ from a relational one?" Probing: the semantics. Stalls: "It is the same." Moves up: it is an upsert — there is no duplicate key error, it simply overwrites.

2. "Why does SELECT sometimes require ALLOW FILTERING?" Probing: the warning. Stalls: "A permission." Moves up: the query cannot be answered from the partition key, so it would scan the cluster — it is a sign the data model does not match the query.

3. "Is there a difference between an update and an insert?" Probing: the model. Stalls: "Yes." Moves up: no — both write a new version with a timestamp; last write wins on read.

4. "What does a DELETE actually do?" Probing: the mechanism. Stalls: "Removes the row." Moves up: writes a tombstone, which is removed only after compaction and the grace period.