How to manage the state of resources that Fivetran syncs to destination?

[CobySim01]

In our data pipeline, we have a complex Infrastructure as Code setup. Fivetran's ingestion into our BigQuery destination forms the first layer of this. Oftentimes, if there is no data for a particular table in the schema, Fivetran will not sync the table. This creates issues because all downstream infrastructure config is expecting this table to exist, but it does not exist. How can I extract, from Fivetran, the tables that are configured to exist (in the schema config) but do not exist because of a lack of data?

[SVijay]

Hey @CobySim01, what sources are you not seeing empty tables and columns for?

Fivetran can sync empty tables and columns for connectors such as Salesforce, Google Ads etc. This feature may not necessarily apply to every connector, so I can check with my product team once you are able to provide me with the list of sources you need this functionality for.

Additionally, you can also provide default values for tables which don't exist because of lack of data. Example for Terraform:

resource "google_bigquery_table" "my_table" {
# ... table definition ...

depends_on = [google_bigquery_dataset.my_dataset]

# Check if table exists using BigQuery Information Schema
lifecycle {
ignore_changes = google_bigquery_table.my_table.exists != true ? [
google_bigquery_table.my_table.view,
google_bigquery_table.my_table.schema,
] : []
}
}

output "table_data" {
value = var.use_existing_table ? google_bigquery_table.my_table.self_link : "default_data"
}

Thanks! @Toby-Metcalf 

[CobySim01]

Can you please explain this a bit further? I don't think `exists` is an attribute on the `google_bigquery_table` resource, so not sure how we can have `google_bigquery_table.my_table.exists`

[SVijay]

Hey @CobySim01 that was just a high-level example.

Could you please let me know the list of sources you need empty tables and columns for?

Thanks

[CobySim01]

Hi @SVijay,

Here is the list of connectors:

• TikTok Ads
• Facebook Ads
• Zendesk
• Fivetran Platform
• Bing Ads
• Jira
• GitHub

However, I'd like to note that we have are experiencing a similar issue with the dbt Quickstart models and we might add more connectors that are not on this list, so it would be useful to have a working Terraform solution too – to my knowledge, it is not possible to check if a resource exists within the resource itself (as in your example above).