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
    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
comment	Optional	String	Specifies a comment for the table
grants	Optional	Map: See Supported View Grants to Roles	List of Privileges and Roles to which privileges are granted on the current view
is_secure	Optional	Boolean: false (default)	Specifies that the view is secure.
manage_mode	Optional	String: grants (default), all, none	Configures what properties to manage for the view
or_replace	Optional	Boolean: false (default)	Using OR REPLACE is the equivalent of using DROP VIEW on the existing view and then creating a new view with the same name.
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 table

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.

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
    or_replace: 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
    or_replace: true
    grants:
      SELECT:
        - rel(role.DEV_ROLE)