Data Governance practice questions

Access History only records queries executed by human users.

Why it fails: Access History records all data access, including those originating from internal system processes, automated scripts, and service accounts. Governance requires visibility into every access point, regardless of the initiator, to ensure that automated ETL jobs and machine learning models are compliant with data privacy policies and organizational standards.

Access History is automatically enabled for all accounts and cannot be disabled.

Why it fails: While Access History is a core feature, certain aspects of telemetry and data retention are tied to Snowflake edition capabilities and account settings. Administrators must ensure the account is properly configured to capture the desired level of detail to support enterprise-wide governance initiatives and detailed security audits.

Access History includes the results of all queries executed.

Why it fails: Access History records the metadata about the query, such as objects queried and columns accessed, not the actual result sets returned to the user. Storing result sets would be a significant security risk and would create massive data volumes, making the audit logs difficult to query or maintain.

Tags must be created in the same database where the objects reside.

Why it fails: Tags can be created in any database or schema and then applied to objects located in different databases, provided the appropriate privileges are granted. This allows for centralized governance where security teams can manage a shared tag library across multiple business-specific databases or shared environments.

Dynamic Data Masking can only be applied to columns containing string data.

Why it fails: Masking policies support various data types, including numeric, date, and timestamp types. The output of the masking function must simply be compatible with the column's data type, ensuring that analytical queries can still execute correctly even when the raw sensitive data is obscured for unauthorized users.

Query Profile.

Why it fails: Query Profile provides execution details for a specific query to identify performance bottlenecks. It does not store historical data about object relationships or data flow across the entire platform, making it unsuitable for long-term lineage tracking or auditing the movement of data between tables.

Snowflake Object Tagging.

Why it fails: Object tagging is used for metadata classification, such as marking a column as PII. It does not record the history of data movement or lineage. While tags are useful for data discovery, they are passive labels that do not track the dynamic flow of data between storage objects.

Result Set Caching.

Why it fails: Result set caching is an optimization feature used to speed up repeated queries. It stores the result of a query, not the lineage or the source-to-target mapping of the data within the tables. It is completely irrelevant to the requirements of tracking data lineage for governance purposes.

Masking policies must be manually applied to every column, even if a tag is used.

Why it fails: Tag-based masking eliminates the need for manual application. Once a masking policy is associated with a tag, and that tag is applied to a column, the masking automatically takes effect. This automation ensures that sensitive data is protected consistently, reducing the risk of human error in security enforcement.

A tag can have multiple masking policies associated with it.

Why it fails: A tag-based masking association allows only one masking policy per tag. This limitation prevents conflicting masking behaviors from being applied to the same column, ensuring a clear and deterministic security outcome. Governance teams should design tags to map to specific, clear security outcomes for consistency.

The UNSET command must be used to remove the masking policy from a column.

Why it fails: Removing the tag from the column via the UNSET command automatically removes the associated masking policy. This behavior simplifies management significantly, as administrators do not need to keep track of individual policies on columns; they only need to manage the tags, which act as the central security lever.

Create a secure view with a CASE statement masking the email column.

Why it fails: Secure views provide data security but are computationally expensive to manage at scale. Every time a new column needs masking or a new role is added, the view definition must be updated. DDM is a more scalable, declarative policy-based approach that decouples security logic from object definitions.

Use row-level security to filter rows containing emails for analysts.

Why it fails: Row-level security restricts access to entire records based on conditions. This is ineffective for masking individual columns within rows. If analysts need aggregate counts, filtering rows would lead to inaccurate reporting by excluding relevant data points entirely from the result set instead of just masking the PII.

Use data replication to create a separate table for analysts with masked data.

Why it fails: Creating separate tables for different audiences leads to data sprawl, synchronization issues, and increased storage costs. It violates the single source of truth principle and introduces potential security gaps where the masked data might be incorrectly managed or updated, failing modern data governance best practices.

A Dynamic Data Masking policy on every column.

Why it fails: Row Access Policy and Dynamic Data Masking are distinct governance features. While they can be used together, masking is not a prerequisite for implementing row-level security. You can secure rows without masking columns, and masking columns does not inherently provide row-level filtering capabilities for the dataset.

A physical copy of the table for each user role.

Why it fails: The main benefit of Row-Level Security is that it eliminates the need to create physical clones of tables for different user groups. By using policies, a single table serves all users, and the system filters rows at runtime based on the user's role and defined security policies.

To apply tags to multiple objects at once.

Why it fails: TAG_REFERENCES is a read-only function used for querying tag assignments. It cannot be used to modify or apply tags to objects. Applying tags must be done via the ALTER object syntax, as the reference function's role is purely observational and auditing-focused, not transactional or configuration-based.

