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

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 with manage_mode set to all or grants in the current schema.
For more information, see Manage Mode.
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 provider providing the share
shareRequiredStringName of the share from which to create the database
accountOptionalStringName 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>"
databases:
<database_name>:
from_share:
provider: "organization"
account: "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:

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

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:

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

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

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