Terraform Snowflake Module - Database Schema

         

A Terraform module for creating and managing Snowflake databases and schemas using a map of configuration objects. Supports creating single or multiple databases with nested schemas in a single module call.

Features

• Map-based configuration for creating single or multiple databases
• Nested schema configuration within each database
• Built-in input validation with descriptive error messages
• Sensible defaults for optional properties
• Outputs keyed by database identifier for easy reference
• Support for transient databases and schemas
• Support for managed access schemas
• Configurable data retention time at database and schema level
• Database-level grants (USAGE)
• Schema-level grants (USAGE, CREATE FILE FORMAT, CREATE STAGE, CREATE TABLE, CREATE PIPE, CREATE DYNAMIC TABLE, CREATE STREAM, CREATE TASK, CREATE VIEW, CREATE MATERIALIZED VIEW)

Usage

Single Database (No Schemas)

module "database" {
  source = "github.com/subhamay-bhattacharyya-tf/terraform-snowflake-database-schema"

  database_configs = {
    analytics = {
      name                        = "ANALYTICS_DB"
      comment                     = "Analytics database for reporting"
      data_retention_time_in_days = 1
      is_transient                = false
      schemas                     = []
    }
  }
}

Database with One Schema

module "database" {
  source = "github.com/subhamay-bhattacharyya-tf/terraform-snowflake-database-schema"

  database_configs = {
    app = {
      name    = "APPLICATION_DB"
      comment = "Main application database"
      grants = {
        usage_roles = ["DATA_READER_ROLE"]
      }
      schemas = [
        {
          name       = "PUBLIC_DATA"
          comment    = "Public facing data schema"
          is_managed = true
          grants = {
            usage_roles              = ["DATA_READER_ROLE"]
            create_table_roles       = ["DATA_WRITER_ROLE"]
            create_file_format_roles = ["ETL_ROLE"]
          }
        }
      ]
    }
  }
}

Database with Multiple Schemas

module "database" {
  source = "github.com/subhamay-bhattacharyya-tf/terraform-snowflake-database-schema"

  database_configs = {
    datawarehouse = {
      name                        = "DATA_WAREHOUSE"
      comment                     = "Central data warehouse"
      data_retention_time_in_days = 7
      schemas = [
        {
          name       = "RAW"
          comment    = "Raw ingested data"
          is_managed = false
        },
        {
          name         = "STAGING"
          comment      = "Data transformation staging area"
          is_transient = true
        },
        {
          name                        = "CURATED"
          comment                     = "Curated business data"
          is_managed                  = true
          data_retention_time_in_days = 14
        }
      ]
    }
  }
}

Multiple Databases with Multiple Schemas

module "database" {
  source = "github.com/subhamay-bhattacharyya-tf/terraform-snowflake-database-schema"

  database_configs = {
    production = {
      name    = "PROD_DB"
      comment = "Production database"
      schemas = [
        { name = "APP", comment = "Application schema" },
        { name = "AUDIT", comment = "Audit logging schema", is_managed = true }
      ]
    },
    development = {
      name         = "DEV_DB"
      comment      = "Development database"
      is_transient = true
      schemas = [
        { name = "SANDBOX", comment = "Developer sandbox" },
        { name = "TESTING", comment = "Test data schema", is_transient = true }
      ]
    }
  }
}

Examples

• Database Only - Create a single database without schemas
• Database with One Schema - Create a database with a single schema
• Database with Multiple Schemas - Create a database with multiple schemas
• Multiple Databases with Multiple Schemas - Create multiple databases with multiple schemas

Requirements

Name	Version
terraform	>= 1.3.0
snowflake	>= 0.87.0

Providers

Name	Version
snowflake	>= 0.87.0

Inputs

Name	Description	Type	Default	Required
database_configs	Map of configuration objects for Snowflake databases and their schemas	map(object)	{}	no

database_configs Object Properties

Property	Type	Default	Description
name	string	-	Database name (required)
comment	string	null	Description of the database
data_retention_time_in_days	number	1	Time Travel data retention period in days
is_transient	bool	false	Whether the database is transient
grants	object	{}	Database-level grants configuration
schemas	list(object)	[]	List of schema configurations

