Incremental Load with Union Query
I am trying to tackle delete scenario in source system with below logic in incremental code, its throwing error, any help is appreciated.
SELECT 'N' AS IS_DELETED,
FROM SOURCE_TABLE ST
WHERE ST.UPDTE_DATE > ?-2
SELECT 'Y' AS IS_DELETED,
FROM INCORTA_TABLE INC
WHERE INC.KEY_COLUMN IN (
SELECT KEY_COLUMN FROM INCORTA_TABLE WHERE UPDATE_DATE > ?-180
SELECT KEY_COLUMN FROM SOURCE_TABLE WHERE UPDATE_DATE > ?-180 )
Can this type of SQL statement work in incremental logic?
The error message I am getting is as below
INC_03070402: Failed retrieving sample data from [OTM_CERT] with properties [[incremental, true], [incrementalMethod, 0], [incrementalColumn, ], [timestampFieldType, DATE], [fetchsize, 5000], [batchMethod, 0], [batchSize, ], [batchColumn, ], [batchUpperBound, ], [batchLowerBound, ], [timeBatchColumn, ], [chunkPeriod, ], [customNumberOfDays, ], [timeBatchUpperBound, ], [timeBatchLowerBound, ]]
I see. How about using the approach as we layout in the post: https://community.incorta.com/t/x1rarf/how-does-incorta-handle-source-deletions-of-rows
Basically, run this query as a full load:
SELECT KEY_COLUMN FROM SOURCE_TABLE WHERE CREATION_DATE > ?-180
Join the incorta table with the above key only table.
Filtering out all data that does not exist in the key table using a run time security filter.
Otherwise, the above query you described can be done within a MV. The only difference is that the source table is now extracted as another regular incorta table before they are used in this query.