Batch transformation

ETL transforms data before loading it - on a separate processing box, so only clean data ever lands. That made sense when warehouse compute was scarce and expensive. ELT loads the raw data first, then transforms it in place using the warehouse engine's own power. Today ELT is the default, because the engine (Snowflake, BigQuery, DuckDB) is now the most powerful, cheapest-to-scale compute you have.

• ELT keeps the raw copy: load first means you always have the untouched source to re-transform when requirements change. ETL throws the raw away.
• The engine is the muscle: pushing transforms into a columnar engine beats hand-rolled row-by-row processing on a middle box. Let the database do what it is best at.
• DuckDB is doing ELT right now: every playground on this page loads Daybreak's raw source, then transforms in the engine. That is ELT in miniature.

ELT is the default, not a law. ETL-before-load still wins in a few spots: when you legally must not land raw PII (mask it before it touches the warehouse), when the source volume is so huge that loading everything is wasteful (filter first), or when a lightweight streaming transform is cheaper mid-flight than a batch pass later. The instinct that matters: default to ELT, reach for pre-load transformation only when a real constraint forces it.

This transform reads raw orders, casts the text date to a real DATE, filters to completed orders, and aggregates to a daily grain - all with CREATE OR REPLACE. Press ▶ Run. Then press it again. The row count does not move, because the table is rebuilt from source every time, not appended to. That is idempotency.

CREATE OR REPLACE TABLE daily_orders AS
SELECT CAST(order_date AS DATE)                       AS day,
       count(*)                                       AS orders,
       count(*) FILTER (WHERE status = 'completed')   AS completed,
       count(*) FILTER (WHERE status = 'refunded')    AS refunded
FROM orders
GROUP BY day;

SELECT count(*) AS rows_in_table, sum(orders) AS orders_total
FROM daily_orders;

Two transform moves you will reach for constantly:

• Derived columns: a transform rarely just copies - it computes. CAST(order_date AS DATE) fixes the text-date sin from the source. quantity * unit_price AS line_amount derives revenue that the source never stored. Deriving in the transform means every consumer downstream sees the same clean value.
• FILTER aggregates: count(*) FILTER (WHERE ...) and sum(x) FILTER (WHERE ...) let one aggregate query answer several questions at once - completed vs refunded vs total in a single scan. It is faster and clearer than joining three sub-queries.

Here is Demo 1's daily aggregate expressed in PySpark - the distributed engine you would use if Daybreak had billions of rows instead of thirty-three. The logic is identical; only the engine and the scale change. This snippet cannot run in a browser - Spark needs a cluster (or at least a local JVM) - so read it, do not run it.

Note mode("overwrite") - that is Spark's way of staying idempotent, exactly like CREATE OR REPLACE in DuckDB. The discipline travels across engines; the syntax does not.

This transform appends with INSERT instead of rebuilding. To simulate a pipeline that crashed and got retried, the same load fires twice. Run it and read row_copies: every day appears twice, so any revenue sum off this table is doubled. That is the bug idempotency prevents.

-- NON-idempotent: an empty shell we append into
CREATE TABLE daily_orders (day DATE, orders INTEGER);

-- First load
INSERT INTO daily_orders
SELECT CAST(order_date AS DATE), count(*)
FROM orders WHERE status = 'completed' GROUP BY 1;

-- The pipeline crashed and got re-run - the SAME insert fires again
INSERT INTO daily_orders
SELECT CAST(order_date AS DATE), count(*)
FROM orders WHERE status = 'completed' GROUP BY 1;

-- Every day is now duplicated - revenue would double-count
SELECT day, count(*) AS row_copies
FROM daily_orders GROUP BY day ORDER BY day LIMIT 8;

No SQL - reason it through. You leave a single-node engine like DuckDB for a distributed one like Spark when the data no longer fits or no longer chews on one machine: roughly, when a single transform's input pushes past what one big box can hold in memory plus spill (call it the ~100 GB-and-up zone, though hardware keeps moving the line). Below that, a cluster is pure overhead - more ops, more cost, more moving parts, and usually slower for small data because of coordination. The honest default: start on one node, prove you have outgrown it with a real measurement, and only then distribute. Most Daybreak-sized workloads never leave the laptop. Distributed processing is exactly what b8 builds.