Snowflake Lead Function

Question

I have a question about the use of the lead function within Snowflake.

Let's say I have a text field of '881885895.1636104601'. When I run the lead function (assume this number repeats and returns this from the lead result), I noticed that the output gets trimmed/truncated to '881885895.16361'.

Does anyone know why this is the case, or has seen this strange behavior before?

Answer 1

Using default value that has different data type causes an implicit conversion. An example to reporduce the case:

WITH cte AS (
    SELECT '881885895.1636104601' AS COL, 1 AS ID
    UNION ALL SELECT '881885895.1636104601' AS COL, 2 AS ID
)
SELECT *, LEAD(col,1,0) OVER(ORDER BY ID)
FROM cte;
-- Output
-- 881885895.1636104601    vs 881885895.16361

DESCRIBE RESULT LAST_QUERY_ID();
-- name      col
-- col       VARCHAR(20)
-- lead      NUMBER(18,5)

---

By matching the data type the output is as intended:

WITH cte AS (
    SELECT '881885895.1636104601' AS COL, 1 AS ID
    UNION ALL SELECT '881885895.1636104601' AS COL, 2 AS ID
)
SELECT *, LEAD(col,1,'0') OVER(ORDER BY ID)
FROM cte;

Answer 2

It has nothing to do with LEAD() function, it happens when you implicitly casting string to number.

See the example below:

select '881885895.1636104601'::double;
+--------------------------------+
| '881885895.1636104601'::DOUBLE |
|--------------------------------|
|                881885895.16361 |
+--------------------------------+

When users casting string to number implicitly, Snowflake will default to NUMBER(18,5).

If you need more scale and precision, you need to cast explicitly.

select '881885895.1636104601'::number(38,10);
+---------------------------------------+
| '881885895.1636104601'::NUMBER(38,10) |
|---------------------------------------|
|                  881885895.1636104601 |
+---------------------------------------+