Hard Deletes in ELT: Why Cursor-Based Syncs Miss Them and How CDC Fixes It

Deleted rows stay in your warehouse forever with cursor-based syncs. Learn how to detect them and fix it with full refresh, soft deletes or CDC.

Hard Deletes in ELT

A few minutes after placing order 3, the customer cancels it. The application removes the row from the orders table in production. An hour later, the sync to the warehouse completes with no errors. The revenue dashboard still shows order 3. It will next week too. It will next year too. Nobody gets an alert because nothing failed. The warehouse slowly fills up with rows that no longer exist in the source. Finance sees one number in the app and another in the dashboard. The data team can't explain why.

This is the hard delete problem. It's caused by the way incremental syncs look for new data. This is proven by a simple SQL query that you can run on your warehouse.

Why does an incremental sync miss deleted rows?

An incremental sync asks the source system for rows that have changed since the last time the sync was run. Most often, the source system will filter the table using some timestamp (like updated_at) that is part of the data. If a row has been deleted, there is no way for the query to return that row and, as a result, the data warehouse retains the old copy forever.

The column the sync filters on is called the cursor. In Erathos, the sync takes the highest updated_at value already in the destination and asks the source only for rows newer than that. The query looks like this:

SELECT id, status, amount, updated_at
FROM orders
WHERE updated_at > '2026-10-22 09:00:00';

We ran this against a small orders table. At 09:00 the table had orders 1 to 5, and the warehouse had the same five rows. Before the next run, we deleted order 3, marked order 2 as shipped, and added order 6. The cursor query returned only two rows:

+----+---------+--------+---------------------+
| id | status | amount | updated_at |
+----+---------+--------+---------------------+
| 2 | shipped | 200.00 | 2026-10-22 09:30:00 |
| 6 | pending | 600.00 | 2026-10-22 09:40:00 |
+----+---------+--------+---------------------+

For the second and sixth order, their timestamps changed. For the third order, the timestamp is not affected by the DELETE (the record is removed, so the row is no longer there to be read). The sync process merges the two records and says it succeeded.

The whole process from the first sync to the dashboard is as follows:

[IMAGE 1: upload gtm-1477-img1.png here]

Two changes between runs, one row in the warehouse

The loud case of problem is deletes, but a more quiet one is multiple updates in the same row in between two runs of the sync process.

Suppose an order goes from pending to paid at 09:10, and then to refunded at 09:50. The next run of the sync process at 10:00 will read the row as it is now. So, the warehouse will only ever see refunded as state, and the paid state is lost for good. Since the source table does not keep old versions of the row either, there is no way for a cursor-based query to read the earlier values.

This is a problem only if you need historical values. A report on the current status of all orders is correct, but a report on how many orders were first paid and then refunded, or how long they were pending before being paid, is wrong, but looks fine.

The window can be made smaller by running the sync process more frequently, but there is always a chance of multiple updates being made within the window. The only way to address this is with a method that records each update as it happens, and that is what log-based CDC does.

How to check if your warehouse has the problem

We can compare the row counts and primary keys between the source and the warehouse. If there are extra rows in the warehouse that have keys that don’t exist in the source, then those extra rows are hard deletes that the sync missed.

After the sync in our test, the counts no longer matched:

SELECT COUNT(*) AS source_count FROM orders; -- 5
SELECT COUNT(*) AS warehouse_count FROM wh_orders; -- 6

The money gap is the part people notice first. The warehouse total was 300.00 higher than production, exactly the amount of the deleted order:

+--------------+-----------------+---------------+
| source_total | warehouse_total | excess_amount |
+--------------+-----------------+---------------+
| 1800.00 | 2100.00 | 300.00 |
+--------------+-----------------+---------------+

A count tells you something is off, but not which rows. To find them, copy the list of primary keys from the source into the warehouse, for example as a one-off export into a table called source_order_ids. Then look for warehouse rows with no match:

SELECT w.id, w.status, w.amount
FROM wh_orders w
LEFT JOIN source_order_ids s ON s.id = w.id
WHERE s.id IS NULL;

In our test, it returned the deleted order and nothing else:

+----+---------+--------+
| id | status | amount |
+----+---------+--------+
| 3 | pending | 300.00 |
+----+---------+--------+

If there is activity on the table, new rows will have been added to the source, between your two counts. This is why you must compare rows created before some time (yesterday at midnight).

Option 1: periodic full refresh

In a full refresh, Erathos reads the entire table from the source and overwrites its counterpart in the destination. This deletes rows in the warehouse that have been deleted in the source because those rows will no longer exist in the new table copy. This method does not require a cursor column in the source table nor does it require any modifications to the source table.

