The route

Loop over data: one file per country

Download the exercise files

Iterate doesn't care where its rows come from. In 5.2 they were files; here they're rows of a small CSV that lists the countries you want to export. The list drives the loop: add a line, and next run there's one more file. Nothing else changes.

Goal

A pipeline that reads countries.csv and writes one customer file per country into a folder per region: iberia/customers_PT.csv, latam/customers_BR.csv, and so on — 5 files.

You'll need

  • The exercise files for this level: countries.csv (5 countries and their region) and customers.csv.
  • About 20 minutes.

Steps

1. The driver

Create a pipeline w5-3-per-country. Add a CSV Source countries and connect it to an Iterate (the edge turns dashed). Set the Iterate's Variable Prefix to c.

With a prefix, the columns become @{c.country} and @{c.region} instead of @{country} and @{region}. It costs nothing, and it removes any doubt about which loop a variable belongs to when pipelines grow — or when a column name happens to match one of your context variables.

2. The work, once per country

After the Iterate:

  1. A CSV Source customers.

  2. A SQL node only_this_country:

    SELECT *
    FROM input
    WHERE country = '@{c.country}'
    
  3. A CSV Write per_country with Output Folder …/by_country/@{c.region} and Filename customers_@{c.country}.csv.

Variables from the driver row in the SQL and in the path

Variables work anywhere a path or a query does — including the folder part, which is how the regions become folders.

3. Execute

Execute, then look in by_country/.

Expected result

File Rows
iberia/customers_ES.csv 28
iberia/customers_PT.csv 36
latam/customers_AR.csv 29
latam/customers_BR.csv 37
latam/customers_MX.csv 41

Add NL,benelux as a new line in countries.csv and run again: benelux/customers_NL.csv appears with 42 customers. No change to the pipeline.

Common pitfalls

Every file contains all 500 customers. The WHERE is missing or doesn't use the variable.

The loop stops with Schema error: No field named pt. The variable is in the query without quotes — WHERE country = @{c.country} — so after substitution SQL sees country = PT and looks for a column called pt. Text values need single quotes: '@{c.country}'.

'per_country': variable @{country} is not defined. The prefix is set to c, so the variable is @{c.country}. An unknown variable in an output path stops the run instead of writing a file with @{…} in its name.

The customers source is read once per country. Yes — everything after the Iterate runs every round, sources included. With five countries and a small file that's fine; with a big table, filter at the source (a database query with the variable in its WHERE, World 4) rather than reading everything each time.

What you built

A configuration-driven pipeline: the what lives in a file anyone can edit, the how in the pipeline. It's the same mechanism as a database-driven loop — "for every table in this list, export it" — which is where Iterate is at its most useful.

Check yourself

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

  1. What drives this loop?