A data engineer is tasked with migrating a legacy data warehouse to Snowflake. They need to create a database named 'SALES DATA and a schema named 'TRANSACTIONS within it. The schema should be managed

Question

Options

• ACreate the database and schema with `OWNER = DATA ADMIN'. Use 'GRANT USAGE ON
• BCreate the database and schema. Then, use a future grant: 'GRANT SELECT ON FUTURE
• CCreate the database and schema with 'OWNER = DATA_ADMIN'. Then, create a stored
• DCreate the database and schema. Then, schedule a task that runs every minute and grants
• ECreate the database and schema with 'OWNER = DATA ADMIN'. Use 'GRANT OWNERSHIP ON

• A
  5% (1)
• B
  52% (11)
• C
  24% (5)
• D
  10% (2)
• E
  10% (2)

Explanation

Option B is correct because Snowflake's FUTURE GRANTS feature (GRANT SELECT ON FUTURE TABLES IN SCHEMA ... TO ROLE REPORTING_ROLE) is the native, purpose-built mechanism for automatically applying privileges to objects that don't yet exist - no manual intervention needed each time a new table is created. Combined with GRANT OWNERSHIP ON SCHEMA ... TO ROLE DATA_ADMIN, this cleanly satisfies both requirements in two straightforward statements.

Why the distractors fail:

• A - OWNER = DATA_ADMIN is not valid Snowflake DDL syntax; ownership is transferred with GRANT OWNERSHIP, not set inline at creation. The rest of the option only addresses USAGE, not auto-granting SELECT on future tables.
• C - A stored procedure can technically work, but it's unnecessary complexity when FUTURE GRANTS already handles this declaratively. Snowflake best practices favor built-in governance features over custom procedural workarounds.
• D - Scheduling a task to poll and grant every minute is an anti-pattern: it's delayed, wasteful, and fragile - a table could exist ungoverned for up to a minute. This is exactly the problem FUTURE GRANTS was designed to eliminate.
• E - Granting OWNERSHIP to REPORTING_ROLE is dangerously overpermissive; a reporting role needs only SELECT, not schema ownership. Ownership grants full control including the ability to drop or restructure objects.

Memory tip: When you see "automatically grant on not-yet-created objects," think FUTURE GRANTS - it's Snowflake's "set it and forget it" privilege mechanism. If the word future appears in the requirement, the answer almost always involves GRANT ... ON FUTURE ....

No community discussion yet for this question.