Tip of the week: Duplicate rows on tables without Delta Filter in a Delta Load job

What is the Tip of the Week Program?

With thousands of users posting across the global Celonis community daily, incredible workarounds and technical breakthroughs are happening every minute, but they can be easy to miss as you cannot follow all product categories.

To ensure our India User Group stays at the cutting edge of process intelligence, we are launching the Tip of the Week initiative.

This is how it works:  I and our team continuously monitors global forums, support trends, and partner channels to source the most relevant, real-world challenges users are facing.

We then curate the verified solutions, workarounds, and expert hacks into a 1-minute, easy-to-read tip tailored just for you.

Question 

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?

Workaround provided by ​@Danieldo 

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.

Have you faced similar issue before?

What's your go-to solution for this? Share it in the comments! And hey, if you'd like to contribute a tip for next post, I'm here just send me a message.