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 MATERIALIZED VIEW [IF NOT EXISTS] view_name
[REFRESH EVERY interval time_unit]
[WITH (option = value [, ...])]
AS select_statement

OR REPLACE is a separate form, and it does not accept IF NOT EXISTS — the parser rejects CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS ...:

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

The clause order is fixed: REFRESH EVERY comes before WITH (...). The reverse order is rejected.

view_name may be written bare (orders_mv), double-quoted ("orders_mv") or back-quoted (`orders_mv`) — all three name the same view.

⚠️ A qualifier is not interpreted, and the two spellings are not equivalent. The parser records a quoted qualifier without making it part of the name, so CREATE MATERIALIZED VIEW "analytics"."orders_mv" creates the view orders_mv; a bare dotted name is a single legal index name, so CREATE MATERIALIZED VIEW analytics.orders_mv creates the view analytics.orders_mv. The two statements create two different indices.

Component	Required	Description
view_name	Yes	Unique name for the materialized view
OR REPLACE	No	Replace existing view (drops and recreates). Cannot be combined with IF NOT EXISTS. A new definition refused for its JOIN structure (for example a RIGHT or FULL OUTER JOIN), for aggregates without a GROUP BY, for an aggregate of an expression or for two aggregates sharing a column name leaves the existing view in place, and so does a new definition whose source tables cannot be loaded; a later refusal (the refresh interval or delay, column lineage) and a failure while deploying the new definition come after the existing view has been dropped
IF NOT EXISTS	No	Skip creation if view already exists. Accepted only on CREATE MATERIALIZED VIEW, never after OR REPLACE
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

Supported time units: MILLISECOND(S), SECOND(S), MINUTE(S), HOUR(S), DAY(S), WEEK(S), MONTH(S), YEAR(S)

⚠️ Unlike every other keyword in the dialect, the unit is case-sensitive and must be upper case, and the whitespace between the number and the unit is required: REFRESH EVERY 30 seconds and REFRESH EVERY 30SECONDS are both rejected, while refresh every 30 SECONDS is accepted.

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;

When a GROUP BY clause is present, the engine generates an additional pivot transform for the aggregation step.

The LIMIT of a view’s definition is recorded with it, but the view stores every group: limit the rows when you query the view. An OFFSET of groups is refused by name, for the same reason.

A view grouped by an expression — SELECT YEAR(o.created_at) AS y, COUNT(*) AS n … GROUP BY YEAR(o.created_at), or GROUP BY y, or GROUP BY 1 — stores one row per group, keyed by the SELECT alias. Its key column is declared with the type its expression reports: BIGINT for YEAR, MONTH, DAY and the other date parts, the column’s own type for ABS, and VARCHAR with a keyword sub-field for UPPER and the other string functions. A view grouped by an aliased column (SELECT c.city AS town, COUNT(*) … GROUP BY town, GROUP BY c.city or the ordinal) stores the key under its alias, over one table or a JOIN. A group whose key is NULL is not stored.

A HAVING in a view is more constrained than a HAVING in a SELECT — most importantly, every aggregate it reads must be published in the SELECT list with an alias, as SUM(o.amount) AS total_amount is above. See HAVING in a materialized view.

---

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 (NOT implemented today - see the note below)
SHOW MATERIALIZED VIEWS;

⚠️ SHOW MATERIALIZED VIEWS is not implemented today. The statement parses, and the Materialized Views extension claims it, but the extension has no branch for the plural form and answers 400 Unsupported statement for Materialized Views extension. Until that is implemented, inspect a view you can name with SHOW MATERIALIZED VIEW <name>.

