This example demonstrates how to use a Mapper Snap to pass key-value pairs to the Redshift - Delete Snap, enabling conditional deletion of records based on expressions. This approach is useful when you need to delete specific records based on dynamic criteria passed from upstream Snaps.
The pipeline uses a Mapper Snap to create key-value pairs that are passed to the Delete Snap, which uses these values in expressions for conditional deletion.
- A valid Redshift account
- A Redshift table with existing records
- Understanding of SnapLogic expressions and pipeline parameters
- Appropriate permissions to delete records from the target table
-
Configure the Mapper Snap.
Create mappings to pass values to the Delete Snap. For example, to delete records where the ID is greater than a specific value:
- Target path:
$id
- Mapping expression:
100 (or use a pipeline parameter like $_id)
-
Configure the Redshift - Delete Snap.
Set the following properties:
- Schema name: Specify the database schema (for example,
public).
- Table name: Specify the table name (for example,
orders).
- Delete condition: Use an expression to reference the mapped value. For example,
id > $id to delete records where the ID is greater than the value passed from the Mapper.
Additional expression examples:
status = $status - Delete records matching a specific status"customer_id = " + $customer_id + " AND order_date < '" + $date + "'" - Delete records using multiple conditions
Warning: Using expressions that concatenate strings to create SQL queries has a potential SQL injection risk. Ensure you understand all implications and risks before using string concatenation with expressions enabled.
-
Execute the pipeline.
When the pipeline runs, the Mapper passes the specified values to the Delete Snap, which uses them in the WHERE clause to delete only the matching records.
The Snap successfully deletes records matching the specified condition. The output shows:
status: success- The number of records deleted based on the WHERE clause condition
Note: This approach allows for dynamic deletion criteria, making your pipelines more flexible and reusable across different execution contexts.
