ELT platforms with CDC schema drift and automatic backfills

The strongest ELT candidates are Fivetran, Airbyte, Estuary, Rivery, and Matillion Data Loader. They support different combinations of change data capture (CDC), automatic schema evolution, and historical reprocessing—but the important distinction is whether a platform can backfill historical data while updating existing warehouse rows rather than inserting duplicates.

My recommendation: start with Fivetran or Airbyte for conventional database-to-warehouse replication, and evaluate Estuary if you need streaming CDC with explicit schema evolution and backfill controls. Rivery and Matillion are also worth evaluating for automated schema-drift handling. None should be assumed to guarantee duplicate-free results in every configuration; primary keys, merge semantics, and backfill behavior matter.fivetran.com+4

Platform comparison

Based on vendor documentation reviewed in October 2026.

Sources:fivetran.com+8

The three strongest candidates

• 

1. Fivetran — best for managed replication

Fivetran automatically propagates schema changes and uses merge operations to update existing rows. Certain SQL schema changes trigger automatic table re-syncs, and full or table-level re-syncs are available for recovery.fivetran.com+8

Watch out: Fivetran documents duplicate and data-integrity risks when a source primary key changes or is added after replication has started.fivetran.com+1

Docs: Schema changes ·Database connectors

---

• 

2. Airbyte — best for configurable schema handling

Airbyte lets you choose how source schema changes propagate. You can enable backfills for new or renamed columns so historical rows receive values for the new fields, rather than only updating recently changed records.docs.airbyte.com+1

For duplicate prevention, use a supported deduplicated sync mode with a stable primary key. Avoid append-only mode for a final table that must contain only one current row per entity.fivetran.com+8

Watch out: automatic backfills can be expensive, and CDC may require an explicit stream refresh when new tables or columns are added.fivetran.com+11

Docs: Schema change management ·Sync modes

---

• 

3. Estuary — best for streaming CDC and controlled backfills

Estuary handles compatible schema evolution without necessarily requiring a historical reload. Breaking changes can trigger a targeted backfill or resource recreation. Its standard materialization mode merges updates using collection keys, making it a good candidate for idempotent replication.fivetran.com+9

Watch out: delta-update mode can insert duplicates, and special backfill settings change how existing destination rows are handled. Validate the exact destination connector and configuration.docs.estuary.dev+1

Docs: Schema evolution ·Backfilling data

How to ensure backfills don't create duplicate records

Regardless of which platform you select, verify these five behaviors in a proof of concept.

1. Stable record identity: Every replicated entity has a reliable primary key or composite key.
2. Idempotent writes: Replaying the same source record updates or merges into its existing warehouse row instead of blindly inserting another.
3. Historical column backfill: When a column is added, historical rows are re-read if you need actual historical values rather than NULL.
4. Safe schema changes: Test column additions, renames, type changes, dropped columns, and primary-key changes separately. These are not equivalent operations.
5. Recovery and concurrency: Test a failed sync followed by a retry, a backfill overlapping live CDC events, and a re-sync after a schema change.

A key distinction: deduplication is not the same as exactly-once ingestion. A platform may deliver an event more than once but still maintain one current row per primary key. If you need a full audit history, duplicate versions in a history table may be intentional.

My shortlist by requirement

• Lowest operational overhead: Fivetran.
• Flexible schema propagation and configurable deduplication: Airbyte.
• Streaming CDC with key-based merges and targeted schema evolution: Estuary.

To narrow this to the best fit, which warehouse are you loading into—Snowflake, BigQuery, Databricks, Redshift, or something else? The destination's merge and transaction capabilities can materially change how safely a platform handles backfills.

Modern ELT platforms handle the complex intersection of Change Data Capture (CDC), schema drift , and historical backfills by relying on primary keys, log-based state tracking, and upsert (MERGE) logic in the target data warehouse.

When executed correctly, updates and backfills overwrite or append to existing rows safely via primary-key constraints rather than blindly duplicating warehouse records.

Leading ELT Platforms Supporting These Capabilities

• Airbyte (Cloud & Open Source)
  • CDC & Schema Drift: Features automated schema change management. It detects upstream alterations (like added or renamed columns) and provides propagation policies (e.g., Propagate field changes only or Propagate all field and stream changes).
  • Backfills & Duplication: Uses primary keys defined in the CDC stream to execute upserts/merges in the destination warehouse. When new columns are propagated or historical backfills trigger for a stream, Airbyte pulls data using state tracking tied to the primary key, preventing record duplication as long as the destination supports standard merge operations.
  • Learn more: Review Airbyte Schema Change Management.docs.airbyte.com
