Data types in Snowflake - Deep Dive

Overview - Data types in Snowflake

What is it?

Data types in Snowflake define the kind of information that can be stored in a table's column. They tell Snowflake how to interpret and manage the data, like numbers, text, dates, or special formats. Each data type has rules about what values are allowed and how much space they take. Understanding data types helps organize data correctly and makes queries work as expected.

Why it matters

Without clear data types, data would be messy and confusing, like mixing apples and oranges in one basket without labels. This would cause errors, slow queries, and wrong results. Data types ensure data is stored efficiently and accurately, enabling fast and reliable analysis. They also help prevent mistakes, like putting a phone number where a date should be.

Where it fits

Before learning data types, you should understand basic database concepts like tables and columns. After mastering data types, you can learn about data modeling, query optimization, and advanced Snowflake features like semi-structured data handling and data transformations.

Mental Model

Core Idea

Data types are labels that tell Snowflake what kind of data each piece is, so it can store, organize, and process it correctly.

Think of it like...

Imagine a library where every book is sorted by genre: fiction, history, science. Data types are like these genres, helping the library know where to put each book and how to find it later.

┌───────────────┐
│   Table       │
│ ┌───────────┐ │
│ │ Column 1  │ │  ← Data type: NUMBER
│ │ Column 2  │ │  ← Data type: VARCHAR
│ │ Column 3  │ │  ← Data type: DATE
│ └───────────┘ │
└───────────────┘
Each column has a data type that defines what data it holds.

Build-Up - 7 Steps

1

Foundation

Understanding Basic Data Types

Concept: Introduce the simplest data types like numbers, text, and dates.

Snowflake supports common data types such as: - NUMBER: for whole or decimal numbers - VARCHAR: for text of variable length - DATE: for calendar dates These types help Snowflake know how to store and compare data.

Result

You can create table columns that hold numbers, text, or dates correctly.

Knowing basic data types is essential because they form the foundation for all data storage and querying.

2

Foundation

Fixed vs Variable Length Types

Concept: Explain the difference between fixed and variable length data types.

VARCHAR stores text with variable length, saving space by only using what is needed. CHAR stores fixed-length text, always using the same space even if the text is shorter. NUMBER types can also have precision and scale to fix size and decimal places.

Result

You understand how choosing the right type affects storage and performance.

Choosing between fixed and variable length types impacts how efficiently Snowflake uses storage and processes data.

3

Intermediate

Handling Semi-Structured Data Types

🤔Before reading on: do you think Snowflake treats JSON as plain text or a special data type? Commit to your answer.

Concept: Introduce Snowflake's support for semi-structured data types like VARIANT, OBJECT, and ARRAY.

Snowflake can store JSON, XML, and Avro data using special types: - VARIANT: holds any semi-structured data - OBJECT: stores key-value pairs - ARRAY: stores ordered lists These types allow flexible data storage without strict schemas.

Result

You can store and query complex data formats directly in Snowflake.

Understanding semi-structured types unlocks powerful ways to work with flexible data without losing query performance.

4

Intermediate

Precision and Scale in Numeric Types

🤔Before reading on: does increasing precision always increase storage size? Commit to your answer.

Concept: Explain how NUMBER types use precision (total digits) and scale (digits after decimal) to control numeric data.

NUMBER(p,s) defines a number with p total digits and s digits after the decimal point. For example, NUMBER(5,2) can store values like 123.45. Higher precision allows more digits but may use more storage.

Result

You can define numeric columns that fit your data needs exactly.

Knowing precision and scale helps prevent data truncation and optimizes storage.

5

Intermediate

Date and Time Data Types Explained

Concept: Describe Snowflake's date and time types and their differences.

Snowflake supports: - DATE: calendar date only - TIME: time of day - TIMESTAMP: date and time combined - TIMESTAMP_TZ: timestamp with time zone These types help store and compare time-related data accurately.

Result

You can choose the right type for your time data and handle time zones properly.

Understanding time types prevents errors in time calculations and supports global applications.

6

Advanced

Optimizing Storage with Data Types

🤔Before reading on: do you think using larger data types always improves performance? Commit to your answer.

Concept: Teach how choosing appropriate data types affects storage size and query speed.

Using smaller or more precise data types reduces storage and speeds up queries. For example, using NUMBER(5,0) instead of NUMBER(38,0) saves space. Avoid using VARCHAR for numeric data. Also, semi-structured types can be costly if overused.

Result

You can design tables that are both efficient and fast.

Knowing how data types impact storage and performance helps build scalable Snowflake solutions.

7

Expert

Internal Handling of Semi-Structured Data

🤔Before reading on: do you think Snowflake stores JSON as plain text or parses it internally? Commit to your answer.

Concept: Reveal how Snowflake parses and stores semi-structured data internally for fast querying.

Snowflake parses VARIANT, OBJECT, and ARRAY data into a compressed binary format called 'micro-partitions'. This allows fast access to nested fields without scanning entire documents. It also supports automatic schema-on-read, meaning you don't define structure upfront.

Result

You understand why querying JSON in Snowflake is fast and flexible.

Understanding internal parsing explains how Snowflake balances flexibility with performance for semi-structured data.

Under the Hood

Snowflake stores data in micro-partitions, which are small, compressed files. Each column's data type determines how data is encoded and compressed. For example, numbers use efficient binary formats, text uses dictionary encoding, and semi-structured data is parsed into a tree-like binary structure. This allows Snowflake to skip irrelevant data during queries and optimize storage.

Why designed this way?

