When loading data, Incorta can insert new records or update existing records that already have been loaded but it does not delete records that have already been loaded. Your source systems, however, may allow users to delete data from the database. After the data are deleted, those records may still exist in Incorta unless we handle the situation.
There are a number of strategies that can be implemented depending on the size of your data and whether the deleted records can be easily identified.
We recommend that you be familiar with these Incorta concepts before exploring this topic further.
These concepts apply to all releases of Incorta.
When a schema table is defined without incremental logic, full load logic will be applied on incremental refresh. The data in incorta will be truncated and reloaded in full. Joins from child tables and to parent tables will be recalculated. Formula columns will also be recalculated. Since the the table is rebuilt as a result of the full load, deleted records from the source will no longer be reflected in Incorta.
No special schema model design is required in Incorta to handle deletes in the source when using full loads.
Running full load is the easiest approach to managing data deletes but when the size of the table is large, incremental refresh may take a long time. Data sync from the loader service to analytics will also take a long time. This option should be used only when the size of the data is small or the incremental refresh is not frequent.
If executing full reloads is not practical, then deleted records can be identified in the source system and the deleted records can be extracted into a skinny table with the primary key of the table only,
Various ways can be used to identify deleted records in the source system. Here is one recommended approach:
You will need the ability to create a new table and trigger in the source database. The method is the following:
Some databases may provide a delete log and we can load that data into Incorta as well to populate the key-only table.
If a source delete log is not available and creating tables and database triggers is not allowed, we can use Incorta to identify the deleted records.
This design assumes that the full load of the key-only table is running much faster than running a full load for the base table. Otherwise, a simple solution can be just running full load for the base table.
Limit the size of the full extract of the deleted key columns
When the base table is huge and extracting even just the key column takes awhile, a possible workaround is to limit the number of records that are extracted from the source into the key-only table by setting the extract since date. This approach assumes that the old data will not be deleted and uses that assumption to define the range of data to be scanned.
The approaches described above are applied on an individual tables basis. The runtime security described above is only applied during runtime on the driving table. When there are deleted records in the parent table and the child records that joined under those deleted parent need to be filtered out or deleted, here is an alternate approach:
2. All tables with the associated key-only table and the DELETED formula will have a runtime security rule on the DELETED column
<SCHEMA>.<TABLE>.DELETED = 'N'
3. Parent to Child Cascade Delete will be implemented in the runtime security filter with the parent table DELETED column as well.
<SCHEMA>.<TABLE>.DELETED = 'N',
<SCHEMA>.<PARENT TABLE 1>.DELETED = 'N',
<SCHEMA>.<PARENT TABLE 2>.DELETED = 'N'
In this approach, the key-only table will not participate in any join directly and will not alter the query plan.
Please note that the deletion filter should be driven by the reporting requirement and defined individually. For example, when a supplier record is deleted or inactive, it may simply mean that no more transactions should be created against the supplier, but the existing historical transactions may still valid. The DELETED flag may not need to be cascaded to the transaction table.