Materialized Views

Materialized views provide precomputed, automatically refreshed query results stored as Elasticsearch indices. They are ideal for:

• Denormalizing joins — flatten data from multiple indices into a single queryable index
• Precomputing aggregations — store GROUP BY results for fast dashboard queries
• Enriching data — combine lookup data with transactional data
• Computed columns — add scripted fields to the materialized result

Unlike regular views, materialized views persist their results and refresh automatically at a configurable interval.

---

Architecture

Under the hood, a materialized view translates into a pipeline of Elasticsearch primitives:

SQL Concept	Elasticsearch Primitive
Source tables	Source indices
JOIN	Enrich policies + ingest pipelines
Computed columns	Script processors in ingest pipelines
Continuous refresh	Transforms (latest mode) with configurable frequency
Aggregations (GROUP BY)	Transforms (pivot mode)
Auto-refresh watcher	Watcher (re-executes enrich policies on source data changes)

Deployment Sequence

When a materialized view is created, the engine deploys artifacts in this order:

1. Alter source schemas — add changelog tracking fields (_updated_at)
2. Create intermediate indices — changelog, enriched, and final view index
3. Preload changelogs — copy existing data into changelog indices
4. Create enrich policies — define lookup enrichment from source indices
5. Create watcher — schedule automatic re-execution of enrich policies
6. Execute enrich policies — build initial enrich indices
7. Create ingest pipelines — enrichment + computed field processors
8. Create transforms — changelog, enrichment, computed fields, aggregation
9. Start transforms — sequentially, with checkpoint waits between groups
10. Save metadata — persist MV definition for SHOW/DESCRIBE/DROP

Rollback is automatic on deployment failure.

---

CREATE MATERIALIZED VIEW

CREATE [OR REPLACE] MATERIALIZED VIEW [IF NOT EXISTS] view_name
[REFRESH EVERY interval time_unit]
[WITH (option = value [, ...])]
AS select_statement

Component	Required	Description
view_name	Yes	Unique name for the materialized view
OR REPLACE	No	Replace existing view (drops and recreates)
IF NOT EXISTS	No	Skip creation if view already exists
REFRESH EVERY	No	Automatic refresh interval
WITH (...)	No	Additional options (delay, user_latency)
AS select	Yes	The SELECT query defining the view

Refresh Interval

REFRESH EVERY 10 SECONDS
REFRESH EVERY 5 MINUTES
REFRESH EVERY 1 HOUR

Options

Option	Type	Description
delay	Interval	Delay before processing new data (allows late arrivals)
user_latency	Interval	Maximum acceptable query latency for users

Simple View (no JOIN)

CREATE MATERIALIZED VIEW active_orders_mv
REFRESH EVERY 30 SECONDS
AS
SELECT id, amount, status, created_at
FROM orders
WHERE status = 'active';

View with JOIN

CREATE OR REPLACE MATERIALIZED VIEW orders_with_customers_mv
REFRESH EVERY 8 SECONDS
WITH (delay = '2s', user_latency = '1s')
AS
SELECT
  o.id,
  o.amount,
  c.name AS customer_name,
  c.email,
  c.department.zip_code AS customer_zip,
  UPPER(c.name) AS customer_name_upper,
  COALESCE(
    NULLIF(o.createdAt, DATE_PARSE('2025-09-11', '%Y-%m-%d') - INTERVAL 2 DAY),
    CURRENT_DATE
  ) AS effective_date
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 'completed';

This creates changelog transforms, enrich policies, ingest pipelines with enrichment and script processors, and the final materialized view index.

View with Aggregations

CREATE OR REPLACE MATERIALIZED VIEW orders_by_city_mv
AS
SELECT
  c.city,
  c.country,
  COUNT(*) AS order_count,
  SUM(o.amount) AS total_amount,
  AVG(o.amount) AS avg_amount,
  MAX(o.amount) AS max_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.city, c.country
HAVING SUM(o.amount) > 10000
ORDER BY total_amount DESC
LIMIT 100;

