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 30 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

Single-table View (no JOIN)

A materialized view over a single table is supported. With no JOIN there is no enrichment chain: the engine generates exactly one transform, reading the source table and writing the view index, applying the WHERE, GROUP BY and aggregations of the definition.

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

This creates:

• One transform (source → view) — no changelog transform, no enrich policy, no ingest pipeline
• No watcher — nothing needs re-executing on a schedule, so a single-table view never touches Watcher at all and needs no automatic refresh (see Watcher Dependency and Elasticsearch Licensing)
• The view index active_orders_mv

A single-table view whose SELECT has no WHERE, no GROUP BY and no aggregation is also accepted — it materialises the projected columns of the source table.

Minimum refresh interval

When REFRESH EVERY is given without an explicit delay, the engine derives the per-transform delay from the frequency. Every transform must be able to run twice per refresh, so:

REFRESH EVERY  ≥  2 × (number of transforms) × 10 seconds

View shape	Transforms	Minimum REFRESH EVERY
Single table (no JOIN)	1	20 seconds
One JOIN + WHERE	3 (changelog + enrichment + final)	60 seconds
One JOIN + WHERE + computed columns	4 (+ computed-fields)	80 seconds

Below that the statement is rejected with “Calculated delay (N seconds) is too small … Minimum required frequency: M seconds”. Supply an explicit WITH (delay = '…') to use a shorter frequency — the delay you give is then used as-is, subject only to delay × 2 × transforms ≤ frequency.

View with JOIN

CREATE OR REPLACE MATERIALIZED VIEW orders_with_customers_mv
REFRESH EVERY 8 SECONDS
WITH (delay = '1s', 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 = '1s', 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
RIGHT JOIN / FULL OUTER JOIN	Not supported (see below). Use LEFT JOIN with swapped table order.
Quota limits	Community: 1 view · Pro: 50 · Enterprise: unlimited
Watcher dependency	Automatic enrich policy re-execution relies on Elasticsearch Watcher, which the free Basic license does not include. The view is still created and REFRESH MATERIALIZED VIEW still works
Eventual consistency	Data is eventually consistent based on refresh frequency and delay
Join cardinality	JOINs use enrich policies which match on a single field

Supported JOIN types

Only INNER JOIN and LEFT JOIN (LEFT OUTER JOIN) are supported for materialized views.

The MV’s ingest pipeline is driven by writes to the main (left-hand) FROM table — every joined table is enriched into the main-table document via an EnrichProcessor. There is no mechanism for the pipeline to fire from the right-hand side, so:

• RIGHT JOIN A ON A.x = B.y cannot preserve unmatched rows of the joined table when no matching main-table row triggers the pipeline. Rewrite the query with the right-hand table as the main FROM table and use LEFT JOIN.
• FULL OUTER JOIN needs to preserve rows from both sides, which the single-direction enrichment pipeline cannot do.

Attempting to create a materialized view with RIGHT JOIN or FULL OUTER JOIN fails at creation time with an actionable error message; no partial artifacts are deployed.

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 Watcher is not included in the free Basic license — it needs a Trial license or a subscription that offers it.

Impact:

• With a license that includes Watcher (Trial, or a paid subscription): fully automatic — the watcher re-executes the enrich policies transparently.
• Without it (the free Basic license): CREATE MATERIALIZED VIEW still succeeds and returns a warning. The view is created, its metadata is persisted and it is immediately queryable — only the automatic refresh is unavailable. SHOW MATERIALIZED VIEW <name> then reports auto_refresh as unavailable: … and watcher_id as N/A. Changes to lookup tables are not reflected until the enrich policies are re-executed, which is exactly what REFRESH MATERIALIZED VIEW <name> does — it always works, on every license.

✅ Success (9549ms)
⚠️ Materialized view 'orders_with_customers_mv' was created, but automatic refresh is
   unavailable: this deployment cannot host the refresh watcher, so the joined data cannot
   be refreshed on a schedule. Run 'REFRESH MATERIALIZED VIEW orders_with_customers_mv'
   whenever the joined tables change, or schedule that statement externally (cron,
   Kubernetes CronJob, Airflow). Reason: Elasticsearch error during createWatcher:
   security_exception - current license is non-compliant for [watcher]

The wording names no cause, because the licence is only one of two ways to land here. The other is a cluster with no usable webhook credentials for the watcher to call back with — which is what xpack.security.enabled: false gives you, the default local and CI setup. The actual cause is quoted verbatim after Reason:. Either way the outcome is the same: the view exists and is queryable, only the scheduled refresh is missing.

A wrong credential is not this case. A username with no password, or a bad API key, keeps failing the statement loudly — that is a fixable misconfiguration, not a missing capability, and degrading it would hide the typo.

A view created this way keeps auto_refresh: unavailable even if the cluster is later upgraded to a license that includes Watcher — re-run CREATE OR REPLACE MATERIALIZED VIEW with a changed definition to redeploy it with a watcher.

Refreshing a view on a cluster without Watcher:

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, enrich policies and ingest pipelines are all available in the free/basic Elasticsearch license starting from ES 7.5+. The Watcher component is the only part of a materialized-view deployment that a Basic license refuses — which is why the view itself is still created.