DbSchema | Cassandra - How to Create a Materialized View?

Table of Contents

1. Introduction
2. When to use a materialized view
3. Prerequisites
4. Materialized view restrictions
5. Create a materialized view in cqlsh
6. Create a materialized view in DbSchema
7. Operational cautions
8. Conclusion
9. References

Introduction

A materialized view in Cassandra creates an additional read path over an existing base table. It lets you query the same data with a different primary key, while Cassandra keeps the view synchronized with writes to the base table.

That sounds convenient, but materialized views have strict CQL rules and real operational cost. Use them when the alternative query path is well understood, not as a general substitute for deliberate table design.

When to use a materialized view

Materialized views can be useful when:

• you already have a base table with the right source columns
• you need one additional lookup pattern
• the view definition fits Cassandra's materialized view restrictions
• your team accepts the write overhead and asynchronous propagation behavior

In many applications, creating another application-managed table is still the clearer and more predictable option.

Prerequisites

Before creating a view, make sure you have:

• a base table already defined
• the columns required by the view primary key available in the base table
• cqlsh access or DbSchema connected to Cassandra
• a clear reason for adding a new query path

Materialized view restrictions

Cassandra materialized views are more restricted than normal tables. Important rules include:

• the SELECT can reference only base-table columns
• the view primary key must include all base-table primary-key columns
• the view primary key can add only one extra non-primary-key column
• every column used in the view primary key must be constrained with IS NOT NULL
• ORDER BY, LIMIT, functions, aliases, and ALLOW FILTERING are not allowed
• static columns cannot be included in the view
• if the base table has static columns, SELECT * is not allowed

These rules are why materialized views work best for narrow, well-defined alternate access patterns.

Create a materialized view in cqlsh

Suppose the base table is:

CREATE TABLE IF NOT EXISTS ecommerce.orders (
    order_id uuid PRIMARY KEY,
    customer_id uuid,
    created_at timestamp,
    status text,
    total decimal
);

Now create a materialized view that supports lookups by customer_id:

CREATE MATERIALIZED VIEW IF NOT EXISTS ecommerce.orders_by_customer AS
    SELECT order_id, customer_id, created_at, status, total
    FROM ecommerce.orders
    WHERE order_id IS NOT NULL
      AND customer_id IS NOT NULL
    PRIMARY KEY (customer_id, order_id);

This definition is valid because:

• order_id is the base-table primary key and is included in the view primary key
• customer_id is the one extra non-primary-key column
• both key columns are restricted with IS NOT NULL
• all selected columns come from the base table

After creation, verify the result:

DESCRIBE MATERIALIZED VIEW ecommerce.orders_by_customer;

Create a materialized view in DbSchema

DbSchema can help you document the relationship between the base table and the view before deployment.

1. Connect DbSchema to the Cassandra database.
2. Open the base table in the model.
3. Define the materialized view and choose the alternate primary key.
4. Review the generated CQL carefully to make sure it follows Cassandra's restrictions.
5. Deploy the change only after confirming the base-table and view-key columns are correct.

The visual model is especially useful here because it makes the alternate access path explicit for the rest of the team.

Operational cautions

Keep these caveats in mind:

• the initial build scans the base table and can be expensive on large datasets
• view updates are propagated asynchronously, so reads can briefly lag behind base-table writes
• lower consistency levels such as ONE make anomalies more likely than stronger levels such as LOCAL_QUORUM
• every write to the base table now also has view-maintenance cost

Because of those tradeoffs, materialized views should be justified, measured, and monitored rather than added casually.

Conclusion

Creating a Cassandra materialized view is straightforward once the rules are understood, but the feature is intentionally constrained. Make sure the alternate query path is worth the added write cost and operational complexity, and validate the CQL carefully before deploying it.

If you need to change a view later, remember that ALTER MATERIALIZED VIEW is limited. For that, see How to Alter a Materialized View in Cassandra.

References

• Apache Cassandra CQL DDL documentation
• Apache Cassandra materialized views
• DataStax materialized view concepts
• DbSchema Cassandra page