To calculate the cost associated with a specific tag.

Why it fails: While tags help with cost attribution, the TAG_REFERENCES function itself does not perform cost calculations. It only returns the mapping of tags to objects. Cost calculations are performed using other tools like the Account Usage views, which aggregate warehouse usage and storage metrics against those specific tags.

To enable tag-based access control.

Why it fails: The TAG_REFERENCES function does not govern access control. Access control is managed through RBAC (Role-Based Access Control) and security policies (Row Access or Masking). This function is purely for visibility and audit purposes and has no capability to grant or restrict user access to data objects.

The user must have the ACCOUNTADMIN role assigned to their current session.

Why it fails: While the ACCOUNTADMIN role has the necessary permissions by default, it is not a strict requirement for applying a masking policy. Snowflake follows the principle of least privilege, allowing these permissions to be delegated to custom functional roles like SECURITYADMIN or a specific Data Governance lead role.

The column being masked must not be part of a primary key constraint.

Why it fails: Snowflake allows Dynamic Masking Policies to be applied to columns regardless of whether they are part of a primary key or other constraints. The masking only affects the data's appearance during query execution and does not interfere with the underlying storage or the logical integrity defined by the constraints.

The table must be a permanent table rather than a transient or temporary table.

Why it fails: Dynamic Masking Policies can be applied to columns in permanent, transient, and temporary tables alike. The table's persistence type does not affect the engine's ability to evaluate masking logic at runtime, allowing engineers to protect sensitive data even in temporary staging environments or short-lived data pipelines.

Manually create a custom XML schema to define the PII patterns for the classifier.

Why it fails: Snowflake's classification system uses built-in machine learning models and patterns to identify sensitive data automatically. Users do not need to provide XML schemas or manual pattern definitions, as the system is designed to recognize common PII types like names, addresses, and credit card numbers out of the box.

Configure a Snowpipe to continuously monitor the table for new PII data types.

Why it fails: Snowpipe is used for data ingestion and does not have built-in functionality for continuous data classification. While classification can be triggered via tasks or scripts as new data arrives, it is not a native feature of Snowpipe, which focuses on the movement of data rather than its semantic analysis.

Row Access Policy that filters rows based on the department and salary column.

Why it fails: Row Access Policies filter entire rows, not columns. They cannot selectively show or hide a specific column's values. Applying a row access policy would either show or hide the entire row, which is not the requirement. Additionally, it does not address column-level obfuscation.

Object Tagging with a tag for salary and a policy that enforces access based on the tag.

Why it fails: Object Tagging is for metadata and discovery; it does not enforce access control. While tags can be used in conjunction with masking policies, the policy itself must be attached to the column. Tags alone cannot dynamically restrict data based on department. This option does not provide the required enforcement.

Secure Views that join to a department mapping table and conditionally display the salary column.

Why it fails: Secure Views can hide the view definition and can be used to implement column-level security by conditionally returning values. However, they require creating a separate view for each table and do not scale as easily as masking policies. They also may not dynamically reflect changes without altering the view. Masking policies are more scalable and integrated.

Use a subquery in the row access policy to join the mapping table on the fly.

Why it fails: Using a subquery in a row access policy can lead to performance issues because the policy is evaluated for each row, potentially causing repeated lookups. Snowflake recommends avoiding subqueries in policies for performance reasons. Instead, use a mapping table with a secure join or a policy that leverages a session variable or a secure function.

Create a secure function that queries the mapping table and use it in the row access policy.

Why it fails: Secure functions can encapsulate logic, but they still execute per row, which can be inefficient. While secure functions are useful for hiding logic, they do not inherently optimize performance. The mapping table approach with a join in the policy is generally more efficient if the mapping table is small and properly clustered.

Embed the mapping logic directly in the policy using a CASE statement with hardcoded values.

Why it fails: Hardcoding values in a row access policy is inflexible and requires policy alteration whenever mappings change. It also does not scale and can lead to errors. The requirement is for frequent updates, so a dynamic approach like a mapping table is necessary.

Create a secure view that filters rows based on CURRENT_ROLE() and grant it to all roles except ACCOUNTADMIN.

Why it fails: A secure view can filter rows, but it does not automatically exempt ACCOUNTADMIN unless the view logic includes a condition for that role. Simply granting the view to all roles except ACCOUNTADMIN does not prevent ACCOUNTADMIN from querying the base table directly, which would bypass the filtering. The requirement is to apply filtering at the table level via a row access policy.

