Snowflakeで配列の値やカンマ区切り等の複合値を複数行に展開する方法

要するに第1正規化ができてないデータがあるときにそれを複数行に展開する方法です。

a,b,c みたいな文字列がデータにあるときにa, b, c をそれぞれ別行のデータに展開します。

これには lateral flatten という構文を使います。参考になるドキュメントはこちらです。
参考: FLATTEN | Snowflake Documentation

元々のデータが配列型の場合は split が不要になるのでその分を読み替えてください。
最初のwith句でダミーデータを生成して実験しています。

from 句で指定してるテーブルの後、joinで複数テーブルを結合している場合はそれらの一番最後にカンマを打ってから使う点に注意してください。また、 inputとか values とかも決まった単語ですのでそのまま使います。

with
dummy_data as (
    select
        1 as id,
        'a,b,c' as text
    union all select
        2 as id,
        None as text
    union all select
        3 as id,
        'd' as text
)

select
    id,
    value::varchar as text
from
    dummy_data, lateral flatten(input => split(text, ','))

これで、
id, text
1, a
1, b
1, c
3, d
という結果が得られます。

さて、id = 2のレコードについてはtextの値がNullだったので結果に出てきませんでした。
これがNullではなく空白文字列であれば、id=3のレコードと同様に行は作られたのですけどね。

このように展開する値がNullのレコードも残したい場合は、 outerオプションを使います。= でではなく => で trueに指定するSQLでは見慣れない記法ですが、次のような形になります。

with
dummy_data as (
    select
        1 as id,
        'a,b,c' as text
    union all select
        2 as id,
        Null as text
    union all select
        3 as id,
        'd' as text
)

select
    id,
    value::varchar as text
from
    dummy_data, lateral flatten(input => split(text, ','), outer => true)

そもそも正規化がちゃんと行われているデータだけ使えばいいのであれば滅多に使わない記法なのですが、残念ながら自分はよく使う場面が頻繁にあることと、その割になかなか覚えられなくて毎回調べているので今回記事にしました。