terraform-provider-snowflake icon indicating copy to clipboard operation
terraform-provider-snowflake copied to clipboard
verified publisher icon Snowflake-Labs

Please avoid destroying Snowflake database if not empty

Open hongbo-miao opened this issue 2 years ago • 2 comments

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: https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/2714
  • I am currently using two workaround ways to prevent it got dropped
    • Terraform's prevent_destroy = true
    • Transfer database ownership

hongbo-miao avatar Apr 16 '24 08:04 hongbo-miao

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.

sfc-gh-asawicki avatar Apr 16 '24 09:04 sfc-gh-asawicki

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! ☺️

hongbo-miao avatar Apr 16 '24 20:04 hongbo-miao