grants Object Properties (Database Level)

Property	Type	Default	Description
usage_roles	list(string)	[]	Roles to grant USAGE privilege on the database

schemas Object Properties

Property	Type	Default	Description
name	string	-	Schema name (required)
comment	string	null	Description of the schema
is_transient	bool	false	Whether the schema is transient
is_managed	bool	false	Whether the schema has managed access
data_retention_time_in_days	number	null	Time Travel data retention (inherits from database if null)
grants	object	{}	Schema-level grants configuration

grants Object Properties (Schema Level)

Property	Type	Default	Description
usage_roles	list(string)	[]	Roles to grant USAGE privilege on the schema
create_file_format_roles	list(string)	[]	Roles to grant CREATE FILE FORMAT privilege
create_stage_roles	list(string)	[]	Roles to grant CREATE STAGE privilege
create_table_roles	list(string)	[]	Roles to grant CREATE TABLE privilege
create_pipe_roles	list(string)	[]	Roles to grant CREATE PIPE privilege
create_dynamic_table_roles	list(string)	[]	Roles to grant CREATE DYNAMIC TABLE privilege
create_stream_roles	list(string)	[]	Roles to grant CREATE STREAM privilege
create_task_roles	list(string)	[]	Roles to grant CREATE TASK privilege
create_view_roles	list(string)	[]	Roles to grant CREATE VIEW privilege
create_materialized_view_roles	list(string)	[]	Roles to grant CREATE MATERIALIZED VIEW privilege

Outputs

Name	Description
database_names	Map of database config keys to database names
database_fully_qualified_names	Map of database config keys to fully qualified names
databases	All database resource objects
schema_names	Nested map of database keys to schema names
schema_fully_qualified_names	Nested map of database keys to schema fully qualified names
schemas	All schema resource objects

Validation

The module validates inputs and provides descriptive error messages for:

• Empty database name
• Empty schema name
• Negative data_retention_time_in_days value

Testing

The module includes Terratest-based integration tests:

cd test
go mod tidy
go test -v -timeout 30m

Required environment variables for testing:

• SNOWFLAKE_ORGANIZATION_NAME - Snowflake organization name
• SNOWFLAKE_ACCOUNT_NAME - Snowflake account name
• SNOWFLAKE_USER - Snowflake username
• SNOWFLAKE_ROLE - Snowflake role (e.g., "SYSADMIN")
• SNOWFLAKE_PRIVATE_KEY - Snowflake private key for key-pair authentication

Test Coverage

Test File	Example Tested	Properties Validated
database_only_test.go	database-only	Database creation, configuration fidelity
database_with_one_schema_test.go	database-with-one-schema	Database/schema creation, managed access
databases_with_multiple_schemas_test.go	databases-with-multiple-schemas	Multiple schemas, transient schema, managed access
multiple_databases_with_multiple_schemas_test.go	multiple-databases-with-multiple-schemas	Multiple databases, transient resources

CI/CD Configuration

The CI workflow runs on:

• Push to main, feature/**, and bug/** branches (when *.tf, examples/**, or test/** changes)
• Pull requests to main (when *.tf, examples/**, or test/** changes)
• Manual workflow dispatch

The workflow includes:

• Terraform validation and format checking
• Examples validation
• Terratest integration tests (output displayed in GitHub Step Summary)
• Changelog generation (non-main branches)
• Semantic release (main branch only)

The CI workflow uses the following GitHub organization variables:

Variable	Description	Default
TERRAFORM_VERSION	Terraform version for CI jobs	1.3.0
GO_VERSION	Go version for Terratest	1.21
SNOWFLAKE_ORGANIZATION_NAME	Snowflake organization name	-
SNOWFLAKE_ACCOUNT_NAME	Snowflake account name	-
SNOWFLAKE_USER	Snowflake username	-
SNOWFLAKE_ROLE	Snowflake role (e.g., SYSADMIN)	-

The following GitHub secrets are required for Terratest integration tests:

Secret	Description	Required
SNOWFLAKE_PRIVATE_KEY	Snowflake private key for key-pair authentication	Yes

License

MIT License - See LICENSE for details.