A simple data validation scenario using FULL OUTER JOIN & ORDER BY

Data Engineers do a lot of Data Analysis work, too.

For example: we need to understand why is there a difference between two approaches or data in two source systems.

I've previously shown how a FULL OUTER JOIN in SQL with simple validation and how you can ORDER BY an expression.

Let's look at a scenario where we can combine the two.

So we've got two source systems A and B, which in theory should provide the same sales figures, but they don't.

Understanding the difference between the two sources means identifying individual cases where the values are different, starting with the biggest discrepancies.

In the example below, we're going to order the results by the absolute value (ABS) of the difference between the figures in two systems, considering a missing value as 0.

This way, we can start our investigation from the biggest differences, regardless of which system shows 'bigger' values and also take into account missing values between the two.

SELECT
  department,
  sales_date,
  a.total_sales AS sales_a,
  b.total_sales AS sales_b,
  ABS(IFNULL(a.total_sales, 0) - IFNULL(b.total_sales, 0)) AS difference

FROM sales_system_a a
FULL OUTER JOIN sales_system_b b USING (department, sales_date)

ORDER BY ABS(IFNULL(sales_a, 0) - IFNULL(sales_b, 0)) DESC

---

Enjoyed this? Here are some related articles you might find useful:

• Self-joins in SQL
• Anti-joins in SQL
• SEMI-JOINS in SQL
• NON-EQUI joins in SQL