UNION ALL Data Migration

Data migration considerations for UNION ALL.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

UNION ALL is a SQL operator that allows the combination of multiple resultsets. The syntax is the following:

query_expression_1 UNION [ ALL ] query_expression_2

For more information, please review the following Teradata documentation.

Column Size differences

Even though the operator is translated into the same operator in Snowflake, there could be detailed differences in functional equivalence. For example, the union of different columns which have different column sizes. Teradata does truncate the values when the first SELECT statement contains less space in the columns.

Teradata behavior

Same behavior in ANSI and TERA session modes.

For this example, the following input will show the Teradata behavior.

Teradata setup data

CREATE TABLE table1
(
col1 VARCHAR(20)
);

INSERT INTO table1 VALUES('value 1 abcdefghijk');
INSERT INTO table1 VALUES('value 2 abcdefghijk');

CREATE TABLE table2
(
col1 VARCHAR(10)
);

INSERT INTO table2 VALUES('t2 row 1 a');
INSERT INTO table2 VALUES('t2 row 2 a');
INSERT INTO table2 VALUES('t2 row 3 a');

Snowflake setup data

Case 1 - one single column: UNION ALL for a column varchar (20) over a column varchar (10)

For this case, the functional equivalence is the same

Teradata input

SELECT col1 FROM table1
UNION ALL
SELECT col1 FROM table2;

Teradata output

Snowflake input

SELECT
col1 FROM
table1
UNION ALL
SELECT
col1 FROM
table2;

Snowflake output

Case 2 - one single column: UNION ALL for a column varchar (10) over a column varchar (20)

In this case, the function equivalence is not the same.

The following case does not show functional equivalence in Snowflake. The column values should be truncated as in the Teradata sample.

Teradata input

SELECT col1 FROM table2
UNION ALL
SELECT col1 FROM table1;

Teradata output

Snowflake input

SELECT
col1 FROM
table2
UNION ALL
SELECT
col1 FROM
table1;

Snowflake output

Workaround to get the same functionality

In this case, the size of the column of the table2 is 10 and the table1 is 20. So, the size of the first column in the query should be the element to complete the LEFT() function used here. Review more information about the Snowflake LEFT function HERE.

Snowflake input

SELECT col1 FROM table2 -- size (10)
UNION ALL
SELECT LEFT(col1, 10) AS col1 FROM table1;

Snowflake output

Case 3 - multiple columns - same size by table: UNION ALL for columns varchar (20) over columns varchar (10)

For this case, it is required to set up new data as follows:

Teradata setup data

CREATE TABLE table3
(
col1 VARCHAR(20),
col2 VARCHAR(20)
);

INSERT INTO table3 VALUES('value 1 abcdefghijk', 'value 1 abcdefghijk');
INSERT INTO table3 VALUES('value 2 abcdefghijk', 'value 2 abcdefghijk');

CREATE TABLE table4
(
col1 VARCHAR(10),
col2 VARCHAR(10)
);

INSERT INTO table4 VALUES('t2 row 1 a', 't2 row 1 b');
INSERT INTO table4 VALUES('t2 row 2 a', 't2 row 2 b');
INSERT INTO table4 VALUES('t2 row 3 a', 't2 row 3 b');

Snowflake setup data

Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

Teradata input

select col1, col2 from table3
union all
select col1, col2 from table4;

Teradata output

Snowflake input

SELECT
col1, col2 FROM
table3
UNION ALL
SELECT
col1, col2 FROM
table4;

Snowflake output

Case 4 - multiple columns - same size by table: UNION ALL for columns varchar (10) over columns varchar (20)

In this case, the function equivalence is not the same.

Teradata input

select col1, col2 from table4
union all
select col1, col2 from table3;

Teradata output

Snowflake input

SELECT
col1, col2 FROM
table4
UNION ALL
SELECT
col1, col2 FROM
table3;

Snowflake output

Workaround to get the same functionality

Apply the column size to the second SELECT on the columns to get the same functionality.

Snowflake input

