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.

Practice

1. Which Snowflake data type is best suited to store true or false values?

easy

A. BOOLEAN

B. VARCHAR

C. NUMBER

D. DATE

Solution

1. Step 1: Understand the purpose of BOOLEAN data type

   BOOLEAN is designed to store logical values: true or false.

2. Step 2: Compare with other data types

   VARCHAR stores text, NUMBER stores numbers, and DATE stores dates, none are for true/false.

3. Final Answer:

   BOOLEAN -> Option A

4. Quick Check:

   True/False = BOOLEAN [OK]

Hint: True/false values always use BOOLEAN type [OK]

Common Mistakes:

• Choosing VARCHAR for true/false values
• Using NUMBER to represent logical states
• Confusing DATE with BOOLEAN

2. Which of the following is the correct way to declare a VARCHAR column with a maximum length of 100 characters in Snowflake?

easy

A. VARCHAR{100}

B. VARCHAR100

C. VARCHAR[100]

D. VARCHAR(100)

Solution

1. Step 1: Recall Snowflake syntax for VARCHAR

   Snowflake uses parentheses to specify length, e.g., VARCHAR(100).

2. Step 2: Identify incorrect syntax

   Options with brackets or no parentheses are invalid in Snowflake.

3. Final Answer:

   VARCHAR(100) -> Option D

4. Quick Check:

   Length in parentheses = VARCHAR(100) [OK]

Hint: Use parentheses for length in VARCHAR [OK]

Common Mistakes:

• Using brackets or braces instead of parentheses
• Omitting parentheses for length
• Writing VARCHAR100 as one word

3. What will be the result of this Snowflake SQL query?

SELECT CAST('2024-06-15' AS DATE) AS my_date;

medium

A. 2024-06-15

B. '2024-06-15'

C. Error: Invalid cast

D. NULL

Solution

1. Step 1: Understand CAST to DATE

   CAST converts a string in 'YYYY-MM-DD' format to a DATE type in Snowflake.

2. Step 2: Check the output format

   The DATE value is returned as 2024-06-15 without quotes.

3. Final Answer:

   2024-06-15 -> Option A

4. Quick Check:

   CAST string 'YYYY-MM-DD' to DATE = date value [OK]

Hint: CAST string 'YYYY-MM-DD' to DATE returns date value [OK]

Common Mistakes:

• Expecting quotes around the date output
• Thinking CAST causes error for valid date strings
• Assuming NULL if format looks like a string

4. You try to insert into a table with this column definition:

price NUMBER(5,2)

But Snowflake gives an error. What is the likely cause?

medium

A. The scale (2) cannot be greater than precision (5)

B. NUMBER(5,2) is invalid syntax in Snowflake

C. NUMBER(5,2) means 5 digits total, 2 after decimal, so max 999.99 allowed

D. NUMBER cannot have scale and precision specified

Solution

1. Step 1: Understand NUMBER(precision, scale)

   Precision is total digits, scale is digits after decimal.

2. Step 2: Calculate max value for NUMBER(5,2)

   Max number is 999.99 (3 digits before decimal, 2 after).

3. Final Answer:

   NUMBER(5,2) means 5 digits total, 2 after decimal, so max 999.99 allowed -> Option C

4. Quick Check:

   Precision=5, Scale=2 means max 999.99 [OK]

Hint: Precision includes all digits; scale is decimal digits [OK]

Common Mistakes:

• Thinking NUMBER(5,2) syntax is invalid
• Confusing precision and scale order
• Assuming scale can be greater than precision

5. You want to store a timestamp with timezone in Snowflake. Which data type should you use to keep both date, time, and timezone information?

hard

A. TIMESTAMP_NTZ

B. TIMESTAMP_TZ

C. DATE

D. VARCHAR

Solution

1. Step 1: Review Snowflake timestamp types

   TIMESTAMP_NTZ stores timestamp without timezone; TIMESTAMP_TZ stores with timezone.

2. Step 2: Identify correct type for timezone info

   Only TIMESTAMP_TZ keeps timezone data along with date and time.

3. Final Answer:

   TIMESTAMP_TZ -> Option B

4. Quick Check:

   Timestamp with timezone = TIMESTAMP_TZ [OK]

Hint: Use TIMESTAMP_TZ for timezone-aware timestamps [OK]

Common Mistakes:

• Choosing TIMESTAMP_NTZ which ignores timezone
• Using DATE which lacks time info
• Storing timestamps as VARCHAR