Clean a Dataset with the Transform Window

What you will build

A recipe that cleans a table, saved as version 2 of the dataset with version 1 untouched. We use the Titanic passenger table, which has duplicates and missing values to clean (you can use any CSV with a few duplicate rows).

Before you start

• A dataset in the Data Hub. To load Titanic, choose Import from URL and paste a public CSV address, or use a file from your disk.
• The dataset should already be at v1.

Step 1: Open the Transform window

Open the dataset and press Transform. The window shows a ribbon along the top, your Applied steps on the side, and a preview grid. The button is disabled for datasets that cannot be read by the SQL engine, such as a live database preview; import a snapshot of those first.

Step 2: Remove duplicates

On the Home tab choose Remove duplicates. Leave it on all columns and apply. The preview updates and the step appears in Applied steps. Click an earlier step and the grid shows the data as it was at that point.

Step 3: Drop rows without an age

Still on Home, choose Remove missing, pick the Age column and apply. This is a quick way to keep only complete rows. For modelling you would usually impute instead; that is a PrepFlow job.

Step 4: Filter and retype

Choose Filter rows and keep passengers where Fare is greater than 0. Then use Data type to make sure Age is a number. Every step you add can be edited or switched off later without starting over.

Step 5: Add a derived column

On the Add column tab choose Custom column. Name it FamilySize and enter a formula that adds SibSp, Parch and 1. The new column appears at the end of the grid.

Step 6: Read the code

Open the code drawer. The SQL tab shows the steps folded into one query and tells you how many steps folded; the Python tab shows the same logic as pandas. Try editing the SQL in the scratchpad and pressing run: it never changes your steps. A simple SELECT can be turned back into steps with → steps.

Step 7: Save a new version

Press Save. The result is saved as a new dataset version, with a message that a new dataset version was saved. Open the Versions tab: v1 is still there, and v2 records the recipe that produced it. Select both and use the diff to see what changed.

Step 8: Reuse the recipe

Choose Save as template and name it. The template is listed under Templates in this project and can be applied to another dataset. A pipeline can run it with the Apply Recipe step.

What to try next

• Add a parameter to the recipe (a minimum fare) and use it in the filter as {{min_fare}}.
• Pin v1 for training, then promote v2 and see how the pin behaves.
• Read the Transform window reference.