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

Classic Configuration

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>

Data Products Configuration

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>

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

Classic Configuration

manage tags

databases:
  DB1:
    schemas:
      SCHEMA1:
        views:
          VIEW1:
            with_tags:
              DEPARTMENT:
                value: Sales
                schema: TAG_LIBRARY
              CONFIDENTIALITY:
                value: Sensitive
                schema: TAG_LIBRARY

Data Products Configuration

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

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.

Classic Configuration

manage grants

databases:
  SALES_RECORD:
    schemas:
      SALES:
        views:
          DEV_VIEW:
            grants:
              SELECT:
                - HR_ROLE
                - ACCOUNTADMIN
            shares:
              SELECT:
                - DEV_SHARE

Data Products Configuration

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)

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)