Progress
0% View detailed progress dashboard

Cloud Lab: Fivetran CDC Pipeline

Build a fully managed CDC pipeline using Fivetran. Connect source databases to cloud warehouses with minimal configuration and zero infrastructure management.

Check Prerequisites Start Building

Overview

Fivetran is a fully managed data integration platform that automates data pipelines from sources to destinations. Unlike infrastructure-heavy solutions like Debezium or AWS DMS, Fivetran requires zero infrastructure management—no servers, no Kafka clusters, no replication instances.

Key features:

  • Fully managed SaaS platform—no infrastructure to provision or maintain
  • 500+ pre-built connectors for databases, SaaS apps, files, and events
  • Automatic schema drift detection and propagation
  • Log-based CDC where available (MySQL binlog, Postgres WAL), polling fallback otherwise
  • Built-in data type conversions and normalization
  • Usage-based pricing (Monthly Active Rows - MAR)

CDC Approaches in Fivetran:

  1. Log-based CDC: Reads MySQL binlog or Postgres WAL for low-latency capture (~1–5 minutes depending on plan)
  2. Incremental updates: Polls source using updated_at or similar column (minutes latency)
  3. Full table refresh: Re-imports entire table (for sources without CDC or incremental columns)

Prerequisites

  • Fivetran Account: Free trial available (14 days, no credit card required)
  • Source Database: MySQL 5.6+ or PostgreSQL 10+ with network access
  • Destination: Snowflake, BigQuery, Redshift, or Databricks account
  • Network Access: Fivetran IPs whitelisted on source database firewall
  • Database Permissions: Read access on source, write access on destination

Architecture

┌────────────────┐      ┌─────────────────┐      ┌──────────────┐
│   PostgreSQL   │      │   Fivetran      │      │  Snowflake   │
│   (Source)     │─────▶│   Connector     │─────▶│  (Target)    │
│   + WAL        │      │   (Managed)     │      │              │
└────────────────┘      └─────────────────┘      └──────────────┘
                              │
                              │ Monitoring
                              ▼
                        ┌─────────────┐
                        │  Dashboard  │
                        │  (Web UI)   │
                        └─────────────┘

Fivetran runs entirely on its own infrastructure—you only configure connectors via the web UI or API.

Lab Setup

Step 1: Sign Up for Fivetran Trial

  1. Visit https://fivetran.com/signup
  2. Create account with email (no credit card required for 14-day trial)
  3. Complete onboarding wizard
  4. You'll land on the Fivetran dashboard at https://fivetran.com/dashboard

Step 2: Prepare Source Database (PostgreSQL Example)

Enable logical replication for log-based CDC:

-- Check current replication setting
SHOW wal_level;
-- Should be 'logical' for CDC. If not, update postgresql.conf:

-- In postgresql.conf:
wal_level = logical
max_replication_slots = 5
max_wal_senders = 5

-- Restart PostgreSQL
sudo systemctl restart postgresql

-- Verify
SHOW wal_level;
-- Should return 'logical'

Create Fivetran User

-- Create read-only user for Fivetran
CREATE USER fivetran_user WITH PASSWORD '${FIVETRAN_PASSWORD}';

-- Grant schema access
GRANT USAGE ON SCHEMA public TO fivetran_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fivetran_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO fivetran_user;

-- Grant replication privileges for CDC
ALTER USER fivetran_user WITH REPLICATION;

Create Publication (for log-based CDC)

-- Create publication for all tables
CREATE PUBLICATION fivetran_publication FOR ALL TABLES;

-- Or create for specific tables
CREATE PUBLICATION fivetran_publication FOR TABLE customers, orders, products;

-- Verify publication
SELECT * FROM pg_publication;

Whitelist Fivetran IPs

Add Fivetran's IP addresses to your database firewall/security group. Get the list from:

Step 3: Prepare Destination (Snowflake Example)

In Snowflake, create objects for Fivetran to load data:

