# Create materialized views in ClickHouse®


Use materialized views to transform data automatically as it arrives. When a new block of data
is inserted into a source table, the query defined by the materialized view runs on that block
and writes the result to a destination table. This is the standard ClickHouse pattern for
pre-aggregating data, maintaining rollups, or persisting transformed copies of raw events.

The destination of the data (for example, a MergeTree family table) is defined by the `TO`
clause. This process is illustrated in the following diagram:

```mermaid
flowchart LR
  a[Inserts into<br />source table]
  b{{Source table}}
  c[Materialized view]
  d{{MergeTree<br />family table}}
  a --> b --> c --> d
```

## Create a materialized view

This example builds a daily rollup of the `default.trips` table loaded in the
[quick start](/product/dbaas/service-specific/clickhouse/quick-start/#load-a-dataset). Any
MergeTree table with a date column works the same way.

1. Create the destination table that stores the transformed data:

   ```sql
   CREATE TABLE default.daily_totals (
       pickup_date Date,
       total UInt64
   )
   ENGINE = SummingMergeTree
   ORDER BY pickup_date
   ```

1. Create the materialized view on top of the source table:

   ```sql
   CREATE MATERIALIZED VIEW default.my_view TO default.daily_totals
   AS SELECT pickup_date, count() AS total
   FROM default.trips
   GROUP BY pickup_date
   ```

From this point on, every insert into `default.trips` automatically updates
`default.daily_totals`. The view only processes new blocks of data, so the two million rows
already in the table are not aggregated yet. Backfill them once with the same query:

```sql
INSERT INTO default.daily_totals
SELECT pickup_date, count() AS total
FROM default.trips
GROUP BY pickup_date
```

> [!NOTE]
> The two `CREATE` statements are DDL, and databases in Managed ClickHouse use the `Replicated`
> engine, so each one returns one row per node instead of the empty output stock ClickHouse
> prints. See
> [reading the output of a DDL statement](/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/#read-the-output-of-a-ddl-statement).

> [!IMPORTANT]
> A materialized view aggregates within each inserted block, not across the whole destination
> table, so it writes one partial row per key per insert. That is why the destination uses
> `SummingMergeTree` rather than plain `MergeTree`: the engine collapses rows sharing the same
> `ORDER BY` key. Collapsing happens during background merges, at an unpredictable time, so
> always re-aggregate at read time rather than trusting a single row to be final:
>
> ```sql
> SELECT pickup_date, sum(total) AS total
> FROM default.daily_totals
> GROUP BY pickup_date
> ```
>
> With a plain `MergeTree` destination the rows would never collapse at all, and a query
> reading `total` directly would silently return one partial value per insert.

> [!NOTE]
> Tables declared with a `MergeTree` family engine are automatically replicated by the service,
> so the data written by the materialized view is exchanged and replicated across all the nodes.