[ADAP-1014] [Bug] Contract enforcement method is not reliable about dbt-redshift

[dbeatty10]

Thanks for reporting this @jack-johnson-instapro !

For clarity, what does your example dbt model look like? Is it something like this? Or something else?

select null::bigint as col1 
union all
select col1
from test_bigint_varchar

Were you able to find any workarounds to avoid this issue within your own project?

[jack-johnson-instapro]

Hi @dbeatty10, our model looks very much like this, although I simplified it to isolate and clarify the issue. Basically we are unioning results which contain columns defined as NULL::BIGINT to other results coming from a table.

Currently we have not found a workaround for this issue, we could probably redesign our data model(s) to avoid this questionable union but we don't have bandwidth for this at this time. Right now, the solution we decided for is to not use contracts on this model.

[dbeatty10]

Thanks for using the most simple model possible for isolating and clarifying the issue 😎.

Did you try out this, by any chance? I'd be curious if flipping the order of the unions makes a difference one way or the other.

-- models/my_model.sql

select col1
from test_bigint_varchar
union all
select null::bigint as col1 

[jack-johnson-instapro]

That's a nice finding @dbeatty10, flipping the union order does make a difference! The snippet you suggested results in col1 being of type BIGINT and not VARCHAR. However I'm afraid we cannot use it as a workaround... The a-bit-less-simplified model we are using has NULL literals on both side of the union 🤦 something like:

select 
    col1,
    null::bigint as col2
from table1
union all
select 
    null::bigint as col1,
    col2
from table2

So unfortunately flipping the order results in either one or the other being of type VARCHAR...

[dbeatty10]

Okay!

What about adding an empty relation that has explicit values & types?

select 
    0::bigint as col1,
    0::bigint as col2
where 1=0
union all
select 
    col1,
    null::bigint as col2
from table1
union all
select 
    null::bigint as col1,
    col2
from table2

[dbeatty10]

@jack-johnson-instapro did you get a chance to check if this works for you or not?

I tried it out on my end, and it seemed to work.

[jack-johnson-instapro]

Sorry for the late response @dbeatty10. Yes this seems to function correctly as a workaround. Thanks for your help.

[dbeatty10]

Yes this seems to function correctly as a workaround. Thanks for your help.

Awesome news @jack-johnson-instapro !

Since we can't do anything about how Redshift handles null values but we have a workaround now, I'm going to close this as "not planned".

Workaround

If anyone else runs into this, our recommendation is union an empty relation like the following:

select 
    0::bigint as col1,
    0::bigint as col2
where 1=0
union all
select 
    col1,
    null::bigint as col2
from table1
union all
select 
    null::bigint as col1,
    col2
from table2