Master NoSQL Data Modeling for Scale

A mental-model and design-pattern reference for distributed wide-column and key-value stores, specifically Apache Cassandra, ScyllaDB, and Amazon DynamoDB, built on the core inversion that these systems require modeling queries first, not entities. It contrasts SQL and distributed NoSQL directly: SQL models entities and relationships with CPU-intensive joins at read time and minimizes duplication under ACID consistency and vertical scaling, while distributed NoSQL pre-computes joins at write time, treats storage as cheap, offers tunable BASE or eventual consistency, and scales horizontally across nodes. Five core design patterns: query-first modeling (list entities, list every access pattern, then design tables specifically to serve each pattern with a single lookup); the partition key as the central design decision, where a low-cardinality key like status="active" creates hot partitions that cap throughput to one node while high-cardinality keys such as user IDs or composite keys spread traffic evenly; clustering keys in Cassandra or sort keys in DynamoDB that pre-sort data within a partition for efficient range queries; single-table design or adjacency lists, primarily a DynamoDB technique, that store multiple entity types under one partition key so a single query such as PK="USER#123" fetches a user's profile and all their orders in one network request; and denormalization or duplication across purpose-built tables (users_by_id, users_by_email) accepting the trade-off of managing consistency across copies. Cassandra-specific guidance covers the partition-key-plus-clustering-columns primary key structure, avoiding joins and aggregates by pre-calculating into a counter table instead, treating any ALLOW FILTERING in production as a sign the data model is wrong since it forces a full cluster scan, cheap LSM-tree appends for writes, and the cost of tombstones from high-velocity deletes like queue patterns. DynamoDB-specific guidance covers GSIs (alternative data views, eventually consistent) versus LSIs (different sort order within the same partition, must be created at table-creation time), WCU and RCU capacity planning, and TTL attributes for free, tombstone-free automatic expiry. An expert checklist gates schema finalization on access-pattern coverage, partition-key cardinality, split-partition risk such as sharding a partition once it exceeds roughly 10GB, and whether the application can tolerate eventual consistency for a given read. Three named anti-patterns close it out: scatter-gather scans across all partitions, hot keys from poor time-bucketing, and relational modeling that tries to join separate tables in application code instead of embedding or duplicating data.

Use when designing for scale beyond a single-node database, evaluating or implementing Cassandra, ScyllaDB, or DynamoDB, tuning performance or troubleshooting hot partitions and high latency in an existing NoSQL system, or implementing database-per-service microservices patterns needing highly optimized reads. Not for SQL or relational modeling or document stores like MongoDB - the query-first, denormalize-at-write mental model here is specifically for wide-column and key-value distributed stores, and applying it to a system with different consistency and join semantics will produce the wrong schema.

Input is the full list of entities and every access pattern the application needs to serve. Output is a table or single-table design with a chosen partition key and clustering or sort key per access pattern, denormalized or duplicated data where needed, and an explicit consistency and partition-sizing plan checked against the expert checklist.

Named systems are Apache Cassandra, ScyllaDB, and Amazon DynamoDB, with DynamoDB-specific mechanisms (GSI, LSI, WCU/RCU, TTL) and Cassandra-specific mechanisms (LSM-tree writes, tombstones, ALLOW FILTERING) treated separately where their behavior diverges.

Backend engineers and data architects designing or troubleshooting a Cassandra, ScyllaDB, or DynamoDB schema who need the query-first mental model and concrete anti-patterns - hot partitions, scatter-gather, relational modeling in NoSQL - rather than relearning distributed-database theory from scratch.