Capstone: Check It in Python

Your star is built, and a report will read it next. But a pipeline can be wrong without crashing: an order loaded twice, a customer the dimension doesn't know, an order lost on the way. The report would add it all up, and nobody would see an error. A data quality check catches this: a query that counts the rows breaking one rule. 0 means PASS; any other number is a FAIL. At work, checks like these run after every load, before anyone reads the tables.

The function check(rule, query) is already written. It runs the query, prints rule: PASS or rule: FAIL (n), and returns 1 for a FAIL, so run_checks can add up the failures in failed. A check for another rule, no negative amounts, would look like this:

query = (
    "SELECT COUNT(*) "
    "FROM fact_orders "
    "WHERE amount < 0 "
)
failed += check("no negative amount", query)

Three SQL pieces are new. COUNT(DISTINCT x) counts the different values of x, so rows minus different ids is the number of extra copies. x NOT IN (SELECT …) keeps the rows whose value the inner query doesn't return. A subquery in brackets, like (SELECT COUNT(*) FROM dim_product), is one number you can add or subtract. Every order that arrived (75 different ids in the 78 rows of raw_orders) ends up in fact_orders (73 rows) or in rejected_orders (2 rows), so fact + rejected − arrived must be 0.

A FAIL line is easy to miss in a log, so the run must stop. raise ValueError("message") stops the program at once with that error: the report after run_checks() never runs, and the scheduler marks the run as failed. Your code prints four PASS lines. Then the check breaks the star on purpose, twice, and prints the error each time:

fact keys unique: PASS
no missing customer: PASS
every product_key in dim_product: PASS
source and fact totals agree: PASS
The check takes Pen out of dim_product and calls run_checks() again:
fact keys unique: PASS
no missing customer: PASS
every product_key in dim_product: FAIL (9)
source and fact totals agree: PASS
Run stopped: quality checks failed: 1
Then order 5001 twice in the fact, and the customer of order 5002 gone:
fact keys unique: FAIL (1)
no missing customer: FAIL (1)
every product_key in dim_product: FAIL (9)
source and fact totals agree: FAIL (1)
Run stopped: quality checks failed: 4

Your task: complete the four queries in run_checks so each one counts the rows breaking its rule: extra copies of an order in fact_orders; fact rows whose customer_id isn't in dim_customer; fact rows whose product_key isn't in dim_product; and fact rows + rejected rows − the different order ids of raw_orders. Then, when failed is more than 0, raise a ValueError whose message is "quality checks failed: " followed by the number of failed checks.

Assignment
Follow the Coding Tutorial to finish the four quality checks in run_checks and stop the run when one of them fails.

Hint
Each query must return 0 on a healthy star. In the fourth one, subtract the different order ids, not the 78 rows: three orders arrived twice, and staging keeps one row of each. The raise goes inside the if, with str(failed) at the end of its message.