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.

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.

All BigQuery lessons