Skip to content

getindata/terraform-snowflake-database

BranchesTags

Repository files navigation

Snowflake Database Terraform Module

Snowflake Terraform

License Release

We help companies turn their data into assets

Terraform module for Snowflake database management.

  • Creates Snowflake database
  • Can create custom Snowflake database roles with role-to-role assignments
  • Can create a set of default database roles to simplify access management:
    • READONLY - granted USAGE and MONITOR privileges on the database
    • TRANSFORMER - allows creating schemas and some Snowflake objects in them
    • ADMIN - full access, including database options like data_retention_time_in_days
  • Can create number of schemas in the database with their specific stages and access roles
  • Can create database ownership to specified account role

USAGE

module "snowflake_database" {
  source = "getindata/database/snowflake"
  # version  = "x.x.x"
  name = "MY_DB"

  is_transient                = false
  data_retention_time_in_days = 1

  create_default_roles = true
}

EXAMPLES

  • Simple - Basic usage of the module
  • Complete - Advanced usage of the module

BREAKING CHANGES IN v2.x.x

Due to breaking changes in Snowflake provider and additional code optimizations, breaking changes were introduced in v2.0.0 version of this module.

List of code and variable (API) changes:

  • Switched to snowflake_database_role module to leverage new database_roles mechanism
  • database default_roles and custom_roles are now managed by getindata/database_role/snowflake module
  • snowflake_database resource was updated to use newly introduced changes in Snowflake provider
  • snowflake_schema resource was updated to use newly introduced changes in Snowflake provider
  • variable add_grants_to_existing_objects was removed as it is no longer needed
  • minimum Snowflake provider version is 0.90.0

For more information, refer to variables.tf, list of inputs below and Snowflake provider documentation

When upgrading from v1.x, expect most of the resources to be recreated - if recreation is impossible, then it is possible to import some existing resources.

Breaking changes in v3.x of the module

Due to replacement of nulllabel (context.tf) with context provider, some breaking changes were introduced in v3.0.0 version of this module.

List od code and variable (API) changes:

  • Removed context.tf file (a single-file module with additional variables), which implied a removal of all its variables (except name):
    • descriptor_formats
    • label_value_case
    • label_key_case
    • id_length_limit
    • regex_replace_chars
    • label_order
    • additional_tag_map
    • tags
    • labels_as_tags
    • attributes
    • delimiter
    • stage
    • environment
    • tenant
    • namespace
    • enabled
    • context
  • Remove support enabled flag - that might cause some backward compatibility issues with terraform state (please take into account that proper move clauses were added to minimize the impact), but proceed with caution
  • Additional context provider configuration
  • New variables were added, to allow naming configuration via context provider:
    • context_templates
    • name_schema
    • drop_public_schema_on_creation which is true by default

Inputs

Name Description Type Default Required
catalog The database parameter that specifies the default catalog to use for Iceberg tables string null no
comment Specifies a comment for the database string null no
context_templates Map of context templates used for naming conventions - this variable supersedes naming_scheme.properties and naming_scheme.delimiter configuration map(string) {} no
create_default_roles Whether the default roles should be created bool false no
data_retention_time_in_days Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table number null no
database_ownership_grant The name of the account role to which database privileges will be granted string null no
default_ddl_collation Specifies a default collation specification for all schemas and tables added to the database. string null no
drop_public_schema_on_creation Whether the PUBLIC schema should be dropped after the database creation bool true no
enable_console_output If true, enables stdout/stderr fast path logging for anonymous stored procedures bool null no
external_volume The database parameter that specifies the default external volume to use for Iceberg tables string null no
is_transient Specifies a database as transient. Transient databases do not have a Fail-safe period so they do not incur additional storage costs once they leave Time Travel; however, this means they are also not protected by Fail-safe in the event of a data loss bool null no
log_level Specifies the severity level of messages that should be ingested and made available in the active event table. Valid options are: [TRACE DEBUG INFO WARN ERROR FATAL OFF] string null no
max_data_extension_time_in_days Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for tables in the database to prevent streams on the tables from becoming stale number null no
name Name of the resource string n/a yes
name_scheme Naming scheme configuration for the resource. This configuration is used to generate names using context provider:
- properties - list of properties to use when creating the name - is superseded by var.context_templates
- delimiter - delimited used to create the name from properties - is superseded by var.context_templates
- context_template_name - name of the context template used to create the name
- replace_chars_regex - regex to use for replacing characters in property-values created by the provider - any characters that match the regex will be removed from the name
- extra_values - map of extra label-value pairs, used to create a name		 
object({
    properties            = optional(list(string), ["environment", "name"])
    delimiter             = optional(string, "_")
    context_template_name = optional(string, "snowflake-database")
    replace_chars_regex   = optional(string, "[^a-zA-Z0-9_]")
    extra_values          = optional(map(string))
  })
 {} no
