Fivetran Cheat Sheet

Overview

Fivetran is a fully managed ELT (Extract, Load, Transform) platform that automates data pipelines from sources to destinations. With over 500 pre-built connectors, Fivetran handles the complexity of API changes, schema evolution, data type mapping, and incremental loading. The platform requires minimal configuration and automatically manages connector updates, ensuring reliable data replication without engineering maintenance.

Fivetran supports destinations including Snowflake, BigQuery, Redshift, Databricks, and PostgreSQL. It provides incremental syncs, log-based CDC for databases, automatic schema migration, transformation capabilities via dbt integration, and a comprehensive REST API for programmatic management. Fivetran is widely used in modern data stacks alongside dbt for transformation and BI tools for analytics.

Setup and Configuration

Connecting via Web UI

1. Log in to https://fivetran.com/dashboard
2. Click "Add connector" in the sidebar
3. Select your source (e.g., PostgreSQL, Salesforce, Stripe)
4. Configure source credentials
5. Select destination warehouse
6. Choose sync frequency
7. Select tables and columns to replicate
8. Click "Save & Test"

Connector Configuration Examples

PostgreSQL Source

{
  "service": "postgres",
  "config": {
    "host": "db.example.com",
    "port": 5432,
    "database": "production",
    "user": "fivetran_reader",
    "password": "${POSTGRES_PASSWORD}",
    "update_method": "WAL",
    "schemas": ["public", "analytics"],
    "replication_slot": "fivetran_slot",
    "publication_name": "fivetran_pub"
  }
}

Salesforce Source

{
  "service": "salesforce",
  "config": {
    "is_sandbox": false,
    "sync_mode": "INCREMENTAL"
  }
}

Stripe Source

{
  "service": "stripe",
  "config": {
    "api_key": "${STRIPE_API_KEY}",
    "sync_mode": "INCREMENTAL"
  }
}

REST API

Authentication

# Fivetran uses Basic Auth with API key and secret
# Get your API key/secret from Settings > API Config

# Base URL: https://api.fivetran.com/v1

# Test authentication
curl -X GET https://api.fivetran.com/v1/account \
    -u "${FIVETRAN_API_KEY}:${FIVETRAN_API_SECRET}" \
    -H "Content-Type: application/json"

Connector Management

# List all connectors
curl https://api.fivetran.com/v1/groups/{group_id}/connectors \
    -u "${API_KEY}:${API_SECRET}"

# Get connector details
curl https://api.fivetran.com/v1/connectors/{connector_id} \
    -u "${API_KEY}:${API_SECRET}"

# Create a connector
curl -X POST https://api.fivetran.com/v1/connectors \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "service": "google_analytics_4",
        "group_id": "group_id",
        "config": {
            "property_id": "123456789",
            "reports": [
                {
                    "table": "website_traffic",
                    "dimensions": ["date", "country", "city"],
                    "metrics": ["sessions", "activeUsers", "newUsers"]
                }
            ]
        },
        "sync_frequency": 360,
        "paused": false,
        "trust_certificates": true,
        "run_setup_tests": true
    }'

# Trigger manual sync
curl -X POST https://api.fivetran.com/v1/connectors/{connector_id}/force \
    -u "${API_KEY}:${API_SECRET}"

# Pause connector
curl -X PATCH https://api.fivetran.com/v1/connectors/{connector_id} \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{"paused": true}'

# Resume connector
curl -X PATCH https://api.fivetran.com/v1/connectors/{connector_id} \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{"paused": false}'

# Delete connector
curl -X DELETE https://api.fivetran.com/v1/connectors/{connector_id} \
    -u "${API_KEY}:${API_SECRET}"

# Resync connector (historical resync)
curl -X POST https://api.fivetran.com/v1/connectors/{connector_id}/resync \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{"scope": {"schema": {"table": ["column1", "column2"]}}}'

Destination Management

# Get destination details
curl https://api.fivetran.com/v1/destinations/{destination_id} \
    -u "${API_KEY}:${API_SECRET}"

# Create Snowflake destination
curl -X POST https://api.fivetran.com/v1/destinations \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "group_id": "group_id",
        "service": "snowflake",
        "config": {
            "host": "account.snowflakecomputing.com",
            "port": 443,
            "database": "FIVETRAN_DB",
            "auth": "PASSWORD",
            "user": "FIVETRAN_USER",
            "password": "${SNOWFLAKE_PASSWORD}",
            "role": "FIVETRAN_ROLE"
        },
        "region": "US",
        "time_zone_offset": "-5"
    }'

Groups (Workspaces)

# List groups
curl https://api.fivetran.com/v1/groups \
    -u "${API_KEY}:${API_SECRET}"

# Create group
curl -X POST https://api.fivetran.com/v1/groups \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{"name": "Production Team"}'

# List users in group
curl https://api.fivetran.com/v1/groups/{group_id}/users \
    -u "${API_KEY}:${API_SECRET}"

