MSCEWI2051

Teradata BYTES function results differs from Snowflake LENGTH function for byte columns

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Since Teradata byte datatype has a fixed length, BYTES function will always count the trailing zeros inserted to fit smaller byte type values into the column, returning the size of the column instead of the size of the value inserted originally. However, Snowflake binary type has variable size, meaning that the LENGTH function will always return the size of the inserted values. Take the following code as an example:

Teradata:

create table exampleTable(
	bytecol byte(10)
);

insert into exampleTable values ('2B'XB);

select bytes(bytecol) from exampleTable; 
-- Will return 10, the size of bytecol

Equivalent code in Snowflake:

create table exampleTable(
	bytecol binary
);

insert into exampleTable values (to_binary('2B'));

select length(bytecol) from exampleTable; 
// Will return 1, the size of the value 2B

Example code:

Input code:

create table sampleTable(
    byteColumn byte(10),
    varbyteColumn varbyte(15)
);

select bytes(byteColumn), bytes(varbyteColumn) from sampleTable;

Output code:

CREATE TABLE PUBLIC.sampleTable (
byteColumn BINARY,
varbyteColumn BINARY(15)
);

SELECT
LENGTH(byteColumn) /*** MSC-WARNING - MSCEWI2051 - TERADATA BYTES FUNCTION RESULTS DIFFER FROM SNOWFLAKE LENGTH FUNCTION FOR BYTE TYPE COLUMNS ***/,
LENGTH(varbyteColumn)
FROM PUBLIC.sampleTable

Recommendations

• Analyze the use given to the BYTES function results, maybe the Snowflake LENGTH function behavior was the one desired from the start and no changes are required.

• If you need more support, you can email us at [email protected]