-- Connect to Snowflake
USE ROLE ACCOUNTADMIN;

-- Create database and schema
CREATE DATABASE IF NOT EXISTS FIVETRAN_DB;
CREATE SCHEMA IF NOT EXISTS FIVETRAN_DB.PUBLIC;

-- Create warehouse
CREATE WAREHOUSE IF NOT EXISTS FIVETRAN_WH 
  WITH WAREHOUSE_SIZE = 'XSMALL' 
  AUTO_SUSPEND = 300 
  AUTO_RESUME = TRUE;

-- Create Fivetran user
CREATE USER IF NOT EXISTS fivetran_user 
  PASSWORD = '${FIVETRAN_PASSWORD}' 
  DEFAULT_WAREHOUSE = FIVETRAN_WH 
  DEFAULT_NAMESPACE = FIVETRAN_DB.PUBLIC;

-- Create role and grant permissions
CREATE ROLE IF NOT EXISTS FIVETRAN_ROLE;
GRANT USAGE ON WAREHOUSE FIVETRAN_WH TO ROLE FIVETRAN_ROLE;
GRANT USAGE ON DATABASE FIVETRAN_DB TO ROLE FIVETRAN_ROLE;
GRANT ALL ON SCHEMA FIVETRAN_DB.PUBLIC TO ROLE FIVETRAN_ROLE;
GRANT CREATE TABLE ON SCHEMA FIVETRAN_DB.PUBLIC TO ROLE FIVETRAN_ROLE;
GRANT CREATE VIEW ON SCHEMA FIVETRAN_DB.PUBLIC TO ROLE FIVETRAN_ROLE;

-- Assign role to user
GRANT ROLE FIVETRAN_ROLE TO USER fivetran_user;

-- Set role as default
ALTER USER fivetran_user SET DEFAULT_ROLE = FIVETRAN_ROLE;

Step 4: Create Destination Connector in Fivetran

  1. In Fivetran dashboard, click + Connector (top right)
  2. Search for and select Snowflake
  3. Click Continue in "Setup as Destination" dialog
  4. Configure connection details:

Snowflake Destination Settings

  • Destination schema: fivetran_db (lowercase, Fivetran convention)
  • Host: xy12345.us-east-1.snowflakecomputing.com
  • Port: 443
  • User: fivetran_user
  • Password: (your password)
  • Database: FIVETRAN_DB
  • Warehouse: FIVETRAN_WH
  • Role: FIVETRAN_ROLE (optional)

Click Save & Test. Fivetran will validate connectivity.

Step 5: Create Source Connector (PostgreSQL)

  1. Click + Connector
  2. Search for and select PostgreSQL
  3. Click Continue in "Setup as Source" dialog
  4. Configure connection details:

PostgreSQL Source Settings

  • Destination schema prefix: postgres_prod (will appear as postgres_prod schema in Snowflake)
  • Host: postgres.example.com or IP address
  • Port: 5432
  • User: fivetran_user
  • Password: (your password)
  • Database: production_db

Advanced Settings

  • Update Method: Select Detect Changes via Log-based (CDC)
  • Publication Name: fivetran_publication
  • Replication Slot: Leave as default (Fivetran auto-creates: fivetran_<connector_id>)
  • Sync Frequency: 5 minutes (or higher for testing)

Click Save & Test. Fivetran will verify database connectivity and CDC setup.

Step 6: Configure Table Selection

After connection test passes:

  1. Fivetran auto-discovers all tables in the source database
  2. Review the Schema tab showing all tables
  3. Toggle tables/columns you want to sync (all are selected by default)
  4. Review column hashing options for sensitive data (e.g., email, ssn)

Special Fivetran Columns

Fivetran automatically adds metadata columns to each table:

  • _fivetran_synced: Timestamp when row was last synced to destination
  • _fivetran_deleted: Boolean flag for soft-deleted rows (true = deleted)
  • _fivetran_id: Internal unique identifier added only to tables that lack a primary key (used for deduplication)

