Semi-structured data querying (JSON, Avro) in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is semi-structured data?

Semi-structured data is data that does not follow a strict table format but has some organizational properties like tags or keys, for example, JSON or Avro files.

beginner

How does Snowflake store JSON data?

Snowflake stores JSON data in a VARIANT column type, which allows flexible storage and querying of nested JSON structures.

intermediate

Which Snowflake function extracts a value from a JSON object?

The function is called GET_PATH or the simpler : operator, for example, data:field extracts the value of 'field' from the JSON stored in 'data'.

intermediate

What is Avro and why is it used?

Avro is a compact, fast, binary data serialization format often used for big data. It supports schema evolution and is efficient for storage and transport.

intermediate

How do you query nested JSON arrays in Snowflake?

You use the LATERAL FLATTEN function to expand arrays into rows, allowing you to query each element individually.

Which Snowflake data type is best for storing JSON data?

A VARIANT

B VARCHAR

C INTEGER

D BOOLEAN

VARIANT is designed to store semi-structured data like JSON in Snowflake.

What does the Snowflake function LATERAL FLATTEN do?

A Converts JSON arrays into multiple rows

B Compresses JSON data

C Encrypts data

D Joins two tables

LATERAL FLATTEN expands JSON arrays into rows for easier querying.

Avro files are primarily used because they are:

A Plain text and human-readable

B Binary and support schema evolution

C Only for images

D Unstructured data format

Avro is a binary format that supports schema changes over time.

How do you extract a nested field 'name' from a JSON column 'data' in Snowflake?

A data.name

B data->name

C data:name

D data[name]

Snowflake uses the colon operator to extract JSON fields, e.g., data:name.

Which of the following is NOT a characteristic of semi-structured data?

A Often stored as JSON or Avro

B Flexible schema

C Contains nested structures

D Fixed schema with strict columns

Semi-structured data does not have a fixed schema with strict columns.

Explain how Snowflake handles querying JSON data stored in a VARIANT column.

Think about how you would get data from a nested folder in your computer.

Describe the advantages of using Avro format for big data storage and querying.

Consider why a small, flexible box is better for shipping many items.