https://linen.dev logo
Join Slack
Powered by
anomaly_scores as ( select md5_b...
# ask-elementary-ai
m
anomaly_scores as ( select md5_binary(cast(coalesce(cast(metric_id as varchar), '') || '-' || coalesce(cast(cast('9cd25e8f-7dac-4486-b617-a3cab4e6c423.test.lnd_teamcenter.elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated.d791354bce' as varchar) as varchar), '') as varchar)) as id, metric_id, cast('9cd25e8f-7dac-4486-b617-a3cab4e6c423.test.lnd_teamcenter.elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated.d791354bce' as varchar) as test_execution_id, cast('test.lnd_teamcenter.elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated.d791354bce' as varchar) as test_unique_id, cast ( convert_timezone('UTC', current_timestamp::timestamp )::timestamp as timestamp) as detected_at, full_table_name, column_name, metric_name, case when training_stddev is null then null when training_stddev = 0 then 0 else (metric_value - training_avg) / (training_stddev) end as anomaly_score, 3 as anomaly_score_threshold, source_value as anomalous_value, cast(bucket_start as timestamp) as bucket_start, cast(bucket_end as timestamp) as bucket_end, bucket_seasonality, metric_value, case when training_stddev is null then null when ((-1) * 3 * training_stddev + training_avg) > 0 or metric_name in ('min', 'max', 'average', 'standard_deviation', 'variance', 'sum') then ((-1) * 3 * training_stddev + training_avg) else 0 end as min_metric_value, case when training_stddev is null then null else (3 * training_stddev + training_avg) end as max_metric_value, training_avg, training_stddev, training_set_size, cast(training_start as timestamp) as training_start, cast(training_end as timestamp) as training_end, dimension, dimension_value from time_window_aggregation where metric_value is not null and training_avg is not null ) select * from anomaly_scores ) /* {"app": "dbt", "dbt_version": "2025.11.4+d8fa9da", "profile_name": "user", "target_name": "default", "node_id": "test.lnd_teamcenter.elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated.d791354bce"} */; 154654 Snowflake adapter: Snowflake query id: 01c041b2-0108-09f6-005d-410e9004db6b 154654 Snowflake adapter: Snowflake error: 001790 (42601): SQL compilation error: inconsistent data type for result columns for set operator input branches, expected BINARY(16), got VARCHAR(16777216) 154654 Database Error in test elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated (models/transform/digital_industries/c_parts/_c_parts_models.yml) 001790 (42601): SQL compilation error: inconsistent data type for result columns for set operator input branches, expected BINARY(16), got VARCHAR(16777216) 154654 1 of 3 ERROR elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated [[31mERROR[0m in 2.06s] 154654 Finished running node test.lnd_teamcenter.elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated.d791354bce 154658 [31mFailure in test elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated (models/transform/digital_industries/c_parts/_c_parts_models.yml)[0m 154658 Database Error in test elementary_freshness_anomalies_cp_tc_manufacturer_day__7__date_sdc_updated (models/transform/digital_industries/c_parts/_c_parts_models.yml) 001790 (42601): SQL compilation error: inconsistent data t
k
A message from kapa.ai
The error you're encountering:
Copy code
SQL compilation error: inconsistent data type for result columns for set operator input branches, expected BINARY(16), got VARCHAR(16777216)
is caused by a mismatch in the data types of the 
id
column when performing a 
UNION
or similar set operation in Snowflake. Specifically, one branch is producing a 
BINARY(16)
(from 
md5_binary
), while another is producing a 
VARCHAR
. This issue is known to occur in Elementary when the macro or function used to generate the 
id
column differs in type between different parts of the query. For example, if one part uses 
md5_binary()
(which returns a binary type) and another uses 
md5()
or a string-based hash (which returns a varchar), Snowflake will throw this error during a union. A similar issue was reported in the Elementary community, where a hash macro was overridden by another package, causing the type to change from 
varchar
to 
binary
. The solution in that case was to ensure that the correct macro is used and, if necessary, adjust the dispatch order so that Elementary's version takes precedence. Here’s the relevant discussion: > "Another package is overriding the hash() macro, making it binary. I will try to solve it with dispatch order." [Snowflake error: union the ID column, once as binary and once as varchar](https://slack.com/archives/C02CTC89LAX/p1735815549097269) How to resolve: - Ensure that all branches of your union produce the 
id
column with the same data type. For Elementary, this is typically 
VARCHAR
. - Check if any other dbt packages in your project override the 
hash()
or 
generate_surrogate_key()
macros, and if so, use dbt's [dispatch mechanism](https://docs.getdbt.com/docs/build/custom-macros#dispatch) to ensure Elementary's implementation is used. - If you are using 
md5_binary
, consider switching to 
md5
or casting the result to 
VARCHAR
to match the expected type. Example fix: Replace
Copy code
sql md5_binary(...)
with
Copy code
sql md5(...)
or
Copy code
sql cast(md5_binary(...) as varchar)
in all relevant places, so the type is consistent. If you continue to have issues, review your dbt project for any custom or overridden macros that might affect hashing or surrogate key generation, and ensure consistency across all test and model definitions. If you need more specific guidance, please provide the full SQL for all branches of the union, and check for any custom macro overrides in your dbt project or dependencies.
34 Views
Open in Slack
PreviousNext
Powered by