Working with semi-structured data in Snowflake

Introduction to NoSQL

Jake Roach

Data Engineer

Semi-structured data in Snowflake

{
    "ISBN_13": "978-1685549596",
    "publisher": "Notion Press Media",
    "size": {
        "dimensions": "8.5 x 1.01 x 11 inches",
        "weight": "2.53 pounds"
    }
}

Allows data to be stored in "raw" format
VARIANT type
Store each object in a single column

Semi-structured data types in Snowflake

Snowflake also supports the OBJECT and ARRAY types

OBJECT is similar to dictionaries in Python
ARRAY is similar to lists in Python

VARIANT type

Stores semi-structured data in a single column

A Snowflake table with a column of type VARIANT.

Querying semi-structured data with bracket notation

A Snowflake table with a column of type VARIANT with two key-values pairs highlighted.

Query:

SELECT
    library['ISBN_13']
FROM books;

Result:

A result set of a query against a Snowflake table.

Querying semi-structured data with dot notation

A Snowflake table with a column of type VARIANT with four key-values pairs highlighted.

Query:

SELECT
    library:ISBN_13,
    library:publisher
FROM books;

Result:

A result set of a query against a Snowflake table.

Querying nested semi-structured data

A Snowflake table with a column of type VARIANT with six key-values pairs highlighted, with four of these key-value pairs being nested.

SELECT
    library:ISBN_13,
    library:size.dimensions,
    library:size.weight,
FROM books;

SELECT
    library["ISBN_13"],
    library["size"]["dimensions"],
    library["size"]["weight"],
FROM books;

A result set of a query against a Snowflake table.

Let's practice!

Introduction to NoSQL