ClickHouse / ClickHouse/dbt-clickhouse
Inefficient Schema change check
- 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
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