Distributed Data Quality and Integrity | Dataford Interview Questions

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

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

Schema

customer_master

Column	Type	Description
customer_idPK	INT	Canonical customer identifier
full_name	VARCHAR(100)	Canonical customer name
status	VARCHAR(20)	Canonical customer status

customer_source_records

Column	Type	Description
source_record_idPK	INT	Source record identifier
customer_id	INT	Customer identifier supplied by a source system
source_system	VARCHAR(30)	Originating source system
source_full_name	VARCHAR(100)	Customer name supplied by the source
source_status	VARCHAR(20)	Customer status supplied by the source

Your question is Distributed Data Quality and Integrity. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem