Check a table's structure

-- what arrived: ids and amounts as text, no order_date, and a column nobody asked for
CREATE TEMP TABLE arrived AS SELECT * FROM (VALUES
  ('1', 'Alice Ng', '120.5', 'first'), ('2', 'Bob Okafor', 'n/a', 'second')) t(order_id, customer, amount, note);
CREATE TEMP TABLE expected_structure AS SELECT * FROM (VALUES
  ('order_id', 'BIGINT'), ('customer', 'VARCHAR'), ('amount', 'DOUBLE'), ('order_date', 'DATE')) t(column_name, column_type);
-- read the structure from DESCRIBE, then compare it with the expectation in both directions
CREATE TEMP TABLE problems AS
SELECT coalesce(e.column_name, a.column_name) AS column_name,
       CASE WHEN a.column_name IS NULL THEN 'missing'
            WHEN e.column_name IS NULL THEN 'unexpected'
            ELSE 'wrong type: ' || a.column_type END AS problem
FROM expected_structure e FULL OUTER JOIN (SELECT column_name, column_type FROM (DESCRIBE arrived)) a USING (column_name)
WHERE a.column_name IS NULL OR e.column_name IS NULL OR a.column_type <> e.column_type;
-- conform: cast to the expected types (a value that does not fit becomes NULL), add the missing column, drop the extra one
CREATE TEMP TABLE conformed AS
SELECT TRY_CAST(order_id AS BIGINT) AS order_id, customer, TRY_CAST(amount AS DOUBLE) AS amount, CAST(NULL AS DATE) AS order_date FROM arrived;
CREATE TEMP TABLE problems_after AS
SELECT coalesce(e.column_name, a.column_name) AS column_name FROM expected_structure e
FULL OUTER JOIN (SELECT column_name, column_type FROM (DESCRIBE conformed)) a USING (column_name)
WHERE a.column_name IS NULL OR e.column_name IS NULL OR a.column_type <> e.column_type;
SELECT * FROM problems ORDER BY column_name;
SELECT * FROM conformed ORDER BY order_id;
SELECT CASE WHEN ((SELECT list((column_name, problem) ORDER BY column_name) FROM problems) = [('amount', 'wrong type: VARCHAR'), ('note', 'unexpected'), ('order_date', 'missing'), ('order_id', 'wrong type: VARCHAR')] AND (SELECT count(*) FROM problems_after) = 0 AND (SELECT list(column_name) FROM (DESCRIBE conformed)) = ['order_id', 'customer', 'amount', 'order_date'] AND (SELECT list((order_id, amount) ORDER BY order_id) FROM conformed) IS NOT DISTINCT FROM [(1, 120.5), (2, NULL)]) THEN 'as expected' ELSE error('the arrived table should have four structure problems, and the conformed table none, with the amount n/a turned to NULL') END AS verdict;