[Bug]: Issue creating snowflake alert

[NaveenAutomate]

Terraform CLI Version

1.9.5

Terraform Provider Version

0.92.0

Terraform Configuration

I am trying to create snowflake_alert, the alert is created in the snowflake but the terraform apply failed with error 

Error: Provider produced inconsistent result after apply
│
│ When applying changes to module.snowflake_alerts.snowflake_alert.slack_alert, provider "provider[\"registry.terraform.io/snowflake-labs/snowflake\"]" produced an unexpected new value:
│ Root object was present, but now absent.
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker. 


//code
resource "snowflake_alert" "slack_alert" {
  database  = var.snowflake_database
  name      = upper("${var.db_site}SlackEmailNotificationAlert")
  schema    = var.snowflake_schema
  warehouse = var.warehouse

  alert_schedule {
    interval = 1 #check every minute for new alerts
  }

  action    = "CALL SYSTEM$SEND_EMAIL(integration_name => 'EMAIL_INTEGRATION', recipients => ARRAY_CONSTRUCT(${join(",", formatlist("'%s'", var.email_recipients))}), subject => 'Snowflake Alert Notification', content => 'New alerts have been logged in ${var.alert_table_name} table.');"

  condition  = "SELECT * FROM ${var.snowflake_database}.${var.snowflake_schema}.${snowflake_table.alert_table.name} WHERE alert_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME()"

  enabled   = true
  comment   = "Slack Notification alert for new entries in the alert table"
}


I have observed one thing in snowflake for alert, when I try to describe the alert with the same user who created the alert,

DESC ALERT DBNAME.SCHEMA.ALERTNAME;-- throws an error not exists or unauthorized
DESC ALERT DBNAME.SCHEMA."ALERTNAME";-- when wrapped the alert name in quotes it works

Category

category:resource

Object type(s)

resource:alert

Expected Behavior

Alert should be created successfully and apply should not fail

Actual Behavior

Alert is created but terraform failed with

Error: Provider produced inconsistent result after apply
│
│ When applying changes to module.snowflake_alerts.snowflake_alert.slack_alert, provider "provider["registry.terraform.io/snowflake-labs/snowflake"]" produced an unexpected new value:
│ Root object was present, but now absent.
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.

Steps to Reproduce

1. use the following resource
   `resource "snowflake_alert" "slack_alert" {
   database = var.snowflake_database
   name = upper("${var.db_site}SlackEmailNotificationAlert")
   schema = var.snowflake_schema
   warehouse = var.warehouse

alert_schedule {
interval = 1 #check every minute for new alerts
}

action = "CALL SYSTEM$SEND_EMAIL(integration_name => 'EMAIL_INTEGRATION', recipients => ARRAY_CONSTRUCT(${join(",", formatlist("'%s'", var.email_recipients))}), subject => 'Snowflake Alert Notification', content => 'New alerts have been logged in ${var.alert_table_name} table.');"

condition = "SELECT * FROM ${var.snowflake_database}.${var.snowflake_schema}.${snowflake_table.alert_table.name} WHERE alert_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME()"

enabled = true
comment = "Slack Notification alert for new entries in the alert table"
}`
2. terraform apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-asawicki]

Hey @NaveenAutomate. Thanks for reaching out to us.

snowflake_alert is a resource that has not yet been reworked. However, some changes occurred between v0.92 and the current (v0.96) version.

1. Please check if the problem is reproducible in v0.96.
2. It seems that there is a problem with the identifier. What value does ${var.db_site} have? I am interested here in special characters, casing, etc.

[NaveenAutomate]

@sfc-gh-asawicki
I cannot really upgrade to 0.96, looks like all the grant resources are removed from this version, we have too many of them and takes some time to use new resrouces

Regarding the ${var.db_site}, its just chars and upper case

[sfc-gh-asawicki]

I will try to reproduce this behavior this week. Just keep in mind, that in case of discovering a problem in the provider logic, the fix will come in the next minor version. We are not providing backward bug fixes for old minor versions because of the experimental state of the project.

[sfc-gh-asawicki]

Hey @NaveenAutomate. I tried to reproduce this error multiple ways and I haven't reached such an error.

Could you please provide a full minimal working sample with all the attributes filled out (i.e. without vars)?

[NaveenAutomate]

This is the plan, just to give an idea on values

# module.snowflake_alerts.snowflake_alert.slack_alert will be created resource "snowflake_alert" "slack_alert" { action = "CALL SYSTEM$SEND_EMAIL(integration_name => 'EMAIL_INTEGRATION', recipients => ARRAY_CONSTRUCT('data-delivery-qa-aler-aaaaibwsrvrsdaz2o2ijzxfpbe@tenable.org.slack.com'), subject => 'Snowflake Alert Notification', content => 'New alerts have been logged in ALERTS_LOG table.');" comment = "Slack Notification alert for new entries in the alert table" condition = "SELECT * FROM TOOLS.STATS.ALERTS_LOG WHERE alert_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME()" database = "TOOLS" enabled = true id = (known after apply) name = "all_slackEmailNotificationAlert" schema = "STATS" alert_schedule { interval = 1 } }

