Hi, quick question does anyone knows if is there a...
# replication-troubleshooting
a
Hi, quick question does anyone knows if is there already work in progress the support for
incremental
and
delete+insert
for Clickhouse as destionation from Airbyte? By delete+insert I mean this If I'm understand correctly this requires dbt-clickhouse@1.3.2 and I see in Airbyte's master branch =1.3.1> Also. How I can check in my Airbyte installation what version of dbt-clickhouse is installed? In what docker container should I check?
✅ 1
u
Andres, Clickhouse destination already supports incremental + dedup. It is using dbt-clickhouse 1.3.1 check the changelog for the destination connector https://docs.airbyte.com/integrations/destinations/clickhouse#changelog
a
We're using Clickhouse CLOUD and it fails with this error when is configured with
incremental+dedup
Copy code
:HTTPDriver for <https://zytq4zf64e.eu-central-1.aws.clickhouse.cloud:8443> returned response code 500
Code: 36. DB::Exception: ALTER UPDATE/ALTER DELETE statement with subquery may be nondeterministic, see allow_nondeterministic_mutations setting. (BAD_ARGUMENTS)
Also this document in Clickhouse docs explains that
incremental+dedup
is slow because it creates a full copy of the table before adding the delta of the changes https://clickhouse.com/docs/en/integrations/dbt/dbt-incremental-model#internals Down below in that same page explain that
delete_insert
is more efficient https://clickhouse.com/docs/en/integrations/dbt/dbt-incremental-model#deleteinsert-mode-experimental
For that I want to know if you know an ETA to make Airbyte compatible with
delete_insert
incremental sync mode
u
Andres, I find this Github issue https://github.com/airbytehq/airbyte/issues/15352 which maybe is related to normalization performance. I didn't find anything related to the
allow_nondeterministic_mutations
flag. There isn't any ETA in current Airbyte roadmap for Clickhouse destination, my recommendation is: export the normaization module https://docs.airbyte.com/operator-guides/transformation-and-normalization/transformations-with-sql and edit as your need to improve or achieve conditions to run in Clickhouse Cloud.
a
Wow! @Marcos Marx (Airbyte) thanks a lot! I'll take a look and come back to you
I opened a ticket on Clickhouse.cloud about this error:
Copy code
:HTTPDriver for https://[OUR_ID].clickhouse.cloud:8443 returned response code 500)
Code: 36. DB::Exception: ALTER UPDATE/ALTER DELETE statement with subquery may be nondeterministic, see allow_nondeterministic_mutations setting. (BAD_ARGUMENTS)
They responded this:
Hi Andres,
This is a known issue, the Airbyte integration do queries that are not supported by ClickHouse. I have open an escalation to the integration team to see if we could workaround this issue.
Maybe Airbyte documentation should put a note about connecting with Clickhouse.cloud and this limitation when you want to use
incremental+dedup
m
I created the issue https://github.com/airbytehq/airbyte/issues/21963 to improve our docs. Thanks for the suggestion
a
Super fast! Thanks
g
We (ClickHouse) are waiting for lightweight deletes to be "production ready" in ClickHouse Cloud before updating the Airbyte integration to use the dbt delete+insert strategy by default. I don't have an exact date, but it should be "soon". The non_deterministic mutation problem will require some research, because that is code that isn't normally executed by our dbt flows. We need to understand how Airbyte is triggering column updates and probably find a different approach.
a
Oh @Geoff Genz that sounds amazing! Could you be a more specific than "soon"? 😂 Are we talking days, weeks or months?
g
Unfortunately I can't be more specific -- when the ClickHouse engineer working on it says it's ready 🙂
a
Thanks for the info @Geoff Genz appreciate
g
It's also not a magic bullet -- the improvement is significant but that's still a lot of data (in many cases) to update