Semi-structured data querying (JSON, Avro) in Snowflake - Step-by-Step Execution

Process Flow - Semi-structured data querying (JSON, Avro)

Load semi-structured data into VARIANT column

↓

Use Snowflake SQL functions to parse data

↓

Extract fields using dot notation or functions

↓

Filter or transform data as needed

↓

Return structured query results

This flow shows how Snowflake loads semi-structured data, parses it, extracts fields, and returns query results.

Execution Sample

Snowflake

SELECT
  data:name AS name,
  data:age AS age
FROM people_json
WHERE data:age > 30;

This query extracts 'name' and 'age' from JSON data stored in a VARIANT column and filters for age over 30.

Process Table

Step	Action	Input Data	Operation	Result
1	Read table 'people_json'	{"name": "Alice", "age": 25}	Load VARIANT column	Row with VARIANT JSON data
2	Read table 'people_json'	{"name": "Bob", "age": 35}	Load VARIANT column	Row with VARIANT JSON data
3	Extract 'name' and 'age' from VARIANT	Row with VARIANT JSON data	Use dot notation data:name, data:age	name='Alice', age=25 for first row
4	Extract 'name' and 'age' from VARIANT	Row with VARIANT JSON data	Use dot notation data:name, data:age	name='Bob', age=35 for second row
5	Apply filter WHERE data:age > 30	name='Alice', age=25	Check age > 30	False, exclude row
6	Apply filter WHERE data:age > 30	name='Bob', age=35	Check age > 30	True, include row
7	Return query results	Filtered rows	Output selected columns	Result: name='Bob', age=35

💡 Query ends after filtering all rows and returning matching results.

Status Tracker

Variable	Start	After Row 1	After Row 2	Final
data	VARIANT column with JSON	{"name": "Alice", "age": 25}	{"name": "Bob", "age": 35}	N/A
name	N/A	Alice	Bob	Bob
age	N/A	25	35	35
filter_result	N/A	False	True	True

Key Moments - 3 Insights

Why does the query exclude Alice's row even though her data is loaded?

Because the filter condition data:age > 30 is False for Alice (age 25), so her row is excluded as shown in execution_table step 5.

How does Snowflake access fields inside the JSON stored in VARIANT?

Snowflake uses dot notation like data:name or functions to extract fields from the VARIANT column, as shown in steps 3 and 4.

What type of data does the VARIANT column hold?

VARIANT holds semi-structured data like JSON or Avro, allowing flexible schema storage, as seen in step 1 and 2.

Visual Quiz - 3 Questions

Look at the execution_table at step 5, what is the filter_result for Alice's row?

A True

B False

C Null

D Error

💡 Hint

Check the 'filter_result' column in variable_tracker after Row 1 and execution_table step 5.

At which step does the query exclude a row based on age?

A Step 5

B Step 4

C Step 3

D Step 7

💡 Hint

Look at execution_table where filtering happens using WHERE clause.

If the filter changed to data:age > 20, which rows would be included?

A Only Bob's row

B Only Alice's row

C Both Alice's and Bob's rows

D No rows

💡 Hint

Refer to variable_tracker ages and filter condition logic.

Concept Snapshot

Snowflake stores semi-structured data in VARIANT columns.
Use dot notation (data:field) to extract JSON or Avro fields.
Apply SQL filters on extracted fields.
Query returns structured results from semi-structured data.
No schema needed upfront; flexible querying.
Ideal for JSON, Avro, XML data formats.

Full Transcript

This lesson shows how Snowflake handles semi-structured data like JSON or Avro stored in VARIANT columns. The data is loaded as-is without fixed schema. Using SQL dot notation, fields inside the JSON are extracted. Then filters like WHERE data:age > 30 select rows. The execution table traces each step: loading rows, extracting fields, filtering, and returning results. Variables like 'name' and 'age' track extracted values. Key moments clarify why some rows are excluded and how dot notation works. The quiz tests understanding of filtering and data extraction steps. This approach lets you query flexible data formats easily in Snowflake.

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