Data types in Snowflake - Commands & Configuration

Introduction

Data types in Snowflake define the kind of data you can store in each column of a table. Choosing the right data type helps keep your data organized and makes queries faster and more accurate.

When you want to store numbers like prices or counts in your database.

When you need to save text such as names, descriptions, or addresses.

When you want to record dates and times for events or transactions.

When you need to store true or false values for conditions.

When you want to save large objects like images or documents in a table.

Config File - create_table.sql

create_table.sql

CREATE OR REPLACE TABLE example_table (
  id INT,
  name STRING,
  price NUMBER(10,2),
  created_at TIMESTAMP_NTZ,
  is_active BOOLEAN,
  data BINARY
);

This SQL file creates a table named example_table with columns using different Snowflake data types:

• INT for whole numbers.
• STRING for text.
• NUMBER(10,2) for numbers with up to 10 digits and 2 decimals.
• TIMESTAMP_NTZ for date and time without timezone.
• BOOLEAN for true/false values.
• BINARY for storing raw data like files.

Commands

This command creates a new table named example_table with columns of various data types to store different kinds of data.

Terminal

snowsql -q "CREATE OR REPLACE TABLE example_table (id INT, name STRING, price NUMBER(10,2), created_at TIMESTAMP_NTZ, is_active BOOLEAN, data BINARY);"

Expected Output

Done. Snowflake successfully executed the query.

This command shows the structure of example_table, listing each column and its data type so you can verify the table was created correctly.

Terminal

snowsql -q "DESC TABLE example_table;"

Expected Output

name       | type           | kind   | null? | default | primary key | unique key | check | expression | comment

-----------|----------------|--------|-------|---------|-------------|------------|-------|------------|--------

id         | INT            | COLUMN | Y     |         | N           | N          |       |            | 

name       | STRING         | COLUMN | Y     |         | N           | N          |       |            | 

price      | NUMBER(10,2)   | COLUMN | Y     |         | N           | N          |       |            | 

created_at | TIMESTAMP_NTZ  | COLUMN | Y     |         | N           | N          |       |            | 

is_active  | BOOLEAN        | COLUMN | Y     |         | N           | N          |       |            | 

data       | BINARY         | COLUMN | Y     |         | N           | N          |       |            | 

This command inserts a row into example_table with values matching the data types defined for each column.

Terminal

snowsql -q "INSERT INTO example_table (id, name, price, created_at, is_active, data) VALUES (1, 'Coffee', 3.50, CURRENT_TIMESTAMP(), TRUE, TO_BINARY('example'));"

Expected Output

Done. 1 row inserted.

This command retrieves all rows from example_table to show the stored data and confirm the insert worked.

Terminal

snowsql -q "SELECT * FROM example_table;"

Expected Output

ID | NAME   | PRICE | CREATED_AT                  | IS_ACTIVE | DATA

---|--------|-------|-----------------------------|-----------|-------

1  | Coffee | 3.50  | 2024-06-01 12:00:00.000000 | true      | 6578616D706C65

Key Concept

If you remember nothing else from this pattern, remember: choosing the right data type for each column keeps your data accurate and your queries efficient.

Common Mistakes

Using STRING data type for numbers or dates.

This causes slower queries and can lead to errors when sorting or calculating values.

Use NUMBER for numbers and TIMESTAMP for dates to let Snowflake handle them properly.

Not specifying precision and scale for NUMBER data type.

This can lead to unexpected rounding or storage of more digits than needed.

Always define precision and scale like NUMBER(10,2) for prices with two decimals.

Storing true/false values as strings like 'true' or 'false'.

This wastes space and makes logical operations harder.

Use BOOLEAN data type for true/false values.

Summary

Create tables with columns using appropriate Snowflake data types like INT, STRING, NUMBER, TIMESTAMP, BOOLEAN, and BINARY.

Use SQL commands to create the table, check its structure, insert data, and query the data.

Choosing the right data type helps keep data accurate and queries fast.

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