Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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

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.

productline.txt
95B
Open
tr_delete.ktr
30KB
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)

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:

STG_SALES_DATA constraint QUANTITYORDERED > 50

CSV File input

Read productlines.csv. Each row is one PRODUCTLINE value.

  1. Start Spoon.

  1. Drag the CSV File Input step onto the canvas.

  2. Open the CSV File Input properties dialog box.

  3. Configure it to read: ${Internal.Transformation.Filename.Directory}/productlines.csv

  4. Select Get Fields.

CSV File input - PRODUCTLINE list

Transformation parameters

Use a parameter for your minimum quantity threshold. This keeps your transformation easy to reuse.

  1. Double-click on the canvas and select the Parameter tab.

  2. Create a parameter named min_quantityordered.

  3. Set a default value (for example 50).

Set parameters

Get variables

Bring min_quantityordered into the stream so the Delete step can use it.

  1. Drag the Get variables step onto the canvas.

  2. Open the Get variables properties dialog box.

  3. Configure the step to output a field for ${min_quantityordered}.

Get variables

Delete

Delete is a terminal step. It does not pass rows downstream. It builds DELETE statements from the input stream.

Be careful with the comparators in this step. Always validate your criteria before you run.

  1. Drag the Delete step onto the canvas.

  2. Open the Delete properties dialog box.

  3. Configure the database Connection and set Table name to STG_SALES_DATA.

  4. Map stream fields to table fields for your delete criteria.

Delete step

This workshop uses criteria based on:

  • QUANTITYORDERED and the min_quantityordered value

  • PRODUCTLINE values from productlines.csv

Run and validate

Run the transformation, then validate the row counts and sample rows.

Re-run your baseline queries from the first tab. Confirm the results match your delete criteria.

  1. Select Run in Spoon.

  2. In your database tool, inspect STG_SALES_DATA.

STG_SALES_DATA
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.

PreviousInsert / Update DB
NextSCDs

Last updated

Was this helpful?