Ingestion patterns

The other big ingestion decision is when you transform. The letters are the order of operations:

• ETL (extract, transform, load): reshape the data before it lands in the destination. The old default, from when storage was expensive and you cleaned data to save space.
• ELT (extract, load, transform): land the raw data first, transform it later, inside the destination. The modern default - cloud storage is cheap, so you keep the raw copy and reshape on demand.

This whole track is ELT-shaped: b2-b4 land raw data, b5 and b8 transform it after it lands. Keeping the raw copy means you can re-transform any time without re-extracting from the source.

Full reload feels harmless when a table has a thousand rows. The problem is that its cost grows with the total size of the source, not the amount of new data. A table that gains 1% new rows a day still gets copied 100% every night under full reload. After a year that is 365 full copies of an ever-larger table to capture a trickle of change. Incremental flips the cost to scale with change, not history - which is why every high-volume fact table eventually moves to it.

Pretend your last run stopped at a watermark date. This run pulls only orders after it. Change the date and re-run to see the "new rows" count move. Press ▶ Run (first run caches the ~8 MB engine).

-- watermark = the max order_date we saw last run
SELECT count(*) AS new_rows_since_watermark
FROM orders
WHERE order_date > '2026-04-01';

A naive watermark assumes rows always arrive in order. Reality disagrees. A mobile app buffers events offline and syncs a two-day-old order tomorrow; a batch job backfills yesterday's records at noon. If your watermark jumps straight to "now", those late rows arrive behind the mark and never get pulled - silent data loss.

Extract one month of orders to a file named for that window. Re-running writes the same file, so the window is idempotent - run it once or ten times, the landed result is identical.

-- the March 2026 window, landed as its own dated file
COPY (SELECT * FROM orders
      WHERE order_date >= '2026-03-01'
        AND order_date < '2026-04-01')
TO 'windows/orders_2026-03.parquet' (FORMAT PARQUET);

SELECT count(*) AS rows_in_march_window
FROM 'windows/orders_2026-03.parquet';

Pick a different watermark date and count only the orders after it. Try a few dates and watch the "new rows" number respond.

SELECT count(*) AS new_since_watermark
FROM orders
WHERE order_date > '2026-05-01';

How many orders would a single daily window carry, versus a full month? One query shows both, so you can feel the window-size trade-off.

SELECT
  count(*) FILTER (WHERE order_date = '2026-03-15')            AS one_day,
  count(*) FILTER (WHERE order_date >= '2026-03-01'
                     AND order_date < '2026-04-01')            AS one_month
FROM orders;

A thought exercise, no SQL. Given a table, how do you decide? The rule of thumb:

• Small dimension table (customers, products): full reload. A few hundred or thousand rows are cheap to re-copy, and you dodge all the watermark bookkeeping. Simpler is better when the table is small.
• Huge, append-heavy fact table (orders, events, clicks): incremental. The table only grows, so full reload's cost climbs forever while incremental stays flat at "today's new rows".
• The tie-breaker: if the source has a reliable, indexed timestamp or id to watermark on, incremental is easy. If it does not, full reload may be the only correct option until the source adds one.