Snowflake SQL Tutorial 70 - ROUND, CEIL, FLOOR, TRUNC, ABS, SIGN, MOD - Numbers

Snowflake SQL · Episode 70

In this post we are going to cover the number functions in Snowflake: ROUND, CEIL, FLOOR, TRUNC, ABS, SIGN and MOD.

These are the functions that take a number and give you back a tidier one. Four of them remove the decimal part in four different ways, ABS and SIGN split a number into how far and which way, and MOD gives you the remainder after a division.

We will start with ROUND on a real money column and build it up step by step. Along the way we will settle two things that are not safe to answer from memory: which way Snowflake sends a half, and which remainder rule MOD follows.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 70 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with ROUND
3. Which way does a half go?
4. Now let's put all four on the same number
5. Can all four take a second argument?
6. How often do ROUND and TRUNC actually disagree?
7. Now let's look at ABS and SIGN
8. Let's move on to MOD
9. So what is MOD actually for? Buckets
10. One last thing: everything on five rows

Let's create some sample data

Before we round anything we need a column of numbers to round. The script below creates the ORDERS table that this whole course uses.

Copy it into a Snowflake worksheet and run it once. Every post in this series uses the same tables, so you only have to do this one time.

Sample data

-- ORDERS comes from the setup script this course uses throughout.
-- Run it once; every episode reuses the same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID     INT,
    CUSTOMER_ID  INT,
    ORDER_DATE   DATE,
    SHIP_DATE    DATE,
    STATUS       VARCHAR,
    AMOUNT       NUMBER(10,2),
    QUANTITY     INT
);

-- AMOUNT is NUMBER(10,2): an EXACT decimal, not a binary float.
-- That one detail is behind a result further down this page that surprises
-- people who have done the same arithmetic in another language.
-- The INSERT is in the course setup script; it loads 41 orders.

Now let's look at what we are working with.

Query

SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT, STATUS
FROM ORDERS ORDER BY ORDER_ID LIMIT 10;

Result10 rows · 5 columns

ORDER_ID	CUSTOMER_ID	ORDER_DATE	AMOUNT	STATUS
1001	101	2023-01-12	4200.00	SHIPPED
1002	102	2023-01-28	890.50	SHIPPED
1003	101	2023-02-14	6150.00	SHIPPED
1004	103	2023-02-27	1500.00	CANCELLED
1005	104	2023-03-09	2310.75	SHIPPED
1006	101	2023-03-22	5020.00	SHIPPED
1007	105	2023-04-05	7890.00	SHIPPED
1008	102	2023-04-19	1120.00	SHIPPED
1009	103	2023-05-02	3340.25	SHIPPED
1010	106	2023-05-21	640.00	PENDING

The column we care about is AMOUNT. Most of those amounts are whole. A few carry cents, and those are the rows the rounding functions are going to do something to.

Let's also get the shape of the whole column, because ten rows is not the table.

Query

SELECT COUNT(*) AS ROWS_TOTAL, MIN(AMOUNT) AS MIN_AMOUNT,
       MAX(AMOUNT) AS MAX_AMOUNT, AVG(AMOUNT) AS AVG_AMOUNT,
       SUM(AMOUNT) AS TOTAL
FROM ORDERS;

Result1 row · 5 columns

ROWS_TOTAL	MIN_AMOUNT	MAX_AMOUNT	AVG_AMOUNT	TOTAL
41	640.00	13100.00	5135.67500000	205427.00

That is the row count, the smallest and largest amount, the average and the total. Keep an eye on the average. Its decimal part is what makes the rest of this page worth running rather than reading.

A row that is quietly not in the average

One of these orders has no amount at all. We will see it later on this page, at the top of the distance grid in the ABS section. It matters here only because AVG averages the rows that have an amount and says nothing about the one that does not. That is correct and it is silent, and Episode 71 drags it into the open with a GROUP BY.

Let's start with ROUND

ROUND with one argument rounds to a whole number. With a second argument it rounds to that many decimal places, and that second argument is allowed to be negative.

Let's ask for all four at once on the real column.

Query

SELECT ORDER_ID, AMOUNT,
       ROUND(AMOUNT)     AS ROUND_0,
       ROUND(AMOUNT, 1)  AS ROUND_1,
       ROUND(AMOUNT, -2) AS ROUND_TO_HUNDREDS,
       ROUND(AMOUNT, -3) AS ROUND_TO_THOUSANDS
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 10;

