Updating a DynamicTable defintion fails with SQL compilation error: unexpected <EOF>

[rmargarint-nydig]

Attempting to create dynamic tables through TF, however running into a show stopper bug.

The very first apply of the dynamic table succeeds without any problems.
But:

• Every subsquent apply that attempts to update the resource fails with

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 71 unexpected '<EOF>'.
│ 
│   with snowflake_dynamic_table.my_dyn_table,
│   on my_dynamic_tables.tf line 59, in resource "snowflake_dynamic_table" "my_dyn_table":
│   59: resource "snowflake_dynamic_table" "my_dyn_table" {

• Every tf apply thinks it detects changes for dynamic tables even if nothing changed. It seems to be because it thinks there were external changes to the query of the DT that added some spacing to the start of it, even if there were no changes at all (the exact same tf apply reran back to back)

Note: Objects have changed outside of Terraform

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

  # snowflake_dynamic_table.my_dyn_table has changed
  ~ resource "snowflake_dynamic_table" "my_dyn_table" {
        id                   = "MY_DB|PUBLIC|MY_DYN_TABLE"
        name                 = "my_dyn_table"
      ~ query                = <<-EOT
          -     ---
          + ---
                select * from (

[mgarrett2008]

hi @rmargarint-nydig , I experienced same error.

[mgarrett2008]

hello @rmargarint-nydig , did you find a work around for this? We are still experiencing the same issue.

[sonya]

It looks like the syntax error happens because the query that's executed is this:

ALTER DYNAMIC TABLE "<database>"."<schema>"."<table>" SET

(database, schema, and table are placeholders -- the important feature is that there is nothing after SET)

This appears to happen when Terraform detects that there are changes but there are actually no changes.

A workaround is to introduce an innocuous change that Terraform will actually apply. For example, if the table's target lag is an hour, Snowflake harmonizes this to 1 hour. If this is specified in Terraform as 60 minutes, the terraform provider detects this as a change, and the following statement will always be applied

ALTER DYNAMIC TABLE "<database>"."<schema>"."<table>" SET TARGET_LAG = '60 minutes'

Despite being an unnecessary alter statement, this works around the <EOF> error.

We actually discovered this because we tried to get rid of the spurious change and realized we were depending on a bug.

[sfc-gh-asawicki]

Hey @rmargarint-nydig. Thanks for creating the issue.

Yes, it looks like a bug in the dynamic table resource. It is relatively easy to fix. I will work on it next week so that it will hopefully be ready for next Wednesday's release.