Snowflake Semi-Structured Data: Intermediate-Level Quiz

This quiz tests your knowledge and practical skills in working with semi-structured data in Snowflake, focusing on formats such as JSON, Avro, and XML. You can expect a mix of theory, syntax-based questions, and code completion tasks to simulate real-world scenarios you might encounter. This quiz is ideal for intermediate users as it will challenge your understanding of semi-structured data handling techniques in Snowflake. It ensures you're well-prepared to tackle data integration and transformation tasks in any Snowflake environment.

1 / 20

To transform a JSON array into separate rows, which clause should you use with FLATTEN?

CROSS JOIN

OUTER APPLY

JOIN

LEFT JOIN

2 / 20

Given JSON data in a VARIANT column data containing { "employees": [ { "name": "Jane" }, { "name": "Tom" } ] }, which query will return the names of all employees?

SELECT data:employees[*].name FROM table;

SELECT data['employees']['name'] FROM table;

SELECT data:employees:name FROM table;

SELECT data.employees.name FROM table;

3 / 20

Snowflake’s VARIANT type can hold any data type, including semi-structured and structured data.

False

True

4 / 20

What does the PARSE_JSON function in Snowflake do?

Transforms a JSON string into a Snowflake VARIANT type.

Converts a VARIANT type to a string.

Removes null values from a JSON object.

Flattens a JSON array into separate rows.

5 / 20

The TO_VARIANT function converts JSON to an XML object in Snowflake.

True

False

6 / 20

To navigate deeply nested JSON, which symbol references nested keys?

7 / 20

Given a JSON column data containing { "name": "John", "age": 30 }, which query correctly extracts the namefield?

SELECT data->'name' FROM table;

SELECT data.name FROM table;

SELECT data['name'] FROM table;

SELECT data:name FROM table;

8 / 20

Which of the following functions would you use to create a new JSON object in Snowflake?

JSON_COMBINE

VARIANT_PARSE

OBJECT_AGG

OBJECT_CONSTRUCT

9 / 20

Using data.key in a SELECT statement will return a JSON element's value if it exists within the JSON document.

True

False

10 / 20

Snowflake automatically detects the schema of semi-structured data formats like JSON.

True

False

11 / 20

Which of the following will extract zip from the JSON object { "address": { "city": "Seattle", "zip": 98101 } } in a data column?

SELECT data['address']['zip'] FROM table;

SELECT data->'address'->'zip' FROM table;

SELECT data.address.zip FROM table;

SELECT data:address:zip FROM table;

12 / 20

Which function removes a key from a JSON object in Snowflake?

JSON_REMOVE

ARRAY_REMOVE

KEY_REMOVE

OBJECT_REMOVE

13 / 20

A Snowflake VARIANT type can store nested JSON objects and arrays.

True

False

14 / 20

What is the outcome of using the TO_JSON function in Snowflake?

Aggregates values into JSON.

Flattens nested JSON data.

Converts a VARIANT type to a JSON string.

Converts JSON data into a VARIANT type.

15 / 20

JSON data can only be queried in Snowflake using the FLATTEN function.

False

True

16 / 20

Given a JSON array [{ "id": 1 }, { "id": 2 }, { "id": 3 }] in a VARIANT column data, which query flattens the array and returns each id?

SELECT FLATTEN(ARRAY_CONSTRUCT(data)) FROM table;

SELECT FLATTEN(data) FROM table;

SELECT id FROM FLATTEN(INPUT => data);

SELECT VALUE:id FROM TABLE(FLATTEN(INPUT => data));

17 / 20

In Snowflake, the $1 symbol is used to refer to:

An alias for column referencing.

The entire JSON document in a SELECT statement.

The first element in an ARRAY.

A specific key in a JSON object.

18 / 20

In Snowflake, PARSE_JSON is used to create a JSON array.

True

False

19 / 20

JSON parsing in Snowflake always returns data in tabular format.

False

True

20 / 20

In the following query, which value will be selected? SELECT data:profile:name FROM table; assuming data has { "profile": { "name": "Sara" } }.

name

{ "name": "Sara" }

Sara

profile

Your score is

The average score is 0%

How well do you know Snowflake? Here’s a quiz on Snowflake Semi-Structured Data for those with an intermediate understanding of Snowflake.

With Snowflake, you can work with structured data like rows and tables and complex, semi-structured formats like JSON, Avro, and XML. As a result of Snowflake’s flexibility, it is a favourite among data professionals who work with diverse types of data.

This quiz will help you confidently work with Snowflake’s semi-structured data features.

Good luck.