The downside to a full refresh is that its performance and cost do not scale with the amount of data that’s changed. Erathos reads all data in the table regardless of how much of it has changed. This results in more pressure on your production database and more warehouse computing, and for very large tables, full refreshes can take very long to complete. Also, because Erathos plans are based on the number of rows that pass through your pipelines each month, a full refresh of a table with 1 million rows once a day would result in 30 million rows being processed in a 30-day month.

The best of both worlds is to run incremental refreshes on the table most of the time and a full refresh once in a while to clean things up. Erathos’s Enable Full On setting allows you to schedule full runs on top of the partial runs. The downside to this approach is that deleted rows are still present in the warehouse until the next full refresh, and state changes that occur between syncs are lost.

Option 2: soft deletes with a deleted_at column

With soft deletes, the application never removes a row. It writes a timestamp in a deleted_at column instead. If it also moves updated_at forward, the cursor sync picks up the change like any other update:

UPDATE orders
SET deleted_at = TIMESTAMP '2026-10-22 09:15:00',
updated_at = TIMESTAMP '2026-10-22 09:15:00'
WHERE id = 3;

The next cursor query returns order 3 with its delete time:

+----+---------+--------+---------------------+
| id | status | amount | deleted_at |
+----+---------+--------+---------------------+
| 3 | pending | 300.00 | 2026-10-22 09:15:00 |
+----+---------+--------+---------------------+

Reports are then modified to filter out any deleted rows: WHERE deleted_at IS NULL. In our case the deleted rows will be ignored and we’ll have 2 live orders worth 300.00.

This approach works fine provided we can modify the application and its schema. If we are using a third party application and its database then we can’t use soft deletes. Also any path that removes data must be aware of the soft delete rule and follow it. This includes any administrative script, any cleanup job, and even support engineers running manual DELETE statements. Even if the rule is not followed on a single path then we have problem again. Soft deletes also do not address the issue of states missed between runs.

Option 3: log-based change data capture

The log is where all of the changes to a database are recorded. For PostgreSQL, it’s the write-ahead log (WAL). For MySQL, it’s the binary log (or binlog). Instead of querying tables, log-based change data capture (CDC) reads the log.

Each insert, update and delete is recorded as a separate change in the log. A delete is just another event in a stream of changes. PostgreSQL turns the WAL into a stream of changes using logical decoding. A client can replay the stream of changes in the same order that they occurred. A row that was changed from pending to paid to refunded will appear as three separate changes, not one.

This is the path a delete takes from the application to the warehouse:

[IMAGE 2: upload gtm-1477-img2.png here]

Each delete event will contain the old row (or at least the primary key of the old row) but no new row. In Debezium, an open source CDC engine, a delete event will have the old values in the before field, a null in the after field, and an op value of d. The null after field means the row no longer exists.

The warehouse tools will then transform those events into one of two common shapes:

  • A flag column. The row will continue to exist in the table and there will be a column indicating whether the row has been deleted or not. Fivetran, for example, will add a _fivetran_deleted column to each table in soft delete mode.
  • A delete timestamp on a change record. Each change to a row is recorded as a row and delete rows will have the time of deletion. Airbyte will add an _ab_cdc_deleted_at column that will only be set on records coming from DELETE statements. In the final table, it will exclude rows where the most recent change was a delete.

In any case, we have captured the delete and timestamped it, so we can ignore rows like this in our reporting, whilst retaining them in history tables. Your tool may write deletes into your tables in different ways based on configuration, so make sure to check its documentation before building reports.

With CDC, the nature of incremental delivery changes. Incremental delivery is not the same as streaming, as you can still schedule the delivery of changes in batches. However, instead of running a query on a table, the delivery process starts from the log.

What the source database needs for CDC

CDC relies on the database's log, so the database needs to write detailed log data. Thus, both PostgreSQL and MySQL require some configuration before the first CDC connection can be made.

PostgreSQL

PostgreSQL requires that the wal_level configuration parameter is set to logical. If you are creating a CDC connection with Erathos, the source database needs the following settings:

  • wal_level = logical
  • max_replication_slots and max_wal_senders must be at least 10
  • max_slot_wal_keep_size must not be set to -1
  • A user with replication permission must exist in the database

You can learn more about how to connect PostgreSQL to Erathos in our PostgreSQL documentation.

A database restart applies these settings. The one exception is max_slot_wal_keep_size, which takes effect without a restart. On a fresh PostgreSQL install, we set the WAL level and restarted:

ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
SHOW wal_level;
wal_level
-----------
logical
(1 row)

  • max_slot_wal_keep_size: A replication slot retains WAL data until the CDC reader reads the data. This setting sets the maximum WAL size to retain. Erathos notes that by default a slot will retain an infinite amount of WAL data. This means if the reader stops for a long time, the WAL size will continue to grow until it exceeds the database disk size. It is a good idea to set this value based on your disk size, e.g. ALTER SYSTEM SET max_slot_wal_keep_size = '5GB';.
  • REPLICA IDENTITY: This is a per-table setting. By default, PostgreSQL only writes old primary key values to the WAL when a row is updated/deleted. Setting the replica identity to FULL means the WAL will record the old values of all columns. Erathos needs this on a per-table basis to capture the complete before and after state of its rows.

