terraform-provider-snowflake: snowflake_file_format update-in-place doesn't apply
Terraform CLI and Provider Versions
Terraform v1.6.5
on linux_amd64
+ provider registry.terraform.io/snowflake-labs/snowflake v0.82.0
Terraform Configuration
resource "snowflake_file_format" "csv" {
binary_format = "HEX"
compression = "AUTO"
database = "RAW"
date_format = "AUTO"
empty_field_as_null = true
encoding = "UTF8"
error_on_column_count_mismatch = true
escape = "NONE"
escape_unenclosed_field = "NONE"
field_delimiter = ","
field_optionally_enclosed_by = "\""
format_type = "CSV"
id = "RAW|EXAMPLE_SCHEMA|PROCESS_CSV"
name = "PROCESS_CSV"
null_if = [
"NULL",
]
parse_header = false
record_delimiter = <<-EOT
EOT
replace_invalid_characters = false
schema = "EXAMPLE_SCHEMA"
skip_blank_lines = false
skip_byte_order_mark = true
skip_header = 1
time_format = "AUTO"
timestamp_format = "AUTO"
trim_space = false
}
Expected Behavior
Updating a field in the file_format resource and re-applying updates the file format in snowflake
Actual Behavior
The update does not change anything in snowflake, and terraform picks up the change as needing to be applied every time
Steps to Reproduce
- Create a
snowflake_file_formatresource terraform apply- Update the resource, change the
field_optionally_enclosed_byfield for instance (not specific to this field) toNONE terraform apply
How much impact is this issue causing?
Medium
Logs
No response
Additional Information
Workaround at the moment is to taint the resource in terraform and allow it to be destroyed and recreated.
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 27 (8 by maintainers)
Commits related to this issue
- fix: FileFormat not detecting changes correctly (#2436) ref https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/2385 I detected 3 bugs: 1. `d.Get("format_type")` has to be ca... — committed to snowflakedb/terraform-provider-snowflake by raulbonet 3 years ago
- fix: file_format validate() for field_optionally_enclosed_by (#2575) ref #2385 As pointed out by https://github.com/alexmaras , the `validate()` function does not properly validate the use-case ... — committed to snowflakedb/terraform-provider-snowflake by raulbonet 3 years ago
Thanks @raulbonet for helping!
0.87.1has solved the issue, and I can apply file format updates successfully, including"NONE"!Resolved by https://github.com/Snowflake-Labs/terraform-provider-snowflake/pull/2575 and released in
0.87.1Thanks @alexmaras , I will have a look tomorrow (EMEA timezone)
thank you @raulbonet ;
I thought I had to escape the backslashes.
This is working like a charm since I configured the field to
\\; the change is correctly propagated to the FILE_FORMAT.@sfc-gh-asawicki version 0.87.1 fixed the issue we were having
Hey @raulbonet. My answers below:
ad 1 I will note it down, we should run it with
NONE, not'NONE'.ad 2 This is one of the topics we are aware of and that we will address during resources redesign (https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#our-roadmap). One of the PRs that shows how we can handle this is #2502. We are of course limited only to objects where we can easily access the default snowflake value. For the cases where this is not possible, we will just encourage the user to fill in the value (or even force the user to do it by requiring it).
@sfc-gh-asawicki , I notice a couple of issues here, just bringing it to your attention:
NONEis a Snowflake reserved keyword that afaik, currently the SQLBuilder does not support. So, the statement executed by Snowflake is:ALTER ...... = 'NONE', when it should beALTER ...... = NONEIt seems that both seem to work in this case, but not sure if this is the case for absolutely all resources in Snowflake.
I guess we do not want to address this in this PR, but this probably affects all resources. I wonder if we want to account for this use-case or at least track it in the backlog.
Hello @benriou I had a look. I think that the problem is just that
\\\\is not a valid value for this field, but Snowflake instead of raising an exception, does nothing. If you read the documentation here , section ESCAPE_UNENCLOSED_FIELD:A singlebyte character string used as the escape character for unenclosed field values only. An escape character invokes an alternative interpretation on subsequent characters in a character sequence. You can use the ESCAPE character to interpret instances of the FIELD_DELIMITER or RECORD_DELIMITER characters in the data as literals. The escape character can also be used to escape instances of itself in the data.
Accepts common escape sequences, octal values, or hex values.
But
\\\\cannot be expressed as a single byte sequence, nor is a “common escape sequence”.If you manually try to execute the statement in Snowflake GUI:
ALTER FILE FORMAT "DEV"."PUBLIC"."LOADBALANCER_LOGS" SET ESCAPE_UNENCLOSED_FIELD = '\\\\'And then query the file format:
SHOW FILE FORMATS LIKE 'LOADBALANCER_LOGS' IN SCHEMA "DEV"."PUBLIC";You will see that, indeed, the character has not changed.
I tried with other characters, like “A” or “a” and in this case, it gets replaced by
\n, and still not raising an exception.Hello @benriou Can you please post the terraform configuration and the plan so I can debug?
We are having a similar issue: Terraform v1.7.0 on darwin_arm64
These are included on the plan every time:
However the state does not show any change after this plan is applied:

To add to the complexity of this issue, this problem only persists in our staging and testing workspaces but not in Production. We speculated that this was because we imported these in production and the values were already set to true.