Semi-structured data querying (JSON, Avro) in Snowflake - Commands & Configuration

Introduction

Sometimes data comes in formats that are not simple tables, like JSON or Avro. Snowflake lets you easily read and query this kind of data without changing it into tables first.

When you receive data from web apps that send information as JSON.

When you want to analyze logs stored in Avro format without converting them.

When you need to combine structured data with flexible, nested data in one query.

When you want to quickly explore data from APIs that return JSON responses.

When you want to store and query event data that has varying fields.

Commands

Create a table with a VARIANT column to store JSON data. VARIANT can hold any semistructured data.

Terminal

CREATE OR REPLACE TABLE events_json (data VARIANT);

Expected Output

Table EVENTS_JSON successfully created.

Insert a JSON record into the VARIANT column using PARSE_JSON to convert the string into JSON format.

Terminal

INSERT INTO events_json (data) VALUES (PARSE_JSON('{"user":"alice","action":"login","time":"2024-06-01T12:00:00Z"}'));

Expected Output

1 row inserted.

Query the JSON data by extracting the 'user' and 'action' fields from the VARIANT column.

Terminal

SELECT data:user AS user, data:action AS action FROM events_json;

Expected Output

USER  | ACTION

alice | login

Create a file format object for Avro files so Snowflake knows how to read them.

Terminal

CREATE OR REPLACE FILE FORMAT avro_format TYPE = 'AVRO';

Expected Output

File format AVRO_FORMAT successfully created.

Create a stage pointing to an S3 bucket with Avro files using the Avro file format.

Terminal

CREATE OR REPLACE STAGE avro_stage URL='s3://example-bucket/avro-data/' FILE_FORMAT = avro_format;

Expected Output

Stage AVRO_STAGE successfully created.

Create a table with a VARIANT column to load Avro data into.

Terminal

CREATE OR REPLACE TABLE events_avro (data VARIANT);

Expected Output

Table EVENTS_AVRO successfully created.

Load Avro files from the stage into the table. Snowflake automatically parses Avro into VARIANT.

Terminal

COPY INTO events_avro FROM @avro_stage FILE_FORMAT = (FORMAT_NAME = 'avro_format');

Expected Output

Copy into EVENTS_AVRO completed. 5 files loaded, 0 errors.

Query nested fields inside the Avro data stored in VARIANT, showing event ID and status.

Terminal

SELECT data:event_id AS event_id, data:details:status AS status FROM events_avro LIMIT 3;

Expected Output

EVENT_ID | STATUS

123      | success

124      | failed

125      | success

Key Concept

If you remember nothing else from this pattern, remember: Snowflake's VARIANT type lets you store and query JSON or Avro data directly without converting it first.

Common Mistakes

Trying to insert JSON data as plain text without using PARSE_JSON.

Snowflake treats it as a string, so you cannot query JSON fields properly.

Always use PARSE_JSON to convert JSON strings into VARIANT format before inserting.

Not creating or specifying the correct file format when loading Avro files.

Snowflake cannot parse the files correctly and loading fails or data is incorrect.

Create a FILE FORMAT object with TYPE='AVRO' and use it when creating the stage or loading data.

Querying nested JSON or Avro fields without using the colon syntax (data:field).

Snowflake will not find the nested data and returns NULL or errors.

Use colon notation to access nested fields inside VARIANT columns.

Summary

Create tables with VARIANT columns to store JSON or Avro data.

Use PARSE_JSON to insert JSON data correctly.

Create file formats and stages to load Avro files.

Query nested fields using colon notation on VARIANT columns.

Practice

1. What is the Snowflake data type used to store semi-structured data like JSON or Avro?

easy

A. INTEGER

B. VARIANT

C. VARCHAR

D. BOOLEAN

Solution

1. Step 1: Understand Snowflake data types

   Snowflake uses specific data types for different data. VARIANT is designed for semi-structured data.

2. Step 2: Identify the correct type for JSON/Avro

   VARIANT can store JSON, Avro, XML, and other semi-structured formats directly.

