search

ClickHouse / dbt-clickhouse

The Clickhouse plugin for dbt (data build tool)
Apache License 2.0
253 stars 113 forks source link

Lost data when updating materialized view #383

Open the4thamigo-uk opened 1 week ago

the4thamigo-uk commented 1 week ago

Describe the bug

Steps to reproduce

  1. Establish a high-volume realtime data feed into a CH table A
  2. Set up a materialized view writing data to table B
  3. Run dbt to update the materialized view whilst the data feed is running

Expected behaviour

The update of the materialized view should be atomic, but data is lost in between the drop and the create.

For altering the SQL we should use the atomic ALTER TABLE ... MODIFY QUERY https://clickhouse.com/docs/en/sql-reference/statements/alter/view

the4thamigo-uk commented 1 day ago

@BentsiLeviav I would like to implement a solution for this. I think we need to try to perform the ALTER statement, but if it fails, then to fallback to the DROP and CREATE approach. However, the problem I have is that there doesnt seem to be a way in jinja or dbt to catch the exception if the ALTER fails. I think maybe the only way is to add something to the adapter? Do you have any ideas/advice?