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

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.

2 / 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 id FROM FLATTEN(INPUT => data);

SELECT FLATTEN(ARRAY_CONSTRUCT(data)) FROM table;

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

SELECT FLATTEN(data) FROM table;

3 / 20

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

4 / 20

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

False

True

5 / 20

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

Aggregates values into JSON.

Converts JSON data into a VARIANT type.

Converts a VARIANT type to a JSON string.

Flattens nested JSON data.

6 / 20

If the FLATTEN function is used on a column containing a JSON array, the result will be:

A single JSON object.

An aggregated JSON array.

A table of individual array elements.

A concatenated JSON string.

7 / 20

What does the PARSE_JSON function in Snowflake do?

Removes null values from a JSON object.

Transforms a JSON string into a Snowflake VARIANT type.

Flattens a JSON array into separate rows.

Converts a VARIANT type to a string.

8 / 20

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

ARRAY_AGG

OBJECT_CONCAT

VARIANT_COMBINE

ARRAY_CONSTRUCT

9 / 20

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

False

True

10 / 20

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

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

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

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

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

11 / 20

What does the FLATTEN function in Snowflake do when working with semi-structured data?

Extracts data from nested structures, turning arrays into tabular format.

Combines multiple JSON objects into one VARIANT type.

Transforms VARIANT data into scalar data types.

Converts JSON data to XML format.

12 / 20

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

SELECT ARRAY_CONCAT(data1, data2);

SELECT OBJECT_CONCAT(data1, data2);

SELECT OBJECT_MERGE(data1, data2);

SELECT JSON_COMBINE(data1, data2);

13 / 20

Which function is used to aggregate values into a JSON array in Snowflake?

TO_JSON

JSON_ARRAY

ARRAY_AGG

ARRAY_CONSTRUCT

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

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

16 / 20

What does the Snowflake function GET_PATH do?

Retrieves a value at a specified path within a JSON object.

Parses JSON data into a VARIANT.

Lists all JSON object paths.

Fetches file paths from external storage.

17 / 20

To convert a JSON object { "type": "car", "model": "Ford" } in a column data into a string, which function should be used?

SELECT JSON_TO_STRING(data) FROM table;

SELECT ARRAY_TO_STRING(data) FROM table;

SELECT TO_STRING(data) FROM table;

SELECT TO_JSON(data) FROM table;

18 / 20

Which Snowflake function should be used to check if a key exists in a JSON object stored in a VARIANT column data?

SELECT HAS_KEY(data, 'key');

SELECT IFNULL(data['key'], NULL) FROM table;

SELECT OBJECT_HAS_KEY(data, 'key');

SELECT JSON_EXISTS(data, 'key');

19 / 20

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

True

False

20 / 20

How would you retrieve the third element from a JSON array [10, 20, 30, 40] stored in column data?

SELECT GET(data, 2) FROM table;

SELECT data[3] FROM table;

SELECT data:2 FROM table;

SELECT data::3 FROM table;

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.