terraform-provider-snowflake
terraform-provider-snowflake copied to clipboard
Snowflake-Labs
terraform materialized view ends in error (Root resource was present, but now absent)
Provider Version v0.25.33
Terraform Version 1.1.3
Describe the bug I am in no way an expert and this might be a total fail just on my side but when using the materialized views I'm having an error at the end of the execution of the terraform cli. Could this be a bug? And if now can someone point me to what may be the detail? It says: "module.snowflake_deploy.provider["registry.terraform.io/chanzuckerberg/snowflake"].datavault" produced an unexpected new value: Root resource was present, but now absent.
Expected behavior Materialized view is created without any error:
Code samples and commands
resource "snowflake_materialized_view" "meta_query_calendar" {
provider = snowflake.datavault # SYSADMIN
for_each = { for index, db in local.databases : index => db }
database = upper(var.SF_CLIENT_DATABASE)
schema = "META"
name = "CALENDAR"
warehouse = var.SF_WH_DPROC
comment = "View of a calendar to be used in PIT"
statement = <<-SQL
WITH DATES AS (SELECT DATEADD(DAY, SEQ4(), DATE('1850-01-01')) AS DATE FROM TABLE(GENERATOR(ROWCOUNT => 127835)))
SELECT DATE AS DATE
,DAY(DATE) AS DAY
,MONTH(DATE) AS MONTH
,YEAR(DATE) AS YEAR
,WEEK(DATE) AS WEEK
,YEAROFWEEK(DATE) AS WEEK_YEAR
,QUARTER(DATE) AS QUARTER
,MONTHNAME(DATE) || '-' || YEAR(DATE) AS MONTH_YEAR
,DATE_TRUNC('YEAR', DATE) - 1 AS PREVIOUS_YEAR_END
,DATE - 1 AS PREVIOUS_DAY
FROM DATES ORDER BY DATE
;
SQL
or_replace = false
is_secure = false
depends_on = [null_resource.datavault_init]
}
Additional context
This is the output I get from the terraform CLI
...
- Installing hashicorp/aws v3.71.0...
- Installed hashicorp/aws v3.71.0 (signed by HashiCorp)
- Installing chanzuckerberg/snowflake v0.25.33...
- Installed chanzuckerberg/snowflake v0.25.33 (self-signed, key ID 90FC1F93BCDEFDC1)
- Installing hashicorp/null v3.1.0...
- Installed hashicorp/null v3.1.0 (signed by HashiCorp)
- Installing hashicorp/random v3.1.0...
- Installed hashicorp/random v3.1.0 (signed by HashiCorp)
...
# module.snowflake_deploy.snowflake_materialized_view.meta_query_calendar["0"] will be created
+ resource "snowflake_materialized_view" "meta_query_calendar" {
+ comment = "View of a calendar to be used in PIT"
+ database = "INSIGHT_AWS_MASTER"
+ id = (known after apply)
+ is_secure = false
+ name = "CALENDAR"
+ or_replace = false
+ schema = "META"
+ statement = <<-EOT
WITH DATES AS (SELECT DATEADD(DAY, SEQ4(), DATE('1850-01-01')) AS DATE FROM TABLE(GENERATOR(ROWCOUNT => 127835)))
SELECT DATE AS DATE
,DAY(DATE) AS DAY
,MONTH(DATE) AS MONTH
,YEAR(DATE) AS YEAR
,WEEK(DATE) AS WEEK
,YEAROFWEEK(DATE) AS WEEK_YEAR
,QUARTER(DATE) AS QUARTER
,MONTHNAME(DATE) || '-' || YEAR(DATE) AS MONTH_YEAR
,DATE_TRUNC('YEAR', DATE) - 1 AS PREVIOUS_YEAR_END
,DATE - 1 AS PREVIOUS_DAY
FROM DATES ORDER BY DATE
;
EOT
+ warehouse = "VERIFICATION"
}
...
│ Error: Provider produced inconsistent result after apply
│
│ When applying changes to
│ module.snowflake_deploy.snowflake_materialized_view.meta_query_calendar["0"],
│ provider
│ "module.snowflake_deploy.provider[\"registry.terraform.io/chanzuckerberg/snowflake\"].datavault"
│ produced an unexpected new value: Root resource was present, but now
│ absent.
│
│ This is a bug in the provider, which should be reported in the provider's
│ own issue tracker.
Thanks for any feedback and best regards
I think this is a problem in the materialized view itself, I had it as a view and it was working fine and now I wanted to make it as a materialized view and it does not work. Anyway the output is not very clear and I would expect it to fail and not execute as if it had no errors.
We are facing the same issue with snowflake_materialized_view resources.
Would you run SQL to create the same materialized view? If it failed, you SQL is the issue?
Would you run SQL to create the same materialized view? If it failed, you SQL is the issue?
in my case yes the SQL was the issue, not sure about the others. Still my point is that I was expecting a different kind of error, specially when executing the resource, that message of error is a bit ambiguous. Anyway I have managed to create other materialized views without issues so for this case I would say the issue was for sure the SQL
Thanks, everyone. I can confirm that the SQL statement was the issue, in my case as well. But I would like to agree with the previous comment, that error is too ambiguous.
@cstmgl @ilyasemenov84 Would you suggest how we can improve error message if SQL is invalid?
Hi Again I have an error in this but this time there is nothing wrong in my query, I'm a bit lost. I get consistently this error:
18:14:38 2022-03-07T18:14:38.612+0100 [ERROR] vertex "module.snowflake_deploy.snowflake_materialized_view.meta_config_json[\"INSIGHT_AWS_DEMO_SG\"]" error: Provider produced inconsistent result after apply
18:14:40 [31m│[0m [0m[1m[31mError: [0m[0m[1mProvider produced inconsistent result after apply[0m
18:14:40 [31m│[0m [0m
18:14:40 [31m│[0m [0m[0mWhen applying changes to
18:14:40 [31m│[0m [0mmodule.snowflake_deploy.snowflake_materialized_view.meta_config_json["INSIGHT_AWS_DEMO"],
18:14:40 [31m│[0m [0mprovider
18:14:40 [31m│[0m [0m"module.snowflake_deploy.provider[\"registry.terraform.io/chanzuckerberg/snowflake\"].datavault_sysadmin"
18:14:40 [31m│[0m [0mproduced an unexpected new value: Root resource was present, but now
18:14:40 [31m│[0m [0mabsent.
18:14:40 [31m│[0m [0m
18:14:40 [31m│[0m [0mThis is a bug in the provider, which should be reported in the provider's
18:14:40 [31m│[0m [0mown issue tracker.
this is my resource:
resource "snowflake_external_table" "meta_config_xml" {
provider = snowflake.datavault_sysadmin
for_each = local.databases
database = each.value
schema = "META"
name = "CONFIG_XML"
auto_refresh = false
refresh_on_create = false
location = "@${each.value}.META.CONFIG_STAGE"
file_format = "TYPE = XML"
pattern = ".+.xml"
column {
name = "CONFIG_VALUE"
type = "VARIANT"
as = "VALUE"
}
column {
name = "CONFIG_FILENAME"
type = "VARCHAR"
as = "METADATA$FILENAME"
}
depends_on = [snowflake_stage.stage_config]
}
resource "snowflake_materialized_view" "meta_config_xml" {
provider = snowflake.datavault_sysadmin
for_each = local.databases
database = each.value
schema = "META"
name = "CONFIG_XML_V"
warehouse = var.SF_WH_INFRA
statement = <<-SQL
SELECT * FROM META.CONFIG_XML
SQL
or_replace = true
is_secure = false
depends_on = [snowflake_external_table.meta_config_xml]
}
I can go in snowflake and select from my external table without any problem so I really don't get it.
The part that gets me confused is that I really don't get the error.
I ran the information with debug and this is the only thing I found in the log that is a warning:
09:22:05 2022-03-08T09:22:05.018+0100 [WARN] Provider "registry.terraform.io/chanzuckerberg/snowflake" produced an invalid plan for module.snowflake_deploy.snowflake_external_table.meta_config_json["INSIGHT_AWS_DEMO"], but we are tolerating it because it is using the legacy plugin SDK.
Having a similar issue. I was able to create the resource manually, import it, and even destroy it using Terraform.
But even after importing, the state never really converged - my terraform plan shows + warehouse = "..." # forces replacement, and removing warehouse (correctly) raises an error that we miss an argument.
(with just importing the state)
❯ cat snowflake.tf
...
resource "snowflake_materialized_view" "matview" {
database = snowflake_schema.schema.database
schema = snowflake_schema.schema.name
warehouse = snowflake_warehouse.warehouse.name
name = "MATVIEW"
statement = ...
or_replace = true
}
❯ terraform plan
...
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement
Terraform will perform the following actions:
# snowflake_materialized_view.matview must be replaced
-/+ resource "snowflake_materialized_view" "matview" {
~ id = "DB|SCHEMA|MATVIEW" -> (known after apply)
name = "MATVIEW"
+ or_replace = true
+ warehouse = "warehouse" # forces replacement
# (4 unchanged attributes hidden)
}
(trying to match remote on snowflake.tf)
❯ cat snowflake.tf
...
resource "snowflake_materialized_view" "matview" {
database = snowflake_schema.schema.database
schema = snowflake_schema.schema.name
# warehouse = snowflake_warehouse.warehouse.name
name = "MATVIEW"
statement = ...
or_replace = true
}
❯ terraform plan
╷
│ Error: Missing required argument
│
│ on snowflake.tf line 54, in resource "snowflake_materialized_view" "matview":
│ 54: resource "snowflake_materialized_view" "matview" {
│
│ The argument "warehouse" is required, but no definition was found.
╵
Versions:
❯ terraform version
Terraform v1.0.0
on darwin_amd64
+ provider registry.terraform.io/confluentinc/confluent v1.0.0
+ provider registry.terraform.io/snowflake-labs/snowflake v0.37.1
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.