PostgreSQL supports four types of replica identity.

[IMAGE 3: upload gtm-1477-img3.png here]

We created an orders table, checked its replica identity, changed it, and checked again (d means default, f means full):

CREATE TABLE orders (id INT PRIMARY KEY, status TEXT);
CREATE TABLE
SELECT relname, relreplident FROM pg_class WHERE relname = 'orders';
relname | relreplident
---------+--------------
orders | d
(1 row)

ALTER TABLE orders REPLICA IDENTITY FULL;
ALTER TABLE
SELECT relname, relreplident FROM pg_class WHERE relname = 'orders';
relname | relreplident
---------+--------------
orders | f
(1 row)

MySQL

MySQL needs the binlog turned on, in ROW format with FULL row images. Without them, update and delete events don't carry the complete before and after state of the rows. MySQL 8.0 already uses row-based logging by default, and its default row image is full. A fresh MySQL 8.4 server showed both values:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+

This still needs to be checked since servers and managed services might have changed those defaults. Also note that old binlog files are deleted after a certain retention period. If the Erathos connection loses a binlog file that CDC needs, then Erathos will have to run a fresh initial snapshot.

Snapshot modes and plan

The first choice is the snapshot mode, which can be either initial or no_data. The first option will initially create a copy of the table, and after that, the changes are streamed from the WAL/binlog. The second option will only stream the changes still available in the log.

You typically use initial if you have a new table to replicate into the warehouse, and no_data if you already have the history and only need future changes.

CDC is part of the Erathos Pro plan, and we have a full guide on setting up a MySQL CDC replication into BigQuery here.

Choosing an update type per table

One database usually holds very different tables. A list of countries, a log of page views, and an orders table don't need the same sync. In Erathos you set the update type for each table, so each one can use the method that fits it:

Table shape

Update type

Why it fits

Small lookup tables (countries, plans, categories)

Full refresh

Reloading is cheap, and deletes go away on every run

Append-heavy tables with a primary key and a date or datetime column (events, logs)

Incremental (partial)

Reads only new rows, and rows are rarely deleted

Tables where deletes or status changes matter (orders, subscriptions, payments)

CDC

Gets every insert, update and delete from the log, in order

To use incremental sync in Erathos, you must define a primary key and select a date or datetime column to use as a cursor. For database sources, users must select the cursor column for each job. For partial modes, Partial Overwrite is the recommended partial mode, which updates existing rows in place rather than inserting new copies of each row.

The third row is the perfect example of our test table. The five orders, single delete, and $300 revenue gap could be resolved with a full refresh, as long as the table remains small. For millions of rows, CDC is the solution to avoid a full table re-read.

FAQ

Does running the incremental sync more often catch deletes?

No. Since cursor queries only return rows that are still present, deleted rows are missing regardless of how frequently the query runs. Increased frequency only reduces the time window where rows can be changed twice.

Do I need REPLICA IDENTITY FULL to capture deletes in PostgreSQL?

By default, PostgreSQL will include the old primary key on a delete event. This is sufficient to identify the deleted row. Erathos uses the FULL setting to obtain both the before and after state of the modified row. By default, a table without a primary key will not result in any update or delete events being emitted by Debezium. For more information, please see the Debezium documentation on PostgreSQL: Debezium PostgreSQL connector docs

What happens if the CDC connection stops for a while?

In PostgreSQL, the replication slot stores WAL until the reader catches up, so if max_slot_wal_keep_size has no limit, disk can fill up. In MySQL, the danger is in the opposite direction. If the binlog files are deleted before the CDC can read them, the connection will have to start over with a fresh initial snapshot.

Does CDC mean real-time streaming?

Not necessarily. CDC is just a fancy way of saying “the changes come from the database log”. You could still load up those changes into your data warehouse on a schedule if you wanted to.

Should I use the initial or no_data snapshot mode?

  • initial when the warehouse doesn't have the table yet. Erathos copies the table and then streams the changes.
  • no_data when the history is already loaded and you only want the changes from now on.

Can different tables from the same database use different sync methods?

Yes. In Erathos the update type is a per-table setting, so you can do a full refresh on lookup tables while using CDC on the orders table.

Which Erathos plan includes CDC?

CDC is available in the Pro plan, for the price of US$ 250/mo, paid yearly. All new accounts get the Pro version for 14 days.

Try it on your own tables

If you are seeing mismatching numbers between your warehouse and production, run the above check on primary keys on your largest table first. Then use CDC on any tables where you care about deletes. Try Erathos for free for 14 days, CDC included in the trial.