SELECT col1, col2 FROM table4 -- size (10)
UNION ALL
SELECT LEFT(col1, 10) AS col1, LEFT(col2, 10) AS col2 FROM table3;

Snowflake output

Case 5 - multiple columns - different sizes by table: UNION ALL for columns varchar (10) over columns varchar (20)

For this case, it is required to set up new data as follows:

Teradata setup data

CREATE TABLE table5
(
col1 VARCHAR(20),
col2 VARCHAR(12)
);

INSERT INTO table5 VALUES('value 1 abcdefghijk', 'value 1 abcdefghijk');
INSERT INTO table5 VALUES('value 2 abcdefghijk', 'value 2 abcdefghijk');

CREATE TABLE table6
(
col1 VARCHAR(10),
col2 VARCHAR(5)
);

INSERT INTO table6 VALUES('t2 row 1 a', 't2 row 1 b');
INSERT INTO table6 VALUES('t2 row 2 a', 't2 row 2 b');
INSERT INTO table6 VALUES('t2 row 3 a', 't2 row 3 b');

Snowflake setup data

Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

Teradata input

select col1, col2 from table5
union all
select col1, col2 from table6;

Teradata output

Snowflake input

SELECT
col1, col2 FROM
table5
UNION ALL
SELECT
col1, col2 FROM
table6;

Snowflake output

Case 6 - multiple columns - different sizes by table: UNION ALL for columns varchar (20), varchar(10) over columns varchar (10), varchar(5)

In this case, the function equivalence is not the same.

Teradata input

select col1, col2 from table6
union all
select col1, col2 from table5;

Teradata output

Snowflake input

SELECT
col1, col2 FROM
table6
UNION ALL
SELECT
col1, col2 FROM
table5;

Snowflake output

Workaround to get the same functionality

The column with the smallest size from the first SELECT is used to determine the size of the columns from the second SELECT.

Snowflake input

SELECT
col1, col2 FROM
table6
UNION ALL
SELECT
LEFT(col1, 5) as col1, LEFT(col2, 5) AS col2 FROM
table5;

Snowflake output

Case 7 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

Use the data set up here. Once the new tables and data are created, the following query can be evaluated.

For this case, the functional equivalence is the same

Teradata input

select col1 || col2 from table3
union all
select col1 || col2 from table4;

Teradata output

Snowflake input

SELECT
col1 || col2 FROM
table3
UNION ALL
SELECT
col1 || col2 FROM
table4;

Snowflake output

Case 8 - multiple columns expression - different sizes by table: UNION ALL for columns varchar (20), varchar(20) over columns varchar (10), varchar(10)

This case has functional differences.

Teradata input

select col1 || col2 from table4
union all
select col1 || col2 from table3;

Teradata output

Snowflake input

SELECT
col1 || col2 FROM
table4
UNION ALL
SELECT
col1 || col2 FROM
table3;

Snowflake output

Workaround to get the same functionality

The sum of the column sizes of the less big column should be used in the LEFT function. For example, the less big column is varchar(10), so the limit of the LEFT function should be 20 (10 + 10).

The sum of the first SELECT if this is less big, it would be used for the truncation of the values.

Snowflake input

SELECT
col1 || col2 FROM
table4
UNION ALL
SELECT
LEFT(col1 || col2, 20) FROM
table3;

Snowflake output

Other considerations about column size differences

• CHAR and VARCHAR behave the same.

• Number columns may behave differently. The numbers cannot be truncated, so there is an overflow in the Teradata environment. So, this is not applied to these data types. Review the following example:

-- Teradata number sample 
CREATE TABLE table11
(
col1 NUMBER(2)
);

INSERT INTO table11 VALUES(10);
INSERT INTO table11 VALUES(10);

CREATE TABLE table12
(
col1 NUMBER(1)
);

INSERT INTO table12 VALUES(1);
INSERT INTO table12 VALUES(1);
INSERT INTO table12 VALUES(1);

-- ERROR!  Overflow occurred when computing an expression involving table11.col1
SELECT col1 FROM table12
UNION ALL
SELECT col1 FROM table11;