CEIL function in snowflake

Explained: CEIL() function in Snowflake?

by

Are you looking to find how to use the CEIL() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how CEIL function works in Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what it is and how to use the CEIL() function. I will explain the CEIL() function by taking a practical example. So don’t waste time, let’s start a step by step guide to understand the CEIL() expression.

What is CEIL() Function ?

CEIL() function returns the value from input expression rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point.

How can we use CEIL() Function ?

The CEIL() function can be used with numeric data types, such as FLOAT or NUMBER.

What is the Syntax of the CEIL function?

-- Syntax : 

CEIL( <input_expr> [, <scale_expr> ] )

CEIL() Argument Details :

Parameter NameParameter Description
Input_ExprIt is the value of expression in Float or number
scale_exprIt is defined as scale of digits after decimal
Table 1: Parameter Table for CEIL() function in Snowflake

input_expr:

It is the value of expression on which we operate. The data type which we can pass on in input expression must be numeric data type. For example: FLOAT or NUMBER.

scale_expr:

Scale_expr is basically the number of digits the CEIL function will include in the output after the decimal point. It evaluates to an integer from -38 to 38. Its default value is zero, meaning that the function will remove all the digits after the decimal point. For example: CEIL(20.355) will return the value 21, since scale expression by default is 0 and in that case CEIL function will remove all digits after decimal point. 

Examples of CEIL() function in Snowflake:

Firstly, Let’s take sample data to test the CEIL function.

select ceil(45.795), ceil(-134.64);
+----------------------+-------------------------+
| CEIL(45.795) | CEIL(-134.64)          |
|-----------------------+------------------------|
|            46            |          -134            |
+----------------------+-------------------------+

As we can see in the above example, CEIL function is demonstrated without the scale_expr, so CEIL function will consider its default value i.e. 0 and will return the output as integer rounded to the nearest equal or larger integer.

create transient table test1 (m float, scale integer);
insert into test1 (m,scale) values
                     (-375.875,-1),
                     (-75.875,0),
                     (-75.875,2),
                     (235.235,-2),
                     (35.235,0),
                     (35.235,1),
                     (35.235,3),
                     (35.235,70),
                     (35.235,NULL);
--Output:

select m,scale,ceil(m,scale) from test1;
+----------+-------+---------------+
|        M | SCALE | CEIL(M,SCALE) |
|----------+-------+---------------|
| -375.875 |    -1 |      -370     |
|  -75.875 |     0 |       -75     |
|  -75.875 |     2 |       -75.87  |
|  235.235 |    -2 |       300     |
|   35.235 |     0 |        36     |
|   35.235 |     1 |        35.3   |
|   35.235 |     3 |        35.235 |
|   35.235 |    70 |        35.235 |
|   35.235 |  NULL |          NULL |
+----------+-------+---------------+

As we can see in the above example, the CEIL function is demonstrated with the scale_expr with all different cases including scale_expr set to negative number. 

Note: If scale_expr is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100. Similarly, for scale -1, the result is a multiple of 10.

If the scale_expr is greater than the input_expr scale, then there will be no change in the output which happened in case of CEIL(73.247,40), the output is 73.247 as scale expression value ‘40’ is larger than input expression scale ‘3’.

When should you use CEIL() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the CEIL function within the Snowflake cloud data warehouse which are as follows: 

We can use this function whenever a nearest possible integer value is sufficient to do further calculations and makes no significant impact on the cost.

Real World Use Case Scenarios for CEIL Function in Snowflake

  • It is used in many business applications of Insurance and banking domain as it always rounds down and returns the largest integer less than or equal to a given number. For example: If the monthly electricity bill to be paid by a customer is 89.932$, then in that case, the electricity department will send a bill of $90 which can be done with the help of the CEIL function.

CEIL() Snowflake Official Documentation Link

Limitations of CEIL() Function in Snowflake?

  • CEIL can be used only with FLOAT or NUMBER.
  • CEIL evaluates the scale between -38 to 38.

Final Thoughts

In this article, we have learned about CEIL function and its uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible in normal scenarios. I hope this information which is provided here helped in gaining the knowledge and make you action ready on CEIL.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Prateek Gupta