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.
event_date, then event_id.event_id, event_date, user_id, channel, email_is_valid, outcome, and issue_codes.outcome must be eligible or quarantine; use none when no issue exists.| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique marketing event identifier |
| user_id | VARCHAR(20) | Pseudonymous user identifier |
| event_time | TIMESTAMP | Timestamp when the marketing event occurred |
| channel | VARCHAR(20) | Marketing activation channel |
| campaign_id | VARCHAR(20) | Campaign associated with the event |
| VARCHAR(255) | Captured email value, used only for validation |
| Column | Type | Description |
|---|---|---|
| consent_idPK | INT | Unique consent record identifier |
| user_id | VARCHAR(20) | Pseudonymous user identifier |
| consent_status | VARCHAR(20) | Consent state at grant time |
| granted_at | TIMESTAMP | Time the consent record became effective |
| revoked_at | TIMESTAMP | Time consent was revoked |
| Column | Type | Description |
|---|---|---|
| campaign_idPK | VARCHAR(20) | Registered campaign identifier |
| campaign_name | VARCHAR(100) | Campaign display name |
| active_from | TIMESTAMP | Campaign activation timestamp |
| active_to | TIMESTAMP | Campaign deactivation timestamp |
| Column | Type | Description |
|---|---|---|
| suppression_idPK | INT | Unique suppression record identifier |
| user_id | VARCHAR(20) | Pseudonymous user identifier |
| channel | VARCHAR(20) | Suppressed channel, or all channels when null |
| starts_at | TIMESTAMP | Suppression start timestamp |
| ends_at | TIMESTAMP | Suppression end timestamp |