Improve the flatten performance of this query

Question

Snowflake Community,

I'm running a Snowpipe that is continuously landing JSON into a staging table, which is then flattened into numerous target tables.

One of the destination tables requires running 4 lateral flattens on the JSON - something like this, but the data is loading faster than I can run the flattening.:

LATERAL FLATTEN(INPUT => JSON:data:Element) ELEMENT, 

LATERAL FLATTEN(INPUT => ELEMENT.VALUE) ATTRIBUTE, 

LATERAL FLATTEN(INPUT => ATTRIBUTE.VALUE) SUBATTRIBUTE, 

LATERAL FLATTEN(INPUT => SUBATTRIBUTE.VALUE) SUB_SUB_ATTRIBUTE

Would it be faster if I stored each flattened element in it's own table, then run the subsequent flatten statements on the upstream table?

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2020-01-03T06:09:38

Have you considered a single recursive FLATTEN() to replace all the previous?

LATERAL FLATTEN(INPUT => JSON:data:Element, RECURSIVE => TRUE)

Improve the flatten performance of this query

1 Answers