HOW TO: Round Numeric Value With Snowflake ROUND (2024)

Snowflake provides various numeric functions that allow handling various operations like ROUNDing, TRUNCation, CEILing—and FLOORing on numeric values or expressions. One of the most commonly used numeric functions is the Snowflake ROUND() function, which is used to round the decimal portion of a numeric value/expression.

In this article, we will provide an in-depth overview of the Snowflake ROUND() function. We will cover everything from syntax, arguments, supported data types, practical real-world examples, limitations, best practices—and so much more!!

What Is ROUND() function in Snowflake?

Snowflake ROUND() is a numeric function that allows you to round or truncate the decimal portion of a numeric value or expression. It works just like standard rounding rules. If the decimal is 0.5 or higher, it rounds up. If the decimal is lower than 0.5, it rounds down, which allows you to clean up numeric values by truncating extra decimal places. It's an easy way to control precision and make your numbers tidier.

Snowflake ROUND() function has several different use cases, such as:

• Formatting numeric data for readability and consistency
• Preparing data for analysis as well as reporting
• Reducing storage needs and improving performance
• Avoiding errors or inaccuracies due to floating-point representation

Now that we've covered the basics of Snowflake ROUND() function, it's time to look under the hood. In the next section, we'll dive deeper into the technical details of Snowflake ROUND() function, exploring its syntax, arguments, data types—and how it handles those tricky null and negative values. But for now, remember this: The Snowflake ROUND() function is an easy way to clean up numbers with too many decimal places. It rounds values to the precision you specify, making your reports, analysis, and calculations much cleaner and easier to read. This function takes care of trimming all those extra decimals for you.

How Does Snowflake ROUND() Work?

Snowflake ROUND() function works by applying the standard rounding rules to the decimal portion of a numeric value/expression. Snowflake ROUND() function takes three arguments:

• Input expression
• Scale expression
• Rounding mode

The function returns a rounded value based on these arguments.

The full syntax of Snowflake ROUND() function is as follows:

ROUND( <input_expr> [ , <scale_expr> [ , <rounding_mode> ] ] )

OR

ROUND( EXPR => <input_expr> , SCALE => <scale_expr> [ , ROUNDING_MODE => <rounding_mode> ])

Let’s look at each argument in detail:

• <input_expr> OR EXPR => <input_expr>: Input expression is the value or expression to operate on. The data type should be one of the numeric data types, such as FLOAT or NUMBER.
• <scale_expr> OR SCALE => <scale_expr>: Scale expression is the number of digits the output should include after the decimal point. The expression should evaluate to an integer from -38 to +38. The default scale expression is 0, meaning that the function removes all digits after the decimal point.
• <rounding_mode> OR ROUNDING_MODE => <rounding_mode>: Rounding mode is the rounding mode to use. You can specify one of the following values:
• HALF_AWAY_FROM_ZERO
• HALF_TO_EVEN (also known as banker’s rounding)

What Is the Default Rounding Mode?

The default rounding mode is HALF_AWAY_FROM_ZERO, which means that the function rounds the value half away from zero. For example, 0.5 is rounded to 1.0 and -0.5 is rounded to -1.0. The other rounding mode is ‘HALF_TO_EVEN, which means that the function rounds the value half to even. This mode is also known as the banker’s rounding, as it minimizes the rounding error in repeated calculations.

Data Type Supported by Snowflake ROUND() function

Snowflake ROUND() function supports two numeric data types:

• NUMBER : Rounds to a NUMBER with same precision and scale, unless scale exceeds input precision
• FLOAT: Rounds to a FLOAT with same precision, returns NULL on error
• INT: Rounds to an INT by truncating decimal portion
• DECIMAL: Rounds to a DECIMAL with same precision and scale

The data type of the return value matches the input data type. One exception is that very large DECIMAL values may convert to FLOAT if the rounded value overflows the scale.

The data type of the input expression determines the data type of the output value. If the input expression is a FLOAT, Snowflake ROUND() function returns a FLOAT.

For example,

SELECT ROUND(12.34567::float, 3);

If the input expression is a NUMBER, Snowflake ROUND() function returns a NUMBER.

For example,

SELECT ROUND(1234567::number, 1);

The precision and scale of the output value depend on the scale expression and the rounding mode.

How Snowflake ROUND Handles Null and Negative Values?

