unquoted binary_format in SQL for snowflake_file_format

[ivica-k]

Provider Version

0.68.2

Terraform Version

1.4.2

Describe the bug

The SQL query produced by snowflake_file_format resource does not quote the binary_format parameter. The resulting query looks like (shortened for brevity):

... TIMESTAMP_FORMAT = 'AUTO' BINARY_FORMAT = UTF-8 ESCAPE = 'NONE' ...

This produces an error

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 284 unexpected '-'.
│ syntax error line 1 at position 284 unexpected '-'.

Expected behavior

The SQL query that is produced should put quotes around UTF-8, like so:

... TIMESTAMP_FORMAT = 'AUTO' BINARY_FORMAT = 'UTF-8' ESCAPE = 'NONE' ...

Code samples and commands

resource "snowflake_file_format" "csv_crlf_semicolon" {
  database                     = var.database
  schema                       = snowflake_schema.schema.name
  name                         = "CSV_CRLF_SEMICOLON_FF"
  format_type                  = "CSV"
  binary_format                = "UTF-8"
  compression                  = "AUTO"
  date_format                  = "AUTO"
  encoding                     = "UTF8"
  escape                       = "NONE"
  escape_unenclosed_field      = "NONE"
  field_delimiter              = ";"
  field_optionally_enclosed_by = "\""
  record_delimiter             = "\r\n"
  time_format                  = "AUTO"
  timestamp_format             = "AUTO"
  empty_field_as_null          = true
  null_if                      = ["", "NA", "NULL"]
  provider                     = snowflake.db_write
  skip_header                  = 1
}

Simple terraform apply will fail.

Additional context

Importing the file format and then applying to reconcile changes produces another error, as shown in #1947

[ivica-k]

A workaround:

Run terraform apply with TF_LOG=DEBUG terraform apply -target=path.to.file_format and grab the SQL it produces. Fix the SQL and run it manually through a worksheet. Then import the resource using the instructions from here.

The subsequent terraform apply shows no changes.

[sfc-gh-jcieslak]

Hey @ivica-k
I'm sorry we're responding so late on this one, but I'm pretty certain the issue was that the binary_format = "UTF-8" is an invalid value in Snowflake as you can see here. The correct value would be binary_format = "UTF8". Please confirm and close the ticket if that was the case.

[sfc-gh-jcieslak]

Hey @ivica-k
Did you have a chance to see and confirm if the binary_format fix resolves your issue?

[sfc-gh-jcieslak]

Closing due to long inactivity. As mentioned, the issue lays in the incorrect binary format name.