• Fivetran
  • CDC & Schema Drift: Automatically detects schema modifications (column additions, deletions, or data type changes) across database and application connectors. It safely evolves destination tables without failing the pipeline.
  • Backfills & Duplication: For log-based CDC, Fivetran reads write-ahead logs (WAL/binlogs) and maintains state precisely. When history is re-synced or an automatic backfill is forced for a modified or historical table, it applies updates using primary keys via MERGE statements in warehouses like Snowflake, BigQuery, and Databricks, avoiding duplicate primary keys.
  • Learn more: Explore Fivetran Automated Schema Handling.fivetran.com
• Estuary Flow
  • CDC & Schema Drift: Built on a real-time streaming architecture (utilizing collections and derivations) rather than batch-based polling. It handles real-time log-based CDC (Debezium-based connectors for PostgreSQL, MySQL, etc.) and manages schema changes dynamically within its unified runtime.
  • Backfills & Duplication: Flow treats historical backfills and real-time CDC streams as continuous unified data fabric endpoints. Because updates map back to capture time and unique keys within the active collections, materializations to a data warehouse write idempotently, avoiding duplication.
  • Learn more: Read about Estuary Flow CDC and Pipelines.vertexfrontier.com+1

How Duplication is Prevented Under the Hood

To achieve backfills and schema drift adjustments without record explosion, platforms rely on three rigid architecture requirements:

1. Mandatory Primary Keys: CDC incremental syncs require a defined primary key or unique identifier on the source table.docs.airbyte.com
2. Idempotent Destination Loading: Instead of simple INSERT statements, platforms generate MERGE (upsert) commands in the target warehouse. If an automatic backfill replays historical entries or a schema modification rescans a table, rows with existing primary keys are overwritten/updated rather than appended.
3. State Checkpointing: The sync engine tracks incremental state pointers (LSN for Postgres, binlog position for MySQL). Re-running a backfill updates the state safely without breaking historical offsets.

If you'd like to narrow this down, please share:

• Your specific source database (PostgreSQL, MySQL, Oracle, SQL Server, etc.)
• Your destination warehouse (Snowflake, BigQuery, Databricks, ClickHouse, etc.)
• Whether you prefer an open-source or fully-managed SaaS deployment

I can provide a tailored recommendation on which of these tools fits your exact stack.

Finding an ELT tool that natively ties together Change Data Capture (CDC), automatic schema drift handling, automatic historical backfills (especially for new columns/structures), and deduplication requires looking at platforms built on real-time streaming architectures or robust state management.docs.airbyte.com+2

The top options that meet these exact requirements span both managed platforms and open-source ecosystems:

1. Airbyte (Cloud & Open Source)

Airbyte handles schema evolution gracefully and provides native mechanisms to address historical gaps when schema shifts happen.docs.airbyte.com

• CDC Support: Uses log-based CDC (via Debezium under the hood for databases like PostgreSQL, MySQL, Oracle, and SQL Server).
• Schema Drift: Automatically detects upstream changes (new, renamed, or dropped columns) and supports automatic propagation.airbyte.com+1
• Automatic Backfills: Airbyte features a dedicated Backfill new or renamed columns setting. When enabled, if a schema change is detected, Airbyte can automatically trigger a resync/backfill for that stream so historical rows get populated for the new column rather than just leaving past data as NULL.docs.airbyte.com
• Deduplication: Uses primary keys defined in the source streams to load data cleanly into destination tables via standard upsert/append modes depending on the destination capabilities.
• Get started: Explore documentation or deploy via Airbyte.airbyte.com

2. Estuary Flow

Estuary Flow is a real-time streaming ETL/ELT tool built on a decoupled architecture that treats data as immutable collections, making continuous streaming and historical backfills seamless.medium.com

• CDC Support: Sub-second, log-based CDC capture from transactional databases.
• Schema Drift: Native, automated schema evolution. DDL changes (like ALTER TABLE ADD COLUMN ) in the database transaction log are automatically detected and propagated to the collection and warehouse destination without requiring pipeline restarts.
• Automatic Backfills: Combines log-based CDC with concurrent historical state snapshots. It handles historical backfills concurrently with live streaming data streams so you don't suffer downtime or manual intervention gaps.
• Deduplication: Enforces unique keys and precise materialization semantics on destinations (like ClickHouse or Snowflake) to prevent duplicate records.
• Get started: Check out the architecture on Estuary.vertexfrontier.com+2