quoted_identifiers_ignore_case If true, the case of quoted identifiers is ignored bool null no
replace_invalid_characters Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character () in query results for an Iceberg table bool null no
roles Roles created in the database scope 
map(object({
    name_scheme = optional(object({
      properties            = optional(list(string))
      delimiter             = optional(string)
      context_template_name = optional(string)
      replace_chars_regex   = optional(string)
      extra_labels          = optional(map(string))
    }))
    comment              = optional(string)
    role_ownership_grant = optional(string)
    granted_roles        = optional(list(string))
    granted_to_roles     = optional(list(string))
    granted_to_users     = optional(list(string))
    database_grants = optional(object({
      all_privileges    = optional(bool)
      with_grant_option = optional(bool, false)
      privileges        = optional(list(string), null)
    }))
    schema_grants = optional(list(object({
      all_privileges             = optional(bool)
      with_grant_option          = optional(bool, false)
      privileges                 = optional(list(string), null)
      all_schemas_in_database    = optional(bool, false)
      future_schemas_in_database = optional(bool, false)
      schema_name                = optional(string, null)
    })))
    schema_objects_grants = optional(map(list(object({
      all_privileges    = optional(bool)
      with_grant_option = optional(bool)
      privileges        = optional(list(string), null)
      object_name       = optional(string)
      on_all            = optional(bool, false)
      schema_name       = optional(string)
      on_future         = optional(bool, false)
    }))), {})
  }))
 {} no
schemas Schemas to be created in the database 
map(object({
    name_scheme = optional(object({
      properties            = optional(list(string))
      delimiter             = optional(string)
      context_template_name = optional(string)
      replace_chars_regex   = optional(string)
      extra_labels          = optional(map(string))
    }))
    skip_schema_creation                          = optional(bool, false)
    comment                                       = optional(string, null)
    data_retention_time_in_days                   = optional(number, null)
    max_data_extension_time_in_days               = optional(number, null)
    is_transient                                  = optional(bool, null)
    with_managed_access                           = optional(bool, null)
    external_volume                               = optional(string, null)
    catalog                                       = optional(string, null)
    replace_invalid_characters                    = optional(bool, null)
    default_ddl_collation                         = optional(string, null)
    storage_serialization_policy                  = optional(string, null)
    log_level                                     = optional(string, null)
    trace_level                                   = optional(string, null)
    suspend_task_after_num_failures               = optional(number, null)
    task_auto_retry_attempts                      = optional(number, null)
    user_task_managed_initial_warehouse_size      = optional(string, null)
    user_task_timeout_ms                          = optional(number, null)
    user_task_minimum_trigger_interval_in_seconds = optional(number, null)
    quoted_identifiers_ignore_case                = optional(bool, null)
    enable_console_output                         = optional(bool, null)
    pipe_execution_paused                         = optional(bool, null)
    create_default_roles                          = optional(bool)
    stages = optional(map(object({
      name_scheme = optional(object({
        properties            = optional(list(string))
        delimiter             = optional(string)
        context_template_name = optional(string)
        replace_chars_regex   = optional(string)
        extra_labels          = optional(map(string))
      }))
      aws_external_id      = optional(string)
      comment              = optional(string)
      copy_options         = optional(string)
      credentials          = optional(string)
      directory            = optional(string)
      encryption           = optional(string)
      file_format          = optional(string)
      snowflake_iam_user   = optional(string)
      storage_integration  = optional(string)
      url                  = optional(string)
      create_default_roles = optional(bool)
      roles = optional(map(object({
        name_scheme = optional(object({
          properties            = optional(list(string))
          delimiter             = optional(string)
          context_template_name = optional(string)
          replace_chars_regex   = optional(string)
          extra_labels          = optional(map(string))
        }))
        with_grant_option         = optional(bool)
        granted_to_roles          = optional(list(string))
        granted_to_database_roles = optional(list(string))
        granted_database_roles    = optional(list(string))
        stage_grants              = optional(list(string))
        all_privileges            = optional(bool)
      })), {})
    })), {})
    roles = optional(map(object({
      name_scheme = optional(object({
        properties            = optional(list(string))
        delimiter             = optional(string)
        context_template_name = optional(string)
        replace_chars_regex   = optional(string)
        extra_labels          = optional(map(string))
      }))
      comment                   = optional(string)
      granted_to_roles          = optional(list(string))
      granted_to_database_roles = optional(list(string))
      granted_database_roles    = optional(list(string))
      schema_grants = optional(list(object({
        all_privileges    = optional(bool)
        with_grant_option = optional(bool, false)
        privileges        = optional(list(string), null)
      })))
      schema_objects_grants = optional(map(list(object({
        all_privileges    = optional(bool)
        with_grant_option = optional(bool)
        privileges        = optional(list(string), null)
        object_name       = optional(string)
        on_all            = optional(bool, false)
        on_future         = optional(bool, false)
      }))), {})
    })), {})
  }))
 {} no