Create a row access policy that uses CURRENT_USER() to look up the role in the mapping table and returns the region match.

Why it fails: CURRENT_USER() returns the login name of the user, not the active role. The mapping table is keyed by role, so using CURRENT_USER() would not correctly match role-to-region mappings. Additionally, this does not provide a special case for ACCOUNTADMIN, so the requirement to bypass filtering for that role would not be met.

Create a row access policy that uses IS_ROLE_IN_SESSION('ACCOUNTADMIN') to bypass filtering and otherwise uses CURRENT_ROLE() with the mapping table.

Why it fails: IS_ROLE_IN_SESSION checks if a role is in the session's active role hierarchy, but ACCOUNTADMIN is not automatically in the session unless activated. More importantly, the policy must return TRUE for ACCOUNTADMIN; using IS_ROLE_IN_SESSION alone does not guarantee that, and it may not cover all cases where ACCOUNTADMIN is the active role. The simpler and correct approach is to check CURRENT_ROLE() = 'ACCOUNTADMIN'.

The tag must be a system tag provided by Snowflake.

Why it fails: Tags used for tag-based masking can be custom tags created by users; they do not have to be system tags. System tags are used for Data Classification, but tag-based masking works with any tag, as long as it has a masking policy assigned.

The masking policy must be created with the data type matching the column.

Why it fails: While the masking policy's input data type must match the column's data type for the policy to be valid, this is a general requirement for any masking policy, not specific to tag-based masking. The question asks for requirements to enable tag-based masking, which are the tag creation and column tagging.

The user must have the APPLY MASKING POLICY privilege on the account.

Why it fails: The APPLY MASKING POLICY privilege is required to assign a masking policy to a tag or column, but it is not a requirement for the tag-based masking to function at query time. It is a privilege needed during configuration, not a functional requirement for the feature itself.

Create a dynamic data masking policy using the TAG_BASED_MASKING_POLICY function and assign tags to the column.

Why it fails: Tag-based masking policies are applied via tags, but they are not the only way to attach a masking policy. More importantly, the scenario does not require tag-based assignment; a direct column masking policy is sufficient and simpler. Using TAG_BASED_MASKING_POLICY would add unnecessary complexity and is not the most direct solution.

Use a secure view that excludes the ACCOUNT_NUMBER column and grant analysts access to the view instead of the table.

Why it fails: A secure view that excludes the column would prevent analysts from joining on ACCOUNT_NUMBER, which is a required capability. Additionally, the scenario specifies that existing queries should not be modified, and replacing table access with a view would likely require query changes. This approach does not satisfy the join requirement.

Implement a row access policy that filters out rows where the analyst does not own the account.

Why it fails: A row access policy filters rows, not columns. It would not mask the ACCOUNT_NUMBER value; it would only restrict which rows are visible. The requirement is to obscure the account number for all rows, not to limit row visibility. Therefore, this does not meet the masking objective.

CASE WHEN IS_ROLE_IN_SESSION('PII_READER') THEN val ELSE '***-***-****' END

Why it fails: IS_ROLE_IN_SESSION checks if a role is in the session's active role hierarchy, but it does not guarantee that the role is currently active. A user could have PII_READER in their role hierarchy but not have it active, yet IS_ROLE_IN_SESSION would return TRUE, potentially granting access unintentionally. The requirement is to show full number only to users with the role PII_READER active, so CURRENT_ROLE() is more precise.

CASE WHEN CURRENT_ROLE() IN ('PII_READER', 'ACCOUNTADMIN') THEN val ELSE '***-***-****' END

Why it fails: This expression would also grant full access to ACCOUNTADMIN, which is not specified in the requirement. The requirement states that only users with the role PII_READER should see the full number. Including ACCOUNTADMIN broadens access beyond what is intended, potentially violating the principle of least privilege.

CASE WHEN CURRENT_USER() = 'PII_READER' THEN val ELSE '***-***-****' END

Why it fails: CURRENT_USER() returns the login name of the user, not the role. PII_READER is a role, so comparing CURRENT_USER() to 'PII_READER' will not match unless a user happens to have that exact login name, which is unlikely and not the intended design. The policy would not correctly grant access based on role.

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

Why it fails: SHOW TAGS lists the tag objects that exist in a schema or account, not the columns to which a tag has been applied. It can confirm that PII_LEVEL exists, but it will not tell the administrator which columns carry it. To map a tag to columns, a tag reference function or view is required, so SHOW TAGS alone does not satisfy the reporting need.

Query INFORMATION_SCHEMA.COLUMNS and filter on the COLUMN_NAME pattern.

