Cannot create Refreshable Materialized View according to the documentation

[LPauzies]

Describe the bug

I'm under dbt-clickhouse 1.9.1

Using this model, I should be able to create a refreshable materialized view :

{{  config(
        materialized='materialized_view',
        engine='AggregatingMergeTree()',
        refreshable={
            "interval": "EVERY 1 DAY",
            "randomize": "10 MINUTE"
        },
        order_by='(cleaned_name, city)'
    )
}}

with
    users as (
        select id, name, city, age
        from {{ ref('mat_users') }}
    )

select
    coalesce(name, '') as cleaned_name,
    city,
    countState() as count,
    avgState(age) as average_age
from users
group by name, city

The equivalent in SQL should be :

CREATE MATERIALIZED VIEW mv_agg ON CLUSTER '{cluster}'
REFRESH EVERY 1 DAY RANDOMIZE FOR 10 MINUTE
(
    cleaned_name String,
    city         String,
    count_state AggregateFunction(count, Int64),
    average_age_state AggregateFunction(avg, Decimal(10, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (cleaned_name, city)
AS
(
    with
    users as (
        select id, name, city, age
        from {{ ref('mat_users') }}
    )

select
    cleaned_name,
    city,
    countState() as count,
    avgState(age) as average_age
from users
group by name, city
);

Expected behaviour

I expect to have a materialized view that can be retrieved using :

SELECT *
FROM system.view_refreshes
WHERE view = 'mv_agg'

But nothing appears...

Instead I have two elements : mv_agg_mv and mv_agg.

The first one is a classic MV at the sense of ClickHouse (insert trigger) while the other is a table with the correct engine. But this is not what I want... I would want to avoid multiple entities for nothing, while refreshing a simple MV is sufficient...

How would you achieve this in a DBT ClickHouse context ?

Configuration

Environment

• dbt version: 1.9.8
• dbt-clickhouse version: 1.9.1
• clickhouse-driver version (if using native): 0.29
• Python version: 3.12
• Operating system: Ubuntu 22

ClickHouse server

• ClickHouse Server version: 25.3.2.39

[koletzilla]

Hi @LPauzies! As far as I can see from your description, this is the expected behaviour. We have documented it here: https://github.com/ClickHouse/dbt-clickhouse?tab=readme-ov-file#materialized-views-experimental

A materialized_view materialization should be a SELECT from an existing (source) table. The adapter will create a target table with the model name and a ClickHouse MATERIALIZED VIEW with the name <model_name>_mv.

This is similar to what ClickHouse implements when you send the query you shared. ClickHouse also creates two resources: one for the materialized view, and another to store the data (usually called .inner. ...). The dbt connector uses the TO variant of CREATE MATERIALIZED VIEW, so instead of having a "hidden" resource to store the data, it makes it explicit by following the name of the model, so you can reference it from other models without problems.

Currently, we don't support a different way to create Materialized Views. Would this other way work for you?

[LPauzies]

Understood, thank you. But what about the refresh in this case ? It means DBT will always drop and recreate the MV + the MergeTree associated instead of just "Refresh". It's a bit weird of how it is designed in ClickHouse vs how it is implemented with DBT ClickHouse. The Refreshable MV statuses are stored inside their own system table and I expected to have the same logic as the CH documentation.

As the model will not change and we will need to trigger only the refresh by hand, I guess we should use a classic migration with a migration tool over DBT for this kind of task.

It would be cool to have this kind of way to do MV implemented :

CREATE MATERIALIZED VIEW mv_agg ON CLUSTER '{cluster}'
REFRESH EVERY 1 DAY RANDOMIZE FOR 10 MINUTE
(
    cleaned_name String,
    city         String,
    count_state AggregateFunction(count, Int64),
    average_age_state AggregateFunction(avg, Decimal(10, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (cleaned_name, city)
AS
(
    with
    users as (
        select id, name, city, age
        from {{ ref('mat_users') }}
    )

select
    cleaned_name,
    city,
    countState() as count,
    avgState(age) as average_age
from users
group by name, city
);

[koletzilla]

Yes, sorry about that. It's in our plans to add more ways to create Materialized views apart from the current (with the TO target_table added). I can't give you dates, but we hope to start working on it soon.

As the model will not change and we will need to trigger only the refresh by hand

Can you help me understand deeper what do you mean with trigger only the refresh by hand? The MV that is created is a REFRESH one so it will not act as an insert trigger, but a refresh one. It will work in the same way as the one created with the SQL you defined but in the case of the DBT connector, storing the data in a different table.

In fact if you execute a SHOW CREATE TABLE mv_agg_mv you can see that the SQL used to create the MV is practically the same as the one you shared.

[BentsiLeviav]

@LPauzies

Given that you create a refreshable MV model in dbt named X, you are supposed to have X as the target table, and X_mv as the MV inserting data into this X target table.

If you add the refreshable config, you will have in X_mv a refreshable caluse.

Can you share the show create table result of the created MV and the table?