Skip to content

View

You can provide configuration to Snowflake Object Lifecycle Engine for the following operation with view:

  • Manage the lifecycle of new and existing views
  • Manage the grants of a view
  • Manage the tags applied to views

Usage

create or alter view
- view:
    name: <view-name>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    statement: select ... from rel(...)
    manage_mode: all
    is_secure: false
    is_recursive: false
    is_temporary: false
    change_tracking: false
    depends_on:
      - rel(table.<dependency-table-name>)
      - rel(function.<dependency-function-name>)
    columns:
      <column-name>:
        masking_policy:
          policy_name: rel(masking_policy.<masking-policy-name>)
          using: [<column-name>]
    row_access_policy:
      policy_name: rel(row_access_policy.<row-access-policy-name>)
      "on": [<column-name>]
    shares:
      <privilege>:
        - rel(share.<share-name>)
        - rel(share.<share-name>)
    grants:
      <privilege>:
        - rel(role.<grant-name>)
        - rel(role.<grant-name>)
    with_tags:
      <tag-name>:
        value: <tag-value>
        schema: <source-schema>
manage grants
databases:
  <database-name>:
    schemas:
      <schema-name>:
        views:
          <view-name>:
            shares:
              <privilege>:
                - <share-name>
                - <share-name>
            grants:
              <privilege>:
                - <grant-name>
                - <grant-name>
            with_tags:
              <tag-name>:
                value: <tag-value>
                schema: <source-schema>

Supported parameters

The engine supports the parameters listed below.

Configuration Key Required/Optional Data Types and Values Description
change_tracking Optional Boolean: false (default) Specifies whether to enable change tracking on the view
columns Optional Object: See here for a definition of columns Specifies column-level configurations including masking policies
comment Optional String Specifies a comment for the view
copy_grants Optional Boolean: false (default) Retains the access permissions from the original view in case of an object re-creation
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this view depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
grants Optional Map: See Supported View Grants to Roles List of Privileges and Roles to which privileges are granted on the current view
is_recursive Optional Boolean: false (default) Specifies that the view is recursive
is_secure Optional Boolean: false (default) Specifies that the view is secure
is_temporary Optional Boolean: false (default) Specifies that the view is temporary and will be dropped at the end of the session
manage_mode Optional String: grants (default), all, none Configures what properties to manage for the view
row_access_policy Optional Object: See here for a definition of row_access_policy Specifies the row access policy to apply to the view
shares Optional Map: See Supported View Grants to shares List of shares to which privileges are granted
statement Optional;
REQUIRED if manage_mode is all
String: see here for a definition of statement Specifies the query used to create the view. Can be on one or more source tables or any other valid SELECT statement. This query serves as the text/definition for the view and is displayed in the SHOW VIEWS output and the VIEWS Information Schema view.
with_tags Optional Object: See here for a definition of with_tags List of tag references to be applied to the current view

statement parameter

The statement parameter is required, when you want to create or alter views and not just manage grants on them.

Tip

To create or alter views by using the statement parameter, set manage_mode to all.

You can define the statement parameter by directly inputting raw SQL code or referencing a file path containing the desired code using the !dataops.include directive. However, ensure the file is stored either in the $CI_PROJECT_DIR directory or in the parent directory of the YAML configuration file.

This code creates the view's logic, which executes complex operations or tasks within the Snowflake environment.

For one-line view definitions, put the SQL directly into the statement parameter.

dataops/snowflake/databases.template.yml
- view:
    name: PRODUCT
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Product Info"
    statement: "select * from rel(table.CUSTOMERS)"
    manage_mode: all

For a more complex definition, factor out your SQL into a separate file:

dataops/snowflake/databases.template.yml
- view:
    name: PRODUCT
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Product Info"
    statement: !dataops.include PRODUCT.sql
    manage_mode: all

The file PRODUCT.sql is co-located in the same directory as the databases.template.yml.

dataops/snowflake/PRODUCT.sql
select *
  from rel(table.CUSTOMERS) c,
    rel(table.ORDERS) s,
  where c.id = s.customer_id

Note that you can use the rel function in your SQL.

columns parameter

The columns parameter allows you to specify column-level configurations, including masking policies for individual columns in the view.

Each column is defined as a key-value pair where the key is the column name and the value is an object containing column-specific configurations.

