Last Updated: 3/19/2026

Materialized Views

Materialized views in Feldera are incrementally maintained query results that can be queried and output to external systems. Unlike regular views, materialized views store their results and update them efficiently as input data changes.

Creating Materialized Views

Use CREATE MATERIALIZED VIEW to define a materialized view:


CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    user_id,
    COUNT(*) as event_count,
    MAX(event_time) as last_event
FROM events
GROUP BY user_id;

Materialized vs. Non-Materialized Views

Materialized views:

Results are stored and incrementally updated
Can be queried with ad-hoc SQL
Can be output to external systems via connectors
Consume memory or storage

Non-materialized views (regular views):

Results are not stored
Cannot be queried directly
Cannot be output to connectors
Used as intermediate computations


-- Regular view (not materialized)
CREATE VIEW filtered_events AS
SELECT * FROM events WHERE event_type = 'click';
 
-- Materialized view (can be queried and output)
CREATE MATERIALIZED VIEW click_stats AS
SELECT COUNT(*) as total_clicks FROM filtered_events;

Querying Materialized Views

Query materialized views using ad-hoc SQL:


for row in pipeline.query("SELECT * FROM user_stats ORDER BY event_count DESC LIMIT 10"):
    print(row)

Only materialized views can be queried. Attempting to query a non-materialized view will fail.

Outputting Materialized Views

Connect materialized views to output connectors:


outputs:
  stats:
    stream: user_stats
    connector_config:
      transport:
        name: kafka_output
        config:
          bootstrap.servers: "localhost:9092"
          topic: "user-stats"
      format:
        name: json

Incremental Maintenance

Feldera updates materialized views incrementally. When input data changes, only the affected parts of the view are recomputed:


-- Initial state
INSERT INTO events VALUES (1, 'user1', 'click', TIMESTAMP '2024-01-01 10:00:00');
-- user_stats: user1 has 1 event
 
-- Add another event
INSERT INTO events VALUES (2, 'user1', 'view', TIMESTAMP '2024-01-01 10:05:00');
-- user_stats: user1 now has 2 events (incremental update)
 
-- Delete an event
DELETE FROM events WHERE id = 1;
-- user_stats: user1 back to 1 event (incremental update)

Complex Queries

Materialized views support the full SQL syntax:

Joins


CREATE MATERIALIZED VIEW user_orders AS
SELECT 
    u.name,
    u.email,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name, u.email;

Window Functions


CREATE MATERIALIZED VIEW user_rankings AS
SELECT 
    user_id,
    score,
    RANK() OVER (ORDER BY score DESC) as rank
FROM user_scores;

Subqueries


CREATE MATERIALIZED VIEW high_value_users AS
SELECT 
    u.id,
    u.name,
    (SELECT SUM(amount) FROM orders WHERE user_id = u.id) as total_spent
FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 10;

Nested Views

Build complex computations by nesting views:


-- Base view (not materialized)
CREATE VIEW active_users AS
SELECT * FROM users WHERE last_login > NOW() - INTERVAL 30 DAY;
 
-- Intermediate view (not materialized)
CREATE VIEW user_activity AS
SELECT 
    u.id,
    u.name,
    COUNT(e.id) as event_count
FROM active_users u
LEFT JOIN events e ON u.id = e.user_id
GROUP BY u.id, u.name;
 
-- Final materialized view
CREATE MATERIALIZED VIEW top_active_users AS
SELECT * FROM user_activity
ORDER BY event_count DESC
LIMIT 100;

Memory Considerations

Materialized views consume memory or storage. For large views:

Enable storage to spill to disk:


{
  "storage": {
    "backend": {"name": "default"}
  }
}

Filter data to reduce view size:


CREATE MATERIALIZED VIEW recent_events AS
SELECT * FROM events
WHERE event_time > NOW() - INTERVAL 7 DAY;

Aggregate data to reduce cardinality:


CREATE MATERIALIZED VIEW hourly_stats AS
SELECT 
    DATE_TRUNC('hour', event_time) as hour,
    COUNT(*) as count
FROM events
GROUP BY hour;

Indexes on Materialized Views

Create indexes on materialized views for efficient lookups:


CREATE INDEX idx_user_stats_user ON user_stats(user_id);
 
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id;

Indexes are used by:

Ad-hoc queries with WHERE clauses
Output connectors that produce key-value pairs

What’s Next

Ad Hoc Queries: Learn how to query materialized views
Recursive Queries: Write recursive SQL queries
Pipeline Configuration: Configure memory and storage settings