Building a Multi-Step Data Pipeline

You load a messy export from your CRM. It has bad rows at the top, inconsistent date formats, a "full name" column that should be two columns, no revenue calculations, and the output needs to be filtered and ordered for a specific stakeholder. That's half a dozen separate fixes. In a spreadsheet, you'd do them one at a time, overwriting cells, hoping you don't make a mistake you can't undo.

In ExploreMyData, each operation becomes a step in a pipeline. The pipeline lives in the sidebar: every step is visible, editable, and shows the SQL it generates. This post walks through building a real five-step pipeline from scratch, plus one thing you fix before the pipeline even starts.

The scenario

You've exported a contacts CSV from your CRM. Here's what's wrong with it:

• The first 3 rows are metadata headers, not real data (a common CRM export artifact).
• The "name" column has full names like "Jane Smith", and you need first and last separately.
• The "signup_date" column is a string like "03/15/2025", not a real date type.
• There's no "days since signup" column, which your report needs.
• You only want active users from the last 90 days.
• The output should be ordered by signup date, newest first.

Let's build the pipeline.

Before step 1: skip the metadata rows

The first three rows are metadata: column descriptions, export timestamps, that kind of thing. The instinct is to add a Filter step that throws them out. Don't. Those rows have already poisoned the import by the time a pipeline step could see them. DuckDB sniffed its column names and types from row 1, which was junk, so every column is probably text and the real header is sitting in your data as an ordinary row.

Fix it at parse time instead. Hover the file tab at the top of the app and click the gear icon, which opens Configure CSV Parsing. Set Skip first N rows to 3 and reload. The file is re-parsed from row 4, the real header is picked up as a header, and types are detected from actual data.

This is not a pipeline step and it won't appear in the sidebar. It's a property of how the file was read. The same dialog is where you override the delimiter, quote character, and encoding when a file loads wrong, and it's the first place to look when a CSV imports as one giant column.

Step 1: Split the name column

Click the green + in the Pipeline panel and select Split Column from the Columns group. Select the "name" column, set the delimiter to a space, and set the number of parts to 2. This creates "name_1" (first name) and "name_2" (last name).

The sidebar now shows one card telling you exactly what it did. Watch out for names with more than one space. Split takes the nth piece between delimiters, it does not put the remainder in the last column, so "Mary Jane Watson" gives you name_1 = "Mary" and name_2 = "Jane". "Watson" is simply gone. If your data has middle names, raise the part count, or use Extract Text from the Transform group with the "after delimiter" method, which returns everything past the first space: "Jane Watson". For more on name handling, see the name column post.

Step 2: Convert the date column

The "signup_date" column is text. Click the green + and select Convert Type from the Transform group, then set the target to date. Leave "Auto-detect date format" checked and it will recognise 03/15/2025 as March 15th. Anything unparseable becomes NULL rather than throwing an error. After this step the column is a real date, so it orders chronologically and supports date arithmetic.

Step 3: Add a calculated column

You need "days since signup." Click the green +, select Add Column from the Columns group, and enter: CURRENT_DATE - signup_date. Name the output column "days_since_signup". DuckDB handles date subtraction natively and returns the number of days. The calculated column post and date operations guide cover more expression patterns.

Step 4: Filter to active users, last 90 days

Click the green + and select Filter from the Filter & Sort group. Use two conditions with AND:

• status is "active"
• days_since_signup <= 90

Notice that you're filtering on a column you created in step 3. Each pipeline step builds on the previous result, so "days_since_signup" exists by the time this filter runs. The complex filters post covers more advanced AND/OR/NOT patterns.

Step 5: Order by signup date

Here's the one that surprises people: there is no Sort operation in the transform list. Ordering lives in three other places, and which one you want depends on whether the order needs to survive the export.

Just to look at it: click the "signup_date" column header in the grid. That sorts the view. It's instant, it costs nothing, and it does not add a step, which also means it does not change the data your export produces.

To bake the order into the result: use a SQL Query step from the Advanced group:

SELECT * FROM pipeline_output ORDER BY "signup_date" DESC

pipeline_output is how a SQL step refers to the cumulative result of the four steps above it. Point it at the original file's table name instead and you'd quietly discard all of that work.

If you also want a cap: Top / Bottom Rows and Limit Rows both take a "Sort by" column, so "the 100 newest active signups" is a single step rather than a sort plus a limit.

The pipeline sidebar now shows five cards, top to bottom:

1. Split Column: split "name" by space into 2 parts
2. Convert Type: cast signup_date to date
3. Add Column: days_since_signup
4. Filter: keep active users, last 90 days
5. SQL Query: order by signup_date descending

Editing and deleting steps

This is where pipelines beat spreadsheets. Click any card to edit it. Change the filter condition, rename the output column, change the ORDER BY direction. When you apply the edit, every step downstream re-executes automatically.

Deleting works the same way. Remove step 2 and steps 3 through 5 re-execute against the new state, though they may break if they depended on something the deleted step produced. More on that next.

What you cannot do is drag steps into a different order. The pipeline is a fixed sequence, and each step's SQL is generated against the columns that existed at the moment you added it, so shuffling them would invalidate exactly the assumptions each step was built on. If you need step 4 to run before step 2, delete both and re-add them in the order you want. That sounds tedious and mostly isn't: the cards tell you what each one did, so rebuilding two steps takes under a minute.

When steps break

Pipeline steps can have three statuses:

• Applied (green): the step ran successfully.
• Warning (yellow): the step ran but something looks off, like a column that might not exist in all cases.
• Broken (red): the step failed. This usually happens when a step references a column that was removed or renamed by an earlier edit.

If step 3 breaks, steps 4 and 5 won't execute because they depend on step 3's output. The sidebar makes this visible immediately: you can see exactly which step failed and why. Fix it, and the downstream steps recover.

The SQL trail

Every card shows the SQL it generates. This isn't just for debugging, it's documentation. When you come back to this analysis in two weeks, the pipeline tells you exactly what was done and in what order. Each step has a plain-language description and the precise SQL. No guessing.

Compare this to a spreadsheet where transformations are invisible. You see the final result but have no idea what formulas, sorts, or manual edits got you there. The pipeline is a ledger of every transformation.

Tips for longer pipelines

• Clean first, calculate second. Put filters and type conversions at the top. Calculations and aggregations come after the data is clean.
• Name your output columns clearly. When you add a calculated column, give it a descriptive name. Future steps (and future you) will thank you.
• Use the SQL view for debugging. If a result looks wrong, click the card and read the SQL. Often the issue is a column name mismatch or a type you forgot to convert.
• Don't be afraid to delete and rebuild. Unlike a spreadsheet, deleting a pipeline step doesn't destroy your source data. The original file is untouched. You're always working on a derived view.

Build your pipeline →