[sfc-gh-asawicki]

I still can't reproduce this with 0.96 or 0.92 versions. Please:

1. Double-check if you are really using the 0.92 version of the provider.
2. Run the plan+apply with TF_LOG=DEBUG environment variables and check the SQL statements that are run against Snowflake (I am interested in CREATE ALERT ... and SHOW ALERTS LIKE ...). Do not modify the name, schema, and database when pasting the output.
3. I see that you are using underscore (the pasted plan) and not only chars. Underscore is a special character in SHOW (it's a wildcard). We had such problems in much earlier versions of the provider (e.g., two objects differed slightly, for example, prefix1 versus prefix_and SHOW output was not filtered on the provider side correctly, so the object found was a different object than anticipated, which resulted in similar errors). However, the alert resource is already immune to this problem (and I made sure by checking such a situation in v0.92). Nevertheless, you can try verifying what other alerts you have in that schema.
4. Check if you have QUOTED_IDENTIFIERS_IGNORE_CASE set on ACCOUNT, DATABASE, or SCHEMA level. Having it set to true may cause the provider to misbehave if not used correctly (i.e. all identifiers in the configs have to be capitalized).
5. Try reproducing the same error on your side with simplified config for action and condition (e.g. set the both to "select 0 as c"). Let's exclude the possibility of the problem lying there.

One more note: the initial config you pasted have name = upper("${var.db_site}SlackEmailNotificationAlert") and the plan output has name = "all_slackEmailNotificationAlert" (not uppercased), so it seems that these two are not parts of the same execution.

[NaveenAutomate]

Hi @sfc-gh-asawicki ,

Thanks for the reply!

1. I have validated we are using 0.92 version
2. Partial Debug output for the commands

00 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:18 [DEBUG] sql-conn-query: [query SELECT CURRENT_ACCOUNT() as CURRENT_ACCOUNT err duration 140.165583ms args {}] ()
2024-10-13T22:52:18.538+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:18 [DEBUG] sql-conn-query: [query SELECT CURRENT_SESSION() as CURRENT_SESSION err duration 384.819334ms args {}] ()
2024-10-13T22:52:18.538+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:18 [DEBUG] connection success! Account: , Session identifier: 531095852186894
module.snowflake_alerts.snowflake_alert.slack_alert: Creating...
2024-10-13T22:52:18.546+0100 [INFO] Starting apply for module.snowflake_alerts.snowflake_alert.slack_alert
2024-10-13T22:52:18.547+0100 [DEBUG] module.snowflake_alerts.snowflake_alert.slack_alert: applying the planned Create change
2024-10-13T22:52:18.755+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:18 [DEBUG] sql-conn-exec: [query CREATE ALERT "TOOLS"."STATS"."all_slackEmailNotificationAlert" SCHEDULE = '1 MINUTE' COMMENT = 'Slack Notification alert for new entries in the alert table' IF (EXISTS (SELECT * FROM TOOLS.STATS.ALERTS_LOG WHERE alert_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME())) THEN CALL SYSTEM$SEND_EMAIL(integration_name => 'EMAIL_INTEGRATION', recipients => ARRAY_CONSTRUCT(''), subject => 'Snowflake Alert Notification', content => 'New alerts have been logged in ALERTS_LOG table.'); err duration 206.803292ms args {}] ()
2024-10-13T22:52:18.988+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:18 [DEBUG] sql-conn-exec: [query ALTER ALERT "TOOLS"."STATS"."all_slackEmailNotificationAlert" RESUME err duration 232.921417ms args {}] ()
2024-10-13T22:52:19.164+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:19 [DEBUG] sql-conn-query: [query SHOW ALERTS LIKE 'all_slackEmailNotificationAlert' IN SCHEMA "TOOLS"."STATS" err duration 175.638709ms args {}] ()
2024-10-13T22:52:19.164+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:19 [DEBUG] err: sql: Scan error on column index 6, name "warehouse": converting NULL to string is unsupported
2024-10-13T22:52:19.164+0100 [DEBUG] provider.terraform-provider-snowflake_v0.92.0: 2024/10/13 22:52:19 [DEBUG] alert (TOOLS|STATS|all_slackEmailNotificationAlert) not found

3. The config and actual plan are different as the alert was created before, I have updated the name to try create again!
4. The parameter QUOTED_IDENTIFIERS_IGNORE_CASE is set to false for account, database and schema
5. even with this its the same issue
   While writing this I realized the error from the debug output, and warehouse value is null which is causing this issue!

After adding warehouse parameter it works fine now!

[sfc-gh-asawicki]

Great to hear! So we can close the issue?

[NaveenAutomate]

not sure if the null needs to be handled, but yea this can be closed!
Thanks for the support @sfc-gh-asawicki!