Snowflake SnowPro Core Prep — Part 10: Wrangling the “Messy” Stuff (Semi-Structured Data)

Photo by Jesus Hilario H. on Unsplash

If you’ve ever tried to load a JSON file into a traditional database, you know the pain. You usually have to “shred” or pre-process the data into a relational format before it ever touches a table. Snowflake flips the script: it allows you to load semi-structured data into a single column and query it using standard SQL.

1. The Super-Type: The VARIANT Column

In Snowflake, we have a special data type called VARIANT. Think of it as a “box” that can hold up to 16MB of compressed data in any format — JSON, Avro, ORC, Parquet, or XML.

• The Magic: When you load JSON into a VARIANT column, Snowflake doesn’t just treat it as a giant string of text. Under the hood, it optimizes the storage by looking for common paths and storing them columnarly. This means querying a JSON field is almost as fast as querying a regular table.

2. Querying with “Dot Notation”

Once your JSON is in a VARIANT column (let’s call it RAW_DATA), you don't need fancy tools to read it. You just use dots or brackets.

• Example: If your JSON looks like {"customer": {"id": 101, "name": "Saket"}}, you just write: SELECT RAW_DATA:customer.name FROM my_table;
• The Result: Snowflake returns “Saket.” It’s that simple.

3. The FLATTEN Function: Exploding Arrays

Data gets tricky when you have arrays (lists of items inside a single record). For example, a customer might have multiple “Items” inside one JSON object. To analyze these individually, you need each item to have its own row.

Let’s look at a quick example: Imagine your RAW_DATA column has this JSON:

To turn that one row into three separate rows, you use FLATTEN with a LATERAL join:

What’s happening here?

• **input =>**: You’re telling Snowflake exactly which array to "un-nest."
• **LATERAL**: This is the "glue" that joins the original customer name to every new row created.
• **value**: This is a special keyword that represents the actual content (e.g., "Laptop").
• **::string: Remember to cast** your data so it turns into a clean string instead of a "Variant" type.

4. Casting Your Data

When you pull data out of a VARIANT column, Snowflake treats it as a “Variant” type. If you want to use it in a calculation (like adding tax to a price), you have to cast it.

• Syntax: RAW_DATA:price::float or RAW_DATA:id::integer.
• Exam Tip: Always remember the double colon :: for casting!

Summary for the Exam

• VARIANT is the go-to type for semi-structured data (max 16MB).
• Dot Notation (:) is used to traverse paths.
• FLATTEN turns nested arrays into separate rows.
• Key Columns: When using Flatten, remember VALUE (the data) and INDEX (the position).

Checkout the older posts in the series —

Snowflake SnowPro Core Prep — Part 9: Tables and the “Magic” of Micro-partitions