ClickHouse / ClickHouse/dbt-clickhouse

use EMPTY statement in refreshable MVs

Open
#465 0 comments 1 reaction 0 assignees View on GitHub
enhancement
Dominant language
Python
Stars
362
Forks
177
Avg merge
2d 10h
Merged PRs (30d)
8

Description

**Is your feature request related to a problem? Please describe.**
Currently, the `EMPTY` statement ([https://clickhouse.com/docs/sql-reference/statements/create/view#refreshable-materialized-view](https://clickhouse.com/docs/sql-reference/statements/create/view#refreshable-materialized-view)) is not used in the dbt-clickhouse materialized views macros. So when the refreshable view is created, a first refresh will instantly occured.
It's problematic in dbt because this behaviour is redundant with the 'catchup' statement.
So currently it's problematic in both case:
- Let's assume I want to create a refreshable views (append=True) with catchup=False because I don't want historical data --> At t=0, I'm going to have historical data in my destination table because of the first refresh
- Let's assume I want to create a refreshable views (append=True) with catchup=True because I want historical data --> At t=0, I'm going to have historical data twice (2 x historical data) because of first refresh + catchup

**Describe the solution you'd like**
By adding an empty statement in the `clickhouse__create_mv` macro.
```
{% macro clickhouse__create_mv(mv_relation, target_relation, cluster_clause, refreshable_clause, view_sql) -%}
{% call statement('create existing mv: ' + mv_relation.name) -%}
create materialized view if not exists {{ mv_relation }} {{ cluster_clause }}
{{ refreshable_clause }}
to {{ target_relation }}
{% if refreshable_clause %}
empty
{% endif %}
as {{ view_sql }}
{% endcall %}
{%- endmacro %}
```
It's working in my case, but I'm am not sure if there is maybe side effects because of this modification.

Contributor guide

Open the contributing guide

Research direction

Start by locating the clickhouse__create_mv macro and inspect how refreshable_clause is assembled. Verify the proposed EMPTY placement against ClickHouse refreshable materialized view syntax, then test both catchup=False and catchup=True scenarios to confirm that the initial refresh no longer duplicates or adds historical data unexpectedly.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Feature
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.