The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started
Back to feedPost
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
- item.index gives you the position, which makes a handy line number
Three tips:
- Always cast (::STRING, ::INT). Uncast VARIANT values compare and join in surprising ways.
- Use FLATTEN(..., OUTER => TRUE) to keep parent rows whose array is empty.
- Flatten once in a staging model, not in every report.
You get semi-structured data into clean relational tables, all in plain SQL.
#Snowflake #SQL #JSON
The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started
Related posts
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. When several source rows match one target row, the MERGE either errors or does extra work. Deduplicate first with QUALIFY ROW_NUMBER() = 1.
3. No pruning on the target. Snowflake can only skip micro-partitions when the ON or filter conditions touch a column that the target is clustered or naturally ordered by, such as a date. Adding a date-range condition can cut the scan dramatically, as long as late updates can't fall outside that window.
Fix these three and you'll often see a MERGE that took minutes finish in seconds.
#Snowflake #SQL #DataEngineering
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 order_id ORDER BY updated_at DESC) = 1
That one line keeps the latest version of every order. You don't need a nested SELECT, a helper column like rn, or a SELECT * that drags rn into your output.
It also works with RANK, DENSE_RANK, LAG and any other window expression, which makes it ideal for deduplication, top-N per group and keeping the latest record per key.
Cleaner SQL is easier to review, and easier to review means fewer bugs in production.
#Snowflake #SQL #DataEngineering
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 order_id ORDER BY updated_at DESC) = 1
That one line keeps the latest version of every order. You don't need a nested SELECT, a helper column like rn, or a SELECT * that drags rn into your output.
It also works with RANK, DENSE_RANK, LAG and any other window expression, which makes it ideal for deduplication, top-N per group and keeping the latest record per key.
Cleaner SQL is easier to review, and easier to review means fewer bugs in production.
#Snowflake #SQL #DataEngineering