Configuration Key Required/Optional Data Types and Values Description
masking_policy Optional Object Specifies the masking policy to apply to the column. See Column Masking Policy for details.

Column Masking Policy

When applying a masking policy to a column, you need to specify:

Configuration Key Required/Optional Data Types and Values Description
policy_name Required String Reference to the masking policy using rel(masking_policy.<policy-name>) or fully qualified name
using Required List of String List of column names to pass as arguments to the masking policy

Example for columns

columns:
  PRODUCT_ID: {}
  PRODUCT_NAME:
    masking_policy:
      policy_name: rel(masking_policy.SALARY_MASK)
      using: [PRODUCT_NAME]
  CUSTOMER_EMAIL:
    masking_policy:
      policy_name: rel(masking_policy.EMAIL_MASK)
      using: [CUSTOMER_EMAIL]

row_access_policy parameter

The row_access_policy parameter allows you to apply a row access policy to the view, controlling which rows are visible to users based on the policy's logic.

Configuration Key Required/Optional Data Types and Values Description
policy_name Required String Reference to the row access policy using rel(row_access_policy.<policy-name>) or fully qualified name
on Required List of String List of column names that the row access policy will evaluate

Example for row_access_policy

row_access_policy:
  policy_name: rel(row_access_policy.CUSTOMER_ROW_POLICY)
  "on": [PRODUCT_ID]

Note

The on parameter must be quoted as "on" in YAML to avoid conflicts with YAML reserved keywords.

with_tags parameter

You can specify a list of tags to apply to the table/column.

List multiple tags in the with_tags parameter in an object format with the following parameters:

Configuration Key Required/Optional Data Types and Values Description
value Required String Value to assign to the tag
database Optional String Database of the Tag identifier. If omitted, it defaults to the name of the database the table/column is in.
schema Optional String Schema of the Tag identifier. If omitted, it defaults to the name of the schema the table/column is in.

Example for with_tags

manage tags
- view:
    name: VIEW1
    database: rel(database.DB1)
    schema: rel(schema.SCHEMA1)
    with_tags:
      DEPARTMENT:
        value: Sales
        schema: TAG_LIBRARY
      CONFIDENTIALITY:
        value: Sensitive
        schema: TAG_LIBRARY
manage tags
databases:
  DB1:
    schemas:
      SCHEMA1:
        views:
          VIEW1:
            with_tags:
              DEPARTMENT:
                value: Sales
                schema: TAG_LIBRARY
              CONFIDENTIALITY:
                value: Sensitive
                schema: TAG_LIBRARY

Supported view grants to roles

Following are the privileges you can grant to roles in the view definition:

  • ALL PRIVILEGES
  • SELECT
  • OWNERSHIP

ALL PRIVILEGES handling

When you define ALL PRIVILEGES in the SOLE configuration file, you grant all the privileges listed above to roles on this object except OWNERSHIP. However, the management of ALL PRIVILEGES in SOLE differs from its handling in Snowflake. See Handling ALL PRIVILEGES in SOLE for more information.

Supported view grants to shares

Following is the privilege you can grant to shares in the view definition:

  • SELECT

Examples

Manage grants

These example assume that the view in question already exists. Since manage_mode defaults to grants, the example performs only grant management for roles or shares.

manage grants
- view:
    name: DEV_VIEW
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    grants:
      SELECT:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
    shares:
      SELECT:
        - rel(share.DEV_SHARE)
manage grants
databases:
  SALES_RECORD:
    schemas:
      SALES:
        views:
          DEV_VIEW:
            grants:
              SELECT:
                - HR_ROLE
                - ACCOUNTADMIN
            shares:
              SELECT:
                - DEV_SHARE

Defining a view

Note

Defining views with the statement parameter is only supported with SOLE for Data Products.

To define a view, include the statement parameter and set and have manage_mode: all in your definition. The rel() function allows you to reference other tables and views in your SELECT statement.

Define a view with the rel() function in the same schema
- view:
    name: PRODUCT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    comment: "Product Info"
    statement: "select * from rel(table.CUSTOMERS)"
    manage_mode: all
    is_secure: false
    copy_grants: false
    grants:
      SELECT:
        - rel(role.DEV_ROLE)
