Getting Started with Fivetran

This guide will walk you through setting up Fivetran and creating your first data connector.

Prerequisites

Before you start, make sure you have:

• A Fivetran account (sign up for free trial)
• Access to a data warehouse (Snowflake, BigQuery, Redshift, Databricks, etc.)
• Warehouse credentials with permissions to create schemas and tables
• Access to a source system you want to sync data from (database, SaaS app, etc.)
• Source system credentials with read permissions

Step 1: Create Your Fivetran Account

Sign Up

1. Visit fivetran.com/signup
2. Choose your plan:
   • Free Trial: 14-day trial with full features
   • Starter: For small teams (500k MAR free)
   • Enterprise: For larger organizations

Set Up Your Account

1. Verify your email address
2. Complete the onboarding questionnaire
3. Access the Fivetran dashboard

---

Step 2: Connect Your Data Warehouse (Destination)

Fivetran needs to know where to load your data. Set up your destination first.

Snowflake Example

1. In Fivetran Dashboard:

   • Click Destinations → + Add destination
   • Select Snowflake

2. Create Dedicated Fivetran User in Snowflake:

3. Enter Connection Details in Fivetran:

   • Host: account.region.snowflakecomputing.com
   • Port: 443
   • User: FIVETRAN_USER
   • Password: Your password
   • Database: FIVETRAN_DB
   • Connection Method: Choose authentication type

4. Test Connection: Click "Test" to verify connectivity

BigQuery Example

1. In Google Cloud Console:

   • Create a service account for Fivetran
   • Grant roles: BigQuery Data Editor, BigQuery Job User
   • Download JSON key file

2. In Fivetran Dashboard:

   • Click Destinations → + Add destination
   • Select BigQuery
   • Upload service account JSON key
   • Select project and dataset
   • Test connection

Other Supported Warehouses

• Amazon Redshift
• Azure Synapse Analytics
• Databricks
• PostgreSQL
• MySQL
• Snowflake
• Google BigQuery
• And more...

---

Step 3: Create Your First Connector (Source)

Now let's connect a data source. We'll use PostgreSQL as an example.

Example 1: PostgreSQL Database Connector

1. Prepare Your PostgreSQL Database

Enable logical replication for CDC (optional but recommended):

Create Fivetran user with read permissions:

2. Configure Connector in Fivetran

1. In Fivetran Dashboard:

   • Click Connectors → + Add connector
   • Search and select PostgreSQL

2. Enter Connection Details:

   • Host: Your database hostname
   • Port: 5432 (default)
   • User: fivetran_user
   • Password: Your password
   • Database: your_database

3. Choose Connection Method:

   • Direct Connection: If Fivetran can reach your database directly
   • SSH Tunnel: For databases behind firewalls
   • Reverse SSH Tunnel: For maximum security

4. Configure Sync Settings:

   • Sync Frequency: Every 6 hours (default) - adjust based on needs
   • Replication Method:
     • Logical Replication (CDC) - real-time, most efficient
     • XMIN - good for smaller databases
     • Batch - full table scans

5. Select Tables to Sync:

   • Choose schemas and tables to include/exclude
   • Review column selection
   • Set primary keys if not detected

6. Test & Save: Test connection and save connector

Example 2: SaaS Application (Salesforce)

1. In Fivetran Dashboard:

• Click Connectors → + Add connector
• Search and select Salesforce

2. Authenticate:

• Click Authorize to connect via OAuth
• Log in to Salesforce
• Grant permissions to Fivetran

3. Configure Sync:

• Sync Frequency: Every 6 hours (or custom)
• API Type:
  • Bulk API - for historical loads (recommended)
  • REST API - for smaller data volumes
• Objects: Select Salesforce objects (tables) to sync
  • Standard objects: Account, Contact, Opportunity, Lead, etc.
  • Custom objects: Your custom Salesforce objects

4. Historical Sync:

• Choose historical sync range (all time, last year, etc.)
• Initial sync may take hours depending on data volume

5. Test & Save

---

Step 4: Monitor Your First Sync

Understanding Sync Status

1. Go to Connector Dashboard:

   • View sync history
   • Monitor current sync progress
   • Check for errors or warnings

2. Sync Phases:

   • Historical Sync: Initial load of all data (first sync only)
   • Incremental Sync: Ongoing syncs of new/changed data
   • Schema Update: When source schema changes are detected

3. Key Metrics to Watch:

   • Rows synced: Number of rows updated
   • Duration: Time taken for sync
   • MAR (Monthly Active Rows): For billing purposes
   • Warnings/Errors: Any issues encountered

Check Data in Your Warehouse

After the sync completes, verify data has arrived:

Snowflake:

BigQuery:

---

Step 5: Understand Fivetran Schema Conventions

Schema Naming

Fivetran creates schemas in your warehouse based on the connector:

• salesforce - Salesforce data
• google_analytics - Google Analytics data
• postgres_public - PostgreSQL public schema
• mysql_db_name - MySQL database

