Last update: 2025-09-26T16:41:40.000Z
Content:
---
title: "Materialized views · Tinybird Docs"
theme-color: "#171612"
description: "Materialized views process your data at ingest time to increase the performance of your queries and endpoints."
inkeep:version: "forward"
---




# Materialized views [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#materialized-views)

Copy as MD A materialized view is the continuous, streaming result of a pipe saved as a new data source. As new data is ingested into the origin data source, the transformed results from the pipe are continually inserted in the new materialized view, which you can query as any other data source.

Preprocessing data at ingest time reduces latency and cost-per-query, and can significantly improve the performance of your endpoints. For example, you can transform the data through SQL queries, using calculations such as counts, sums, averages, or arrays, or transformations like string manipulations or joins. The resulting materialized view acts as a data source you can query or publish.

Typical use cases of Materialized views include:

- Aggregating, sorting, or filtering data at ingest time.
- Improving the speed of a query that's taking too much time to run.
- Simplifying query development by automating common filters and aggregations.
- Reducing the amount of data processed by a single query.
- Changing an existing schema for a different use case.

You can create a new materialized view and populate it with all existing data without any cost.

## Create materialized views [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#create-materialized-views)

Materialized views are defined in a .pipe file using the `TYPE MATERIALIZED` directive. See [Materialized pipes](/docs/forward/dev-reference/datafiles/pipe-files#materialized-pipes).

Consider an `origin` data source, for example `my_origin.datasource` , like the following:

##### Origin data source

SCHEMA >
    `id` Int16,
    `local_date` Date,
    `name` String,
    `count` Int64 You might want to create an optimized version of the data source that preaggregates `count` for each ID. To do this, create a new data source that uses a `SimpleAggregateFunction` as a materialized view.

First, define the `destination` data source, for example `my_destination.datasource`:

##### Destination data source

SCHEMA >
    `id` Int16,
    `local_date` Date,
    `name` String,
    `total_count` SimpleAggregateFunction(sum, UInt64)

ENGINE "AggregatingMergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(local_date)"
ENGINE_SORTING_KEY "local_date,id,name" Write a materialized pipe, for example `my_transformation.pipe`:

##### Transformation pipe

NODE transformation_node

SQL >
    SELECT
        id,
        local_date,
        name,
        sum(count) as total_count
    FROM
        my_origin
    GROUP BY
        id,
        local_date,
        name

TYPE materialized
DATASOURCE my_destination Once you have the origin and destination data sources defined and the materialized pipe, deploy the project to apply the changes. Materialized views are populated automatically in the target environment.

The order of columns in your transformation pipe's SELECT statement doesn't need to match the order defined in the destination data source schema. Tinybird maps columns by name, not by position. As long as the column names and data types are compatible, the materialization will work correctly.

## Populating materialized views [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#populating-materialized-views)

Materialized views are populated automatically when you deploy your project. The deployment process handles the initial population and subsequent data migrations.

When you first deploy a project with a materialized view, Tinybird will create the view and populate it with all existing data from the origin data source. This process happens automatically and is managed by Tinybird's deployment system.

### Failed populate jobs [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#failed-populate-jobs)

When a populate job fails for the first time, the materialized view is automatically unlinked from its source pipe. This prevents failed jobs from affecting the materialized view's data integrity.

You can get failed population jobs and their errors to fix them with a query like this:

SELECT * FROM tinybird.jobs_log
WHERE job_type = 'populate' 
AND status = 'failed' 
ORDER BY created_at DESC Use the [Jobs API](/docs/api-reference/jobs-api) to get more detailed information about failed populate jobs and troubleshoot the issues before re-linking the materialized view.

## Changing a materialized view [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#changing-a-materialized-view)

If you need to make changes to a materialized view after deployment (such as changing its schema or engine settings), you'll need to follow the data source evolution process. This might require using a [forward query](/docs/forward/test-and-deploy/evolve-data-source#forward-query) to ensure historical data is properly migrated.

Learn more about:

- [  Deployments in Tinybird](/docs/forward/test-and-deploy/deployments)   to understand how deployments manage state changes
- [  Evolving data sources](/docs/forward/test-and-deploy/evolve-data-source#materialized-data-sources)   for details on making changes to materialized views after deployment

## Altering materialized views [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#altering-materialized-views)

When you need to update the query logic of an existing materialized view without recreating the entire table, you can use the `DEPLOYMENT_METHOD alter` directive. This approach uses `ALTER TABLE ... MODIFY QUERY` instead of dropping and recreating the materialized view, which minimizes data movement and improves deployment performance.

### When to use ALTER [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#when-to-use-alter)

Use the `DEPLOYMENT_METHOD alter` directive when you want to:

- Update the query logic of an existing materialized view
- Preserve existing data while modifying the transformation
- Minimize deployment time and resource usage
- Avoid data movement in production environments with large datasets

### Syntax [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#syntax)

Add the `DEPLOYMENT_METHOD alter` directive to your materialized pipe file:

##### my_transformation.pipe

NODE transformation_node
SQL >
    SELECT
        id,
        local_date,
        name,
        sum(count) as total_count
    FROM
        my_origin
    WHERE
        local_date >= today() - interval 90 day  -- Updated filter from 30 to 90 days
    GROUP BY
        id,
        local_date,
        name

TYPE materialized
DATASOURCE my_destination
DEPLOYMENT_METHOD alter
### Limitations of ALTER usage [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#limitations-of-alter-usage)

- **  Existing materialized views only**   : Can only be used on materialized views that already exist in the target environment
- **  Query changes only**   : The directive only works when there are actual changes to the SQL query
- **  No target changes**   : Cannot change the target data source ( `DATASOURCE`   directive)
- **  Deployment limitations**   : Staging ingestion and reads are not available during ALTER deployments

## Limitations [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#limitations)

As explained, materialized views work as insert triggers, which means a delete or truncate operation on your original data source doesn't affect the related materialized views. But be sure to understand [materialized views iterations](/docs/forward/test-and-deploy/evolve-data-source#materialized-data-sources) before deleting data or setting TTLs to the origin data source.

As transformation and ingestion in the materialization is done on each block of inserted data in the original data source, some operations such as `GROUP BY`, `ORDER BY`, `DISTINCT` and `LIMIT` might need a specific `engine` , such as `AggregatingMergeTree` or `SummingMergeTree` , which can handle data aggregations.

The data source resulting from a materialization generated using `JOIN` is automatically updated **only** if and when a new operation is performed over the data source in the `FROM`.

You can't create Materialized Views that depend on the `UNION` of several Data Sources.

## Next steps [¶](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views#next-steps)

- Learn about[  Copy pipes](/docs/forward/work-with-data/optimize/copy-pipes)  .
- Publish your data with[  API endpoints](/docs/forward/work-with-data/publish-data)  .


