Data types in Snowflake - Time & Space Complexity

Time Complexity: Data types in Snowflake

O(n)

Understanding Time Complexity

When working with data types in Snowflake, it's important to understand how operations scale as data grows.

We want to know how the time to process data changes when using different data types.

Scenario Under Consideration

Analyze the time complexity of inserting and querying data with different data types.

-- Create a table with various data types
CREATE OR REPLACE TABLE example_table (
  id INT,
  name STRING,
  created_at TIMESTAMP,
  data VARIANT
);

-- Insert multiple rows
INSERT INTO example_table (id, name, created_at, data)
SELECT seq4(), 'name_' || seq4(), CURRENT_TIMESTAMP(), OBJECT_CONSTRUCT('key', seq4())
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

-- Query data filtering by id
SELECT * FROM example_table WHERE id < 500;
    

This sequence creates a table with different data types, inserts 1000 rows, and queries part of the data.

Identify Repeating Operations

Identify the API calls, resource provisioning, data transfers that repeat.

• Primary operation: Inserting rows and querying rows with filters.
• How many times: Bulk INSERT processes 1000 rows, query scans rows based on filter.

How Execution Grows With Input

As the number of rows increases, the time to insert and query grows roughly in proportion to the number of rows.

Input Size (n)	Approx. Api Calls/Operations
10	Bulk insert processes 10 rows, query scans up to 10 rows
100	Bulk insert processes 100 rows, query scans up to 100 rows
1000	Bulk insert processes 1000 rows, query scans up to 1000 rows

Pattern observation: Operations grow linearly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to insert or query data grows directly with the number of rows.

Common Mistake

[X] Wrong: "Using complex data types like VARIANT will make queries run in constant time regardless of data size."

[OK] Correct: Complex data types can add processing overhead, and query time still grows with the amount of data scanned.

Interview Connect

Understanding how data types affect operation time helps you design efficient databases and answer questions about scaling data workloads.

Self-Check

"What if we changed the data type of the 'id' column from INT to STRING? How would the time complexity change?"

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