Flattening Complex Data in Snowflake: A Guide to the FLATTEN Function

Data in the real world often arrives in messy, nested structures. JSON, arrays, and objects are common ways of organizing information, but they can be tricky to query and analyze in their raw form. That’s where Snowflake’s FLATTEN function comes to the rescue!

What is the FLATTEN Function?

The FLATTEN function is a robust table function in Snowflake that helps you break down complex hierarchical data into a simple, relational format. Think of it as a tool to untangle nested structures and make them easier to work with. FLATTEN works on the following data types:

• VARIANT: Semi-structured data formats like JSON
• OBJECT: Key-value pair structures
• ARRAY: Collections of elements

Unpacking the FLATTEN Syntax

The core syntax of the FLATTEN function is:

SQL

FLATTEN(INPUT => <input_expression>, PATH => <path_to_element>)

Use code with caution.

content_copy

Let’s break down the components:

• INPUT => <input_expression>: This is the VARIANT, OBJECT, or ARRAY column you want to flatten.
• PATH => <path_to_element>: This is an optional parameter. It specifies the element within the input data structure you intend to flatten.

Output Columns

FLATTEN generates the following output columns:

• KEY: The object’s key or an array element’s index.
• VALUE: The key’s corresponding value (in an object) or the element at a given index (in an array).
• INDEX: The array index (if applicable).
• PATH: The path within the original data structure.
• SEQ: A sequence number for ordering
• THIS: A reference to the original JSON object or array element.

FLATTEN in Action: Examples

1. Flattening a JSON Column

SQL

SELECT 

    id, 

    f.value: name AS name,

    f.value: age AS age

FROM customer,

LATERAL FLATTEN(INPUT => customer_info) f;

Use code with caution.

content_copy

Assume you have a ‘customer’ table with a JSON column ‘customer_info’ storing name and age. This query breaks out the name and age into separate columns.

2. Flattening an Array of Objects

SQL

SELECT 

    product_id,

    f.value: color AS color,

    f.value: size AS size

FROM inventory,

LATERAL FLATTEN(INPUT => available_options) f;

Use code with caution.

content_copy

Imagine you have an inventory table with an array column ‘available_options’ containing product color and size variations. This query extracts color and size into separate columns for easier analysis.

Why Flatten Data?

• Simplified Querying: Flattened data makes it easier to write SQL queries that filter, join, and aggregate across the elements of your nested structures.
• Improved Performance: In some cases, flattening can streamline query execution.
• Data Normalization: Flattening aligns with relational database principles, making your data more compatible with traditional analysis tools.

Let’s Get Practical!

If you want to try this, Snowflake provides a sample dataset- “SNOWFLAKE_SAMPLE_DATA”—experiment by flattening columns within this dataset.

In Conclusion

The FLATTEN function in Snowflake is your secret weapon for taming complex data. Embrace the power of flattening, and you’ll quickly analyze hierarchical data structures.