SOL-C01 Question #70: Real Exam Question with Answer & Explanation

Question

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 by a specific role, DATA ADMIN'. Furthermore, they need to ensure that all newly created tables within this schema are automatically granted SELECT privileges to the 'REPORTING ROLE. Which of the following steps are required to accomplish this, following Snowflake best practices for security and governance?

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

Explanation

Future grants are the most efficient and secure way to automatically grant privileges on newly created objects. By using 'GRANT SELECT ON FUTURE TABLES IN SCHEMA SALES_DATA.TRANSACTIONS TO ROLE REPORTING_ROLE;', any new table created in that schema will automatically have SELECT privileges granted to the REPORTING ROLE. Option A only grants usage, not select privileges. Option C and D are inefficient and complex. Option E grants ownership which is not required, and does not address the 'future' requirement.

No community discussion yet for this question.