Hi Folks! I would like to know if there is any way to avoid inserting duplicate records when we execute delta jobs. I cannot use Replication Cockpit, since we're using a non-standard system.

Several ideas:

• play with the filters and/or the join tables associated to the extraction. Depending of the source system data structure you can remove duplicates that way
• Add transformation to the datajob where you have the delta extractions. Use those transformations to remove the duplicates
• in the source system, generate a view without duplicates. Extract from that view.

and that's all ideas I can think at this hour of the morning.... :)

HTH

Thanks, Guillermo, I'm trying to use a Delta Filter Statement like this VALUE >= (CURRENT_DATE - 1) to take the records from the last day, but Celonis doesn't allow me to use this type of function

AFIK you need to use vertica sql. So curren_date -1 should be CURRENT_DATE + INTERVAL '-1 day'

Or NOW() + INTERVAL '-1 day'

That said, I think to remember some colleague complaining that the filter fields of extraction didn't behave exactly as expected.... so maybe that will not work.

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/IntervalValueInput.htm

HTH

Hi Andres,

have you tried to set primary keys within your table extractions? (Configure columns -> Configure)

For our SAP connections this information is set automatically but for other connections like JDBC you need to set them manually.

When using Primary Keys the Delta Load will remove the old entry and you will not have any duplicates.

And regarding your Delta Filter statement. Isn't it possible to setup parameters that idenfity date_column_in_your_source_system >= MAX(<<date_column_in_your_source_system>>) ?

When you run the delta load on a daily level you should get Yesterday as MAX(date).

Thanks Dennis! I tried setting the primary keys manually, and it seems to be working.