DEA-C02 Data Governance Practice Test 2 — 15 Questions

Question 1

A Snowflake administrator needs to know which columns in a table have been assigned a specific tag named PII_LEVEL so that a governance report can be produced. Which approach most directly returns the columns carrying that tag?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Call the TAG_REFERENCES table function with the tag name and filter the results by the target table.. The TAG_REFERENCES table function is purpose-built to return the objects and columns associated with a specified tag. By calling it with the tag name and filtering for the target table, the administrator obtains the exact set of columns carrying PII_LEVEL. This is the most direct and reliable native method for producing a column-level tag report.

Answer

Run SHOW TAGS and inspect the output for the target table name.

Answer

Query INFORMATION_SCHEMA.COLUMNS and filter on the COLUMN_NAME pattern.

Answer

Query ACCOUNT_USAGE.TAG_REFERENCES view and join to ACCOUNT_USAGE.TABLES.

Question 2

A data engineer at a financial services company has been asked to implement dynamic data masking on sensitive columns in a table called ACCOUNTS. The masking policy should allow members of the role FINANCE_ANALYST to see full account numbers, while all other roles see only the last four digits. The engineer creates a masking policy and applies it to the ACCOUNT_NUMBER column. After testing, the engineer notices that users with the ACCOUNTADMIN role can see the full account number even though they are not in FINANCE_ANALYST. What should the engineer do to ensure that ACCOUNTADMIN users also see masked data?

Accepted Answer

Alter the masking policy to include a condition that checks CURRENT_ROLE() and returns the masked value for ACCOUNTADMIN.. Masking policies are evaluated for every query, and the policy logic determines what each role sees. ACCOUNTADMIN is not exempt from masking policies, but if the policy does not explicitly mask for that role, it will see unmasked data. To enforce masking for ACCOUNTADMIN, the policy must include a condition that returns the masked value for that role. This ensures consistent data protection regardless of role privileges.

Answer

Create a new role that inherits from ACCOUNTADMIN and assign the masking policy to that role instead.

Answer

Set the masking policy to use IS_ROLE_IN_SESSION('FINANCE_ANALYST') instead of CURRENT_ROLE(), which will automatically mask for ACCOUNTADMIN.

Answer

Revoke the SELECT privilege on the ACCOUNT_NUMBER column from the ACCOUNTADMIN role.

Question 3

A data engineer is configuring a masking policy on a column containing credit card numbers. The policy should show the full number only to users with the role 'PAYMENT_ADMIN' and show a partially masked version (e.g., last four digits) to all other users. Which masking policy expression correctly implements this?

Accepted Answer

CASE WHEN CURRENT_ROLE() = 'PAYMENT_ADMIN' THEN VAL ELSE REGEXP_REPLACE(VAL, '\d{12}', '************') END. The correct masking policy must check the current role and conditionally return either the full value or a masked version. Using CURRENT_ROLE() is essential to enforce role-based access. The REGEXP_REPLACE function with a pattern targeting the first 12 digits effectively masks all but the last four, assuming a standard 16-digit credit card number. The other options use incorrect context functions or mask the wrong portion.

Answer

CASE WHEN CURRENT_USER() = 'PAYMENT_ADMIN' THEN VAL ELSE REGEXP_REPLACE(VAL, '\d{12}', '************') END

Answer

CASE WHEN CURRENT_ROLE() = 'PAYMENT_ADMIN' THEN VAL ELSE '************' || RIGHT(VAL, 4) END

Answer

CASE WHEN CURRENT_ROLE() = 'PAYMENT_ADMIN' THEN VAL ELSE REGEXP_REPLACE(VAL, '\d{4}$', '****') END

Question 4

A data engineer needs to enforce a policy that restricts access to rows in a SALES table based on the region of the sales representative. The policy must dynamically evaluate the current user's region attribute, which is stored as a tag on the user object. Which Snowflake feature should the engineer use to implement this requirement?

Accepted Answer

A row access policy that references the user's region tag via a mapping table and applies a filter condition.. Row access policies are the correct feature for row-level filtering based on dynamic conditions. By referencing a mapping table that links users to regions and using the user's region tag, the policy can evaluate at query time and return only rows where the region matches the user's region. This enforces the required restriction without altering the underlying data.

Answer

A masking policy that checks the current user's region tag and returns NULL for rows outside their region.

Answer

A secure view that joins the SALES table with a user-region mapping table and filters on the current user.

Answer

A tag-based masking policy that uses the user's region tag to mask the region column in the SALES table.

Question 5

