Skip to main content

Database

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with databases:

  • Manage Lifecycle of new and existing Databases
  • Manage Lifecycle of cloned Databases
  • Manage Grants of Databases
  • Manage Tags applied to Databases

Supported Parameters

The engine supports the parameters listed below.

  • DATA_RETENTION_TIME_IN_DAYS: Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, range 0 to 90.
    • Configuration key: data_retention_time_in_days
    • Data Type: Integer
    • Range:
      • Standard Edition: 0 or 1
      • Enterprise Edition:
        • 0 to 90 for permanent databases
        • 0 or 1 for transient databases
  • FROM_DATABASE: Specify a database to create a clone from.
    • Configuration key: from_database
    • Data Type: String
  • FROM_SHARE: Specify a provider and a share in this map to create a database from a share.
    • Configuration key: from_share
    • Data Type: Object. See here for definition of from_share.
  • COMMENT: Specifies a comment for the database.
    • Configuration key: comment
    • Data Type: String
  • SCHEMAS: List of schemas to be managed for the database.
    • Configuration key: schemas
    • Data Type: List of Schema Definitions
  • NAMESPACING: Specify whether Prefix or Suffix or both are to be added to Database Name[Doesn't apply to Default Database].
    • Configuration key: namespacing
    • Data Type: String
    • Possible Values:
      • none
      • prefix
      • suffix
      • both(Default)
  • ENVIRONMENT: Specify the environment in which the Database is managed. Regex can be provided as well.
    • Configuration key: environment
    • Data Type: String
  • MANAGE_MODE: Configures what properties to manage for the Database.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Database.
    • Configuration key: grants
    • Data Type: Map
  • SCHEMA_GRANTS: List of Privileges and Roles to which privileges are granted to for all Schemas in the current Database.
    • Configuration key: schema_grants
    • Data Type: Map
  • TABLE_GRANTS: List of Privileges and Roles to which privileges are granted to for all Tables in the current Database.
    • Configuration key: table_grants
    • Data Type: Map
  • SHARES: List of Privileges and Shares to which privileges are granted to on the current Database.
    • Configuration key: shares
    • Data Type: Map
  • WITH_TAGS: List of Tag references to be applied to the current Database.
    • Configuration key: with_tags
    • Data Type: Object. See here for definition of with_tags.

Basic syntax

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>

from_share Parameter

User can specify the provider organization, account, and share names to create a database from a share.

A database can be created from a share present in:

  • Different organizations, only if both are in the same region
  • Same organization, but a different account

It accepts the following parameters:

  • PROVIDER: Name of the organization and account providing the share.
    • REQUIRED
    • Configuration Key: provider
    • Data Type: String
  • SHARE: Name of the share from which to create the database.
    • REQUIRED
    • Configuration Key: share
    • Data Type: String

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

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

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

  • VALUE: Value to assign to Tag
    • REQUIRED
    • Configuration key: value
    • Data Type: String
  • SCHEMA: Schema of the Tag identifier.
    • REQUIRED
    • Configuration key: schema
    • Data Type: String
  • DATABASE: Database of the Tag identifier. If omitted defaults to the name of the current database.
    • Configuration key: database
    • Data Type: String

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 is the list of Privileges Grant to Roles that can be specified in the database definition

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

For supported Schema Grants see Grants section in Schema

info

For supported Table Grants see Grants section in Table

Supported Database Grants to Shares

Following is the list of Privileges Grant to Shares that can be specified in the database definition

  • USAGE
  • REFERENCE_USAGE

Examples

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