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

Question

You are designing a data pipeline in Snowflake where raw data is ingested into a staging schema, transformed, and then loaded into a production schema. You want to ensure that only authorized users can access the data in the production schema, and that data analysts can only query the data but not modify it. You have two roles: `DATA ENGINEER (responsible for loading and transforming data) and 'DATA ANALYST (responsible for querying data). Which of the following sets of grants provide the MOST secure and appropriate access control for this scenario? Assume that 'PRODUCTION DB' and 'STAGING DB' databases already exist.

Options

• AOption A
• BOption B
• COption C
• DOption D
• EOption E

Explanation

Option D provides the most secure and appropriate access control. It uses GRANT SELECT ON FUTURE TABLES' for DATA ANALYST ensuring they automatically have access to new tables without needing explicit grants. It also uses GRANT INSERT, UPDATE, DELETE ON FUTURE TABLES' for `DATA_ENGINEER so they are able to load and transform data to any new table within Option A only grants select on current tables. Option B grants insert, update and delete on all current tables which is not ideal. Option C grants ALL privileges which violates principle of least privilege. Option E does not grand any rights to schema for DATA_ANALYST , it will not work. Also, does not cover future grants.

No community discussion yet for this question.