Why it fails: INFORMATION_SCHEMA.COLUMNS describes column definitions such as data type and nullability, but it does not expose tag assignments. Filtering on column names would only find columns whose names happen to match a pattern, not columns formally tagged with PII_LEVEL. This approach cannot reliably identify tagged columns and would miss columns whose names do not reflect their sensitivity.

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

Why it fails: The correct account usage view for tag-to-object mappings is TAG_REFERENCES in the INFORMATION_SCHEMA or the corresponding table function, and ACCOUNT_USAGE does not expose a TAG_REFERENCES view with the same semantics. Even if a similar view existed, joining to TABLES would not directly yield column-level tag assignments. This option misstates the available governance views and is not the direct method for the report.

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

Why it fails: Masking policies are applied to columns, not roles. You cannot assign a masking policy to a role; you attach it to a column and define conditions based on roles. Creating a new role that inherits from ACCOUNTADMIN would not change the masking policy's evaluation, and ACCOUNTADMIN would still see unmasked data because the policy does not explicitly mask it for that role.

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

Why it fails: Using IS_ROLE_IN_SESSION('FINANCE_ANALYST') checks if the role is active in the session, but it does not automatically mask for ACCOUNTADMIN. If ACCOUNTADMIN is not in the session, the condition would return false and the policy would need an ELSE clause to mask. However, the policy as described likely uses a simple CASE; switching to IS_ROLE_IN_SESSION does not inherently mask ACCOUNTADMIN unless the ELSE clause does so. The core issue remains that the policy must explicitly handle ACCOUNTADMIN.

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

Why it fails: Revoking SELECT from ACCOUNTADMIN is ineffective because ACCOUNTADMIN has inherent privileges and can grant itself access back. Moreover, the goal is not to remove access entirely but to mask the data. Masking policies are the correct tool for this, and revoking privileges does not change the masking behavior. This option fails because it does not address the masking logic and could disrupt administrative tasks.

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

Why it fails: This expression checks CURRENT_USER() instead of CURRENT_ROLE(). The requirement is based on role, not user. Even if a user named PAYMENT_ADMIN exists, the policy should apply to anyone with the role PAYMENT_ADMIN. Using CURRENT_USER() would fail if multiple users have the role or if the role is assigned to different users, so it does not meet the requirement.

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

Why it fails: This expression concatenates asterisks with the last four digits, but it always shows exactly 12 asterisks regardless of the original length. If the credit card number has a different length or contains non-digit characters, the masking may not align correctly. The REGEXP_REPLACE approach in the correct option is more robust because it replaces the first 12 digits while preserving the rest.

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

Why it fails: This expression masks the last four digits instead of the first twelve. It would show the first digits and hide the last four, which is the opposite of the desired partial masking. The requirement is to show the last four digits to non-admins, so this expression does not meet the requirement.

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

Why it fails: Masking policies operate on column values to obfuscate data, not to filter rows. They cannot conditionally exclude entire rows based on user attributes. While a masking policy can use CURRENT_USER() or tag references, it only alters column output, leaving all rows visible. This does not meet the requirement to restrict row access by region.

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

Why it fails: Secure views can restrict data but require defining and maintaining a separate view for each access pattern. They do not automatically apply to all queries against the base table unless users are forced to query the view. Row access policies are attached directly to the table and apply universally, making them more robust for dynamic row-level security.

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

Why it fails: Tag-based masking policies mask column values, not rows. They cannot filter out rows based on user attributes. While they can use tags to determine masking behavior, they operate at the column level. This approach would hide the region value but still return all rows, failing to restrict access to only the user's region.

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

Why it fails: Tags can be applied to a variety of objects including databases, schemas, tables, columns, and even warehouses. The statement that they cannot be applied to databases or accounts is incorrect. Account-level tags are supported, and database-level tags are also supported. This option is a distractor because it understates the scope of tag application, which is broader than described.

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

Why it fails: Tags are primarily used for metadata classification and can drive masking policies, but they cannot directly enforce row access policies. Row access policies are separate objects that are attached to tables and use expressions that can reference tag values via functions like SYSTEM$GET_TAG, but the policy itself is not automatically applied based on tags. The statement that tags can be used to enforce row access policies is misleading because it implies automatic enforcement, which is not the case. This option is a distractor.

Tags must be unique across the entire Snowflake account.

Why it fails: Tags are schema-level objects, so they must be unique within a schema, not across the entire account. You can have tags with the same name in different schemas. This allows for decentralized tag management. The statement that they must be unique account-wide is false and would impose an unnecessary restriction. This option is a distractor based on a misunderstanding of tag scope.