ClickHouse / ClickHouse/dbt-clickhouse
use EMPTY statement in refreshable MVs
- 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
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