Practice: Data modelling across stores
Generated by Claude through GigAI (karthik446/gigai-learning-center 4c0cb514) on 2026-10-13.
Main tool: Apache Cassandra · Apache Cassandra runs on a laptop in a container and rejects queries that the primary key does not serve, so it makes you design tables from the access patterns and feel the write-side cost of every denormalised copy. · Alternatives: PostgreSQL or MySQL model entities first and normalise with joins; Amazon DynamoDB is a managed key-value store with a hash key and a sort key per item; MongoDB nests related data inside documents; Snowflake and BigQuery hold facts and dimensions for analytics, often built with dbt; Debezium streams changes out of a source store to keep copies in step.
Every step links into the tool's own documentation; links were checked on 2026-10-13 · version 1
Setup (one time)
1. Start a single-node Apache Cassandra in a local container (Docker or Podman is the prerequisite) and open a cqlsh session against it.
Every later step is typed as CQL into this shell, so you need one running node and a working prompt first. One node is enough until the last step.
Explained in: The Cassandra Query Language (CQL) | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: The cqlsh prompt appears and a query on the system keyspaces returns rows.
2. Read the CQL conventions and statement basics so you can follow the grammar notation used in the reference pages.
The reference pages describe each statement with a compact grammar. Knowing how to read it saves time on every later step.
Explained in: Definitions | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: You can explain what the optional and repeated parts of a statement grammar mean.
3. Create a practice keyspace with SimpleStrategy and a replication factor of one, then switch to it with USE.
A keyspace is the container for all tables and sets replication. SimpleStrategy is the simplest choice for a single laptop node.
Explained in: Data Definition | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: DESCRIBE KEYSPACES lists the practice keyspace and the prompt shows it as the current keyspace.
Steps
1. Write the list of queries a hotel booking application must answer, in plain words, before drawing any table.
Query-first modelling starts from access patterns, not entities. This list drives every table you create in the following steps.
Explained in: Introduction | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: You have a written list of at least five queries, each naming what it filters by and how the results are ordered.
2. Compare an entity-first, normalised sketch of the same hotel domain with the query-first one, and note where joins are replaced by duplicated columns.
Seeing both shapes side by side shows what a relational design assumes that Cassandra does not offer: joins and referential integrity.
Explained in: Introduction | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: You have a two-column note that lists, for each query, the joins in the relational sketch and the single table that answers it in the query-first sketch.
3. Create one table per query with a partition key that groups the rows read together and clustering columns that set their order inside the partition.
The partition key decides which node holds the data and the clustering columns decide the sort order on disk. Together they are the core of wide-column modelling.
Explained in: Data Definition | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: DESCRIBE TABLE shows each table with its partition key and clustering order, and rows selected from one partition come back sorted by the clustering column.
4. Create a single-table key-value design as you would for DynamoDB (a paid cloud service, so use Cassandra as the local substitute): one partition key as the hash key, one clustering column as the sort key, and static columns for attributes shared by the whole item collection.
DynamoDB key design follows the same hash key plus sort key idea, so practising it here teaches item collections and overloaded keys without a cloud account.
Explained in: Data Definition | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: One partition holds several item types distinguished by the sort key, and a static column shows the same value on every row of that partition.
5. Query each table through its partition key, then run a query that filters on a non-key column and read the error before trying it with ALLOW FILTERING.
The WHERE clause rules are how Cassandra enforces query-first design. The rejected query shows exactly which access pattern your model does not serve.
Explained in: Data Manipulation | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: The key-based query returns rows, the non-key query is rejected with a filtering warning, and you can say why forcing it would scan the whole table.
6. Create a storage-attached index on the non-key column and run the previously rejected query again, then compare it with building a second denormalised table for the same query.
An index is the alternative to another copy of the data. Comparing the two makes the trade-off between write cost and read flexibility concrete.
Explained in: Working with Storage-attached indexing (SAI) | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: The query on the non-key column returns rows without ALLOW FILTERING, and you have noted when you would choose the index and when the extra table.
7. Read how an indexed write goes through the memtable, the flush and compaction, so you know what each extra index costs on the write path.
Every read-side shape is paid for at write time. This section shows where that cost lands on disk and when compaction rebuilds index data.
Explained in: Storage-attached indexing (SAI) concepts | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: You can describe in three sentences what happens to index data at write, at flush and at compaction.
8. Write the same booking into two denormalised tables with a logged BATCH, then repeat it as two separate inserts and note what a failure between them would leave behind.
Denormalised copies drift apart unless the writes are grouped. A batch is the basic tool for keeping copies consistent, and it has a cost you should know.
Explained in: Data Manipulation | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: Both tables return the same booking after the batch, and you can explain which copy would be stale if the second separate insert never ran.
9. Create a materialized view that re-keys one base table for a second query (enable materialized views in the node configuration first if the statement is refused), update the base row and query the view.
A materialized view lets the server maintain a denormalised copy for you. Its limitations explain why many teams still prefer application-managed copies.
Explained in: Materialized Views | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: A change to a base row appears in the view under its new primary key, and you can name two limitations of views from the same page.
10. Create a fact-style table of nightly bookings partitioned by hotel and a day bucket, with dimension attributes copied onto each row, using the datetime functions to derive the bucket.
Dimensional modelling separates facts from dimensions. In Cassandra the time bucket becomes part of the partition key, so the window of data read is bounded.
Explained in: Functions | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: Rows for different days land in different partitions and a query for one hotel and one day returns only that day's bookings.
11. Run count, sum and average over one day bucket of the fact table and compare the result with what a warehouse star schema would compute across all days.
Aggregates in Cassandra work well inside one partition and poorly across many. That limit is the reason analytics usually moves to a warehouse.
Explained in: Functions | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: The aggregate over one partition returns the expected totals, and you have noted which analytical questions would need a warehouse instead.
12. Reproduce the whole model by saving every statement in one CQL file, dropping the keyspace and replaying the file with the source command in cqlsh.
A schema that exists only in shell history cannot be reviewed or run in CI. A single replayable file is the pinned, repeatable form of your model.
Explained in: Definitions | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: After a drop and a replay, DESCRIBE KEYSPACE shows the same tables, index and view as before.
13. Configure a production-like setup: start a three-node local cluster in containers and recreate the keyspace with NetworkTopologyStrategy and a replication factor of three for the datacenter.
Production keyspaces name their datacenters and replicate each partition to several nodes. This is where partition key choices start to affect load and availability.
Explained in: Data Definition | Apache Cassandra Documentation (cassandra.apache.org), checked 2026-10-13
Done when: The node status shows three nodes up, the keyspace description shows NetworkTopologyStrategy with three replicas, and a read still succeeds with one node stopped.