Dynamic tables are not parsing the query correctly and recreate resources on every apply

[dtiesling]

Terraform CLI and Provider Versions

terraform v1.3.9
snowflake-provider v0.82.0

Terraform Configuration

resource "snowflake_dynamic_table" "lease_signature" {
  database  = "DB"
  name      = "LEASE_SIGNATURE"
  schema    = "PUBLIC"
  warehouse = "COMPUTE_WH"
  target_lag {
    maximum_duration = "20 minutes"
  }
  query = "SELECT * FROM DB.PUBLIC.LEASE_SIGNATURE"
}

Expected Behavior

I expect the state to show the query as "SELECT * FROM ADDITIONAL_TERMS FROM DB.PUBLIC.FOO"

Actual Behavior

State says query = "_SIGNATURE\" lag = '20 minutes' refresh_mode = 'AUTO' initialize = 'ON_CREATE' warehouse = COMPUTE_WH AS SELECT * FROM DB.PUBLIC.LEASE_SIGNATURE"

Steps to Reproduce

1. terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

The select query in the example has been altered to not expose our code. If you cannot reproduce please reach out to me and I will help reproduce.

[dtiesling]

I tested terraform v1.6.6 as well. Same behavior.

[sfc-gh-asawicki]

Hey, @dtiesling. Thanks for reporting the issue.

I can't reproduce it. We even have a test verifying such a simple query:

terraform-provider-snowflake/pkg/resources/dynamic_table_acceptance_test.go

Line 21 in 05f96c6

func TestAcc_DynamicTable_basic(t *testing.T) {

Maybe to reproduce it, we should know the query you are using (because as you say, you altered it not to expose your code)? Is the provided config failing for you with such an altered query?

[dtiesling]

@sfc-gh-asawicki I emailed you with one of the the exact terraform resources that is getting this behavior.

[sfc-gh-asawicki]

@dtiesling, thanks; I will try it out and follow up here or through email.

[dtiesling]

@sfc-gh-asawicki were you able to reproduce my issue?

[sfc-gh-asawicki]

Hey @dtiesling. I did not manage to check it last week I have it planned for this one, sorry for the delay.

[sonya]

I have been looking into this issue (it's only a mild annoyance for us because the change doesn't actually end up getting applied).

This appears to affect dynamic tables whose names include the string AS (e.g. REASON). I believe the issue is that we're using a simple substring to detect where the query begins

terraform-provider-snowflake/pkg/resources/dynamic_table.go

Line 204 in a5ce699

query := strings.TrimSpace(text[strings.Index(text, "AS")+3:])

I was looking into a fix that involves creating a new method on ViewSelectStatementExtractor for dynamic tables. If you wait long enough I may eventually open a PR to try this out, but it's probably not a good idea to wait for me.

[dtiesling]

I'll test that theory today. Is the fix as simple as changing the trim to use " AS "?

[dtiesling]

@sfc-gh-asawicki @sonya confirmed I'm only seeing the issue on tables that contain "as". Unfortunately that is the majority of our tables.

[sonya]

@dtiesling a workaround for you might be to use an older version of the Snowflake provider. We are using 0.78.0 and while it does show this spurious diff, it doesn't actually get applied. That behavior was changed in #2287

[dtiesling]

@sonya thanks! If you change the select query does the older version correctly recreate the table?

[sonya]

Ah, that's an unfortunate bug with the old version. If we want to change the query we have to delete and recreate the table

[dtiesling]

@sfc-gh-asawicki I tossed a draft PR in with a proposed fix. I don't have the bandwidth atm to build/host the provider locally for testing or set up a test snowflake account to run the tests (even unit tests seem to fail without some real credentials?) though.

[sfc-gh-asawicki]

I will be looking into this issue later today. Thanks for analyzing it further, @sonya @dtiesling! This will be helpful.

[sfc-gh-asawicki]

@sonya @dtiesling I have merged the changes, please test it after we release the v0.85.0 version.

[sonya]

@sfc-gh-asawicki thanks for the update. Unfortunately the fix doesn't work with a lot of our queries because we make liberal use of CTEs. Those queries start with WITH instead of SELECT.

I think I will actually PR the change I proposed above, as I need to open a few PRs for other DT features anyway. I understand that you guys are in the middle of a big rewrite, so I'm hoping that I won't cause too much disruption with peripheral feature contributions.

[sfc-gh-asawicki]

@sonya, we are open to contributions. :)

So that you know, we are removing the old logic from the snowflake package. Additionally, during the discussions, we were really close to pulling the read for the query entirely and accepting the downsides of such a move (import not fully functional and external changes not recognized by the provider). We aim to simplify the logic and the usage of the provider (check that part in the explanation comment:

terraform-provider-snowflake/pkg/resources/dynamic_table.go

Line 264 in 119af5e

* We did not want to complicate the implementation too much by introducing parsers.

). I can't guarantee that we won't stick with the option to not read with V1 of the provider.

[sfc-gh-asawicki]

@sonya one more thought: are you starting the queries with something else than AS SELECT or AS WITH?

[sfc-gh-asawicki]

Closing the issue, there were fixes in 0.83.0 and 0.85.0 versions. Please create a new issue if the problem persists for the newest version of the provider.