Result10 rows · 6 columns · scroll →

ORDER_ID	AMOUNT	ROUND_0	ROUND_1	ROUND_TO_HUNDREDS	ROUND_TO_THOUSANDS
1001	4200.00	4200	4200.0	4200	4000
1002	890.50	891	890.5	900	1000
1003	6150.00	6150	6150.0	6200	6000
1004	1500.00	1500	1500.0	1500	2000
1005	2310.75	2311	2310.8	2300	2000
1006	5020.00	5020	5020.0	5000	5000
1007	7890.00	7890	7890.0	7900	8000
1008	1120.00	1120	1120.0	1100	1000
1009	3340.25	3340	3340.3	3300	3000
1010	640.00	640	640.0	600	1000

Read across one row at a time. No second argument gives us whole currency units. 1 gives us one decimal place. -2 rounds to the nearest hundred, and -3 rounds to the nearest thousand.

So a negative scale means digits to the left of the point. That is how you get an order-of-magnitude column into a report without doing any arithmetic yourself, and it is the part most tutorials leave out.

Before we move on, look at two rows in that grid. Order 1002's amount ends in exactly half a unit. Order 1004's amount sits exactly halfway between two thousands. Both of those are ties, and both of them had to go somewhere.

Which way does a half go?

There is more than one defensible answer to that, which is why it is not a thing to guess at.

Round-half-up sends every tie away from zero. Round-half-even, which you may know as banker's rounding, sends a tie to whichever neighbour is even so that a long column of ties does not drift upward. Plenty of languages do the second one by default.

Let's just ask Snowflake. Four positive ties and two negative ones.

Query

SELECT ROUND(0.5)  AS R_0_5,
       ROUND(1.5)  AS R_1_5,
       ROUND(2.5)  AS R_2_5,
       ROUND(3.5)  AS R_3_5,
       ROUND(-0.5) AS R_MINUS_0_5,
       ROUND(-2.5) AS R_MINUS_2_5;

Result1 row · 6 columns · scroll →

R_0_5	R_1_5	R_2_5	R_3_5	R_MINUS_0_5	R_MINUS_2_5
1	2	3	4	-1	-3

Read the first four columns as a sequence. They march straight up. If this were banker's rounding then ROUND(2.5) would have come back as an even number and broken that pattern. It did not. Snowflake is not using banker's rounding.

The last two columns tell us the direction. Both negative ties moved further from zero rather than toward it. So the rule is half away from zero, and it works the same way on both sides.

Now let's try the same thing at a decimal place

These four values are all exact ties on paper, and we are asking for two decimal places.

Query

SELECT ROUND(0.125, 2) AS R_0_125,
       ROUND(0.135, 2) AS R_0_135,
       ROUND(2.675, 2) AS R_2_675,
       ROUND(1.005, 2) AS R_1_005;

Result1 row · 4 columns

R_0_125	R_0_135	R_2_675	R_1_005
0.13	0.14	2.68	1.01

All four went up, which is what half away from zero promises. The interesting one is ROUND(2.675, 2), because in most programming languages that same expression gives you 2.67 instead.

Exact decimal versus binary float

Those languages are not broken and neither is Snowflake. They store 2.675 as a binary floating-point number, and the nearest value they can store is a hair below the true tie, so there is nothing there to round up. Snowflake's NUMBER type is an exact decimal, so 2.675 really is 2.675 and the tie rule applies.
If a Python script and your warehouse disagree by a penny, this is usually why.

It is the type, not the engine

That guarantee belongs to NUMBER, not to Snowflake in general. Cast to FLOAT, or call a function that returns one, and you are back in binary floating point with everybody else. Episode 71 shows exactly that happening to LOG.

Now let's put all four on the same number

ROUND, CEIL, FLOOR and TRUNC all remove the decimal part. Let's give the four of them one positive number and see what comes back.

Query

SELECT 2.7 AS VALUE, ROUND(2.7) AS ROUNDED, CEIL(2.7) AS CEILING,
       FLOOR(2.7) AS FLOOR_, TRUNC(2.7) AS TRUNCATED;

Result1 row · 5 columns

VALUE	ROUNDED	CEILING	FLOOR_	TRUNCATED
2.7	3	3	2	2

Four functions, two different answers. ROUND and CEIL agree with each other, FLOOR and TRUNC agree with each other, and if we stopped here we would walk away with a tidy rule of thumb that is wrong.

