Skip to content
Journal

3 min readBuildingUpdated

Why I am building FORMA

Writing and debugging the same kind of ETL logic again and again left me with a question about messy data. FORMA is where I am testing it.

The repetition

A lot of my work with data has been preparation: opening a messy source, working out what it actually contains and turning it into something a system can rely on. For a while that meant Excel templates and VBA. Later it meant Python for the recurring parts.

The routine stayed close to the same. Read the file until I understood its quirks. Write the transformation, run it, find the rows that did not fit and go back to the code. Check the result against the rules, and often go back again. No single step was hard. The time went into moving between them, with the source in one window, the code in another, the output somewhere else and validation in a separate script.

The question

I started wondering whether working with messy data could be more direct. I did not want to remove the engineering. The code is what makes a pipeline repeatable and reviewable, and tools that hide it tend to work until the data does something unexpected, which messy data always does eventually.

So the question I kept was narrower. Could I cut out some of the back and forth while keeping every decision visible and every step reproducible as code?

The loop

FORMA puts the data at the centre and grows the pipeline out of what I do to it. Underneath is the engineering sequence I was already following by hand.

Fig. 1 / The core loop

SeeOpen the source. Every sheet of a workbook is its own source, stored once in the project and shared by pipelines.

Engine and exported code checked cell for cell

FORMA’s core loop. The export mattered most to me: if the visual work could not leave the tool as readable code, I would only be trading one kind of lock-in for another.

Engine first

The first thing I built had no interface. It was the pipeline specification, a deterministic engine in TypeScript that runs it in the browser, a generator that writes the same pipeline as pandas code, and a parity suite that runs both on the same files and checks that they agree cell for cell.

Starting there made the interface work calmer. Whenever I changed a screen, the parity tests kept asking whether the exported code still matched it.

FORMA analyst workbench: pipeline steps, a data preview of invoice rows, a before and after comparison, the step inspector and a data profile

The analyst workbench on FORMA’s built-in example project: steps, the data preview, before and after for the selected step, and the data profile.

Real product · Built-in example project and sample data

What changed

Two decisions came from the data more than from any plan.

  • Sources. I had treated an uploaded workbook as one source. Making every sheet its own source fixed a bug where two steps reading different sheets of one file both read the same sheet, and it pushed FORMA towards projects, where a source is stored once and pipelines refer to it.
  • The canvas. I started with a list of step cards and was deliberate that it should not be a node editor. The cards could not show a step pulling in a second source for a join or a lookup, so I replaced them with a canvas. The rule I held on to is that layout must never change behaviour: node positions are stored apart from the pipeline specification, so moving a box cannot change what runs or what code is generated.
FORMA pipeline canvas after a run: eleven steps from source to destination with row counts and timings, summarised as 1,001 input, 985 ready and 16 to review

The pipeline canvas after a run on the example project: 1,001 rows in, 985 ready and 16 held back for review.

Real product · Built-in example project and sample data

What FORMA is not

It is not meant to replace Python, SQL or an orchestration platform, and it is not a place to upload a CSV and let AI fix it. AI can assist, but it should never be the hidden execution engine. When a row cannot be transformed or validated with confidence, I would rather show it to a person.

What I am still figuring out

  • The canvas pulls against the principle I started from. My working answer is that the canvas is the map and the workbench is where the work happens. I do not know yet whether that holds at thirty steps instead of eleven.
  • How visible the code should be. Whether people read it, or only want to know it is there, is something I will only learn by watching them use it.
  • Branching, for cleaning logic that splits, with one rule for some rows and another for the rest.
  • What should happen when someone edits the exported code by hand. For now, export is a handoff.

On the example project, the loop works end to end. The case study has the screens and the details; the questions above are what I am working through next.