Step 7: Trigger Initial Sync

  1. Click Start Initial Sync button
  2. Fivetran performs full table load for all selected tables
  3. Monitor progress in the connector's Status tab
  4. Initial sync time depends on table size and varies widely with row width, network, and destination

What Happens During Initial Sync?

  • Fivetran snapshots each table (full SELECT * equivalent)
  • Creates replication slot in PostgreSQL
  • Captures current WAL position as baseline
  • Begins tailing WAL for subsequent changes

Step 8: Enable Incremental CDC

After initial sync completes:

  1. Connector automatically switches to incremental CDC mode
  2. Reads PostgreSQL WAL for INSERT, UPDATE, DELETE operations
  3. Syncs changes every 5 minutes (or per configured frequency)
  4. No further configuration needed—CDC is automatic

Schema Change Handling

Fivetran automatically detects and propagates schema changes. Here's how it handles common DDL operations:

Column Addition

-- In source PostgreSQL
ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20);

-- Fivetran automatically:
-- 1. Detects new column during next sync
-- 2. Adds column to Snowflake table: ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20)
-- 3. Backfills NULL for existing rows
-- 4. Syncs new values going forward

Result: Zero downtime, automatic propagation.

Column Type Change

-- In source PostgreSQL
ALTER TABLE customers ALTER COLUMN age TYPE BIGINT;

-- Fivetran behavior:
-- 1. Detects the type change during the next sync
-- 2. Alters the destination column to a wider, compatible type
--    (e.g. age INTEGER -> age BIGINT) so existing and new values
--    continue to land in the same column

Note: For a compatible widening (such as INTEGER → BIGINT), Fivetran adjusts the destination column type in place, so no downstream column migration is needed. For a change Fivetran cannot apply safely, review its Schema Changes log to see how the column was handled.

Column Rename

-- In source PostgreSQL
ALTER TABLE customers RENAME COLUMN email TO email_address;

-- Fivetran behavior:
-- 1. Treats as column DROP + column ADD
-- 2. Stops syncing 'email' column
-- 3. Creates new 'email_address' column
-- 4. Backfills from source (NULL for old rows if data moved)

Best Practice: Avoid renames. Instead, add new column, backfill, then drop old.

Table Rename

-- In source PostgreSQL
ALTER TABLE customers RENAME TO clients;

-- Fivetran behavior:
-- 1. Creates NEW table 'clients' in destination
-- 2. Performs full re-sync
-- 3. Stops syncing 'customers' (becomes stale)
-- 4. Does NOT auto-drop old 'customers' table

Manual cleanup required: Drop old table in Snowflake if no longer needed.

Schema Change Best Practices

  • Test schema changes in dev/staging connector first
  • Use additive changes (add column, deprecate old) instead of renames/drops
  • Monitor Fivetran's Schema Changes log after DDL operations
  • Set up alerts for unexpected schema drift (Fivetran webhook → Slack/PagerDuty)

Delete Handling

Fivetran supports two modes for handling deletes:

1. Soft Deletes (Default)

Deleted rows are marked with _fivetran_deleted = true but retained in destination.

-- In source PostgreSQL
DELETE FROM customers WHERE id = 42;

-- In Snowflake (after sync)
SELECT * FROM customers WHERE id = 42;
-- Returns:
-- id | name        | email             | _fivetran_deleted | _fivetran_synced
-- 42 | John Smith  | john@example.com  | true              | 2024-06-15 10:32:00

Querying Without Deleted Rows

-- Exclude soft-deleted rows in queries
SELECT * FROM customers WHERE _fivetran_deleted = false;

-- Or create view for convenience
CREATE VIEW customers_active AS
SELECT * FROM customers WHERE _fivetran_deleted = false;

