Creating tables (permanent, temporary, transient) in Snowflake - Performance & Efficiency

Time Complexity: Creating tables (permanent, temporary, transient)

O(n)

Understanding Time Complexity

When creating tables in Snowflake, it is important to understand how the time taken grows as you create more tables.

We want to know how the number of operations changes when creating many tables.

Scenario Under Consideration

Analyze the time complexity of the following operation sequence.

-- Create a permanent table
CREATE TABLE my_permanent_table (id INT, name STRING);

-- Create a temporary table
CREATE TEMPORARY TABLE my_temp_table (id INT, name STRING);

-- Create a transient table
CREATE TRANSIENT TABLE my_transient_table (id INT, name STRING);

-- Repeat creation for multiple tables
DECLARE i INT DEFAULT 1;
WHILE i <= 100 DO
  EXECUTE IMMEDIATE 'CREATE TABLE table_' || i || ' (id INT, value STRING)';
  LET i = i + 1;
END WHILE;

This sequence creates different types of tables and then creates many tables in a loop.

Identify Repeating Operations

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

• Primary operation: The CREATE TABLE command executed once per table.
• How many times: Once for each table created, for example 100 times in the loop.

How Execution Grows With Input

Each new table requires a separate CREATE TABLE operation, so the total work grows as you create more tables.

Input Size (n)	Approx. Api Calls/Operations
10	10 CREATE TABLE commands
100	100 CREATE TABLE commands
1000	1000 CREATE TABLE commands

Pattern observation: The number of operations grows directly with the number of tables created.

Final Time Complexity

Time Complexity: O(n)

This means the time to create tables grows in a straight line as you create more tables.

Common Mistake

[X] Wrong: "Creating multiple tables happens all at once, so time stays the same no matter how many tables."

[OK] Correct: Each CREATE TABLE is a separate operation that takes time, so more tables mean more total time.

Interview Connect

Understanding how operations scale helps you design efficient database setups and shows you can think about system behavior as it grows.

Self-Check

"What if we created tables in parallel instead of one after another? How would the time complexity change?"