ClickHouse / ClickHouse/dbt-clickhouse

Allow different dimensions when creating multiple materialized views

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

Description

We have a use case where we have a `CoalescingMergeTree` and insert data into this table from different materialized views. The idea is that each materialized view provides different columns to the final table. However, since dbt-clickhouse currently requires each mv to have the same columns, we must write a significant amount of boilerplate, which also makes our models more complicated to understand.

What we would like to be able to write is this:

```sql
{{
config(
materialized="materialized_view",
engine="CoalescingMergeTree",
order_by=["campaign_id"],
)
}}

-- responses_mv:begin
select
campaign_id,
is_accepted
from {{ source("rapid_service", "responses") }}
-- responses_mv:end
union all
-- sessions_mv:begin
select
campaign_id,
if(dsp_name = 'Kayzen', attributes['CAMPAIGN_ID'], null) as kayzen_campaign_id
from {{ source("session_service", "user_sessions") }}
-- sessions_mv:end
```

Currently, this is not supported, and we have to write the following:

```sql
-- responses_mv:begin
select
campaign_id,
is_accepted,
CAST(null, 'Nullable(String)') as kayzen_campaign_id
from {{ source("rapid_service", "responses") }}
-- responses_mv:end
union all
-- sessions_mv:begin
select
campaign_id,
if(dsp_name = 'Kayzen', attributes['CAMPAIGN_ID'], null) as kayzen_campaign_id,
CAST(null, 'Nullable(Bool)') as is_accepted
from {{ source("session_service", "user_sessions") }}
-- sessions_mv:end
```

I suggest that an MV should only insert values that it provides, but the table is created based on the combination of all MVs together.

I could also take a look at an implementation.

Contributor guide

Open the contributing guide

Research direction

Start at the materialized_view configuration and the responses_mv:begin/end and sessions_mv:begin/end markers shown in the issue; trace how the combined SELECT defines the target table and how each view's columns are validated. Reproduce the two-view example in a focused test, then verify that each materialized view may provide only its own columns while the target table includes the union of columns.

Written by the indexing model from the issue text.

Assessment

Tech stack
clickhouse, python, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.