Data types in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Identify the correct Snowflake data type for storing variable-length text

Which Snowflake data type is best suited for storing text strings of varying length up to 16 MB?

A CHAR

B VARCHAR

C TEXT

D STRING

💡 Hint

Think about which type allows flexible length and is optimized for text.

VARCHAR is the Snowflake data type designed for variable-length text up to 16 MB. CHAR is fixed length, TEXT is not a Snowflake type, and STRING is an alias but VARCHAR is preferred.

❓ Configuration

intermediate

Determine the output data type of a Snowflake expression

What is the resulting data type of this Snowflake expression?

SELECT 123.45 + 67;

Snowflake

SELECT 123.45 + 67;

A VARCHAR

B NUMBER(10,2)

C INTEGER

D FLOAT

💡 Hint

Consider how Snowflake handles numeric literals with decimals.

Adding a decimal number and an integer results in a FLOAT type in Snowflake, which can represent fractional numbers.

❓ Architecture

advanced

Choose the best Snowflake data type for storing JSON data

You need to store semi-structured JSON data efficiently in Snowflake. Which data type should you use?

A ARRAY

B OBJECT

C VARIANT

D VARCHAR

💡 Hint

Snowflake has special types for semi-structured data.

VARIANT is the Snowflake data type designed to store semi-structured data like JSON efficiently. OBJECT and ARRAY are subtypes but VARIANT is the container type. VARCHAR stores JSON as plain text without parsing.

❓ security

advanced

Identify the Snowflake data type that supports encryption at rest natively

Which Snowflake data type inherently supports encryption at rest without additional configuration?

A BINARY

B VARIANT

C TIMESTAMP_NTZ

D BOOLEAN

💡 Hint

Think about data types that store raw bytes.

Snowflake encrypts all data at rest by default, including BINARY data. BINARY stores raw byte data. Other types are also encrypted but BINARY is the type for raw bytes.

❓ service_behavior

expert

Predict the behavior when inserting incompatible data types in Snowflake

What happens if you try to insert a string value 'abc' into a Snowflake column defined as NUMBER(5,0)?

Snowflake

CREATE TABLE test_num (id NUMBER(5,0));
INSERT INTO test_num VALUES ('abc');

A Insertion fails with a conversion error

B String 'abc' is truncated and stored as NULL

C String 'abc' is stored as 0

D Insertion succeeds storing 'abc' as a string

💡 Hint

Consider how Snowflake handles type mismatches during insert.

Snowflake attempts to convert the string to a number but fails, causing an error and rejecting the insert.