# Moose / Olap / Model Materialized View Documentation – TypeScript

## Included Files
1. moose/olap/model-materialized-view/model-materialized-view.mdx

## Creating Materialized Views

Source: moose/olap/model-materialized-view/model-materialized-view.mdx

Create and configure materialized views for data transformations

# Modeling Materialized Views

## Overview

Materialized views are write-time transformations in ClickHouse. A static `SELECT` populates a destination table from one or more sources. You query the destination like any other table. The `MaterializedView` class wraps [ClickHouse `MATERIALIZED VIEW`](https://clickhouse.com/docs/en/sql-reference/statements/create/view/#create-materialized-view) and keeps the `SELECT` explicit. When you edit the destination schema in code and update the `SELECT` accordingly, Moose applies the corresponding DDL, orders dependent updates, and backfills as needed, so the pipeline stays consistent as you iterate.

In local dev, Moose Migrate generates and applies DDL to your local database.

Today, destination schemas are declared in code and kept in sync manually with your `SELECT`. Moose Migrate coordinates DDL and dependencies when you make those changes. A future enhancement will infer the destination schema from the `SELECT` and update it automatically.

This dependency awareness is critical for [cascading materialized views](https://clickhouse.com/docs/en/sql-reference/statements/create/view/#create-materialized-view-with-dependencies). Moose Migrate [orders DDL across views and tables](https://www.fiveonefour.com/blog/Moose-SQL-Getting-DDL-Dependencies-in-Order) to avoid failed migrations and partial states.

### Basic Usage

```ts filename="BasicUsage.ts" copy

// Define the schema of the transformed rows-- this is static and it must match the results of your SELECT. It also represents the schema of your entire destination table.
interface TargetSchema {
  id: string;
  average_rating: number;
  num_reviews: number;
}
);
```

The ClickHouse `MATERIALIZED VIEW` object acts like a trigger: on new inserts into the source table(s), it runs the SELECT and writes the transformed rows to the destination.

### Quick Reference

```typescript filename="ViewOptions.ts"

interface MaterializedViewConfig {
  // Static SELECT that computes the destination rows
  selectStatement: string | Sql;

  // Tables/views the query reads from
  selectTables: (OlapTable<any> | View)[];

  // Name of the ClickHouse MATERIALIZED VIEW object
  materializedViewName: string;

  // Destination table where materialized rows are stored
  targetTable?:
    | OlapTable
    | {
        name: string;
        engine?: ClickHouseEngines;
        orderByFields?: (keyof T & string)[];
      };

  /** @deprecated prefer targetTable */
  tableName?: string;
  /** @deprecated prefer targetTable */
  engine?: ClickHouseEngines;
  /** @deprecated prefer targetTable */
  orderByFields?: (keyof T & string)[];
}
```

## Modeling the Target Table

The destination table is where the transformed rows are written by the materialized view. You can model it in two ways:

<Tabs.Tab>

### Option 1 — Define target table inside the MaterializedView (most cases)

- Simple, co-located lifecycle: the destination table is created/updated/dropped with the MV.
- Best for: projection/denormalization, filtered serving tables, enrichment joins, and most rollups.

```ts filename="InlineTarget.ts" copy

interface Dest { id: string; value: number }

new MaterializedView({
  selectStatement: sql`SELECT id, toInt32(value) AS value FROM ${sourceTable}`,
  selectTables: [sourceTable],
  targetTable: { name: "serving_table", orderByFields: ["id"] }, // MergeTree by default
  materializedViewName: "mv_to_serving_table",
});
```

</Tabs.Tab>

<Tabs.Tab>

### Option 2 — Decoupled: reference a standalone `OlapTable`

Certain use cases may benefit from a separate lifecycle for the target table that is managed independently from the MV.

```ts filename="DecoupledTarget.ts" copy

interface Dest { id: string; value: number }

const targetTable = new OlapTable("target_table", {
  engine: ClickHouseEngines.MergeTree,
  orderByFields: ["id"],
});

new MaterializedView({
  selectStatement: sql`SELECT id, toInt32(value) AS value FROM ${sourceTable}`,
  selectTables: [sourceTable],
  targetTable: targetTable,
  materializedViewName: "mv_to_target_table",
});
```

</Tabs.Tab>

### Basic Transformation, Cleaning, Filtering, Denormalization
Create a narrower, query-optimized table from a wide source. Apply light transforms (cast, rename, parse) at write time.

  ```ts filename="Denormalization.ts" copy

  interface Dest { id: string; value: number; created_at: string }

  new MaterializedView({
    selectStatement: sql`
      SELECT id, toInt32(value) AS value, created_at
      FROM ${sourceTable}
      WHERE active = 1
    `,
    selectTables: [sourceTable],
    targetTable: { name: "proj_table" },
    materializedViewName: "mv_to_proj_table",
  });
  ```

### Aggregations

<Tabs.Tab>
### Simple Additive Rollups
When you want to maintain running sums (counts, totals) that are additive per key, use the `SummingMergeTree` engine:

  ```ts filename="Summing.ts" copy

  interface DailyCounts { 
    day: string; 
    user_id: string; 
    events: number; 
  }

  const stmt = sql`
    SELECT 
      toDate(${events.columns.timestamp}) AS day, 
      ${events.columns.user_id} AS user_id, 
      count(*) AS events
    FROM ${events}
    GROUP BY day, user_id
  `;

  new MaterializedView({
    selectStatement: stmt,
    selectTables: [events],
    targetTable: {
      name: "daily_counts",
      engine: ClickHouseEngines.SummingMergeTree,
      orderByFields: ["day", "user_id"],
    },
    materializedViewName: "mv_to_daily_counts",
  });
  ```

</Tabs.Tab>
<Tabs.Tab>
#### Complex Aggregations

When you want to compute complex aggregation metrics that are not just simple additive operations (sum, count, avg, etc), but instead uses more complex anlaytical functions: (topK,percentile, etc), create a target table with the `AggregatingMergeTree` engine.

  ```ts filename="AggTransform.ts" copy

  interface MetricsById {
    id: string;
    avg_rating: number & Aggregated<"avg", [number]>;
    daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;
  }

  //  All Aggregate Functions in this query have a [functionName][State]() suffix.
  const stmt = sql`
    SELECT
      ${events.columns.id} AS id,
      avgState(${events.columns.rating})      AS avg_rating,
      uniqExactState(${events.columns.user_id}) AS daily_uniques
    FROM ${events}
    GROUP BY ${events.columns.id}
  `;

  new MaterializedView({
    selectStatement: stmt,
    selectTables: [events],
    targetTable: {
      name: "metrics_by_id",
      engine: ClickHouseEngines.AggregatingMergeTree,
      orderByFields: ["id"],
    },
    materializedViewName: "mv_metrics_by_id",
  });
  ```

Jump to the [Advanced: AggregatingMergeTree transformations](#advanced-aggregatingmergetree-transformations) section for more details.

</Tabs.Tab>

### Fan-in Patterns
When you have multiple sources that you want to merge into a single destination table, its best to create an OlapTable and reference it in each MV that needs to write to it:

```ts filename="FanIn.ts" copy
interface DailyCounts { day: string; user_id: string; events: number }

// Create the destination table explicitly
const daily = new OlapTable("daily_counts", {
  engine: ClickHouseEngines.SummingMergeTree,
  orderByFields: ["day", "user_id"],
});

// MV 1 - write to the daily_counts table
const webStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${webEvents}`;
const mv1 = new MaterializedView({
  selectStatement: webStmt,
  selectTables: [webEvents],
  targetTable: daily,
  materializedViewName: "mv_web_to_daily_counts",
});

// MV 2 - write to the daily_counts table
const mobileStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${mobileEvents}`;
const mv2 = new MaterializedView({
  selectStatement: mobileStmt,
  selectTables: [mobileEvents],
  targetTable: daily,
  materializedViewName: "mv_mobile_to_daily_counts",
});
```

### Blue/green schema migrations
Create a new table for a breaking schema change and use an MV to copy data from the old table; when complete, switch reads to the new table and drop just the MV and old table.

For more information on how to use materialized views to perform blue/green schema migrations, see the [Schema Versioning](./schema-versioning) guide.

## Defining the transformation

The `selectStatement` is a static SQL query that Moose runs to transform data from your source table(s) into rows for the destination table.

Transformations are defined as ClickHouse SQL queries. We strongly recommend using the ClickHouse SQL reference and functions overview to help you develop your transformations.

- Use the Moose `sql` template to interpolate tables and columns safely. This gives type-checked column references and prevents runtime parameters.
- Reference tables and columns via objects in your project (e.g., `${sourceTable}`, `${sourceTable.columns.id}`) rather than string literals.

```ts filename="Transformation.ts" copy

interface Dest { id: string; name: string; day: string }

const transformation = sql`
  SELECT
    ${users.columns.id}        AS id,
    ${users.columns.name}      AS name,
    toDate(${events.columns.ts}) AS day
  FROM ${events}
  JOIN ${users} ON ${events.columns.user_id} = ${users.columns.id}
  WHERE ${events.columns.active} = 1
`;

new MaterializedView({
  selectStatement: transformation,
  selectTables: [events, users],
  targetTable: { name: "user_activity_by_day" },
  materializedViewName: "mv_user_activity_by_day",
});
```

The columns returned by your `SELECT` must exactly match the destination table schema.

- Use column aliases (`AS target_column_name`) to align names.
- All destination columns must be present in the `SELECT`, or the materialized view won't be created. Adjust your transformation or table schema so they match.

Go to the [Advanced: Writing SELECT statements to Aggregated tables](#writing-select-statements-to-aggregated-tables) section for more details.

## Backfill Destination Tables
When the MaterializedView is created, Moose backfills the destination once by running your `SELECT` (so you start with a fully populated table).

Materialized views that source from S3Queue tables are **not backfilled** automatically. S3Queue tables only process new files added to S3 after the table is created - there is no historical data to backfill from. The MV will start populating as new files arrive in S3.

You can see the SQL that Moose will run to backfill the destination table when you generate the [Migration Plan](./migration-plan).

During dev mode, as soon as you save the MaterializedView, Moose will run the backfill and you can see the results in the destination table by querying it in your local ClickHouse instance.

## Query Destination Tables

You can query the destination table like any other table.

<Tabs.Tab>
For inline or decoupled target tables, you can reference target table columns and tables directly in your queries:

```ts filename="Query.ts" copy

// Inline-defined destination table from earlier examples
const q = sql`
SELECT 
  ${mv.targetTable.columns.id},
  ${mv.targetTable.columns.value} 
FROM ${mv.targetTable} 
ORDER BY ${mv.targetTable.columns.id} 
LIMIT 10`;
```

</Tabs.Tab>

<Tabs.Tab>
If you define your target table outside of the MaterializedView, you can also just reference the table by its variable name in your queries:

```ts filename="QueryDecoupled.ts" copy

const targetTable = new OlapTable<{ id: string; average_rating: number }>("target_table")

// Assuming `targetTable` is the OlapTable you created explicitly
const q = sql`
  SELECT ${targetTable.columns.id}, ${targetTable.columns.average_rating}
  FROM ${targetTable}
  WHERE ${targetTable.columns.id} = 'abc'
`;
```

</Tabs.Tab>

Go to the [Querying Aggregated tables](#querying-aggregated-tables) section for more details on how to query Aggregated tables.

## Advanced: Aggregations + Materialized Views

This section dives deeper into advanced patterns and tradeoffs when building aggregated materialized views.

### Target Tables with `AggregatingMergeTree`

When using an `AggregatingMergeTree` target table, you must use the `AggregateFunction` type to model the result of the aggregation functions:

  ```ts filename="AggTransform.ts" copy

  interface MetricsById {
    id: string;
    /**
     * Result of avgState(events.rating)
     *   - avgState(number) returns number, so model the type as number
     *   - Aggregated arg type is [number] because the column (events.rating) is a number
     *   - Aggregated function name is "avg"
     */
    avg_rating: number & Aggregated<"avg", [number]>;
    /** 
    * Result of uniqExactState(events.user_id)
    *  - uniqExact returns an integer; use number & ClickHouseInt<"uint64"> for precision
    *  - Aggregated arg type is [string] because the column (events.user_id) is a string
    *  - Aggregated function name is "uniqExact"
    */
    daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;
  }

  //  All Aggregate Functions in this query have a [functionName][State]() suffix.
  const stmt = sql`
    SELECT
      ${events.columns.id} AS id,
      avgState(${events.columns.rating})      AS avg_rating,
      uniqExactState(${events.columns.user_id}) AS daily_uniques
    FROM ${events}
    GROUP BY ${events.columns.id}
  `;

  new MaterializedView({
    selectStatement: stmt,
    selectTables: [events],
    targetTable: {
      name: "metrics_by_id",
      engine: ClickHouseEngines.AggregatingMergeTree,
      orderByFields: ["id"],
    },
    materializedViewName: "mv_metrics_by_id",
  });
  ```

  - Using `avg()`/`uniqExact()` in the SELECT instead of `avgState()`/`uniqExactState()`
  - Forgetting to annotate the schema with `Aggregated<...>` so the target table can be created correctly
  - Mismatch between `GROUP BY` keys in your `SELECT` and the `orderByFields` of your target table

### Modeling columns with `AggregateFunction`

- Pattern: `U & Aggregated<"agg_func_name", [Types]>`
- `U` is the read-time type (e.g., `number`, `string`)
- `agg_func_name` is the aggregation name (e.g., `avg`, `uniqExact`)
- `Types` are the argument types. These are the types of the columns that are being aggregated.

```ts filename="FunctionToTypeMapping.ts" copy
number & Aggregated<"avg", [number]> // avgState(col: number)
number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]> // uniqExactState(col: string)
number & ClickHouseInt<"uint64"> & Aggregated<"count", []> // countState(col: any)

string & Aggregated<"argMax", [string, Date]> // argMaxState(col: string, value: Date)
string & Aggregated<"argMin", [string, Date]> // argMinState(col: string, value: Date)

number & Aggregated<"corr", [number, number]> // corrState(col1: number, col2: number)

```

### Writing SELECT statements to Aggregated tables

When you write to an `AggregatingMergeTree` table, you must add a `State` suffix to the aggregation functions in your `SELECT` statement.

```ts filename="AggTransform.ts" copy

interface MetricsById {
  id: string;
  avg_rating: number & Aggregated<"avg", [number]>;
  total_reviews: number & Aggregated<"sum", [number]>;
}

const aggStmt = sql`
  SELECT
    ${reviews.columns.id} AS id,
    avgState(${reviews.columns.rating}) AS avg_rating,
    countState(${reviews.columns.id})   AS total_reviews
  FROM ${reviews}
  GROUP BY ${reviews.columns.id}
`;

const mv = new MaterializedView({
  selectStatement: aggStmt,
  selectTables: [reviews],
  targetTable: {
    name: "metrics_by_id",
    engine: ClickHouseEngines.AggregatingMergeTree,
    orderByFields: ["id"],
  },
  materializedViewName: "mv_metrics_by_id",
});
```

Why states? Finalized values (e.g., `avg()`) are not incrementally mergeable. Storing states lets ClickHouse maintain results efficiently as new data arrives. Docs: https://clickhouse.com/docs/en/sql-reference/aggregate-functions/index and https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state

### Querying Aggregated Tables
When you query a table with an `AggregatingMergeTree` engine, you must use aggregate functions with the `Merge` suffix (e.g., `avgMerge`) `or rely on Moose’s `Aggregated` typing plus `sql` to auto-finalize at query time.

```ts filename="QueryAgg.ts" copy

// Auto-finalized via Aggregated + sql
const cols = mv.targetTable.columns; // mv from earlier Agg example
const autoFinalized = sql`
  SELECT ${cols.avg_rating}, ${cols.total_reviews}
  FROM ${mv.targetTable}
  WHERE ${cols.id} = '123'
`;

// Manual finalization (explicit ...Merge)
const manual = sql`
  SELECT
    avgMerge(avg_rating)   AS avg_rating,
    countMerge(total_reviews) AS total_reviews
  FROM metrics_by_id
  WHERE id = '123'
`;
```

## Choosing the right engine

- Use `MergeTree` for copies/filters/enrichment without aggregation semantics.
- Use `SummingMergeTree` when all measures are additive, and you want compact, eventually-consistent sums.
- Use `AggregatingMergeTree` for non-additive metrics and advanced functions; store states and finalize on read.
- Use `ReplacingMergeTree` for dedup/upserts or as an idempotent staging layer before rollups.