Data types in Snowflake - Mini Project: Build & Apply

Data types in Snowflake

📖 Scenario: You are working as a cloud data engineer. Your task is to create a table in Snowflake to store employee information. You need to define the correct data types for each column to ensure data is stored efficiently and correctly.

🎯 Goal: Create a Snowflake table named employees with appropriate data types for each column.

📋 What You'll Learn

Create a table named employees

Include columns: employee_id, first_name, last_name, email, hire_date, and salary

Use the correct Snowflake data types for each column

💡 Why This Matters

🌍 Real World

Defining correct data types in Snowflake tables is essential for storing data efficiently and ensuring data integrity in cloud data warehouses.

💼 Career

Cloud data engineers and database administrators frequently create and manage tables with appropriate data types to support analytics and reporting.

1

Create the table structure

Write a CREATE TABLE statement to create a table called employees with columns employee_id, first_name, and last_name. Use NUMBER data type for employee_id and VARCHAR(50) for first_name and last_name.

Snowflake

-- Write the CREATE TABLE statement with employee_id, first_name, and last_name columns
-- Your code here

Hint

Use NUMBER for numeric IDs and VARCHAR with length for text columns.

2

Add email and hire_date columns

Modify the employees table creation statement to add columns email with data type VARCHAR(100) and hire_date with data type DATE.

Snowflake

CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    -- Add email and hire_date columns below
    -- Your code here
);

Hint

Use VARCHAR(100) for email and DATE for hire_date.

3

Add salary column with decimal type

Add a salary column to the employees table with data type NUMBER(10,2) to store salaries with two decimal places.

Snowflake

CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    -- Add salary column below
    -- Your code here
);

Hint

Use NUMBER(10,2) to allow salaries with up to 10 digits and 2 decimal places.

4

Complete the table creation with all columns

Ensure the full CREATE TABLE employees statement includes all columns: employee_id NUMBER, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100), hire_date DATE, and salary NUMBER(10,2).

Snowflake

-- Verify the full CREATE TABLE statement is complete and correct
-- Your code here

Hint

Double-check all columns and data types are included correctly.

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