How to troubleshoot ELT errors resulting from zero-row tables incorrectly understanding their own data types in Redshift

When dealing with an unexpected SQL failure in ELT, if the error message looks something like:

"CASE types character varying and integer cannot be matched"

it might just be as it looks: the results of a CASE WHEN pattern are of mismatched data types, and need to be aligned.

However, if you are working with Redshift and the object is part of an Ursa package (or has otherwise been battle-tested to be reliable) then it's likely that you're running into a Redshift bug in which zero-row tables are typically unable to correctly understand their own data types. This bug can be neatly encapsulated via the following queries:

CREATE TABLE ursa.no_test_001 AS  
SELECT  
     null::integer AS an_int 
    ,null::numeric(28,9) AS a_float 
    ,null::date AS a_date 
    ,null::varchar(4000) AS a_string 
WHERE 1 = 0; 

SELECT column_name, data_type 
FROM information_schema.columns  
WHERE table_name LIKE 'no_test_001'; 

column_name     data_type 
a_float         numeric 
a_string        character varying 
a_date          character varying 
an_int          character varying 

Note how the integer and date fields are being incorrectly identified.

What this typically means for the error message you've just seen is that there is likely a zero-row upstream table which is incorrectly interpreting an integer field as a varchar field. This field is then used in a CASE WHEN pattern, as one integer result among other integer results, and Redshift is complaining that you're trying to match varchars with integers.

The most straightforward way to find the offending table is to use the external-link icon-button in each object screen in the erroring object's definition, and look in the ELT history for a zero-row build. The offending object might be more than one layer upstream, if the erroneously-identified field is passed through as a published field.

Happily, the remediation is fairly simple: change the "Database Object Type" of the object from a table to a view, then ELT that object (as well as any objects between that object and the erroring object). Now the erroring object should build without problem.