Let's flip the sign and run the same four again.

Query

SELECT -2.7 AS VALUE, ROUND(-2.7) AS ROUNDED, CEIL(-2.7) AS CEILING,
       FLOOR(-2.7) AS FLOOR_, TRUNC(-2.7) AS TRUNCATED;

Result1 row · 5 columns

VALUE	ROUNDED	CEILING	FLOOR_	TRUNCATED
-2.7	-3	-2	-3	-2

Two different answers again, but look at who is paired with whom now. ROUND has moved across to agree with FLOOR, and CEIL has swapped over to agree with TRUNC. Every pairing from the positive case has broken.

That flip is the whole confusion, and it happens because these four functions are not four versions of one idea. They answer three different questions.

Function	What it is really about	Which way it moves
CEIL	the number line	always upward, whatever the sign
FLOOR	the number line	always downward, whatever the sign
TRUNC	zero	always toward zero, so its direction depends on the sign
ROUND	which value is nearer	to the nearer neighbour, and on a tie away from zero

CEIL and FLOOR never change their mind. Up is up and down is down on both sides of zero. TRUNC never changes its mind either, but what it means on the number line is different on each side. And ROUND is not about direction at all. It is about distance, which is why it can side with either neighbour.

Let's do the tie case too, because a half is where ROUND has to make a decision rather than measure one.

Query

SELECT -2.5 AS VALUE, ROUND(-2.5) AS ROUNDED, CEIL(-2.5) AS CEILING,
       FLOOR(-2.5) AS FLOOR_, TRUNC(-2.5) AS TRUNCATED;

Result1 row · 5 columns

VALUE	ROUNDED	CEILING	FLOOR_	TRUNCATED
-2.5	-3	-2	-3	-2

ROUND still lands with FLOOR, because away from zero and downward are the same direction here.

Two answers, but only when there is a fraction

All three of those grids feed the four functions a number with a fraction in it, which is why each one comes back with two different answers. Hand the same four a whole number and they all land in the same place, because there is nothing to disagree about. We will see that happen in the last grid on this page.

Can all four take a second argument?

TRUNC takes the same second argument as ROUND does, so we can put the pair side by side at two different scales.

Query

SELECT 1234.5678 AS VALUE,
       TRUNC(1234.5678, 2)  AS TRUNC_2,
       ROUND(1234.5678, 2)  AS ROUND_2,
       TRUNC(1234.5678, -2) AS TRUNC_MINUS_2,
       ROUND(1234.5678, -2) AS ROUND_MINUS_2;

Result1 row · 5 columns

VALUE	TRUNC_2	ROUND_2	TRUNC_MINUS_2	ROUND_MINUS_2
1234.5678	1234.56	1234.57	1200	1200

At two decimal places they differ, which is what we would expect. One cuts the digits off and the other rounds. At -2, on this particular value, they happen to agree. That is a coincidence of the number we picked and not a rule, and it is a good reminder that one matching pair of columns proves nothing.

Now let's try CEIL and FLOOR

This is the one that catches people coming from almost any programming language, where ceil and floor take a single argument and hand you back a whole number.

Query

SELECT CEIL(1.234, 2)  AS CEIL_2,
       FLOOR(1.234, 2) AS FLOOR_2,
       CEIL(1234, -2)  AS CEIL_MINUS_2,
       FLOOR(1234, -2) AS FLOOR_MINUS_2;

Result1 row · 4 columns

CEIL_2	FLOOR_2	CEIL_MINUS_2	FLOOR_MINUS_2
1.24	1.23	1300	1200

Both of them took the scale. Both of them moved to the next value at that scale rather than to the next whole number, and both of them took a negative scale as happily as a positive one.

So rounding a price up to the next cent, or a figure up to the next hundred, is one function call and no arithmetic.

How often do ROUND and TRUNC actually disagree?

Everything so far has been single values. On a real column the useful question is a different one: how much of my data would move if I changed the function?

That is countable, so let's count it.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(ROUND(AMOUNT) <> TRUNC(AMOUNT))   AS DIFFER_AT_0,
       COUNT_IF(ROUND(AMOUNT, 1) <> TRUNC(AMOUNT, 1)) AS DIFFER_AT_1,
       COUNT_IF(CEIL(AMOUNT) <> FLOOR(AMOUNT))    AS HAS_A_FRACTION
FROM ORDERS;

Result1 row · 4 columns

