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.