Delete DB table

Delete rows from STG_SALES_DATA using transformation-driven criteria.

Workshop - Delete DB table

Delete rows from STG_SALES_DATA based on criteria in a stream. This workshop uses a product line list and a minimum quantity threshold.

Use this step when your delete logic is driven by transformation output. Use Execute SQL script for simple deletes.

What you’ll do

• Inspect the target table before you delete

• Build a delete criteria stream (product line + quantity)

• Delete matching rows with Delete

• Validate results with SQL

Prerequisites

• STG_SALES_DATA exists. Create it in Create DB table.

• A working database connection. See Database Connections.

Estimated time: 20 minutes

---

Workshop files

Create a file named productlines.csv. Save it in the same folder as your transformation.

This workshop assumes a single-column file with no header row:

Classic Cars
Motorcycles
Planes
Ships
Trains
Trucks and Buses
Vintage Cars

Back up your table before you run deletes. You can copy the table, or use a database snapshot.

95B

productline.txt

Open

30KB

tr_delete.ktr

Open

---

Delete

Create a new transformation

Use any of these options to open a new transformation tab:

• Select File > New > Transformation

• Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

---

1. Inspect data

Inspect the data

Inspect STG_SALES_DATA before you delete. You need a baseline to validate the change.

1. In your database tool, view STG_SALES_DATA.

STG_SALES_DATA

You will filter deletes using PRODUCTLINE and QUANTITYORDERED.

1. Run a quick check for high-quantity rows:

select * from STG_SALES_DATA
where QUANTITYORDERED > 50;

STG_SALES_DATA constraint QUANTITYORDERED > 50

2. CSV File input

3. Parameters

4. Get Variables

5. Delete

6. Run and validate

Troubleshooting

Nothing was deleted Your criteria did not match any rows. Confirm your comparator and data types.

Too many rows deleted Your comparator is too broad, or you mapped the wrong field names.

Delete step fails with type conversion errors Cast min_quantityordered to a number with Select values.