Build Compliant Marketing Data Pipelines

How would you approach building a robust data pipeline to ensure data quality, compliance, and privacy controls in marketing operations?

For the supplied marketing event data, write a PostgreSQL query that identifies records requiring quarantine before activation or reporting. Apply the stated validity, consent, campaign, duplicate, and suppression rules without exposing raw email addresses.

Output

1. One row per marketing event, ordered by event_date, then event_id.
2. Return event_id, event_date, user_id, channel, email_is_valid, outcome, and issue_codes.
3. Include all events. outcome must be eligible or quarantine; use none when no issue exists.

Output details

• Sort by event_date ascending, then event_id ascending.
• Derive event_date from event_time; consider trimmed nonblank emails valid only when matching the specified single-@, domain-dot pattern.
• Mark later events sharing user_id, event_date, and channel as duplicate_event, retaining the earliest event_time, then event_id.
• Use the latest consent granted by the event time, requiring consent_status granted and either no revocation or an event before revoked_at.
• Treat campaign validity as active_from inclusive and active_to exclusive, with NULL active_to meaning ongoing; apply the same bounds to suppressions.
• Emit issue codes in this order: duplicate_event, invalid_email, campaign_not_eligible, consent_missing_or_invalid, suppression_active, comma-separated, or none.