Sync Configuration

Sync Frequencies

Frequency	Minutes	Use Case
5 min	5	Real-time dashboards, critical data
15 min	15	Near real-time analytics
30 min	30	Standard operational data
1 hour	60	Regular business data
6 hours	360	Daily reporting data
12 hours	720	Reference/lookup data
24 hours	1440	Infrequently changing data

Schema Management

# Get connector schema (tables and columns)
curl https://api.fivetran.com/v1/connectors/{connector_id}/schemas \
    -u "${API_KEY}:${API_SECRET}"

# Modify schema (enable/disable tables and columns)
curl -X PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "schemas": {
            "public": {
                "tables": {
                    "orders": {
                        "enabled": true,
                        "columns": {
                            "id": {"enabled": true},
                            "amount": {"enabled": true},
                            "internal_notes": {"enabled": false, "hashed": false}
                        }
                    },
                    "audit_logs": {"enabled": false}
                }
            }
        },
        "schema_change_handling": "ALLOW_ALL"
    }'

Column Hashing

# Hash sensitive columns
curl -X PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "schemas": {
            "public": {
                "tables": {
                    "customers": {
                        "columns": {
                            "email": {"enabled": true, "hashed": true},
                            "phone": {"enabled": true, "hashed": true},
                            "ssn": {"enabled": false}
                        }
                    }
                }
            }
        }
    }'

Transformations (dbt)

# List transformation projects
curl https://api.fivetran.com/v1/dbt/projects \
    -u "${API_KEY}:${API_SECRET}"

# Create dbt project
curl -X POST https://api.fivetran.com/v1/dbt/projects \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "group_id": "group_id",
        "dbt_version": "1.7.0",
        "default_schema": "dbt_production",
        "git_remote_url": "https://github.com/org/dbt-project.git",
        "git_branch": "main",
        "folder_path": "/dbt",
        "target_name": "production",
        "threads": 4
    }'

# Create transformation schedule
curl -X POST https://api.fivetran.com/v1/dbt/transformations \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "dbt_project_id": "project_id",
        "dbt_model_name": "staging_orders",
        "run_tests": true,
        "paused": false,
        "schedule": {
            "schedule_type": "INTEGRATED",
            "connector_ids": ["connector_1", "connector_2"]
        }
    }'

Advanced Usage

Webhooks

# Create webhook for sync events
curl -X POST https://api.fivetran.com/v1/webhooks/account \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "url": "https://hooks.example.com/fivetran",
        "events": [
            "sync_start",
            "sync_end",
            "status",
            "dbt_transformation_execution"
        ],
        "active": true,
        "secret": "webhook-secret-123"
    }'

Terraform Provider

# Manage Fivetran infrastructure as code
terraform {
  required_providers {
    fivetran = {
      source  = "fivetran/fivetran"
      version = "~> 1.0"
    }
  }
}

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

resource "fivetran_group" "production" {
  name = "Production"
}

resource "fivetran_destination" "snowflake" {
  group_id = fivetran_group.production.id
  service  = "snowflake"
  config {
    host     = "account.snowflakecomputing.com"
    port     = 443
    database = "FIVETRAN_DB"
    auth     = "PASSWORD"
    user     = "FIVETRAN_USER"
    password = var.snowflake_password
    role     = "FIVETRAN_ROLE"
  }
}

resource "fivetran_connector" "postgres" {
  group_id = fivetran_group.production.id
  service  = "postgres"
  config {
    host     = "db.example.com"
    port     = 5432
    database = "production"
    user     = "fivetran"
    password = var.postgres_password
  }
  sync_frequency = 60
}

Log Service Integration

# Configure log destination (for audit logs)
curl -X POST https://api.fivetran.com/v1/external-logging \
    -u "${API_KEY}:${API_SECRET}" \
    -H "Content-Type: application/json" \
    -d '{
        "group_id": "group_id",
        "service": "azure_monitor_log",
        "config": {
            "workspace_id": "workspace-id",
            "primary_key": "primary-key"
        },
        "enabled": true
    }'

Troubleshooting

Issue	Solution
Sync stuck in progress	Check connector health in dashboard. Force sync or pause/resume connector
Schema changes detected	Review schema changes in UI. Set schema_change_handling policy
API rate limit errors	Reduce sync frequency. Check source API rate limits
CDC replication lag	Ensure replication slot is active. Check database WAL retention
Destination write errors	Verify warehouse permissions. Check disk/compute capacity
Historical resync slow	Increase warehouse size during resync. Limit tables/columns
OAuth token expired	Re-authenticate connector in Fivetran dashboard
Missing data	Check column exclusions in schema config. Verify source data exists
High MAR (Monthly Active Rows)	Exclude unnecessary tables/columns. Use column hashing for PII
Webhook not triggering	Verify webhook URL is accessible. Check event type configuration