ClickHouse / ClickHouse/dbt-clickhouse

Inefficient Schema change check

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

Description

### Describe the bug

The current dbt-clickhouse adapter implements a rather inefficient schema change check ([source code](https://github.com/ClickHouse/dbt-clickhouse/blob/main/dbt/include/clickhouse/macros/materializations/incremental/distributed_incremental.sql#L87)) when using `distributed_incremental` materialization with the `append` strategy. If the `on_schema_change` is not set to ignore, it wraps the model query into a `select * from {{model query}} limit 0` ([source code](https://github.com/ClickHouse/dbt-clickhouse/blob/main/dbt/adapters/clickhouse/httpclient.py#L32)) to infer the new schema.

However, the keyword `limit` in clickhouse doesn’t actually limit the amount of data read. This query could be therefore rather expensive. In our case, some queries read more than 1 TiB of data.

### Expected Behaviour

Ideally, the model query should only be run once, since the query could be rather complex and expensive. It should not be run "just to check the schema".

One potential solution could be to compare the schema of the `intermediate_relation` with that of the `target_table`. This might require refactoring the of macro, however the model query should hopefully run only once and used for both the schema check and the incremental data update.

Contributor guide

Open the contributing guide

Research direction

Start with dbt/include/clickhouse/macros/materializations/incremental/distributed_incremental.sql around the schema-change check and dbt/adapters/clickhouse/httpclient.py where the model query is wrapped with LIMIT 0. Trace how distributed_incremental with append and on_schema_change invokes these paths. Done means avoiding the expensive schema-check execution while preserving schema-change handling and the incremental update.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.