Drill 5 of 12 in Data engineering questions
0

Interview Question Bank · Aggregate first · FULL OUTER JOIN · COALESCE

5. Source vs warehouse row counts

MediumData engineeringQuestion #245

Concept: Aggregate first · FULL OUTER JOIN · COALESCEshow

To reconcile two systems, count each side per day on its own, then join the two summaries; joining raw rows first multiplies them. A FULL OUTER JOIN keeps days that exist on only one side, and COALESCE turns the missing side's count into 0 and picks whichever date is present.

Example (different data):

WITH a AS (SELECT sold_on, COUNT(*) AS n FROM pos_sales GROUP BY sold_on),
     b AS (SELECT sold_on, COUNT(*) AS n FROM ledger_sales GROUP BY sold_on)
SELECT COALESCE(a.sold_on, b.sold_on) AS day,
  COALESCE(a.n, 0) AS pos, COALESCE(b.n, 0) AS ledger
FROM a FULL OUTER JOIN b ON b.sold_on = a.sold_on;

Your task

Fernhill copies shipments from the operational database (de_src_shipments) into the warehouse (de_wh_shipments) every night. An auditor wants daily proof that both sides hold the same number of rows.

For every day with shipments on either side (by created_at date), return business_date, source_rows, target_rows (count every warehouse row, duplicates included), row_diff (target_rows - source_rows) and status: 'ok' when equal, 'missing' when the warehouse has fewer, 'extra' when it has more. Sort by business_date.

Starting Postgres in your browser…

Checking your access…