Key Concepts

​

Overview

​

Sources

​

Mental Model

• Tables — they’re structured components you can read from
• Clients — they’re responsible for reading data from external systems

​

Source Components

​

Supported Connectors

​

Creating a Source

CREATE SECRET pgpass AS 'your_password';

CREATE CONNECTION pg_connection TO POSTGRES (
  HOST 'postgres.example.com',
  PORT 5432,
  USER 'materialize',
  PASSWORD SECRET pgpass,
  DATABASE 'myapp'
);

CREATE SOURCE pg_source
  FROM POSTGRES CONNECTION pg_connection
  (PUBLICATION 'mz_source')
  FOR ALL TABLES;

CREATE SECRET mysql_password AS 'your_password';

CREATE CONNECTION mysql_connection TO MYSQL (
  HOST 'mysql.example.com',
  PORT 3306,
  USER 'materialize',
  PASSWORD SECRET mysql_password
);

CREATE SOURCE mysql_source
  FROM MYSQL CONNECTION mysql_connection
  FOR ALL TABLES;

CREATE CONNECTION kafka_connection TO KAFKA (
  BROKER 'kafka.example.com:9092',
  SECURITY PROTOCOL SASL_SSL,
  SASL MECHANISMS 'PLAIN',
  SASL USERNAME 'user',
  SASL PASSWORD SECRET kafka_password
);

CREATE SOURCE kafka_source
  FROM KAFKA CONNECTION kafka_connection
  (TOPIC 'orders')
  FORMAT JSON;

CREATE SOURCE webhook_source
  IN CLUSTER ingestion_cluster
  FROM WEBHOOK
  BODY FORMAT JSON;

-- Get the webhook URL
SHOW CREATE SOURCE webhook_source;

​

Subsources

-- This creates multiple subsources
CREATE SOURCE pg_source
  FROM POSTGRES CONNECTION pg_connection
  FOR ALL TABLES;

-- View all created subsources
SHOW SOURCES;

• Write-only by the source (only the source can write to them)
• Readable by users (you can query them like tables)

​

Sources and Clusters

​

Views

​

How Views Work

CREATE VIEW revenue_by_region AS
SELECT 
  region,
  SUM(order_total) AS total_revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY region;

• The query is not executed during view creation
• Results are recomputed from scratch each time you query the view
• Views are not associated with a cluster and can be referenced anywhere

​

Indexed Views

CREATE INDEX idx_revenue ON revenue_by_region (region);

• The query is executed and results are stored in memory
• As new data arrives, results are incrementally updated
• Queries read from memory — making them fast and computationally free

​

Materialized Views

​

Creating Materialized Views

CREATE MATERIALIZED VIEW top_customers AS
SELECT 
  customer_id,
  customer_name,
  SUM(order_total) AS lifetime_value,
  COUNT(*) AS order_count
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id, customer_name
ORDER BY lifetime_value DESC;

• Query is executed during creation
• Results are stored in durable storage (not just memory)
• Results are incrementally updated as data changes
• Can be queried from any cluster (cross-cluster access)

​

Hydration

• The view is first created
• Its cluster is restarted

​

Indexes on Materialized Views

CREATE INDEX idx_top_customers ON top_customers (customer_id);

• Require no additional computation to stay current
• Simply make results available in memory for faster access
• Are useful in serving clusters that need low-latency access

​

Indexes

​

How Indexes Work

1. The underlying query is executed
2. Results are stored in memory within the cluster
3. As new data arrives, results are incrementally updated
4. Queries read from memory — making them instant and free

​

Creating Indexes

CREATE VIEW order_summary AS
SELECT 
  order_id,
  order_date,
  customer_id,
  SUM(quantity * price) AS total
FROM order_items
GROUP BY order_id, order_date, customer_id;

-- Create index in current cluster
CREATE INDEX idx_orders ON order_summary (customer_id);

-- Or specify cluster explicitly
CREATE INDEX idx_orders 
  IN CLUSTER serving_cluster 
  ON order_summary (customer_id);

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT 
  DATE(order_time) AS date,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE(order_time);

-- Index for serving cluster
CREATE INDEX idx_daily_revenue 
  IN CLUSTER serving_cluster
  ON daily_revenue (date);

-- Less common, but possible
CREATE INDEX idx_raw_orders 
  ON orders_source (customer_id);

​

How Materialize Uses Indexes

CREATE INDEX idx_orders ON orders_view (customer_id);

​

Point Lookups

• The WHERE clause uses equality (= or IN) on all indexed columns
• Only AND is used to combine conditions (no OR)
• The condition uses the exact expression type from the index

SELECT * FROM orders_view 
WHERE customer_id = 123;

​

Multi-Column Indexes

CREATE INDEX idx_orders_region ON orders_view (region, status);

