DATA_RETENTION_TIME_IN_DAYS on databases not using account level value

[emoyer0928]

Provider Version
This problem was introduced in v0.62.0. It is not an issue with v0.61.0.

Describe the bug
We set the DATA_RETENTION_TIME_IN_DAYS at the Snowflake account level. In previous versions of the provider, the DATA_RETENTION_TIME_IN_DAYS for databases matched the value set at the account level. In v0.62.0 this value is set to 1 day. Terraform attempts an update in-place to change the value from what we set at the account level to a 1.

Expected behavior
The DATA_RETENTION_TIME_IN_DAYS for a database should use:

• the value specified with the resource
• the value set at the account
• lastly a default of 1 day if no other value exists

Code samples and commands

• Set the DATA_RETENTION_TIME_IN_DAYS to a value >1 at the account level
• Create a database without specifying DATA_RETENTION_TIME_IN_DAYS and with <0.62.0
• Change the provider to 0.62.0 and run a TF plan without making any other changes. The plan will show an in-place update to change DATA_RETENTION_TIME_IN_DAYS for that database to 1.

[WobblyRobbly]

We are seeing a similar issue where the database level retention policy is being set to a default 1. The provider is querying Snowflake to get the database details correctly however it is then unable to parse those results into the required struct. Snowflake now returns an additional field, kind. When our schema tries to use the value, it's not available, so just defaults to 1

e.g.

data "snowflake_database" "this" {
  name = var.database_name
}

locals {
retention_time = var.data_retention_days != null ? var.data_retention_days : data.snowflake_database.this.retention_time
}

resource "snowflake_schema" "schema" {
  database            = var.database_name
  name                = local.schema_name
  comment             = "Terraform-managed Schema"
  is_transient        = false
  is_managed          = var.is_managed
  data_retention_days = local.retention_time
}

Unfortunately the provider doesn't error and carries on without logging an error, although if we set
export TF_LOG=DEBUG

we can see

2023-06-14T21:06:38.909+0100 [INFO]  provider.terraform-provider-snowflake: 2023/06/14 21:06:38 [DEBUG] sql-conn-query [query SHOW DATABASES LIKE 'DEV_DB' err <nil> duration 106.320233ms args {}]: timestamp=2023-06-14T21:06:38.908+0100
2023-06-14T21:06:38.909+0100 [INFO]  provider.terraform-provider-snowflake: 2023/06/14 21:06:38 [DEBUG] no databases found: timestamp=2023-06-14T21:06:38.909+0100
2023-06-14T21:06:38.909+0100 [INFO]  provider.terraform-provider-snowflake: 2023/06/14 21:06:38 [DEBUG] list database failed to decode: timestamp=2023-06-14T21:06:38.909+0100

We can see a successful SHOW DATABASES LIKE 'DEV_DB' in the Query History

A couple of potential fixes

1. Add kind into the various definitions and functions
2. Use Unsafe() to allow any new fields to be ignore until explicitly added in future versions of the provider

[WobblyRobbly]

So I think my issue is a different issue caused by default enablement of

https://community.snowflake.com/s/article/7-19-Behavior-Changes-June-5-8-2023

I have created #1882 for my issue

[sfc-gh-asawicki]

Hey @emoyer0928. The behavior of falling back to 1 should have been fixed with #2029. It was released as a part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/releases/tag/v0.71.0 release. Can you confirm that it fixed it for you?

[sfc-gh-asawicki]

Closing due to lack of response. @emoyer0928 please bump to the newest version and create new issue if the problem persists.