Define a view with the rel() function in a different db and schema
- view:
    name: ACCOUNT
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    comment: "Product Info"
    statement: "select * from rel(table.CUSTOMERS,database.OTHER_DB,schema.OTHER_SCHEMA)"
    manage_mode: all
    is_secure: true
    copy_grants: true
    grants:
      SELECT:
        - rel(role.DEV_ROLE)

View with column masking policy

This example demonstrates how to apply a masking policy to specific columns in a view.

View with column masking policy
- view:
    name: CUSTOMER_VIEW
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    statement: "select CUSTOMER_ID, CUSTOMER_NAME, EMAIL, PHONE from rel(table.CUSTOMERS)"
    manage_mode: all
    comment: "Customer view with email masking"
    columns:
      CUSTOMER_ID: {}
      CUSTOMER_NAME: {}
      EMAIL:
        masking_policy:
          policy_name: rel(masking_policy.EMAIL_MASK)
          using: [EMAIL]
      PHONE:
        masking_policy:
          policy_name: rel(masking_policy.PHONE_MASK)
          using: [PHONE]
    grants:
      SELECT:
        - rel(role.ANALYST_ROLE)

View with row access policy

This example shows how to apply a row access policy to control which rows are visible to users.

View with row access policy
- view:
    name: SALES_VIEW
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    statement: "select REGION_ID, SALES_AMOUNT, CUSTOMER_ID from rel(table.SALES)"
    manage_mode: all
    comment: "Sales view with row-level security"
    row_access_policy:
      policy_name: rel(row_access_policy.REGION_ACCESS_POLICY)
      "on": [REGION_ID]
    grants:
      SELECT:
        - rel(role.REGIONAL_MANAGER)

Comprehensive view with all security features

This example demonstrates a view with both column masking policies and row access policy, along with other configuration options.

Comprehensive view with masking and row access policies
- view:
    name: view_comprehensive
    database: rel(database.SALES_RECORD122245)
    schema: rel(schema.SALES)
    manage_mode: all
    statement: "select PRODUCT_ID, PRODUCT_NAME from rel(table.PRODUCT)"
    comment: "View with both column masking and row access policy"
    is_secure: true
    is_recursive: false
    is_temporary: false
    change_tracking: false
    columns:
      PRODUCT_ID: {}
      PRODUCT_NAME:
        masking_policy:
          policy_name: rel(masking_policy.SALARY_MASK)
          using: [PRODUCT_NAME]
    row_access_policy:
      policy_name: rel(row_access_policy.CUSTOMER_ROW_POLICY)
      "on": [PRODUCT_ID]
    grants:
      SELECT:
        - rel(role.DATA_ANALYST)
    with_tags:
      SECURITY_LEVEL:
        value: High
        schema: TAG_LIBRARY

Recursive view

This example shows how to create a recursive view for hierarchical data.

Recursive view
- view:
    name: EMPLOYEE_HIERARCHY
    database: rel(database.HR_DB)
    schema: rel(schema.EMPLOYEES)
    manage_mode: all
    is_recursive: true
    statement: |
      WITH RECURSIVE emp_hierarchy AS (
        SELECT employee_id, manager_id, employee_name, 1 as level
        FROM rel(table.EMPLOYEES)
        WHERE manager_id IS NULL
        UNION ALL
        SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
        FROM rel(table.EMPLOYEES) e
        INNER JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
      )
      SELECT * FROM emp_hierarchy
    grants:
      SELECT:
        - rel(role.HR_ROLE)

Temporary view

This example demonstrates creating a temporary view that exists only for the duration of the session.

Temporary view
- view:
    name: TEMP_ANALYSIS
    database: rel(database.ANALYTICS_DB)
    schema: rel(schema.TEMP)
    manage_mode: all
    is_temporary: true
    statement: "select * from rel(table.DAILY_METRICS) where metric_date = CURRENT_DATE()"
    comment: "Temporary view for daily analysis"
    grants:
      SELECT:
        - rel(role.ANALYST_ROLE)

View with change tracking

This example shows how to enable change tracking on a view.

View with change tracking enabled
- view:
    name: TRACKED_ORDERS
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    manage_mode: all
    change_tracking: true
    statement: "select ORDER_ID, CUSTOMER_ID, ORDER_DATE, TOTAL_AMOUNT from rel(table.ORDERS)"
    comment: "Orders view with change tracking"
    grants:
      SELECT:
        - rel(role.ETL_ROLE)