Table Naming

• Tables retain their source names (lowercase)
• Nested/semi-structured data is flattened into relational tables
• Arrays become separate tables with foreign keys

System Columns

Fivetran adds metadata columns to every table:

• _fivetran_synced - Timestamp when row was last synced
• _fivetran_deleted - Boolean indicating if row was deleted (soft deletes)
• _fivetran_id - Unique identifier for deduplication (when no primary key exists)

Example Schema Structure

---

Step 6: Set Up Transformations (Optional)

Fivetran integrates natively with dbt for transformations.

Enable dbt Transformations

1. In Fivetran Dashboard:

   • Navigate to Transformations
   • Click + Create Transformation
   • Choose dbt Core or dbt Cloud

2. Connect Your Git Repo:

   • Link GitHub, GitLab, or Bitbucket
   • Select repository with dbt project
   • Choose branch to run

3. Configure Run Schedule:

   • After each connector sync - Most common
   • On a schedule - Independent of syncs
   • Manual - Run on-demand only

4. Environment Variables:

   • Set dbt profiles and environment variables
   • Configure warehouse connection for transformations

Use Fivetran dbt Packages

Fivetran provides pre-built dbt packages for common sources:

These packages include:

• Pre-built staging and mart models
• Standard transformations and enrichments
• Tests and documentation
• Best practice SQL patterns

---

Step 7: Configure Alerts and Notifications

Stay informed about your data pipelines:

1. In Fivetran Dashboard:

   • Go to Settings → Notifications

2. Configure Alert Channels:

   • Email: Send to team members
   • Slack: Post to channels
   • PagerDuty: For critical alerts
   • Webhooks: Custom integrations

3. Alert Types:

   • Sync failures: When a connector fails
   • Schema changes: When source schema changes
   • Data volume anomalies: Unusual row counts
   • Sync delays: When syncs take longer than expected
   • Connector warnings: Non-fatal issues

---

Common Setup Patterns

Pattern 1: Multi-Environment Setup

Separate dev/staging/prod environments:

Tips:

• Use separate Fivetran connectors for each environment
• Sync production more frequently (hourly)
• Sync staging less frequently (daily) to save costs
• Use Fivetran's duplicate connector feature

Pattern 2: Multi-Region Setup

For global companies with regional data:

Pattern 3: Hybrid Cloud Setup

Mix of cloud warehouses:

• Snowflake: For operational analytics
• BigQuery: For machine learning workloads
• Fivetran: Sync same sources to both warehouses

---

Troubleshooting Common Issues

Issue: "Connection Timeout"

Causes:

• Firewall blocking Fivetran IPs
• Incorrect host/port
• Network connectivity issues

Solutions:

1. Whitelist Fivetran IP addresses
2. Verify host and port are correct
3. Use SSH tunnel or Reverse SSH for databases behind firewalls
4. Test connectivity with telnet or nc from allowed network

Issue: "Permission Denied"

Causes:

• Insufficient database permissions
• User doesn't have read access to tables
• Warehouse permissions not granted

Solutions:

1. Review and grant necessary permissions (see Step 3)
2. Verify user can query tables manually
3. Check warehouse-specific requirements in Fivetran docs

Issue: "High MAR Usage"

Causes:

• Syncing unnecessary tables
• Full table replication instead of incremental
• High update frequency on large tables

Solutions:

1. Exclude unnecessary tables and columns
2. Switch to incremental or CDC sync modes
3. Reduce sync frequency for less critical data
4. Review MAR breakdown in Fivetran dashboard

Issue: "Schema Drift Warnings"

Causes:

• Source schema changed (column added/removed)
• Data type changes in source

Solutions:

1. Review schema change notifications
2. Approve schema changes in Fivetran dashboard
3. Update downstream dbt models if needed
4. Enable auto-approval for trusted sources

---

Next Steps

Congratulations! You've set up your first Fivetran connector. Here's what to do next:

1. Add More Connectors:

   • Identify other critical data sources
   • Set up additional connectors following same process

2. Optimize Sync Schedules:

   • Align with business needs
   • Balance freshness vs. cost

3. Set Up Transformations:

   • Install dbt if not already done
   • Use Fivetran dbt packages as starting point
   • Build custom transformations (dbt guide)

4. Implement Monitoring:

   • Set up alerts for failures
   • Monitor MAR usage
   • Track sync performance

5. Learn Best Practices:

   • Review Fivetran best practices
   • Explore use cases
   • Try hands-on tutorials

---

Getting Help

Fivetran Support

• Documentation: fivetran.com/docs
• Support Portal: Available to paid customers
• Status Page: status.fivetran.com

Community Resources

• Fivetran Community: community.fivetran.com
• Knowledge Base: Searchable help articles
• Slack: Join data engineering communities

Need Expert Help?

Setting up a robust data integration platform involves many considerations: security, cost optimization, connector selection, and architecture design. Contact me for consulting on Fivetran implementation and ELT architecture.