Snowflake was designed to handle both structured and semi-structured data efficiently in a cloud environment. Using micro-partitions and type-specific encoding balances fast query performance with flexible data storage. Alternatives like storing everything as text would be simpler but much slower and more costly.

┌───────────────┐
│   Table       │
│ ┌───────────┐ │
│ │ Column 1  │ │
│ │ (NUMBER)  │ │
│ └───────────┘ │
│ ┌───────────┐ │
│ │ Column 2  │ │
│ │ (VARCHAR) │ │
│ └───────────┘ │
│ ┌───────────┐ │
│ │ Column 3  │ │
│ │ (VARIANT) │ │
│ └───────────┘ │
└─────┬─────────┘
      │
      ▼
┌───────────────────────────────┐
│ Micro-partitions (compressed)  │
│ ┌─────────────┐ ┌───────────┐ │
│ │ Binary NUM  │ │ Encoded   │ │
│ │ data       │ │ VARCHAR   │ │
│ └─────────────┘ └───────────┘ │
│ ┌───────────────────────────┐ │
│ │ Parsed Semi-structured     │ │
│ │ binary tree (VARIANT)      │ │
│ └───────────────────────────┘ │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Is VARCHAR unlimited in length by default in Snowflake? Commit to yes or no.

Common Belief: VARCHAR columns can store unlimited text without specifying length.

Reality: VARCHAR in Snowflake can store up to 16 MB of text by default, which is very large but not unlimited. Specifying length limits storage and can improve performance.

Why it matters: Assuming unlimited length can lead to inefficient storage and unexpected errors if data exceeds limits.

Quick: Does Snowflake store JSON as plain text or parsed data internally? Commit to your answer.

Common Belief: Snowflake stores JSON and other semi-structured data as plain text blobs.

Reality: Snowflake parses semi-structured data into a compressed binary format for fast querying and storage efficiency.

Why it matters: Believing it's plain text leads to underestimating Snowflake's performance and capabilities with semi-structured data.

Quick: Does increasing NUMBER precision always increase storage size linearly? Commit to yes or no.

Common Belief: Higher precision in NUMBER types always means proportionally more storage used.

Reality: Snowflake uses optimized storage for NUMBER types, so storage increase is not always linear with precision.

Why it matters: Misunderstanding this can cause over-optimization or unnecessary data type restrictions.

Quick: Can you store time zone information in a DATE type? Commit yes or no.

Common Belief: DATE type stores time zone information along with the date.

Reality: DATE stores only the calendar date without time or time zone; TIMESTAMP_TZ is needed for time zone info.

Why it matters: Using DATE for time zone data causes incorrect time calculations and confusion.

Expert Zone

1

Snowflake's VARIANT type supports automatic schema evolution, allowing new fields in JSON without altering table schema.

2

Choosing between TIMESTAMP_NTZ, TIMESTAMP_LTZ, and TIMESTAMP_TZ affects how time zones are handled and displayed, critical for global apps.

3

Snowflake compresses data differently based on data type, so mixing types in a column (like storing numbers as text) reduces compression efficiency.

When NOT to use

Avoid using overly generic types like VARCHAR for numeric or date data, as it disables type-specific optimizations. For heavy JSON processing, consider external tools or flattening data for better performance.

Production Patterns

In production, numeric columns are carefully sized with precision and scale to save space. Semi-structured data is stored in VARIANT columns with frequent use of Snowflake's JSON functions. Time zone-aware timestamps are used for global user data. Data types are chosen to balance flexibility, storage cost, and query speed.

Connections

Relational Database Data Types

Data types in Snowflake build on and extend traditional relational database types.

Understanding classic database types helps grasp Snowflake's types and their cloud-specific extensions.

JSON Data Structures

Snowflake's semi-structured data types directly support JSON and similar formats.

Knowing JSON structure aids in using VARIANT, OBJECT, and ARRAY types effectively.

Human Language Classification

Just as languages classify words into nouns, verbs, and adjectives, data types classify data into categories for meaning and use.

Recognizing classification systems in language helps understand why data types organize data for clarity and function.

Common Pitfalls

#1 Storing numeric data as VARCHAR causes slow queries and errors.

Wrong approach:

CREATE TABLE sales (amount VARCHAR);

Correct approach:

CREATE TABLE sales (amount NUMBER);

Root cause:

Misunderstanding that text types can replace numeric types leads to inefficient storage and broken numeric operations.

#2 Using DATE type to store timestamps with time zones causes wrong time data.

Wrong approach:

CREATE TABLE events (event_time DATE); -- storing timestamps here

Correct approach:

CREATE TABLE events (event_time TIMESTAMP_TZ);

Root cause:

Confusing date-only types with timestamp types leads to loss of time and zone information.

#3 Not specifying precision and scale for NUMBER leads to unexpected rounding or truncation.

Wrong approach:

CREATE TABLE payments (price NUMBER); -- no precision or scale

Correct approach:

CREATE TABLE payments (price NUMBER(10,2));

Root cause:

Ignoring precision and scale causes data quality issues and surprises in calculations.

Key Takeaways

Data types in Snowflake define how data is stored, interpreted, and processed, ensuring accuracy and efficiency.

Choosing the right data type affects storage size, query speed, and data integrity.

Snowflake supports both traditional structured types and flexible semi-structured types like VARIANT for JSON data.

Understanding precision, scale, and time zone handling is critical for numeric and temporal data correctness.

Proper use of data types unlocks Snowflake's performance and flexibility, while misuse leads to errors and inefficiency.

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