Semi-structured data querying (JSON, Avro) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Extracting nested JSON values in Snowflake

Given a Snowflake table events with a VARIANT column data storing JSON objects, what is the output of this query?

SELECT data:user:name AS user_name FROM events WHERE data:user:age > 30;

A A list of ages where the user's name is greater than 30

B Syntax error due to incorrect JSON path syntax

C A list of user names where the user's age is greater than 30

D Empty result because VARIANT columns cannot be filtered

💡 Hint

Remember that Snowflake uses colon notation to access nested JSON keys inside VARIANT columns.

The query extracts the 'name' field from the nested 'user' object in the JSON stored in 'data'. The WHERE clause filters rows where the 'age' field inside 'user' is greater than 30. This returns user names matching the condition.

❓ Configuration

intermediate

Loading Avro data into Snowflake

Which Snowflake COPY INTO command correctly loads Avro files from an S3 bucket into a table named avro_table with a VARIANT column data?

A COPY INTO avro_table(data) FROM @my_s3_stage FILE_FORMAT = (TYPE = 'AVRO');

B COPY INTO avro_table(data) FROM @my_s3_stage FILE_FORMAT = (TYPE = 'PARQUET');

C COPY INTO avro_table(data) FROM @my_s3_stage FILE_FORMAT = (TYPE = 'CSV');

D COPY INTO avro_table FROM @my_s3_stage FILE_FORMAT = (TYPE = 'JSON');

💡 Hint

Avro files require specifying FILE_FORMAT type as 'AVRO' in Snowflake.

Option A correctly specifies the FILE_FORMAT as AVRO and loads data into the VARIANT column 'data'. Other options specify wrong file formats.

❓ Architecture

advanced

Optimizing queries on semi-structured data in Snowflake

You have a large Snowflake table with a VARIANT column storing JSON data. Which approach best improves query performance when filtering on nested JSON fields?

A Use a materialized view that extracts and stores the nested JSON fields as separate columns

B Store the JSON as plain text and parse it at query time

C Create a clustered index on the VARIANT column

D Disable automatic clustering on the table

💡 Hint

Materialized views can precompute and store extracted data for faster access.

Materialized views that extract nested JSON fields into separate columns allow Snowflake to optimize queries better than scanning VARIANT columns directly.

❓ security

advanced

Securing access to semi-structured data in Snowflake

Which Snowflake feature allows restricting access to specific keys inside a VARIANT column storing JSON data?

A Network policies restricting IP addresses

B Row Access Policies with JSON path filtering

C Column-level access control on the VARIANT column

D Masking Policies applied to the VARIANT column

💡 Hint

Masking policies can dynamically hide parts of data based on conditions.

Masking policies can be defined to mask or reveal specific keys inside VARIANT columns, controlling access at a fine-grained level.

🧠 Conceptual

expert

Understanding Snowflake's handling of nested JSON arrays

Given a VARIANT column data containing JSON arrays of objects, which Snowflake SQL function correctly flattens the array for querying individual elements?

A EXPLODE(data)

B LATERAL FLATTEN(input => data)

C ARRAY_EXPLODE(data)

D UNNEST(data)

💡 Hint

Snowflake uses a specific function to flatten VARIANT arrays in lateral joins.

Snowflake's LATERAL FLATTEN function is designed to expand arrays inside VARIANT columns into rows for querying.

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