[Bug]: Snowpipe Not Being Recreated When Only Integration Changes

[Joe-Mooney]

Terraform CLI Version

1.8.2

Terraform Provider Version

0.87.2

Terraform Configuration

resource "snowflake_pipe" "main" {
  database          = var.target_database_name
  schema            = var.target_schema_name
  name              = "${var.table_name}_PIPE"
  integration       = var.integration_name
  error_integration = var.error_integration_name
  auto_ingest       = true

  copy_statement = <<-EOT
    COPY INTO ${var.target_database_name}.${var.target_schema_name}.${var.table_name} FROM (
      SELECT  ${join(",", [for i in range(1, var.num_columns + 1) : format("$%s", i)])},
      METADATA$START_SCAN_TIME,
      NULL,
      uuid_string(),
      METADATA$FILENAME,
      METADATA$FILE_LAST_MODIFIED
      FROM @${var.target_database_name}.${var.target_schema_name}.${var.target_stage_name}${var.path}
      )
    file_format = (
      FORMAT_NAME=${var.target_database_name}.${var.target_schema_name}.my_csv_format
      error_on_column_count_mismatch=false
      encoding=ISO88591
      )
    pattern='.*[.]csv'
    on_error = SKIP_FILE
  EOT
}

Category

category:resource

Object type(s)

resource:pipe

Expected Behavior

After having created a snowpipe with the above code, if only the integration value is changed (for example going from "TEST_INTEGRATION" to "TEST_INTEGRATION_V2"), the expectation is that the snowpipe will be updated to change the integration after an apply. At the very least, if this doesn't happen, there should be an error message saying the integration was not updated.

Actual Behavior

Upon running terraform plan, the following was returned:

Terraform will perform the following actions:

  # module.snowpipe_test.snowflake_pipe.main["TEST"] will be updated in-place
  ~ resource "snowflake_pipe" "main" {
        id                   = "TEST_DB|TEST_SCHEMA|TEST_PIPE"
      ~ integration          = "TEST_INTEGRATION" -> "TEST_INTEGRATION_V2"
        name                 = "TEST_PIPE"
        # (8 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Upon running terraform apply, the following was returned:

module.snowpipe_test.snowflake_pipe.main["TEST"]: Modifying... [id=TEST_DB|TEST_SCHEMA|TEST_PIPE]
module.snowpipe_test.snowflake_pipe.main["TEST"]: Modifications complete after 1s [id=TEST_DB|TEST_SCHEMA|TEST_PIPE]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

However, in Snowflake itself, the pipe's integration remained unchanged, staying as "TEST_INTEGRATION".

Steps to Reproduce

1. Copy the provided configuration into a module with the appropriate variables.
2. Set up a database, schema, and table in Snowflake, along with an error integration, stage, and csv file format.
3. Set up two integrations in Snowflake, the initial one to use and the one to switch to.
4. Set up the appropriate permissions to allow Terraform access to the resources in Snowflake.
5. Plan and apply in Terraform once with variables set to point to the resources you made in Snowflake in step 2 along with the integration set to the initial one to create the Snowpipe.
6. Once the Snowpipe is created, only change the integration variable to the second integration you made, and rerun the Terraform plan and apply.

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

I think this issue revolves around how the parameters are set in the snowflake_pipe resource. In the documentation for ALTER PIPE, it states that:

Currently, it is not possible to modify the following pipe properties using an ALTER PIPE statement:

- [COPY INTO <table>](https://docs.snowflake.com/en/sql-reference/sql/copy-into-table) statement
- AWS_SNS_TOPIC parameter
- INTEGRATION parameter

However, comparing the AWS_SNS_TOPIC and INTEGRATION definitions in the pkg/resources/pipe.go file, I noticed that only AWS_SNS_TOPIC has ForceNew=True:

"aws_sns_topic_arn": {
	Type:        schema.TypeString,
	Optional:    true,
	ForceNew:    true,
	Description: "Specifies the Amazon Resource Name (ARN) for the SNS topic for your S3 bucket.",
},
"integration": {
	Type:        schema.TypeString,
	Optional:    true,
	Description: "Specifies an integration for the pipe.",
},

I believe that integration also needs a ForceNew=True in it as well to force recreation of the pipe when the value of the integration parameters is changed. I am not sure though if this is accurate, or if this is the only change that would need to be made - it is just a theory I have from looking a few hours in the code base.

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @Joe-Mooney 👋
Thanks for reporting such a detailed issue!
I already see this is an issue with the fact that we currently do not check for changes in the integration field and that's why the changes are not detected. As we have plans to go over Snowflake GA resources in the provider (link to roadmap), this will be done as part of the Pipe refactoring (Pipes are planned for the next quarter). For now, try to utilize the built-in replace_triggered_by HCL meta argument and let me know if that works for you (link to the documentation). I'll add this issue to the list of issues that need to be resolved during refactoring pipes (link to the list).

[Joe-Mooney]

Thanks for the response! I'll keep an eye open for the pipe refactoring in the upcoming quarter, and in the meantime will manually set the replace_triggered_by parameter to track the integration. It would be useful in the meantime if the snowflake_pipe resource page on the Terraform website stated that Integrations aren't tracked for replacement currently so other developers can know this as well.

[sfc-gh-jcieslak]

Great, for now, I'll add a note at the top of the pipe docs.

[afeld]

Ha, I'm on @Joe-Mooney's team and just stumbled into this problem. In this case, the named value used to set the integration argument changed; I don't think there's a clean way to have replace_triggered_by pick that up. My workaround was to force re-creation of all the pipe resources.

[sfc-gh-jcieslak]

Hey @afeld
It's strange that it's not picking up. Anyway, it's great to hear you had a workaround you could use. It will still take some time until we get to the pipe resource to refactor it. If you or anyone from the team would like to contribute, you are more than welcome to do so. If you would like to, you can familiarize yourself with the contribution guidelines and we're always here to help and guide you: what needs to be done, how to do it, etc.