ROWS_TOTAL	DIFFER_AT_0	DIFFER_AT_1	HAS_A_FRACTION
41	3	2	4

Out of 41 rows, ROUND and TRUNC differ on 3 of them at zero decimal places and on 2 of them at one decimal place.

The last column is the control. Only 4 rows have any fractional part at all, so the rest of the table could not possibly have differed.

Which leaves one row that has a fraction and still agrees, and it is already on this page. Order 1009 in the ROUND grid is the row whose cents are below a half, so rounding down and cutting off land in the same place. That is a fact about this data and not a rule.

Count it on your own column

This is the shape of check worth running before you change a rounding rule in a report. Not “are these functions different”, which you can read in the documentation, but “how many of my rows would move”, which only your own data can answer.

Now let's look at ABS and SIGN

ABS throws the sign away. SIGN keeps nothing but the sign. Between them they split a number into how far and which way.

Let's put five values through both.

Query

SELECT V.N,
       ABS(V.N)  AS ABSOLUTE,
       SIGN(V.N) AS SIGN_
FROM (SELECT -7 AS N UNION ALL SELECT -0.5 UNION ALL SELECT 0
      UNION ALL SELECT 0.5 UNION ALL SELECT 7) V
ORDER BY V.N;

Result5 rows · 3 columns

N	ABSOLUTE	SIGN_
-7.0	7.0	-1
-0.5	0.5	-1
0.0	0.0	0
0.5	0.5	1
7.0	7.0	1

SIGN has exactly three possible answers and no others, whatever you feed it: negative, zero, positive. It does not tell you the magnitude and it is not meant to.

Now let's try the two edges that people worry about.

Query

SELECT SIGN(NULL) AS SIGN_NULL,
       SIGN(0)    AS SIGN_ZERO,
       SIGN(-0.0) AS SIGN_MINUS_ZERO,
       ABS(NULL)  AS ABS_NULL;

Result1 row · 4 columns

SIGN_NULL	SIGN_ZERO	SIGN_MINUS_ZERO	ABS_NULL
NULL	0	0	NULL

NULL in gives NULL out of both functions, which is the normal rule and still worth confirming. And SIGN(-0.0) comes back as plain zero, so there is no negative zero to trip over. A CASE on SIGN needs three branches and not four.

Now let's give ABS some real work

The natural use for ABS is distance. How far is each order from the average, and which side of it?

Query

SELECT ORDER_ID, AMOUNT,
       ROUND(AVG(AMOUNT) OVER (), 2)              AS AVG_AMOUNT,
       ROUND(AMOUNT - AVG(AMOUNT) OVER (), 2)     AS SIGNED_GAP,
       ROUND(ABS(AMOUNT - AVG(AMOUNT) OVER ()), 2) AS DISTANCE
FROM ORDERS
ORDER BY DISTANCE DESC
LIMIT 10;

Result10 rows · 5 columns

ORDER_ID	AMOUNT	AVG_AMOUNT	SIGNED_GAP	DISTANCE
1041	NULL	5135.68	NULL	NULL
1037	13100.00	5135.68	7964.33	7964.33
1032	12850.00	5135.68	7714.33	7714.33
1025	11200.00	5135.68	6064.33	6064.33
1020	10400.00	5135.68	5264.33	5264.33
1040	9640.00	5135.68	4504.33	4504.33
1010	640.00	5135.68	-4495.68	4495.68
1014	750.00	5135.68	-4385.68	4385.68
1002	890.50	5135.68	-4245.18	4245.18
1021	980.00	5135.68	-4155.68	4155.68

SIGNED_GAP tells us which side of the average a row sits on. DISTANCE throws that away, which is what makes it the column we can sort by. That is how the biggest outliers in either direction come to the top of one list.

Both columns are built from the same window average, worked out once with AVG(AMOUNT) OVER ().

And there is the order with no amount, promised at the start of the post, sitting at the very top of the grid. It is not an outlier. It is Snowflake's default ordering: in a DESC sort, NULLs come first. ABS(NULL - anything) is NULL, so that row has no distance to sort by and lands at the front of the one list it least belongs in.

Let's move on to MOD

MOD is the remainder after a division. Snowflake spells it as a function and as an operator, and they are the same thing.

Query

SELECT MOD(10, 3) AS TEN_MOD_3,
       MOD(10, 5) AS TEN_MOD_5,
       MOD(10, 1) AS TEN_MOD_1,
       10 % 3     AS PERCENT_OPERATOR;