---

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 _ingest.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' FILE_FORMAT = 'JSON';
COPY INTO customers FROM '/data/customers.json' FILE_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
HAVING	More constrained than in a SELECT: every aggregate the clause reads must be published in the SELECT list with an alias, and it cannot filter on a grouping key (see below)
LIMIT / OFFSET	A LIMIT in the definition is only recorded: the view stores every row or group. An OFFSET of groups is refused by name.
A GROUP BY key the view cannot compute	A key whose function the engine does not script — DATE_TRUNC with an interval, DATE_FORMAT, … — is refused at creation, naming the key and its expression. So is a view whose aggregation would never write one of its key columns.
The NULL group	A group whose key is NULL is not stored, as a query’s GROUP BY forms no NULL group either.
A nested column in WHERE	A condition over a nested column written without NESTED(…) matches nothing in a view’s WHERE (a query tests the column’s elements). Write the NESTED(…) condition.
Every table needs its schema	A view one of whose tables has no schema under its index name is refused: “Materialized view v cannot be typed: no schema was loaded for table t.”
UNNEST JOIN	Not supported in materialized views
RIGHT JOIN / FULL OUTER JOIN	Not supported (see below). Use LEFT JOIN with swapped table order.
Aggregates need a GROUP BY	A view that selects an aggregate — a window function, a percentile or a standard deviation included — without a GROUP BY, or whose GROUP BY reaches none of the tables it reads (a CROSS JOINed table, or an alias of a table joined more than once that the view does not read: only one alias of such a table is read), is refused at creation: a materialized view computes aggregates per GROUP BY group, and without one it would store the source rows and none of the aggregates. The refusal names each aggregate the view would not store: compute it with a query instead. Known limitation: an aggregate nested inside a function or an expression, such as ROUND(AVG(amount), 2) or SUM(amount) / COUNT(*), is not detected — the refusal does not name it, and does not refuse a view whose only aggregates are nested this way.
Aggregates of an expression	An aggregate whose operand is an expression rather than a column — MAX(DATE_TRUNC(created_at, MONTH)), SUM(CAST(amount AS INT)), COUNT(UPPER(name)) — is refused at creation: a materialized view aggregates a column as it is stored, so it would compute MAX(created_at), SUM(amount) and COUNT(*) in their place. A CAST to the column’s own declared type (SUM(CAST(amount AS DOUBLE)) over a DOUBLE column, and the ::, TRY_CAST and CONVERT spellings) is the column itself, and is accepted, except under a window; a cast to another type is refused. Compute it with a query instead.
One column per aggregate	Two aggregates that would produce the same column name — typically unaliased, COUNT(x), COUNT(DISTINCT x) or SUM(amount), MAX(amount) — are refused at creation; give each its own alias (COUNT(x) AS n, COUNT(DISTINCT x) AS n_distinct). The refusal proposes aliases only for the aggregates the view would store under them, and names the others — for example a window function, a percentile, or an aggregate of a column in a single-table view without a computed column — to be computed with a query. A COUNT(*) is one of the aggregates a view stores, so the refusal proposes an alias for it too.
Quota limits	Community: 1 view · Pro: 50 · Enterprise: unlimited
Watcher dependency (ES license)	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 (see below)
Eventual consistency	Data is eventually consistent based on refresh frequency and delay
Join cardinality	JOINs use enrich policies which match on a single field

HAVING in a materialized view

Since engine 0.24.0, CREATE MATERIALIZED VIEW refuses a HAVING that the Elasticsearch transform behind the view cannot express, naming the clause and the remedy. The refusal is a parse-time rejection (HTTP 400), so it reaches every venue — REPL, JDBC, Flight SQL — and no artifact is deployed.

The same HAVING usually stays valid in a plain SELECT. This is a limit of the transform a view deploys, not of the query engine, and not of Elasticsearch.

The case you are most likely to meet: an aggregate with no SELECT alias.

-- REFUSED: SUM(amount) carries no alias
CREATE MATERIALIZED VIEW sales_by_city_mv
AS
SELECT city, SUM(amount)
FROM orders
GROUP BY city
HAVING SUM(amount) > 5;

-- Accepted: the same view, with the aggregate published under an alias
CREATE MATERIALIZED VIEW sales_by_city_mv
AS
SELECT city, SUM(amount) AS total
FROM orders
GROUP BY city
HAVING SUM(amount) > 5;

A view’s transform names each aggregation after its SELECT alias, so an unaliased aggregate leaves the group filter pointing at a metric the view never creates. Adding AS total is the whole fix — the HAVING itself does not change.

Why a view differs from a search

A search has three channels for a HAVING; a transform’s pivot offers two of them.

