A Data Engineer used a secondary role to create a dynamic table using a CREATE DYNAMIC TABLE command. The secondary role has been granted the USAGE privilege, but the primary role has not. What…

Question

Options

• AThe dynamic table will not initialize or refresh
• BThe dynamic table will initialize but will not refresh.
• CThe dynamic table will initialize and can be refreshed, but net on a schedule.
• DThe dynamic table will initialize and refresh only once, future refresh attempts will fail.

• A
  13% (3)
• B
  79% (19)
• C
  4% (1)
• D
  4% (1)

Explanation

Snowflake Dynamic Tables & Role Privileges

Option B is correct because Snowflake dynamic tables use two distinct processes for their lifecycle. Initialization occurs at creation time within the active session, where the secondary role (which has USAGE) is in scope - so the table populates successfully. Refresh, however, is a background scheduled operation that runs under the primary role only. Since the primary role lacks USAGE privilege, every subsequent refresh attempt fails silently, leaving the table frozen at its initial state.

Why the distractors are wrong:

• A - Initialization does succeed because the secondary role's USAGE privilege is available during the CREATE statement execution.
• C - The dynamic table cannot refresh at all, not just "not on a schedule." Refreshes fail regardless of trigger method (scheduled or manual).
• D - No refresh succeeds after initialization; there is no "one successful refresh" grace period. The primary role's missing privilege blocks all refresh attempts from the start.

Memory tip: Think "Create = your session, Refresh = your primary role." If anything about your primary role is misconfigured, the table will look healthy after creation but silently go stale - a common gotcha in Snowflake environments where secondary roles are used for object creation.

No community discussion yet for this question.