Configuring Fivetran and Airbyte Incremental Sync: Understanding Sync Modes, Cursor Fields, and CDC to Minimize Warehouse Load

Introduction

Imagine you've built a pipeline that syncs your e-commerce orders table from Postgres into Snowflake every hour. It works beautifully — right up until the day your orders table hits 50 million rows. Suddenly, each sync takes 40 minutes, your warehouse credits are burning through the roof, and your data team is getting angry Slack messages about costs. The root cause? Your connector is still doing a full table scan every single hour, re-copying all 50 million rows just to capture the 2,000 new orders that came in since the last run.

This is one of the most common and most expensive mistakes in data engineering, and it's entirely preventable. Both Fivetran and Airbyte — two of the most widely used EL (Extract-Load) tools in the modern data stack — offer incremental sync modes that copy only the data that has changed since the last run. But configuring them correctly requires understanding a handful of concepts: sync modes, cursor fields, and Change Data Capture (CDC). Get these right, and you can cut your warehouse load by 90% or more on large, active tables.

By the end of this lesson, you'll be able to look at any table in your data stack and confidently decide how it should be synced — and then configure that sync correctly in Fivetran or Airbyte.

What you'll learn:

• The difference between full refresh and incremental sync, and why the distinction matters at scale
• What cursor fields are, how to choose good ones, and why bad cursor choices silently break your pipelines
• How Change Data Capture (CDC) works at the database level and when it's worth the extra setup
• How to configure incremental sync in both Fivetran and Airbyte, including the specific settings and trade-offs
• How to detect and fix the most common incremental sync failures

---

Prerequisites

• A basic understanding of what a data pipeline does (data moves from a source database to a destination warehouse)
• Familiarity with SQL at a beginner level — you should know what SELECT, WHERE, and a timestamp column look like
• You don't need a running Fivetran or Airbyte instance to follow along, though the hands-on exercise assumes access to at least one

---

Why Full Refresh Is Secretly Killing Your Budget

Before we talk about incremental sync, let's make sure we really understand what a full refresh does, because it's intuitive and that's exactly what makes it dangerous.

In a full refresh sync, your EL tool connects to the source, runs something equivalent to SELECT * FROM orders, takes every single row, and loads all of it into the destination. If rows already exist in the destination, they're either replaced or the table is truncated and rebuilt from scratch. Every. Single. Sync.

This is fine when your tables are small. A 10,000-row table full-refreshes in seconds and costs almost nothing. But data has a way of growing. A busy SaaS app might generate 100,000 new event records per day. After a year, that's 36 million rows. After two years, 73 million rows. If you're syncing that table every hour, you're doing 8,760 full scans of an ever-growing table per year. Your warehouse query costs scale linearly with data volume, and full refresh makes you pay for the entire history every single time.

The alternative is incremental sync: instead of asking "give me everything," you ask "give me everything that changed since the last time I asked." This is the core idea, and everything else in this lesson is about how to implement it correctly.

---

The Two Approaches to Incrementalism: Cursor Fields and CDC

There are fundamentally two ways a sync tool can figure out what's changed since the last run. Understanding the difference between them is the most important conceptual step in this lesson.

Cursor Field (Watermark-Based) Sync

A cursor field is a column in your source table that monotonically increases over time — meaning it never goes backward. The most common examples are:

• updated_at — a timestamp that gets set whenever a row is created or modified
• created_at — a timestamp set only at row creation
• id — an auto-incrementing integer primary key

Here's how cursor-based sync works: after each successful sync, the tool saves the maximum value it saw in the cursor field. On the next run, it only queries for rows where the cursor field is greater than that saved value. In SQL terms, it's essentially doing this:

SELECT *
FROM orders
WHERE updated_at > '2024-03-15 14:00:00'

The saved value (2024-03-15 14:00:00 in this example) is called the watermark or cursor state. It moves forward with each successful sync like a bookmark.

This approach is elegant and simple, but it has one critical limitation: it only works if your source table actually updates the cursor field when a row changes. If a developer inserts a row with updated_at hardcoded to an old date, or if your source system has a bug where updated_at doesn't get touched on updates, those rows will be silently skipped. More on this in the troubleshooting section.

Change Data Capture (CDC)

