data-governance

name	data-governance
description	Data governance & security analysis by generating SQL queries against Snowflake ACCOUNT_USAGE views. Use for: access control, audit trails, who accessed what, permissions, role hierarchies, PII/data classification, masking/row access policies, grants, tags, compliance monitoring, object dependencies. Triggers: governance, access history, who has access, permissions, classification, PII, sensitive data, masking policy, grants, roles, audit, compliance.
location	system

Data Governance Instructions

Generate SQL queries against Snowflake ACCOUNT_USAGE governance data using the embedded semantic model.

When to Use

Access & Audit: User access patterns, permissions, role hierarchies, query history, activity tracking
Classification & PII: Sensitive data discovery, classification analysis, tables/columns without masking policies
Compliance: Policy analysis (masking, row access, aggregation), grant analysis, audit trails
Advanced: Cross-database access patterns, object dependencies, role effectiveness analysis

Workflow

Step 1: Parse Question → Identify Domain

• Access/audit → ACCESS_HISTORY, GRANTS_*
• Classification/PII → DATA_CLASSIFICATION_LATEST, TAGS
• Policies → *_POLICIES, POLICY_REFERENCES
• Objects → TABLES, VIEWS, COLUMNS, OBJECT_DEPENDENCIES

Step 2: Check Verified Queries FIRST

Search the embedded semantic model's verified_queries section (100+ queries) for matching patterns:

• "who has access" → Roles/users with privileges queries
• Sensitive data → Classification and policy protection queries
• Access history → LATERAL FLATTEN patterns
• Policies → Policy-specific queries

If match found: Adapt the verified query SQL (adjust time filters, object names). Replace __table_name with SNOWFLAKE.ACCOUNT_USAGE.TABLE_NAME from base_table definition.

If no match: Generate SQL using semantic model table definitions and similar verified queries as structural reference.

Step 3: SQL Construction Guidelines

• All tables in SNOWFLAKE.ACCOUNT_USAGE schema
• JSON columns (DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED, OBJECTS_MODIFIED) require LATERAL FLATTEN
• Use UPPER() for case-insensitive identifier matching
• Add time filters for ACCESS_HISTORY queries (QUERY_START_TIME)

Step 4: Execute Query

Return generated SQL and results.

Key Notes

• ACCESS_HISTORY requires LATERAL FLATTEN for column-level analysis
• Tables have up to 120 minute latency
• 100+ verified queries embedded below with proven patterns
• Use UPPER() for identifier matching

---

Semantic Model

Semantic model embedded below:

the list of supported account_usage views

Category 1: Historical Data

ACCESS_HISTORY [TODO verify definition]

QUERY_HISTORY [TODO verify definition]

Category 2: Tags, Policies, Sensitivity and Classifications

AGGREGATION_POLICIES

MASKING_POLICIES

ROW_ACCESS_POLICIES

PROJECTION_POLICIES

TAGS

TAG_REFERENCES

DATA_CLASSIFICATION_LATEST [TODO verify definition]

POLICY_REFERENCES

Category 3: Data Objects

COLUMNS

DATABASES

SCHEMATA

TABLES

VIEWS

OBJECT_DEPENDENCIES [TODO verify definition]

Category 4: Security and Sensitivity

GRANTS_TO_ROLES

GRANTS_TO_USERS

ROLES

USERS

name: Governance custom_instructions: >

1. Identifier Case Sensitivity and Formatting:

When generating SQL for Snowflake, follow these key guidelines to ensure correct identifier handling and formatting::

Unquoted identifiers (e.g., orders) must:
  - Start with a letter (A-Z, a-z) or an underscore (_)
  - Contain only letters, underscores, digits (0-9), and dollar signs ($)
  - These are interpreted by Snowflake as uppercase and are treated case-insensitively.
To ensure accurate comparisons involving unquoted identifiers use one of the two approaches below:
  - Use uppercase directly (e.g., WHERE table_name = 'ORDERS')
  - Use the UPPER() function (e.g., WHERE table_name = UPPER('orders'))

Quoted identifiers (e.g., "SalesData-2024'q2") are case-sensitive and must be used exactly as
 written.
  - When referencing them as values, preserve the original casing
  - Ensure the first and the last double quotes are removed if explicitly provided
  - Escape special characters as needed. Example Filter: WHERE table_name = 'SalesData-2024\\'q2'

How to choose between Quoted and Unquoted Identifiers:
  - If an identifier is not explicitly quoted and conforms to unquoted rules, treat it as unquoted (case-insensitive)
  - Otherwise, treat it as a quoted (case-sensitive) identifier.

