DWHPro Insights

Rounding Differences Between Teradata and Snowflake

By Roland Wenzlofsky ·

Rounding Differences Between Teradata and Snowflake

Rounding differences can cause migration mismatches, but distinguish the ROUND function from CAST and arithmetic scale reduction. Do not infer one operation’s behavior from another or from a database setting alone.

ROUND and CAST in Teradata

On Teradata Database 20.00.28.81, the audit returned 1.30 for ROUND(DECIMAL 1.25,1), 1.40 for ROUND(1.35,1), and -1.30 for ROUND(-1.25,1). CAST of 1.25 to DECIMAL(4,1) returned 1.2. Thus ROUND did not share the half-even behavior observed for this CAST. Check exact input types, precision, scale and settings in the source workload.

SELECT ROUND(CAST(1.25 AS DECIMAL(4,2)), 1) AS rounded_value,
       CAST(CAST(1.25 AS DECIMAL(4,2)) AS DECIMAL(4,1)) AS cast_value;

Half-away-from-zero and half-to-even

At an exact halfway value, half-away-from-zero maps 1.25 to 1.3 and -1.25 to -1.3 at one decimal place. Half-to-even maps 1.25 to 1.2 and 1.35 to 1.4. Bias depends on the input distribution and signs; rounding errors need not be universally upward.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Snowflake’s explicit rounding mode

Snowflake supports ROUND with HALF_TO_EVEN for supported fixed-point inputs, so a custom BANKERS_ROUND UDF is unnecessary. An unscaled NUMBER parameter can discard fractional precision and is unsuitable for that workaround. This Snowflake example is a documented syntax illustration, not a runtime test performed in the Teradata audit.

SELECT v,
       ROUND(v, 1) AS default_round,
       ROUND(v, 1, 'HALF_TO_EVEN') AS half_even_round
FROM (VALUES (1.25::NUMBER(4,2)), (1.35::NUMBER(4,2)),
             (-1.25::NUMBER(4,2))) AS samples(v);

Configuration and migration checks

RoundHalfwayMagUp is an administrative database setting relevant to documented rounding contexts; it is not a general replacement for checking ROUND semantics. The former MODIFY DBS example was not ordinary SQL and has been removed. No database setting was changed.

  • Inventory ROUND calls separately from CAST, implicit conversion and arithmetic scale reduction.
  • Use identical fixed-point input types and test positive and negative halfway values, nearby values, NULL and precision limits.
  • Choose Snowflake’s rounding mode to match the required business rule, then reconcile row-level results and totals.
  • Validate FLOAT separately: binary representation can change whether a value is an exact decimal halfway case.

Planning a Teradata-to-Snowflake migration?

Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.

Explore migration services

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam