> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Multi-Stage Materialized Views

<Warning>
  New in StarTree Cloud 0.16.0.
</Warning>

<Note>
  Not to be confused with the **StarTree Index**, a segment-local pre-aggregation index sometimes described as "an intelligent materialized view" — this page covers a different capability: a minion task that materializes a query's result into a separate, queryable table.
</Note>

`MaterializedViewGenerationTask` is a minion task that periodically materializes the result of a **multi-stage SQL query — including joins** — into a regular Pinot table, on a schedule. There is no automatic broker-side query rewrite: you query the materialized table directly.

## When to use this

Use this when your query needs a `JOIN` — fact-fact (two append-only tables) or fact-dimension (a fact table joined with a Pinot dimension table). For a single-table aggregation with no joins, a per-segment index (such as the StarTree Index) may already cover your use case without the operational overhead of a second table.

## Creating a multi-stage materialized view

### Option 1: SQL DDL

```sql theme={null}
CREATE MATERIALIZED VIEW factDimView (
  ts      LONG DATETIME FORMAT '1:MILLISECONDS:EPOCH' GRANULARITY '1:DAYS',
  country STRING,
  amountSum DOUBLE METRIC
)
REFRESH EVERY '1d'
PROPERTIES (
  'timeColumnName'      = 'ts',
  'useMultiStageEngine' = 'true',
  'bucketTimePeriod'    = '1d',
  'bufferTimePeriod'    = '0d'
)
AS
SELECT f.ts AS ts, d.country AS country, SUM(f.amount) AS amountSum
FROM salesFact f JOIN countryDim d ON f.userId = d.userId
GROUP BY f.ts, d.country;
```

`'useMultiStageEngine' = 'true'` is what routes this DDL to `MaterializedViewGenerationTask` instead of the native single-stage `MaterializedViewTask` — without it, a `JOIN` in the `AS` query is rejected outright. An **explicit column list is required** for join views; Pinot can't infer a schema from more than one source table.

### Option 2: JSON table config

Equivalent to the DDL above, expressed directly in the table config:

```json theme={null}
{
  "isMaterializedView": true,
  "segmentsConfig": { "timeColumnName": "ts" },
  "task": {
    "taskTypeConfigsMap": {
      "MaterializedViewGenerationTask": {
        "definedSQL": "SELECT f.ts AS ts, d.country AS country, SUM(f.amount) AS amountSum FROM salesFact f JOIN countryDim d ON f.userId = d.userId GROUP BY f.ts, d.country",
        "bucketTimePeriod": "1d",
        "bufferTimePeriod": "0d",
        "maxTasksPerBatch": "10"
      }
    }
  }
}
```

### Supported and rejected query shapes

**Supported:** fact-fact joins between two append-only tables, and fact-dimension joins (fact table + a Pinot table with `isDimTable=true`).

**Rejected at creation time:** inner `LIMIT`/`OFFSET`, top-level `UNION`/`INTERSECT`/`EXCEPT`/`WITH`, `SELECT *`, and leading `SET ...;` statements.

Cluster config `materialized.view.task.default.query.limit` overrides the default outer `LIMIT` the task injects per materialization window.

## Limitations (v1)

* **No automatic query rewrite.** Query the materialized table directly by name — queries against the source fact/dimension tables are never redirected here.
* **No automatic repair on base-table schema changes.** `MaterializedViewDefinitionMetadata` isn't persisted, so if a source table's schema changes underneath a materialized view, there's no auto-repair path — you need to drop and recreate the view.
* **APPEND-only scheduling.** The task materializes new time windows as they complete; it does not currently handle upserts/backfills into already-materialized windows.

## FAQs

### Can I use this with a REALTIME source table?

Fact-fact joins require append-only tables; check the current source-table restrictions before onboarding a realtime table as a join source.

### What happens if I change the source tables' schema after creating the view?

Nothing automatic — since view metadata isn't tracked for repair in v1, drop and recreate the materialized view after a base-table schema change.

### Does querying the materialized table require the multi-stage engine?

No — once materialized, the output is a regular Pinot table; query it however you'd query any other table. Only the *materialization* itself runs on the multi-stage engine.