2. Fully-Qualified Object Names:

- A fully-qualified schema-level object (such as a table, view, tag, function, procedure, or file
  format) has the form: <database_name>.<schema_name>.<object_name> where each part is separated
  by a period and represents the database, schema, and object name, respectively.

- To simplify usage, users often omit parts of the qualification from left to right. For example:
  both <schema_name>.<object_name> and <object_name> may be used to refer to objects.

- On the other hand some object types, such as schemas and database roles, are only qualified by
   database and follow this format: <database_name>.<object_name>

- Use the context of the user question to determine the object type, and parse the components
  accordingly when generating SQL.

3. Query Behavior Expectations:

- Do not add ORDER BY clauses unless the user specifically requests them or the questions clearly needs them.
- If the question mentions tables, columns, or views, treat this as referring to relational tabular
  data stored in the user's Snowflake account. Specifically for tables and columns to not get
  confused with physical tables columns.

4. Using ACCESS_HISTORY table and JSON data Handling:

To analyze the ACCESS_HISTORY table, you must use LATERAL FLATTEN to extract detailed information from the JSON columns:

  - DIRECT_OBJECTS_ACCESSED
      Raw JSON array of data objects explicitly named in the query.

      STRUCTURE:
      This is an array of objects with fields such as:
      - objectDomain: Type of object (Materialized view, Procedure, Table, View, Function, Stage)
      - objectName: Fully qualified name of the object
      - objectId: Unique object identifier
      - columns: Array of columns accessed (when applicable)

  - BASE_OBJECTS_ACCESSED
      Raw JSON array of all base data objects accessed to execute the query,
      including the underlying tables for views, UDFs, and stored procedures.

      STRUCTURE:
      This is an array of objects with fields such as:
      - objectDomain: Type of object (Materialized view, Procedure, Table, View, Function, Stage)
      - objectName: Fully qualified name of the object
      - objectId: Unique object identifier
      - columns: Array of columns accessed (when applicable)

  - OBJECTS_MODIFIED
      Raw JSON array specifying the objects that were associated with a write
      operation in the query.

      STRUCTURE:
      This is an array of objects with fields such as:
      - objectDomain: Type of object (Materialized view, Procedure, Table, View, Function, Stage)
      - objectName: Fully qualified name of the object
      - objectId: Unique object identifier
      - columns: Array of modified columns with source information

  - OBJECT_MODIFIED_BY_DDL
      Raw JSON object specifying the DDL operation on database objects.

      STRUCTURE:
      This is an object with fields such as:
      - objectDomain: Type of object (Materialized view, Procedure, Table, View, Function, Stage)
      - objectName: Fully qualified name of the object
      - objectId: Object identifier
      - operationType: SQL operation (CREATE, ALTER, DROP, etc.)
      - properties: Array of object properties

EXAMPLE QUERY:

  CREATE OR REPLACE VIEW ACCESS_HISTORY_FLATTENED AS
  SELECT
      QUERY_ID,
      QUERY_START_TIME,
      USER_NAME,
      'direct_objects' as ACCESS_TYPE,
      o_flattened.value:objectDomain::STRING AS OBJECT_DOMAIN,
      o_flattened.value:objectId::NUMBER AS OBJECT_ID,
      o_flattened.value:objectName::STRING AS OBJECT_NAME,
      c_flattened.value:columnId::NUMBER AS COLUMN_ID,
      c_flattened.value:columnName::STRING AS COLUMN_NAME,
      PARENT_QUERY_ID,
      ROOT_QUERY_ID
  FROM
      SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
      LATERAL FLATTEN(input => DIRECT_OBJECTS_ACCESSED) o_flattened,
      LATERAL FLATTEN(input => o_flattened.value:columns) c_flattened

  UNION ALL

  SELECT
      QUERY_ID,
      QUERY_START_TIME,
      USER_NAME,
      'base_objects' as ACCESS_TYPE,
      o_flattened.value:objectDomain::STRING AS OBJECT_DOMAIN,
      o_flattened.value:objectId::NUMBER AS OBJECT_ID,
      o_flattened.value:objectName::STRING AS OBJECT_NAME,
      c_flattened.value:columnId::NUMBER AS COLUMN_ID,
      c_flattened.value:columnName::STRING AS COLUMN_NAME,
      PARENT_QUERY_ID,
      ROOT_QUERY_ID
  FROM
      SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
      LATERAL FLATTEN(input => BASE_OBJECTS_ACCESSED) o_flattened,
      LATERAL FLATTEN(input => o_flattened.value:columns) c_flattened

  UNION ALL

  SELECT
      QUERY_ID,
      QUERY_START_TIME,
      USER_NAME,
      'objects_modified' as ACCESS_TYPE,
      o_flattened.value:objectDomain::STRING AS OBJECT_DOMAIN,
      o_flattened.value:objectId::NUMBER AS OBJECT_ID,
      o_flattened.value:objectName::STRING AS OBJECT_NAME,
      c_flattened.value:columnId::NUMBER AS COLUMN_ID,
      c_flattened.value:columnName::STRING AS COLUMN_NAME,
      PARENT_QUERY_ID,
      ROOT_QUERY_ID
  FROM
      SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
      LATERAL FLATTEN(input => OBJECTS_MODIFIED) o_flattened,
      LATERAL FLATTEN(input => o_flattened.value:columns) c_flattened

