snowflake_stage is not idempotent

[jrobison-sb]

Provider Version

$ terraform version
Terraform v0.13.5
+ provider registry.terraform.io/chanzuckerberg/snowflake v0.25.24

Describe the bug

snowflake_stage is not idempotent. If it does not exist in Snowflake and it does exist in terraform state, the provider should detect this and recreate it. Instead, the terraform run fails when refreshing state with this error:

Error: 002003 (02000): SQL compilation error:
Stage 'DB_NAME.SCHEMA_NAME.STAGE_NAME' does not exist or not authorized.

Expected behavior

If it does not exist in Snowflake and it does exist in terraform state, the provider should detect this and recreate it.

Code samples and commands

The use case I'm working with is refreshing a copy of a non-prod database with a copy of prod. The steps to reproduce would look like this:

1. Create a database and a snowflake_stage and apply them in a prod account and a non-prod account. Works as expected. In this example the PROD_DB and NONPROD_DB's have both been created/managed by Terraform.
2. Refresh the non-prod account with a copy of prod, by running this in the non-prod account, out-of-band from Terraform:

create database if not exists PROD_DB as replica of MY_ORG.PROD_ACCOUNT.PROD_DB;
alter database PROD_DB refresh;
create or replace database NONPROD_DB clone PROD_DB;

This step recreated the NONPROD_DB, but with the same name as is used in terraform state, so the database will continue to be managed by terraform. No problem there. But the stage which was previously associated with NONPROD_DB will no longer exist. 3. Run terraform apply and see the above error. Terraform should instead detect that the stage is gone and should recreate it.

[amit0904]

I have encountered a problem where at each deployment the stage is recreated and that breaks link between stage and pipe. This should not be happening. Terraform should create or replace it only when something has changed.

[sfc-gh-asawicki]

We are closing this issue as part of a cleanup described in announcement. If you believe that the issue is still valid in v0.89.0, please open a new ticket.