Result1 row · 4 columns

TEN_MOD_3	TEN_MOD_5	TEN_MOD_1	PERCENT_OPERATOR
1	0	0	1

Nothing surprising while both arguments are positive. A divisor that does not divide evenly leaves a remainder, one that does leaves zero, and % gives us the same answer as MOD.

Now let's put a negative in it

Query

SELECT MOD(7, 3)   AS POS_POS,
       MOD(-7, 3)  AS NEG_POS,
       MOD(7, -3)  AS POS_NEG,
       MOD(-7, -3) AS NEG_NEG;

Result1 row · 4 columns

POS_POS	NEG_POS	POS_NEG	NEG_NEG
1	-1	1	-1

Read those four columns as a pair of pairs. The two with a positive left-hand side agree with each other, and the two with a negative left-hand side agree with each other, whatever the divisor's sign is doing. The sign of the result follows the dividend, which is the left-hand side, and the divisor's sign is ignored.

That is a description though, and a description is exactly what you cannot port to another language. There are two rules a language can follow here and they only differ on a negative dividend, so let's put both of them next to the answer.

Query

SELECT MOD(-7, 3)                 AS MOD_SAYS,
       -7 - 3 * TRUNC(-7 / 3)     AS TRUNCATED_DIVISION_RULE,
       -7 - 3 * FLOOR(-7 / 3)     AS FLOORED_DIVISION_RULE;

Result1 row · 3 columns

MOD_SAYS	TRUNCATED_DIVISION_RULE	FLOORED_DIVISION_RULE
-1	-1	2

MOD_SAYS matches the TRUNCATED_DIVISION_RULE column and not the FLOORED_DIVISION_RULE one, and the two rules did not just differ in sign. They gave genuinely different numbers. Snowflake follows truncated division.

A genuine portability bug

Python's % follows the other rule. -7 % 3 is a positive number there and a negative one here. Nothing errors and nothing warns about it, so a bucketing or cycling calculation ported either way simply puts some rows in a different bucket. If you are moving a calculation between a warehouse and a script, this is the line to check first, and the test is a negative input.

What about decimals, and dividing by zero?

Query

SELECT MOD(10.5, 3)   AS DECIMAL_MOD,
       MOD(10, 2.5)   AS DECIMAL_DIVISOR,
       MOD(NULL, 3)   AS MOD_NULL,
       MOD(0, 3)      AS ZERO_MOD;

Result1 row · 4 columns

DECIMAL_MOD	DECIMAL_DIVISOR	MOD_NULL	ZERO_MOD
1.5	0.0	NULL	0

MOD took a decimal on either side, which is not true in every SQL dialect, and a NULL argument gave us NULL as usual. A zero dividend was perfectly fine.

A zero divisor is a different story.

Query

SELECT MOD(10, 0) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100051 (22012): Division by zero

A real error, with the name you would expect. Not NULL, not zero, not a silently skipped row. The statement fails. So if the divisor is computed rather than typed, it needs a NULLIF or a CASE around it, the same as any other division.

So what is MOD actually for? Buckets

The everyday job for MOD is splitting rows into a fixed number of groups. For sampling, for parallel processing, for A/B assignment, for spreading a backfill over several runs.

Let's split the orders four ways on their ID.

Query

SELECT MOD(ORDER_ID, 4) AS BUCKET, COUNT(*) AS N,
       ROUND(SUM(AMOUNT), 2) AS TOTAL
FROM ORDERS
GROUP BY MOD(ORDER_ID, 4)
ORDER BY BUCKET;

Result4 rows · 3 columns

BUCKET	N	TOTAL
0	10	48035.50
1	11	61451.00
2	10	35190.50
3	10	60750.00

MOD(ORDER_ID, 4) gave us four buckets holding 10, 11, 10 and 10 rows. Those counts add up to the 41 rows of the table, which is the first thing to check. A bucketing expression that loses rows loses them quietly.

Let's check it properly rather than adding it up by eye.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(MOD(ORDER_ID, 4) BETWEEN 0 AND 3) AS IN_RANGE,
       COUNT(DISTINCT MOD(ORDER_ID, 4))           AS DISTINCT_BUCKETS
FROM ORDERS;

Result1 row · 3 columns

ROWS_TOTAL	IN_RANGE	DISTINCT_BUCKETS
41	41	4