The HAVING names	A search applies it through	A view’s pivot
a grouping key — HAVING city = 'Paris'	the terms aggregation’s include / exclude list	no such channel — group_by emits {"terms":{"field":…}} and nothing else
arithmetic over aggregates — HAVING spread > 3	a bucket_script	a bucket_script, for a calculation the SELECT list publishes under an alias
a metric — HAVING SUM(amount) > 5	a bucket_selector	a bucket_selector

A pivot also computes a narrower set of metrics than a search: only MIN, MAX, SUM, AVG and COUNT (including COUNT(DISTINCT …)).

What is refused

A view’s HAVING that…	Why	Remedy
reads an aggregate with no SELECT alias — SELECT city, SUM(amount) … HAVING SUM(amount) > 5	the pivot names each aggregation after its SELECT alias, so the condition matches none of them	publish the aggregate with an alias: SUM(amount) AS total
filters on a grouping key — HAVING city = 'Paris'	a transform’s group_by has no include / exclude channel	move the condition into the view’s WHERE
has no GROUP BY	no GROUP BY means no pivot, and a bucket_selector hangs on the pivot	add a GROUP BY, or materialize the aggregate and filter when querying the view
names an aggregate a pivot cannot compute — HAVING STDDEV(amount) > 1	a pivot computes only MIN, MAX, SUM, AVG and COUNT, so this aggregate exists in no view	filter when querying the view
names an aggregate the SELECT list does not publish — SELECT city, COUNT(*) AS n … HAVING MAX(amount) > 100	the filter would read a metric the view does not compute	add MAX(amount) AS max_amount to the SELECT list
contains a nested, child or parent predicate	the pivot would emit the metric condition alone — a partial filter returning the wrong groups at HTTP 200	filter when querying the view
reads the clock — CURRENT_DATE, NOW() — in the HAVING or in a calculation	nothing binds the clock in a transform	filter when querying the view
reads a calculation over aggregates that has no alias, whose operands are not SELECT items, that reads an aggregate a pivot cannot compute, or whose alias collides with another column	the pivot computes a calculation from the published aggregates, under its alias	alias the calculation, and publish its operands in the SELECT list

Every aggregate a HAVING names is read, so two aggregates compared (HAVING MAX(amount) > MIN(amount)), a function over several of them (COALESCE(MAX(a), MIN(b)) > 1, GREATEST, LEAST), ISNULL(MIN(a)) and an alias inside a function filter the groups as the same HAVING does in a search. A group with no value of the column reads its MIN, MAX or AVG as NULL, as in a search.

What still works

• A metric compared with a constant, when the aggregate is published under a SELECT alias — SELECT city, SUM(amount) AS total … HAVING SUM(amount) > 5. The alias form, HAVING total > 5, is equally accepted.
• A JOIN view with a metric-only HAVING — the aggregation example above.
• COUNT(DISTINCT …) — SELECT city, COUNT(DISTINCT customer_id) AS customers … HAVING COUNT(DISTINCT customer_id) > 1.
• A function grouping key — SELECT DATE_TRUNC(order_date, MONTH) AS month, SUM(amount) AS total … GROUP BY month HAVING SUM(amount) > 100.
• A calculation over published aggregates, and a HAVING on it — SELECT city, MAX(amount) AS hi, MIN(amount) AS lo, MAX(amount) - MIN(amount) AS spread … GROUP BY city HAVING spread > 3. The view stores the calculation with its type: a DATE - DATE is a number of days, and a DATE / TIMESTAMP calculation (MAX(d) + 1, GREATEST(MAX(d), MAX(d2))) is stored as a date.

What changed

Before engine 0.24.0 every refused statement above was accepted, and the view then materialized the wrong groups at HTTP 200: the condition was silently dropped and every group was materialized, or the deployed bucket_selector read an undeclared parameter, rejected every group, and the view came out empty. A calculation over aggregates was declared in the view schema and never stored. A view bakes that missing filter into a stored transform which then feeds dashboards, which is why the refusal is preferable. A view already deployed keeps running as it is — the rules apply when a view is created, and CREATE OR REPLACE is validated exactly like CREATE.

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.