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

Snowflake supports both structured and semi-structured data.

True

False

2 / 20

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

A specific key in a JSON object.

The first element in an ARRAY.

The entire JSON document in a SELECT statement.

An alias for column referencing.

3 / 20

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

4 / 20

To merge two JSON objects in Snowflake, such as { "name": "Alex" } and { "age": 25 }, which function is correct?

SELECT OBJECT_CONCAT(data1, data2);

SELECT JSON_COMBINE(data1, data2);

SELECT OBJECT_MERGE(data1, data2);

SELECT ARRAY_CONCAT(data1, data2);

5 / 20

To get the JSON structure { "city": "Paris", "country": "France" } as an array of its values, which function should be used?

TO_JSON_ARRAY(data)

OBJECT_VALUES(data)

ARRAY_CONSTRUCT(data)

ARRAY_TO_JSON(data)

6 / 20

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

True

False

7 / 20

What is the correct syntax to insert a new key-value pair into a JSON object { "name": "Alice" } in a column data?

SELECT ARRAY_INSERT(data, 'age', 25) FROM table;

SELECT OBJECT_INSERT(data, 'age', 25) FROM table;

SELECT OBJECT_CONSTRUCT(data, 'age', 25) FROM table;

SELECT TO_JSON(data, 'age', 25) FROM table;

8 / 20

The OBJECT_INSERT function in Snowflake:

Combines multiple JSON objects.

Inserts JSON data as a new row.

Adds a new key-value pair to an existing JSON object.

Transforms JSON data into a VARIANT.

9 / 20

How can you combine multiple JSON objects into a single JSON array in Snowflake?

VARIANT_COMBINE

OBJECT_CONCAT

ARRAY_AGG

ARRAY_CONSTRUCT

10 / 20

To parse a JSON string '{ "city": "New York" }' and store it as a VARIANT in Snowflake; which code is correct?

SELECT OBJECT_CONSTRUCT('{ "city": "New York" }');

SELECT TO_VARIANT('{ "city": "New York" }');

SELECT FLATTEN('{ "city": "New York" }');

SELECT PARSE_JSON('{ "city": "New York" }');

11 / 20

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

JSON_REMOVE

KEY_REMOVE

OBJECT_REMOVE

ARRAY_REMOVE

12 / 20

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

False

True

13 / 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(data) FROM table;

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

SELECT id FROM FLATTEN(INPUT => data);

SELECT FLATTEN(ARRAY_CONSTRUCT(data)) FROM table;

14 / 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;

15 / 20

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

OBJECT_AGG

VARIANT_PARSE

JSON_COMBINE

OBJECT_CONSTRUCT

16 / 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" }

profile

Sara

17 / 20

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

Converts a VARIANT type to a JSON string.

Flattens nested JSON data.

Aggregates values into JSON.

Converts JSON data into a VARIANT type.

18 / 20

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

True

False

19 / 20

JSON parsing in Snowflake always returns data in tabular format.

False

True

20 / 20

What does the PARSE_JSON function in Snowflake do?

Converts a VARIANT type to a string.

Transforms a JSON string into a Snowflake VARIANT type.

Flattens a JSON array into separate rows.

Removes null values from a JSON object.

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.