Using Materialized Views

When aggregating or transforming data, sometimes it’s useful to save the results in a Materialized View, and then use the Materialized View in your pipes.

Materialized Views work as insert triggers over the left most table in the Materialized Node SQL query, that means when new data is appended to the left most Data Source (the trigger) the Materialized Node query is executed. As a rule of thumb, use aggregate functions (count, avg, …) and GROUP BY expressions over the left most Data Source in the top most node or query being materialized.

Creating Materialized Views from the Tinybird UI is a guided process that analyzes the query to be materialized and suggests default engine parameters. While this work in most of the cases we recommend you to read the guide to master Materialized Views and look at the following troubleshooting tips.

Use same alias in the SELECT and GROUP BY

When there’s a column in the GROUP BY with a different alias in the SELECT clause, for instance:

BAD: different alias in SELECT and GROUP BY
SELECT
    key,
    multiIf(value = 0, 'isZero', 'isNotZero') as zero,
    sum(amount) as amount
FROM ds
GROUP BY key, value

In this case, you might get an error like this:

` Column 'value' is present in the GROUP BY but not in the SELECT clause `

To avoid possible errors, always include in the GROUP BY the column alias:

GOOD: same alias in SELECT and GROUP BY
SELECT
    key,
    multiIf(value = 0, 'isZero', 'isNotZero') as zero,
    sum(amount) as amount
FROM ds
GROUP BY key, zero

Avoid multiple GROUP BY over the trigger Data Source

As a rule of thumb, try to not make multiple GROUP BY queries over the same Data Source in the same Materialized View, most of the times this is an anti-pattern, for instance:

BAD: multiple GROUP BY over the trigger Data Source
SELECT
    product,
    count() as c
FROM (
    SELECT
        key,
        product,
        count() as orders
    FROM ds
    GROUP BY key, product
)
GROUP BY product

You’ll get a warning like this:

` Columns 'key, product' are present in the GROUP BY but not in the SELECT clause `

To fix this issue, make sure you don’t nest multiple GROUP BY clauses or user a GROUP BY inside a subquery:

GOOD: avoid multiple GROUP BY over the trigger Data Source
SELECT
    key,
    product,
    count() as orders
FROM ds
GROUP BY key, product