Snowflake ROUND() function can handle both null and negative values as input expressions. But, there are some differences in how the function treats these values and how they affect the output.

Null values:

If either the input expression or the scale expression is NULL, then the result is NULL, which means that the Snowflake ROUND() function does not perform any rounding or truncation on null values.

For example,

SELECT ROUND(null, 2);

SELECT ROUND(2, null);

Negative values:

If the input expression is a negative number, then Snowflake ROUND() function rounds it down or up depending on the scale expression and the rounding mode, which means that the rounded value is further from zero than the original value.

For example,

SELECT ROUND(-1.23666, 3);

Note that if scale_expr is negative, it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, the result is a multiple of 100.

SELECT ROUND(100.23666, -2);

Practical Examples of Snowflake ROUND() function

Now in this section, we will look at some of the practical examples of using the Snowflake ROUND() function in different scenarios.

Example 1—Basic Rounding to Integer

First, we will begin with the basic example of rounding to a whole number. For that, we will create a simple “users” table that we'll use throughout this initial example.

CREATE TABLE users (
  user_id INT, 
  name VARCHAR(50),
  age DECIMAL(3,1), 
  weight DECIMAL(4,1)
);

INSERT INTO users VALUES
  (1, 'Elon', 23.4, 55.6),
  (2, 'Jeff', 31.7, 78.9),
  (3, 'Larry', 28.3, 66.4), 
  (4, 'Chaos', 35.9, 72.3),
  (5, 'Genius', 26.5, 60.2);

Now that we have the users table ready, let's begin with the basic example of the Snowflake ROUND() function. The simplest way to use the ROUND() function is to round a numeric value or expression to the nearest integer. This means the decimal part of the input will be either truncated or incremented based on the value of the first decimal digit. If the first decimal digit is less than 5, the input will be truncated. If the first decimal digit is equal to or greater than 5, the input will be increased by 1. Let's explore how we can utilize the Snowflake ROUND() function to round the age and weight columns to the nearest integer. The query and the output are as follows:

SELECT
  user_id,
  name,
  ROUND(age) AS age,
  ROUND(weight) AS weight
FROM users;

As you can see, Snowflake ROUND() function has rounded the values to the nearest integer. For example, 23.4 is rounded to 23, 55.6 is rounded to 56, and so on!

Example 2—Rounding With Positive and Negative Scales

Snowflake ROUND() function can also take a second argument, called the scale, that specifies the number of decimal places to round to. The scale can be either positive or negative. A positive scale means that the input will be rounded to that many decimal places. A negative scale means that the input will be rounded to that many places before the decimal point.

For example, suppose we have the same table as before, but we want to round the age, and weight columns to different scales. The query and the output are as follows:

SELECT
  user_id,
  name,
  ROUND(age, 1) AS age,
  ROUND(weight, -2) AS weight
FROM users;

As you can see, Snowflake ROUND() function has rounded the values to different scales. For example, 23.4 is rounded to 23.4 with a scale of 1, and 55.6 is rounded to 100 with a scale of -2.

Note that rounding with a positive scale can be useful for preserving some precision or formatting the output for display purposes, whereas rounding with a negative scale can be useful for grouping values by larger units or reducing the storage size of the data.

Example 3—Rounding With Rounding Mode

Snowflake ROUND() function can also take a third argument, called the rounding mode, that specifies how to handle the cases where the input is exactly halfway between two possible rounded values. The rounding mode can be either HALF_AWAY_FROM_ZERO or

HALF_TO_EVEN. The default rounding mode is HALF_AWAY_FROM_ZERO, which means that the input will be rounded up if it is exactly halfway. The HALF_TO_EVEN rounding mode means that the input will be rounded to the nearest even number if it is exactly halfway.

For example, suppose we have the same table as before, but we want to round the age, and weight columns without any scale and with different rounding modes. The query and the output are as follows:

1) Using HALF_AWAY_FROM_ZERO as rounding mode:

SELECT
  user_id,
  name,
  ROUND(age, 0, 'HALF_AWAY_FROM_ZERO') AS AGE_HALF_AWAY_FROM_ZERO,
  ROUND(weight, 0, 'HALF_AWAY_FROM_ZERO') AS WEIGHT_HALF_AWAY_FROM_ZERO
FROM users;

