PostgreSQL Transaction Data Quality Audit

Sigmoid's transaction pipelines need a repeatable SQL audit before data is loaded into downstream analytics surfaces. Write a PostgreSQL query that returns every transaction with one or more data quality issues.

Requirements

1. Flag missing customer_id, merchant_id, amount, currency, or status, treating empty customer IDs as missing.
2. Flag duplicate business keys defined by (merchant_id, external_transaction_id).
3. Flag non-positive amounts, transactions after 2025-01-31, and transaction rows whose merchant does not exist.
4. Return one row per affected transaction with a comma-separated, alphabetically ordered list of issue types, sorted by transaction_id.

Output details

• Return columns in this order: transaction_id, external_transaction_id, and issue_types.
• Use exactly these issue labels: missing_customer_id, missing_merchant_id, missing_amount, missing_currency, missing_status, duplicate_business_key, non_positive_amount, transaction_date_after_cutoff, and unknown_merchant.
• Treat customer IDs containing only whitespace as missing after trimming surrounding whitespace.
• Flag unknown_merchant only when merchant_id is non-NULL and no matching merchant exists.