Rounding Differences Between Teradata and Snowflake

One of the smallest but most persistent sources of data mismatches during a Teradata-to-Snowflake migration comes from an unassuming function: ROUND().

While both systems follow ANSI SQL semantics, they differ in how they handle values that lie exactly halfway between two rounding candidates (i.e., when the digit after the rounding precision is 5).

---

Standard Rounding (Half-Up)

Snowflake uses the standard half-up rounding rule.

When the digit after the precision is 5 or greater, the number is always rounded away from zero.

Value	Rounded (1 decimal)
1.24	1.2
1.25	1.3
1.26	1.3

This method is simple and intuitive. However, when applied repeatedly over large datasets, it introduces a slight upward bias, as all halfway cases round in the same direction.

---

Banker’s Rounding (Half-to-Even)

Teradata’s default is Banker’s rounding, also known as half-to-even.

In this method, when the digit after the precision is exactly 5, the result is rounded toward the nearest even last digit. This minimizes cumulative bias in aggregates.

Value	Rounded (1 decimal)	Explanation
1.25	1.2	1.3 would make the last digit odd → round down
1.35	1.4	1.4 is even → round up
1.45	1.4	1.5 would be odd → round down

In practice, this produces slightly different results for .5 values only.

---

Teradata Configuration

The behavior can be controlled through the DBS parameter:

MODIFY DBS ROUNDHALFWAYMAGUP = TRUE | FALSE;

Setting	Behavior	Description
FALSE (default)	Banker’s rounding	Half-to-even
TRUE	Standard rounding	Half-up (like Snowflake)

Therefore:

• Default Teradata: 1.25 → 1.2
• Snowflake: 1.25 → 1.3

Unless this parameter was explicitly modified, most Teradata systems use Banker’s rounding.

---

Emulating Banker’s Rounding in Snowflake

Snowflake has no configuration parameter for rounding behavior.

However, equivalent results can be achieved with a short SQL function:

CREATE OR REPLACE FUNCTION BANKERS_ROUND(v NUMBER, s INT)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
  CASE
    WHEN ABS(v*POWER(10,s) - ROUND(v*POWER(10,s))) = 0.5
      THEN SIGN(v) *
           (CASE WHEN MOD(TRUNC(ABS(v*POWER(10,s))),2)=0
                   THEN TRUNC(ABS(v*POWER(10,s)))
                   ELSE TRUNC(ABS(v*POWER(10,s)))+1
            END) / POWER(10,s)
    ELSE ROUND(v, s)
  END
$$;

Example

SELECT
  v,
  ROUND(v,1) AS standard_round,
  BANKERS_ROUND(v,1) AS bankers_round
FROM VALUES (1.05),(1.25),(1.35),(1.45),(1.55);

v	Standard (Snowflake)	Banker’s (Teradata)
1.25	1.3	1.2
1.35	1.4	1.4
1.45	1.5	1.4

Only exact halfway values (.5) are affected.

---

When the Difference Matters

For most datasets, the difference is negligible.

However, in financial or statistical reporting systems—where consistency with historical Teradata behavior is mandatory—the distinction becomes important.

Value	Teradata	Snowflake	Same?
1.21	1.2	1.2	✅
1.27	1.3	1.3	✅
1.25	1.2	1.3	❌

If you rarely encounter .5 values, the two systems will behave identically.

---

Migration Guidelines

Aspect	Teradata	Snowflake	Action
Default behavior	Banker’s (half-to-even)	Standard (half-up)	Review .5 rounding logic
Configurable	✅ via ROUNDHALFWAYMAGUP	❌	Use UDF if equivalence required
1.25 result	1.2	1.3	Replace ROUND() with BANKERS_ROUND()
Bias tendency	Neutral	Slight upward	Validate totals and aggregates

Checklist:

1. Check the value of ROUNDHALFWAYMAGUP on your Teradata system.
2. If it’s FALSE, implement the BANKERS_ROUND() UDF in Snowflake.
3. If it’s TRUE, Snowflake’s native rounding matches.
4. Validate all .5 boundaries before sign-off.

---

Conclusion

Rounding differences rarely cause major migration issues—but they can quietly distort totals and audit reports if overlooked.

Understanding how Teradata’s half-to-even logic differs from Snowflake’s half-up approach ensures mathematical consistency and protects data integrity during migration.