Repeat the previous Value for a null value in Snowflake

Question

I'm loading merge excel cells using Matilion into Snowflake table.

After loading into SF table, I'm trying to replace all nulls with the first column value.

Below is the input data

I/p:

Expected O/P:

I'm trying with the below sql but getting error unsupported subquery value in Snowflake.

Can someone help me out in this to replace the nulls.

select *,
(
select 
top 1 "A"  
from table_name inner_tbl
where inner_tbl.b=outr_tbl.B
and inner_tbl.C=outr.C) as st
from table_name outr_tbl;

Answer 1

Excel has one thing that a relational database does not, which is a reliable physical row number. Ideally you should add the row number when you ingest the data into Snowflake. There is a METADATA$FILE_ROW_NUMBER for this purpose.

But anyway, without that - and especially if the table is very small - you might be able to get away with creating a row number artificially like this:

WITH data AS (
    SELECT category, value, label, SEQ8() AS rownumber
    FROM table_name)
SELECT LAST_VALUE(category)
           IGNORE NULLS OVER (ORDER BY rownumber
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS category,
       value, label
FROM data
ORDER BY rownumber

The SQL is from this Matillion article on data densification