TRACKDELETES (Default Behavior)

You can use the TRACKDELETES directive to ensure that deletions are automatically tracked and handled during data extraction or import. It doesn't require separate deletion logs or manual intervention.

Hard Delete

When records are deleted in the source system, the TRACKDELETES directive ensures that these deleted records are automatically excluded during data import or processing.

Example:

IMPORT SOURCE PRODUCTS WITH TRACKDELETES AS PROD_BASE
IMPORT SOURCE AS SALES

DEFINE DATASET PROD_DIM
 ROWSOURCE PROD_BASE ;
 THIS = PROD_BASE;
END

DEFINE DATASET PROD_REPLENISH
 ROWSOURCE PROD_DIM INNER JOIN SALES ON (PROD_DIM.PROD_ID = SALES.PROD_ID);
 THIS = PROD_DIM[PROD_NAME, PROD_ID];
 THIS[REPLENISH_FLG] = CASE WHEN SUM(SALES.QUANTITY_SOLD) > 5 THEN 1 ELSE 0 END;
 GROUPBY[PROD_NAME, PROD_ID];
 PRIMARYKEY[PROD_ID];
 REFRESH ON CHANGES IN [PROD_DIM, SALES];
END

In this example, TRACKDELETES ensures that discontinued products are reflected in the PROD_DIM dataset without requiring deleted records to be handled separately.

Soft Delete

The deletion operation is applied during the extraction of data. The DELETETYPE[SOFT] flag is used to mark the deleted records, and the extraction process identifies which records need to be flagged. If you don't specify a flag name, the default flag ISDELETED is used.

IMPORT SOURCE CUSTOMERS DELETETYPE[SOFT] WITH PRIMARYKEY[CUST_ID] TRACKDELETES

DEFINE UPDATEABLE DATASET CUSTOMERS_SD_D FROM CUSTOMERS END

In this example, soft delete is initiated during the import of the CUSTOMERS dataset. The records identified by TRACKDELETES are flagged as deleted using the default ISDELETED column.

You can specify a custom flag name in the DELETETYPE directive:

IMPORT SOURCE SALES DELETETYPE[SOFT[ISTRANDELETED]] TRACKDELETES[IN[THDELETE]]

DEFINE UPDATEABLE DATASET CUSTOMERS_SD_D FROM CUSTOMERS END