Fivetran Best Practices
This guide provides comprehensive best practices for implementing and managing Fivetran at scale. Follow these guidelines to optimize cost, reliability, security, and maintainability of your data pipelines.
Table of Contents
- Connector Setup & Configuration
- Schema Design & Management
- Cost Optimization
- Security & Compliance
- Performance Optimization
- Monitoring & Alerting
- Transformations with dbt
- Team Collaboration
- Disaster Recovery & Business Continuity
- Documentation & Maintenance
1. Connector Setup & Configuration
Choose the Right Sync Mode
Log-based CDC (Change Data Capture) - RECOMMENDED when available
Incremental Sync - Good for most use cases
Full Table Replication - Use sparingly
Configure Sync Frequency Appropriately
Best Practices:
- Align sync frequency with business SLAs, not "as fast as possible"
- Schedule syncs during source system off-peak hours
- Use different frequencies for different connectors based on data criticality
- Monitor and adjust based on actual usage patterns
Example: Salesforce Connector Configuration
Use SSH Tunnels for Database Security
Naming Conventions
2. Schema Design & Management
Understand Fivetran Schema Mapping
Leverage Fivetran System Columns
Every table includes these metadata columns:
Best Practices:
Handle Schema Drift Properly
Response Workflow:
- Receive schema change notification
- Review impact on downstream transformations
- Update dbt models if necessary
- Approve change in Fivetran dashboard
- Monitor initial sync after approval
Organize Warehouse Schemas
3. Cost Optimization
Understanding Monthly Active Rows (MAR)
Strategy 1: Exclude Unnecessary Data
Audit Checklist:
Strategy 2: Optimize Sync Frequency
Strategy 3: Use Incremental Sync Modes
Strategy 4: Consolidate Small Connectors
Strategy 5: Monitor MAR Usage
Create MAR Alerts:
Cost Estimation Formula
4. Security & Compliance
Principle of Least Privilege
Database Connectors:
Warehouse Destinations:
Network Security
Data Encryption
Handle PII/PHI Properly
Compliance Frameworks
SOC 2 Compliance
GDPR Compliance
HIPAA Compliance
5. Performance Optimization
Optimize Source Database Impact
Benefits:
- Zero impact on production writes
- No query contention with application
- Can use replica with different resource allocation
Optimize Warehouse Performance
Sizing Guidelines:
Optimize Sync Timing
Partition Large Tables
When to partition:
- Tables > 1GB
- Time-series data
- Queries typically filter on date ranges
6. Monitoring & Alerting
Key Metrics to Monitor
Monitoring Queries
Fivetran Metadata Tables
Create a Monitoring Dashboard
7. Transformations with dbt
Fivetran + dbt Integration
Use Fivetran dbt Packages
dbt Source Configuration
Incremental Models Using Fivetran Metadata
Best Practices for dbt with Fivetran
8. Team Collaboration
Role-Based Access Control
Change Management Process
Documentation Standards
9. Disaster Recovery & Business Continuity
Backup Strategy
Disaster Recovery Plan
Multi-Region Redundancy
10. Documentation & Maintenance
Quarterly Maintenance Checklist
Version Control for Configurations
Summary: The Fivetran Excellence Framework
Next Steps
- Assess Current State: Audit your existing Fivetran setup against this guide
- Prioritize Improvements: Focus on security and cost first
- Implement Incrementally: Don't try to do everything at once
- Measure Impact: Track before/after metrics (cost, reliability, performance)
- Iterate: Revisit quarterly and refine your approach
Need help implementing these best practices? Contact me for consulting on Fivetran optimization, cost reduction, and architectural review.