storage_serialization_policy The storage serialization policy for Iceberg tables that use Snowflake as the catalog. Valid options are: [COMPATIBLE OPTIMIZED] string null no
suspend_task_after_num_failures How many times a task must fail in a row before it is automatically suspended. 0 disables auto-suspending number null no
task_auto_retry_attempts Maximum automatic retries allowed for a user task number null no
trace_level Controls how trace events are ingested into the event table. Valid options are: [ALWAYS ON_EVENT OFF] string null no
user_task_managed_initial_warehouse_size The initial size of warehouse to use for managed warehouses in the absence of history string null no
user_task_minimum_trigger_interval_in_seconds Minimum amount of time between Triggered Task executions in seconds number null no
user_task_timeout_ms User task execution timeout in milliseconds number null no

Modules

Name Source Version
roles_deep_merge Invicton-Labs/deepmerge/null 0.1.5
snowflake_custom_role getindata/database-role/snowflake 2.0.1
snowflake_default_role getindata/database-role/snowflake 2.0.1
snowflake_schema getindata/schema/snowflake 3.0.0

Outputs

Name Description
catalog The database parameter that specifies the default catalog to use for Iceberg tables
data_retention_time_in_days Data retention days for the database
database_ownership_grant The name of the account role to which database ownership will be granted
database_roles Snowflake Database roles
default_ddl_collation Specifies a default collation specification for all schemas and tables added to the database.
enable_console_output If true, enables stdout/stderr fast path logging for anonymous stored procedures
external_volume The database parameter that specifies the default external volume to use for Iceberg tables
is_transient Specifies a database as transient. Transient databases do not have a Fail-safe period so they do not incur additional storage costs once they leave Time Travel; however, this means they are also not protected by Fail-safe in the event of a data loss
log_level Specifies the severity level of messages that should be ingested and made available in the active event table. Valid options are: [TRACE DEBUG INFO WARN ERROR FATAL OFF]
max_data_extension_time_in_days Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for tables in the database to prevent streams on the tables from becoming stale
name Name of the database
quoted_identifiers_ignore_case If true, the case of quoted identifiers is ignored
replace_invalid_characters Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character () in query results for an Iceberg table
schemas This database schemas
storage_serialization_policy The storage serialization policy for Iceberg tables that use Snowflake as the catalog. Valid options are: [COMPATIBLE OPTIMIZED]
suspend_task_after_num_failures How many times a task must fail in a row before it is automatically suspended. 0 disables auto-suspending
task_auto_retry_attempts Maximum automatic retries allowed for a user task
trace_level Controls how trace events are ingested into the event table. Valid options are: [ALWAYS ON_EVENT OFF]
user_task_managed_initial_warehouse_size The initial size of warehouse to use for managed warehouses in the absence of history
user_task_minimum_trigger_interval_in_seconds Minimum amount of time between Triggered Task executions in seconds
user_task_timeout_ms User task execution timeout in milliseconds

Providers

Name Version
context >=0.4.0
snowflake ~> 0.95

Requirements

Name Version
terraform >= 1.3
context >=0.4.0
snowflake ~> 0.95

Resources

Name Type
snowflake_database.this resource
snowflake_grant_ownership.database_ownership resource
context_label.this data source

CONTRIBUTING

Contributions are very welcomed!

Start by reviewing contribution guide and our code of conduct. After that, start coding and ship your changes by creating a new PR.

LICENSE

Apache 2 Licensed. See LICENSE for full details.

AUTHORS

Made with contrib.rocks.

About

Terraform module for managing Snowflake database

Topics

schema database terraform snowflake terraform-module snowflake-db

Resources

Readme

License

Apache-2.0 license

Code of conduct

Code of conduct
Activity
Custom properties

Stars

4 stars

Watchers

7 watching

Forks

0 forks
Report repository

Releases 17

Version v3.0.0 Latest
Nov 13, 2024
+ 16 releases

Packages

No packages published

Contributors 5

Languages