Advantages of Soft Deletes

  • Preserves historical data for audit/compliance
  • Enables point-in-time analysis
  • Simplifies accidental delete recovery
  • Required for Type 2 Slowly Changing Dimensions (SCD2)

2. Hard Deletes (Opt-in)

Enable via connector settings: Advanced → Delete Mode → Hard Delete

-- In source PostgreSQL
DELETE FROM customers WHERE id = 42;

-- In Snowflake (after sync)
SELECT * FROM customers WHERE id = 42;
-- Returns: 0 rows (record physically deleted)

When to Use Hard Deletes

  • GDPR/CCPA compliance (right to be forgotten)
  • Reduce storage costs for high-churn tables
  • Simplify queries (no need to filter _fivetran_deleted)

Gotcha: Hard Deletes + Resyncs

If you trigger a Historical Re-sync with hard deletes enabled, deleted rows will NOT be restored (source no longer has them). Plan accordingly.

Verification

1. Test INSERT (Create New Row)

-- In source PostgreSQL
INSERT INTO customers (name, email, created_at) 
VALUES ('Alice Johnson', 'alice@example.com', NOW());

-- Wait for next sync (check connector Status tab for "Synced" timestamp)

-- In Snowflake destination
SELECT * FROM postgres_prod.customers 
WHERE email = 'alice@example.com';

-- Should show new row with _fivetran_synced timestamp within last 5 min

2. Test UPDATE (Modify Existing Row)

-- In source PostgreSQL
UPDATE customers 
SET email = 'alice.j@example.com' 
WHERE name = 'Alice Johnson';

-- After sync

-- In Snowflake destination
SELECT name, email, _fivetran_synced 
FROM postgres_prod.customers 
WHERE name = 'Alice Johnson';

-- Should show updated email with new _fivetran_synced timestamp

3. Test DELETE (Remove Row)

-- In source PostgreSQL
DELETE FROM customers WHERE name = 'Alice Johnson';

-- After sync

-- In Snowflake (soft delete mode)
SELECT name, email, _fivetran_deleted 
FROM postgres_prod.customers 
WHERE name = 'Alice Johnson';

-- Should show: _fivetran_deleted = true

4. Monitor Sync Metrics

In Fivetran dashboard:

  • Status Tab: Last sync time, rows synced, errors
  • Logs Tab: Detailed sync logs, warnings, schema changes detected
  • Usage Tab: Monthly Active Rows (MAR) consumption

Key Metrics to Watch

Metric                  | Healthy Range      | Action if Outside Range
------------------------|--------------------|--------------------------
Sync Latency            | < 10 minutes       | Check network, increase frequency
Rows Synced per Hour    | Matches source     | Investigate if low or zero
Error Count             | 0                  | Review logs, fix auth/network
Schema Changes Detected | Expected changes   | Alert if unexpected drift

Fivetran-Specific Gotchas

1. Column Name Normalization

Issue: Fivetran lowercases all column names and replaces special characters with underscores.

Example:

-- Source: CustomerEmail
-- Destination: customeremail

-- Source: user-id
-- Destination: user_id

Solution: Use lowercase, snake_case column names in source. Or update queries to match Fivetran's normalization.

2. Replication Slot Growth

Issue: If Fivetran connector is paused or fails, PostgreSQL replication slot retains WAL logs, consuming disk space.

Solution: Monitor replication lag: 

-- Check replication slot lag
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name LIKE 'fivetran%';

-- If lag > 10GB and connector won't resume, drop slot:
SELECT pg_drop_replication_slot('fivetran_xxx');

Prevention: Set alerts for replication lag, resume connectors promptly.

3. Primary Key Requirements

Issue: Tables without primary keys sync slowly (full table refresh each cycle).

Solution: Add primary key or define unique column in Fivetran UI:

  • Connector → Schema tab → Table → Click gear icon → Set Primary Key
  • Or add PK in source: ALTER TABLE customers ADD PRIMARY KEY (id);

4. Large Table Initial Sync

