Inconsistent Snowflake state on apply if errors occur

[AndreasHEbcont]

Terraform CLI and Provider Versions

Snowflake 0.88
Terraform >= 1.0.11

Terraform Configuration

e.g. warehouse module:

resource "snowflake_warehouse" "warehouse" {
  name                      = var.warehouse_name
  comment                   = "A warehouse" 
  warehouse_size            = var.warehouse_size
  auto_suspend              = var.auto_suspend
  initially_suspended       = var.initially_suspended
  max_cluster_count         = var.max_cluster_count
  enable_query_acceleration = var.enable_query_acceleration
  resource_monitor = var.resource_monitor
}

Database module:

resource "snowflake_database" "database" {
  name                        = var.database_name
  comment                     = "A Database"
  data_retention_time_in_days = var.database_data_retention_time_in_days
  is_transient                = var.database_is_transistent
}

Expected Behavior

Ressources will be created/destroyed and the Terraform state file will be updated accordingly

Actual Behavior

The ressources will be created/destroyed but the changes will not be saved to the Terraform state file

Steps to Reproduce

1. terraform apply
   ERROR OCCURS
2. 'terraform apply'
   STATE IS INCONSISTENT resulting in multiple errors

How much impact is this issue causing?

High

Logs

No response

Additional Information

We are currently creating various resources via modules, including databases, warehouses, roles, and grants. During the apply statement of Terraform, if there are any errors, the state file will be inconsistent.

For example:

We want to create a new database, warehouse, and corresponding roles. During the Terraform apply statement, we made an error, and the executing role did not have sufficient privileges:

Error: 003001 (42501): SQL access control error: Insufficient privileges to operate on account '[ACCOUNTNAME]'

If we then try to execute a second Terraform apply while creating new resources, we will get multiple errors indicating these resources already exist. The reason for that is when the Terraform apply failed the first time, it already created some of the resources but failed to update the state file:

SQL compilation error: Object '[DATABASENAME]' already exists.

On the other hand, if an error occurs while destroying resources, Terraform will destroy some of the resources but not update the state file, resulting in an error stating that the resources to be destroyed do not exist. That is because Terraform already destroyed them, but they are still included in the Terraform state file.

This behavior occurs frequently and currently the only workaround we found is to manually remove the resources from the state file.

[sfc-gh-asawicki]

Hey @AndreasHEbcont. Thanks for reaching out to us.

There are several resources that behave incorrectly in such a case but, to my knowledge, database and warehouse are not one of them.

Please provide the exact minimal steps to reproduce the incorrect behavior, so we can reproduce it.

[AndreasHEbcont]

Hey @sfc-gh-asawicki sure:

1. Create following Terraform configuration:

main.tf

---

resource "snowflake_database" "simple" {
name = "testing"
comment = "test comment"
data_retention_time_in_days = 3
}

resource "snowflake_role" "parent_role" {
name = "parent_role_name"
}

resource "snowflake_database_role" "db_role" {
database = snowflake_database.simple.name
name = "db_role_name"
}

resource "snowflake_grant_database_role" "g" {
database_role_name = ""${snowflake_database_role.db_role.database}"."${snowflake_database_role.db_role.name}""
parent_role_name = snowflake_role.parent_role.name
}

resource "snowflake_grant_privileges_to_database_role" "example" {
database_role_name = ""${snowflake_database_role.db_role.database}"."${snowflake_database_role.db_role.name}""
on_database = snowflake_database_role.db_role.database
all_privileges = true
}

resource "snowflake_warehouse" "warehouse" {
name = "test"
comment = "foo"
warehouse_size = "small"
resource_monitor = "null"
}

---

2.Execute a Terraform apply with an user with following account level privilages:

CREATE DATABASE
CREATE INTEGRATION
CREATE ROLE
CREATE WAREHOUSE
MANAGE GRANTS
MANAGE WAREHOUSES

This should result in an Error as the account does not have enought privilages to assign the resource_monitor. At this point the State file was already diffrent to the provisioned ressource, as Terraform managed to create the "SYSADMIN"role and the Database but did not add it to the state file.

(3). If the ressources somehow should have been provisioned or the state should not be out of sync:

Alter the configuration file to just include the warehouse and change the name and comment in one terraform apply:

main.tf

---

resource "snowflake_warehouse" "warehouse" {
name = "newName"
comment = "NewComment"
warehouse_size = "small"
}

---

This should result in a Error also creating an out of sync state file because Terraform will first try to rename the warehouse and afterwards try to change to comment, not being able to find the renamed warehouse.

Please let me know if you need further information! Or of course if the provided configuration is incorrect

[sfc-gh-asawicki]

The case with the rename is already handled globally as part of #2702.

We will try to reproduce the first case in the next few days. I have two questions, though:

