Category: Semi-Structured Data / Table Functions

Platform Support:

✅ Snowflake (FLATTEN) | ✅ BigQuery (UNNEST) | ✅ Databricks (EXPLODE)

Description

Expands arrays or nested structures into multiple rows. This is essential for working with JSON, arrays, and nested data formats common in modern data warehouses. Each platform has its own syntax but achieves the same goal.

Syntax by Platform

Snowflake:

SELECT value
FROM table,
LATERAL FLATTEN(input => array_column)

SELECT value
FROM table,
LATERAL FLATTEN(input => array_column)

SELECT value
FROM table,
LATERAL FLATTEN(input => array_column)

BigQuery:

SELECT element
FROM table,
UNNEST(array_column) AS

SELECT element
FROM table,
UNNEST(array_column) AS

SELECT element
FROM table,
UNNEST(array_column) AS

Databricks:

SELECT exploded_value
FROM table
LATERAL VIEW EXPLODE(array_column) AS

SELECT exploded_value
FROM table
LATERAL VIEW EXPLODE(array_column) AS

SELECT exploded_value
FROM table
LATERAL VIEW EXPLODE(array_column) AS

Platform-Specific Notes

Snowflake:

• FLATTEN works with VARIANT, ARRAY, and OBJECT types

• Supports recursive flattening with RECURSIVE => TRUE

• Returns structured data with SEQ, KEY, PATH, INDEX, VALUE, THIS

• Can handle deeply nested JSON

BigQuery:

• UNNEST has simpler syntax

• Works with ARRAY and STRUCT types

• Can unnest multiple arrays in same query with cross join behavior

• Supports WITH OFFSET for array indices

Databricks:

• EXPLODE is standard Spark SQL

• Can also use inline() for structs

• EXPLODE_OUTER keeps NULL values

• Compatible with Hive/Spark ecosystems

Example 1: Expand Order Items

Snowflake:

SELECT 
    o.order_id,
    f.value:product_name::STRING as product_name,
    f.value:quantity::INTEGER as quantity,
    f.value:price::FLOAT as price
FROM orders o,
LATERAL FLATTEN(input => o.items)

SELECT 
    o.order_id,
    f.value:product_name::STRING as product_name,
    f.value:quantity::INTEGER as quantity,
    f.value:price::FLOAT as price
FROM orders o,
LATERAL FLATTEN(input => o.items)

SELECT 
    o.order_id,
    f.value:product_name::STRING as product_name,
    f.value:quantity::INTEGER as quantity,
    f.value:price::FLOAT as price
FROM orders o,
LATERAL FLATTEN(input => o.items)

BigQuery:

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o,
UNNEST(o.items) as

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o,
UNNEST(o.items) as

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o,
UNNEST(o.items) as

Databricks:

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o
LATERAL VIEW EXPLODE(o.items) AS

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o
LATERAL VIEW EXPLODE(o.items) AS

SELECT 
    o.order_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders o
LATERAL VIEW EXPLODE(o.items) AS

Sample Data (orders table with nested items):

Result (all platforms):

Example 2: Flatten with Multiple Levels

Snowflake (Recursive Flatten):

SELECT 
    user_id,
    f1.value:name::STRING as category_name,
    f2.value::STRING as tag
FROM user_data,
LATERAL FLATTEN(input => categories) f1,
LATERAL FLATTEN(input => f1.value:tags)

SELECT 
    user_id,
    f1.value:name::STRING as category_name,
    f2.value::STRING as tag
FROM user_data,
LATERAL FLATTEN(input => categories) f1,
LATERAL FLATTEN(input => f1.value:tags)

SELECT 
    user_id,
    f1.value:name::STRING as category_name,
    f2.value::STRING as tag
FROM user_data,
LATERAL FLATTEN(input => categories) f1,
LATERAL FLATTEN(input => f1.value:tags)

BigQuery:

SELECT 
    user_id,
    category.name as category_name,
    tag
FROM user_data,
UNNEST(categories) as category,
UNNEST(category.tags) as

SELECT 
    user_id,
    category.name as category_name,
    tag
FROM user_data,
UNNEST(categories) as category,
UNNEST(category.tags) as

SELECT 
    user_id,
    category.name as category_name,
    tag
FROM user_data,
UNNEST(categories) as category,
UNNEST(category.tags) as

Sample Data (user_data table):

Result:

Use Cases

• Processing event logs with nested attributes

• Analyzing e-commerce order data with line items

• Working with API responses in JSON format

• Transforming denormalized data into relational format

• Expanding user behavior arrays (clicks, views, purchases)

Performance Considerations

• Flattening large arrays can significantly increase row count - use filters before flattening when possible

• In Snowflake, FLATTEN creates implicit joins which can be costly

• In BigQuery, UNNEST is optimized for columnar storage

• Consider materialized views for frequently flattened data