Building a Pipeline

Three steps on a real file: keep the delivered orders, work out shipping as a share of each order, then total it up by country.

The file

I am using the ecommerce template, ecommerce.csv: 170 orders in 9 columns, loaded as a table called ecommerce. Six of the rows, with customer_id and subtotal left out for width:

Each step you apply becomes a card in the Pipeline panel on the left. A card is one SQL SELECT over the output of the card above it. The order matters, and every later card re-runs when you change an earlier one.

Step 1: Filter to delivered orders

1. Type filter in the Search transforms box and pick Filter.
2. Leave the action on Keep matching rows.
3. Add a condition: column status, operator is, value delivered.
4. Click Apply.

SELECT * FROM "ecommerce" WHERE "status" ILIKE 'delivered' ESCAPE '\'

ILIKE rather than =, because Match case is off by default, so a file with Delivered in some rows still matches. 38 rows survive. The card describes itself as keeping rows where "status" ILIKE 'delivered' ESCAPE '\'.

Had I chosen Remove matching rows, the WHERE would read NOT COALESCE(("status" ILIKE 'delivered' ESCAPE '\'), FALSE). That COALESCE is deliberate. A plain NOT is unknown wherever status is blank, and an unknown row passes neither filter, so blank rows would vanish from both halves.

Step 2: Add a shipping percentage

1. Search add column and pick Add Column.
2. New column name: shipping_pct. Column type: Number.
3. Expression: ROUND("shipping" / "total" * 100, 1).
4. Apply.

SELECT *, TRY_CAST(ROUND("shipping" / "total" * 100, 1) AS DOUBLE) AS "shipping_pct" FROM step_1

The TRY_CAST comes from the type you picked: a row the expression cannot turn into a number lands as empty instead of failing the step. DuckDB's / always divides as a double, even between two whole-number columns, so 7 / 2 is 3.5 here and not 3. The ROUND is mine; without it the first row reads 4.037107595460946.

Step 3: Group by country

1. Search group and pick Group & Aggregate.
2. Under Group by (optional) tick country.
3. Under Aggregations add three rows: COUNT of order_id, SUM of total, AVG of shipping_pct.
4. Apply.

SELECT "country", COUNT("order_id") AS "order_id_count", SUM("total") AS "total_sum", AVG("shipping_pct") AS "shipping_pct_avg" FROM step_2 GROUP BY "country"

Output names are the column plus the function, total_sum and so on, unless you type an alias. Only the grouped and aggregated columns come through. Every other column is gone, which is the point of the step.

Those long tails are what summing doubles does. 4777.360000000001 is right to the cent and wrong to the eye. I round money before exporting, either with a Math step on the new column or by writing ROUND(SUM("total"), 2) in a SQL Query step, and the same for the average. Round after the SUM, not before it: rounding each row first changes the total.

The pipeline as one query

Every card has a Show SQL toggle. On the card itself the FROM names an internal view for the step before, something like v_…_step_0. The Copy the whole pipeline as one SQL query button at the top of the panel repoints those into a chain of CTEs, with each card's description carried along as a comment, so the result runs anywhere DuckDB does:

WITH step_1 AS (
  -- keeping rows where "status" ILIKE 'delivered' ESCAPE '\'
  SELECT * FROM "ecommerce" WHERE "status" ILIKE 'delivered' ESCAPE '\'
),
step_2 AS (
  -- new column "shipping_pct" (numeric) = ROUND("shipping" / "total" * 100, 1)
  SELECT *, TRY_CAST(ROUND("shipping" / "total" * 100, 1) AS DOUBLE) AS "shipping_pct" FROM step_1
),
step_3 AS (
  -- count of order_id, sum of total +1 more by country
  SELECT "country", COUNT("order_id") AS "order_id_count", SUM("total") AS "total_sum", AVG("shipping_pct") AS "shipping_pct_avg" FROM step_2 GROUP BY "country"
)
SELECT * FROM step_3

Changing your mind

Hover a card for its controls. Edit step (the pencil) reopens the panel with the settings filled in. Delete step asks once. Move this step up and down reorder the chain, and Add a copy of this step right after it is how I try a variant without losing the original. Edit step 1 to keep returned instead of delivered and steps 2 and 3 recompute on their own.

Order can break a chain. Move the Group step above the Add Column step and it fails, because shipping_pct does not exist yet at that point. Pipelines are saved in your browser with the file, and each view has its own, which is how I keep the raw grid in one view and the country summary in another.