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

Need a 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
);

Need a 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
);

Need a 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

Need a hint?

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