Snowflake - Use ARRAY field without junction table

[Dawid]

When I worked with BigQuery, I used this article to unnest repeated records: https://help.looker.com/hc/en-us/articles/360023638874-Nested-Data-in-BigQuery-Repeated-Records-

When working with Snowflake, however, I realised I’m missing a similar pattern for ARRAYs.

I have a table <categories> and then <products>. Product table can have multiple categories.

So far I would have to use another view and another join from a junction table product_categories but I wanted to have something easier. 

I added a new column “category_ids” to the products  and then used the following code:

explore: categories {
  join: products {
    type: left_outer
    relationship: many_to_many
    sql_on: 1 = 1, LATERAL FLATTEN(${products.category_ids}) c ;;
    sql_where: c.value = ${categories.category_id}  ;;
  }
}

This is from the point of view of a category. It basically creates a CROSS JOIN and SQL_WHERE changes it into an INNER.

1. We can’t use type: cross, because then sql_on will not be added
2. 1 = 1 is needed because sql_on adds the keyword ON
3. You won’t be able to add any more joins to products because LATERAL can’t be on the left side of the join, however you can use the trick by moving the LATERAL FLATTEN to the last join
4. If you need it to be LEFT you can add OR c.value IS NULL to SQL_WHERE

[JohnRomanski]

This topic helped me down this path, but this medium article is what helped the most. 

https://medium.com/gumgum-tech/exploring-compound-value-dimensions-with-snowflake-and-looker-e759069...

I can't believe that this isn’t higher traffic.