SyncSpur | Managing Fivetran as Code with the Terraform Provider

Most Fivetran accounts grow the same way. Someone connects Salesforce in the UI to prove a point, someone else adds Postgres, a third person copies the setup into a sandbox with slightly different settings, and eighteen months later nobody can say why the production HubSpot connection syncs every six hours while staging syncs every fifteen minutes. Nothing is documented because nothing was ever written down — it was clicked.

The fix is the same one that fixed this problem for cloud infrastructure: put the configuration in a repository. Fivetran publishes an official Terraform provider that covers destinations, connections, groups, users, teams, transformation projects, webhooks and more. This tutorial walks through adopting it on an account that already exists, which is the situation almost everyone is actually in.

What you need

Terraform 1.5 or newer (or OpenTofu).
A Fivetran account-level API key and secret, created under Account Settings → API Config. Connection-scoped keys are not enough; the provider manages account objects.
A remote state backend. Fivetran state contains connection IDs and configuration; treat it like any other sensitive state file.

1. Provider setup

terraform {
  required_version = ">= 1.5"
  required_providers {
    fivetran = {
      source  = "fivetran/fivetran"
      version = "~> 1.6"
    }
  }
}

provider "fivetran" {
  api_key    = var.fivetran_api_key
  api_secret = var.fivetran_api_secret
}

Pin the major version. The provider tracks the Fivetran REST API closely and resource schemas do change between majors — notably the rename of fivetran_connector to fivetran_connection, which is worth checking against whichever version you pin.

Pass credentials with the FIVETRAN_API_KEY and FIVETRAN_API_SECRET environment variables rather than a .tfvars file, so they never sit on disk.

2. Import what already exists — do not recreate it

This is the step people get wrong, and it is expensive when they do. If you write a fivetran_connection resource for a connector that already exists and apply it, Terraform creates a second connection. A second connection means a second initial sync, which means every row in that source counts as a monthly active row again. On a large source that is a real invoice.

So import first. List what you have:

curl -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  https://api.fivetran.com/v1/groups

curl -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  https://api.fivetran.com/v1/groups/<group_id>/connections

Then use import blocks so the plan is reviewable rather than a pile of shell commands:

import {
  to = fivetran_destination.warehouse
  id = "decent_dropsy"
}

import {
  to = fivetran_connection.salesforce_prod
  id = "speak_liquid"
}

Run terraform plan -generate-config-out=generated.tf. Terraform writes HCL matching the live objects. Read it, move the parts you want into your real files, delete the noise, and re-plan until the plan is empty. An empty plan is the goal. Do not apply until you get one.

3. A project layout that survives contact with a team

fivetran/
  main.tf              # provider, backend
  destinations.tf      # one or two, rarely change
  connections/
    salesforce.tf
    postgres_app.tf
    hubspot.tf
  schemas.tf           # schema/table selection
  users.tf             # users, teams, roles
  environments/
    prod.tfvars
    staging.tfvars

One file per source beats one giant connections.tf. Fivetran connection blocks are wide — a Postgres connection carries thirty-odd attributes — and per-source files keep pull request diffs legible and reviewable by whoever owns that source system.

4. Destination and connection

resource "fivetran_destination" "warehouse" {
  group_id           = fivetran_group.analytics.id
  service            = "snowflake"
  region             = "GCP_US_EAST4"
  time_zone_offset   = "-5"
  run_setup_tests    = true
  daylight_saving_time_enabled = true

  config {
    host     = var.snowflake_host
    database = "ANALYTICS"
    user     = "FIVETRAN_USER"
    password = var.snowflake_password
    role     = "FIVETRAN_ROLE"
    auth     = "PASSWORD"
  }
}

resource "fivetran_connection" "postgres_app" {
  group_id = fivetran_group.analytics.id
  service  = "postgres"

  destination_schema {
    prefix = "app_prod"
  }

  config {
    host              = var.pg_host
    port              = 5432
    database          = "app"
    user              = "fivetran"
    password          = var.pg_password
    update_method     = "WAL_PGOUTPUT"
    replication_slot  = "fivetran_slot"
    publication_name  = "fivetran_pub"
  }

  run_setup_tests = true
  trust_certificates = false
}

resource "fivetran_connection_schedule" "postgres_app" {
  connection_id = fivetran_connection.postgres_app.id
  sync_frequency = "60"
  paused         = false
  schedule_type  = "auto"
}

Two details that trip people up:

Schedule is a separate resource. Sync frequency, pause state and schedule type live in fivetran_connection_schedule, not in the connection. Splitting them lets you pause a whole environment without touching connection config — handy for staging.
destination_schema is immutable. Changing the prefix or schema name forces replacement, and replacement means a fresh initial sync and a fresh pile of MAR. Get naming right on day one. If you must rename, plan it as a migration, not as an edit.

5. Schema and table selection

Selecting tables in code is where Terraform earns its place, because table selection is the single biggest lever on both cost and warehouse clutter.

