Terraform fetches the wrong Dynamic Table definition when Dynamic Tables with the same name exist in multiple databases or schemas

[sonya]

Provider Version

0.75.0

Terraform Version

v1.4.4

Describe the bug

Steps to reproduce:

1. In the same database, create two schemas: A_TEST and B_TEST.
2. In A_TEST, manually create a dynamic table called TEST_DT
3. Using Terraform, create a completely different dynamic table in the B_TEST schema also called TEST_DT.
4. Run terraform apply. This should create a dynamic table in B_TEST called TEST_DT.
5. Run terraform plan. Observe that both the schema and query text have changes.

Expected behavior

In step 5 above, there should be no change.

Code samples and commands

Anonymized example of the resource definition:

resource "snowflake_dynamic_table" "test_dt" {
  database = var.snowflake_database
  schema   = "B_TEST"
  target_lag {
    maximum_duration = "1 hour"
  }
  warehouse = "COMPUTE_WH"
  name      = "TEST_DT"

  query = <<-EOT
    SELECT
        ID
    FROM
        B_TEST.BAR
  EOT

  or_replace = true
}

Anonymized example of the Terraform plan:

~ resource "snowflake_dynamic_table" "test_dt" {
        id                   = "TEST_DB|B_TEST|TEST_DT"
        name                 = "TEST_DT"
      ~ query                = <<-EOT
          - SELECT DISTINCT r.*
          + SELECT
          +     ID
            FROM
          -     A_TEST.FOO
          +     B_TEST.BAR
        EOT
      ~ schema               = "A_TEST" -> "B_TEST"
        # (12 unchanged attributes hidden)
    }

In the query logs, we can see that Terraform is running the following query during the plan:

SHOW DYNAMIC TABLES LIKE 'TEST_DT';

This suggests that when there are multiple dynamic tables with the same name in the same account, Terraform will match the first one that shows up, instead of the one in the targeted database/schema.

Additional context

It turns out running terraform apply despite the changes is safe, because the update is applied to the correct database and schema. However, there are multiple issues with the incorrect targeting during terraform plan:

• The terraform plan output is misleading and can cause developers to make bad decisions
• SHOW DYNAMIC TABLES LIKE '<name>' has poor performance when searching across a large number of databases and schemas. (We are attempting to manage dozens of dynamic tables and each SHOW command takes 3-4 minutes (and these queries appear to not use the cache). In contrast, SHOW DYNAMIC TABLES LIKE '<name>' IN <database>.<schema> takes a few seconds.)

[sfc-gh-asawicki]

Hey @sonya. Thanks for creating the issue.

You are correct that the current implementation of ShowByID could leverage the IN SCHEMA syntax. This is a straightforward fix; I will work on it next week.