3. Final Answer:

   VARIANT -> Option B

4. Quick Check:

   Semi-structured data type = VARIANT [OK]

Hint: Remember VARIANT stores JSON/Avro data in Snowflake [OK]

Common Mistakes:

• Choosing VARCHAR which stores plain text only
• Confusing INTEGER or BOOLEAN with semi-structured types
• Thinking JSON needs special external storage

2. Which of the following is the correct way to extract the value of the key name from a VARIANT column data containing JSON in Snowflake as a string?

easy

A. data:name

B. data['name']

C. data:name::string

D. data->'name'

Solution

1. Step 1: Understand JSON field extraction syntax in Snowflake

   Snowflake uses colon : to access JSON keys inside VARIANT columns.

2. Step 2: Cast extracted value to string for proper type

   Using ::string casts the extracted value to string, which is often needed for correct results.

3. Final Answer:

   data:name::string -> Option C

4. Quick Check:

   Extract and cast JSON key = data:name::string [OK]

Hint: Use colon and cast (::string) to get JSON string value [OK]

Common Mistakes:

• Using incorrect arrow syntax like data->'name'
• Not casting extracted value to string
• Using bracket notation data['name'] without casting to string

3. Given the JSON data stored in a VARIANT column data:

{"user": {"id": 101, "active": true}}

What will the query SELECT data:user:id::int FROM users; return?

medium

A. 101

B. "101"

C. true

D. NULL

Solution

1. Step 1: Access nested JSON key

   The query accesses user object then id key inside it.

2. Step 2: Cast the extracted value to integer

   The ::int cast converts the value 101 to integer type.

3. Final Answer:

   101 -> Option A

4. Quick Check:

   Nested JSON id cast to int = 101 [OK]

Hint: Use colon to access nested keys and cast to int for numbers [OK]

Common Mistakes:

• Returning string "101" without cast
• Confusing boolean true with id value
• Getting NULL due to wrong key access

4. You run the query SELECT data:user:active FROM users; but get NULL values even though the JSON has "active": true. What is the likely cause?

medium

A. Missing cast to BOOLEAN

B. JSON key is case-sensitive and should be capitalized

C. Incorrect key path syntax

D. Column data is not VARIANT type

Solution

1. Step 1: Check data type of column

   If the column is not VARIANT, JSON path extraction returns NULL.

2. Step 2: Confirm correct key path and case

   The key path user:active is correct and JSON keys are case-sensitive but here lowercase matches JSON.

3. Final Answer:

   Column data is not VARIANT type -> Option D

4. Quick Check:

   Non-VARIANT column returns NULL on JSON path [OK]

Hint: Ensure column is VARIANT type to query JSON paths [OK]

Common Mistakes:

• Assuming missing cast causes NULL for boolean
• Using wrong key path syntax
• Ignoring data type of the column

5. You have a VARIANT column data storing JSON arrays like

{"items": [{"id": 1}, {"id": 2}, {"id": 3}]}

. Which query correctly extracts all id values from the items array as separate rows?

hard

A. SELECT value:id::int FROM users, LATERAL FLATTEN(input => data:items);

B. SELECT data:items:id FROM users;

C. SELECT data:items[0]:id FROM users;

D. SELECT FLATTEN(data:items):id FROM users;

Solution

1. Step 1: Use FLATTEN to expand JSON array

   FLATTEN function explodes the array into rows, each with a value field.

2. Step 2: Extract id from each value and cast to int

   Access value:id and cast to integer for each row.

3. Final Answer:

   SELECT value:id::int FROM users, LATERAL FLATTEN(input => data:items); -> Option A

4. Quick Check:

   Use FLATTEN with LATERAL and extract id from value [OK]

Hint: Use LATERAL FLATTEN to turn JSON arrays into rows [OK]

Common Mistakes:

• Trying to access array elements without FLATTEN
• Using incorrect syntax like data:items:id
• Not casting extracted values to int