-- Point lookup (uses index efficiently)
SELECT * FROM orders_view 
WHERE region = 'US' AND status = 'shipped';

-- Full scan (missing status column)
SELECT * FROM orders_view 
WHERE region = 'US';

​

Indexes and Query Optimization

• Sequential access — faster than scanning unindexed data
• Random access — fast point lookups for specific keys
• Join operations — delta joins use indexes for efficiency
• Ad-hoc queries — even queries not optimized for the index benefit

​

Clusters

​

What Requires a Cluster?

• Maintaining sources and sinks
• Maintaining indexes and materialized views
• Executing SELECT and SUBSCRIBE statements

​

Resource Isolation

• Each cluster has dedicated CPU, memory, and disk
• Workloads on different clusters can’t affect each other
• Clusters can fail independently

-- Create clusters for different workloads
CREATE CLUSTER ingestion SIZE '100cc';
CREATE CLUSTER transformation SIZE '200cc';
CREATE CLUSTER serving SIZE '50cc', REPLICATION FACTOR 2;

​

Cluster Sizing

• Resource allocation (CPU, memory, disk)
• Processing speed
• Cost

ALTER CLUSTER serving SET (SIZE = '100cc');

​

Replication Factor

CREATE CLUSTER serving 
  SIZE '100cc',
  REPLICATION FACTOR 2;  -- Creates 2 replicas

• Each replica does the same work on the same data
• Provides high availability if one replica fails
• Increases cost proportionally (2 replicas = 2× cost)

​

Three-Tier Architecture (Production Best Practice)

CREATE CLUSTER ingestion SIZE '100cc';

CREATE SOURCE pg_source
  IN CLUSTER ingestion
  FROM POSTGRES CONNECTION pg_conn
  FOR ALL TABLES;

CREATE CLUSTER transformation SIZE '200cc';

CREATE MATERIALIZED VIEW revenue_rollup
  IN CLUSTER transformation
AS
  SELECT region, DATE(order_time) AS date, SUM(total)
  FROM orders
  GROUP BY region, DATE(order_time);

CREATE CLUSTER serving 
  SIZE '100cc',
  REPLICATION FACTOR 2;

-- Index the materialized view in serving cluster
CREATE INDEX idx_revenue
  IN CLUSTER serving
  ON revenue_rollup (region, date);

• Isolation — ingestion issues don’t affect query serving
• Scalability — size each tier independently
• Availability — replicate serving clusters for high availability

​

Sinks

​

What Can You Sink?

• Materialized views
• Sources
• Tables

​

Supported Sink Types

CREATE SINK kafka_sink
  FROM revenue_by_region
  INTO KAFKA CONNECTION kafka_conn
  (TOPIC 'revenue-updates')
  FORMAT JSON
  ENVELOPE DEBEZIUM;

-- Not technically a sink, but serves the same purpose
SUBSCRIBE TO (
  SELECT * FROM revenue_by_region
);

​

Sinks and Clusters

​

Hydration Considerations

​

Putting It All Together

-- 1. Create clusters for different workloads
CREATE CLUSTER ingestion SIZE '100cc';
CREATE CLUSTER compute SIZE '200cc';
CREATE CLUSTER serving SIZE '100cc', REPLICATION FACTOR 2;

-- 2. Create a source in the ingestion cluster
CREATE SOURCE orders_source
  IN CLUSTER ingestion
  FROM POSTGRES CONNECTION pg_conn
  (PUBLICATION 'mz_source')
  FOR TABLES (orders, customers);

-- 3. Create a materialized view in the compute cluster
CREATE MATERIALIZED VIEW customer_lifetime_value
  IN CLUSTER compute
AS
SELECT 
  c.customer_id,
  c.name,
  c.region,
  COUNT(o.order_id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM customers c
LEFT JOIN orders o USING (customer_id)
GROUP BY c.customer_id, c.name, c.region;

-- 4. Create an index in the serving cluster
CREATE INDEX idx_clv
  IN CLUSTER serving
  ON customer_lifetime_value (region, customer_id);

-- 5. Query from the serving cluster (fast!)
SET CLUSTER = serving;
SELECT * FROM customer_lifetime_value 
WHERE region = 'US'
ORDER BY lifetime_value DESC
LIMIT 10;

-- 6. Create a sink to push results to Kafka
CREATE SINK kafka_sink
  IN CLUSTER compute
  FROM customer_lifetime_value
  INTO KAFKA CONNECTION kafka_conn
  (TOPIC 'customer-metrics')
  FORMAT JSON
  ENVELOPE DEBEZIUM;

​

Decision Guide: Views, Materialized Views, and Indexes

​

Best Practices Summary

​

Next Steps

​

Additional Resources

• Official Documentation
• Transform Data Guide
• Ingest Data Guide
• Community Slack