Architecture and the staging layer

Staging looks boring - it is mostly SELECT with a few CASTs. Its value is the four promises it makes to every layer above it:

• Typed: text dates become DATE, so month grouping and date math stop being string tricks. The source's mess ends here.
• Renamed: app-developer names become analyst names, and computed basics (like line_amount) get calculated once, in one place.
• Quality-gated: every load passes row-count, uniqueness, null, and freshness checks before anything downstream sees it. Part 3 builds all four.
• Replayable: staging is derived entirely from source by a script. Drop it, re-run it, get the same thing. No hand-edited table ever earns trust.

In this course staging is a set of tables inside DuckDB. In production the idea is identical, only the address changes:

• Cloud warehouses: a staging schema (or database) in Snowflake / BigQuery / Redshift, loaded by an ingestion tool, rebuilt or appended on a schedule.
• dbt shops: staging is literally a folder of models named stg_* - one per source table, CAST and rename only. The convention you learn tonight is the industry convention.
• Lakehouse stacks: the same layer wears medallion names - "bronze" is roughly raw-landed, "silver" is roughly staged. Session b9 connects the vocabularies.

In b1, typeof(order_date) exposed VARCHAR dates in the source. Fix it, then run the same proof against the staged copy:

CREATE TABLE stg_orders AS
SELECT order_id, customer_id,
       CAST(order_date AS DATE) AS order_date,
       status, channel
FROM orders;

SELECT order_id, order_date,
       typeof(order_date) AS stored_as
FROM stg_orders
LIMIT 4;

VARCHAR became DATE. From here on, strftime(order_date, '%Y-%m'), date arithmetic, and calendar joins all just work - because staging paid the tax once.

Two habits that make a staging layer readable by strangers:

• The stg_ prefix, one source table each: orders becomes stg_orders, nothing merged, nothing skipped. Anyone can trace any staged column back to exactly one source column.
• TRY_CAST when the source is dirty: CAST fails the whole load on one bad value; TRY_CAST turns the bad value into NULL and lets the load finish - then your null scan (Part 3) reports how many landed.

SELECT CAST('2026-06-22' AS DATE)     AS clean_date,
       TRY_CAST('oops' AS DATE)       AS junk_becomes_null,
       TRY_CAST('2026-13-99' AS DATE) AS impossible_date;

This playground starts with the staging layer already built (data-setup="staging"), so you can play inspector. One script, four gates, one PASS/FAIL summary:

SELECT '1 row counts reconcile' AS gate,
       CASE WHEN (SELECT count(*) FROM orders)
               = (SELECT count(*) FROM stg_orders)
            THEN 'PASS' ELSE 'FAIL' END AS result
UNION ALL
SELECT '2 order_id unique',
       CASE WHEN (SELECT count(*) FROM stg_orders)
               = (SELECT count(DISTINCT order_id) FROM stg_orders)
            THEN 'PASS' ELSE 'FAIL' END
UNION ALL
SELECT '3 no NULL order dates',
       CASE WHEN (SELECT count(*) FROM stg_orders
                  WHERE order_date IS NULL) = 0
            THEN 'PASS' ELSE 'FAIL' END
UNION ALL
SELECT '4 data is fresh',
       CASE WHEN (SELECT max(order_date) FROM stg_orders)
               >= DATE '2026-06-01'
            THEN 'PASS' ELSE 'FAIL' END;

Gate 1 catches lost or doubled batches. Gate 2 catches duplicate loads and broken joins-to-be. Gate 3 catches the NULLs that TRY_CAST quietly minted. Gate 4 catches the silent scheduler that stopped running last Tuesday.

Subscriptions carry TWO text dates, and cancel_date is NULL for active subscribers - CAST handles NULL just fine. Build stg_subscriptions and prove the types:

CREATE TABLE stg_subscriptions AS
SELECT sub_id, customer_id, product_id,
       CAST(start_date AS DATE)  AS start_date,
       CAST(cancel_date AS DATE) AS cancel_date,
       monthly_qty
FROM subscriptions;

SELECT sub_id, start_date,
       typeof(start_date) AS stored_as,
       cancel_date
FROM stg_subscriptions
ORDER BY sub_id;

A data contract says: "orders.status is always one of completed, refunded, cancelled." Write the query that finds violations - an empty result means the contract holds, and that emptiness is the answer you want:

SELECT order_id, order_date, status
FROM orders
WHERE status NOT IN ('completed', 'refunded', 'cancelled');

GROUP BY the key, HAVING count above one - the four-line pattern that catches double loads, bad merges, and fan-out joins for the rest of your career. Empty result = clean:

SELECT order_id, count(*) AS copies
FROM orders
GROUP BY order_id
HAVING count(*) > 1;

Where it saves you: someone re-runs yesterday's load script by hand, every order now exists twice, and every revenue number silently doubles. Gate 2 plus this pattern is how you find out in minutes, not months.