Change Data Capture, or CDC, is a fundamentally different approach. Instead of querying the source table directly, CDC reads from the database's internal transaction log — the record the database itself keeps of every insert, update, and delete operation.

Most relational databases maintain this log for their own internal purposes (replication, crash recovery). It goes by different names depending on the database: the WAL (Write-Ahead Log) in Postgres, the binlog in MySQL, the redo log in Oracle. CDC connectors tap into this log and stream changes in real time.

The power of CDC is completeness and accuracy. Because you're reading from the transaction log rather than inferring changes from a column value, you capture:

• Inserts (new rows)
• Updates (changed rows) — including updates to the cursor field itself
• Hard deletes (rows that were removed) — something cursor-based sync cannot detect at all

The trade-off is complexity. CDC requires specific database configuration (enabling logical replication in Postgres, for example), appropriate permissions, and more operational overhead. It's also more sensitive to source database load and log retention settings.

---

Choosing the Right Sync Mode for Each Table

Not every table in your source system needs the same treatment. Here's a practical decision framework:

Use full refresh when:

• The table is small (under ~100,000 rows) and changes slowly — like a countries lookup table
• The table doesn't have any reliable timestamp column and CDC isn't available
• You need to capture hard deletes and can't set up CDC

Use cursor-based incremental when:

• The table is large and growing
• The source table has a reliable updated_at timestamp that gets updated on every write
• You don't need to capture hard deletes (or deletes are rare and handled separately)

Use CDC when:

• You need real-time or near-real-time data freshness
• You need to capture hard deletes accurately
• Your source database supports it and you have the infrastructure access to configure it
• The table is very high-volume and even cursor-based queries are too expensive

---

Configuring Incremental Sync in Fivetran

Fivetran takes an opinionated approach to sync configuration — it does a lot of the heavy lifting for you, which is both a strength and a constraint. Let's walk through how it works in practice.

Sync Modes in Fivetran

For most database connectors (Postgres, MySQL, SQL Server, etc.), Fivetran defaults to using CDC via the database's replication log when possible. When CDC isn't available or isn't configured, it falls back to a cursor-based approach using the primary key and a _fivetran_synced metadata column it manages itself.

For API-based connectors (Salesforce, Stripe, HubSpot), Fivetran manages the cursor internally — you don't choose the cursor field, because Fivetran knows the API's pagination and filtering endpoints better than you do.

Setting Up a Fivetran Postgres Connector with CDC

To configure a new Postgres connector in Fivetran using CDC (logical replication):

1. In your Fivetran dashboard, navigate to Connectors in the left sidebar, then click + Add connector.
2. Search for and select PostgreSQL.
3. Fill in your connection details: host, port (usually 5432), database name, username, and password.
4. In the Connection configuration section, you'll see a field labeled Replication slot. This is where Fivetran expects the name of the Postgres logical replication slot you've created.
5. On your Postgres server, you (or your DBA) need to run:

-- Enable logical replication (requires superuser and postgresql.conf change)
-- In postgresql.conf: wal_level = logical

-- Create a replication slot for Fivetran
SELECT pg_create_logical_replication_slot(
  'fivetran_slot',
  'pgoutput'
);

-- Create a publication for the tables you want to replicate
CREATE PUBLICATION fivetran_pub FOR TABLE orders, customers, products;

6. Back in Fivetran, enter fivetran_slot as the replication slot name and fivetran_pub as the publication name.
7. Under Table sync settings, you can choose which tables to include. Fivetran will sync them incrementally via CDC.

Fivetran's Sync Mode for Non-CDC Sources

For sources that don't support CDC, Fivetran uses what it calls Standard sync mode, which is cursor-based. You can view (but not always change) the sync behavior per-table in the connector's schema settings. To get there:

1. Open your connector in the Fivetran dashboard.
2. Click the Schema tab.
3. You'll see each table listed. Fivetran shows the sync mode and, for some connectors, allows you to toggle between History mode (which preserves all historical versions of a row) and standard mode.

---

Configuring Incremental Sync in Airbyte

