Loading patterns

Surprise: this course never taught you ETL, because you never needed it. The raw Daybreak tables land in the engine untouched, and every layer - staging, dimensions, facts - is a SQL transform running inside the warehouse. That is the whole ELT idea, and here it is in one breath:

CREATE OR REPLACE TABLE monthly_revenue AS
SELECT strftime(o.order_date, '%Y-%m') AS month,
       ROUND(SUM(oi.line_amount), 2) AS revenue
FROM stg_orders o
JOIN stg_order_items oi USING (order_id)
WHERE o.status = 'completed'
GROUP BY month;

SELECT * FROM monthly_revenue ORDER BY month;

• ETL wins when data must be cleaned or masked before it may land anywhere (strict PII regimes), or when the target engine is weak.
• ELT wins almost everywhere else: raw is preserved for audit and re-processing, transforms are versionable SQL, and the warehouse's own horsepower does the lifting.

Once a team runs dozens of in-warehouse SQL transforms, managing them by hand collapses. dbt is the tool that grew to fill that gap: each of your CTAS scripts becomes a "model" file, dbt figures out the dependency order, runs them, tests them (unique keys, no NULLs, accepted values), and generates docs and lineage automatically. The DeepLearning.AI course demos it; the mental model you need tonight is simply "dbt industrializes what I just did by hand." Depth belongs to a data-engineering course - here it is one card, on purpose.

• Watermark: the highest value already loaded - usually max(date_key) or a max updated-at timestamp. Tonight's extract asks the source only for rows past it.
• Late-arriving data: an order from Tuesday that reaches you Thursday. A naive date watermark skips it forever; a MERGE keyed on business keys absorbs it whenever it shows up. This is the main reason MERGE beats plain INSERT for incremental loads.
• Batch window: the hours (often overnight) when the load must finish before dashboards wake up. Full reloads eat the window as history grows; incremental loads keep it flat.

Picture the crash: tonight's batch INSERTs 500 of 1,000 rows, then the connection drops. Re-run the whole batch and the first 500 rows land twice - revenue double-counts, and worse, it double-counts silently. The pattern that survives the crash:

• Key every target row by its business identity - for fct_order_line, the pair (order_id, product_key) names one line exactly once.
• MERGE, not INSERT: rows already present get updated (or left alone), rows missing get inserted. Replaying a half-finished batch simply completes it.
• Same idea, coarser grain: delete-then-insert the affected date partition, or full reload. All three are idempotent; MERGE is the finest-grained.

Demo 2 does not ask you to trust this - you will run the same load twice and count the rows yourself.

Now be the naive pipeline: swap MERGE for plain INSERT and replay the batch. Two copies of order 1034, and its 36.00 counted as 72.00. This is the exact bug that idempotency exists to make impossible.

INSERT INTO fct_order_line VALUES
  (9, 2, DATE '2026-06-28', 1034, 2, 18.00, 36.00, 'completed', 'app');

INSERT INTO fct_order_line VALUES
  (9, 2, DATE '2026-06-28', 1034, 2, 18.00, 36.00, 'completed', 'app');

SELECT order_id,
       count(*) AS copies,
       SUM(line_revenue) AS revenue_counted
FROM fct_order_line
WHERE order_id = 1034
GROUP BY order_id;

Incremental extraction starts by asking the warehouse what it already has. max(date_key) is the high-water mark; the source is then asked only for rows past it. Tonight the answer is zero new orders - the warehouse is caught up. Tomorrow it will not be.

SELECT (SELECT max(date_key) FROM fct_order_line) AS high_water_mark,
       count(*) AS source_rows_past_watermark
FROM stg_orders
WHERE order_date > (SELECT max(date_key) FROM fct_order_line);

Caveat from Part 2: a date watermark alone misses late arrivals. Production incremental loads typically pair a generous watermark (for cheap extraction) with a keyed MERGE (for correct landing).