The route

A whole folder: File List and Iterate

Download the exercise files

Twelve daily sales files land in a folder — eight CSV, two JSON, two Excel — and they need to become one table. You won't build twelve sources. You'll build one, and run it once per file: File List finds the files, Iterate loops over them, and Magic File from World 2 reads whatever format each one turns out to be.

Goal

One pipeline that reads every file in inbox/ and appends them all into a single all_sales.csv — 288 rows, each tagged with the file it came from.

You'll need

  • The exercise files for this level: an inbox/ folder with 12 files named sales_2025-03-01 … sales_2025-03-12, same five columns in each.
  • About 25 minutes.

Steps

1. List the files

Create a pipeline w5-2-inbox. From INGEST ▸ File System, drop a File List named inbox_files: Directory your inbox folder, Pattern sales_*, directories off. Preview Output:

File List: one row per file, with its name, path and size

One row per file, with name, stem (the name without extension), path, size, is_dir, extension and modified_time. It's a table like any other — which is what makes the next step possible. Notice it isn't in date order; nothing guarantees an order.

2. Loop over the rows

From ORCHESTRATE ▸ Flow Control, drop an Iterate and connect inbox_files into it. The connection is drawn dashed: an iterate edge, not a data edge. The rows don't flow through; each one starts a round of the loop.

The Iterate node and its settings

In every round, each column of the current row becomes a variable: @{name}, @{path}, @{size} … Everything connected after the Iterate runs once per row, with those values filled in. Leave Execution on Sequential and On Error on Stop loop on first failure for now.

3. Read one file per round

After the Iterate, add a Magic File source named read_file with File Path set to @{path} — literally that text.

Magic File reading whatever @{path} is in this round

Magic File is the right reader here because the loop doesn't know in advance whether the next file is CSV, JSON or Excel.

4. Tag and append

Add a SQL node tag_source:

SELECT *, '@{name}' AS source_file
FROM input

'@{name}' becomes 'sales_2025-03-04.csv' in that round: the file name is now a column.

Finish with a CSV Write all_sales, Filename all_sales.csv, and Append to existing file checked — otherwise each round would overwrite the previous one and you'd end with only the last file.

5. Execute and check the output

Execute. Then open all_sales.csv.

The log agrees with the file: each_file reports 288 rows — everything its rounds wrote — and the summary line ends with (288 rows written).

Expected result

  • all_sales.csv has 289 lines: one header and 288 sales.
  • The header appears once: Append writes it only when the file is new.
  • source_file has 12 distinct values, one per file in the inbox.

Common pitfalls

Run it twice and the file has 576 rows. Append does exactly what it says — the second run adds 288 more. Delete the output before running again, or put the date in the filename (level 3.6) so each run writes its own file.

Only the last file's rows are in the output. Append to existing file is off, so every round replaced the file.

The loop runs, but every round reads the same file. The Magic File path is a fixed path instead of @{path}.

Comparing @{modified_time} with a date. Inside the loop it's text like 2026-09-24T17:15:52+00:00 — UTC, with the offset. In SQL, cast it before comparing: CAST('@{modified_time}' AS TIMESTAMP).

A value with a quote in it. Inside a quoted SQL string Odara escapes it for you: a file called o'brien.csv in '@{name}' becomes 'o''brien.csv', which is valid SQL. Outside quotes a variable is pasted as-is, so quote text values.

What you built

The pattern behind most file ingestion: list, loop, read, append. Next level loops over data instead of files.

Check yourself

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

  1. Why Magic File as the reader inside this loop?