When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?
When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?
A Data Engineer would use TABLE with the FLATTEN function when no additional source in the FROM clause is required to refer to. This is because, in this scenario, the FLATTEN function's output can be treated directly as a table. This is useful when the desired operation is to flatten the data without the need for any external references in the FROM clause.
https://docs.snowflake.com/en/sql-reference/functions/flatten
as wrote here: https://docs.snowflake.com/en/user-guide/json-basics-tutorial-flatten You can flatten the event objects in the events array into separate rows using the FLATTEN function. The function output includes a VALUE column that stores these individual events. You can then use the LATERAL modifier to join the FLATTEN function output with any information outside of the object
D. When TABLE with FLATTEN is acting like a sub-query executed for each returned row. The TABLE with FLATTEN combination is typically used when you want to treat the result of the FLATTEN function as a table that can be further joined or manipulated. This is particularly useful when you need to perform additional operations on each row returned by the FLATTEN function. The LATERAL FLATTEN combination is used when the FLATTEN function references other tables or sources in the FROM clause, and it allows for correlated subqueries.
By my opinion, as is wrote here: https://docs.snowflake.com/en/sql-reference/functions/flatten the answer should be: Flattens (explodes) compound values into multiple rows.