1. Why are you referring to the SYSADMIN role that is not part of the config?
2. Can you share the result of the first plan + apply and subsequent plan + apply (after the first error) - with TF_LOG=DEBUG flag enabled?

[AndreasHEbcont]

hey,

1. You are completly correct and I apologize. In this case the "SYSADMIN" role is equivalent to the parent_role ressource.
2. I will get in cotact with my superviser about providing log files.

Regards, Andreas

[AndreasHEbcont]

Hey,

I can provide you with the Error messages when executing both apply Statements. I can also provide you with the full deployment log including plan and apply but only throught a secure connection. Is there a way we can provide it to you without sharing it in github?

Error on 1.st apply:

Error: 003001 (42501): SQL access control error:
Insufficient privileges to operate on account '[accountname]'

with module.workspace_RB_APMEA_IT_BUSINESS_APPS.module.warehouses["WH01"].snowflake_warehouse.warehouse,
on modules/workspace/warehouse/main.tf line 1, in resource "snowflake_warehouse" "warehouse":
1: resource "snowflake_warehouse" "warehouse" {

2.Apply without making any changes to the configuration:

Error: Failed to create account role

with module.workspace_RB_APMEA_IT_BUSINESS_APPS.snowflake_role.role_SYSADMIN,
on modules/workspace/main.tf line 36, in resource "snowflake_role" "role_SYSADMIN":
36: resource "snowflake_role" "role_SYSADMIN" {

Account role name: RB_APMEA_IT_BUSINESS_APPS_SYSADMIN, err: 002002 (42710):
SQL compilation error:
Object 'RB_APMEA_IT_BUSINESS_APPS_SYSADMIN' already exists.

Error: Failed to create account role

with module.workspace_RB_APMEA_IT_BUSINESS_APPS.snowflake_role.role_SECADMIN,
on modules/workspace/main.tf line 49, in resource "snowflake_role" "role_SECADMIN":
49: resource "snowflake_role" "role_SECADMIN" {

Account role name: RB_APMEA_IT_BUSINESS_APPS_SECADMIN, err: 002002 (42710):
SQL compilation error:
Object 'RB_APMEA_IT_BUSINESS_APPS_SECADMIN' already exists.

Error: error creating database RB_APMEA_IT_BUSINESS_APPS_DB: 002002 (42710): SQL compilation error:
Object 'RB_APMEA_IT_BUSINESS_APPS_DB' already exists.

with module.workspace_RB_APMEA_IT_BUSINESS_APPS.module.databases["DB01"].snowflake_database.database,
on modules/workspace/database/main.tf line 2, in resource "snowflake_database" "database":
2: resource "snowflake_database" "database" {

Error: 003001 (42501): SQL access control error:
Insufficient privileges to operate on account '[accountname]'

with module.workspace_RB_APMEA_IT_BUSINESS_APPS.module.warehouses["WH01"].snowflake_warehouse.warehouse,
on modules/workspace/warehouse/main.tf line 1, in resource "snowflake_warehouse" "warehouse":
1: resource "snowflake_warehouse" "warehouse" {

The error applying changes to the warehouse occured when adding resource_monitor = "null" to the configuration.

[sfc-gh-asawicki]

Thanks for the logs @AndreasHEbcont. You can reach out to your Snowflake account manager, share the complete logs with them, and ask them to pass them on to me internally in Snowflake.

[sfc-gh-jcieslak]

Hey @AndreasHEbcont
I tried to reproduce the issue with the configuration you provided, but I was unable to get the same results. I created a user with the privileges you mentioned and ran the configuration. After the first terraform apply I got the error:

│ Error: 003001 (42501): SQL access control error:
│ Insufficient privileges to operate on account 

and every subsequent terraform apply was outputting the same result. I'm not sure why or where the error with the state could happen, so I'm going to need some more help with reproducing the error. If that helps, the error on the warehouse is strictly connected to the privileges needed to create or operate on the resource monitor. As you can see in the ALTER WAREHOUSE documentation, next to the MODIFY privilege it says that to assign a resource monitor to a warehouse the ACCOUNTADMIN role has to be used for that operation (and I'm assuming the same goes for CREATE WAREHOUSE).

[AndreasHEbcont]

Hey @sfc-gh-jcieslak, thank you for looking into this Issue so fast! I will be in touch with my collegue engineers trying to supply you with the best possible way to replicate this behaviour. In the meanwhily please do not close the ticket.

Regards, Andreas

[sfc-gh-jcieslak]

Hey @AndreasHEbcont 👋
Did you have a chance to reproduce the described behaviour?

[AndreasHEbcont]

Hey, (un)fortunately I was not able to reproduce the behaviour with any further deployment. If I should ever encounter that behaviour again I will get in touch with our snowflake account manager.

Thank you for your support!

[sfc-gh-jcieslak]

Alright, I'm closing this one then. If you encounter any similar issues, please create another one and link this one for context. Thank You 👍 .