2) Using HALF_TO_EVEN as rounding mode:

SELECT
  user_id,
  name,
  ROUND(age, 0, 'HALF_TO_EVEN') AS AGE_HALF_TO_EVEN,
  ROUND(weight, 0, 'HALF_TO_EVEN') AS WEIGHT_HALF_TO_EVEN
FROM users;

When to Use Snowflake ROUND() function?

There are many scenarios where the Snowflake ROUND() function can be useful:

1) Formatting Numeric Data for Readability

Sometimes, the numeric data may have too many decimal places that make it hard to read or understand. For instance, if you have a column that stores the prices of products, you may want to round them to two decimal places for report/analysis purposes. You can use the Snowflake ROUND() function with a positive scale to achieve this.

2) Aggregating Data

Rounding decimal values is often necessary when aggregating data:

• Rounding averaged survey responses
• Rounding summary statistics like mean and percentiles
• Reducing precision of aggregated monetary values

3) Reducing Storage

Numeric data may have too high precision that is not necessary/relevant for the business logic. For instance, if you have a column that stores the distances between two locations, you may not need to store them with more than four decimal places. You can use Snowflake ROUND() function with a positive scale to reduce the storage size of the data.

4) Avoiding Rounding Errors

Sometimes, the numeric data may have rounding errors due to the limitations of the floating point representation. Let’s say, that if you have a column that stores the results of some calculations, you may encounter some unexpected values due to the loss of precision. You can use the Snowflake ROUND() function with a specific rounding mode like HALF_AWAY_FROM_ZERO or HALF_TO_EVEN to avoid this.

What Is the Difference Between Snowflake CEIL and Snowflake ROUND?

Snowflake CEIL and Snowflake ROUND() functions are both used to round or truncate the decimal portion of a numeric value or expression. But, they have some differences in how they perform the rounding operation and what arguments they accept.

In this section, we will compare and contrast the CEIL and Snowflake ROUND() functions in Snowflake. Here are the differences between Snowflake CEIL and ROUND().

What Is the Difference Between Snowflake TRUNCATE and Snowflake ROUND?

Snowflake TRUNCATE and ROUND() functions are both used to round or truncate the decimal portion of a numeric value or expression. But, they differ in how they perform the operation and the arguments they accept. In this part, we will compare and contrast Snowflake TRUNCATE and Snowflake ROUND() functions in Snowflake. Here are the differences between Snowflake TRUNCATE and ROUND().

What Is the Difference Between Snowflake FLOOR and Snowflake ROUND?

Finally, in this section, we will compare and contrast the Snowflake FLOOR and Snowflake ROUND() functions. Here, we'll explore the differences between Snowflake FLOOR and Snowflake ROUND().

Limitations of Snowflake ROUND() function

Now, let's move to the final section of this article. Here, we'll discuss some of the limitations of the Snowflake ROUND() function.

1) Precision Loss

Snowflake ROUND() function may cause some loss of precision or information when rounding the numeric data. For example, if you round a number like 3.14159 to two decimal places, you will lose some digits that may be important for some calculations or comparisons. Therefore, always be very careful about the scale and the rounding mode that you use, and only round the data when it is necessary or appropriate.

To avoid or minimize the precision loss, you can use the following tips:

• Use a higher precision data type for your numeric data.
• Use data types that can store more digits and avoid floating point errors that may affect the rounding results.
• Use a larger scale for your numeric data, such as 4 or 6, instead of 2 or 0. This will preserve more decimal places and reduce the rounding errors.

2) Lack of Flexibility for Custom Rounding Rules/Mode

Snowflake ROUND() function only supports two rounding modes: HALF_AWAY_FROM_ZERO or the HALF_TO_EVEN mode. However, there may be some situations where you need to apply some custom rounding rules, such as rounding up, rounding down, or rounding to the nearest multiple of a certain number. This variance may lead to confusion or inconsistency when comparing or aggregating the rounded data.

To avoid or minimize the rounding mode inconsistencies, you can be consistent about the rounding mode that you use, and document it. You should use the same rounding mode for all your numeric data, and explain the rationale behind your choice.

3) Integration Issues With Other Snowflake Functions

