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 get the JSON structure { "city": "Paris", "country": "France" } as an array of its values, which function should be used?

TO_JSON_ARRAY(data)

ARRAY_CONSTRUCT(data)

ARRAY_TO_JSON(data)

OBJECT_VALUES(data)

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

What does the Snowflake function GET_PATH do?

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

Fetches file paths from external storage.

Lists all JSON object paths.

Parses JSON data into a VARIANT.

4 / 20

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

SELECT JSON_COMBINE(data1, data2);

SELECT OBJECT_CONCAT(data1, data2);

SELECT OBJECT_MERGE(data1, data2);

SELECT ARRAY_CONCAT(data1, data2);

5 / 20

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

SELECT ARRAY_TO_JSON(data) FROM table;

SELECT ARRAY_AGG(data) FROM table;

SELECT ARRAY_CONSTRUCT(data) FROM table;

SELECT OBJECT_VALUES(data) FROM table;

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

7 / 20

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

True

False

8 / 20

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

SELECT data:2 FROM table;

SELECT data::3 FROM table;

SELECT GET(data, 2) FROM table;

SELECT data[3] FROM table;

9 / 20

Snowflake charges a premium for storing semi-structured data. True or False?

True

False

10 / 20

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

False

True

11 / 20

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

True

False

12 / 20

Snowflake supports both structured and semi-structured data.

False

True

13 / 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.

14 / 20

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

False

True

15 / 20

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

False

True

16 / 20

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

SELECT OBJECT_HAS_KEY(data, 'key');

SELECT JSON_EXISTS(data, 'key');

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

SELECT HAS_KEY(data, 'key');

17 / 20

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

OBJECT_REMOVE

JSON_REMOVE

KEY_REMOVE

ARRAY_REMOVE

18 / 20

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

ARRAY_CONSTRUCT

VARIANT_COMBINE

OBJECT_CONCAT

ARRAY_AGG

19 / 20

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

CROSS JOIN

JOIN

OUTER APPLY

LEFT JOIN

20 / 20

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

profile

name

{ "name": "Sara" }

Sara

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.