Airbyte gives you significantly more control — and therefore more responsibility — than Fivetran. You explicitly choose the sync mode and cursor field for each stream (Airbyte's term for a table or endpoint).

Airbyte's Sync Mode Vocabulary

Airbyte defines sync modes as a combination of source behavior and destination behavior:

Source Mode	Destination Mode	What it does
Full Refresh	Overwrite	Full refresh, destination table is replaced
Full Refresh	Append	Full refresh, new data is appended (history preserved)
Incremental	Append	Only new/changed rows, appended to destination
Incremental	Append + Dedup	Only new/changed rows, destination deduplicates by primary key
CDC	Append + Dedup	CDC source, destination deduplicates

For production incremental pipelines, Incremental | Append + Dedup is almost always what you want. It ensures that if a row is updated at the source, you don't end up with duplicate rows in your warehouse — the dedup step keeps only the latest version.

Setting Cursor Fields in Airbyte

When you configure a connection in Airbyte:

1. Navigate to Connections in the left menu, then click + New connection.
2. Select your source and destination, complete the authentication steps.
3. On the Configure connection step, you'll see a list of streams (tables). Each stream has a Sync mode dropdown.
4. Set the sync mode to Incremental | Append + Dedup.
5. Once you select Incremental, two new fields appear: Cursor field and Primary key.
6. For the orders table in our example, set:
   • Cursor field: updated_at
   • Primary key: order_id

The cursor field tells Airbyte what to compare against its saved watermark. The primary key tells the dedup logic how to identify "the same" row across multiple syncs.

Setting Up CDC in Airbyte

Airbyte's Postgres CDC source connector uses the same Postgres logical replication mechanism as Fivetran, but you configure it differently:

1. When creating a new Postgres source, you'll see a Replication method dropdown.
2. Select Logical Replication (CDC) instead of the default Standard.
3. Fill in the Replication slot name and Publication name (these are the Postgres objects you created earlier with pg_create_logical_replication_slot and CREATE PUBLICATION).
4. Once you set up the connection and configure streams, Airbyte will use the WAL rather than polling the table.

With CDC active in Airbyte, each stream in the connection automatically gets CDC-based incremental behavior. You'll still want to set the destination mode to Append + Dedup so that updates and deletes are handled correctly at the destination.

---

What a Good Cursor Field Looks Like (and What Doesn't)

Choosing the right cursor field is where incremental sync either succeeds or quietly fails. Here are the properties you need:

A good cursor field:

• Is a timestamp that is set by the application or database on every insert AND every update
• Is indexed at the source (so the WHERE updated_at > ? query is fast)
• Is never set to a past value by the application
• Covers all rows — no NULLs

A bad cursor field:

• created_at on a table where records are frequently updated
• A user-editable field (like a "last login" that users can manipulate)
• An auto-increment ID on a table where existing rows can be updated
• Any column with NULL values

A classic trap is using id as a cursor field on an orders table where order statuses get updated. New orders get new IDs (so they're captured), but when an order status changes from "pending" to "shipped," the id doesn't change — so that update is completely invisible to the incremental sync. Your warehouse ends up with stale order statuses and no error to tell you something's wrong.

---

Hands-On Exercise

This exercise walks you through configuring an Airbyte incremental sync for a realistic scenario and verifying it's working correctly.

Scenario: You have a customer_events table in Postgres that records user activity in a SaaS app. It has columns: event_id (integer, primary key), customer_id, event_type, event_data (jsonb), occurred_at (timestamp), and updated_at (timestamp). The table currently has 8 million rows and grows by ~50,000 rows per day. You want to sync it to BigQuery incrementally.

Step 1: Inspect the source table

Connect to your Postgres source and run:

-- Check for NULLs in your intended cursor field
SELECT COUNT(*) FROM customer_events WHERE updated_at IS NULL;

-- Verify the index exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'customer_events'
AND indexdef LIKE '%updated_at%';

-- Check recent activity range
SELECT MIN(updated_at), MAX(updated_at)
FROM customer_events;

If the NULL check returns any rows, you have a problem to fix before configuring incremental sync.

Step 2: Configure the Airbyte connection

1. In Airbyte, create a new Postgres source with your connection details.
2. In Replication method, choose Standard (cursor-based) for this exercise.
3. Create a new connection to your BigQuery destination.
4. In the stream configuration, find customer_events.
5. Set sync mode to Incremental | Append + Dedup.
6. Set cursor field to updated_at, primary key to event_id.

Step 3: Run the initial sync and record metrics

Trigger a manual sync and note:

• How long it takes
• How many rows were synced (visible in the Airbyte job logs)
• The cursor state saved after the sync

Step 4: Insert a test row and verify incremental behavior

-- Insert a new event
INSERT INTO customer_events (customer_id, event_type, event_data, occurred_at, updated_at)
VALUES (42, 'page_view', '{"page": "/pricing"}', NOW(), NOW());

-- Update an existing event (simulating a correction)
UPDATE customer_events
SET event_data = '{"page": "/pricing", "corrected": true}',
    updated_at = NOW()
WHERE event_id = 1000;

Trigger another sync. The job logs should show exactly 2 rows synced — the new insert and the updated row. Check BigQuery to confirm the update is reflected in the destination.

Step 5: Compare full refresh vs. incremental timing

Temporarily switch the sync mode to Full Refresh, run one sync, and note the duration and row count. Switch back to Incremental. The difference in duration is your incremental sync dividend — the time and cost you're saving on every subsequent run.

---

Common Mistakes & Troubleshooting

Mistake 1: The watermark never advances

Symptom: Each sync copies the same rows over and over. Rows from the first sync keep appearing in the destination.

Cause: The cursor field is a constant value (e.g., updated_at is set once at creation and never updated).

Fix: Switch to using created_at if you only care about inserts, or implement a database trigger/application code change to update updated_at on every write. For updates, CDC is the proper long-term solution.

Mistake 2: Hard deletes disappear

Symptom: A customer deleted their account at the source, but their record still shows up in your warehouse.

Cause: Cursor-based incremental sync has no mechanism to detect deleted rows. It only sees what's there, not what's gone.

Fix: Either use CDC (which reads DELETEs from the transaction log), implement soft deletes at the source (add a deleted_at timestamp column instead of actually removing rows), or run periodic reconciliation queries.

Mistake 3: Clock skew causes missed rows

Symptom: Sporadic missing rows that appear hours or days later.

Cause: If your source database's clock is slightly ahead of the system recording the watermark, rows written right at the boundary of a sync window can be missed.

Fix: In Airbyte, you can configure a lookback window that re-reads a small overlap period to catch boundary rows. In Fivetran, CDC mode eliminates this problem entirely since it reads from the log rather than polling by timestamp.

Mistake 4: Replication slot lag fills disk

Symptom: Postgres server disk usage climbs steadily; alerts fire about disk space.

Cause: A CDC replication slot holds WAL segments until they're consumed. If your sync is paused, delayed, or broken, the slot accumulates data.

Fix: Monitor replication slot lag with:

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

Set up alerts if lag exceeds a threshold. Consider dropping and recreating the slot if the sync is going to be paused for more than a few hours.

---

Summary & Next Steps

You now have a complete mental model for how incremental sync works in modern EL tools, and you can make informed decisions about how to configure it.

Here's the key decision logic to take away: start with cursor-based incremental for any table over ~100,000 rows that has a reliable updated_at column. Validate that the cursor field is actually being updated by your application. Graduate to CDC when you need hard delete detection, real-time freshness, or when even cursor-based queries are too expensive at source.

In Fivetran, CDC is configured at the connector level through database replication slots and publications — Fivetran manages the cursor state for you. In Airbyte, you have explicit per-stream control over sync mode, cursor field, and primary key, giving you more flexibility at the cost of more configuration responsibility.

Next steps to deepen your skills:

• Explore dbt's snapshot feature: Once you're loading incrementally into your warehouse, dbt snapshots let you track slowly changing dimensions and build point-in-time history from your incremental loads.
• Learn about Debezium: This open-source CDC platform is what Airbyte actually uses under the hood for many database connectors. Understanding it gives you flexibility to build custom CDC pipelines.
• Study your warehouse's merge behavior: Tools like BigQuery, Snowflake, and Databricks each handle the deduplication step differently. Understanding how MERGE statements work in your target warehouse helps you reason about the Append + Dedup sync mode's performance.
• Set up data freshness monitoring: Tools like Monte Carlo, re_data, or dbt's built-in source freshness checks can alert you when your incremental syncs stop making progress — catching broken watermarks before they become data quality incidents.