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?

Lists all JSON object paths.

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

Fetches file paths from external storage.

Parses JSON data into a VARIANT.

2 / 20

JSON parsing in Snowflake always returns data in tabular format.

False

True

3 / 20

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

JSON_ARRAY

TO_JSON

ARRAY_AGG

ARRAY_CONSTRUCT

4 / 20

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

VARIANT

ARRAY

VARCHAR

BINARY

5 / 20

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

A single JSON object.

A table of individual array elements.

An aggregated JSON array.

A concatenated JSON string.

6 / 20

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

The first element in an ARRAY.

A specific key in a JSON object.

The entire JSON document in a SELECT statement.

An alias for column referencing.

7 / 20

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

{ "name": "Sara" }

profile

Sara

name

8 / 20

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

Combines multiple JSON objects into one VARIANT type.

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

Transforms VARIANT data into scalar data types.

Converts JSON data to XML format.

9 / 20

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

OBJECT_CONSTRUCT

VARIANT_PARSE

OBJECT_AGG

JSON_COMBINE

10 / 20

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

True

False

11 / 20

Which query will return the number of elements in a JSON array stored in a column data as [1, 2, 3]?

SELECT ARRAY_SIZE(PARSE_JSON(data)) FROM table;

SELECT ARRAY_SIZE(data:array) FROM table;

SELECT ARRAY_SIZE(data) FROM table;

SELECT ARRAY_LENGTH(data) FROM table;

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

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

SELECT TO_STRING(data) FROM table;

SELECT ARRAY_TO_STRING(data) FROM table;

14 / 20

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

False

True

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

16 / 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 ARRAY_INSERT(data, 'age', 25) FROM table;

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

17 / 20

To retrieve a specific key-value pair from a JSON column named data which syntax is correct?

SELECT data.key FROM table;

SELECT key->data FROM table;

SELECT data:key FROM table;

SELECT key:data FROM table;

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

Flattens a JSON array into separate rows.

Removes null values from a JSON object.

19 / 20

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

True

False

20 / 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 JSON_EXISTS(data, 'key');

SELECT OBJECT_HAS_KEY(data, 'key');

SELECT IFNULL(data['key'], NULL) 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.