---

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW [IF EXISTS] view_name;

Drops the view and all associated artifacts: transforms, intermediate indices, ingest pipelines, and enrich policies.

---

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW [IF EXISTS] view_name [WITH SCHEDULE NOW];

Forces an immediate refresh by refreshing changelog indices and re-executing enrich policies.

---

Inspect Commands

-- View the schema
DESCRIBE MATERIALIZED VIEW orders_with_customers_mv;

-- View metadata
SHOW MATERIALIZED VIEW orders_with_customers_mv;

-- View the normalized SQL
SHOW CREATE MATERIALIZED VIEW orders_with_customers_mv;

-- Check transform status
SHOW MATERIALIZED VIEW STATUS orders_with_customers_mv;

-- List all materialized views
SHOW MATERIALIZED VIEWS;

---

Complete Example

1. Create source tables

CREATE TABLE IF NOT EXISTS orders (
  id INT NOT NULL,
  customer_id INT NOT NULL,
  amount DOUBLE,
  status KEYWORD DEFAULT 'pending',
  items ARRAY<STRUCT> FIELDS (
    product_id INT,
    quantity INT,
    price DOUBLE
  ),
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS customers (
  id INT NOT NULL,
  name VARCHAR,
  email KEYWORD,
  department STRUCT FIELDS (
    name VARCHAR,
    zip_code KEYWORD
  ),
  PRIMARY KEY (id)
);

2. Load data

COPY INTO orders FROM '/data/orders.json' WITH (format = 'json');
COPY INTO customers FROM '/data/customers.json' WITH (format = 'json');

3. Create the materialized view

CREATE OR REPLACE MATERIALIZED VIEW orders_with_customers_mv
REFRESH EVERY 8 SECONDS
WITH (delay = '2s', user_latency = '1s')
AS
SELECT
  o.id,
  o.amount,
  c.name AS customer_name,
  c.email,
  c.department.zip_code AS customer_zip,
  UPPER(c.name) AS customer_name_upper
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 'completed';

4. Query the materialized view

SELECT * FROM orders_with_customers_mv
WHERE customer_name = 'Alice'
ORDER BY amount DESC
LIMIT 10;

5. Force a refresh

REFRESH MATERIALIZED VIEW orders_with_customers_mv WITH SCHEDULE NOW;

6. Drop the view

DROP MATERIALIZED VIEW IF EXISTS orders_with_customers_mv;

---

Version Compatibility

Feature	ES6	ES7	ES8	ES9
Materialized Views	No	Yes*	Yes	Yes
WITH SCHEDULE NOW	No	No	Yes	Yes

* Requires Elasticsearch 7.5+ (transforms and enrich policies)

---

Limitations

Limitation	Details
UNNEST JOIN	Not supported in materialized views
Quota limits	Community edition: max 3 views. Pro: limited. Enterprise: unlimited
Watcher dependency	Automatic enrich policy re-execution relies on Elasticsearch Watchers (requires Platinum/Enterprise license)
Eventual consistency	Data is eventually consistent based on refresh frequency and delay
Join cardinality	JOINs use enrich policies which match on a single field

Watcher Dependency and Elasticsearch Licensing

Materialized views with JOINs rely on enrich policies to denormalize data. When lookup table data changes, the corresponding enrich policy must be re-executed. The engine creates an Elasticsearch Watcher to automate this, but Watchers require an Elasticsearch Platinum or Enterprise license.

Workaround for clusters without Watcher support:

Use an external scheduled job (cron, Kubernetes CronJob, Airflow) to periodically re-execute enrich policies:

EXECUTE ENRICH POLICY orders_with_customers_mv_customers_enrich_policy;
-- Or trigger a full refresh
REFRESH MATERIALIZED VIEW orders_with_customers_mv;

Note

Transforms and enrich policies themselves are available in the free/basic Elasticsearch license starting from ES 7.5+. Only the Watcher component requires a paid Elasticsearch license.