SnowPro Advanced: Data Engineer · 10% of the exam

Security: free practice questions

5 sample questions from our 25-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. A security audit requires demonstrating which Snowflake users accessed a specific sensitive table named FINANCE.PUBLIC.PAYROLL during the last 30 days, including the queries they ran. Which Snowflake resources should the auditor query? (Select TWO)

A. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY to retrieve the SQL text and user for all queries executed✓ Correct
B. SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY to identify which base objects (tables) were read or written and by which user✓ Correct
C. SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY to retrieve table-level access events
D. SNOWFLAKE.ACCOUNT_USAGE.STAGE_USAGE_HISTORY to identify file-level access
E. INFORMATION_SCHEMA.TABLE_PRIVILEGES to list current privilege grants on the table

Explanation

QUERY_HISTORY (option A) records every query executed, including the SQL text, user name, role, and timestamp—essential for showing what queries were run. ACCESS_HISTORY (option B) records which base objects (tables, views, columns) were accessed by which user in each query, making it the definitive source for proving table-level access. Together they provide both the query content and the confirmed object-level access. Option C (LOGIN_HISTORY) records only authentication events (logins), not table access. Option D (STAGE_USAGE_HISTORY) tracks file stage usage, not table reads. Option E (INFORMATION_SCHEMA.TABLE_PRIVILEGES) shows current privilege grants, not historical access events—it cannot show who accessed the table in the past.

2. A data governance team wants to implement column-level security on a TRANSACTIONS table so that analysts can see the last four digits of a CREDIT_CARD_NUMBER column but only members of the FINANCE_ADMIN role can see the full value. Which Snowflake feature and implementation pattern achieves this?

A. Create a row access policy that filters rows based on CURRENT_ROLE() so FINANCE_ADMIN sees all rows and analysts see rows with a NULL credit card number.
B. Create a dynamic data masking policy that returns the full value when CURRENT_ROLE() = 'FINANCE_ADMIN' and returns CONCAT('************', RIGHT(val, 4)) otherwise; attach it to the CREDIT_CARD_NUMBER column.✓ Correct
C. Create a Secure View that uses a CASE expression to mask the column, and grant SELECT on the view to analysts while revoking SELECT on the base table.
D. Create a masking policy using CURRENT_USER() checks instead of CURRENT_ROLE() so individual users in FINANCE_ADMIN are unmasked, and apply it directly to the table column.

Explanation

Dynamic data masking policies are Snowflake's native column-level security mechanism and support conditional unmasking based on CURRENT_ROLE(). The policy is attached directly to the column and transparently returns different values depending on the caller's active role — this is the correct, scalable approach. Option A misuses row access policies, which filter entire rows, not column values; returning NULL in a full row is not the same as masking a column and would hide other useful data from analysts. Option C (Secure View) is a valid workaround but is not the preferred enterprise pattern for column-level security when masking policies exist; it also requires revoking base table access and maintaining separate object grants. Option D uses CURRENT_USER() which is fragile — it requires updating the policy every time a user joins or leaves the team, unlike role-based checks which scale automatically.

3. A Snowflake data engineer is building an ETL pipeline role called ETL_LOADER. Following the principle of least privilege, which set of privileges is the MINIMUM required for ETL_LOADER to load data from an internal stage into a target table in schema PROD.SALES?

A. USAGE on database PROD, USAGE on schema PROD.SALES, INSERT and SELECT on the target table, READ on the stage
B. USAGE on database PROD, USAGE on schema PROD.SALES, INSERT on the target table, READ on the stage✓ Correct
C. OWNERSHIP on database PROD, INSERT on the target table, READ on the stage
D. SYSADMIN role inherited via role hierarchy, INSERT on the target table

Explanation

To load data (COPY INTO a table from a stage), the role needs USAGE on the database and schema to navigate the namespace, INSERT on the target table to write rows, and READ on the stage to access staged files. SELECT is NOT required for a load-only operation (option A adds unnecessary SELECT). Option C grants OWNERSHIP on the entire database, which violates least privilege significantly—OWNERSHIP implies all privileges on all child objects. Option D inheriting SYSADMIN is the worst violation of least privilege, as SYSADMIN has account-wide administrative capabilities far beyond what a loader needs.

4. Which statements about Snowflake's GRANT FUTURE PRIVILEGES syntax are accurate? (Select TWO)

A. GRANT FUTURE SELECT ON TABLES IN SCHEMA mydb.myschema TO ROLE analyst_role automatically grants SELECT on all tables created in that schema going forward, without requiring additional GRANT statements.✓ Correct
B. Future grants apply retroactively to all existing tables in the schema at the time the GRANT FUTURE statement is executed.
C. If an explicit privilege is later granted on a specific object that is covered by a future grant, the explicit grant takes precedence for that object only.✓ Correct
D. Future grants can be defined at the database level to apply to all schemas within the database, but not at the schema level.
E. Future grants are revoked automatically if the object ownership is transferred to a different role.

Explanation

Option A is correct: GRANT FUTURE PRIVILEGES is designed exactly for this forward-looking use case — all tables created in the specified schema after the grant is issued will automatically inherit the privilege for the specified role, eliminating the need for manual per-object grants. Option C is correct: explicit object-level grants override future grants for that specific object; this is important for exceptions to the default pattern (e.g., restricting a sensitive table that would otherwise be covered by a future grant). Option B is false and is a very common misconception — future grants are NOT retroactive; they only apply to objects created after the GRANT FUTURE statement is executed. Existing objects require separate grants. Option D is incorrect — future grants CAN be defined at both the database level (applying to all schemas in the database) AND the schema level. Option E is false — future grants are not automatically revoked upon ownership transfer; ownership changes affect OWNERSHIP privilege, not future grant assignments.

5. A Snowflake administrator wants to enforce key rotation for a service account that uses key-pair authentication. After generating a new RSA key pair, what is the correct sequence of steps to rotate the key with ZERO downtime for the pipeline?

A. 1) Remove the existing RSA_PUBLIC_KEY from the user, 2) Add RSA_PUBLIC_KEY_2 with the new public key, 3) Update the pipeline to use the new private key
B. 1) Add the new public key as RSA_PUBLIC_KEY_2 on the user with ALTER USER, 2) Update the pipeline client to use the new private key, 3) Remove the old RSA_PUBLIC_KEY after confirming the pipeline works✓ Correct
C. 1) Drop and recreate the Snowflake user with the new public key, 2) Re-grant all roles and privileges to the new user
D. 1) Update the pipeline to use the new private key, 2) Run ALTER USER SET RSA_PUBLIC_KEY = '<new_key>' replacing the old key simultaneously

Explanation

Snowflake supports two simultaneous public keys per user (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) specifically to enable zero-downtime key rotation. Option B is correct: first register the new public key in the secondary slot (RSA_PUBLIC_KEY_2), then update the pipeline to authenticate with the new private key, then remove the old key once confirmed—the pipeline is never interrupted. Option A removes the old key first, causing an authentication gap while the new key is not yet in use. Option C is unnecessarily destructive—dropping the user loses all role grants and history, causing significant downtime and rework. Option D attempts a simultaneous swap but provides no overlap window; if the pipeline is in the middle of authenticating when the key is swapped, it may fail, and updating the pipeline after the key change causes a brief authentication failure.

20 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free