Row access policies in Snowflake - Time & Space Complexity

Time Complexity: Row access policies

O(n)

Understanding Time Complexity

When using row access policies in Snowflake, it's important to understand how the time to check access grows as data size increases.

We want to know how the number of checks or operations changes when more rows are queried.

Scenario Under Consideration

Analyze the time complexity of applying a row access policy during a SELECT query.

CREATE OR REPLACE ROW ACCESS POLICY filter_policy AS (user_role STRING) RETURNS BOOLEAN ->
  user_role = CURRENT_ROLE();

SELECT * FROM sales_data
  WHERE filter_policy(user_role);
    

This sequence defines a policy that filters rows based on the user's role and applies it during a data query.

Identify Repeating Operations

Look at what happens repeatedly when the query runs.

• Primary operation: The row access policy function is evaluated for each row scanned.
• How many times: Once per row scanned in the table.

How Execution Grows With Input

As the number of rows grows, the policy check runs more times.

Input Size (n)	Approx. Api Calls/Operations
10	10 policy checks
100	100 policy checks
1000	1000 policy checks

Pattern observation: The number of policy evaluations grows directly with the number of rows scanned.

Final Time Complexity

Time Complexity: O(n)

This means the time to apply the row access policy grows linearly as the number of rows increases.

Common Mistake

[X] Wrong: "The row access policy runs only once per query regardless of rows."

[OK] Correct: The policy is checked for each row to decide if it should be included, so it runs many times, not just once.

Interview Connect

Understanding how row access policies scale helps you design secure and efficient data access controls in real projects.

Self-Check

"What if the row access policy used a complex subquery inside? How would that affect the time complexity?"