Consolidate Disparate Data Sources in SQL

Context

You are given multiple source extracts that do not share the same structure, naming, or completeness. In a financial reporting workflow, you need to consolidate them into one reliable dataset before analysis.

Core question

Walk through how you would use SQL to consolidate data from multiple disparate sources. In your answer, explain how you would standardize fields, align keys, handle missing values, resolve duplicates, and validate that the merged output is accurate.

Scope guidance

Keep your answer practical and specific to SQL-based consolidation. Focus on the sequence you would follow, the join and null-handling choices you would make, and how you would check for mismatches or data quality issues after combining the sources.

Source Standardization

Before joining anything, you normalize column names, data types, date formats, and key fields so the sources can be compared consistently. This often means trimming strings, casting types, and creating a common business key.

SELECT
  TRIM(UPPER(account_id)) AS account_id,
  CAST(report_date AS DATE) AS report_date
FROM source_a;

Join Strategy

The join type depends on whether you want to preserve all records from a master source or only matched records across systems. In consolidation tasks, LEFT JOIN is often used to keep the primary population while attaching optional attributes from other sources.

SELECT a.account_id, a.amount, b.region
FROM source_a a
LEFT JOIN source_b b
  ON a.account_id = b.account_id;

Null Handling

Disparate sources often have missing values in different places, so you use COALESCE to choose the best available value across columns. This is especially useful when one system has a populated field and another has a fallback value.

SELECT
  account_id,
  COALESCE(primary_email, backup_email) AS email
FROM consolidated_view;

Deduplication and Reconciliation

When the same entity appears multiple times, you need a rule to keep one row or aggregate duplicates. You can use window functions, GROUP BY, or source-priority logic to make the final dataset deterministic and auditable.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY updated_at DESC) AS rn
  FROM staged_data
) t
WHERE rn = 1;