feat: standard database v1 readiness (#2842)

Done in this pr: - Addressed comments from the [previous pr](#2834) - Added new `standard_database` resource (also added examples, import, mentioned in migration notes) - Deprecate old database resource - Adjust `databases` data-source to align with v1 requirements (add filters, missing values and outputs from DESCRIBE and SHOW PARAMETERS commands) - Replaced `snowflake_database` in all of the examples to `snowflake_standard_database` To be done: - Add missing properties on all three new database types - Make sure all of the issues were resolved with the new types of databases ## Test Plan  * [x] acceptance tests ## References  [CREATE DATABASE](https://docs.snowflake.com/en/sql-reference/sql/create-database) ## Update Changes done: - Added missing parameters to all the database types and moved them and operations on them to a common place (only metric_level parameter wasn't included as it is a preview feature and there wasn't enough information about and it seemed like ORGADMIN or certain privileges were required to be able to test/use it). - Switched to plain values instead of nested ones for parameters and adjusted customdiffs, so that the state is refreshed always when expected. - Every database type resolves database-connected issues (most if not all of them were already resolved by the latest versions of the provider for the `snowflake_database` resource). - Refresh for secondary was not added, as replication guidelines are recommending to create a task that would be refreshing the replica at a certain interval. To aid our users I created an example showing how to create a task that would run the refresh every 10 minutes. An easy upgrade (if we would like to) would be add a toggle to call refresh every read operation. The toggle could be turned on by default with the option to turn it off and refresh it in the task "manually". - State upgrader for snowflake_database (because we chose to rename the old one for have _old suffix).
Snowflake-Labs · Jun 17, 2024 · 3c11953 · 3c11953
1 parent 6664457
commit 3c11953
Show file tree

Hide file tree

Showing 93 changed files with 5,898 additions and 2,033 deletions.
diff --git a/MIGRATION_GUIDE.md b/MIGRATION_GUIDE.md
@@ -60,6 +60,106 @@ To easily handle three-value logic (true, false, unknown) in provider's configs,
 #### *(note)* `resource_monitor` validation and diff suppression
 `resource_monitor` is an identifier and handling logic may be still slightly changed as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#identifiers-rework. It should be handled automatically (without needed manual actions on user side), though, but it is not guaranteed.
 
+### new database resources
+As part of the [preparation for v1](https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#preparing-essential-ga-objects-for-the-provider-v1), we split up the database resource into multiple ones:
+- Standard database - can be used as `snowflake_database` (replaces the old one and is used to create databases with optional ability to become a primary database ready for replication)
+- Shared database - can be used as `snowflake_shared_database` (used to create databases from externally defined shares)
+- Secondary database - can be used as `snowflake_secondary_database` (used to create replicas of databases from external sources)
+
+All the field changes in comparison to the previous database resource are:
+- `is_transient`
+    - in `snowflake_shared_database`
+        - removed: the field is removed from `snowflake_shared_database` as it doesn't have any effect on shared databases.
+- `from_database` - database cloning was entirely removed and is not possible by any of the new database resources.
+- `from_share` - the parameter was moved to the dedicated resource for databases created from shares `snowflake_shared_database`. Right now, it's a text field instead of a map. Additionally, instead of legacy account identifier format we're expecting the new one that with share looks like this: `<organization_name>.<account_name>.<share_name>`. For more information on account identifiers, visit the [official documentation](https://docs.snowflake.com/en/user-guide/admin-account-identifier).
+- p, 
+- `from_replication` - the parameter was moved to the dedicated resource for databases created from primary databases `snowflake_secondary_database`
+- `replication_configuration` - renamed: was renamed to `configuration` and is only available in the `snowflake_database`. Its internal schema changed that instead of list of accounts, we expect a list of nested objects with accounts for which replication (and optionally failover) should be enabled. More information about converting between both versions [here](#resource-renamed-snowflake_database---snowflake_database_old). Additionally, instead of legacy account identifier format we're expecting the new one that looks like this: `<organization_name>.<account_name>`. For more information on account identifiers, visit the [official documentation](https://docs.snowflake.com/en/user-guide/admin-account-identifier).
+- `data_retention_time_in_days` 
+  - in `snowflake_shared_database`
+      - removed: the field is removed from `snowflake_shared_database` as it doesn't have any effect on shared databases.
+  - in `snowflake_database` and `snowflake_secondary_database`
+    - adjusted: now, it uses different approach that won't set it to -1 as a default value, but rather fills the field with the current value from Snowflake (this still can change).
+- added: The following set of [parameters](https://docs.snowflake.com/en/sql-reference/parameters) was added to every database type:
+    - `max_data_extension_time_in_days`
+    - `external_volume`
+    - `catalog`
+    - `replace_invalid_characters`
+    - `default_ddl_collation`
+    - `storage_serialization_policy`
+    - `log_level`
+    - `trace_level`
+    - `suspend_task_after_num_failures`
+    - `task_auto_retry_attempts`
+    - `user_task_managed_initial_warehouse_size`
+    - `user_task_timeout_ms`
+    - `user_task_minimum_trigger_interval_in_seconds`
+    - `quoted_identifiers_ignore_case`
+    - `enable_console_output`
+
+The split was done (and will be done for several objects during the refactor) to simplify the resource on maintainability and usage level.
+Its purpose was also to divide the resources by their specific purpose rather than cramping every use case of an object into one resource.
+
+### Resource renamed snowflake_database -> snowflake_database_old
+We made a decision to use the existing `snowflake_database` resource for redesigning it into a standard database.
+The previous `snowflake_database` was renamed to `snowflake_database_old` and the current `snowflake_database`
+contains completely new implementation that follows our guidelines we set for V1.
+When upgrading to the 0.93.0 version, the automatic state upgrader should cover the migration for databases that didn't have the following fields set:
+- `from_share` (now, the new `snowflake_shared_database` should be used instead)
+- `from_replica` (now, the new `snowflake_secondary_database` should be used instead)
+- `replication_configuration`
+
+For configurations containing `replication_configuraiton` like this one:
+```terraform
+resource "snowflake_database" "test" {
+  name = "<name>"
+  replication_configuration {
+    accounts = ["<account_locator>", "<account_locator_2>"]
+    ignore_edition_check = true
+  }
+}
+```
+
+You have to transform the configuration into the following format (notice the change from account locator into the new account identifier format):
+```terraform
+resource "snowflake_database" "test" {
+  name = "%s"
+  replication {
+    enable_to_account {
+      account_identifier = "<organization_name>.<account_name>"
+      with_failover      = false
+    }
+    enable_to_account {
+      account_identifier = "<organization_name_2>.<account_name_2>"
+      with_failover      = false
+    }
+  }
+  ignore_edition_check = true
+}
+```
+
+If you had `from_database` set, it should migrate automatically.
+For now, we're dropping the possibility to create a clone database from other databases.
+The only way will be to clone a database manually and import it as `snowflake_database`, but if
+cloned databases diverge in behavior from standard databases, it may cause issues.
+
+For databases with one of the fields mentioned above, manual migration will be needed.
+Please refer to our [migration guide](https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/docs/technical-documentation/resource_migration.md) to perform zero downtime migration.
+
+If you would like to upgrade to the latest version and postpone the upgrade, you still have to perform the maunal migration
+to the `snowflake_database_old` resource by following the [zero downtime migrations document](https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/docs/technical-documentation/resource_migration.md).
+The only difference would be that instead of writing/generating new configurations you have to just rename the existing ones to contain `_old` suffix.
+
+### *(behavior change)* snowflake_databases datasource
+- `terse` and `history` fields were removed.
+- `replication_configuration` field was removed from `databases`.
+- `pattern` was replaced by `like` field.
+- Additional filtering options added (`limit`).
+- Added missing fields returned by SHOW DATABASES.
+- Added outputs from **DESC DATABASE** and **SHOW PARAMETERS IN DATABASE** (they can be turned off by declaring `with_describe = false` and `with_parameters = false`, **they're turned on by default**). 
+The additional parameters call **DESC DATABASE** (with `with_describe` turned on) and **SHOW PARAMETERS IN DATABASE** (with `with_parameters` turned on) **per database** returned by **SHOW DATABASES**. 
+It's important to limit the records and calls to Snowflake to the minimum. That's why we recommend assessing which information you need from the data source and then providing strong filters and turning off additional fields for better plan performance.
+
 ## v0.89.0 ➞ v0.90.0
 ### snowflake_table resource changes
 #### *(behavior change)* Validation to column type added
@@ -79,7 +179,7 @@ resource "snowflake_tag_masking_policy_association" "name" {
     masking_policy_id = snowflake_masking_policy.example_masking_policy.id
 }
 ```
-
+    
 After
 ```terraform
 resource "snowflake_tag_masking_policy_association" "name" {

diff --git a/docs/data-sources/databases.md b/docs/data-sources/databases.md
@@ -12,44 +12,147 @@ description: |-
 ## Example Usage
 
 ```terraform
-data "snowflake_databases" "this" {}
+# Simple usage
+data "snowflake_databases" "simple" {
+}
+
+output "simple_output" {
+  value = data.snowflake_databases.simple.databases
+}
+
+# Filtering (like)
+data "snowflake_databases" "like" {
+  like = "database-name"
+}
+
+output "like_output" {
+  value = data.snowflake_databases.like.databases
+}
+
+# Filtering (starts_with)
+data "snowflake_databases" "starts_with" {
+  starts_with = "database-"
+}
+
+output "starts_with_output" {
+  value = data.snowflake_databases.starts_with.databases
+}
+
+# Filtering (limit)
+data "snowflake_databases" "limit" {
+  limit {
+    rows = 10
+    from = "database-"
+  }
+}
+
+output "limit_output" {
+  value = data.snowflake_databases.limit.databases
+}
+
+# Without additional data (to limit the number of calls make for every found database)
+data "snowflake_databases" "only_show" {
+  # with_describe is turned on by default and it calls DESCRIBE DATABASE for every database found and attaches its output to databases.*.description field
+  with_describe = false
+
+  # with_parameters is turned on by default and it calls SHOW PARAMETERS FOR DATABASE for every database found and attaches its output to databases.*.parameters field
+  with_parameters = false
+}
+
+output "only_show_output" {
+  value = data.snowflake_databases.only_show.databases
+}
+
+# Ensure the number of databases is equal to at least one element (with the use of postcondition)
+data "snowflake_databases" "assert_with_postcondition" {
+  starts_with = "database-name"
+  lifecycle {
+    postcondition {
+      condition     = length(self.databases) > 0
+      error_message = "there should be at least one database"
+    }
+  }
+}
+
+# Ensure the number of databases is equal to at exactly one element (with the use of check block)
+check "database_check" {
+  data "snowflake_databases" "assert_with_check_block" {
+    like = "database-name"
+  }
+
+  assert {
+    condition     = length(data.snowflake_databases.test.databases) == 1
+    error_message = "Databases filtered by '${data.snowflake_databases.test.like}' returned ${length(data.snowflake_databases.test.databases)} databases where one was expected"
+  }
+}
 ```
 
 <!-- schema generated by tfplugindocs -->
 ## Schema
 
 ### Optional
 
-- `history` (Boolean) Optionally includes dropped databases that have not yet been purged The output also includes an additional `dropped_on` column
-- `pattern` (String) Optionally filters the databases by a pattern
-- `starts_with` (String) Optionally filters the databases by a pattern
-- `terse` (Boolean) Optionally returns only the columns `created_on` and `name` in the results
+- `like` (String) Filters the output with **case-insensitive** pattern, with support for SQL wildcard characters (`%` and `_`).
+- `limit` (Block List, Max: 1) Limits the number of rows returned. If the `limit.from` is set, then the limit wll start from the first element matched by the expression. The expression is only used to match with the first element, later on the elements are not matched by the prefix, but you can enforce a certain pattern with `starts_with` or `like`. (see [below for nested schema](#nestedblock--limit))
+- `starts_with` (String) Filters the output with **case-sensitive** characters indicating the beginning of the object name.
+- `with_describe` (Boolean) Runs DESC DATABASE for each database returned by SHOW DATABASES. The output of describe is saved to the description field. By default this value is set to true.
+- `with_parameters` (Boolean) Runs SHOW PARAMETERS FOR DATABASE for each database returned by SHOW DATABASES. The output of describe is saved to the parameters field as a map. By default this value is set to true.
 
 ### Read-Only
 
-- `databases` (List of Object) Snowflake databases (see [below for nested schema](#nestedatt--databases))
+- `databases` (List of Object) Holds the output of SHOW DATABASES. (see [below for nested schema](#nestedatt--databases))
 - `id` (String) The ID of this resource.
 
+<a id="nestedblock--limit"></a>
+### Nested Schema for `limit`
+
+Required:
+
+- `rows` (Number) The maximum number of rows to return.
+
+Optional:
+
+- `from` (String) Specifies a **case-sensitive** pattern that is used to match object name. After the first match, the limit on the number of rows will be applied.
+
+
 <a id="nestedatt--databases"></a>
 ### Nested Schema for `databases`
 
 Read-Only:
 
 - `comment` (String)
 - `created_on` (String)
+- `description` (List of Object) (see [below for nested schema](#nestedobjatt--databases--description))
 - `is_current` (Boolean)
 - `is_default` (Boolean)
+- `is_transient` (Boolean)
+- `kind` (String)
 - `name` (String)
 - `options` (String)
 - `origin` (String)
 - `owner` (String)
-- `replication_configuration` (List of Object) (see [below for nested schema](#nestedobjatt--databases--replication_configuration))
+- `owner_role_type` (String)
+- `parameters` (List of Object) (see [below for nested schema](#nestedobjatt--databases--parameters))
+- `resource_group` (String)
 - `retention_time` (Number)
 
-<a id="nestedobjatt--databases--replication_configuration"></a>
-### Nested Schema for `databases.replication_configuration`
+<a id="nestedobjatt--databases--description"></a>
+### Nested Schema for `databases.description`
+
+Read-Only:
+
+- `created_on` (String)
+- `kind` (String)
+- `name` (String)
+
+
+<a id="nestedobjatt--databases--parameters"></a>
+### Nested Schema for `databases.parameters`
 
 Read-Only:
 
-- `accounts` (List of String)
-- `ignore_edition_check` (Boolean)
+- `default` (String)
+- `description` (String)
+- `key` (String)
+- `level` (String)
+- `value` (String)
diff --git a/docs/index.md b/docs/index.md
@@ -231,6 +231,7 @@ The Snowflake provider will use the following order of precedence when determini
 
 - [snowflake_account_grant](./docs/resources/account_grant) - use [snowflake_grant_privileges_to_account_role](./docs/resources/grant_privileges_to_account_role) instead
 - [snowflake_database_grant](./docs/resources/database_grant) - use [snowflake_grant_privileges_to_account_role](./docs/resources/grant_privileges_to_account_role) instead
+- [snowflake_database_old](./docs/resources/database_old)
 - [snowflake_external_table_grant](./docs/resources/external_table_grant) - use [snowflake_grant_privileges_to_account_role](./docs/resources/grant_privileges_to_account_role) instead
 - [snowflake_failover_group_grant](./docs/resources/failover_group_grant) - use [snowflake_grant_privileges_to_account_role](./docs/resources/grant_privileges_to_account_role) instead
 - [snowflake_file_format_grant](./docs/resources/file_format_grant) - use [snowflake_grant_privileges_to_account_role](./docs/resources/grant_privileges_to_account_role) instead