SnowConvert: Teradata¶

Calculation Precision¶

Calculations in Teradata round after every step based upon the data types in the operation. For decimal types, this will keep the larger precision used in the calculation. NUMBER data types will keep full precision. Since Snowflake stores everything as a number, it keeps full precision throughout the calculation which can result in different outcomes for any number type (decimal, integer, float…) other than NUMBER. This behavior is not modified through code conversion as it is typically not an intended desired result by the programmer.

     Teradata:         SELECT (1.00/28) \* 15.00 = 0.60

     Snowflake:       SELECT (1.00/28) \* 15.00 = 0.535710 = 0.54

Integer-Integer Division¶

Teradata performs a truncation or floor when dividing two integer values in, whereas Snowflake will ultimately perform a round operation. This scenario is accounted for in the automated code conversion by adding a TRUNC statement wherever this happens.

     Teradata:         SELECT (5/3) = 1

     Snowflake:       SELECT (5/3) = 1.6666666 = 2

     Converted Snowflake:  SELECT TRUNC(5/3) = 1

Banker Rounding¶

Teradata has the option to use Banker rounding by setting the parameter ROUNDHALFWAYMAGUP while Snowflake only uses normal rounding.

Decimal to Integer Conversion¶

Teradata truncates the decimal values while Snowflake rounds to the nearest integer. This is accounted for in the conversion by inserting a TRUNC statement.

Number without Precision/Scale¶

Number when defined without a scale/precision in Teradata allows for a flexible scale value for any given record from 0 to 38 provided the total precision never exceeds 38. Snowflake does not allow this and always has a fixed scale/precision. An example of numbers defined in a table this way:

CREATE MULTISET TABLE DATABASEXYZ.TABLE_NUMS
     (NUM_COL1 NUMBER(*),
      NUM_COL2 NUMBER,
      NUM_COL3 NUMBER(38,*));

In this table, the example of 2 values below that would not fit into a single Snowflake column but could be found in any of the columns shown in the table above in Teradata:

Value 1: 123,345,678,901,234,567,891,012.0123456789

Value 2: 123.12345678901234567890

These two values would require a fixed precision/scale of NUMBER(42, 20) which exceeds the maximum available in Snowflake precision of 38. Snowflake is currently developing the functionality for flexible precision/scale.

Truncation on INSERT for SQL DML Statements¶

Teradata will auto-truncate a string value upon insert if the string is too large to fit into the specified field. SnowConvert will convert fields on a like-to-like basis (ex: VARCHAR(20) -> VARCHAR(20)). If an ingestion process is relying on auto-truncation, that process will need to be adjusted manually to truncate the data using a LEFT() function. SnowConvert does not automatically add this as there are multiple implications to doing this across an entire code base.

Float Default Issue Example:¶

/* <sc-table> TABLE DUMMY.EXAMPLE </sc-table> */
/**** WARNING: SET TABLE FUNCTIONALITY NOT SUPPORTED ****/
CREATE TABLE DUMMY.PUBLIC.EXAMPLE (
LOGTYPE INTEGER,
OPERSEQ INTEGER DEFAULT 0,
RUNTIME FLOAT /**** ERROR: DEFAULT CURRENT_TIME NOT VALID FOR DATA TYPE ****/
);

Float Data Aggregation¶

Float data types are by definition approximations and as such different databases may aggregate differently due to how these approximations are handled at different points in the internal calculations of the database.

Join Elimination¶

Snowflake does not currently eliminate un-required joins in any SQL that is run. It executes the SQL assuming that all objects included will potentially affect the result sets. Teradata has built-in join elimination features by leveraging primary-foreign key relationships that are defined in the DDL. The primary reason for this is to help avoid improperly written queries and is rarely an issue where code has not been written to intentionally take advantage of this feature. If a design decision was taken in views or DML to purposefully exploit this feature, code conversion cannot address this and re-architecting some of that solution design may be required.

Using max() over (order by) and other non-rank-related window functions

SELECT DEPT_NM, SALARY ,DEPT_NO,
MAX(SALARY) OVER ( ORDER BY DEPT_NO  ) AS MAX_DEPT_SALARY
FROM TEST_WIN;

SELECT DEPT_NM, SALARY ,DEPT_NO,
MAX(SALARY) OVER ( ORDER BY DEPT_NO  ) AS MAX_DEPT_SALARY
FROM TEST_WIN;

SELECT DEPT_NM, SALARY ,DEPT_NO,
MAX(SALARY) OVER ( ORDER BY DEPT_NO RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_DEPT_SALARY
FROM TEST WIN;