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

What does the Snowflake function GET_PATH do?

Parses JSON data into a VARIANT.

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

Fetches file paths from external storage.

Lists all JSON object paths.

2 / 20

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

True

False

3 / 20

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

SELECT JSON_EXISTS(data, 'key');

SELECT OBJECT_HAS_KEY(data, 'key');

SELECT HAS_KEY(data, 'key');

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

4 / 20

Snowflake supports both structured and semi-structured data.

True

False

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

6 / 20

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

SELECT data[3] FROM table;

SELECT data::3 FROM table;

SELECT data:2 FROM table;

SELECT GET(data, 2) FROM table;

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

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

SELECT FLATTEN(data) FROM table;

8 / 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 FLATTEN('{ "city": "New York" }');

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

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

9 / 20

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

Converts JSON data to XML format.

Transforms VARIANT data into scalar data types.

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

Combines multiple JSON objects into one VARIANT type.

10 / 20

The OBJECT_INSERT function in Snowflake:

Inserts JSON data as a new row.

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

Transforms JSON data into a VARIANT.

Combines multiple JSON objects.

11 / 20

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

JSON_COMBINE

OBJECT_AGG

OBJECT_CONSTRUCT

VARIANT_PARSE

12 / 20

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

True

False

13 / 20

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

Converts JSON data into a VARIANT type.

Flattens nested JSON data.

Converts a VARIANT type to a JSON string.

Aggregates values into JSON.

14 / 20

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

15 / 20

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

True

False

16 / 20

Which of the following will convert {"id": 1, "value": "test"} to an array like [1, "test"]?

SELECT ARRAY_AGG(data) FROM table;

SELECT ARRAY_CONSTRUCT(data) FROM table;

SELECT OBJECT_VALUES(data) FROM table;

SELECT ARRAY_TO_JSON(data) FROM table;

17 / 20

Which Snowflake data type is most suitable for storing JSON data?

ARRAY

VARCHAR

BINARY

VARIANT

18 / 20

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

A single JSON object.

A concatenated JSON string.

An aggregated JSON array.

A table of individual array elements.

19 / 20

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

ARRAY_REMOVE

OBJECT_REMOVE

JSON_REMOVE

KEY_REMOVE

20 / 20

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

LEFT JOIN

JOIN

OUTER APPLY

CROSS JOIN

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.