File Formats requiring optional parameters

[TonyGaul]

Version: 0.25.11

I create a basic file_format as per: https://registry.terraform.io/providers/chanzuckerberg/snowflake/latest/docs/resources/file_format With just the mandatory values.

Here is my version: "PARQUET_RAW" = { name = "PARQUET_RAW" comment = "PARQUET file format" database = "RAW" schema = "SHARED" format_type = "PARQUET" }

resource "snowflake_file_format" "file_formats" { provider = snowflake.system_admin depends_on = [snowflake_database.databases, snowflake_schema.schemas] for_each = local.file_formats name = each.value.name comment = each.value.comment format_type = each.value.format_type database = each.value.database schema = each.value.schema }

Expected behavior

First run everything works as expected.

Second run, TF fails and wants me to provide optional params

snowflake_file_format.file_formats["PARQUET_RAW"] will be updated in-place ~ resource "snowflake_file_format" "file_formats" { - compression = "AUTO" -> null id = "RAW|SHARED|PARQUET_RAW" name = "PARQUET_RAW" # (22 unchanged attributes hidden) }

So... I add compression to fix this issue, but then I get a new issue where it wants the next optional param defined

How can I set a very basic set of file_formats with just the mandatory params?

Want to set this: locals { file_formats = { "AVRO_RAW" = { name = "AVRO_RAW" comment = "Avro file format" database = "RAW" schema = "SHARED" format_type = "AVRO" } "CSV_RAW" = { name = "CSV_RAW" comment = "CSV file format" database = "RAW" schema = "SHARED" format_type = "CSV" } "JSON_RAW" = { name = "JSON_RAW" comment = "JSON file format" database = "RAW" schema = "SHARED" format_type = "JSON" } "ORC_RAW" = { name = "ORC_RAW" comment = "ORC file format" database = "RAW" schema = "SHARED" format_type = "ORC" } "PARQUET_RAW" = { name = "PARQUET_RAW" comment = "PARQUET file format" database = "RAW" schema = "SHARED" format_type = "PARQUET" } "XML_RAW" = { name = "XML_RAW" comment = "XML file format" database = "RAW" schema = "SHARED" format_type = "XML" } } }

and run this: resource "snowflake_file_format" "file_formats" { provider = snowflake.system_admin for_each = local.file_formats name = each.value.name comment = each.value.comment format_type = each.value.format_type database = each.value.database schema = each.value.schema }

[jshields]

I am also getting changes to optional parameters I never set on a subsequent plan. I thought it was also worth noting that the values Terraform says the attributes are being changed from are not necessarily the real values in the Snowflake DB. For example, if I show my file_format in the Snowflake console, I see {"TYPE":"CSV","RECORD_DELIMITER":"\n",... in the format_options. But Terraform is saying that the record delimiter is currently set to an "end of transmission" character, not the newline character it is actually set to:

# snowflake_file_format.csv_custom will be updated in-place
  ~ resource "snowflake_file_format" "csv_custom" {
      - binary_format                  = "HEX" -> null
      - compression                    = "AUTO" -> null
      - date_format                    = "AUTO" -> null
      - encoding                       = "UTF8" -> null
      - escape                         = "NONE" -> null
      - escape_unenclosed_field        = "\\" -> null
      - field_delimiter                = "," -> null
        id                             = "EXAMPLE|EXAMPLE|CSV_CUSTOM"
        name                           = "CSV_CUSTOM"
      - record_delimiter               = <<-EOT

        EOT -> null
      - time_format                    = "AUTO" -> null
      - timestamp_format               = "AUTO" -> null
        # (23 unchanged attributes hidden)
    }

Even if I explicitly add the optional attributes with their correct values matching my file_format in Snowflake, I get Terraform detected the following changes made outside of Terraform since the last "terraform apply", so Terraform thinks there have been changes to the infrastructure but there have not. Noting that the record_delimiter is still incorrect (EOT) even though both the TF resource and Snowflake web console are showing the correct value ("\n").

Terraform detected the following changes made outside of Terraform since the last "terraform apply":

  # snowflake_file_format.csv_custom has been changed
  ~ resource "snowflake_file_format" "csv_custom" {
      + binary_format                  = "HEX"
      + compression                    = "AUTO"
      + date_format                    = "AUTO"
      + encoding                       = "UTF8"
      + escape                         = "NONE"
      + escape_unenclosed_field        = "\\"
      + field_delimiter                = ","
        id                             = "EXAMPLE|EXAMPLE|CSV_CUSTOM"
        name                           = "CSV_CUSTOM"
      + record_delimiter               = <<-EOT

        EOT
      + time_format                    = "AUTO"
      + timestamp_format               = "AUTO"
        # (23 unchanged attributes hidden)
    }

[wyattshapiro]

For now, I have created a workaround by telling Terraform not to track changes. Something like lifecycle { ignore_changes = [ binary_format, compression, date_format, encoding, escape_unenclosed_field, field_delimiter, record_delimiter, time_format, timestamp_format, skip_byte_order_mark, validate_utf8 ] }

[amit0904]

Any updates on this issue, we are also getting this error. Even for parameter VALIDATE_UTF8 which is obsolete now.

Error: error updating file format validate_utf8 on DB|SCHEMA|FILE_FORMAT: 001008 (22023): SQL compilation error: │ invalid value [false] for parameter 'VALIDATE_UTF8'

[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.