Using Data Purge Feature For Handling Source Deletes

[amit_kothari]

Incremental data loads in Incorta insert new records and update existing ones, but they don't automatically delete records when they're removed from the source system (like Oracle Cloud ERP). Since source systems often allow data deletion, this can lead to discrepancies where records deleted at the source still exist in Incorta. However, Incorta provides ways to manage these deleted source records.

One such method is the Data Purge feature, introduced in version 2024.7.x. Data Purge removes unnecessary data from physical tables or materialized views, improving performance and disk space utilization. It works by creating an exclusion set that identifies the records deleted from the source system. This exclusion set is then compared against the corresponding table in Incorta. A purge job is then executed, physically deleting the identified records from both the parquet files (storage) and memory. Refer to the Incorta Doc for information on Data Purge

High Level Steps

• Create and load a Table based on PK columns, this will always have the most current records from source and will be Full Load always
• Create an exclusion set MV
• Configure the base table to handle deletes
• Run a Load plan with data purge option
• Run regular incremental loads

Delete Handling Setup

Step 1: For each table that needs delete handling, the first step is to create a corresponding skinny table that contains only the primary key column(s) for the table and that is always loaded in full.  It is recommended that these tables be placed into a separate schema whose only purpose is to hold these types of tables.  

Oracle Cloud ERP

1. Create a new Job in BICC for the particular VO
2. Create a new Connection and set the file type to PECSV and add the Job name and ID pair. 

1. Create a new Incorta schema for these PK tables and add the PK table using the Schema wizard with this data connection. Keep in mind that the PK table will show all the columns but when you do the load Incorta will only load the PK columns.
2. Always do a full load of the PK table. Make sure that the Incremental toggle is turned off for this table and ‘Load Last Batch’ is selected 

Oracle EBS

1. Create a new Incorta table which just has the primary keys from the source EBS table for which you want to track the deletes. For eg ‘Select invoice_id from apps.AP_INVOICES_ALL’
2. This table should not have any incremental logic and must be always run in Full mode

Step 2: The second step is to create a Materialized View (MV) that will compare the PK table against the base table to create the exclusion set which represents the records that have been deleted in the source table.  The MV can be placed in the schema with the PK table.  Here is an example of the MV code you would write to create the table that holds your exclusion set.

SELECT I.INVOICE_ID 
FROM   FUSION_AP.AP_INVOICES_ALL I
ANTI JOIN PK_SCHEMA.AP_INVOICES_ALL_PK P 
ON I.INVOICE_ID = P.INVOICE_ID

Configure Delete Handling

Next, configure your base table to use the exclusion set table that you have created by opening the Advanced settings tab.

Toggle on the Synchronizing delete operations option and select the exclusion set identifier schema and table.

Define the column mappings between the target table and the exclusion set and save.

Purge Deleted Records

Step 3: The next step to delete records from Incorta is to run a purge job.  This can be run table by table as needed or can be scheduled from a separate Load Plan with Data purge option.  The purge job will remove records from Incorta .

1. 
2. Once the above Purge Load plan runs the regular incremental Load plan , this will also synchronize the deletes to Google BigQuery if that has been configured as a data destination.