Can't create a file format TOK_CONSTANT_LIST does not exist

[kyle-hamlin]

Has anyone else got the had trouble creating a snowflake stage with this provider? I keep getting the following error:

Error: error creating stage TABLE_RAW: 002003 (02000): SQL compilation error:
File format 'TOK_CONSTANT_LIST' does not exist or not authorized.

I know for a fact that the file format does indeed exist because I was able to create the stage manually in a snowflake worksheet. The only caveat being that I had to provide the fully qualified file_format:

create or replace stage prod.public.table_raw
url = 's3://<bucket>/prod/'
file_format = "PROD.PUBLIC.RAW"
storage_integration = "RAW_DATA"

Here is my stage resource:

resource "snowflake_stage" "table_raw" {
  name                  = "TABLE_RAW"
  database            = "PROD"
  schema              = "PUBLIC"
  file_format         = "RAW"
  url                       = "s3://<bucket>/prod/"
  storage_integration = "RAW_DATA"
}

When I try adding {DB}.{SCHEMA}.{TABLE} to the file_format in terraform I get the following error:

Error: error creating stage TABLE_RAW: 001003 (42000): SQL compilation error:
syntax error line 1 at position 202 unexpected '.'.
syntax error line 1 at position 202 unexpected '.'.
syntax error line 1 at position 220 unexpected ')'.

[brewkode]

I see the same issue as well. The actual SQL that seems to be executed on Snowflake's side is to the effect
ALTER STAGE %v SET FILE_FORMAT = (%v). In comparison, when I run the following version of SQL directly on my Snowflake console it works.

Working version:
ALTER STAGE %v SET FILE_FORMAT = %v (Without braces around value)

[brewkode]

I went through the snowflake documentation one more time. Turns out, the value for file_format can be a list denoted within () as part of SQL. And, the actual value of FILE_FORMAT should be either of TYPE = <STR> OR FORMAT_NAME = <INTERNAL_FILE_FORMAT_NAME> and the INTERNAL_FILE_FORMAT_NAME is best referred using the fully qualified name.

In your case, you might have to change the stage resource to something like

resource "snowflake_stage" "table_raw" {
  name                  = "TABLE_RAW"
  database            = "PROD"
  schema              = "PUBLIC"
  file_format         = "TYPE = RAW"
  url                       = "s3://<bucket>/prod/"
  storage_integration = "RAW_DATA"
}

[kyle-hamlin]

@brewkode That is correct! The only thing I will mention is that RAW is a file_format so it would be:

resource "snowflake_stage" "table_raw" {
  name                  = "TABLE_RAW"
  database            = "PROD"
  schema              = "PUBLIC"
  file_format         = "FORMAT_NAME = RAW"
  url                       = "s3://<bucket>/prod/"
  storage_integration = "RAW_DATA"
}