41 rows, 41 of them inside the expected range, and exactly 4 distinct bucket values. The range check is the one that earns its keep here. It is what tells us the sign surprise from the previous section is not happening, because ORDER_ID is never negative.

Bucket on something that cannot be NULL

Note which column we bucketed on. ORDER_ID is never NULL, so every row got a bucket, including the order whose AMOUNT is NULL. Bucket on a nullable column instead and MOD returns NULL, which GROUP BY will happily collect into a bucket of its own.

Deterministic is not random

MOD on a sequential ID is a deterministic split, not a random one. It is reproducible, which is usually what you want. But consecutive IDs land in different buckets by construction, so if your IDs correlate with anything, such as time of signup or import batch or source system, your buckets inherit that correlation. Episode 71 covers RANDOM and UNIFORM, which are the tools for when you need the other kind of split.

One last thing: everything on five rows

Let's finish with the whole post in one query. The amount rounded, ceilinged, floored and truncated side by side, a SIGN used as a flag, and a bucket.

Query

SELECT ORDER_ID, AMOUNT,
       ROUND(AMOUNT, 1)  AS ROUNDED,
       CEIL(AMOUNT)      AS CEILING,
       FLOOR(AMOUNT)     AS FLOOR_,
       TRUNC(AMOUNT)     AS TRUNCATED,
       SIGN(AMOUNT - 500) AS ABOVE_500,
       MOD(ORDER_ID, 4)  AS BUCKET
FROM ORDERS
ORDER BY ORDER_ID
LIMIT 5;

Result5 rows · 8 columns · scroll →

ORDER_ID	AMOUNT	ROUNDED	CEILING	FLOOR_	TRUNCATED	ABOVE_500	BUCKET
1001	4200.00	4200.0	4200	4200	4200	1	1
1002	890.50	890.5	891	890	890	1	2
1003	6150.00	6150.0	6150	6150	6150	1	3
1004	1500.00	1500.0	1500	1500	1500	1	0
1005	2310.75	2310.8	2311	2310	2310	1	1

Look at the rows whose amounts are whole. All four rounding columns land on the same number there, which is the point the fourth section was making from the other direction. These functions only ever differ where there is a fraction to disagree about.

And SIGN(AMOUNT - 500) is the idiom worth stealing. Subtract a threshold and take the sign, and you get a three-way above, at or below flag out of one expression instead of a CASE with three branches. Every amount in these five rows is above the threshold, so the column reads the same all the way down.

Key points

• Snowflake rounds half away from zero, not to even. ROUND(2.5) does not come back as an even number, and negative ties move further from zero rather than toward it.
• ROUND's second argument may be negative, which rounds to tens, hundreds or thousands. TRUNC, CEIL and FLOOR all take the same argument, which ceil and floor do not in most programming languages.
• ROUND(2.675, 2) rounds up here and does not in most languages, because NUMBER is an exact decimal and their floats are not. That guarantee belongs to the type, so cast to FLOAT and you lose it.
• Give the four rounding functions a number with a fraction in it and you get two different answers, but which two agree flips with the sign. ROUND sides with CEIL on a positive and with FLOOR on a negative, and CEIL swaps over to TRUNC. Give them a whole number and all four land in the same place.
• CEIL and FLOOR are about the number line, TRUNC is about zero, and ROUND is about which value is nearer. TRUNC is the only one of the four that never moves away from zero.
• How often the choice would change your data is a COUNT_IF, not a reading of the documentation. Here ROUND and TRUNC differ on 3 of 41 rows at zero decimal places, because only 4 rows have a fraction at all.
• SIGN returns one of three values and nothing else, and SIGN(-0.0) is plain zero. There is no negative zero to handle.
• MOD takes its sign from the dividend and follows truncated division. Python follows floored division and gives the opposite sign on a negative dividend. That is a portability bug, not a curiosity, and nothing warns you about it.
• MOD by zero is a real division-by-zero error and not a NULL. As a bucketing tool MOD is deterministic, so bucket on a column that cannot be NULL and check the range.

Next in this series

Episode 71 — the maths functions. POWER, SQRT, EXP, LN, LOG, RANDOM and UNIFORM, including one result that flatly contradicts this post's exact-decimal rounding, and the NULL amount that nobody has mentioned for two episodes.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the error pane. The ORDERS data is invented demo data created by the course setup script. No performance or benchmark claims are made anywhere in this series, and nothing on this page is timed. Function behaviour follows the Snowflake numeric function documentation.