3. Fivetran

Fivetran is the gold standard for zero-maintenance, automated pipelines, though it approaches backfills a bit differently than streaming-native tools.topetl.com

• CDC Support: Enterprise-grade log-based CDC for major relational databases with low impact on source systems.
• Schema Drift: Fully automated schema changes (blocking or non-blocking column additions, type widening). It automatically updates the destination table definitions.
• Automatic Backfills: Fivetran doesn't always auto-backfill entire historical sets on a minor column addition unless you manually trigger a historical resync for that specific table/column, though it automatically captures everything moving forward. For full historical re-syncs, it provides an easy one-click UI action rather than an implicit background guess (to prevent unexpected data warehouse cost spikes).
• Deduplication: Automatically handles primary keys and merges/deduplicates rows natively in supported destinations.
• Get started: Test capabilities via Fivetran.docs.airbyte.com

Recommendation Summary

• Choose Estuary Flow if your priority is sub-second, streaming-native continuous backfilling and automated structural changes at high throughput.
• Choose Airbyte if you want fine-grained configuration over automatic historical column backfills upon schema drift, with flexibility between open-source self-hosting and cloud management.docs.airbyte.com+2

To help narrow down the best fit, tell me:

• What are your source databases (e.g., PostgreSQL, Oracle, MySQL)?
• What is your target data warehouse (e.g., Snowflake, BigQuery, Databricks, ClickHouse)?
• Roughly what is your data volume / change frequency?

For your requirements, I’d focus on ELT platforms that do more than copy data: you need reliable change data capture (CDC), schema evolution, automated historical re-syncs or backfills, and idempotent loading so retries and replayed events don’t create duplicate records in your warehouse.

One important distinction: CDC and backfills are relatively common features, but preventing duplicates end-to-end depends on how the tool handles primary keys, event ordering, deletes, retries, and warehouse-side MERGE or upsert logic.

Best ELT tools to shortlist

• 

1. Airbyte

(Best feature match)

Airbyte documents CDC for database sources, configurable schema-change handling, and an option to automatically backfill new or renamed columns by re-syncing the stream.docs.airbyte.com+1

Duplicate protection: Configure primary keys and an appropriate destination sync mode. Verify the specific connector and destination, particularly for CDC updates and deletes.

Best if schema-drift automation and control are priorities.

Airbyte website ·Schema drift and backfill docs

---

• 

2. Fivetran

(Best managed option)

Fivetran offers connector-dependent incremental replication, soft-delete handling, historical syncs, and table-level or full re-syncs to repair data mismatches.fivetran.com+1

Duplicate protection: A strong managed option for maintaining a current-state warehouse, but confirm the connector's key handling and retry/upsert behavior. History mode intentionally retains multiple versions of a record, so it is not the right mode if you expect only one row per key.fivetran.com+2

Best if you want less infrastructure to operate.

Fivetran website ·Re-sync documentation

---

• 

3. Estuary Flow

Worth evaluating if your workload is CDC-heavy and you want continuous replication rather than relying primarily on scheduled batch syncs.

Validate before choosing: Check support for your exact source and warehouse, automatic recovery/backfill behavior after schema changes, and how destination materializations enforce key uniqueness.

Estuary website

How I would choose

My recommendation: test Estuary Flow and Airbyte first

I would put Estuary Flow first for a CDC-heavy pipeline and Airbyte first if you want configurable ingestion with explicit schema-change and column-backfill controls.

Estuary documents key-based standard merge updates that prevent duplicate rows during incremental backfills; its delta-update mode does not provide the same guarantee. docs.estuary.devdocs.estuary.dev Airbyte's automatic backfill for newly added or renamed columns is also a particularly close match to your requirements.docs.airbyte.com+1

Before committing, run a proof of concept that:

• Replays the same CDC event twice.
• Updates and deletes an existing primary key.
• Adds a source column and verifies historical values are populated.
• Interrupts a sync and restarts it.
• Re-runs a backfill and checks that the warehouse still has exactly one current row per primary key.

One question will help me narrow this to the strongest fit: Which warehouse are you loading into, and what is your source database?