5. Using DATA_CLASSIFICATION_LATEST table: To analyze column level data from DATA_CLASSIFICATION_LATEST table, you must use LATERAL FLATTEN to extract detailed information from the JSON columns: - RESULT Latest classification result as a VARIANT data type. This column contains a complex JSON structure with detailed classification information for each column in the classified table.

      THE RESULT COLUMN STRUCTURE:
      ---------------------------
      The RESULT column is a JSON object where:
      - Each key is a column name from the classified table
      - Each value is an object containing classification details for that column

      EXAMPLE STRUCTURE:
      {{
        "COLUMN_NAME": {{
          "alternates": [],
          "recommendation": {{
            "confidence": "HIGH|MEDIUM|LOW",
            "coverage": 0.9171,
            "details": [],
            "privacy_category": "IDENTIFIER",
            "semantic_category": "EMAIL"
          }},
          "valid_value_ratio": 0.9171
        }},
        "ANOTHER_COLUMN": {{
          ...
        }}
      }}

  EXAMPLE QUERY:
  WITH base_classification AS (
    SELECT
      DATABASE_NAME,
      SCHEMA_NAME,
      TABLE_NAME,
      RESULT
    FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_CLASSIFICATION_LATEST
  ),
  column_categories AS (
    SELECT
      f.value:recommendation:semantic_category::STRING as SEMANTIC_CATEGORY
    FROM base_classification,
    LATERAL FLATTEN(INPUT => RESULT) f
    WHERE f.value:recommendation:semantic_category IS NOT NULL
  )
  SELECT
    SEMANTIC_CATEGORY,
    COUNT(*) as COLUMN_COUNT
  FROM column_categories
  GROUP BY SEMANTIC_CATEGORY
  ORDER BY COLUMN_COUNT DESC

6. Consistent State of Governance Tables:

The tables below are kept in a consistent state, and changes are propagated instantly. When a user is dropped from the database all the associated grants and other tables are updated accordingly. You do not need to join against base tables for grants, views, tables, users, policy_references, and others to make sure the underlying object has not been deleted.

tables:

• name: ACCESS_HISTORY description: > The table contains records of user access history, specifically queries executed by users. Each record represents a single query and includes details about the user, query execution, and accessed objects. This view is available in Enterprise Edition or higher and tracks access history for the last 365 days (1 year).

  Note: This table contains complex JSON arrays (DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED, and OBJECTS_MODIFIED) that require LATERAL FLATTEN operations for detailed analysis. Since LATERAL FLATTEN cannot be used directly in semantic model expressions, this model provides simplified dimensions and metrics based on these JSON columns. For detailed column-level access analysis, use the verified queries that demonstrate proper JSON handling with LATERAL FLATTEN in subsequent operations.

  synonyms:

  • "data access"
  • "object access"
  • "access audit"
  • "access logs"

  base_table: database: SNOWFLAKE schema: ACCOUNT_USAGE table: ACCESS_HISTORY

  primary_key: columns: - QUERY_ID

  time_dimensions:

  • name: QUERY_START_TIME description: > The timestamp when the query was started (in UTC time zone). This can be used for time-based analysis of access patterns. synonyms:

    • "access time"
    • "query time" expr: QUERY_START_TIME data_type: TIMESTAMP_LTZ

  • name: ACCESS_DATE description: "Date part of when the access occurred (without time)" expr: DATE(QUERY_START_TIME) data_type: DATE

  • name: ACCESS_MONTH description: "Month when the access occurred, useful for monthly reporting" expr: DATE_TRUNC('MONTH', QUERY_START_TIME) data_type: DATE

  • name: ACCESS_DAY_OF_WEEK description: "Day of week when access occurred (1=Sunday, 7=Saturday)" expr: DAYOFWEEK(QUERY_START_TIME) data_type: NUMBER

  • name: ACCESS_HOUR description: "Hour of day when access occurred (0-23)" expr: HOUR(QUERY_START_TIME) data_type: NUMBER

  • name: IS_BUSINESS_HOURS description: "Flag indicating if access occurred during business hours (M-F, 9AM-5PM)" expr: > CASE WHEN DAYOFWEEK(QUERY_START_TIME) BETWEEN 2 AND 6 AND HOUR(QUERY_START_TIME) BETWEEN 9 AND 16 THEN TRUE ELSE FALSE END data_type: BOOLEAN

  dimensions:

  • name: QUERY_ID description: > A unique identifier for the query. This value is also mentioned in the QUERY_HISTORY view and can be used to join the tables. expr: QUERY_ID data_type: TEXT unique: true

  • name: USER_NAME description: > The name of the user who issued the query that accessed the data. synonyms:

    • "username"
    • "user" expr: USER_NAME data_type: TEXT