Initial sync of billion-row tables can take hours. Speed up with:

  • Reduce row count: Sync recent data only (e.g., WHERE created_at > '2023-01-01')
  • Partition table: Split into smaller tables, sync separately
  • Bulk load: Export to S3/GCS, load via destination's native bulk import, then enable CDC

5. Data Type Conversions

Fivetran auto-converts types but may not preserve precision:

  • PostgreSQL NUMERIC → Snowflake NUMBER (very high scale may be reduced)
  • PostgreSQL JSON → Snowflake VARIANT (requires parsing in queries)
  • PostgreSQL TIMESTAMPTZ → Snowflake TIMESTAMP type (verify the destination type preserves the time-zone offset; plain TIMESTAMP without time zone carries no zone, so nothing is lost there)

Mitigation: Review data types in destination after initial sync. Add transformations if needed.

Monitoring & Alerting

Built-in Dashboard Metrics

Fivetran provides real-time metrics in the web UI:

  • Sync Success Rate: Percentage of successful syncs over last 7 days
  • Sync Frequency: Average time between syncs
  • Rows Synced: Total rows replicated (broken down by table)
  • Data Latency: Time between source change and destination availability

Alerting Setup

Configure alerts via Account Settings → Notifications:

  1. Email notifications for sync failures (default: enabled)
  2. Webhook integration for custom alerting (Slack, PagerDuty, Datadog)

Webhook Example (Slack)

POST https://hooks.slack.com/services/YOUR/WEBHOOK/URL
{
  "connector_id": "postgres_prod",
  "event": "sync_failed",
  "message": "Connector postgres_prod failed: Connection timeout",
  "timestamp": "2024-06-15T10:32:00Z"
}

Advanced Monitoring via API

# Fetch connector status via Fivetran API
curl -X GET \
  -u "API_KEY:API_SECRET" \
  https://api.fivetran.com/v1/connectors/postgres_prod

# Response includes:
# - succeeded_at: Last successful sync timestamp
# - failed_at: Last failed sync timestamp  
# - sync_state: "scheduled" | "syncing" | "paused"
# - data_delay_sensitivity: Alerting threshold

Best Practices

1. Use Schema Prefixes

Organize connectors with meaningful prefixes:

prod_postgres     → FIVETRAN_DB.PROD_POSTGRES.*
staging_postgres  → FIVETRAN_DB.STAGING_POSTGRES.*
analytics_mysql   → FIVETRAN_DB.ANALYTICS_MYSQL.*

Avoids table name collisions, simplifies multi-environment setups.

2. Implement Idempotent Downstream Transforms

Since Fivetran can re-sync data, ensure downstream dbt/transformations are idempotent:

-- Bad: Incremental without unique key
INSERT INTO fact_orders SELECT * FROM staging.orders;

-- Good: Idempotent merge
MERGE INTO fact_orders AS target
USING staging.orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET target.* = source.*
WHEN NOT MATCHED THEN INSERT *;

3. Enable Column Hashing for PII

Hash sensitive columns before replication:

  1. Connector → Schema → Table → Column gear icon
  2. Enable Hash column
  3. Fivetran applies SHA-256 before loading to destination

Result: Destination contains d2d2d2... instead of john@example.com.

4. Schedule Syncs During Low-Traffic Windows

For large tables, sync during off-peak hours to reduce source DB load:

  • Connector → Setup → Sync Frequency → Custom Schedule
  • Example: Sync every 6 hours starting at 2 AM UTC

5. Monitor MAR Consumption

Fivetran pricing is based on Monthly Active Rows (MAR):

  • MAR = Distinct rows synced per month (UPDATE counts once per month, not per change)
  • Dashboard → Usage → Review per-connector MAR
  • Optimize by excluding high-churn log tables (e.g., audit_logs)

MAR is billed on a tiered, per-million-row basis, and the effective price per row falls as monthly volume grows. Fivetran does not publish a single flat per-row rate that applies to every account, so model your own usage against current published rates: see Fivetran pricing.

