fivetran_connector_schema_config: Disabling columns for a given table when using the schemas param fails, saying columns isnt supported

[tmccall8829]

Describe the bug Previously, we were defining fivetran connector schema configs using the schema arg. We created a number of amazon_selling_partner connectors that way, but the performance was very slow.

Recently, we made the change to use schemas for better performance (as recommended by you all). We were able to successfully apply changes to existing connectors and their schemas using this new config. However, creating new connectors fails, with the following error message:

│ Error: Unable to Create Connector Schema Resource.
│ 
│   with fivetran_connector_schema_config.amazon_selling_partner["<redacted>"],
│   on resources.tf line 109, in resource "fivetran_connector_schema_config" "amazon_selling_partner":
│  109: resource "fivetran_connector_schema_config" "amazon_selling_partner" {
│ 
│ Error while applying schema config patch. status code: 400; expected: 200;
│ code: InvalidInput; message: Table `orders` of schema
│ `<redacted>`
│ doesn't supports columns configuration

To Reproduce Here is a snippet of how we're defining our resources:

Connector

resource "fivetran_connector" "amazon_selling_partner" {
  for_each = var.amazon_fivetran_customers

  service         = "amazon_selling_partner"
  group_id        = var.fivetran_destination_group_id
  run_setup_tests = true

  destination_schema {
    name = "<redacted>"
  }

  config {
    region      = each.value.region
    merchant_id = each.key
  }
}

Connector schedule

resource "fivetran_connector_schedule" "amazon_selling_partner" {
  for_each     = var.amazon_fivetran_customers
  connector_id = fivetran_connector.amazon_selling_partner[each.key].id

  sync_frequency  = "1440"
  daily_sync_time = each.value.daily_sync_time

  paused            = false
  pause_after_trial = false

  schedule_type = "auto"
}

Connector schema config

resource "fivetran_connector_schema_config" "amazon_selling_partner" {
  for_each     = var.amazon_fivetran_customers
  connector_id = fivetran_connector.amazon_selling_partner[each.key].id

  schema_change_handling = "ALLOW_ALL"

  schemas = {
    "<redacted>" = {
      enabled = true
      tables = {
        "asin_inbound_guidance" = {
          enabled = false
        },
        <...other disabled tables...>
        "orders" = {
          enabled = true,
          columns = {
            "buyer_info_buyer_county" = {
              enabled = false
            },
            "buyer_info_buyer_email" = {
              enabled = false
            },
            "buyer_info_buyer_name" = {
              enabled = false
            },
            "buyer_info_buyer_purchase_order_number" = {
              enabled = false
            },
            "buyer_tax_info_buyer_business_address" = {
              enabled = false
            },
            "buyer_tax_info_buyer_legal_company_name" = {
              enabled = false
            },
            "buyer_tax_info_buyer_tax_office" = {
              enabled = false
            },
            "buyer_tax_info_buyer_tax_registration_id" = {
              enabled = false
            },
            "default_ship_from_location_name" = {
              enabled = false
            },
            "default_ship_from_location_phone" = {
              enabled = false
            },
            "shipping_address_name" = {
              enabled = false
            },
            "shipping_address_phone" = {
              enabled = false
            }
          }
        },
        ...
      }
    }
  }
}

Expected behavior Given that this definition matches what the current docs say to use, AFAICT, I'd expect the schema change to be applied properly/without issues.

Logs & Output Apply output is included above. Plan output:

...
+ resource "fivetran_connector_schema_config" "amazon_selling_partner" {
  + connector_id           = "<redacted>"
  + id                     = (known after apply)
  + schema_change_handling = "ALLOW_ALL"
  + schemas                = {
      + "<redacted>" = {
          + enabled = true
          + tables  = {
              + "asin_inbound_guidance" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "catalog" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "easyship_package" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "feed" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "financial_event_group" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "market_basket_analysis_report_daily" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "market_basket_analysis_report_monthly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "market_basket_analysis_report_quarterly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "orders" = {
                  + columns   = {
                      + "buyer_info_buyer_county" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_info_buyer_email" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_info_buyer_name" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_info_buyer_purchase_order_number" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_tax_info_buyer_business_address" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_tax_info_buyer_legal_company_name" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_tax_info_buyer_tax_office" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "buyer_tax_info_buyer_tax_registration_id" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "default_ship_from_location_name" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "default_ship_from_location_phone" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "shipping_address_name" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                      + "shipping_address_phone" = {
                          + enabled = false
                          + hashed  = (known after apply)
                        },
                    }
                  + enabled   = true
                  + sync_mode = (known after apply)
                },
              + "package_tracking_detail" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "preorder_info_result" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "prep_instruction" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "repeat_purchase_report_monthly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "repeat_purchase_report_quarterly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "sales_and_traffic_business_report_daily" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "sales_and_traffic_business_report_monthly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "search_terms_report_daily" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "search_terms_report_monthly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "search_terms_report_quarterly" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "service_job" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "sku_inbound_guidance" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "small_and_light_enrollment_status" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "subscribe_and_save_forecast_report" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "subscribe_and_save_performance_report" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
              + "transport_content" = {
                  + enabled   = false
                  + sync_mode = (known after apply)
                },
            }
        },
    }
  + validation_level       = "TABLES"
}
...

Plugin version: v1.2.0, but noticed on v1.1.26 as well.

Additional context I've also filed a support case with fivetran, as it seems like this could also be related to this connector type specifically. But it's just odd that we were able to retroactively apply the new schema config to old connectors, while it fails for new ones.

[beevital]

@tmccall8829 As you can see from the error message: | code: InvalidInput; message: Table orders of schema │ <redacted> │ doesn't supports columns configuration

But you're trying to setup columns under this table:

"orders" = {
                  + columns   = {
                      + "buyer_info_buyer_county" = {

In versions < 1.2.0 you receive the message from API side, in 1.2.0 we added client-side validation to terraform provider. Also - API didn't checked this as wel - we were accepting the request without error. Recently we fixed that.

The fact that you weren't facing this error below doesn't mean that you were able to configure columns. If you'll open Fivetran Dashboard in browser and open the schema configuration tab for the connector - you'll see that you're not able to configure or even expand table columns for this table.

Sorry for such experience, but now everything works as expected.

If you need to have an ability to configure columns for this table - probably you'll have to file a support ticket on support.fivetran.com about that. It's not a Fivetran Provider issue.

[tmccall8829]

Thanks for the reply @beevital, but, as you'll see below, I don't agree that this issue should be closed.

I chatted with our Fivetran support rep, and that gave us a little bit more clarity:

• As I mentioned in my initial issue report, we were able to apply these column filters on existing connectors of this same type, for this same table
  • The problem was creating a new connector with these column filters/this schema
  • So it's not that this table "doesn't support column config at all". It does, once you've satisfied one key criteria (below)
• Our Fivetran rep mentioned that this is because when you first create a connector, you cannot configure any table schema/column changes until you've synced some data first
• So when we're seeing this "Table orders of schema ... doesn't support columns configuration" error, that's not because the connector itself doesn't support these columns; it's because fivetran hasn't pulled the schema yet for this connector
• That's why, once the connector has been set up for a while and has synced some initial data, then we're able to retroactively apply the column filter successfully
• They also confirmed for me what you mentioned about the API change. I understand that that's why this workflow used to work, pre-API update, but I still think some improvements could be made to this provider to make the fivetran_connector_schema_config resource actually useful for new connectors

For example, just this morning, after the connector had been syncing all weekend, I successfully applied this configuration and restricted columns from this connector's schema.

So to me, that makes it seem like there should at least be some clearer error messaging around this. In the fivetran console/UI, for example, it shows this for newly created connectors:

My question is, why does the fivetran provider even support column schema configuration if you can't apply it to a connector without syncing some data first? That, to me, is quite a broken workflow, especially in the absence of a more meaningful error returned from the provider. It seems like it should at least be mirroring what the console says. If what our support rep is true (and it seems to be, based on what the fivetran console/UI is saying), then wouldn't this workflow be broken for any new connector created with a column-restriction schema config? It seems like this provider is attempting to provide functionality -- column schema restriction -- that the actual fivetran internals do not support.