The nightly reconciliation
A nightly script has to leave several tables agreeing with each other, and it has to be right every night. This capstone asks for one, and nothing in it tells you how to write it.
How it works
The script runs three steps in a fixed order. New and corrected orders are merged into orders, old returns are deleted, and then order_totals is rebuilt from the orders as they now are. The last step reads orders, which the first step has changed.
MERGEpairs the source rows with the target rows through itsONcondition.WHEN MATCHEDacts on the target rows that have a partner, andWHEN NOT MATCHEDacts on the source rows that have none. A branch you leave out means that kind of row is skipped without an error.DELETEremoves the rows itsWHEREmatches, and withWHERE TRUEthat is every row. After a delete, look at the rows that are left. Counting the rows that should be gone cannot tell a scoped delete from an emptied table.CREATE OR REPLACE TABLE ... AS SELECTbuilds the table from the data as it is when the statement runs, and it is not refreshed afterwards. Put it after the statements that change the tables it reads, so it sees their result.- After a join, a column that both tables have must say which table it comes from.
ordersandorder_itemsboth have aquantitycolumn, and an unqualified one is refused as ambiguous. - Check runs your whole script on the original tables, then reads
orders,returnsandorder_totalswith one query and compares what it returns. This capstone has no byte budget, because what matters for a pipeline is the data it leaves behind.
Common mistake
A script can run without an error and still leave the wrong data. A MERGE with no WHEN MATCHED branch adds the new orders and leaves every changed status as it was.
Worked example
CREATE TEMP TABLE stock (item STRING, units INT64);
INSERT INTO stock (item, units) VALUES ('pens', 10), ('ink', 4);
MERGE stock AS t
USING (SELECT 'ink' AS item, 9 AS units UNION ALL SELECT 'tape', 6) AS s
ON t.item = s.item
WHEN MATCHED THEN UPDATE SET units = s.units
WHEN NOT MATCHED THEN INSERT (item, units) VALUES (s.item, s.units);
DELETE FROM stock WHERE units < 7;
SELECT item, units FROM stock ORDER BY item;The MERGE changes the units of ink and adds tape, which the table did not have.
The DELETE then removes tape, the only row under 7 units, so ink and pens are left.
The last query reads the table to show what the three statements left behind.