Another limitation of the Snowflake ROUND function is that it may not work well with some other Snowflake functions that expect or return numeric data. For example, if you use the ROUND function with the SUM function, you may get incorrect results due to the order of operations. Therefore, you should be careful about the order and the scope of Snowflake ROUND function, and test the results before using them.

To avoid or resolve the integration issues with other Snowflake functions, you can use the following tips:

• Use subqueries or CTEs to isolate the rounding operation. You should use subqueries or common table expressions (CTEs) to perform the rounding operation on a subset of the data, and then use the result in the main query or expression, which will ensure that the Snowflake ROUND function does not interfere with other functions or operations that may affect the data quality or accuracy.
• Use the CAST or TO_NUMBER functions to convert the data type. You should use the CAST or TO_NUMBER functions to convert the data type of the numeric data to the appropriate type for the Snowflake ROUND function or other functions, which will ensure that the data type is compatible and consistent with the functions that you use.

Conclusion

And that’s a wrap! Snowflake ROUND() function is a powerful numeric function that offers a flexible way to format decimal values in Snowflake by rounding them to a specified number of decimal places. It helps in preparing numeric data for aggregated reporting and analysis. Understanding how to use this function effectively enables its application in data-wrangling scenarios involving decimal numbers.

In this article, we covered:

• What Is Snowflake ROUND Function?
• How Does Snowflake ROUND() Work?
• Data Type Supported by Snowflake ROUND Function
• How Snowflake Round Handles Null and Negative Values?
• Practical Examples of Snowflake Round Function
• When to Use Snowflake ROUND Function ?
• What are the Differences Between CEIL, TRUNCATE, FLOOR, and Snowflake ROUND?
• Limitations of Snowflake ROUND Function

—and so much more!

FAQs

What is the Snowflake ROUND() function?

Snowflake ROUND() function rounds a number to a specified number of decimal places. It allows you to truncate or clean up numeric values in Snowflake.

How do you use Snowflake ROUND() function in Snowflake?

The basic syntax is ROUND(number, decimals). You specify the number to round and the number of decimals to round it to. The default is 0 decimals which rounds to a whole number.

What are the arguments for the Snowflake ROUND() function?

The arguments are the number to round, the number of decimals to round to, and an optional rounding mode of either HALF_TO_EVEN or HALF_AWAY_FROM_ZERO(default).

What data types can you use with Snowflake ROUND() function?

Snowflake ROUND() works on numeric data types like NUMBER, FLOAT, INT, and DECIMAL in Snowflake. The return type matches the input type.

How does the Snowflake ROUND() function handle null values?

If either the number or decimals argument is null, Snowflake ROUND() function returns null.

How does the Snowflake ROUND() function handle negative numbers?

Negative numbers are rounded away from zero by default. The HALF_TO_EVEN rounding mode will round towards zero.

When would you use a positive scale in Snowflake ROUND() function?

A positive scale rounds your number to that many decimal places. This preserves some precision.

When would you use a negative scale in the ROUND() function?

A negative scale rounds your number to powers of 10 based on the absolute scale value. For example, if the scale is -2, the result is a multiple of 100.

What is the default rounding mode used by Snowflake ROUND()?

The default rounding mode is HALF_AWAY_FROM_ZERO. This rounds 0.5 away from zero.

What is the alternate rounding mode for ROUND()?

HALF_TO_EVEN is an alternate mode that implements banker's rounding. It rounds values like 0.5 towards the nearest even number.

What is the difference between Snowflake ROUND() and CEIL() in Snowflake?

CEIL() always rounds up to the next integer, while ROUND() rounds up or down based on the decimal value.

What is the difference between Snowflake ROUND() and TRUNCATE() in Snowflake?

TRUNCATE removes the decimal portion entirely rather than rounding. ROUND provides more flexible control over decimals.

What are some limitations of the Snowflake ROUND() function?

Limitations include precision loss, rounding errors, lack of custom rounding options, and potential issues when integrated with other functions.

How can you minimize precision loss with Snowflake ROUND()?

Use higher precision data types, larger scales, consistent rounding modes, and isolate rounding operations with subqueries or CTEs.

Why use Snowflake ROUND() instead of displaying full precision?

Rounding cleans up data and reduces noise from spurious digits for easier analysis and reporting.

Can you round date values with Snowflake ROUND()?

No, Snowflake ROUND() only works on numeric data types like floating point and decimal values. Dates are a separate data type.