Use CTEs as readable quality gates — Smart Study Module 10

Reviewers need to follow how raw payments become a trusted KPI table.

Outcomes

• Name intermediate grains
• Detect orphan and duplicate keys
• Keep transformation and reconciliation readable

Understand

A common table expression gives a query step a name; it is not automatically a persisted table or a performance optimization. Name steps after their grain, such as jan_by_loan.

Quality queries should find duplicate payment IDs, payments with no valid loan and loans with no branch. A clean output is not proof that the checks ran: capture the exception count with the result.

Use separate controls for amount, count and population. A duplicated and missing row can cancel numerically, so a matching sum alone is weak evidence.

Follow along

1. Run duplicate payment_id checks using GROUP BY and HAVING.
2. Run an orphan-loan LEFT JOIN check.
3. Inject a bad key only in a disposable copy and confirm it is detected.
4. Document the transformation grain at each CTE.

Worked example

SELECT payment_id,COUNT(*) FROM payments GROUP BY payment_id HAVING COUNT(*) > 1;
SELECT p.payment_id FROM payments p LEFT JOIN loans l ON l.loan_id=p.loan_id WHERE l.loan_id IS NULL;

The supplied clean dataset has zero duplicate IDs and zero orphan payment keys.

Practice

How many orphan payment records exist in the supplied clean kit?

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.