Data types in Snowflake - Step-by-Step Execution

Process Flow - Data types in Snowflake

Start: Define Column

↓

Choose Data Type

↓

Assign Storage Format

↓

Validate Data Type

↓

Create Column

↓

Store Data

↓

End

This flow shows how Snowflake assigns and validates data types when creating table columns, ensuring data is stored correctly.

Execution Sample

Snowflake

CREATE TABLE users (
  id INT,
  name STRING,
  signup_date DATE
);

Creates a table 'users' with three columns, each assigned a specific Snowflake data type.

Process Table

Step	Action	Column	Data Type Chosen	Validation Result	Outcome
1	Define column	id	INT	Valid	Column 'id' created with INT type
2	Define column	name	STRING	Valid	Column 'name' created with STRING type
3	Define column	signup_date	DATE	Valid	Column 'signup_date' created with DATE type
4	Store data	id	INT	N/A	Data stored as integer
5	Store data	name	STRING	N/A	Data stored as string
6	Store data	signup_date	DATE	N/A	Data stored as date
7	End	N/A	N/A	N/A	Table 'users' created successfully

💡 All columns validated and data types assigned correctly; table creation completed.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
id	undefined	INT assigned	INT assigned	INT assigned	INT assigned
name	undefined	undefined	STRING assigned	STRING assigned	STRING assigned
signup_date	undefined	undefined	undefined	DATE assigned	DATE assigned

Key Moments - 2 Insights

Why can't I assign a string value to an INT column?

Because the execution_table shows 'id' column is validated as INT at step 1, Snowflake expects only integer values there.

What happens if I try to use a data type Snowflake does not support?

The flow shows an 'Error: Invalid Type' branch after validation fails, so the table creation will stop with an error.

Visual Quiz - 3 Questions

Look at the execution_table, what data type is assigned to the 'name' column at step 2?

A INT

B STRING

C DATE

D BOOLEAN

💡 Hint

Check the 'Data Type Chosen' column for step 2 in the execution_table.

At which step does Snowflake validate the 'signup_date' column data type?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look for the 'Validation Result' for 'signup_date' in the execution_table.

If you tried to assign a FLOAT data type to the 'id' column instead of INT, what would happen?

A Validation would fail and table creation would stop

B Data would be stored as INT anyway

C Column 'id' would be created as FLOAT without error

D Snowflake would convert FLOAT to STRING automatically

💡 Hint

FLOAT is a valid Snowflake data type, so validation passes and the column would be created as FLOAT.

Concept Snapshot

Snowflake data types define how data is stored in columns.
Common types: INT (numbers), STRING (text), DATE (dates).
Assign types when creating tables.
Snowflake validates types before storing data.
Invalid types cause errors and stop table creation.

Full Transcript

This visual execution traces how Snowflake handles data types when creating a table. First, each column is defined with a chosen data type like INT, STRING, or DATE. Snowflake validates each type to ensure it is supported. If valid, the column is created and data can be stored accordingly. If an invalid type is used, the process stops with an error. The example shows a table 'users' with three columns, each assigned a valid data type, resulting in successful table creation.

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