resource "fivetran_connection_schema_config" "postgres_app" {
  connection_id        = fivetran_connection.postgres_app.id
  schema_change_handling = "BLOCK_ALL"

  schemas = {
    "public" = {
      enabled = true
      tables = {
        "orders"      = { enabled = true }
        "order_items" = { enabled = true }
        "customers" = {
          enabled = true
          columns = {
            "ssn"            = { enabled = false }
            "internal_notes" = { enabled = false }
          }
        }
        "audit_log" = { enabled = false }
      }
    }
  }
}

BLOCK_ALL means a new table appearing in the source does not silently start syncing. Combined with code review, that turns "why did our MAR jump 40% last month?" into a pull request someone had to approve. It is the cheapest cost control in the product. If a team genuinely wants new tables picked up automatically, use ALLOW_ALL on that one connection and be explicit about it.

Column-level exclusion is also how you keep regulated fields out of the warehouse entirely, in a form an auditor can read.

6. Secrets

Never put a source password in HCL. Pull from your existing secret manager:

data "aws_secretsmanager_secret_version" "pg" {
  secret_id = "prod/fivetran/postgres"
}

locals {
  pg = jsondecode(data.aws_secretsmanager_secret_version.pg.secret_string)
}

Then reference local.pg.password. Note that the Fivetran API does not return secrets on read, so the provider cannot detect drift on them — if someone rotates a password in the UI, Terraform will not notice and will not fight you. That is convenient, and it is also a gap: rotate secrets through the same pipeline that manages everything else, or you will eventually have a connection whose real password exists nowhere in your repository.

7. Transformations and alerts as code

If you run dbt inside Fivetran, the project and its schedules are manageable too:

resource "fivetran_transformation_project" "analytics" {
  group_id = fivetran_group.analytics.id
  type     = "DBT_GIT"

  project_config {
    git_remote_url = "git@github.com:acme/analytics.git"
    git_branch     = "main"
    folder_path    = "transformations"
    dbt_version    = "1.8.0"
    default_schema = "analytics"
    target_name    = "prod"
    threads        = 8
  }
}

resource "fivetran_webhook" "alerts" {
  type   = "account"
  url    = var.alert_webhook_url
  secret = var.webhook_secret
  active = true
  events = ["sync_end", "connection_failure"]
}

Once failure webhooks are in code, every new connection inherits alerting automatically instead of depending on someone remembering. That single habit removes most "the dashboard was stale for three days and nobody knew" incidents. Our post on the Fivetran and dbt merger covers what is shifting on the transformation side.

8. Modules for repeated shapes

When you have twenty connections that differ only in credentials and schema prefix, wrap them:

module "salesforce" {
  source = "./modules/saas_connection"

  for_each = var.salesforce_orgs

  group_id       = fivetran_group.analytics.id
  service        = "salesforce"
  schema_prefix  = each.value.prefix
  sync_frequency = each.value.frequency
  secret_id      = each.value.secret_id
}

Resist over-abstracting. A module that tries to cover Postgres, Salesforce and S3 in one interface ends up with forty optional variables and is harder to read than three plain resources. Modularise per connector family, not universally.

9. CI that cannot destroy a pipeline

A workable pipeline:

Pull request runs terraform plan and posts the output as a comment.
A policy check fails the build if the plan contains any destroy or replace on fivetran_connection or fivetran_destination. Those need a human decision, because both mean a re-sync.
Merge to main runs terraform apply against staging.
Production apply is manual, gated on an approval.

The cheap version of step 2:

terraform plan -out=tf.plan
terraform show -json tf.plan > plan.json
jq -e '[.resource_changes[]
  | select(.type | test("fivetran_(connection|destination)$"))
  | select(.change.actions | index("delete"))] | length == 0' plan.json \
  || { echo "Plan destroys a Fivetran connection. Blocked."; exit 1; }

Run plan on a schedule too, not only on pull requests. A nightly drift plan tells you when someone changed a sync frequency in the UI at 2am during an incident and never came back to codify it.

What the provider does not do

Be realistic about the boundaries:

Historical re-syncs and data ops are API/UI actions, not Terraform state. Trigger them with the REST API or the CLI.
Some connector config fields are write-only or normalised server-side, which produces perpetual diffs. lifecycle { ignore_changes = [...] } on the specific attribute is the pragmatic answer; document why each one is there.
Connector SDK deployments are separate. Terraform can manage the connection that runs a custom connector, but the code deploys through fivetran deploy. See our Connector SDK tutorial.
It is not a cost tool. It gives you the controls — table selection, schedules, pause states — but you still need to read usage. Our connection-level MAR guide covers that side.

A sane adoption order

Do not convert the whole account in one sprint. In order:

Import destinations and groups. Low risk, immediate value.
Import your three noisiest connections and put schema config under BLOCK_ALL.
Add webhooks and alerting.
Move users and teams across, so access reviews become a diff.
Backfill the remaining connections as each one next needs a change — never rewrite a working connection just to codify it.

After a quarter of that, the answer to "why is this configured this way?" is a commit message instead of a shrug.

If you would like this set up on an existing account without an accidental re-sync along the way, our Fivetran platform setup and management and maintenance and support teams do exactly this migration, or get in touch with the shape of your account.