Closed smoothml closed 1 month ago
Thanks for reaching out @smoothml !
It sounds to me like the root cause is that dbt-clickhouse doesn't yet support dbt unit tests rather than this being a bug in dbt-core. So I'm going to close this issue in favor of you opening an issue in dbt-clickhouse instead.
Specifically, it looks like dbt-clickhouse will need to override this line within get_empty_schema_sql
:
{{ cast('null', col['data_type']) }} as {{ col_name }}{{ ", " if not loop.last }}
Thanks for getting back to me so quickly @dbeatty10. As you recommended, I have raised the issue with dbt-clickhouse
.
Hello @dbeatty10 , I think the problem is rather in this line, where the unit test boldly assumes that null could be an appropriate value. However, for not nullable data it's obviously not a great choice of a default value. In it's current implementation, that piece is also hard to override by an adapter as it is not an adapter specific macro.
One might tamper with safe_cast
macro, but if might interfere with a different context. It could make sense to create a separate adapter specific macro that allows the selection of an appropriate default value.
Thanks for identifying the relevant portion of the code @steffen030 ! 🤩
My belief is that null
is the best value here for a couple reasons:
It looks like the crux of the issue is that ClickHouse has some restrictions / differences in behavior as it relates to E131 that is preventing null
from being used in this way. That will naturally have some consequences for the adapter implementation for https://github.com/ClickHouse/dbt-clickhouse/issues/315, possibly including relatively complicated macro overrides.
If it turns out there is a macro abstraction that dbt should consider, we'd welcome a feature request.
Is this a new bug in dbt-core?
Current Behavior
When using Clickhouse it is common practice to not allows columns to be nullable so as to improve the efficiency of analytic-type queries. However, this is not compatible with how unit tests in DBT handle missing values and so results in an error such as the following:
Expected Behavior
If a column is not-nullable and a value not provided in the test input, the values used should be the appropriate non-null default. For example, Clickhouse would typically use 0 instead of
NULL
for a non-nullable numeric column.Steps To Reproduce
Consider the following example, based on the output of the
dbt init
command:Values for the column
foo
are omitted in the tests, with only theid
values provided. Runningdbt build
ordbt test
results in the error.Relevant log output
Environment
Which database adapter are you using with dbt?
other (mention it in "Additional Context")
Additional Context
We are using Clickhouse.
I previously brought this up in the Slack community and was directed here to raise an issue.