Skip to main content
Solved

Duplicate rows on tables without Delta Filter in a Delta Load job

  • September 24, 2026
  • 1 reply
  • 9 views
Songfan
Level 6
Forum|alt.badge.img+1

Running into a confusing behavior with our Delta Load setup and hoping someone can shed some light on it.

We have a Data Job set to Delta Load mode, with several tables that don't have a Delta Filter configured. According to the docs, those tables should automatically fall back to a Full Load. For most of them that seems to work fine, but for some we're getting duplicate rows instead — identical in every column except _CELONIS_CHANGE_DATE.

What makes it stranger: if we manually trigger a Full Load for those same tables, the duplicates disappear and everything looks clean. The problem only comes back when those tables run through the Delta Load job again.

Two questions:

  1. Why does the "implicit Full Load" within a Delta job produce duplicates rather than overwriting existing data?
  2. Why does it behave differently from a manually triggered Full Load?

Best answer by Danieldo

This tripped us up too, and the explanation turned out to be less obvious than expected.

From what I understand, when a Data Job runs in Delta mode, Celonis doesn't do a classic Full Load (drop, truncate, reload) even for tables without a Delta Filter. Instead it reloads all rows from the source and tries to merge them into the existing target table. That merge relies on Primary Keys to decide whether a row should be updated or inserted as new.

If the PKs aren't set or aren't configured correctly, the system apparently can't match incoming rows to existing ones and falls back to just appending everything — which is where the duplicates come from. The differing _CELONIS_CHANGE_DATE is just the load timestamp, so it's the only thing that differs between the original and the appended copy.

If that sounds like it fits your situation, the steps that seem to resolve it:

  1. Check the PK configuration for the affected tables in Data Integration and make sure the right columns are flagged
  2. Verify that the key combination is actually unique in the source system
  3. After fixing the PKs, run a one-time manual Full Load to clear out the existing duplicates and get back to a clean baseline

After that, the Delta job should handle things as an upsert rather than an append. At least that's how it worked out in our case.

      1 reply

      Danieldo
      Level 8
      Forum|alt.badge.img+3
      • Danieldo
      • Level 8
      • Answer
      • September 24, 2026

      This tripped us up too, and the explanation turned out to be less obvious than expected.

      From what I understand, when a Data Job runs in Delta mode, Celonis doesn't do a classic Full Load (drop, truncate, reload) even for tables without a Delta Filter. Instead it reloads all rows from the source and tries to merge them into the existing target table. That merge relies on Primary Keys to decide whether a row should be updated or inserted as new.

      If the PKs aren't set or aren't configured correctly, the system apparently can't match incoming rows to existing ones and falls back to just appending everything — which is where the duplicates come from. The differing _CELONIS_CHANGE_DATE is just the load timestamp, so it's the only thing that differs between the original and the appended copy.

      If that sounds like it fits your situation, the steps that seem to resolve it:

      1. Check the PK configuration for the affected tables in Data Integration and make sure the right columns are flagged
      2. Verify that the key combination is actually unique in the source system
      3. After fixing the PKs, run a one-time manual Full Load to clear out the existing duplicates and get back to a clean baseline

      After that, the Delta job should handle things as an upsert rather than an append. At least that's how it worked out in our case.

      Community Code of ConductAccessibility statement