A data engineer is responsible for implementing a data governance strategy using Snowflake tags. The engineer needs to ensure that tags are applied consistently and that tag-based masking policies are enforced. Which two of the following statements are true regarding Snowflake tags and their usage? (Choose two.)

Accepted Answer

A tag can be assigned a value that is a string, number, or boolean.. Tag-based masking policies allow a masking policy to be associated with a tag, so that any column with that tag is automatically masked. Tags also support values that can be strings, numbers, or booleans, stored as strings. These two statements are true and are key aspects of using tags for governance. The other options misstate tag scope, uniqueness, or capabilities.

Answer

Tags can be applied to columns, tables, and schemas, but not to databases or accounts.

Answer

Tags can be used to enforce row access policies based on tag values.

Answer

Tags must be unique across the entire Snowflake account.

Question 6

A data engineer needs to ensure that a new column added to a table inherits the same masking policy as an existing column. The table already has a masking policy applied to the 'SSN' column. The engineer adds a new column 'TAX_ID' that also contains sensitive data. What is the most efficient way to apply the same masking policy to the new column?

Accepted Answer

Use ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY to apply the existing policy to the new column.. The most efficient way to apply an existing masking policy to a new column is to use ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY. This directly attaches the policy without creating duplicates or additional configurations. Creating a new policy, using tags, or attempting to overwrite the policy are either inefficient or invalid approaches for this scenario.

Answer

Use a tag-based masking policy that automatically applies to columns with a specific tag, and tag the new column.

Answer

Use CREATE MASKING POLICY with the same name to overwrite the existing policy and include the new column.

Answer

Create a new masking policy with the same logic and apply it to the new column.

Question 7

A data engineer needs to audit all grants of the SELECT privilege on a sensitive table named CUSTOMER_PII in the SALES database. The engineer wants to identify which roles have been granted SELECT on this table, including any grants made through role hierarchy. Which Snowflake feature should the engineer use to retrieve this information?

Accepted Answer

Query the GRANTS_TO_ROLES view in the SNOWFLAKE.ACCOUNT_USAGE schema.. The GRANTS_TO_ROLES view in SNOWFLAKE.ACCOUNT_USAGE is designed for auditing grants across the account. It captures all grants to roles, including those on tables, and retains history. This makes it the best choice for identifying all roles with SELECT on a sensitive table, including indirect grants through role hierarchy.

Answer

Use the DESCRIBE TABLE CUSTOMER_PII command.

Answer

Query the TABLE_PRIVILEGES view in the INFORMATION_SCHEMA.

Answer

Use the SHOW GRANTS ON TABLE CUSTOMER_PII command.

Question 8

A data engineer is implementing a data governance strategy and needs to track the lineage of data as it moves from raw tables to transformed views and then to downstream reports. The engineer wants to understand which columns in the raw tables contribute to specific columns in the final report. Which Snowflake feature provides this column-level lineage information?

Accepted Answer

Snowflake's native column-level lineage feature available in the Snowsight UI and via the GET_LINEAGE function.. Snowflake's column-level lineage feature, accessible in Snowsight and via the GET_LINEAGE function, provides detailed mapping of how columns flow from source to target through transformations. It leverages query history and object dependencies to construct a lineage graph. This is the correct tool for understanding column-level data lineage in a governance context.

Answer

Object Dependencies in the ACCOUNT_USAGE schema.

Answer

Data Lineage in the ACCOUNT_USAGE schema (ACCESS_HISTORY and OBJECT_DEPENDENCIES combined).

Answer

Access History in the ACCOUNT_USAGE schema.

Question 9

A data engineer is configuring access controls for a new database named SALES_DB. The requirement is that only users with the role SALES_ANALYST should be able to query tables in the PUBLIC schema. All other roles should be denied access, even if they have USAGE on the database. Which grant should the data engineer execute to meet this requirement?

Accepted Answer

GRANT USAGE ON DATABASE SALES_DB TO ROLE SALES_ANALYST; GRANT USAGE ON SCHEMA SALES_DB.PUBLIC TO ROLE SALES_ANALYST; GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO ROLE SALES_ANALYST;. To allow only SALES_ANALYST to query tables, the role needs USAGE on the database, USAGE on the schema, and SELECT on the tables. The other options either grant incomplete privileges or focus solely on revoking from PUBLIC without granting to SALES_ANALYST. The correct approach combines the necessary grants to the intended role, and in practice, the engineer would also revoke any existing privileges from other roles to enforce the restriction fully.

Answer

GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO ROLE SALES_ANALYST;

Answer

REVOKE SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC FROM ROLE PUBLIC;

Answer

GRANT USAGE ON SCHEMA SALES_DB.PUBLIC TO ROLE SALES_ANALYST;

Question 10