6. Use dbt with Fivetran

Fivetran integrates natively with dbt Cloud for transformations:

  1. Enable dbt integration in Fivetran dashboard
  2. Configure dbt to run after each Fivetran sync
  3. Result: Automatic ELT pipeline (Extract, Load, Transform)

Cost Considerations

Fivetran uses usage-based pricing built on Monthly Active Rows (MAR):

How MAR Pricing Works

  • What counts: a MAR is any distinct row that is inserted, updated, or deleted at least once in a calendar month. A row is counted once per month no matter how many times it changes.
  • Tiered and per-million: MAR is billed in tiers, priced per million rows rather than as a single flat per-row rate.
  • Volume discounts: the effective price per row falls as monthly volume grows, so higher-volume accounts pay less per row.
  • Plan-dependent: Fivetran's plans bundle different features and rate structures, and rates are often quote-based. Model your own usage against current published numbers rather than any figure hard-coded here.

See Fivetran pricing for current tiers and rates.

MAR Calculation Example

Scenario: E-commerce database
- Customers table: 1M rows, 10K updates/day → ~1.3M MAR/month
- Orders table: 5M rows, 50K new/day → ~6.5M MAR/month
- Products table: 100K rows, 100 updates/day → ~130K MAR/month
--------------------------------------------------------------------
Total MAR/month: ~7.93M
Estimate cost by applying this MAR total to Fivetran's current
published pricing tiers.

Cost Optimization Tips

  • Exclude high-churn, low-value tables (e.g., session logs, click streams)
  • Use longer sync intervals for slowly changing data (hourly vs. 5-min)
  • Archive old data in source before initial sync (e.g., > 2 years old)
  • Negotiate volume discounts for high-volume (tens-of-millions of MAR) workloads

Fivetran vs. Other CDC Tools

Aspect Fivetran AWS DMS Debezium
Infrastructure Zero (fully managed SaaS) Replication instance (managed) Self-hosted Kafka + Connect
Setup Time 15-30 minutes 1-2 hours 4-8 hours
Connectors 500+ pre-built AWS services only 10+ databases
Schema Changes Auto-detected and propagated Manual handling required Manual handling required
Latency ~1–5 minutes (log-based, depending on plan) Seconds Sub-second
Cost Model Usage-based (MAR) Instance hours Infrastructure only
Monitoring Built-in dashboard + API CloudWatch metrics Kafka metrics + custom
Best For SaaS-first teams, rapid setup AWS-native stacks Event-driven architectures

Common Issues & Solutions

"Connection timeout" during setup

  • Verify Fivetran IPs are whitelisted in source database firewall
  • Check database is publicly accessible (or use SSH tunnel/VPN)
  • Test connectivity: psql -h HOST -p 5432 -U fivetran_user -d production_db

"Replication slot does not exist" error

  • Fivetran's auto-created replication slot was dropped (manual cleanup or DB restart)
  • Solution: In Fivetran UI, click Re-authenticate to recreate slot
  • Or manually create: SELECT pg_create_logical_replication_slot('fivetran_xxx', 'pgoutput');

Sync shows "Delayed" status

  • Source database is under heavy load (Fivetran queries timing out)
  • Check source DB CPU/connections: SELECT count(*) FROM pg_stat_activity;
  • Increase sync frequency or add read replica for Fivetran

Data not appearing in destination

  • Check connector status: Should show "Synced" not "Paused" or "Incomplete"
  • Verify table is selected: Connector → Schema tab → Table toggle
  • Review logs: Logs tab → Filter by table name for errors

Duplicate rows in destination

  • Table lacks primary key (Fivetran can't deduplicate)
  • Solution: Add PK in source or configure in Fivetran Schema tab
  • Temporary fix: Deduplicate with QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _fivetran_synced DESC) = 1

Additional Resources

View All Cloud Labs