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

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

OBJECT_CONSTRUCT

VARIANT_PARSE

OBJECT_AGG

JSON_COMBINE

2 / 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(data:array) FROM table;

SELECT ARRAY_LENGTH(data) FROM table;

SELECT ARRAY_SIZE(PARSE_JSON(data)) FROM table;

SELECT ARRAY_SIZE(data) FROM table;

3 / 20

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

Converts JSON data to XML format.

Combines multiple JSON objects into one VARIANT type.

Transforms VARIANT data into scalar data types.

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

4 / 20

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

False

True

5 / 20

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

False

True

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

7 / 20

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

SELECT key:data FROM table;

SELECT data.key FROM table;

SELECT data:key FROM table;

SELECT key->data FROM table;

8 / 20

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

JSON_REMOVE

KEY_REMOVE

OBJECT_REMOVE

ARRAY_REMOVE

9 / 20

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

VARIANT

BINARY

VARCHAR

ARRAY

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

SELECT FLATTEN(data) FROM table;

SELECT id FROM FLATTEN(INPUT => data);

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

12 / 20

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

True

False

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

14 / 20

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

ARRAY_AGG

OBJECT_CONCAT

ARRAY_CONSTRUCT

VARIANT_COMBINE

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

SELECT OBJECT_HAS_KEY(data, 'key');

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

16 / 20

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

A table of individual array elements.

A single JSON object.

An aggregated JSON array.

A concatenated JSON string.

17 / 20

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

True

False

18 / 20

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

True

False

19 / 20

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

False

True

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

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.