Table 1
ID value DataType
001 A VARCHAR(10)
002 A VARCHAR(10)
003 B VARCHAR(10)
004 C VARCHAR(10)
Table 1
ID value DataType
001 A VARCHAR(100)
002 A VARCHAR(100)
003 B VARCHAR(100)
004 C VARCHAR(100)
Will the Union in Snowflake be safe, if the datatype is same but the length, scale and precision differs between two tables?
You don't have to worry about string lengths, but you do have to watch out for numeric scale and precision when doing unions. Here's an example:
create or replace table TABLE_A(MY_NUM number(38,0));
create or replace table TABLE_B(MY_NUM number(38,37));
insert into TABLE_A (MY_NUM) values (12345);
insert into TABLE_B (MY_NUM) values (1.23456789);
select * from TABLE_A
union all
select * from TABLE_B
Running this will give you the error:
Number out of representable range: type FIXEDSB16{nullable}, value 12345
In this case, the optimizer can't win. If it casts that column to NUMBER(38,0) then the value in TABLE_B would be truncated silently. If it casts to NUMBER(38,37), then it cannot represent a number with more than one place before the decimal point as stored in TABLE_A.
If you make sure the optimizer can always find a way to reconcile the precisions and scales for the column, it will be safe. If there is a possibility that one table's column can hold a value that the other table cannot, it may not be safe. If there is a standoff situation like the example here where two or more tables have values other tables in the union cannot represent then it's unsafe.