Delete Data from Datasets in Automated Data Distribution

You can adjust your Automated Data Distribution (ADD) output stage to delete old or obsolete data from a dataset while loading new data to the dataset. When you delete old data and upload new data to datasets, deleting and uploading the data is done in a single transaction. This helps keep data in your project consistent and avoid situations when new data is already in the dataset but the old data are not yet removed.

The primary use case for deleting data using ADD is to delete data during incremental load. ADD deletes data using the data loading API (see Delete Old Data while Loading New Data to a Dataset via API). To set up ADD to be able to delete data, update the structures in the output stage according to Naming Convention for Output Stage Objects.

Contents:

Deletion Modes

  • Deleting by the data load table
  • Deleting by the delete side table
  • Deleting using both the data load table and delete side table

Deleting by the Data Load Table

Add the x__deleted column to the data load table. The x__deleted column indicates whether a specific row should be deleted from the dataset when running data load.

Deleting by the data load table works in the following load modes:

  • Incremental load: The data is processed in the following way:
    • Data after a certain point in time (based on LSLTS, Last Successfully Loaded Time Stamp) and where x__deleted=FALSE is loaded to the datasets using the UPSERT method.
    • Data after a certain point in time (based on LSLTS) and where x__deleted=TRUE is deleted from the datasets.
  • Full load: All data where x__deleted=FALSE is loaded to the datasets. The datasets are completely rewritten.

Deleting by Connection Point

Imagine you have the following data in ADD:

Previously loaded data

cp_invoice_idr_customerf_totalpricex__timestampx__deleted
10033242017-12-01 0:00:00.000000FALSE
101781092017-12-01 0:00:00.000000FALSE

New incremental data update:

cp_invoice_idr_customerf_totalpricex__timestampx__deleted
1013NULL2017-12-02 0:00:00.000000
TRUE

“cp_invoice_id” is the connection point (CP) for the dataset.

Previously, you loaded data to the dataset by CP with “invoice_id”=“101”.

Now, you want to delete particular records from the dataset as a part of your data load. The data where “invoice_id”=”101” will be deleted from the dataset.

Deleting by Fact Table Grain

Imagine you have the following data in ADD:

Previously loaded data

a_ticket_ida_organizationf_timespentsecx__timestampx__deleted
12345ABC company4582017-12-01 0:00:00.000000FALSE
12346CDE company113422017-12-01 0:00:00.000000FALSE

New incremental data update:

a_ticket_ida_organizationf_timespentsecx__timestampx__deleted
12345ABC companyNULL2017-12-02 0:00:00.000000
TRUE

Fact Table Grain (FTG) consists of “a_ticket_id” and “a_organization” attributes.

Previously, you loaded data to the dataset by FTG with “ticket_id”=”12345” and “organization”=“ABC company”.

Now, you want to delete particular records from the dataset as a part of your data load. The data where “ticket_id”=”12345” AND “organization”=”ABC company” will be deleted from the dataset.

You do not have to explicitly specify FTG in the output stage table. When data load is running, the output stage table is compared to the logical data model, and FTG is automatically recognized.

Deleting by Attributes (no CP or FTG)

Imagine you have the following data in ADD:

Previously loaded data

a_brancha_organizationa_addressf_mrrx__timestampx__deleted
NYC

ABC company

5th avenue

20000

2017-12-01 0:00:00.000000

FALSE

LA

CDE company

3rd street100000

2017-12-01 0:00:00.000000

FALSE

New incremental data update:

a_brancha_organizationa_addressf_mrrx__timestampx__deleted
NYC

ABC company

5th avenue

20000

2017-12-02 0:00:00.000000

TRUE

If no CP or FTG exists, data is deleted by all attributes (facts are ignored). For data to be deleted, all attribute values must match.

Previously, you loaded data to the dataset, and now you want to delete particular records from the dataset as a part of your data load. The data where attribute “branch”=”NYC” AND “organization”=”ABC company” AND “address”=”5th avenue” will be deleted from the dataset.

Deleting by the Delete Side Table

Create a delete side table that describes columns by which you want delete data.

You do not have to specify all columns according to the definition of the dataset within your LDM. The delete side table lets you flexibly define criteria by which you can delete data (the delete vector).

The delete side table is an optional entity in the ADD output stage. The delete side table adheres to the same naming conventions as the data load table (see Naming Convention for Output Stage Objects). The delete side table works the same way as deleting by attributes using the data load table (no connection point or Fact Table Grain present) except the delete side table must not contain any fact.

Deleting by the delete side table works in the following load modes:

  • Incremental load: Data after a certain point in time (based on LSDTS, Last Successfully Deleted Time Stamp) is deleted from the datasets.
  • Interval or full load: The delete side table is skipped.

Imagine you have the following data in ADD:

Previously loaded data (data load table)

a_brancha_organizationa_addressx__timestamp
LACDE company3rd street2017-12-01 0:00:00.000000

New incremental data update (delete side table)

a_brancha_organizationx__timestamp
LACDE company
2017-12-02 0:00:00.000000

Previously, you loaded data to the dataset, and now you want to delete particular records from the dataset as a part of your data load. The data where “branch”=”LA” AND “organization”=”CDE company” will be deleted from the dataset.