The route

Filter rows and write a CSV

Download the exercise files

Level 1.3 printed data; this one changes it and keeps the result. You'll put a SQL transform between the source and a CSV Write target, keep only the customers that matter to the sales team, and end with a file on disk.

Goal

A three-node pipeline — CSV Source → SQL → CSV Write — that writes the active customers on the pro plan to active_pro_customers.csv.

You'll need

  • Level 1.3 done. This level reuses its customers.csv, so there's nothing new to download.
  • A folder Odara can write to for the output. The screenshots use /tmp/odara-academy/out/.
  • About 15 minutes.

Steps

1. Start a new pipeline with the same source

Create a pipeline named w1-4-filter-to-csv. From INGEST ▸ Files, drop a CSV Source, name it customers and point it at customers.csv exactly as in level 1.3 — First row is header checked, Delimiter ,.

2. Add a SQL transform

From TRANSFORM ▸ Data Processing, drop a SQL node to the right of the source and name it active_pro. Connect customers → active_pro.

Click it. The query editor starts with SELECT * FROM input. Replace it with:

SELECT *
FROM input
WHERE plan = 'pro'
  AND active = true

The SQL node's Properties with the WHERE query

input is the table name for whatever arrives on the node's input — here, the 500 customer rows. Two things in that WHERE are worth noticing:

  • 'pro' is in single quotes because it's a text value. Double quotes mean a column name in SQL; "pro" would look for a column called pro.
  • active = true, with no quotes: Odara read the true/false column as a real boolean, so you compare it to a boolean. Write active = 'true' and the query won't even plan.

Click Preview Output on the SQL node. It runs the query against the source and shows Showing 10 of 121 rows.

3. Add a CSV Write target

From DELIVER ▸ Files, drop a CSV Write node, name it active_pro_csv and connect active_pro → active_pro_csv.

Field Value
Output Folder a folder you can write to
Filename active_pro_customers.csv
Include header row checked
Append to existing file unchecked
Delimiter Comma (,)

The CSV Write node's Properties

If the folder doesn't exist, Odara creates it. If the file is already there, a run replaces it — unless Append to existing file is checked, in which case every run adds another 121 rows to the end. Leave it off here.

4. Execute

Open Console and click Execute.

The three-node pipeline after a successful run

Read the per-node lines in the log:

customers: 500 rows
active_pro: 121 rows
active_pro_csv: 121 rows

This is the rows-in/rows-out habit from level 1.3 paying off. 500 went into the SQL node and 121 came out, so that's where the other 379 went — exactly as intended here. When a pipeline "loses" rows you didn't mean to lose, these lines tell you which node did it.

5. Open the file

Open active_pro_customers.csv in a text editor. It's a normal CSV that any other tool can read.

Expected result

  • The run completes with no failed node.
  • active_pro_customers.csv has 122 lines: the header and 121 customers.
  • The first customer is 1004, Grace Dias, from DE.
  • The column order is unchanged, from customer_id to active.

One difference from the input is worth knowing: monthly_spend is written as 99, not 99.00. Odara read it as a number and writes it back as one; the trailing zeros were only ever formatting.

Common pitfalls

Preview fails with Schema error: No field named pro. Valid fields are input.customer_id, … You wrote "pro" with double quotes, so SQL went looking for a column named pro. Use single quotes for text values. The error helpfully lists the columns that do exist.

Preview fails with Cannot infer common argument type for comparison operation Boolean = Utf8. You compared the boolean active column to the text 'true'. Drop the quotes.

The file has 0 rows but the run is green. The WHERE matches nothing — usually plan = 'Pro' with a capital P. Text comparison is case-sensitive. Preview the source and look at how the values are actually spelled.

The run is green but the file isn't where you expected. A typo in Output Folder doesn't fail — Odara creates the folder you typed and writes there. Copy the folder from the Properties panel and look for it.

You reach for a "Filter" node and can't find it. There isn't one in the palette: filtering is what WHERE is for, and the SQL node does it with the full expression power of SQL — ranges, IN (...), LIKE, combinations.

What you built

A pipeline that changes data and keeps the result: source, transform, target. Every pipeline in this course is that shape with more nodes. World 2 keeps the shape and changes what goes in — delimiters, encodings, Excel, XML, and files that don't say what they are.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. What happens if you run the pipeline twice with *Append to existing file* checked?