Please avoid destroying Snowflake database if not empty

[hongbo-miao]

Terraform CLI and Provider Versions

• Terraform: v1.7.4
• Provider: v0.88.0

Use Cases or Problem Statement

Currently use Terraform to manage Snowflake database has high risk.

Proposal

It would be great to support avoiding destroy Snowflake database when either one condition meet

• if has schemas that are not PUBLIC inside
• if has tables in PUBLIC schema

Thanks!

For instance, AWS Terraform modules will prevent the destruction of a resource if there are any elements nested within it. For example, if there are IAM users within an IAM group, the destruction process will fail with an error:

│ Error: deleting IAM Group (MyIAMGroup): DeleteConflict: Cannot delete entity, must remove users from group first.
│ 	status code: 409, request id: 3f99ee73-64d3-4b26-8d82-affddbb7a0d5

Likewise, attempting to destroy a S3 bucket containing files will also result in failure.

How much impact is this issue causing?

High

Additional Information

• Related feature request: Please avoid destroying Snowflake schema if not empty #2714
• I am currently using two workaround ways to prevent it got dropped
  • Terraform's prevent_destroy = true
  • Transfer database ownership

[sfc-gh-asawicki]

Hey @hongbo-miao. Thanks for the suggestion.

We may consider it an opt-in mechanism, but it will be a low priority for the time being. We want to be aligned with Snowflake SQL API, and no errors/warnings are returned in a situation like this. Furthermore, in cases when something is deleted by accident (if using the terraform, you have to approve the terraform apply explicitly), there is still the time-travel mechanism: https://docs.snowflake.com/en/user-guide/data-time-travel that can be used to bring back db, schema, and table to life.

[hongbo-miao]

Thank you @sfc-gh-asawicki !

I've added additional workaround solutions to my original post. However, since we're dealing with the entire company's databases and schemas. These two features are quite essential for us to lower risk effectively. Thank you! ☺️