Skip to main content

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.

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>

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired / OptionalData Type and ValuesDescription
commentOptionalStringSpecifies a comment for the database
data_retention_time_in_daysOptionalInteger: 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
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
environmentOptionalStringSpecifies the environment in which the database is managed. Regex can be provided as well
from_databaseOptionalStringSpecifies a database to create a clone from.
For more information about the database recreation behavior, see Database storing masking policies or tags.
from_shareOptionalObject: See here for a definition of from_shareSpecifies 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_grantsOptionalMap: See Future grants on objectsList of future grants on the database objects managed outside SOLE
grantsOptionalMap: See Supported database grants to rolesList of Privileges and Roles to which privileges are granted on the current database
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the Database.
See Changing Manage Mode before changing the value.
namespacingOptionalString: both (default), none, prefix, suffixSpecifies whether prefix or suffix or both are to be added to database name [doesn't apply to Default Database]
schemasOptionalList of schema definitions: both (default), none, prefix, suffixList of schemas to be managed for the database
schema_grantsOptionalMapList of Privileges and Roles to which privileges are granted for all schemas in the current database
sharesOptionalMap: See Supported database grants to sharesList of Privileges and Shares to which privileges are granted on the current database
table_grantsOptionalMapList of Privileges and Roles to which privileges are granted for all tables in the current database
with_tagsOptionalObject: See here for a definition of with_tagsList 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.

ParameterRequired/OptionalData Type and ValuesDescription
providerRequiredStringName of the account providing the share
shareRequiredStringName 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:

databases:
SNOWFLAKE_SAMPLE_DATA:
namespacing: none
from_share:
provider: SFC_SAMPLES
share: SAMPLE_DATA
grants:
IMPORTED PRIVILEGES:
- WRITER
- 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:

ParameterRequired/OptionalData Type and ValuesDescription
valueRequiredStringValue to assign to tag
schemaRequiredStringSchema of the tag identifier
databaseOptionalStringDatabase of the tag identifier. If omitted defaults to the name of the current database.

Example

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

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

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