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

Default Configuration
Data Products 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:
    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 with `manage_mode` set to `all` or `grants` in the current schema. For more information, see Manage Mode.
`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 provider providing the share
`share`	Required	String	Name of the share from which to create the database
`account`	Optional	String	Name of the account providing the share

Examples

Mapping Snowsight query to SOLE configuration for the share database creation:

Snowsight query
CREATE DATABASE <database_name> FROM SHARE <organization>.<account>.<share_name>

In the Snowsight query, the placeholders organization, account, and share_name represent the actual values specific to your Snowflake environment. You can replace them accordingly in the SOLE configuration under the from_share section.

SOLE configuration
databases:
  <database_name>:
    from_share:
      provider: "<organization>"
      account: "<account>"
      share: "<share_name>"

from_share with provider and account
from_share with provider

databases:
  <database_name>:
    from_share:
      provider: "organization"
      account: "account"
      share: "share"

databases:
  <database_name>:
    from_share:
      provider: 'organization"."account'
      share: "share"

SNOWFLAKE_SAMPLE_DATA

If, for some reason, the Snowflake account is missing the database SNOWFLAKE_SAMPLE_DATA usually created by Snowflake and required to do some basic operations, you can recreate this database using the following configuration:

Default Configuration
Data Products Configuration

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

- 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

Default Configuration
Data Products Configuration

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

- 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 - only if the database is created from a share
OWNERSHIP
APPLYBUDGET
CREATE DATABASE ROLE

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.

info

For supported schema_grants, see the grants section for schemas.
For supported table_grants, see the grants section for tables.

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 or hybrid 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.

databases:
  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:

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

Behavioral changes upon cloning

With the release of the Snowflake 2023_07 bundle planned for January 2024, when a table is cloned, its load history will also be cloned. As a result, files are not reloaded, and data is not duplicated in the cloned table. You can override this behavior using the FORCE = TRUE COPY option.

See Snowflake Documentation for more information.

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

Default Configuration
Data Products 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
      APPLYBUDGET:
        - HR_ROLE
      CREATE DATABASE ROLE:
        - HR_ROLE
    shares:
      USAGE:
        - DEV_SHARE
    schema_grants:
      CREATE TABLE:
        - ACCOUNTADMIN
    table_grants:
      UPDATE:
        - HR_ROLE
        - ACCOUNTADMIN

- 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
      APPLYBUDGET:
        - rel(role.HR_ROLE)
      CREATE DATABASE ROLE:
        - rel(role.HR_ROLE)
    shares:
      USAGE:
        - rel(share.DEV_SHARE)
    schema_grants:
      CREATE TABLE:
        - ACCOUNTADMIN
    table_grants:
      UPDATE:
        - rel(role.HR_ROLE)
        - ACCOUNTADMIN

Usage​

Supported parameters​

from_share parameter​

Examples​

with_tags parameter​

Example​

Supported database grants to roles​

Supported database grants to shares​

Future grants on objects in a database​

Example​

Database storing masking policies or tags​

Database created from Share​

Database created from Clone​

Examples​

Usage

Supported parameters

`from_share` parameter

Examples

`with_tags` parameter

Example

Supported database grants to roles

Supported database grants to shares

Future grants on objects in a database

Example

Database storing masking policies or tags

Database created from Share

Database created from Clone

Examples