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
Introduction to NoSQL

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.

Introduction to NoSQL

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.

Introduction to NoSQL

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.

Introduction to NoSQL

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.

Introduction to NoSQL

Let's practice!

Introduction to NoSQL

Preparing Video For Download...