[Bug]: External Table - Scan error on column index 13, name "region": converting NULL to string is unsupported

[wpl-sf-nbirch]

Terraform CLI Version

1.9.4

Terraform Provider Version

0.94.1

Terraform Configuration

resource "snowflake_external_table" "sync_data" {
  depends_on = [ snowflake_stage.sync_data ]

  database          = var.snowflake_database
  schema            = var.snowflake_schema
  name              = "SYNC_EXT_DATA"
  comment           = "Sync external table."
  auto_refresh      = true
  refresh_on_create = true
  aws_sns_topic     = "arn:aws:sns:<region>:<aws_account_id>:<sns_topic_name>"
  file_format       = local.csv_gz_file_format
  location          = "@${snowflake_stage.sync_stage.database}.${snowflake_stage.sync_stage.schema}.${snowflake_stage.sync_stage.name}"
  partition_by      = ["DATE_PART"]

  column {
    name = "KEY"
    type = "VARCHAR(16777216)"
    as   = "(value:c1::varchar)"
  }

  column {
    name = "NAME"
    type = "VARCHAR(16777216)"
    as   = "(value:c2::varchar)"
  }

  column {
    name = "SYNC_TIMESTAMP"
    type = "TIMESTAMP_NTZ(9)"
    as   = "(value:c3::timestamp_ntz)"
  }

  column {
    name = "FILENAME"
    type = "VARCHAR(16777216)"
    as   = "(metadata$filename)"
  }

  column {
    name = "FILE_ROW_NUMBER"
    type = "NUMBER(38,0)"
    as   = "(metadata$file_row_number)"
  }

  column {
    name = "FILE_LAST_MODIFIED"
    type = "TIMESTAMP_NTZ(9)"
    as   = "(metadata$file_last_modified)"
  }

  column {
    name = "DATE_PART"
    type = "DATE"
    as   = "(to_date(split_part(metadata$filename,'/', -2), 'YYYY-MM-DD'))"
  }
}

Category

category:resource

Object type(s)

resource:external_table

Expected Behavior

It should successfully create an external table using a stage and SNS resource reference.

Actual Behavior

The external table gets created successfully, however, the following error is returned to Terraform, so it does not get added to the state file.

Error: sql: Scan error on column index 13, name "region": converting NULL to string is unsupported

On a subsequent run on terraform, the following error returns showing the external table exists.

Error: 002002 (42710): SQL compilation error: Object 'STAGING.RAW.SYNC_EXT' already exists.

I believe there might be a mismatch in the column mapping for state when it runs a check of the external table post create.

SHOW EXTERNAL TABLES LIKE 'SYNC_EXT' IN SCHEMA STAGING.RAW;

'region' is the 14 columns (13th columns if starting from 0)

Steps to Reproduce

1. Create AWS resource and permissions (S3 bucket + SNS + IAM access)
2. Create a Terraform Snowflake stage and file format pointing to the S3 location
3. Create a Terraform Snowflake external table using the stage with refresh and an SNS subscription

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Seems similar to an existing error for internal stages: #129

Would you like to implement a fix?

• Yeah, I'll take it 😎

[wpl-sf-nbirch]

Initial investigations for SHOW EXTERNAL TABLES IN SCHEMA STAGING.RAW;

Does return some null values for the region column in question.

[wpl-sf-nbirch]

So it appears this is due to a timing thing, if an S3 bucket doesn't exist when a stage + file_format + external table get created, it will store a null region.

When terraform removes the external table, it might concurrently drop the file format first, which means the external table is not dropped, however, it shows as successfully dropped in the GUI. But the external table still exists with an error saying 'Missing file format'.

Fix was to manually drop the external table, and redeploy everything post the S3 bucket bring created.