The Wide-Column Data Model and Why It Exists

Build 10 dismantled single-leader replication and showed how Dynamo runs leaderless clusters, consistent-hashes keys onto a ring, and tunes N/R/W. That was the distribution layer. What actually sits on each node — what data structure holds the rows, what queries are cheap, what queries are impossible — was deferred. Build 11 opens that box.

The answer is the wide-column model, and it looks nothing like the relational tables you have used since freshman year.

The Google web-crawl problem

In 2004, Google's crawler had indexed a substantial chunk of the public web — roughly 8 billion pages, on its way to hundreds of billions. For each URL, the crawler kept the last crawl timestamp, the full HTML content, the outbound links, the inbound links ("anchors"), extracted metadata, language detection, near-duplicate cluster IDs, per-crawl-version revision history. Some URLs had tens of anchors; others had tens of thousands. Some were crawled every hour; others once a year. The data was enormous, extremely sparse, and wildly uneven across rows.

No relational database in 2004 could hold it. A pages table with columns (url, content, last_crawl, language, anchor_1, ..., anchor_N) was impossible because N was unbounded and mostly empty. Normalising to a separate anchors(url, anchor_url) table forced a seven-way join for every page fetch — a disaster at crawler scale. Denormalising into JSON blobs defeated range scans like "all URLs on wikipedia.org crawled this week".

The engineers who wrote the Bigtable paper (Chang et al., OSDI 2006) described this exactly. They needed three properties that relational databases delivered only in the small: horizontal scale to petabytes, per-row schema flexibility, and locality of access for rows that share a key prefix. Bigtable was the design that came out, and it introduced the wide-column model the rest of this chapter describes.

Why relational falls down here: the relational model trades flexibility for guarantees — fixed schema, joins to recombine normalised entities, transactions across rows. Those guarantees cost money on every write and become expensive to distribute. Petabyte-scale OLTP gives up most of the guarantees anyway — you are not running multi-row ACID across 100 billion web pages. Wide-column accepts the loss of guarantees up front and spends the savings on scale and flexibility.

Relational vs wide-column — a side-by-side

Relational (PostgreSQL, MySQL, Oracle). Tables have a fixed schema declared up front. Every row has the same columns. Data is normalised — each fact stored in exactly one place — and queries use joins to recombine related rows. You model by thinking about entities and their relationships. Queries are ad-hoc: the SQL planner figures out how to combine tables, indexes, and join algorithms.

Wide-column (Cassandra, HBase, Bigtable, DynamoDB). Rows are identified by a compound primary key and hold a sparse map of column-name to value. Two rows in the same table can have completely different sets of columns. Data is denormalised — each query gets its own table, and the same fact may be written to many tables. There are no joins. You model by thinking about access patterns and build one table per query. Ad-hoc queries that were not planned for are slow or impossible.

The Cassandra community motto, due to Patrick McFadin: "in relational, you model your entities; in wide-column, you model your queries." Internalise this sentence. It is the single thing that most trips up engineers coming from Postgres — they try to normalise, and then the joins they expect are not there.

The primary key — partition plus clustering

A wide-column table has a compound primary key with two named parts.

The partition key decides which physical node in the cluster stores the row. Cassandra and DynamoDB pass it through the consistent-hash function (see Consistent Hashing and Virtual Nodes) to find N preference-list nodes and replicate to all N. Every row with the same partition key lives together.

The clustering key decides the order of rows within a partition. Rows are stored physically sorted by clustering key on disk — this is the storage layout, not a query-time sort. Scanning a partition in clustering-key order is a sequential SSTable read, the fastest I/O pattern the system offers.

Put in CQL (Cassandra Query Language) syntax:

CREATE TABLE user_events (
    user_id    text,
    event_time timestamp,
    event_type text,
    payload    text,
    PRIMARY KEY ((user_id), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

The inner parentheses around user_id declare it as the partition key. Everything after — here, event_time — is clustering keys. CLUSTERING ORDER BY (event_time DESC) stores rows newest-first within each partition, so "last 100 events for a user" reads the first 100 rows sequentially, no sort.

Why this two-level key matters: the partition key is a sharding and availability decision — one partition lives on one set of N nodes and is the unit of read-your-writes, failure isolation, and single-partition transactions. The clustering key is a layout decision — it determines whether the scans you care about are sequential or random on disk. Get the partition key wrong and queries hit every node; get the clustering key wrong and scans become random I/O. Both choices happen at table-creation time and are nearly impossible to change without rewriting the table.

The sparse-map row

Inside a row, the data is a dictionary: {column_name: value}. This is the single design choice that most distinguishes wide-column from relational.

Columns are not declared in a global schema. Cassandra lets you declare them — CREATE TABLE user_events (...) lists the known ones — but dynamic column families accept arbitrary names at runtime via map<text, text> types. DynamoDB is more relaxed still: declare the partition and sort keys, and every other attribute is write-time dynamic. HBase treats the schema as advisory.

Two rows can have different columns. Row A might have 5 columns: {event_type: "click", target: "#btn", session: "s1", device: "mobile", ts: "..."}. Row B might have 50 columns including gift_note and 47 A/B-test flags that A does not. The database does not care. Missing columns consume no storage — sparse on disk, sparse in RAM.

Columns can be added on the fly. Need a referer_source column? Start writing it on new rows. Old rows do not have it; if you want them to, you migrate explicitly. No ALTER TABLE lock, no rewrite of 100 million rows.

This is why wide-column is often mis-described as "schemaless". It is not — partition and clustering keys are schema, and most production tables declare their main columns. What is flexible is the long tail of sparse columns: A/B flags, experiment buckets, per-event metadata, profile attributes that differ by region or tier. Relational requires ALTER TABLE for each; wide-column lets you add them at write time.

Access patterns — wide-column is not a relational substitute

Wide-column scales because it imposes a narrow set of fast access patterns and makes everything outside that set slow or impossible.

Point get by full primary key. SELECT * FROM user_events WHERE user_id = 'priya' AND event_time = '2026-04-24T11:05:00Z'. Coordinator hashes the partition key, routes to N replicas, reads R, merges. O(1) logical cost.

Range scan within a partition. SELECT * FROM user_events WHERE user_id = 'priya' AND event_time > '2026-04-24T00:00:00Z' LIMIT 100. Same routing — one partition, one set of replicas. The scan is a sequential SSTable read. Bread-and-butter for timelines, feeds, per-entity history.

Cross-partition query without a secondary index. SELECT * FROM user_events WHERE event_type = 'purchase' hits every node and scans every partition. Production Cassandra and DynamoDB reject or heavily warn on such queries. Either build a separate table with event_type as the partition key, or run the query through an external batch tool (Spark, Hive, EMR).

Joins. Not supported. No JOIN operator in CQL. Either look up related rows on the application side (round-trips per row), or denormalise by storing the join result inside the row.

Aggregates. COUNT(*), SUM(amount) are supported only within a single partition. Cluster-wide aggregates go through external tools (Spark on Cassandra, Athena on DynamoDB).

Secondary indexes exist but fan out to every node and are catastrophic for high-cardinality columns. Production advice is almost always: build a second table instead.

Why access patterns are king: the wide-column storage layout — sorted SSTables per column-family, compaction, bloom filters, memtables — is optimised for exactly two operations: point get by (partition, clustering) and range scan by clustering inside a partition. Queries that match are fast at any cluster size; queries that do not are slow, period. You design the schema to keep queries inside the sweet spot.

Denormalisation is the default

In relational, you normalise to avoid redundancy; each fact lives in one table and joins recombine. In wide-column, you denormalise to match queries; each query gets its own table and the same fact may live in five tables. Storage is multiplied; query-time cost is flat.

Messaging example. A relational schema: conversations, participants, messages — three tables, normalised; "show my conversations with last messages and participants" is a three-way join.

The wide-column translation splits by access pattern:

-- Query: "show messages in conversation X, ordered by time"
messages_by_conversation (
    conversation_id text,
    message_time    timestamp,
    sender_id       text,
    text            text,
    PRIMARY KEY ((conversation_id), message_time)
);

-- Query: "show all conversations this user participates in"
conversations_by_user (
    user_id         text,
    conversation_id text,
    last_activity   timestamp,
    title           text,
    other_members   set<text>,
    PRIMARY KEY ((user_id), last_activity, conversation_id)
) WITH CLUSTERING ORDER BY (last_activity DESC);

-- Query: "show messages this user sent across all conversations"
messages_by_user (
    user_id         text,
    message_time    timestamp,
    conversation_id text,
    text            text,
    PRIMARY KEY ((user_id), message_time)
) WITH CLUSTERING ORDER BY (message_time DESC);

Three tables, each for one query, all denormalised. Sending a message writes to messages_by_conversation, updates conversations_by_user for every participant, and appends to messages_by_user. One logical send is three or four writes. The reward: each of the three queries runs in one partition scan on one node, regardless of system scale.

Cassandra's batch API groups related writes together — not as an ACID transaction, but as a logged group the coordinator retries until every target succeeds. Write amplification is the tax you pay for flat read latency.

Schema flexibility in practice

Adding a column. In Cassandra, ALTER TABLE user_events ADD column_name text is metadata-only — schema version bump, gossip, done in seconds. Existing rows do not acquire the column; new writes may include it. In DynamoDB, no ALTER at all — just start writing the attribute. Compare to PostgreSQL, where adding a non-null column with a default on a billion-row table can take hours and lock out writes.

Removing a column. Stop writing it; old rows still have it; a background scan with DELETE column_name FROM ... or a TTL cleans up. No blocking operation.

Changing a column type. Not supported directly. Dual-write the new column, backfill, drop the old. The application manages the migration — more work than ALTER TABLE ... TYPE ..., but never blocks writes.

Per-row variation. Premium users can have a premium_expiry column; free users do not. Row size grows only for users who have the column.

Column families — Bigtable and HBase

Bigtable introduced one grouping above columns: the column family. Columns are grouped into families declared at table-creation time ("metadata", "contents", "anchors"), and each family is stored in a separate SSTable on disk.

Why separate storage per family? Access patterns for different columns of the same row are often wildly different. The contents family in a web-crawl table — full HTML of each page — is huge and rarely read alongside metadata (timestamp, HTTP status). Storing them together forces every metadata read to drag multi-megabyte HTML through I/O. Storing them separately means metadata reads touch only the metadata SSTable.

Families also enable per-family configuration: different compression, different TTL, in-memory versus on-disk. HBase inherited this directly. Cassandra's "column family" terminology is related but works differently — what Cassandra calls a column family is closer to a table. DynamoDB simplified further: no families, just attributes, all stored together.

Python sketch — a toy wide-column storage

Strip the model down to a data structure and the access primitives become unambiguous:

from sortedcontainers import SortedDict

class WideColumnStore:
    """A single-node wide-column store. No replication, no SSTables,
    no compaction — just the logical model."""

    def __init__(self):
        # partition_key -> SortedDict of (clustering_key -> {column: value})
        self.partitions = {}

    def put(self, pk, ck, columns):
        if pk not in self.partitions:
            self.partitions[pk] = SortedDict()
        # Merge semantics: new columns overwrite, absent columns persist.
        row = self.partitions[pk].setdefault(ck, {})
        row.update(columns)

    def get(self, pk, ck):
        return self.partitions[pk][ck]

    def range(self, pk, ck_start, ck_end, limit=None):
        partition = self.partitions.get(pk, {})
        out = []
        for ck in partition.irange(ck_start, ck_end):
            out.append((ck, partition[ck]))
            if limit is not None and len(out) >= limit:
                break
        return out

    def delete_column(self, pk, ck, column):
        self.partitions[pk][ck].pop(column, None)

Every operation does what the model says: put upserts and merges columns, get returns the column map for one (partition, clustering) pair, range scans a clustering-key interval within one partition, delete_column drops a single cell.

Why SortedDict and not dict: the clustering-key order is the whole point. Range scans must be O(log n + results). In real Cassandra this is the memtable — an in-RAM sorted map that buffers writes until flush to on-disk SSTable. The SSTable is also sorted by clustering key, so the range-scan primitive survives the RAM-to-disk transition.

What the sketch skips: replication across nodes, SSTable compaction, bloom filters, per-column-family separation, cell timestamps for conflict resolution. Those are engineering on top of the logical model.

When wide-column is the right choice

• Time-series and telemetry. IoT devices, server metrics, application events, sensor readings. Partition by device, cluster by timestamp. Writes append to the end of each partition; reads are "last N hours for device X" — one partition, one scan.
• Message feeds and activity streams. Partition by conversation or user, cluster by time. Cassandra's original use at Facebook was inbox search.
• User profiles with many sparse attributes. Partition by user, store hundreds of attributes with per-user variation.
• Entity-plus-history records. Orders with item lines, accounts with transactions. Partition by entity, cluster history by time.
• Petabyte-scale OLTP with simple access patterns. Netflix stores hundreds of petabytes of user-viewing metadata on Cassandra; Apple runs iCloud backends on it; Discord stores trillions of chat messages.

Rule of thumb: if your dominant query is "give me the latest N items for a specific entity", wide-column is the right tool. If it is "items matching ad-hoc filters", it is not.

When wide-column is the wrong choice

• Ad-hoc queries. A BI dashboard that filters on arbitrary combinations. Every access path needs a table; you cannot pre-build all combinations.
• Cross-entity relational queries. "Orders joined with customers joined with products" — relational shines, wide-column forces denormalising every join up front.
• Strong cross-row consistency. Bank ledgers with multi-row invariants, inventory where two buys must not both succeed on the last unit. Cassandra's lightweight transactions (LWT) do Paxos-based compare-and-set on a single partition at 4 round-trips; DynamoDB added multi-item transactions in 2018 capped at 100 items. Neither scales across partitions cheaply.
• Small data. A 10 GB database that fits on one machine. Postgres wins — cluster operational cost is not worth the scale you do not need.

CREATE TABLE tweets_by_user (
    author_id  text,
    tweet_time timestamp,
    tweet_id   text,
    text       text,
    media_url  text,
    PRIMARY KEY ((author_id), tweet_time, tweet_id)
) WITH CLUSTERING ORDER BY (tweet_time DESC);

CREATE TABLE home_timeline (
    user_id      text,
    tweet_time   timestamp,
    author_id    text,
    tweet_id     text,
    text_snippet text,
    PRIMARY KEY ((user_id), tweet_time, tweet_id)
) WITH CLUSTERING ORDER BY (tweet_time DESC);

Common confusions

• "Wide-column means NoSQL." Too broad. NoSQL covers key-value, document, graph, search, and wide-column — four distinct designs. Wide-column is specifically the Bigtable lineage: partition keys, clustering keys, sparse columns, column families.
• "Wide-column is schemaless." Misleading. Partition and clustering keys are declared schema, and most production tables declare their main columns. What is flexible is the sparse long tail.
• "Cassandra is Bigtable." Cassandra took Bigtable's data model and married it to Dynamo's distribution model. HBase is the direct Bigtable clone; Cassandra is the hybrid.
• "You cannot do transactions." You can, within limits. Cassandra's LWT uses Paxos for single-partition compare-and-set at 4 round-trips; DynamoDB added multi-item transactions in 2018 capped at 100 items. Available, but not the default.
• "Denormalisation wastes storage so it is bad." For OLTP at petabyte scale, disk is cheap and latency is precious. Duplicating a tweet into 5000 timelines costs a few megabytes; the alternative is seconds of join latency. Storage has fallen 100× since 2004; latency budgets have not.
• "Any column can be queried efficiently." Only columns in the primary key. Non-key columns are returned but not filterable without a full-partition scan or a separate table.

Going deeper

The Bigtable paper

Chang, Dean, Ghemawat et al. published Bigtable: A Distributed Storage System for Structured Data at OSDI 2006 after years of production use at Google. The paper describes the row-key-plus-column-family model, the SSTable on-disk format, the Chubby lock service for metadata, the three-level tablet location hierarchy, and performance measurements at petabyte scale. HBase, Cassandra, ScyllaDB, Hypertable, Accumulo, and DynamoDB all owe design debts to it. Bigtable itself runs on GFS (SOSP 2003); MapReduce (OSDI 2004) consumes it for offline analysis.

HBase, Cassandra, ScyllaDB

HBase (2007) was an open-source Bigtable reimplementation for the Hadoop ecosystem, running on HDFS instead of GFS. It preserves the data model closely — column families, regions, coprocessors.

Cassandra (Facebook 2008) merged Bigtable's data model with Dynamo's distribution. Architecturally it is closer to Dynamo — gossip membership, leaderless quorum, no master — but the SSTable format and column-family organisation are Bigtable-derived.

ScyllaDB (2015) is a C++ reimplementation of Cassandra, wire-compatible at the CQL protocol level but using a thread-per-core shared-nothing architecture (Seastar). It delivers 5-10× higher per-node throughput on the same hardware.

DynamoDB's management

Amazon's DynamoDB service is a managed database whose name contains "Dynamo" but whose architecture has drifted. Vogels et al. published Amazon DynamoDB at USENIX ATC 2022, describing auto-sharding to partitions (10 GB units), auto-adaptive capacity, per-partition Paxos-based strong consistency, and CRDT-based global tables. Recognisably wide-column on the data model, but structurally closer to sharded range-partitioning than to Dynamo-the-paper.

Where this leads next

This chapter introduced the model. Build 11 drills into the pieces:

• Chapter 86 — partition keys and clustering keys in depth: composite partition keys, hot-partition pathology, how to choose both without painting yourself into a corner.
• Chapter 87 — the SSTable on-disk format: how a partition is physically laid out, how multiple SSTables per partition are merged at read time, how bloom filters accelerate point gets.
• Chapters 88-90 — writes (memtable, commit log, SSTable flush), reads (bloom filters, partition cache, merge), compaction strategies.
• Chapters 91-93 — time-series patterns, partitioning best practices, query modelling (the Chebotko diagram approach).
• Chapter 94 — Cassandra's lightweight transactions and why you almost always want to avoid them.

By the end of Build 11 you will design a production wide-column schema for a realistic workload — a ride-hail event stream, a messaging service, an IoT pipeline — and reason about scaling bottlenecks before deployment.

References

1. Chang, Dean, Ghemawat, Hsieh, Wallach, Burrows, Chandra, Fikes, and Gruber, Bigtable: A Distributed Storage System for Structured Data, OSDI 2006 — the founding paper of the wide-column model. Describes the row-key-plus-column-family design, the SSTable format, Chubby-based metadata, and production deployments at Google including the web-crawl use case that motivated the original design.
2. DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store, SOSP 2007 — the leaderless-replication paper that Cassandra combined with Bigtable's data model. Relevant here for the distribution half of Cassandra's design.
3. Apache Software Foundation, Apache Cassandra Architecture Documentation — the canonical reference for Cassandra's architecture, data model, partition and clustering keys, and CQL. Especially useful as a cross-check on the data-modelling vocabulary this chapter uses.
4. Amazon Web Services, Amazon DynamoDB Developer Guide — the managed DynamoDB reference, with coverage of the table/item/attribute model, partition and sort keys, global secondary indexes, and transaction support added in 2018.
5. Kleppmann, Designing Data-Intensive Applications, Chapter 3 — Storage and Retrieval, O'Reilly 2017 — the clearest pedagogical treatment of LSM-trees, SSTables, and the Bigtable data model, with worked comparisons to B-tree-based relational storage engines.
6. Allen, Bouchier, Boyd, et al., Scaling Cassandra beyond a Thousand-Node Cluster, Netflix Technology Blog 2019 — a production account of running Cassandra at hundreds-of-petabytes scale at Netflix, with concrete discussion of partition design, hot-partition pathology, and denormalisation strategies learned the hard way.