A data engineer is implementing a data governance strategy using Snowflake tags. The engineer needs to ensure that tags are used to control access to sensitive data and to track data lineage. Which two statements are true regarding Snowflake tags? (Choose two.)

Accepted Answer

Tags can be used in masking policies to apply conditional masking based on tag values.. Tags in Snowflake are versatile governance objects. They can be referenced in masking policies to dynamically mask data based on tag values, enabling centralized policy management. Additionally, the TAG_REFERENCES view allows engineers to query which objects have specific tags, aiding in data discovery and lineage tracking. The other statements are false: tags are not automatically propagated to columns, they can be applied to warehouses and users, and tag names are unique only within a schema, not account-wide.

Answer

Tags can be propagated to columns automatically when a table is created using the TAG keyword in the CREATE TABLE statement.

Answer

Tags can be applied to databases, schemas, tables, and columns, but not to warehouses or users.

Answer

Tags must be unique across the entire Snowflake account, and duplicate tag names are not allowed.

Question 11

A data engineer is tasked with implementing row-level security for a table named ORDERS. The requirement is that users with the role REGIONAL_MANAGER can only see orders for their assigned region, which is stored in a separate table named MANAGER_REGION. The mapping between manager and region is dynamic and can change. Which Snowflake feature should the engineer use to enforce this dynamically without modifying the ORDERS table data?

Accepted Answer

A row access policy attached to the ORDERS table that references the MANAGER_REGION table in its policy expression.. Row access policies are designed to filter rows based on dynamic conditions, and they can reference other tables in their policy expression. By attaching a policy to ORDERS that queries MANAGER_REGION, the engineer ensures that each manager sees only their region's orders. The policy is evaluated at query time, so changes to the mapping are immediately effective. This approach enforces security at the table level, cannot be bypassed by direct queries, and does not alter stored data.

Answer

A tag-based access control policy that assigns tags to rows based on region and grants access based on tag matching.

Answer

A masking policy on the REGION column of the ORDERS table that returns NULL for unauthorized regions.

Answer

A secure view that joins ORDERS with MANAGER_REGION and filters by the current role.

Question 12

A data engineer needs to ensure that PII data in the 'SALES' table is obscured for non-admin users while maintaining original data types for downstream analytical models. Which approach provides the most scalable governance?

Accepted Answer

Apply a masking policy to the columns and grant the APPLY MASKING POLICY privilege.. Dynamic Data Masking allows policies to be applied to columns based on the user's role without duplicating data. By leveraging masking policies, you maintain a single source of truth while ensuring sensitive information is protected at query runtime. This method is highly scalable as a single policy can be assigned to multiple columns across different tables, simplifying maintenance and ensuring consistent security posture across the enterprise.

Answer

Create separate secure views for every user role requiring masked access.

Answer

Use row-level security to filter rows containing PII data for authorized users.

Answer

Physically transform the data during the ETL process and store it in a new table.

Question 13

An organization wants to classify their data to identify PII. Which feature should they use to automatically tag columns containing sensitive information?

Accepted Answer

Data Classification.. Object Tagging is the primary governance feature for metadata management. When combined with Snowflake's Data Classification capability, it can automatically scan and suggest tags for columns based on their content, such as PII or financial data. This automation reduces manual labor and ensures that governance policies are consistently applied across large, complex schemas, which is fundamental to maintaining a high-quality data catalog.

Answer

Row Access Policies.

Answer

Dynamic Data Masking.

Answer

Query Profile.

Question 14

Which object allows a data engineer to assign a security policy based on a user's geographical location attribute?

Accepted Answer

Row Access Policy.. Row Access Policies are the correct mechanism here. By using a policy that evaluates the current user's session context—specifically looking at attributes like their IP address or a custom 'location' attribute—the policy can filter out rows that the user is not authorized to see based on residency or regional compliance regulations like GDPR, ensuring data sovereignty is upheld throughout the organization.

Answer

Masking Policy.

Answer

Secure View.

Answer

Tag-based masking.

Question 15

A company requires that data masking policies be applied automatically whenever a column is tagged with 'PII'. How can this be achieved?

Accepted Answer

Use tag-based masking policies.. Tag-based masking policies provide a direct link between metadata tagging and security enforcement. By associating a masking policy with a specific tag, any column assigned that tag automatically inherits the associated masking behavior. This automation is crucial for governance, as it prevents manual errors and ensures that sensitive data is never exposed simply because someone forgot to manually attach a policy to a new column.

Answer

Write a stored procedure to trigger on every DDL statement.

Answer

Use a Row Access Policy with a conditional tag check.

Answer

Manually apply the masking policy every time a table is created.