Materialized views¶

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¶

Materialized views are defined in a .pipe file using the TYPE MATERIALIZED directive. See Materialized pipes.

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

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:

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:

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.

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.

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 to ensure historical data is properly migrated.

Learn more about:

• Deployments in Tinybird to understand how deployments manage state changes
• Evolving data sources for details on making changes to materialized views after deployment

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¶

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¶

Add the DEPLOYMENT_METHOD alter directive to your materialized pipe file:

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¶

• 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¶

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 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¶

• Learn about Copy pipes.
• Publish your data with API endpoints.