Distributed Data Quality and Integrity

How do you ensure data quality and integrity across distributed data sources?

Write a PostgreSQL query that compares Charles Schwab's customer master records with records received from multiple source systems. Identify missing records, duplicate source records, conflicting attributes, and incomplete values.

Output

1. One row per customer ID appearing in either table, including source-only records.
2. Columns: customer_id, master_name, source_names, source_statuses, issue_count, and issue_summary.
3. Include only records with at least one data-quality issue, ordered by customer_id ascending.

Output details

• Treat duplicate source records as any customer ID with more than one source row.
• Require at least two distinct source systems for source-system coverage; flag fewer when source records exist.
• Aggregate only distinct source names and statuses, sorting each ascending and separating values with ', '.
• Count disagreements when distinct source names or statuses conflict, or when the aggregated source value differs from the master value, only when both values are non-NULL.
• Flag every NULL source status as an issue, while SQL NULL values are omitted from aggregated name and status lists.
• Order issue descriptions as missing master, missing source, duplicate records, coverage, name differences, name disagreements, status differences, status disagreements, and NULL statuses, separated by '; '.