D3.5 - EXPLAIN HOW TO WORK AND LOAD SEMI-STRUCTURED DATA

Question 1

What are the supported file formats, data types and sizes in Snowflake?

Answer 1

• Semi-Structured Data:
• JSON
• ORC
• AVRO
• XML
• Parquet

Question 2

What is the VARIANT column?

Answer 2

• Has a 16MB limit on each individual row
• Useful when you are unsure of what data type to assign semi-structured data
• Data only uses native str and integer types

Question 3

What are the steps for loading semi-structured data into relational tables?

Answer 3

1. Load semi-structured data into a single VARIANT column
2. Extract and transform columns from semi-structured data into separate columns in target tables(s)
3. Detect and retrieve the column definitions from staged and semi-structured data files. Create Snowflake tables, external tables, or views from the column definitions. To save time, create tables with the columns definitions automatically retrieved from the staged file

Question 4

What is the FLATTEN function?

Answer 4

• The FLATTEN function explodes nested values into separate columns.
• You can use the function to filter query results in a WHERE clause

Question 5

How to use FLATTEN when working with unfamiliar semi-structured data?

Answer 5

• You can use the FLTTEN function with the RECURSIVE argument to return the list of distinct key names nested in all nested elements in an object