Category 2: Tags, Policies, Sensitivity and Classifications

• name: AGGREGATION_POLICIES description: Account Usage view that provides information about aggregation policies in your account. Each row represents a different aggregation policy that controls data access constraints. Has a latency of up to 120 minutes and shows only objects accessible to the current role.

  base_table: database: SNOWFLAKE schema: ACCOUNT_USAGE table: AGGREGATION_POLICIES

  primary_key: columns: - POLICY_ID

  time_dimensions:

  • name: CREATED expr: CREATED description: Date and time when the aggregation policy was created synonyms : ["CREATED AT", "INITIALIZED", "BUILT", "DEVISED", "INITIALIZED AT", "BUILT AT", "DEVISED AT"] unique: false data_type: TIMESTAMP_LTZ

  • name: LAST_ALTERED expr: LAST_ALTERED description: Date and time when the aggregation policy was last modified synonyms : ["LAST MODIFIED", "LAST CHANGED", "LAST UPDATED", "ALTERED AT", "EDITED AT", "MODIFIED ON"] unique: false data_type: TIMESTAMP_LTZ

  • name: DELETED expr: DELETED description: Date and time when the aggregation policy was dropped synonyms : ["REMOVED", "DROPPED", "REMOVED AT", "DELETED AT", "DROPPED AT", "DELETION TIME", "POLICY DELETION TIME"] unique: false data_type: TIMESTAMP_LTZ

  dimensions:

  • name: POLICY_ID expr: POLICY_ID description: Internal/system-generated identifier for the aggregation policy synonyms: ["POLICY ID", "ID", "IDENTIFIER"] data_type: NUMBER

  • name: POLICY_NAME expr: POLICY_NAME description: Name of the aggregation policy synonyms: ["POLICY NAME", "NAME", "AGGREGATION POLICY NAME"] data_type: VARCHAR

  • name: POLICY_SCHEMA_ID expr: POLICY_SCHEMA_ID description: Internal/system-generated identifier for the schema containing the policy synonyms: ["POLICY SCHEMA ID", "SCHEMA ID", "AGGREGATION POLICY SCHEMA ID", "AGGREGATION_POLICY PARENT SCHEMA ID", "POLICY PARENT SCHEMA ID"] data_type: NUMBER

  • name: POLICY_SCHEMA expr: POLICY_SCHEMA description: Schema that contains the aggregation policy synonyms: ["POLICY SCHEMA NAME", "SCHEMA NAME", "AGGREGATION_POLICY SCHEMA NAME", "AGGREGATION_POLICY PARENT SCHEMA NAME", "POLICY PARENT SCHEMA NAME"] data_type: VARCHAR

  • name: POLICY_CATALOG_ID expr: POLICY_CATALOG_ID description: Internal/system-generated identifier for the database containing the policy synonyms: ["CATALOG ID", "DATABASE ID", "POLICY PARENT CATALOG ID", "AGGREGATION_POLICY PARENT CATALOG ID", "POLICY PARENT DATABASE ID", "AGGREGATION_POLICY PARENT DATABASE ID"] data_type: NUMBER

  • name: POLICY_CATALOG expr: POLICY_CATALOG description: Database to which the aggregation policy belongs synonyms: ["CATALOG NAME", "DATABASE NAME", "POLICY PARENT CATALOG NAME", "AGGREGATION_POLICY PARENT CATALOG NAME", "POLICY PARENT DATABASE NAME", "AGGREGATION_POLICY PARENT DATABASE NAME"] data_type: VARCHAR