Database

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with databases:

• Manage the lifecycle of new and existing databases
• Manage the lifecycle of cloned databases
• Manage grants of database
• Manage tags applied to database

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.

Database in Current Configuration

databases:
  <database-name>:
    <configuration-key>: <value>
    grants:
      <privilege>:
        - <role-name>
        - <role-name>
    shares:
      <privilege>:
        - <share-name>
    with_tags:
      <tag-name>:
        value: <tag-value>
        schema: <source-schema>

Database in SOLE for Data Products

- database:
    name: <database-name>
    <configuration-key>: <value>
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)
    shares:
      <privilege>:
        - rel(share.<share-name>)
        - rel(share.<share-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 Type and Values	Description
comment	Optional	String	Specifies a comment for the database
data_retention_time_in_days	Optional	Integer: For standard edition 0 or 1, and for enterprise edition 0 to 90 (permanent databases) and 0 or 1 (transient databases)	Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, range 0 to 90
deleted	Optional	Boolean: True enables deletion prevention, False does nothing	Specifies what objects are allowed to be deleted
environment	Optional	String	Specifies the environment in which the database is managed. Regex can be provided as well
from_database	Optional	String	Specifies a database to create a clone from. For more information about the database recreation behavior, see Database storing masking policies or tags.
from_share	Optional	Object: See here for a definition of from_share	Specifies a provider and a share in the map to create a database from a share. For more information about database recreation behavior, see Database storing masking policies or tags.
future_grants	Optional	Map: See Future grants on objects	List of future grants on the database objects managed outside SOLE
grants	Optional	Map: See Supported database grants to roles	List of Privileges and Roles to which privileges are granted on the current database
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the Database. See Changing Manage Mode before changing the value.
namespacing	Optional	String: both (default), none, prefix, suffix	Specifies whether prefix or suffix or both are to be added to database name [doesn't apply to Default Database]
schemas	Optional	List of schema definitions: both (default), none, prefix, suffix	List of schemas to be managed for the database
schema_grants	Optional	Map	List of Privileges and Roles to which privileges are granted for all schemas in the current database
shares	Optional	Map: See Supported database grants to shares	List of Privileges and Shares to which privileges are granted on the current database
table_grants	Optional	Map	List of Privileges and Roles to which privileges are granted for all tables in the current database
with_tags	Optional	Object: See here for a definition of with_tags	List of tag references to be applied to the current database

from_share parameter

You can specify the provider account and the share name to create a database from a share.

Parameter	Required/Optional	Data Type and Values	Description
provider	Required	String	Name of the account providing the share
share	Required	String	Name of the share from which to create the database

Examples

provider parameter with value as account

from_share:
  provider: "account"
  share: TEST_SHARE

provider parameter with value as organization and account

from_share:
  provider: 'organization"."account'
  share: TEST_SHARE

SNOWFLAKE_SAMPLE_DATA

If the Snowflake account is missing the SNOWFLAKE_SAMPLE_DATA database(Created by Snowflake), it can be created using the following configuration:

Database with v1 configuration

databases:
  SNOWFLAKE_SAMPLE_DATA:
    namespacing: none
    from_share:
      provider: SFC_SAMPLES
      share: SAMPLE_DATA
    grants:
      IMPORTED PRIVILEGES:
        - WRITER
        - READER

Database with v2 configuration

- database:
    name: SNOWFLAKE_SAMPLE_DATA
    namespacing: none
    from_share:
      provider: SFC_SAMPLES
      share: SAMPLE_DATA
    grants:
      IMPORTED PRIVILEGES:
        - rel(role.WRITER)
        - rel(role.READER)

with_tags parameter

You can specify a list of tags to apply to the database.

Multiple tags are listed in the with_tags parameter in an object format, with the following parameters:

Parameter	Required/Optional	Data Type and Values	Description
value	Required	String	Value to assign to tag
schema	Required	String	Schema of the tag identifier
database	Optional	String	Database of the tag identifier. If omitted defaults to the name of the current database.

Example

Database with v1 configuration

databases:
  DB1:
    with_tags:
      DEPARTMENT:
        value: Sales
        schema: TAG_LIBRARY
      CONFIDENTIALITY:
        value: Sensitive
        schema: TAG_LIBRARY
        database: DATABASE_2

Database with v2 configuration

- database:
    name: DB1
    with_tags:
      DEPARTMENT:
        value: Sales
        schema: TAG_LIBRARY
      CONFIDENTIALITY:
        value: Sensitive
        schema: TAG_LIBRARY
        database: DATABASE_2

Supported database grants to roles

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

• ALL PRIVILEGES
• MODIFY
• MONITOR
• USAGE
• CREATE SCHEMA
• IMPORTED PRIVILEGES
• OWNERSHIP

info

• For supported schema grants, see the grants section.
• For supported table grants, see the grants section.

Supported database grants to shares

Following are the privileges you can grant to shares in the database definition:

• USAGE
• REFERENCE_USAGE

Future grants on objects in a database

You can assign future grants on objects in a database or a schema when objects aren't managed by SOLE.

The DataOps SOLE and MATE engines manage all grants on Snowflake objects. The grants key in the Snowflake object configuration and MATE project configuration let you decide what grants to allow on objects before applying them to the target platform via automated DataOps pipelines.

But there are cases when Snowflake objects, typically schema-level objects like tables, are managed outside the two DataOps engines, namely:

• When objects are created during DataOps pipelines by an external tool
• When objects are created outside the DataOps pipelines

The future_grants key in the SOLE configuration lets you define future grants on the Snowflake objects managed outside DataOps and thus have control over their lifecycle.

It is worth mentioning that future grants support all grants supported by child objects in databases and schemas. For example, check out Supported table grants to roles for a list of the privileges you can grant to roles in the table definition with future_grants.

Example

Define future grants in any existing or new YAML file parsed by SOLE, typically in the directory specified in the parameter CONFIGURATION_DIR.

database:
  MY_DATABASE:
    ...
    future_grants:
      tables:
        SELECT:
          - MY_ROLE
        INSERT:
          - MY_ROLE
      views:
        SELECT:
          - MY_ROLE

note

If the schema level is present, the database level is ignored. For more information, see Future Grants on Database or Schema Objects.

Database storing masking policies or tags

If a database is used to store masking policies or tags, a recreation of the database will lead to an error. Read more about this behavior here.

The recreation can occur due to the following factors:

Database created from Share

If the database is created from a share, and the user either alters the specified share or removes the configuration, SOLE will attempt to recreate the database.

Database created from Clone

If the database is cloned, and the user either alters the specified source database or removes the configuration, SOLE will attempt to recreate the database.

SOLE stores the information about the source database locally as Snowflake doesn't persist this. Due to this, if the local/persistent cache is deleted for a cloned database using LIFECYCLE_STATE_RESET, it will lead to recreation.

Examples

Database in Current Configuration

databases:
  SALES_RECORD:
    comment: "product sales record"
    data_retention_time_in_days: 0
    from_database: "product_sales_record"
    namespacing: suffix
    schemas:
      SALES:
        comment: "SALES"
    grants:
      MODIFY:
        - HR_ROLE
        - ACCOUNTADMIN
    shares:
      USAGE:
        - DEV_SHARE
    schema_grants:
      CREATE TABLE:
        - ACCOUNTADMIN
    table_grants:
      UPDATE:
        - HR_ROLE
        - ACCOUNTADMIN

Database in SOLE for Data Products

- database:
    name: SALES_RECORD
    comment: "product sales record"
    data_retention_time_in_days: 0
    from_database: "product_sales_record"
    namespacing: suffix
    grants:
      MODIFY:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN
    shares:
      USAGE:
        - rel(share.DEV_SHARE)
    schema_grants:
      CREATE TABLE:
        - ACCOUNTADMIN
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN