Charl Van Zyl

A slow MERGE in Snowflake usually comes down to one of three things:

1. Scanning everything. If the source is the whole staging table, every run reprocesses history. Filter on a high-water mark (updated_at > last load) so you only merge new rows.

2. Duplicate keys in the source....

JSON lands in Snowflake as a VARIANT column. Then someone needs one row per order item.

LATERAL FLATTEN does that in a single pass. Point it at the array (v:items) and it returns one row per element:

- item.value is the element itself, so item.value:sku::STRING pulls out a field

-...

Still wrapping a query in a subquery just to filter on ROW_NUMBER()? In Snowflake you don't have to.

QUALIFY works like WHERE, but for window functions. It's evaluated after the window is calculated, so you can filter on the result directly:

QUALIFY ROW_NUMBER() OVER (PARTITION BY...