Portal

Hi Amanda,

SD_ and SE_ are lineage tables managed by the certification process. Emptying them is done through two distinct, supported mechanisms depending on what happened to the sample records:

• Records that were certified into master/golden records are removed by publishing a hard delete, which physically removes every trace of the record, including its SD_ rows.
• Records that were rejected during pre-consolidation validation never became golden records - they only exist in SE_, and they are removed by the purge job, driven by the model's data retention policy.

Please take a database backup of the data location schema before running any of the steps below.

1. Remove certified sample records (clears SD_, MD_, GD_, etc.)

1.1 Enable deletion on the entity (one-time model configuration)

1. In the Application Builder, open the model edition and select the entity.
2. Select the Delete Enabled option.
3. For each relation listed under the entity's Is Referenced By menu, set the Delete Propagation value: Cascade, Nullify, or Restrict.
4. Deploy the model edition. Deletion is handled by the jobs generated at deployment, so any change to the deletion or propagation configuration requires a redeployment.

1.2 Publish the deletion

Use HARD_DELETE, not SOFT_DELETE. Soft delete only moves records to the deleted-records storage and explicitly does not remove data from the SD, SA, MH, or GH tables, so it will not achieve what you are asking for.

Option A - From the application (simplest for a small number of records)

Add a Delete action to the entity's action set, deploy, then select the sample records in the collection and run the action with the hard delete option.

Option B - Golden-record deletion via SQL

Delete a golden record and it automatically deletes the master records attached to it, as well as the source records. Load the SA_ table (this applies to all entity types, including ID- and fuzzy-matched):

-- 1. Initialise the load vLoad_id := <repository_schema>.INTEGRATION_LOAD.GET_NEW_LOADID(     '<data_location_name>',     'Sample data cleanup',     'Remove sample records after business review',     '<user_name>' );  -- 2. One INSERT per golden record to delete INSERT INTO <data_location_schema>.SA_PERSON     (B_LOADID, B_CLASSNAME, ID, B_DELETETYPE, B_DELETEOPERATION, B_DELETEAUTHOR, B_DELETEDATE) VALUES     (vLoad_id, 'Person', 37696, 'HARD_DELETE', RAWTOHEX(SYS_GUID()), 'ETL_USER', SYSDATE);  -- 3. Submit the load vBatch_id := <repository_schema>.INTEGRATION_LOAD.SUBMIT_LOAD(     vLoad_id, 'INTEGRATE_DATA', '<user_name>' ); 

Column notes:

• B_LOADID — the load ID returned by GET_NEW_LOADID, or a continuous load ID from GET_CONTINUOUS_LOADID.
• B_DELETETYPE — HARD_DELETE or SOFT_DELETE.
• B_DELETEOPERATION — a unique value per deleted record, for example a UUID as a string.
• B_CLASSNAME — the entity name of the records to delete.
• <GoldenID> — the golden record ID column.
• B_DELETEAUTHOR and B_DELETEDATE are optional.

The delete type and operation ID are automatically propagated to child records according to the delete propagation configured on the references. If a delete fails, for example because a child record prevents it, the error is traced in the entity's AE_ (authoring error) table.

Option C - Master-record deletion via SQL (removes one publisher's source records only)

If the sample data came from a single publisher and you want to remove only that publisher's contribution, use the same process but:

• Load the SD_ table instead of SA_.
• Instead of the golden ID column, load B_PUBID, plus B_SOURCEID for a fuzzy-matched entity, or the primary key attribute column for an ID-matched entity.
• Load B_LOADID, B_DELETETYPE, B_DELETEOPERATION, B_CLASSNAME, and optionally B_DELETEAUTHOR / B_DELETEDATE as above.

Please note that master-record deletion does not support delete propagation. It is not blocked by a restriction, not propagated by a cascade, and references to deleted records are not automatically nullified. Perform all related changes in the same load. If all master records of a golden record are deleted, the golden record becomes legless and is automatically deleted.

Option D - REST API

Call the loading Data URL with the DELETE_DATA action, a deleteOptions element carrying deleteType, and a deleteRecords element listing the entity and the golden IDs, between the load initialisation and submission calls.

2. Clear the SE_ (Source Data with Errors) rows and remaining SD_ lineage

Error records and source lineage are removed by the purge job, according to the retention policy defined in the model. This is the supported way to empty SE_.

1. In the Application Builder, open the model edition and double-click the Retention Policies node.
2. In the Data Retention Policy editor, set the retention for Source Data and Source Errors. For sample data, set the Retention Type to No Retention, or to Period with a short duration. You can define an entity-specific policy via Add Entity Retention Policy if you only want this to apply to the entities used for the sample load.
3. Save and deploy the model edition — the retention policy has no effect until the model is deployed.
4. In the Management view, expand Data Locations > your data location, and double-click the Purge node.
5. Tick Active, set the schedule, optionally tick Purge Repository Artifacts to also prune job logs, batches, and loads once all their data has been purged, then save.
6. Run the purge job.

Two things to keep in mind:

• The purge only impacts the history and lineage of the data in the data location. It does not delete actual golden and master data - that is what step 1 is for.
• Repository artifacts are retained according to the longest retention policy among their associated entities. If any entity is set to Forever, the related artifacts are not purged.

Once the cleanup is finished, remember to set your retention policies back to the values required by your data governance and compliance requirements.

3. If the entire data location contains only throwaway sample data

This applies only if nothing in the data location needs to be kept - for example a sandbox environment you are willing to empty completely. It is destructive and irreversible, so please do not use it if the data location holds any records you intend to keep.

In that case, the documented approach is to generate and run TRUNCATE statements for the entity tables from the database catalog, explicitly excluding the DL_% and EXT_% system tables. This removes data only, not structures. The full procedure with the Oracle, PostgreSQL, and SQL Server queries is in this KB article:

https://support.semarchy.com/support/solutions/articles/43000652381-reset-a-data-location

Thanks and Best regards,
Haashim