Creating and managing shares in Snowflake - Performance & Efficiency

Time Complexity: Creating and managing shares

O(n)

Understanding Time Complexity

When creating and managing shares in Snowflake, it's important to know how the time to complete these tasks changes as you add more objects or accounts.

We want to understand how the number of operations grows when we create or update shares.

Scenario Under Consideration

Analyze the time complexity of the following operation sequence.

CREATE SHARE my_share;

GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT SELECT ON TABLE my_db.public.table1 TO SHARE my_share;
GRANT SELECT ON TABLE my_db.public.table2 TO SHARE my_share;

ALTER SHARE my_share ADD ACCOUNTS = ('account1');
ALTER SHARE my_share ADD ACCOUNTS = ('account2');
ALTER SHARE my_share ADD ACCOUNTS = ('account3');
    

This sequence creates a share, grants access to a database and its tables, then adds multiple accounts to the share.

Identify Repeating Operations

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

• Primary operation: Granting privileges on each table and adding accounts to the share.
• How many times: Once per table for grants, once per account for adding to the share.

How Execution Grows With Input

As the number of tables or accounts grows, the number of grant and add operations grows too.

Input Size (n)	Approx. Api Calls/Operations
10 tables/accounts	About 20 operations (10 grants + 10 account adds)
100 tables/accounts	About 200 operations
1000 tables/accounts	About 2000 operations

Pattern observation: The operations increase roughly in direct proportion to the number of tables and accounts.

Final Time Complexity

Time Complexity: O(n)

This means the time to create and manage shares grows linearly with the number of tables and accounts involved.

Common Mistake

[X] Wrong: "Adding more accounts to a share takes the same time no matter how many accounts there are."

[OK] Correct: Each account added requires a separate operation, so more accounts mean more time.

Interview Connect

Understanding how operations scale when managing shares helps you design efficient data sharing strategies and shows you can think about system behavior as it grows.

Self-Check

"What if we granted privileges on schemas instead of individual tables? How would the time complexity change?"