Great question! Migrating Oracle triggers to Redshift requires a shift in thinking since Redshift is a data warehouse optimized for analytics, not OLTP-style row-level triggers. Let’s walk through your options—direct Redshift-based approaches and managed alternatives like DynamoDB Streams.

Redshift doesn’t support native row-level triggers, but you can replicate similar behavior using these practical patterns:

Materialized Views for Automated Syncs #

If your trigger was used to keep related tables in sync (e.g., updating a summary table when a detail table changes), materialized views are your most straightforward bet. You can set them to refresh automatically on a schedule or on demand.

Example:

-- Create a materialized view that aggregates regional sales data
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales, COUNT(order_id) AS order_count
FROM sales
GROUP BY region;

-- Refresh the view to pick up recent changes (schedule this via Amazon EventBridge + Redshift Data API)
REFRESH MATERIALIZED VIEW sales_summary;

This works exceptionally well for read-heavy, summary-focused trigger logic.

Stored Procedures + Scheduled Queries #

For more complex logic (e.g., data validations, multi-table updates), wrap your trigger logic in a Redshift stored procedure and schedule it to run periodically.

Example stored procedure:

CREATE OR REPLACE PROCEDURE flag_invalid_customers()
AS $$
BEGIN
  -- Simulate BEFORE INSERT/UPDATE trigger logic: flag customers with invalid emails
  UPDATE customers
  SET status = 'INVALID'
  WHERE email NOT LIKE '%@%.%' AND status != 'INVALID';
  
  -- Log changes to an audit table (simulating AFTER trigger logic)
  INSERT INTO customer_audit (customer_id, change_type, change_timestamp)
  SELECT customer_id, 'FLAGGED_AS_INVALID', CURRENT_TIMESTAMP
  FROM customers
  WHERE status = 'INVALID' AND last_updated > CURRENT_TIMESTAMP - INTERVAL '1 hour';
END;
$$ LANGUAGE plpgsql;

Then use Amazon EventBridge to call the Redshift Data API and execute this procedure on a schedule (e.g., every 15 minutes) to catch recent changes.

Lambda UDFs + Event-Driven Triggers #

For near-real-time processing, you can tie Redshift events to Lambda functions:

• Configure COPY or UNLOAD commands to send event notifications to SNS/SQS, which triggers Lambda.
• Lambda can then run your trigger logic (e.g., validating data, syncing to other systems) and write processed results back to Redshift via the Data API.

If you prefer a fully managed, serverless approach, DynamoDB Streams can replicate trigger-like behavior—especially useful if your workflow involves syncing data between Redshift and other systems:

1. Sync Redshift data to DynamoDB: Use Redshift’s COPY command to export frequently changing data to DynamoDB, or use AWS Glue to replicate data on a schedule.
2. Enable DynamoDB Streams: Turn on streams for your DynamoDB table to capture all insert/update/delete events.
3. Process events with Lambda: Attach a Lambda function to the stream to run your original Oracle trigger logic (e.g., sending notifications, updating downstream systems).
4. Write back to Redshift: If needed, Lambda can use the Redshift Data API to push processed results back to your data warehouse.

This approach is ideal for low-maintenance, event-driven workflows where you don’t want to manage scheduling or heavy logic directly in Redshift.

• Batch vs. Row-Level: Redshift is built for batch processing, so avoid forcing row-by-row trigger logic. Opt for periodic or event-driven batch operations instead.
• Consistency: Unlike Oracle’s transactional triggers, Redshift’s approaches may have slight latency. Adjust your expectations for near-real-time vs. eventual consistency.
• Performance: Avoid running heavy logic directly on Redshift during peak hours. Offload complex processing to Lambda or DynamoDB Streams when possible.

内容的提问来源于stack exchange，提问作者Surajit Kundu