I recently started experimenting with building tooling for ETL systems. After many years of wrestling with ETL in industry, I had a few questions on my mind:

1. Can we make common data issues quick to resolve?
2. Can we make automated data transformations as easy to work with as spreadsheets?

I think the answer to both questions is yes, but don’t take my word for it – you can try the prototype at etlsheets.netlify.app (double-click on an issue to get started), and view the source code.

Motivation

Importing data at scale is painful. Your data providers will screw up the formatting, systems will experience connectivity issues, and your transformation logic will fail on cases you didn’t expect. What if our tools focused on helping with those failures, instead of assuming the happy path?

Speaking of transformations, how should we write them? Some systems take a code-first approach, which is great for coders and impenetrable for everyone else. Others take a GUI-driven approach, which usually becomes the stuff of nightmares. I think we can do better, by drawing inspiration from a tool that’s found in every office:

Transformations

Here’s what building a new transformation might look like, and here’s what it might look like when that transformation fails to run successfully.

I’ve been building web UIs in Angular and React for the last few years, and I’ve started to greatly prefer React. Extracting and using UI components is just easier and, for lack of a better word, more JavaScripty.

Extracting components in React

Say I notice that I’m creating multiple <span> elements with the same class and icon:

function Main() {
  return (
    <div>
      <span class="alert-tag"><i class="fa alert"></i>foo</span>
      <span class="alert-tag"><i class="fa alert"></i>bar</span>
      <span class="alert-tag"><i class="fa alert"></i>baz</span>
    </div>
  );
}

It’s trivial to refactor this repeated element into its own <Alert> component function within the same file:

function Main() {
  return (
    <div>
      <Alert>foo</Alert>
      <Alert>bar</Alert>
      <Alert>baz</Alert>
    </div>
  );
}

function Alert({ children }) {
  return (<span class="alert-tag"><i class="fa alert"></i>{children}</span>);
}

Super easy, uses JavaScript’s native language constructs, and I was able to do it all within the same file (but I can easily move Alert() elsewhere for wider re-use if needed). It’s just like extracting a function in a “regular” programming language, it’s something you do without even thinking about it.